By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,665 Members | 1,251 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,665 IT Pros & Developers. It's quick & easy.

SQL Express - Slow response time on all but one machine

P: 4
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 ?
May 4 '10 #1

✓ answered by 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!

Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
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
May 5 '10 #2

P: 4
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...
May 5 '10 #3

P: 4
@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... :-(
May 6 '10 #4

P: 4
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!
May 7 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.