Query Failed Where Runnig Bigger Time Period (mssql Server 2005 Under Php Only)

I have a weird problem. I'm running a query: SELECT IMIE, NAZWISKO, PESEL2, ADD_DATE, CONVERT(varchar, ADD_DATE, 121) AS XDATA, ID_ZLECENIA_XXX, * FROM XXX_KONWERSJE_HISTORIA AS EK

Solution 1:

And the query works ok if I use a date limit around 2 months (63 days - it gives me 1015 results). If I extend the date limit query simply fails (Query failed blabla). ... What is going on? Is there a limit of some kind under apache/php? (There is no information like "query time excessed", only "query failed")

This could happen because selectivity of ADD_DATE>'20140419' AND ADD_DATE<='20140621 23:59:59.999' is medium/low (there are [too] many rows that satisfy this predicate) and SQL Server have to scan (yes, scan) XXX_KONWERSJE_HISTORIA to many times to check following predicate:

WHERE EKH1.ID_KONWERSJE = (
    SELECT ...
    FROM XXX_KONWERSJE_HISTORIA AS EKH2
    WHERE EKH1.ID_ZLECENIA_XXX = EKH2.ID_ZLECENIA_XXX
)

How many times have to scan SQL Server XXX_KONWERSJE_HISTORIA table to verify this predicate ? You can look at the properties of Table Scan [XXX_KONWERSJE_HISTORIA] data access operator: 3917 times

What you can do for the beginning ? You should create the missing index (see that warning with green above the execution plan):

USE [OptimedMain]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[ERLAB_KONWERSJE_HISTORIA] ([ID_ZLECENIA_ERLAB])
INCLUDE ([ID_KONWERSJE])
GO

When I run this query directly from MS SQL SERWER Management Studio everything works fine, no matter what date limit I choose.

SQL Server Management Studio has execution timeout set to 0 by default (no execution timeout).

Note: if this index will solve the problem then you should try (1) to create an index on ADD_DATE with all required (CREATE INDEX ... INCLUDE(...)) columns and (2) to create unique clustered indexes on these tables.

Solution 2:

Try to set these php configurations in your php script via ini_set

ini_set('memory_limit', '512M');
ini_set('mssql.timeout', 60 * 20);

Not sure it will help you out.