none
non key column in where condition

    Question

  • Hello All,

     
    CREATE TABLE [dbo].[DataStore](
    
    [ID] [bigint] NOT NULL, 
    [ParentId] [bigint] NOT NULL, 
    [TimeStampUTC] [decimal](20, 8) NOT NULL,
    [TimeStampLocal] [decimal](20, 8) NULL,
    [Col1] [int] NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL, 
    )
     ON [PRIMARY]
    
    GO
    
    Query 1: 
    SELECT * FROM DataStore WHERE ParentId IN (1) AND TimeStampUTC between 0 and 1400000000
    
    Query 2: 
    SELECT * FROM DataStore WHERE ParentId IN (1) AND TimeStampLocal between 0 and 1400000000
    
    I have added Clustered unique key on ParentId and TimeStampUTC 
    
    Query 1 it works fine but 
    
    Query 2 takes more time.
    

    Please Advise.


    vinaya kumar

    Tuesday, September 10, 2013 11:58 AM

Answers

  • If query is using clustered index it doesn't need key lookup . 

    As here its using nonclustered index its using lookup , and also you can't add all columns in index to avoid lookup.

    If its such urgent you can get related TimeStampUTC from TimeStampLocal using offset between local and UTC and use it in query  for TimeStampLocal. 

    Amish Shah

    http://blog.sqltechie.com

    • Marked as answer by Vinaya Kumar Wednesday, September 11, 2013 11:22 AM
    • Unmarked as answer by Vinaya Kumar Wednesday, September 11, 2013 11:27 AM
    • Marked as answer by Vinaya Kumar Thursday, September 12, 2013 8:24 AM
    Wednesday, September 11, 2013 7:03 AM

