0

Currently I am having a situation where I have to import around 0.5 Million rows into a SQL Server table named BARCODE_MANAGEMENT, which has the following columns:

ID int (identity column),
SETUP_ID int
CUSTOMER_ID int,
BARCODE_LABEL nvarchar(20),
REGULATORY_AGENCY_ID int,
PACKAGE_LEVEL_ID int,
BATCH_NUMBER nvarchar(20),
PRODUCT_CODE nvarchar(20) ,
SERIAL_LIFECYCLE_ID int,
PACK_LIFECYCLE_ID int,

There is no problem in importing and processing the data using ASP.NET program.

Now when I query the database view (BARCODE_MANAGEMENT_VIEW), using the following query , I get a time out on my ASP.NET program as the query execution takes more than 1 min and 40 sec.

SELECT * FROM dbo.BARCODE_MANAGEMENT_VIEW 
        WHERE             
             (CUSTOMER_ID=@CUSTOMER_ID OR @CUSTOMER_ID=-1) AND
             (REGULATORY_AGENCY_ID=@REGULATORY_AGENCY_ID OR @REGULATORY_AGENCY_ID=-1) AND
             (BATCH_NUMBER= @BATCH_NUMBER OR @BATCH_NUMBER='-1') AND 
            (PACKAGE_LEVEL_ID=@PACKAGE_LEVEL_ID OR @PACKAGE_LEVEL_ID=-1) AND  
             (PRODUCT_CODE=@PRODUCT_CODE OR @PRODUCT_CODE=-1) AND
            (SERIAL_LIFECYCLE_ID=@SERIAL_LIFECYCLE_ID OR @SERIAL_LIFECYCLE_ID=-1)  AND
            (PACK_LIFECYCLE_ID=@PACK_LIFECYCLE_ID OR @PACK_LIFECYCLE_ID=-1)   
        ORDER BY ID ASC

I have created the following indexes as well on the table and not sure how I can do it on a SQL VIEW:

CREATE NONCLUSTERED INDEX [IX_RetrieveList] ON [dbo].[BARCODE_MANAGEMENT]
(
    [SETUP_ID] ASC,
    [CUSTOMER_ID] ASC,
    [REGULATORY_AGENCY_ID] ASC,
    [BATCH_NUMBER] ASC,
    [PACKAGE_LEVEL_ID] ASC,
    [PRODUCT_CODE] ASC,
    [SERIAL_LIFECYCLE_ID] ASC,
    [PACK_LIFECYCLE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

What am I doing wrong. I am a newbie to SQL Server database coding and I would appreciate any suggestions on how I could add new indexes to query for faster retrieval.

  • 2
    Why is SETUP_ID first column in the index? – Lennart May 3 '19 at 10:33
1

The first field in your index is SETUPID but this does not appear in the query, rendering the index unable to support this query.

Which of these fields is the best "discriminator" - the one that filters the rows down the quickest? That should appear first in your index.

I would suggest that an Index on just CUSTOMER_ID might be a better place to start.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.