Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2005
    Posts
    10

    Unanswered: How to tune my query

    I have written a query which fetches data from a table with huge amount of data. This query is actually being used in a stored-procedure and it takes a lot of time, hence slows down my SP.

    Here is the query that I'm builiding in the stored-procedure:
    -------------------------------------------------------------
    SET @selectLeadsInPeriodString = ' SELECT LM_Dealer, LM_Brand, SUM(LM_ImpressionCount)
    FROM LM_ImpressionCount_Dealer'
    IF(@timeCriterion IS NULL OR LTRIM(RTRIM(@timeCriterion)) = '' OR LTRIM(RTRIM(@timeCriterion)) = 'NULL')
    SET @selectLeadsInPeriodString = @selectLeadsInPeriodString +
    ' WHERE (CONVERT(varchar,[LM_ImpressionCount_Dealer].[LM_ImpressionDate],102) >= ''' +
    CONVERT(varchar,@startDateTime,102) +
    ''' AND CONVERT(varchar,[LM_ImpressionCount_Dealer].[LM_ImpressionDate],102) <= ''' +
    CONVERT(varchar,@endDateTime,102) + ''')'
    ELSE IF(@timeCriterion = 'CurrentMonth')
    SET @selectLeadsInPeriodString = @selectLeadsInPeriodString +
    ' WHERE MONTH([LM_ImpressionCount_Dealer].[LM_ImpressionDate]) = MONTH(GETDATE())'
    ELSE IF(@timeCriterion = 'PreviousMonth')
    SET @selectLeadsInPeriodString = @selectLeadsInPeriodString +
    ' WHERE MONTH([LM_ImpressionCount_Dealer].[LM_ImpressionDate]) = MONTH(GETDATE()) - 1'
    ELSE IF(@timeCriterion = 'YearToDate')
    SET @selectLeadsInPeriodString = @selectLeadsInPeriodString +
    ' WHERE ([LM_ImpressionCount_Dealer].[LM_ImpressionDate] >= cast((''1/1/''+cast(year(getdate()) AS varchar(4))) AS datetime)
    AND CONVERT(varchar,[LM_ImpressionCount_Dealer].[LM_ImpressionDate],102) <= CONVERT(varchar,GETDATE(),102))'

    IF(@brand IS NOT NULL AND LTRIM(RTRIM(@brand)) <> '')
    SET @selectLeadsInPeriodString = @selectLeadsInPeriodString +
    ' AND [LM_ImpressionCount_Dealer].[LM_Brand] = ''' + @brand + ''''

    SET @selectLeadsInPeriodString = @selectLeadsInPeriodString + ' GROUP BY LM_Dealer, LM_Brand'

    The variables used in the query formation above are passed as input parameters to the SP.
    The table being queried has columns 'LM_Dealer', 'LM_Brand', 'LM_ImpressionCount' and 'LM_ImpressionDate'.
    Also, the table has a non-clustered index on the column LM_ImpressionDate.

    With all this information, can anyone suggest as to how I can optimize the query above.

    Thanks in advance.

    -Dex

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Ugh. So much wrong with this. Here are a few quickies:

    Change: cast(('1/1/'+cast(year(getdate()) AS varchar(4))) AS datetime)
    To: dateadd(year, datediff(year, 0, getdate()), 0)

    Change: IF(@brand IS NOT NULL AND LTRIM(RTRIM(@brand)) <> '')
    To: IF len(@brand) > 0

    Add the following statement to the end of your query:
    Print @selectLeadsInPeriodString
    ...then post the output so we can look at it without having to wade through a swamp of dynamic SQL.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why the dynamic sql?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Sep 2005
    Posts
    10
    Hi

    This SP is being used for a report. The search criteria for the report is selected by the user in the front-end which I am passing as a parameter to this stored-procedure. And it is not necessary that all the parameters that is timecriterion, startdate, enddate and brand are available at all times...

    So I am using a Dynamic SQL in this case...

    However, if I do a print of the Dynamic SQL and run that SQL for a specific condition it takes around 20 sec to get executed in the Query Analyzer.

    Here is the query:
    SELECT LM_Dealer, LM_Distributor, LM_Brand, SUM(LM_ImpressionCount)
    FROM LM_ImpressionCount_Dealer
    WHERE
    ([LM_ImpressionCount_Dealer].[LM_ImpressionDate] >= cast(('1/1/'+cast(year(getdate()) AS varchar(4))) AS datetime)
    AND CONVERT(varchar,[LM_ImpressionCount_Dealer].[LM_ImpressionDate],102) <= CONVERT(varchar,GETDATE(),102))
    AND [LM_ImpressionCount_Dealer].[LM_Brand] = 'TestBrand'
    GROUP BY LM_Dealer, LM_Distributor, LM_Brand

    As I have mentioned earlier the table 'LM_ImpressionCount_Dealer' has an index on the column LM_ImpressionDate. There is no index on LM_Brand as the condition may or may not appear in the query based on the selection made by the user. The table has little more than 510000 records.


    Any suggestion on the performance improvement will be really GREAT!

    Thanks in advance

    -Dex
    Last edited by DexySeeksHelp; 09-29-05 at 05:26.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in this condition --

    AND CONVERT(varchar,[LM_ImpressionCount_Dealer].[LM_ImpressionDate],102) <= CONVERT(varchar,GETDATE(),102))

    you are forcing a conversion of the date field to a string

    this means the optimizer cannot use the index on the date field

    solution? don't convert, just compare date values as date values:

    AND [LM_ImpressionCount_Dealer].[LM_ImpressionDate] <= getdate()
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Dexy

    You don't need to use dynamic SQL. Have your variables default to '%' or some extremely high\ low date (depending on the circumstance - something way outside the valid entries for the field) and instead of:

    Code:
     
    IF(@timeCriterion IS NULL OR LTRIM(RTRIM(@timeCriterion)) = '' OR LTRIM(RTRIM(@timeCriterion)) = 'NULL')
    SET @selectLeadsInPeriodString = @selectLeadsInPeriodString + 
    ' WHERE (CONVERT(varchar,[LM_ImpressionCount_Dealer].[LM_ImpressionDate],102) >= ''' + 
    ......
    try something like:

    Code:
     
    CREATE PROCEDURE [dbo].[sproc] 
     
    @VarCharVariable AS VarChar(10) = '%'
    @DateVariable AS DateTime = '1950/01/01'
     
    AS
     
    SELECT MyVarCharCol , MyDateCol
    FROM MyT
    WHERE ISNULL(MyVarCharCol, '') Like @VarCharVariable
    AND ISNULL(MyDateCol, '2050/01/01') >=@DateVariable
    This is how I tackle this sort of thing - allows a fully compiled SQL statement. No doubt I will be informed if this is folly
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, because you are still forcing unnecessary comparisons, and adding in a transformation function to boot.
    I learned the hard way that many time dynamic SQL is MUCH for efficient for complex statements with lots of optional parameters.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Sep 2005
    Posts
    10
    Thanks a lot for the suggestions guys.
    But then guess that I'll need few more.

    Actually going forward with your suggestions I made few changes in my SQL and now it looks like this :

    Code:
    SELECT 
    	LM_Dealer, LM_Distributor, LM_Brand, SUM(LM_ImpressionCount)
    FROM 
    	LM_ImpressionCount_Dealer 
    WHERE 
    	(DATEDIFF(d,dateadd(year,datediff(year,0,getdate()),0),[LM_ImpressionCount_Dealer].[LM_ImpressionDate]) >= 0
    	AND DATEDIFF(d,[LM_ImpressionCount_Dealer].[LM_ImpressionDate],GETDATE()) >= 0) 
    GROUP BY LM_Dealer, LM_Distributor, LM_Brand
    This has reduced the execution time of the query from 8 seconds to 4 seconds.

    However the problem comes when I try add one more condition in the 'where' clause. In that case the execution time of the query goes up by more than 4 times hence hitting the performance of my SP badly.

    Here is the query with an added condition for brand:
    Code:
    SELECT 
    	LM_Dealer, LM_Distributor, LM_Brand, SUM(LM_ImpressionCount)
    FROM 
    	LM_ImpressionCount_Dealer 
    WHERE 
    	(DATEDIFF(d,dateadd(year,datediff(year,0,getdate()),0),[LM_ImpressionCount_Dealer].[LM_ImpressionDate]) >= 0
          	AND DATEDIFF(d,[LM_ImpressionCount_Dealer].[LM_ImpressionDate],GETDATE()) >= 0) 
    	AND [LM_ImpressionCount_Dealer].[LM_Brand] = 'CA' 
    	GROUP BY LM_Dealer, LM_Distributor, LM_Brand
    The execution time for this query is around 18 seconds.
    I am not at all able to make out as to why there is such a increase in the execution time.

    As far as the data in the table is concerned there are only two brands 'BR' and 'CA'. Given below is the DDL of the table for your reference:

    CREATE TABLE LM_ImpressionCount_Dealer
    (
    LM_Dealer int NULL ,
    LM_Distributor int NULL ,
    LM_Brand nvarchar (100),
    LM_ImpressionCount int NULL ,
    LM_ImpressionDate datetime NULL
    )
    GO

    CREATE INDEX idx_ImpressionDate ON LM_ImpressionCount_Dealer(LM_ImpressionDate) WITH FILLFACTOR = 90
    GO

    So, can anyone please suggest a solution for this.

    Thanks
    Deepak

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your index will be ignored, the query is likely using a table scan

    for example, you have

    ... AND DATEDIFF(d,[LM_ImpressionCount_Dealer].[LM_ImpressionDate],GETDATE()) >= 0

    which is not sargable, whereas this --

    ... AND [LM_ImpressionCount_Dealer].[LM_ImpressionDate] > GETDATE()

    is

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Sep 2005
    Posts
    10
    Hey! Thanks a Lot for this piece of suggestion
    It really helped me out and the stuff is working great now.
    The execution time has reduced from over 16 secs to 2 secs.

    Thanks a lot guys.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by r937
    which is not sargable, whereas this --

    I always thought is was "non-sargable"

    I like saying that word....

    sarrrrrrrrrrrgaaaaaaaaaaaaaable
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •