April 21, 2017 at 7:07 pm
Hi Team,
I need to optimize a query, Please let me know how to fix this code, the DB size is 300GB and the table is huge and taking forever and the user is killing this job from prod (I don't know who wrote this but I have to fix it)
1 table and 3 views
Select
test1, test2, test3,
dbo.mytest tab TB,
View1 V1,
view2 V2
view3 V3
where enddate > getdate - 180
and v1.idv1 = TB.tbid
and v2.idcard = TB.idcard
and v3.hid = TB.hid
and domainname like '%myname%' -- From view1
and cardID = 700 -- mytesttable
Order by startdate -- mytesttab
I'm really bad with joins and there is no test environment to test my logic so please help
Thanks
April 21, 2017 at 9:50 pm
first you'd have to post the definitions of views 1, 2, and 3.
April 21, 2017 at 10:12 pm
I agree with pietlinden - posting the definition of those views would help identify where the bottleneck is. You may also want to post the estimated query plan for this query if that is at all possible.
You may also need to re-write your example query to have valid syntax so it's clear what the query is doing - something like the below would be a help. Note the FROM clause, explicit declaration of which table each referenced column is in (having this in comments, rather than the code would be very strange in production code), and standard joins (rather than the old-style joins where the relevant columns are matched in the WHERE clause).
select
V1.test1,
V2.test2,
V3.test3
from mytesttable as TB
inner join View1 as V1
on v1.idv1 = TB.tbid
inner join view2 as V2
on v2.idcard = TB.idcard
inner join view3 as V3
on v3.hid = TB.hid
where TB.enddate > dateadd(day, -180, getdate())
and V1.domainname like '%myname%'
and TB.cardID = 700
order by TB.startdate
Andrew
April 22, 2017 at 10:36 am
Also that domain filter, with the leading percent, will prevent the optimizer from getting an accurate statistical estimate about row count for the predicate, thus the entire query could be MASSIVELY suboptimal because of that. Often the only solution for that is to put the matching keys in a temp table (not table variable!). If it is "words" that are being searched the Full Text Search could help.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 22, 2017 at 10:44 am
pietlinden - Friday, April 21, 2017 9:50 PMfirst you'd have to post the definitions of views 1, 2, and 3.
Also please post the definitions of all tables involved and their indexes, and the actual execution plan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply