Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Simple query takes too much time /Execution Plan and Table Structure provided

Unanswered Simple query takes too much time /Execution Plan and Table Structure provided

  • Sunday, February 03, 2013 6:57 AM
     
     
    Sign In to Vote
    0

    I have a very simple query as mentioned below where in I am fetching data from two tables using a join. But it takes 4 seconds to load the result. I am curious about this because these two tables are widely used in our project . I want to reduce the execution time to 0. Can some one please suggest me some ideas about how to optimize these two tables. Hotmetal.Tapticket has around 132732 rows and Analysis.ChemicalSample has around 500620 rows at present. I can't think about splitting these two tables into multiple tables also.

    Query:

    select * from HotMetal.TapTicket HT  
    JOIN Analysis.ChemicalSample AC WITH(INDEX(pkChemicalSample)) ON AC.TapTicketID=HT.TapTicketID
    WHERE HT.CreatedDate >= '2011-Sep-01' AND HT.CreatedDate < '2012-Dec-30'

    Here comes the table structure :

    table structure

    Query Execution Plan: 

    HotMetal.Tapticket:

    CREATE TABLE [HotMetal].[TapTicket](
    [TapTicketID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [TapTicketNo] [nvarchar](50) NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [CreatedBy] [nvarchar](100) NOT NULL,

    [PotLineNo]  AS (case when isnumeric(substring([TapticketNo],(4),(1)))=(1) then CONVERT([int],substring([TapticketNo],(4),(1)),(0))  end),
    [IsTreatmentCompleted] [bit] NOT NULL,

     CONSTRAINT [pkTapTicket] PRIMARY KEY CLUSTERED 
    (
    [TapTicketID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY],
     CONSTRAINT [ukTapTicketTapTicketNo] UNIQUE NONCLUSTERED 
    (
    [TapTicketNo] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    Analysis.ChemicalSample

    CREATE TABLE [Analysis].[ChemicalSample](
    [ChemicalSampleID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [ChemicalSampleTypeID] [int] NOT NULL,
    [SampleLevel] [int] NOT NULL,
    [SampleSuffix] [nchar](1) NOT NULL,
    [SampleDate] [datetime] NOT NULL,
    [BatchID] [int] NULL,
    [TapTicketID] [int] NULL,
    [CastID] [int] NULL,

     CONSTRAINT [pkChemicalSample] PRIMARY KEY CLUSTERED 
    (
    [ChemicalSampleID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    )


    • Edited by pepcoder Sunday, February 03, 2013 6:58 AM
    • Edited by pepcoder Sunday, February 03, 2013 7:00 AM
    • Edited by pepcoder Sunday, February 03, 2013 7:11 AM
    • Edited by pepcoder Sunday, February 03, 2013 7:15 AM
    •  
     

All Replies

  • Sunday, February 03, 2013 7:21 AM
     
     
    Sign In to Vote
    1

    Hi - Sorry to say that your picture is not clear to me. Hence am suggesting few, please check with your structure and see it helps you.

    1. Remove the index hint from your query.(Optimizer is smart enough to find the best for you. It is a rare case that you should try with hint forcing after a careful analysis.)

    2. Make sure you have index on TapTicket and CreatedDate column.

    3. Make sure the TapTicket column has the same datatype in both table(I doubt)

    4. Remove '*' and give your necessary columns.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

     
  • Sunday, February 03, 2013 7:30 AM
    Moderator
     
     
    Sign In to Vote
    1

    In addition to Latheesh's note.

    Make sure there are indexes on JOIN columns and WHERE condition columns.

    USE the 'YYYY-MM-DD' ANSI string date format.

    Optimization article:

    http://www.sqlusa.com/articles/query-optimization/

    Kalman Toth SQL 2008 GRAND SLAM
    Paperback/Kindle: SQL Server 2012 Programming


     
  • Sunday, February 03, 2013 7:32 AM
    Answerer
     
     
    Sign In to Vote
    1
    How much data does the SELECT return? Please do not use SELECT * but specify only needed columns. Is that possible to have a NCI on created date column( if it returns large amount of data I would create CI on create date column and have TapTicketID NCI PK)

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

     
  • Sunday, February 03, 2013 7:35 AM
     
     
    Sign In to Vote
    1

    As I said, my third point.

    Why do you have different datatype for TapTicket? Thats the one likely you are getting the sluggishness.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

     
  • Sunday, February 03, 2013 7:37 AM
    Answerer
     
     
    Sign In to Vote
    1
    TapTicketID ARE  both INT.....See the table's structure

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

     
  • Sunday, February 03, 2013 7:43 AM
     
     
    Sign In to Vote
    1

    Uri, My bad, thanks for pointing out it.

    Please remove the index hint and update the stats and let us know the execution plan.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

     
  • Monday, February 04, 2013 6:54 AM
     
     
    Sign In to Vote
    0

    Thank you all. After following the suggestions provided by you all, below mentioned query takes 0.00 seconds.

    select HT.TapticketID,AC.chemicalSampleID,AC.Code,HT.TapticketID from HotMetal.TapTicket HT  
    JOIN Analysis.ChemicalSample AC  ON AC.TapTicketID=HT.TapTicketID
    WHERE HT.CreatedDate >= '2011-Sep-01' AND HT.CreatedDate < '2012-Dec-30'

    Steps I have done:

    step1: Created a new non-clustered index on Hotmetal.Tapticket.CreatedDate column. ->this reduced 2 seconds of the execution time

    Step2: updated the statistics of the tables HotMetal.Tapticket and Analysis.ChemicalSample

    Step 3: Removed the unwanted index I had added in my query.

    Step4: selected only the columns  required..

    Latest execution plan:

    But why the clustered index scan is still taking 79% of the execution time even though o/p time is 0.000?

    Regards

    -pep





    • Edited by pepcoder Monday, February 04, 2013 6:55 AM
    • Edited by pepcoder Monday, February 04, 2013 6:56 AM
    • Edited by pepcoder Monday, February 04, 2013 6:57 AM
    • Edited by pepcoder Monday, February 04, 2013 6:57 AM
    •  
     
  • Monday, February 04, 2013 6:59 AM
     
     
    Sign In to Vote
    1
    Hi pepcoder, you also may remove the index hint and see the execution plan. Due to the index forcing, the clustered index is being used instead of the index on TapTicket(I assume you have index on TapTicket in ChemicalSample table).

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

     
  • Monday, February 04, 2013 7:08 AM
     
     
    Sign In to Vote
    0

    Hi Latheesh,

    Thnx!I had already removed the unwanted index which is creating trouble.

    select HT.TapticketID,AC.chemicalSampleID,AC.Code,HT.TapticketID from HotMetal.TapTicket HT  
    JOIN Analysis.ChemicalSample AC  ON AC.TapTicketID=HT.TapTicketID
    WHERE HT.CreatedDate >= '2011-Sep-01' AND HT.CreatedDate < '2012-Dec-30'

    Execution plan provided in my previous post is the updated one with out the index hint.

    Yes you are correct.I do have an index "idxChemicalSampleTapTicketID" in ChemicalSample table

    -pep




    • Edited by pepcoder Monday, February 04, 2013 7:09 AM
    • Edited by pepcoder Monday, February 04, 2013 7:10 AM
    • Edited by pepcoder Monday, February 04, 2013 7:10 AM
    •  
     
  • Monday, February 04, 2013 7:12 AM
     
     
    Sign In to Vote
    0

    Hi Latheesh,

    Yes you are correct.I do have an index "idxChemicalSampleTapTicketID" in ChemicalSample table

    -pep





    Yes, you may create the index on TapTicketID for ChemicalSample table. However, I dont mean to drop the clustered index, but to remove only the hints.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

     
  • Monday, February 04, 2013 7:26 AM
    Answerer
     
     
    Sign In to Vote
    0
    It has to return chemicalSampleID,AC.Code from chemical table and because there is no index on TapTicketID it used CI scan to return matched rows (INNER JOIN ).

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

     
  • Monday, February 04, 2013 7:34 AM
     
     
    Sign In to Vote
    0

     "However, I dont mean to drop the clustered index, but to remove only the hints."

    @Latheesh:I have removed only the hint.not dropped the clustered index.