Slow response with SQL server over a network.

5 pts.
Tags:
Java
SQL Server 2008
SQL Server administration
SQL Server installation
SQL Server performance
Hi,

We do a simple select 1 million record over a client server setup with SQL server 2008 and it take 10 times more time to get the results that doing the same request with Microsoft access db.

Exactly the same setup for the network. Any special setup in SQL server we are missing.

thank you for your help



Software/Hardware used:
sql server, java,
ASKED: April 4, 2011  5:46 PM
UPDATED: April 5, 2011  6:39 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

The amount of time that it takes to get a recordset back will basically depend on three things.

What driver you use to connect
How fast the network is that you connect over
How fast the application which is getting the recordset can process the response from the SQL Server.

If you look at the waits which are being reported while the query is running and the data is transferring I bet that you would see either network waits or the thread goes to sleep while it waits for the client to accept more data.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Darryn
    You've also got to consider how you are opening the Access db if you want to compare responses. If you are just opening an access db that is stored on a network location, then it will usually create a copy of the db on your local drive, so that when you execute your query it won't actually be fetching the data over the network.
    765 pointsBadges:
    report
  • Kccrosser
    A few things to consider: Are the tables actually identical in format? Particularly variable (or fixed!) length character fields. If a field is defined as a fixed length field in SQL, most drivers will download all the trailing blanks in the field, taking up additional network time. Verify that all fields are in fact identical in type and length (or true equivalents, since there are types that are not exactly identical). Most ODBC drivers (or other "open" client database drivers) tend to return ONE row per IP packet. If you are using a native Microsoft Access client, it may pack multiple rows in a single packet and save a lot of network round trips. (It may also pack/discard trailing blanks in transmission.) This can be particularly bad if the average data row size returned is just a little over 1300 bytes (or just over 1/2 that - 670+ bytes). Many systems are configured with an MTU size of 1400, which means a maximum data payload of about 1380 bytes in most packets. If your average row size is just over that size, each row can wind up taking two packets, with two network round-trip overheads. You can try increasing your MTU size and see if that speed up the response. (However, if elsewhere in your network, a router changes to a smaller MTU size, it can actually re-fragment, causing additional overhead.) If your average row length is just over 1/2 the available payload, the same problem occurs - one row gets sent, wasting possibly half the packet space. Network round-trips are very expensive in time. Lastly, how are you measuring the difference? Is this the time until the first "page" of results are displayed on the screen, or is this the total time to retrieve all the results to a file or other storage on the client? Access may quickly show the 1st page, while SQL Server may be retrieving ALL the results before displaying.
    3,830 pointsBadges:
    report
  • Jack2601
    [...] Mrdenny, Kccrosser, and Darryn had some ideas of why a user might be experiencing a slow response with SQL server over a network. 6. Mrdenny provided a quick and easy description of what to do to create a backup of a SQL [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following