August 4, 2016 at 1:41 pm
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
August 4, 2016 at 1:45 pm
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?
August 4, 2016 at 2:02 pm
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.
August 4, 2016 at 2:02 pm
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;
August 4, 2016 at 2:11 pm
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.
August 4, 2016 at 6:41 pm
The number of brackets open is 2 more than the number of brackets closed.
Am I missing something?
_____________
Code for TallyGenerator
August 4, 2016 at 9:00 pm
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 ?
August 4, 2016 at 11:50 pm
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
August 5, 2016 at 5:08 am
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
August 8, 2016 at 2:49 am
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 ?
August 8, 2016 at 9:53 pm
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