SQL Server Performance

query performance tuning

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by ashwin, Dec 18, 2007.

  1. ashwin New Member

    Hi All,
    I want to tune the following select query:
    select
    a.TestCaseID , a.Result
    from TestResult a , TestCase b
    where TestResultId = (select max(TestResultId) from TestResult c where TestCaseID = b.TestCaseID and Result != 'Not Executed' )
    and a.TestCaseID = b.TestCaseID
    and isnull(a.Product,'') = coalesce('CommandPostWeb',isnull(a.Product,''))
    and isnull(a.Flagship,'') = coalesce(null,isnull(a.Flagship,''))
    and isnull(a.Feature,'') = coalesce(null,isnull(a.Feature,''))
    and isnull(a.protocol,'') = coalesce(null,isnull(a.protocol,''))
    and isnull(a.buildTested,'') = coalesce(null,isnull(a.buildTested,''))
    and isnull(b.priority,'') = coalesce(null,isnull(b.priority,''))
    and isnull(b.configuration,'') = coalesce(null,isnull(b.configuration,''))
    and isnull(b.type,'') = coalesce(null,isnull(b.type,''))
    and isnull(b.automation,'') = coalesce(null, isnull(b.automation,''))
    which takes about 12 to 19 minutes to display the results. But if I query
    select
    a.TestCaseID , a.Result
    from TestResult a , TestCase b
    where TestResultId = (select max(TestResultId) from TestResult c where TestCaseID = b.TestCaseID and Result != 'Not Executed' )
    and a.TestCaseID = b.TestCaseID
    and isnull(a.Product,'') = coalesce('CommandPostWeb',isnull(a.Product,''))
    and isnull(a.Flagship,'') = coalesce(null,isnull(a.Flagship,''))
    and isnull(a.Feature,'') = coalesce(null,isnull(a.Feature,''))
    and isnull(a.protocol,'') = coalesce(null,isnull(a.protocol,''))
    and isnull(a.buildTested,'') = coalesce(null,isnull(a.buildTested,''))
    --and isnull(b.priority,'') = coalesce(null,isnull(b.priority,''))
    --and isnull(b.configuration,'') = coalesce(null,isnull(b.configuration,''))
    --and isnull(b.type,'') = coalesce(null,isnull(b.type,''))
    --and isnull(b.automation,'') = coalesce(null, isnull(b.automation,''))
    the results are returned within a fraction of seconds(de selecting the last four columns). Please suggest something in this regard.

  2. ranjitjain New Member

    Hi Ashwin,
    It seems you have posted same query twice and not the one which returns you result in fraction of second. Also post your index structure on the above tables.
  3. ashwin New Member

    Hi Ranjit,
    The query which retrieved results within fraction of seconds is this:
    select
    a.TestCaseID , a.Result
    from TestResult a , TestCase b
    where TestResultId = (select max(TestResultId) from TestResult c where TestCaseID = b.TestCaseID and Result != 'Not Executed' )
    and a.TestCaseID = b.TestCaseID
    and isnull(a.Product,'') = coalesce('CommandPostWeb',isnull(a.Product,''))
    and isnull(a.Flagship,'') = coalesce('6.10',isnull(a.Flagship,''))
    and isnull(a.Feature,'') = coalesce('Builder',isnull(a.Feature,''))
    and isnull(a.protocol,'') = coalesce(null,isnull(a.protocol,''))
    and isnull(a.buildTested,'') = coalesce(null,isnull(a.buildTested,''))
    and isnull(b.priority,'') = coalesce(null,isnull(b.priority,''))
    which does not involve configuration, type and automation columns from TestCase table.
    Structure of both tables and the columns indexed are as follows:

    Structure of TestCase table

    Column Name Data Type Allow Nulls
    ===========================================
    TestCaseID numeric(18, 0) No (primary key) (clustered, unique, primary key located on PRIMARY) TestCaseID, Flagship
    StaffID int No
    Title nvarchar(1000) Yes
    Objective varchar(3000) Yes
    Assumptions varchar(4000) Yes
    Steps ntext Yes
    ExpectedResults ntext Yes
    ProdSuiteId bigint Yes
    Product nvarchar(50) Yes
    DBType nvarchar(25) Yes
    Component nvarchar(3000) Yes
    Type nvarchar(50) Yes
    OperatingSys nvarchar(50) Yes
    Server nvarchar(50) Yes
    Status nvarchar(50) Yes
    Priority nvarchar(50) Yes
    Author nvarchar(50) Yes
    CreationDate nvarchar(50) Yes
    AssignedTo nvarchar(50) Yes
    Flagship_A nvarchar(5) Yes
    Flagship nvarchar(50) No (primary key)
    Webserver nvarchar(20) Yes
    Webbrowser nvarchar(20) Yes
    AppServer nvarchar(20) Yes
    Attachment nvarchar(20) Yes
    Feature nvarchar(3000) Yes
    Modules nvarchar(3000) Yes
    SmokeTest nvarchar(50) Yes
    Protocol nvarchar(50) Yes
    Configuration nvarchar(50) Yes
    Objects nvarchar(50) Yes
    ProdReqID nvarchar(50) Yes
    UserName char(10) Yes
    Result char(14) Yes
    HostName nvarchar(30) Yes
    automation varchar(70) Yes
    Beta nvarchar(10) Yes
    GA nvarchar(10) Yes
    RequirementId nvarchar(3000) Yes
    IsDeleted int Yes
    Also iam online on y messenger my id is : ashwin_moreyin@yahoo.co.in,ashwinmorey@gmail.com. Please let me know if you have queries.



    Structure of TestResult table

    Column Name Data Type Allow Nulls
    ===========================================
    Flagship nvarchar(9) Yes
    TestCaseID numeric(10, 0) No (nonclustered located on PRIMARY) TestCaseID
    TestResultId numeric(18, 0) No (primary key) (clustered, unique, primary key located on PRIMARY) TestResultId
    TestedBy nvarchar(50) Yes
    Result nvarchar(50) Yes
    UserName nvarchar(50) Yes
    BuildTested nvarchar(25) Yes
    Defect nvarchar(50) Yes
    Comments ntext Yes
    TestEnv nvarchar(50) Yes
    CreationDate nvarchar(50) Yes
    Feature nvarchar(3000) Yes
    Product nvarchar(50) Yes
    Protocol nvarchar(50) Yes
    StartTime nvarchar(50) Yes
    EndTime nvarchar(50) Yes
    IsDeleted int No
  4. martins New Member

    Hi,
    From the query above, it seems like you are using the coalesce function incorrectly. Rather use the "is not null" operator for fields that should not be null and the "=" operator for equality checks. This should also improve performance.
  5. ashwin New Member

    Thanks Ranjit, using IS NOT NULL improved the functionality greatly. But since this query should be dynamic i.e. depending upon user input
    So can I write something like this
    select
    *
    into #test
    from Test1 a , Test2 b
    where

    if color IS NOT NULL
    isnull(a.color,'') = coalesce(@product,isnull(a.color,'') )
    else
    IS NOT NULL
    id brandname IS NOT NULL
    and isnull(a.brandname,'') = coalesce(@Flagship,isnull(a.brandname,'') )
    else
    IS NOT NULL
    if price IS NOT NULL
    and isnull(a.price,'') = coalesce(@Feature,isnull(a.price,'') )
    else
    IS NOT NULL
    Please suggest. Thanks in advance!
  6. martins New Member

    I would not use the coalesce function at all if I were you. I would write it like this: select <field_list> from <tables> where isnull(color,'') = @product
    If the variable @product can be null but it is necessary to have a value (and not blank) then I would add "...and @product is not null"
    The coalesce function returns the first non-null value. Do you want to return all records if the variables are all null?
  7. ashwin New Member

    Hi martin,
    what if @product has a null value, when user does not provide anything for @product. Can you please create a sample query and post. And also please
    explain the difference between 'coalesce' and 'isnull'. Where can I find examples on these two.
    thanks
    ashwin
  8. martins New Member

    Hi,
    The best place to find the difference between isnull and coalesce would be in "Books Online", and I would suggest that you go have a look at the examples there. The coalesce function returns the first non-null value from many given expressions, whereas isnull will return what value you specify in the case of a null.
    Lets say that @product can be null, and in the case of it being null you want to return all records. Your query should then look something like this:
    select *
    from table_a a
    where isnull(a.product,'') = isnull(@product,isnull(a.product,''))
    Using the coalesce function here would do exactly the same and would not be wrong:
    select *
    from table_a a
    where isnull(a.product,'') = coalesce(@product,a.product,'')
    It is however not a good function to use when you just want to check if a field is not null, but like you have mentioned you already changed that
  9. Adriaan New Member

    The COALESCE around a column in a WHERE clause is a bad idea because even if there is a covering index, the function around the column causes a table scan.
    It may look cumbersome, but for SQL Server the direct way of writing criteria is often the best:
    SET @product = ISNULL(@product, '')
    SELECT ................................
    WHERE ((a.product IS NULL OR a.product = '') AND @product = '') OR (a.product = @product)
  10. ashwin New Member

    you mean to say that
    and isnull(a.Test,'') = coalesce(@test,isnull(a.Test,'') )
    is equivalent to

    SET @test = ISNULL(@test, '')
    SELECT ................................
    WHERE ((a.Test IS NULL OR a.Test = '') AND @test = '') OR (a.Test = @test)
    if this is the case then I can replace it in my code. Please suggest.
  11. Adriaan New Member

    Your expression is a little bit fuzzy, since you are using an embedded ISNULL. Note that COALESCE can have any number of parameters, and it will return the first one (left-to-right) that is not null. So the expression could be simply COALESCE(@test, a.Test, '')
    You did notice that your equation has the same column on both sides, I hope? Try to avoid that sort of confusion.
  12. ashwin New Member

    But I think COALESCE does make a whole tablescan so it takes more time than ISNULL function. am I right and is this the only performance consideration?
  13. ashwin New Member

    Hi all,
    now Iam using the following query:
    select
    *
    from Test1 a , Test2 b
    where
    isnull(b.color,'') = coalesce(null,b.color,'')
    and isnull(b.brandname,'') = coalesce(null,b.brandname,'')
    and isnull(b.tag,'') = coalesce(null,b.tag,'')
    and isnull(b.price,'') = coalesce(null,b.price,'')
    which makes proper use of coalesce function, but still there is a performance degradation. Let me tell you that
    there are lot of null values in columns like 'brandname'(4560), 'tag'(123) and 'price'(101256) which might be adding to the performance degradation. Please suggest.
    thanks
  14. martins New Member

    Hi Ashwin,
    Your usage of the COALESCE function is still wrong. The COALESCE function will return the first non-null expression (in the list of expressions you supply). The following therefore does not make sense:
    and isnull(b.brandname,'') = coalesce(null,b.brandname,'')
    The "null" will never be returned, and therefore not needed. That line should rather be written as:
    and isnull(b.brandname,'') = isnull(b.brandname,'')
    It seems like you are very intent to use the COALESCE function, but it is not necessary in the above query. Only use the COALESCE function when you want to return another field when a field is null, for instance lets say if the brandname was null you wanted to return the product name, and if the product name was null you wanted to return the value 'Unknown'. You could do this either with a CASE statement, nested isnull statement or a COALESCE function:
    and isnull(b.brandname,'') = coalesce(b.brandname,b.productname,'Unkown')

    But in your query above the isnull function will do because you only want to return '' in the case of the brandname being null. The fact that you are passing a null as the first expression of your COALESCE function is already an indicator that you should not be using that function.
    Hope this helps.
  15. ashwin New Member

    thanks martin,
    I modified my query to
    select
    *
    from Test1 a , Test2 b
    where
    isnull(b.color,'') = isnull(b.color,'')
    and isnull(b.brandname,'') = isnull(b.brandname,'')
    and isnull(b.tag,'') = isnull(b.tag,'')
    and isnull(b.price,'') = isnull(b.price,'')
    but the performance issue is still there, in the previous message i told you that there are lot of null values for columns like
    brandname(4560), tag(123), price(101256) in the database. Please suggest.
  16. martins New Member

    I am still confused as to what you are trying to do with the above query. You are comparing columns in Test2 with columns in Test2...they will always be equal. Like I mentioned in my previous post, with this query you are returning the cartesian product of the 2 tables.
    Shouldn't your query read:
    select * from Test1 a, Test2 b where isnull(a.color,'') = isnull(b.color,'')...
  17. martins New Member

    Just another comment:
    The query that you posted will return all the records and is really nothing different from"
    select * from Test1, Test2
    The where-clause will not filter any data and you have no join criteria, which means it performs a cross join. That will explain bad performance.
  18. ashwin New Member

    thanks martin,
    let me explain you in detail, the original query is something like
    select
    *
    from Test1 a , Test2 b
    where
    isnull(a.user,'') = isnull('ashwin',a.user)
    and isnull(a.city,'') = isnull('california',a.city)
    and isnull(b.color,'') = isnull(null,b.color)
    and isnull(b.brandname,'') = isnull(null,b.brandname)
    and isnull(b.tag,'') = isnull(null,b.tag)
    and isnull(b.price,'') = isnull(null,b.price)
    the first two columns are from Test1 table and the remaining four are from Test2 table, the first two filter criteria are almost always filled by the user
    but the remaining ones are not (rarely filled) by the user while searching. So if we use
    select
    *
    from Test1 a , Test2 b
    where
    isnull(a.user,'') = isnull('ashwin',a.user)
    and isnull(a.city,'') = isnull('california',a.city)
    the results are retrieved within 4 seconds, but if we include the whole thing

    select
    *
    from Test1 a , Test2 b
    where
    isnull(a.user,'') = isnull('ashwin',a.user)
    and isnull(a.city,'') = isnull('california',a.city)
    and isnull(b.color,'') = isnull(null,b.color)
    and isnull(b.brandname,'') = isnull(null,b.brandname)
    and isnull(b.tag,'') = isnull(null,b.tag)
    and isnull(b.price,'') = isnull(null,b.price)
    same results are retrieved but now takes 35 seconds, thanks for your comments on the issue (which was taking 12 to 19 mins previously).
    but I still need to improve the performance. I think the left part 'and isnull(b.price,'')' is incorrect now, also there are null values in these columns.
    Please suggest. Thanks in advance.
  19. martins New Member

    Firstly you need some join criteria between the two tables. You cannot do anything about the fact that there might be nulls in the table, so you should try and improve performance with the correct indexes.
    How big are these 2 tables and are there any indexes?

Share This Page