May 26, 2011 at 8:48 am
I have the following query
SELECT distinct pt.ptno,t.suspense
From lnksvr1.db1.dbo.Table1 pt Inner Join [271_Ineligible] t on pt.ptno = t.ptno
Where t.uploaded=0 AND t.applied = '2' AND t.hcode in (350, 351 , 352)
AND t.category in (1,2)
AND pt.PTNO not in (SELECT ptno
FROM lnksvr1.db1.dbo.Comments c
WHERE c.ptno = t.ptno
AND left(c.comment_code, 3) = '220'
AND t.hcode in (350, 351 , 352) )
**lnksvr1 is the linked server
There are 1572367 records in the 271_Ineligible table and to return 403 rows it takes about 2 1/2 minutes. Is it too slow or pretty normal?
I have checked the average fragmantation of indexes on these tables and all are below 30%
Can you please help me to see how I can speed up the query? Any help would be appreciated.
May 26, 2011 at 8:53 am
Please post the actual execution plan.
Also have you tried reversing the query to see if it runs faster from the remote server? It might be faster to get the results from there and push them to the 2nd server.
May 26, 2011 at 9:09 am
May 26, 2011 at 9:11 am
Not sure if I uploaded the image right...
May 26, 2011 at 9:20 am
Not a picture of the execution plan. The execution plan itself. See http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
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
May 26, 2011 at 9:33 am
Sorry, will have to wait until tomorrow's process because the data got updated by another process, hence will not return the same result. I will post the execution plan tomorow morning as we will have another batch of data coming in that table tonight. Thank you for your assistance.
May 26, 2011 at 9:46 am
I don't know about your indexs, but applying a function to a column makes it so that predicate is not SARGable.
You might try chaning:
AND left(c.comment_code, 3) = '220'
to something like:
AND c.comment_code LIKE '220%'
May 26, 2011 at 11:28 am
I could have done hat but the records in the comments a table are as follows
220- We receieved the data
219 - Patient match found
218 - This is only going to be hard 220 days left
blah -blah..blah...blah....
I need to select only those rows where it starts with 220. I do not want the 3rd row to be selected. The '%Like' would select the third row as well.
Thank you for replying.
May 26, 2011 at 11:31 am
msharma-393915 (5/26/2011)
I could have done hat but the records in the comments a table are as follows220- We receieved the data
219 - Patient match found
218 - This is only going to be hard 220 days left
blah -blah..blah...blah....
I need to select only those rows where it starts with 220. I do not want the 3rd row to be selected. The '%Like' would select the third row as well.
Thank you for replying.
He wrote LIKE '220%' how would 218 be returned by that filter??
May 26, 2011 at 12:22 pm
Try Ninja's suggestion and run it against the linked server itself, and you can also try it using OPENQUERY
For instance:
SELECT
DISTINCT pt.ptno,t.suspense
FROM OPENQUERY(lnksvr1, '
SELECT pt.ptno,t.suspense
FROM
db1.dbo.Table1 pt
INNER JOIN db1.dbo.[271_Ineligible] t ON
pt.ptno = t.ptno
WHERE
t.uploaded = 0
AND t.applied = ''2''
AND t.hcode IN (350, 351, 352)
AND t.category IN (1,2)
AND pt.PTNO NOT IN (
SELECT ptno
FROM db1.dbo.Comments c
WHERE c.ptno = t.ptno
AND left(c.comment_code, 3) = ''220''
AND t.hcode IN (350, 351, 352)) ')
If that doesn't save you much time you could try pulling two seperate queries using the OPENQUERY and inserting those result-sets into 2 seperate temporary tables, then SELECTing the DISTINCT values from them with a simple JOIN or something.
Just throwing something out there
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
May 26, 2011 at 12:34 pm
@Ninja : My bad ..overlooked where the '%' was ..yes will give it a try. Thanks
@mydoggiejessie : Definitely will give it a shot.
May 26, 2011 at 12:35 pm
@Ninja : My bad ..overlooked where the '%' was ..yes will give it a try. Thanks
@mydoggiejessie : Definitely will give it a shot.
June 3, 2011 at 10:32 am
Can you please review my query ?
This is what I am trying to do.
I have a TABLE1 and TABLE3 on svr1 and TABLE2 on svr2. Now I am trying to do a update on suspense fiield of srvr1.TABLE1 with a join on srvr2.Table2 and ptno not in svr1.Table3 - I am trying to achieve this by the use of openquery as suggested above
--Running this query on Srvr2
Create #tmpInEligible1(ptno int)
SELECT distinct ptno FROM
OPENQUERY(lnksrv1,'SELECT t1.ptno FROM db1.dbo.TABLE1 t1
INNER JOIN lnksrvr2.db2.dbo.TABLE2 t2
on t1.ptno = t2.ptno
WHERE t2.uploaded=0 AND t2.applied = 2 and t1.hosp in (1,2,3)
AND t2.category in (1,2)' )
CREATE #tmpInEligible2(ptno int)
SELECT distinct ptno FROM
OPENQUERY(lnksrv1,'SELECT c.ptno FROM db1.dbo.TABLE3 t3
INNER JOIN lnksrvr2.db2.dbo.TABLE2 t2
ON t3.ptno = t2.ptno
WHERE t2.uploaded=0 AND t2.applied = 2 and t2.hosp in (1,2,3)
AND t2.category in (1,2)
AND t3.comment_code like ''220%'''
)
UPDATE p
SET t1.suspense = tmp1.suspense
FROM
OPENQUERY(lnksrv1,'SELECT suspense FROM db1.dbo.Table1 ') t1
INNER JOIN #tmpInEligible1 tmp1
ON t1.ptno = tmp1.ptno
WHERE not exists (SELECT ptno FROM #tmpInEligible2)
DROP table #tmpInEligible1
DROP table #tmpInEligible2
Thanks a lot for the help!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply