Post reply

Remote Query Speed Issues:

  • December 10, 2012 at 8:48 am

    #393773

    Any query run against the server remotely takes a ridiculous amount of time and I am not sure where to look. I was hoping to get some starting points.

    The query I am testing with is very simple, it looks much like my example:

    Select Field1, Field2, Field3, Field4, Field5

    from Table

    where Field1 = 150

    The entire table has around 200k entries, the result of the query is around 50k. If I run it locally it takes around 1 to 2 seconds to complete. If I run in remotely, either from a different server or Crystal, it takes nearly 15 minutes.

    Any tips on what I can do to solve this problem would be very appreciated.

  • December 10, 2012 at 9:11 am

    #1567084

    By remote, do you mean you query through a Linked Server?

    There is an exception to every rule, except this one...

  • December 10, 2012 at 9:40 am

    #1567093

    I think SQLHeap has got the core issue. linked servers have performance issues you need to be aware of.

    try these two things on the server witht eh linked server on it.

    this coding style requires all the rows to be copied to the local temp database, then the filter applied from the WHERE statement

    Select Field1, Field2, Field3, Field4, Field5 from MyLinkedServer.Databasename.dbo.Table where Field1 = 150'

    this coding style performs the work on the remote server, and only returns the desired results

    SELECT * FROM OPENQUERY( [MyLinkedServer],'SET FMTONLY OFF; Select Field1, Field2, Field3, Field4, Field5 from Table where Field1 = 150')

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 10, 2012 at 9:41 am

    #1567095

    Remote meaning, the query is not being generated on the server the database is hosted.

    So, for example using SQL Server Manager on my local machine to connect to sqlserverhost\sqlserver

    Edit: The remote connections are not Linked Servers

  • December 10, 2012 at 10:02 am

    #1567105

    in that case, baring slow network issues, i'd think the first thing is to look at the execution plan, and the real query;

    anything from parameter sniffing, out of date statistics, missing indexes, local ansi/connection settings might be affecting the query.

    can you post the actual exection plan as a .sqlplan attachment here so we can help?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 10, 2012 at 10:16 am

    #1567110

    I have attached the Execution Plan.

    Edit: Additional information that may be relevant:

    The database that I am attempting to run these Queries on is our Backup/Reporting database. I have two physical servers one is for production the other for backup. I use transcriptional replication from the primary to the secondary. If I run the query against the production server, it is instant.

    BTW, Thank you.

  • December 10, 2012 at 11:57 am

    #1567134

    I'd be interested in knowing how well that query runs against the Reporting/Backup server from Management Studio, as I suspect that what Lowell posted about using OPENQUERY might help. Without it, the entire table being SELECTed from the REMOTE server has to travel the network and be filtered by the server that is considered "LOCAL", before the query results are presented. Using OPENQUERY solves this problem by forcing the REMOTE server to process the query instead of the LOCAL one. Let us know if this makes a difference, or if we're on the wrong page entirely...

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • December 10, 2012 at 12:02 pm

    #1567136

    OOps... just saw the post about connections NOT being Linked Servers. But remembering your mention of Crystal Reports, it does a rather similar thing, so I try to avoid giving Crystal the opportunity and I write parameterized stored procedures for Crystal to execute, and that denies Crystal the opportunity to handle the WHERE clause on it's own.

    Let me know if that helps...

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • December 10, 2012 at 12:03 pm

    #1567137

    Alex, you posted the estimated execution plan, which shows a nice expected clustered index scan; i think we need the actual execution plan, where it's being returned slowly via SSMS query so we can actually see the issue better.

    can you try and post that as well?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply