none
Performance tuning Logical read RRS feed

  • Pergunta

  • Hi All, I have a search query which utilizes 2 parameters to give search result back. I have index on both parameters. I want to bring it down logic read from higher number (15K) to lower number part of performance tuning. This query is getting executed through UI. There is 1 more index on a table with these 2 search parameter columns and including all select columns.

    declare @col6 = ''
    declare @col7='123' 
    select top(1), col1, col2, col3 , col4, col5, col6, col7 
    from tableA 
    where ((col6= @col6) or @col6 is null) or 
          ((col7= @col7) or @col7 is null)

    Things I have tried so far but no luck.

    where ((@col6 in ( col6, null)) or ( @col7 in (col7, null)))
    Please help !

    segunda-feira, 9 de dezembro de 2019 05:19

Todas as Respostas

  • What is the problem? Bad performance? Read Erland's article 

    http://www.sommarskog.se/dyn-search-2008.html#sp_executesql

    PS Perhaps  you need to change OR  operator

    to AND ? 

    where ((col6= @col6 or @col6 is null)AND
          (col7= @col7) or @col7 is null))


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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    segunda-feira, 9 de dezembro de 2019 05:28
    Usuário que responde
  • yes, it takes time to give the result back. Unfortunately I can not use AND operator as value can belong to col6 or col7.

    segunda-feira, 9 de dezembro de 2019 05:32
  • How about:

    declare @col6 = ''
    declare @col7='123' 
    select top(1), col1, col2, col3 , col4, col5, col6, col7 
    from tableA 
    where coalesce(@col6,@col7) is not null
    and (col6 = @col6 or col7 = @col7)

    segunda-feira, 9 de dezembro de 2019 05:32
  • Try union all clause

    declare @col6 = ''
    declare @col7='123' 

    select top(1), col1, col2, col3 , col4, col5, col6, col7 
    from tableA 
    where ((col6= @col6) or @col6 is null) 
    union all
    select top(1), col1, col2, col3 , col4, col5, col6, col7 
    from tableA  where
          ((col7= @col7) or @col7 is null)


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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    segunda-feira, 9 de dezembro de 2019 05:35
    Usuário que responde
  • Thank you @Uri and @Soumen , I tried both suggestions and still no luck , it increases the logical counts
    segunda-feira, 9 de dezembro de 2019 05:54
  • Can you post an execution plan of the query?

    I hope  you  have indexes on col6 and col7? I would also try to include all columns you specify in SELECT  means  

    create index my_id on tbl(col6) include (col1,col2....)


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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    segunda-feira, 9 de dezembro de 2019 05:59
    Usuário que responde
  • ok, can you try this:

    declare @col6 = ''
    declare @col7='123' 
    select top(1), col1, col2, col3 , col4, col5, col6, col7 
    from tableA 
    where coalesce(nullif(@col6,''),nullif(@col7,'')) is not null
    and (col6= @col6 or col7 = @col7)



    segunda-feira, 9 de dezembro de 2019 06:01
  • Hi rommy08, 

    Could you please share us your execution plan?

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    terça-feira, 10 de dezembro de 2019 07:45
  • Your query says to take the TOP (1) and does not have an ORDER BY.  When you do that and more than one row meets the WHERE condition, SQL will return one of the rows which meets the condition, your query will return one of those rows, but you have no control over which row is returned.  I'm worried that this is not what you actually want your query to do.  For example, if @col6 is NULL and @col7 = 9, and your table has two rows, the first row has col6=5 and col7=2 and the second row has col6=5 and col7=9, your query might return the row with col6=5 or the row with col7=9.  That's because the WHERE condition (where @col6 is null or @col6=col6 or @col7 is null or @col7=col7) is true for every row in the table.  If that is what you want your query to do, then that's great, but if you want the query to return the row where col7=9 because @col7=9, then we would want to correct the query and then attempt to optimize it.

    But if you really want the query you have shown us, optimizing it should be easy by splitting it into three queries. 

    if @col6 is null or @col7 is null
      begin
        /* get any row, we don't care which one */
        select top(1) col1, col2, col3, col4, col5, col6, col7
        from tableA
      end
    else if exists(select * from tableA where col6 = @col6)
      begin
        /* get one of those rows, we don't care which one */
        select top(1) col1, col2, col3, col4, col5, col6, col7
        from tableA
        where col6 = @col6
      end
    else
      begin
        /* if a row matches @col7, get one of them
          if there are no matches, return an empty result  */
        select top(1) col1, col2, col3, col4, col5, col6, col7
        from tableA
        where col7 = @col7
      end
    

    The above will do very few logical reads because the first query just grabs the first row it finds, and the second and third queries use the indexes to go directly to a row you want.

    Tom

    quarta-feira, 11 de dezembro de 2019 04:48
  • sorry for delayed response , execution plan is like index scan on col6 and col7  , then key ID look up for col1 ,which is clustered index , nested join and select , cost is more toward Index scan. 

    I will try your solution Tom and will post results here.  However functioanlity wise I wont be able to try below part of query as it would return any row and we do not want that. Table has value stored NULL for col6 and col7 , we want to return it if it does not match.

     /* get any row, we don't care which one */
        select top(1) col1, col2, col3, col4, col5, col6, col7
        from tableA


    • Editado rommy08 quinta-feira, 12 de dezembro de 2019 13:55
    quinta-feira, 12 de dezembro de 2019 13:53
  • Hi rommy08,

    Thank you for your reply .

    Did you have many columns in your table ? If you have , maybe you can create a index with INCLUDE, just like following script. It might help you a lot . 

    ALTER NONCLUSTERED INDEX ix4_exampleindex  ON dbo.mytable (col6,col7)  INCLUDE (col1,col2,col3,col4,col5);  
    

    If not , please try to create CLUSTERED INDEX on col6,col7 . It might be helpful.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    sexta-feira, 13 de dezembro de 2019 07:58
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    terça-feira, 17 de dezembro de 2019 08:48