SQL Express - Slow response time on all but one machine

[goozak]

Hello,

I'm working on a web application using Apache (2.0.55), PHP (5.2.8) & SQL Server 2008 Express (using the Microsoft provided php extension php_sqlsrv_52_ts_vc6.dll) and I have a speed problem with SQL Server Express.

On my development machine, everything works fine. But when I setup other computers, the SQL Express takes much longer to respond.

My development machine is a 3GHz Core2 Duo with 1.96 GB RAM and one of my test machine is a 2.66GHz Core2 Duo with 1.99 GB RAM. Could the processor speed difference be the sole source of this slowness?

Using a profiler (SQL Server 2005/2008 Express Profiler), I can see that most of the SQL events are "exec sp_cursor" & "exec sp_cursorfetch" (I use SELECT statements in my PHP). On the development machine most of these have a duration of 0 (the others have 976); on another machine, the duration varies wildly, but each one is mostly between 30 and 300.

Anybody has some clues as to what could be the cause of this ?

[ck9663]

Is your SQL Server on your development machine? Another thing, cursor is one of the slowest way to access a table in SQL. Try converting it a set-based query. Once two or more client use your application, a lot of things are going on, including table locking which could look like a slow execution of a particular client.


Good Luck!!!

~~ CK

[goozak]

Thanks CK for your help. Yeah, everything is on the same machine (SQL Express & Apache). It's a stand alone product that will be installed on a single machine used by a single user. The code base is the same as the corporate, web server version.

I removed most of the cursors from the queries, but with marginal gain in speed. Other SQL events are taking a long time too, like "USE dbName" (duration of 976 on my machine, and 146484 on the other)...

We use TCP/IP to communicate with the SQL Server, a college found something about Named Pipes and a random configuration problem at installation... He had to leave in a hurry, so I'll look into that tomorrow...

[goozak]

@goozak
Well, still no luck... We tried with TCP/IP, Shared Memory & Named pipes but got the same result. SQL Server is slow on every machine we test on, except my own computer. I even uninstalled SQL Server and reinstalled it - it's still running fine !

Why does Oracle charge so much to include MySQL in our software? We have no speed problem with MySQL... :-(

[goozak]

Found it !! The problem was twofold:

1. The PHP code configured the sqlsrv DLL to log EVERYTHING (SQLSRV_LOG_SYSTEM_ALL & SQLSRV_LOG_SEVERITY_ALL) - these get logged in Apache's ErrorLog.
2. I was using RotateLogs in Apache for our ErrorLog and a CustomLog on my development machine.

The PHP is not my code, I'm starting a new job and the original programmer already left the company. Since it was working fine on my machine, I never thought of looking at the PHP - and these commands were in some dark corner anyway...

As soon as I either comment out the logging lines in PHP or use RotateLogs.exe for my Apache logs, the speed goes way up!

So, if you want to log a lot of stuff in Apache, use RotateLogs!