Pertanyaan Query SQL saya memakan waktu terlalu lama ketika menggunakan indeks non-cluster


Saya memiliki tabel database yang menyimpan rincian pelanggan dan ketika mereka menelepon kami menggunakan telepon mereka untuk mencari detail mereka, tetapi ini biasanya membutuhkan waktu sekitar 2-3 detik tetapi baru-baru ini telah mengambil 5 detik tanpa data tambahan. Jika saya query tabel menggunakan home_phone_no = '441903354676' ini kembali dalam sub detik. Tetapi jika ditanyakan menggunakan home_phone_no = '441903354676' atau business_phone_no = '441903354676' maka ini membutuhkan waktu 5 detik.

Sekarang ada beberapa catatan pelanggan 1,4 juta. Tetapi jika ada yang bisa melihat sesuatu yang jelas atau memberikan beberapa saran yang bermanfaat, ini akan sangat disambut.

Ini adalah struktur meja

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]

Ini adalah struktur indeks:

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]) 

Di sini adalah prosedur tersimpan

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
2018-03-22 17:25


asal


Jawaban:


Indeks tidak dapat digunakan untuk home_phone_no = '441903354676' or business_phone_no = '441903354676', tetapi bisa digunakan untuk home_phone_no = '441903354676' and business_phone_no = '441903354676'.

Ini tidak akan dapat menggunakan kolom kedua dari kunci indeks tanpa syarat untuk kolom pertama dari kunci indeks.

Untuk menggunakan or, Anda akan menggunakan indeks pendukung terpisah, misalnya:

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); 

Juga, Anda tidak perlu memasukkan [bill_account_no] sebagai kolom yang disertakan pada indeks Anda karena merupakan kunci pengelompokan, dan dengan demikian sudah dimasukkan secara implisit.

Anda dapat menyederhanakan seluruh prosedur Anda ke:

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

Jika Anda masih mengalami masalah kinerja setelah membuat indeks yang sesuai dan memperbarui prosedur, maka Anda harus mencoba mengidentifikasi jika sniffing parameter menyebabkan masalah.

Saya akan mulai dengan artikel ini oleh Paul White yang mencakup hal-hal berikut:

SQL Server menyediakan berbagai petunjuk kueri dan opsi lain untuk menyetel perilaku sniffing parameter:

  • Petunjuk OPTIMIZE FOR (@parameter = value) membuat rencana yang dapat digunakan kembali berdasarkan nilai tertentu
  • MENGOPTIMASI UNTUK (@parameter UNKNOWN) menggunakan statistik distribusi rata-rata untuk parameter tertentu
  • MENGOPTIMASI UNTUK DIKETAHUI menggunakan distribusi rata-rata untuk semua parameter (efek yang sama seperti jejak bendera 4136)
  • Opsi prosedur TERUNGKAP DENGAN RECOMPILE mengkompilasi rencana prosedur baru untuk setiap eksekusi
  • The OPTION (RECOMPILE) permintaan query menyusun rencana baru untuk pernyataan individu   
    ~ Parameter Sniffing, Embedding, dan Opsi RECOMPILE - Paul White

6
2018-03-22 17:31



Bahkan dengan indeks yang benar (dalam hal ini, dua indeks single-col terpisah, satu di home_phone_no dan lainnya di business_phone_no), ketika Anda ATAU predikat Anda kemungkinan besar akan berakhir dengan pemindaian.

Jauh lebih baik menggunakan UNION ALL, mungkin dengan CTE untuk menghindari duplikasi seluruh pertanyaan:

;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

Juga, hindari MEREKOMENDASIKAN, itu palu terlalu besar. Gunakan OPSI (REKOMASA) sebagai gantinya, dan hanya jika benar-benar diperlukan (yang saya asumsikan tidak terjadi di sini).


0
2018-03-23 10:55