none
Slow query execution

    Question

  • Hi All, I have a table that has over 30million records, and for this reason the front end application gives a query time out error anything a select query is executed from the application.

    Even when you execute the below select query in management studio it takes more than 30 seconds to return a single row

    Select * from Serial WHERE Serialnumber = '094000700001'

    The below is the table structure.

    Please any idea how I can improve the performance?

    Please assist urgently

    CREATE TABLE [dbo].[Serial](
    	[HQID] [int] NOT NULL,
    	[LastUpdated] [datetime] NOT NULL,
    	[SerialNumber2] [nvarchar](20) NOT NULL,
    	[SerialNumber3] [nvarchar](20) NOT NULL,
    	[StoreID] [int] NOT NULL,
    	[TransactionEntryID] [int] NOT NULL,
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[ItemID] [int] NOT NULL,
    	[SerialNumber] [nvarchar](20) NOT NULL,
    	[DBTimeStamp] [timestamp] NULL,
    	[Status] [int] NOT NULL,
     CONSTRAINT [PK_Serial] PRIMARY KEY NONCLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 70) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    ALTER TABLE [dbo].[Serial] ADD  CONSTRAINT [DF_Serial_HQID]  DEFAULT ((0)) FOR [HQID]
    GO
    
    ALTER TABLE [dbo].[Serial] ADD  CONSTRAINT [DF_Serial_LastUpdated]  DEFAULT (getdate()) FOR [LastUpdated]
    GO
    
    ALTER TABLE [dbo].[Serial] ADD  CONSTRAINT [DF_Serial_SerialNumber2]  DEFAULT ('') FOR [SerialNumber2]
    GO
    
    ALTER TABLE [dbo].[Serial] ADD  CONSTRAINT [DF_Serial_SerialNumber3]  DEFAULT ('') FOR [SerialNumber3]
    GO
    
    ALTER TABLE [dbo].[Serial] ADD  CONSTRAINT [DF_Serial_StoreID]  DEFAULT ((0)) FOR [StoreID]
    GO
    
    ALTER TABLE [dbo].[Serial] ADD  CONSTRAINT [DF_Serial_TransactionEntryID]  DEFAULT ((0)) FOR [TransactionEntryID]
    GO
    
    ALTER TABLE [dbo].[Serial] ADD  CONSTRAINT [DF_Serial_ItemID]  DEFAULT ((0)) FOR [ItemID]
    GO
    
    ALTER TABLE [dbo].[Serial] ADD  CONSTRAINT [DF_Serial_SerialNumber]  DEFAULT ('') FOR [SerialNumber]
    GO
    
    ALTER TABLE [dbo].[Serial] ADD  CONSTRAINT [DF_Serial_Status]  DEFAULT ((0)) FOR [Status]
    GO
    



    CRM Manager


    • Edited by flashyjunior Wednesday, January 11, 2017 10:10 PM
    Wednesday, January 11, 2017 10:06 PM

Answers

  • An index on your serialnumber would be a good start. How unique is that serial number?
    • Marked as answer by flashyjunior Wednesday, January 11, 2017 11:57 PM
    Wednesday, January 11, 2017 10:27 PM

All replies

  • Can you post the indexes you have? Also, what happens when you change the above to be

    Select * from Serial WHERE Serialnumber = N'094000700001'

    Note that N' in front of the number. Why the numbers defined as nvarchar(20) - can they contain unicode letters?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, January 11, 2017 10:25 PM
    Moderator
  • An index on your serialnumber would be a good start. How unique is that serial number?
    • Marked as answer by flashyjunior Wednesday, January 11, 2017 11:57 PM
    Wednesday, January 11, 2017 10:27 PM
  • I agree with RyanAB, an index on SerialNumber will prevent a clustered index scan of the 30M record table.

    If it's an off the shelf application it would not be good advice to change the SerialNumber to int/bigint; but it would certainly help if it were possible.

    Your sample query may not be representative of the actual query that the application is running.  It may be helpful to post the actual query that is timing out.

    What version of SQL Server are you running this on? 

    Wednesday, January 11, 2017 10:38 PM
  • Is it just this query which is slow? Is there blocking going on when running the query?  I agree with the indexing suggestions.

    Also what is the wait stats when running this query?

    Wednesday, January 11, 2017 11:01 PM
  • Hi,

    Thanks for the reply,nothing happened when I used N'.

    Also,the serialnumbers are only numbers eventhough the field datatype is NVARCHAR


    CREATE TABLE [dbo].[Serial] ADD  CONSTRAINT [PK_Serial] PRIMARY KEY NONCLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 70) ON [PRIMARY]




    CRM Manager


    • Edited by flashyjunior Wednesday, January 11, 2017 11:03 PM
    Wednesday, January 11, 2017 11:02 PM
  • If they only can be numbers, then bigint may be a better choice. Are you able to change the structure of the table?

    Are you saying that there is no index on the serial number? If yes, then this is the problem - you need an index to support the above query.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, January 11, 2017 11:07 PM
    Moderator
  • Hi,

    Every serialnumber is very unique


    CRM Manager

    Wednesday, January 11, 2017 11:10 PM
  • Hi,

    Every serialnumber is very unique


    CRM Manager

    If they are unique, then that may be a better choice as your primary key over an arbitrary identity field. Is the ID serving a purpose?
    Wednesday, January 11, 2017 11:32 PM
  • Hi, Thanks, creating a non-clustered index on the serialnumber field resolved it, not it takes less tan a second to return the record Thanks guys

    CRM Manager

    Wednesday, January 11, 2017 11:57 PM