Query Improvement

  • Is there a way to write this query in better way

    declare @listofids varchar(max)

    select @listofids = 'abc1001|abc1002|abc1005|abc1090|bcd1020|bcd1120'

    select t1.col1,t1.col1,t1.col3,t1.col4,t1.col5,

    (select sum((case when @listofids<>replace(@listofids,t2.col20,'') then col15*10 else col15 end)

    from table1 t1

    inner join table2 t2

    on t1.col1=t2.col1 and t1.col5=t2.col5

    inner join table3 t3

    on t2.col3=t3.col3 and t1.col8=t3.col8

    inner join table4 t4

    on t3.col6=t4.col6

    and substring(t2.col10,charindex('||',t2.col10)+2,len(t2.col10))=t4.col10

    where t2.col12 in(select distinct col12 from table5 where col1=t2.col1)

    group by t1.col1,t1.col1,t1.col3,t1.col4,t1.col5

  • sqlenthu 89358 (8/4/2016)


    Is there a way to write this query in better way

    declare @listofids varchar(max)

    select @listofids = 'abc1001|abc1002|abc1005|abc1090|bcd1020|bcd1120'

    select t1.col1,t1.col1,t1.col3,t1.col4,t1.col5,

    (select sum((case when @listofids<>replace(@listofids,t2.col20,'') then col15*10 else col15 end)

    from table1 t1

    inner join table2 t2

    on t1.col1=t2.col1 and t1.col5=t2.col5

    inner join table3 t3

    on t2.col3=t3.col3 and t1.col8=t3.col8

    inner join table4 t4

    on t3.col6=t4.col6

    and substring(t2.col10,charindex('||',t2.col10)+2,len(t2.col10))=t4.col10

    where t2.col12 in(select distinct col12 from table5 where col1=t2.col1)

    group by t1.col1,t1.col1,t1.col3,t1.col4,t1.col5

    First question, what's wrong with the query now?

  • Mainly I see the substring condition in the where clause and the aggregate function in select condition using case statement. The query takes little more time than what I am expecting.

  • For accurate replies, post DDL and sample data. For performance problems, follow this article to know what we need: http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    This has small changes that might not be relevant, but I consider them better practices.

    select t1.col1,

    t1.col1,

    t1.col3,

    t1.col4,

    t1.col5,

    (select sum((case when '|' + @listofids + '|' LIKE '%|' + t2.col20 + '|%' then col15*10 else col15 end)

    from table1 t1

    inner join table2 t2 on t1.col1=t2.col1 and t1.col5=t2.col5

    inner join table3 t3 on t2.col3=t3.col3 and t1.col8=t3.col8

    inner join table4 t4 on t3.col6=t4.col6 and substring(t2.col10,charindex('||',t2.col10)+2,len(t2.col10))=t4.col10

    where t2.col12 in(select col12 from table5 where col1=t2.col1)

    group by t1.col1,t1.col1,t1.col3,t1.col4,t1.col5;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • sqlenthu 89358 (8/4/2016)


    Mainly I see the substring condition in the where clause and the aggregate function in select condition using case statement. The query takes little more time than what I am expecting.

    For the substring condition, you might need to add a persisted computed column and then use it on an index.

    The case statement shouldn't affect performance.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • The number of brackets open is 2 more than the number of brackets closed.

    Am I missing something?

    _____________
    Code for TallyGenerator

  • That is typo from my end. Everything is fine syntax wise.

    Actually this query shows RID lookup with a seek predicate on table table2. This RID lookup takes 98 % of processing. I can create covered index as per my knowledge to tackle this. But are covered indexes good practice in OLTP environment ?

  • sqlenthu 89358 (8/4/2016)


    That is typo from my end. Everything is fine syntax wise.

    So how the correct version would look like?

    Like this?

    select t1.col1,t1.col1,t1.col3,t1.col4,t1.col5,

    sum(case when @listofids<>replace(@listofids,t2.col20,'') then col15*10 else col15 end)

    from table1 t1

    ...

    _____________
    Code for TallyGenerator

  • sqlenthu 89358 (8/4/2016)


    Actually this query shows RID lookup with a seek predicate on table table2. This RID lookup takes 98 % of processing. I can create covered index as per my knowledge to tackle this. But are covered indexes good practice in OLTP environment ?

    I'd suggest starting from looking at clustered index on that table.

    But you need to consider not only SELECTs, but also INSERTs and UPDATEs when choosing correct clustered index.

    There are always trade-offs.

    And this piece also looks not right to me:

    where t2.col12 in(select col12 from table5 where col1=t2.col1)

    _____________
    Code for TallyGenerator

  • HI Sergiey, These tables get populated every few mins. Though there are no updates and deletes. Will that be impacted if clustered indexes are built on them ?

  • sqlenthu 89358 (8/8/2016)


    HI Sergiey, These tables get populated every few mins. Though there are no updates and deletes. Will that be impacted if clustered indexes are built on them ?

    The impact will happen every few mins then. 🙂

    You can minimise the impact by choosing the columns for clustered indexes right.

    But any index is a data repository, which takes time and resources to get populated.

    Just like in a book.

    Clustered index is your "table of content".

    If you re-write your book, you need to spend time (and ink) on re-writing the table of content.

    _____________
    Code for TallyGenerator

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply