Optimise a query that uses a linked server

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 2005 Forums

Transact-SQL (2005)

Optimise a query that uses a linked server

Author

Topic

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-09-23 : 05:25:39

This query retrieves the number of minutes each teacher spends teaching each subject. The data is sourced from another linked server over a VPN, however it takes about 9 seconds to do it. Is there a way to optimise the query? Or a way to improve the performance of the linked server database to speed up and use less resources? tblTblSessions has about 100,000 records in it, and tblTblSubjects about 800.

This query is part of a view definition. This view is then used in a second view to retrieve the required result set.

SELECT DISTINCT TOP (100) PERCENT ST2.RMET_HR_PERSONKEY,
ST2.First_Name, ST2.Surname, SU2.Subject_Name As SubjectName,
SUM(DateDiff(MI,SE2.start_time, SE2.End_Time)) as TeachingMinutes
FROM [db-fmc].db_BE_Freeman.dbo.tblStaDetails AS ST2
INNER JOIN [db-fmc].db_BE_Freeman.dbo.tblTblSessions AS SE2
ON SE2.Session_Leader_ID = ST2.Staff_ID
LEFT OUTER JOIN [db-fmc].db_BE_Freeman.dbo.tblTblSubjects AS SU2
ON SU2.Subject_ID = SE2.Subject_ID
WHERE SE2.Date >= '2008-09-01 00:00:00.000'
Group By ST2.RMET_HR_PERSONKEY, ST2.First_Name, ST2.Surname, SU2.Subject_Name

thanks for your help

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-09-23 : 07:03:16

Distributed queries like this has a tendency to take time...have you run the query directly on the linked server? How long does it take there?

- Lumbago

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-09-23 : 09:39:43

thank you - I will try this tomorrow as I have no access today (sorry)

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-09-24 : 06:52:16

To run the query takes less than a second on the linked server. To run the identical query but on the home server takes 7 seconds. When I then link in other fields from the database on the home server the query takes 14 seconds.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-09-24 : 07:00:44

Hmmm...ok. I have to admit I'm not extremely experienced with linked servers but can you try to make the query in to a view on the linked server and then just run SELECT * FROM db-fmc.db_BE_Freeman.dbo.MyNewView...?

- Lumbago

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-09-24 : 07:19:29

..or a stored procedure or a table valued function if you need to pass parameters...?

- Lumbago

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-09-25 : 06:11:39

Hi - thank you very much for your thoughts. I think what I am going to do is run a stored procedure that creates a temp table locally and use this temp table for the report. The report is run infrequently and I think this will improve the performance dramatically. Again thank you for your time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources