Data Pulling through Link 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

General SQL Server Forums

New to SQL Server Programming

Data Pulling through Link Server

Author

Topic

niraj
Starting Member

2 Posts

Posted - 2009-08-28 : 06:31:04

hi all
I am useng link server in a Store procedure for pulling the data every day . i store procedure i am either truncating and Deleting last 2 days data and pulling it to my sql server 2005 database through link server ,In Store procedure I used these things at the very start

SET NOCOUNT ON
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

still it takes so much time to pull the data the maximum row that i am fetching every day is around 20,00,000

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-08-28 : 07:19:46

Remove the DBCC commands from your stored procedure. Every time it runs those will force a recompile. If that doesn't improve the performance you'll have to post the rest of the code.

Never use those commands INSIDE a procedure. They should only be used BEFORE running a procedure, and only when benchmarking performance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources