Linked Server Performance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Linked Server Performance

Author

Topic

ccrespo
Yak Posting Veteran

59 Posts

Posted - 2007-09-11 : 13:06:22

Has anyone else encountered this:
I have a linked server on SQL 2000. The following query does a remote query that has an estimated row count of 377:

SELECT * FROM [WMG-MILLBROOK].PracticeManager.dbo.Appointments
WHERE 	ApptKind = 1 AND 
	ApptStatusMId IN (305,10326) AND
	OwnerId IN (SELECT DISTINCT PatientProfileId FROM RECALL) 
	--AND ApptStart >= getdate()

Now when I add in the Date filter on the end the remote query row count goes up to 1,849,427.

SELECT * FROM [WMG-MILLBROOK].PracticeManager.dbo.Appointments
WHERE 	ApptKind = 1 AND 
	ApptStatusMId IN (305,10326) AND
	OwnerId IN (SELECT DISTINCT PatientProfileId FROM RECALL) 
	AND ApptStart >= getdate()

Am I missing something here?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-11 : 13:21:46

is this 2005 then a linkedserver to 2000?

in 2005, there was a problem with linked server and sp2 solved that...

otherwise ignore this reply

--------------------
keeping it simple...

ccrespo
Yak Posting Veteran

59 Posts

Posted - 2007-09-11 : 14:38:09

It's 2000 to a 2000 SQL Server. We currently don't use 2005 yet.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-11 : 16:09:24

if you re-write using a join, do you get consistent row count?
amuse me and try

--------------------
keeping it simple...

ccrespo
Yak Posting Veteran

59 Posts

Posted - 2007-09-12 : 07:36:47

Following Has a Row Count of 1,850,617:

SELECT a.* 
FROM [WMG-MILLBROOK].PracticeManager.dbo.Appointments a
JOIN Recall r on r.PatientProfileId=a.OwnerId
WHERE 	a.ApptKind = 1 AND 
	a.ApptStatusMId IN (305,10326)
	AND a.ApptStart >= getdate()

The Next One has a row count of 384:

SELECT a.* 
FROM [WMG-MILLBROOK].PracticeManager.dbo.Appointments a
JOIN Recall r on r.PatientProfileId=a.OwnerId
WHERE 	a.ApptKind = 1 AND 
	a.ApptStatusMId IN (305,10326)
	--AND a.ApptStart >= getdate()

Kristen
Test

22859 Posts

Posted - 2007-09-12 : 14:34:57

Is [WMG-MILLBROOK].PracticeManager.dbo.Appointments a VIEW?

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-09-12 : 15:40:16

can you paste in the execution plans for both queries? that might give us a clue as to what is going on.

-ec

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-12 : 15:52:09

comment out

AND a.ApptStatusMId IN (305,10326)

and run your query again

--------------------
keeping it simple...

ccrespo
Yak Posting Veteran

59 Posts

Posted - 2007-09-13 : 14:01:59

[code]StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SET SHOWPLAN_TEXT ON

SELECT * FROM [WMG-MILLBROOK].PracticeManager.dbo.Appointments
WHERE ApptKind = 1 AND
Canceled IS NULL
--ApptStatusMId IN (305,10326)
AND OwnerId IN (SELECT DISTINCT PatientProfileId FROM RECALL)
--AND ApptStart >= getdate()

(2 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Left Semi Join, WHERE:([WMG-MILLBROOK].[PracticeManager].[dbo].[Appointments].[OwnerId]=[Recall].[PatientProfileId]))
|--Remote Query(SOURCE:(WMG-MILLBROOK), QUERY:(SELECT Tbl1001."AppointmentsId" Col1005,Tbl1001."FacilityId" Col1006,Tbl1001."ApptKind" Col1007,Tbl1001."OwnerId" Col1008,Tbl1001."ApptStart" Col1009,Tbl1001."ApptStop" Col1010,Tbl1001."Status" Col1011,
|--Clustered Index Scan(OBJECT:([WMGRecallSystem].[dbo].[Recall].[PK__Recall__2D27B809]))

(3 row(s) affected)[/code]And heres with ApptStart >= getdate()[code]StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SET SHOWPLAN_TEXT ON

SELECT * FROM [WMG-MILLBROOK].PracticeManager.dbo.Appointments
WHERE ApptKind = 1 AND
Canceled IS NULL
--ApptStatusMId IN (305,10326)
AND OwnerId IN (SELECT DISTINCT PatientProfileId FROM RECALL)
AND ApptStart >= getdate()

(2 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Left Semi Join, WHERE:([WMG-MILLBROOK].[PracticeManager].[dbo].[Appointments].[OwnerId]=[Recall].[PatientProfileId]))
|--Filter(WHERE:(([WMG-MILLBROOK].[PracticeManager].[dbo].[Appointments].[ApptKind]=1 AND [WMG-MILLBROOK].[PracticeManager].[dbo].[Appointments].[Canceled]=NULL) AND [WMG-MILLBROOK].[PracticeManager].[dbo].[Appointments].[ApptStart]>=getdate()))
| |--Remote Query(SOURCE:(WMG-MILLBROOK), QUERY:(SELECT Tbl1001."AppointmentsId" Col1039,Tbl1001."FacilityId" Col1040,Tbl1001."ApptKind" Col1041,Tbl1001."OwnerId" Col1042,Tbl1001."ApptStart" Col1043,Tbl1001."ApptStop" Col1044,Tbl1001."Status" Col
|--Clustered Index Scan(OBJECT:([WMGRecallSystem].[dbo].[Recall].[PK__Recall__2D27B809]))

(4 row(s) affected)[/code]

Kristen
Test

22859 Posts

Posted - 2007-09-13 : 14:10:22

Well that's a pile of Pooh! isn't it.

Pull all the data locally, and then compare it against GetDate()

Might be worth putting GetDate() in an @Variable and trying that, to see if it gets executed at the remote.

Otherwise use OPENQUERY instead.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources