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.