Problem with querry performance locally vs. network

Hi,

I work on speed-up our company SQL Server 2000. I reconstructed one querry and test it on client computer (other than server). There was no profit. I was surprised :(. I supposed several times speed-up. Next I run old and new querry directly on our SQL Server machine. The result was proper - new (after modifications) querry takes 2,5 times less time than previous.

Ok, question: why there wasn't profit of modification from client computer?

I thougth that reason is slow network. So I copy from client computer file of weight 80MB to server. This takes 10seconds. Result of my querry is 35MB and it takes 17 seconds on client computer and 6 seconds directly from server.

Best regards,
Walter Luszczyk

If the result of your query is 35MB in size then it's not particularly surprising that it takes 6 seconds for the client computer to get the results. Likely the reason it's taking longer when you copy the file to the client is that the client computer does not perform as well as the server. After your tuning, the server processed the query faster but it still had to transfer 35MB of data over the LAN.

Consider your 80MB data file. How long did it take for you do copy it from the server to the client?

With this in mind, you should consider whether you need 35MB worth of results. This is 43% of your database.

Can you work with less columns?|||

Unfortunately I cannot select smaller result - it's report ... :(

I try bit this problem from another side and my observations are:

I use SQL Profiler to solve the problem. Sending old querry yields performance: CPU:8641, Reads: 10688, Writes: 0, Duration: 12513. But new querry yields: CPU: 627, Reads: 10627, Writes: 0, Duration: 11956.

So why duration doesn't change (12513 : 11956) while CPU changes a lot (8641 : 627)?

This time result's size is only 7MB - it's really small in out network.

Regards,

Walter

|||

Duration in Profiler is not necessarily a measure of time to execute the query. It actually a measure of the time it takes to execute the query, get the result to the client, and the client to close the recordset.

For example, if you're transferring megabytes of data and your client is on a gigabit ethernet connection, your duration will be lower than if your client is on a 10 megabit ethernet connection. If you can set up a VPN connection between your client and server (connect from home) then you would see an even longer duration.

I've actually seen Microsoft Access tables opened on SQL Server that showed durations of 20-30 minutes but showed very low reads and CPU.

In your case, you need to either reduce the size of the data being returned or get a higher bandwidth connection to your server.

Some ideas:

Reduce the size of the data types. For example: Are you using nvarchar where you could use VarChar? Can you convert the output of your DateTime columns to SmallDateType?