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?

Problem with Performance Monitor

Hi all,

I′m trying to start the Performance Monitor to collect data
of the counters of SQL Server:

- Access Methods
- Buffer Manager
- General Statistics
- Locks
- SQL Statistics

But when I perform the start, it occurs an error.
When I see the Events Log, the only information that
appears is a warning that indicates that the service could not start.

I′m using Windows Server 2003 with SQL Server 2000.

thanks!!!!Hi,

I forgot.
In the log event, the warnig indicates that the service could not add counters to log.

thanks

Problem with performance after leaving SQL Server Express idle for some time

Hi, everyone!

I have this strange problem... After every time my application leaves sql-server idle (doesn't send anything, doesn't retrieve anything) next command to sql-server processes really long.

I've also noticed this bug/feature/misconfiguration even if I open a DB in Management Studio...

Please, could someone tell me, is there any timer that "puts a DB to sleep" if no one is using it for some time? Can I change the way server behaves in this situation?

I saw this problem once long ago when we had to shovel coal into our PC's to make them run.

In our case, the database server was on a dedicated PC. Someone had installed a graphics-intensive screen-saver. Evertime the screen-saver kicked in performance dropped.

Another possibility: Opening a DB connection takes a long time. You may be timing out and getting kicked out of the server.

|||

Thanks for the reply.

Well, that's not screen saver, definitely. I had this problem both on local machine (obviously when screen saver is not running :) ) and on a remote server, but I know for sure what is going on there too.

And as I understood the second possible reason you're saying server kicks me out, but I've set the timeout to pretty big number (before I did that, I actually was kicked out all the time), but now I don't get connection-not-opened exception or anything else like that.

Everything works pretty smooth all the time (like ASP.NET page loads fast, fast postbacks, management studio works fine) until I leave server alone for some time. After that this damn lag happens (for one time, like it's reconnecting to DB or smth like that) and everything works fine again.

|||

Maybe it's not the database but asp.net.

When all sessions end or time out, your application is shut down. See if the Application_End event is raised.

|||No, i don't think so... First of all, I used the same app with MSDE 2000 and didn't have this issue. Second, I see this issue not only when using my asp.net app, but also WinForms app, and even Management Studio (as I think I mentioned in the first post).