Why is a SQL query executing slower in PHP than in Query Analyzer

Question

Hi,

I have a query that takes 5 seconds to run in SQL Server Managment Studio, but it takes 33 seconds in PHP to run the same query using *$qid=db_query($countQuery);* Has anyone got any idea why this might be?

We are using SQL Server 2000 which I do not believe is the problem.

[edit] After doing some profiling we noticed that the execution plans were completely different between the analyzer and the query from PHP. This was done on a single machine, web, sql and query analyzer.

Any Ideas?

Thanks

Answer 1

Are you using parameters? Parameter sniffing can affect execution plans ...

Answer 2

This is known to happen based on different session settings. Like a different setting for set ansi_nulls, or a different login name.

Also, sometimes a connection plan is outdated, but still used for old connections. To see if that is the problem, clear the plan cache with:

DBCC FREEPROCCACHE

Answer 3

I had cases when the Sql login, under which I executed the query, made a difference in performance. When running the query as sysadmin from Query Analyzer, the query was faster than when the applicaiton ran it as a different sql user.

This might not solve your problem, but it's something which can be checked when running out of ideas.

Answer 4

Ok, I found the problem...well, at least in this case. The problem was the mssql_* that ships with PHP. We tested the new sqlsrv_* which ran a 62 second query (mssql_) in 1.4 seconds so it is definitely much faster.

[edit]
The reason for it running slow in the mssql library was because of duplicate data in our table (which only had two id columns without any constraints on so we never noticed it).

Thanks for all the suggestions!