All geek questions in one place

My SQL query takes too long when using a non-clustered index

I have a database table in which customer data is stored, and when they call, we use their phone to search for their details, but usually it takes about 2-3 seconds, but recently it takes 5 seconds without additional data. If I query the table using home_phone_no = '441903354676', this will be returned in a second. But if it is requested using home_phone_no = '441903354676' or business_phone_no = '441903354676', then it takes 5 seconds.

Now there are records of customers with a volume of 1.4 m. But if someone can see something obvious or offer some useful suggestions, this will be very welcome.

This is the table structure.

CREATE TABLE [dbo].[CCDB_ICR]
(
                [bill_account_no] [varchar](10) NOT NULL,
                [reference_id] [varchar](11) NULL,
                [bill_account_status] [varchar](2) NULL,
                [customer_type] [varchar](1) NULL,
                [customer_name] [varchar](56) NULL,
                [property_address] [varchar](69) NULL,
                [outer_post_code] [varchar](4) NULL,
                [inner_post_code] [varchar](3) NULL,
                [customer_move_in_date] [datetime2](7) NULL,
                [customer_move_out_date] [datetime2](7) NULL,
                [debt_flag] [varchar](1) NULL,
                [payment_category_flag] [varchar](2) NULL,
                [payment_plan_flag] [varchar](1) NULL,
                [key_customer_flag] [varchar](1) NULL,
                [home_phone_no] [varchar](12) NULL,
                [business_phone_no] [varchar](12) NULL,
                [contact_type] [varchar](4) NULL,
                [contact_code] [varchar](1) NULL,
                [contact_method] [varchar](1) NULL,
                [contact_date] [date] NULL,
                [contact_time] [varchar](5) NULL,
                [contact_status] [varchar](1) NULL,
                [contact_unit_resp] [varchar](4) NULL,
                [outstanding_balance] [decimal](9, 2) NULL,
                [date_time_inserted] [datetime] NOT NULL,
                [date_time_updated] [datetime] NULL,

    CONSTRAINT [PK_CCDB_ICR] 
        PRIMARY KEY CLUSTERED([bill_account_no] ASC)
                    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
                          ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

This is the index structure:

CREATE NONCLUSTERED INDEX [NC_Business&Home_Phone$CCDB_ICR] 
    ON [dbo].[CCDB_ICR] ([home_phone_no] ASC, [business_phone_no] ASC)
    INCLUDE ([bill_account_no]) 


ALTER procedure [dbo].[GET_ACCOUNT_BY_PHONE_NUMBER]
(
@CLI varchar(12),
@RETURN_VALUE int out,
@NUMBER_OF_ACCOUNTS int out,
@ACCOUNT_NUMBER varchar(10) out
)
as
Begin Try
      declare @ret int
      Select @ret=COUNT(*) from CCDB_ICR where home_phone_no=@CLI or business_phone_no=@CLI
      if @ret=0
      select @RETURN_VALUE=0,
      @NUMBER_OF_ACCOUNTS=0,
      @ACCOUNT_NUMBER=null
      else if @ret=1
      select @RETURN_VALUE=0,
      @NUMBER_OF_ACCOUNTS=1,
      @ACCOUNT_NUMBER=(Select  bill_account_no from CCDB_ICR where home_phone_no=@CLI or business_phone_no=@CLI)
      else if @ret>1
      select @RETURN_VALUE=0,
      @NUMBER_OF_ACCOUNTS=@ret ,
      @ACCOUNT_NUMBER=null
end Try

Begin Catch
      select @RETURN_VALUE=-1,
      @NUMBER_OF_ACCOUNTS=null ,
      @ACCOUNT_NUMBER=null
End Catch
+6
2

home_phone_no = '441903354676' or business_phone_no = '441903354676', home_phone_no = '441903354676' and business_phone_no = '441903354676'.

.

or, , :

create nonclustered index [NC_Business&Home_Phone$CCDB_ICR] 
  on [dbo].[CCDB_ICR] ([home_phone_no] asc);

create nonclustered index [NC_Business&Business_Phone$CCDB_ICR] 
  on [dbo].[CCDB_ICR] ([business_phone_no] asc);

, [bill_account_no] , , .

:

alter procedure [dbo].[get_account_by_phone_number] (
    @cli varchar(12)
  , @return_value int out
  , @number_of_accounts int out
  , @account_number varchar(10) out
) as
begin;
  set nocount, xact_abort on;

  set @return_value = 0;
  set @number_of_accounts = 0;

  select 
      @number_of_accounts = count(*)
    , @account_number = case when count(*)=1 then max(bill_account_no) else null end
  from ccdb_icr 
  where home_phone_no=@cli 
     or business_phone_no=@cli;
end;
go

, , sniffing.

, :

SQL Server sniffing :

+6

( , home_phone_no . business_phone_no), , , .

UNION ALL, , CTE, :

;with x as (
  -- your query here
)
select * from x where home_phone_no = @home_phone_no
union all
select * from x where business_phone_no = @business_phone_no

, WITH RECOMPILE, . OPTION (RECOMPILE), (, , ).

0

Source: https://habr.com/ru/post/1015946/

More articles:


All Articles