SQL Server Performance

Same query - sp_executesql execution duration depends on client connection?

Discussion in 'SQL Server 2005 General Developer Questions' started by niklasr, May 4, 2010.

  1. niklasr New Member

    I have a bunch of application clients across a network connected to the same database. All clients log in as the same user. When the application starts up, certain data is fetched from DB to be cached. The query to fetch this data is excactly the same for all clients. However, the duration for fetching the data differs vastly between the clients.
    For application clients in the house I see a duration of roughly 200 ms. Clients outside the house gets durations around 21000 ms, ie 100 times slower. I'm using Profiler Trace to check execution durations, event RPC:completed. Clients outside the house use a broadband connection with 2-10 mbit/s.
    The query is of type
    exec sp_executesql N'SELECT [columnlist] FROM [table1] INNER JOIN [table2] WHERE (table2.col1 = @param)',N'@param int',@param=39
    and the query plan is the same for all clients. About 1250 rows and 20 columns are returned. One column is of type xml (each message is about 1kb), the rest are short varchars, datetimes and integers. Execution duration for each specific client does not differ very much over the day.
    I can see the same pattern in other queries, but I used this one for trouble shooting.
    To my knowledge, networking is not included in RPC:completed events. Does anybody have a theory what might affect the execution duration when query and query plan is identical?
    Any help is greatly appreciated!
  2. FrankKalis Moderator

    Welcome to the forum!
    Is your problem still current?
    How are the outside clients connecting to SQL Server? Is there a VPN and/or firewall involved?
    EDIT: Fixed a typo.
  3. niklasr New Member

    Yes, the problem remains and I think it will do so for quite some time. My theory (somewhat confirmed) is that it has to do with the way the client fetches the data. It's a C# app, using DataAdapter.Fill for populatng strongly typed datasets. This method does some chatting during fetch, and the impact is hard when on a long and narrow cable. Solution: re-write the structure of the app. Or accept.
  4. moh_hassan20 New Member

    broadband connection with 2-10 mbit/s
    the client in LAN run with speed 100 mbit/s (may 1Gb/s)
    so you should expect slow for data from server to client
    Can you try using DataReader than DataAdapter- overhead is less

Share This Page