[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Slow query between linked servers

Posted on 2011-10-31
5
Medium Priority
?
246 Views
Last Modified: 2012-05-12
In my test environment I have a query that crosses two linked servers. They are both under that same instance of SQL 2005. When I run the query in the test environment, it returns results in about 10 seconds. When I deployed it into production, the exact same query takes 2 1/2 minutes. The biggest difference I see is that in production one database is SQL 2005 and the other is SQL 2000. There are separate instances for both on the same server. Could this be the reason for the slow response time and is there anything, short of dumping SQL 2000, to help speed this up?
0
Comment
Question by:rwheeler23
  • 2
  • 2
5 Comments
 
LVL 5

Assisted Solution

by:bitref
bitref earned 400 total points
ID: 37059307
Compare the properties of the linked servers in both servers; Test and Production. Then, try to apply those of Test on Production.
0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 1600 total points
ID: 37059334
For openers, if you haveen't already migrated off of SQL Server 2000 in your production environment, you seriously need to . . . especially since your test environment is already on SQL Server 2005!  

Short Answwer: Short of migrating the SQL Server 2000 database to SQL Server 2005, you are probably stuck with the timing, unless you can improve the query's overall performance (but, from the test environment time, I would have to think that doesn't offer much hope).

Longer answer:
Yes, the fact that you are linking two separate instances, much less a SQL Server 2000 and a SQL Server 2005 instance, is conributing to the slow-down.  The fact that you have the two databases under one instance on your Test Server and it performs well only shows you that, unless the Test environment matches the Production environment, you aren't really performing a valid test. ;-)  Try bringing your Test Environment into alignment with your Production environment (either by adding a SQL Server 2000 instance to your Test Environment and putting the linked database on it or putting both of your Production databases in the same SQL Server 2005 instance) and you will be better able to compare the performances. ;-)


0
 

Author Comment

by:rwheeler23
ID: 37059417
My problem is that I am being forced to pull data out of the SQL 2000 instance into SQL 2005 where 95% of the data resides. This issue gives me just one more reason to dump SQL 2000. I think the real reason is money. They have a 35 user SQL 2000 license but only a 5 user SQL 2005 license. Personally I would prefer to bring both of them up to SQL 2008 R2 but they have an old legacy application attached to the SQL 2000 database and they have not been willing to invest the time to upgrade that application to at least SQL 2005. My role is simply to advise. As long as they insist on the old SQL 2000 they will need to live with slow response times. One other thought I had was to create an SSIS job that runs at night that will pull the data out of SQL 2000 and into SQL 2005. I only need data in 4 tables. The rest of the tables are meaningless to me and the data does not change that often where this would be a problem. What do you think?
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 1600 total points
ID: 37059472
I started to suggest extracvting the needed data into your SQL Server 2005 world rather than joining across the linkage.  Depending on the number of rows you would bneed to extract and the number of columns, you might be able to do the extraction a couple of times a day and satisfy the needs of whatever you are doing.  Using SSIS 2005 to do the extraction would take a little time but, depending on the frequency of use of the particular query in question, it could be a good trade-off . . . especially if it could be done in off-hours.
0
 

Author Comment

by:rwheeler23
ID: 37060172
We are talking an insignificant amount of records. They are PO records with maybe 2,500 header readers and 10,000 detail records. A simple SSIS job running at 3am will have the records in place for the next day. I may even give them a button where if they the data immediately they just push the button. When I ran SQL Profiler trying to see if I could what was taking so long, you see absolutely nothing for over 3 minutes, then all of a sudden the rpc starts and completes in less than 5 seconds.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question