All replies

  • Do you really need SELECT *? It is always harder to optimize the query when you are selecting all columns.

    If selectivity of second query is under approx 20% of the records in the table non clustered index on (ParentID, TimeStampLocal) should help.


    Regards, Dean Savović

    Tuesday, September 10, 2013 12:01 PM
  • You have a clustering key for the combination of ParentId and TimeStampUTC. So the first query go for a clustered index seek. How ever since you don't have a SARGable index, the second query will go for a clustered index scan. Try creating a non clustered index on ParentID and TimeStampLocal column and test. 

    Krishnakumar S

    Tuesday, September 10, 2013 12:08 PM
  • Thanks for your reply.

    am re writting my queries

    Query 1: SELECT ParentId, TimeStampUTC, Col1, Col2,Cll3 FROM DataStore

    WHERE ParentId IN (1) AND TimeStampUTC between 0 and 1400000000 Query 2: SELECT ParentId, TimeStampLocal, Col1, Col2,Cll3 FROM DataStore

    FROM DataStore WHERE ParentId IN (1) AND TimeStampLocal between 0 and 1400000000

    I tried with non clustered index on (ParentID, TimeStampLocal) but it is taking time when i use order by clause for non clustered.

    please suggest how to proceed.


    vinaya kumar

    Tuesday, September 10, 2013 12:11 PM
  • Hello Krishnakumar,

    after adding non clustered index on ParentID and TimeStampLocal column, i checked the execution plan, it is still referring to Clusteredindex hence query 2 fetching and order by clause is consuming time compare to query 1


    vinaya kumar

    Tuesday, September 10, 2013 12:18 PM
  • Hi,

    Execution Plan

    Though i defined Non Clustered index on ParentId and TimeStampLocal, timestamplocal is not getting seek.

    Its very urgent, can anyone help me how to make timestamplocal also to get seek. ??


    vinaya kumar

    Tuesday, September 10, 2013 1:28 PM
  • You have covert_implicit in your execution plan operator, try avoiding this because this will keep SQL server from using statistics on this fields and thus using the best index. Use the correct data type (same as in table) for your predicate constants in query (decimal (20, 8) and bigint).

    Try using index (TimeStampLocal, ParentId) non-clustered because I think that column TimeStampLocal is more selective.

    The fact that you have non-clustered index on predicate columns does not mean that SQL server will use this index. This happens when predicate values are not selective enough and this is when it is more expensive for SQL server to use non-clutered index and he does clustered index scan.


    Regards, Dean Savović


    Tuesday, September 10, 2013 1:56 PM
  • Hello Dean Savovic,

    I have applied Non Clustered index on (TimeStampLocal, ParentId) below is the execution plan.

    Execution plan for Query2.

    execution plan graph

    removed all convert_implicit by declaring variables. but not able to find how to remove key lookup.


    vinaya kumar

    Wednesday, September 11, 2013 5:07 AM
  • To remove key lookup, you should have all the columns in your select present on your index.

    http://sqlserverperformance.wordpress.com/2010/06/09/how-to-eliminate-key-lookups-in-sql-server-2008/


    Satheesh

    Wednesday, September 11, 2013 5:46 AM
  • The key lookup is because you are selecting columns that are not available in index key. After seeking the key, it has to do a key lookup to find the other columns.

    Only list the required columns in the SELECT column list and INCLUDE that columns in the non clustered index. Like below..

    CREATE INDEX IX_TDataStore
    ON DataStore (TimeStampLocal, ParentId)       
    INCLUDE (Col1, Col2,Col3);


    Krishnakumar S

    Wednesday, September 11, 2013 5:49 AM
  • Thanks to everyone,

    I need to know below which combination is more efficient and recommended to use.

    --Clustered but not primary Key Columns:
    
    CREATE UNIQUE CLUSTERED INDEX [IX_DataStore] ON [dbo].[DataStore] 
    (
    	[ParentId] ASC,
    	[TimeStampUTC] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    GO
    
    
                           OR
    
    --Clustered and primary Key Columns:
    
    ALTER TABLE [dbo].[DataStore] ADD  CONSTRAINT [PK_DataStore] PRIMARY KEY NONCLUSTERED 
    (
    	[ParentId] ASC,
    	[TimeStampUTC] 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) ON [PRIMARY]
    GO



    vinaya kumar

    Wednesday, September 11, 2013 6:21 AM
  • I think both will work almost same way. Its just that the unique key could allow null values if not enforced at column level. But primary key will not

    Satheesh

    Wednesday, September 11, 2013 6:25 AM
  • btw what works better for you?

    Satheesh

    Wednesday, September 11, 2013 6:26 AM
  • Put clustered index on columns that are more often used in queries as predicates. This way you will avoid Key Lookup in execution plan, operator that slows down the query on large number of records.

    Regards, Dean Savović

    Wednesday, September 11, 2013 6:41 AM
  • On which columns is IX_ParentID index defined on?

    What is estimated and actual number of rows for Index Seek operator?

    When you declare variables in the batch and use them in query as predicate values then SQL Server cannot use statistics on predicate columns and he is guessing the cardinality, e.g. selectivity of the set operator is working with. 

    Use stored procedures with input parameters instead of batches with declared variables.


    Regards, Dean Savović

    Wednesday, September 11, 2013 6:45 AM
  • If query is using clustered index it doesn't need key lookup . 

    As here its using nonclustered index its using lookup , and also you can't add all columns in index to avoid lookup.

    If its such urgent you can get related TimeStampUTC from TimeStampLocal using offset between local and UTC and use it in query  for TimeStampLocal. 

    Amish Shah

    http://blog.sqltechie.com

    • Marked as answer by Vinaya Kumar Wednesday, September 11, 2013 11:22 AM
    • Unmarked as answer by Vinaya Kumar Wednesday, September 11, 2013 11:27 AM
    • Marked as answer by Vinaya Kumar Thursday, September 12, 2013 8:24 AM
    Wednesday, September 11, 2013 7:03 AM
  • Hi Dean,

    IX_ParentID is defined as below.

    CREATE UNIQUE CLUSTERED INDEX [IX_ParentID] ON [dbo].[DataStore] 
    (
    	[ParentId] ASC,
    	[TimeStampUTC] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    GO

     As SHAHAMISHM suggested am converting timestamplocal to utc with offset and using above clustered index.

    now am getting Convert_Implicit in Seek in execution plan but  if i define unique key on ID column then Convert_Implicit is eliminated.

    i would like to know how this unique key on ID column will avoid convert_implicit of clusteredindex (ParentId, TimeStampUTC).

    Thanks to each one of you for your valuable information.


    vinaya kumar

    Wednesday, September 11, 2013 11:27 AM
  • Please find the execution plan facing problem with conversion in seek predicates:

    Implicit Conversion


    vinaya kumar

    Wednesday, September 11, 2013 11:56 AM
  • The way to go is the query on the right side.

    The only way to avoid convert_implicit in exec plan is to have same data types on both sides of the operator.


    Regards, Dean Savović

    Wednesday, September 11, 2013 4:34 PM