问题描述
我有一个奇怪的问题.
我正在运行查询:
SELECT IMIE, NAZWISKO, PESEL2, ADD_DATE, CONVERT(varchar, ADD_DATE, 121) AS XDATA, ID_ZLECENIA_XXX, * FROM XXX_KONWERSJE_HISTORIA AS EKH1
INNER JOIN XXX_DANE_PACJENTA EDP1 ON EKH1.ID_ZLECENIA_XXX=EDP1.ORDER_ID_XXX
WHERE EKH1.ID_KONWERSJE = (
SELECT MIN(ID_KONWERSJE)
FROM XXX_KONWERSJE_HISTORIA AS EKH2
WHERE EKH1.ID_ZLECENIA_XXX = EKH2.ID_ZLECENIA_XXX
)
AND EDP1.RECNO = (
SELECT MAX(RECNO)
FROM XXX_DANE_PACJENTA EDP2
WHERE EDP2.ORDER_ID_XXX = EDP1.ORDER_ID_XXX
)
AND EKH1.ID_ZLECENIA_XXX LIKE '%140000393%'
AND ADD_DATE>'20140419' AND ADD_DATE<='20140621 23:59:59.999'
ORDER BY EKH1.ID_KONWERSJE, EKH1.ID_ZLECENIA_XXX DESC
如果我使用2个月左右的日期限制(63天-它给我1015个结果)，则查询正常.如果我延长日期限制，查询只会失败(查询失败等等).
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).
这是在Windows 64位php(Apache，Xamp)下发生的.
This happens under windows 64 bit php (apache, Xamp).
当我直接从MS SQL SERWER Management Studio运行此查询时，无论选择什么日期限制，一切都可以正常工作.
When I run this query directly from MS SQL SERWER Management Studio everything works fine, no matter what date limit I choose.
这是怎么回事?在apache/php下有某种限制吗?(没有查询时间超出"之类的信息，只有查询失败"之类的信息)
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")
推荐答案
之所以会发生这种情况，是因为 ADD_DATE>'20140419'AND ADD_DATE< ='20140621 23:59:59.999'的选择性为中/低(满足该谓词的行太多).和SQL Server必须扫描(是，扫描) XXX_KONWERSJE_HISTORIA 多次，以检查以下谓词:
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
)
要验证此谓词，必须扫描SQL Server XXX_KONWERSJE_HISTORIA 表多少次?您可以查看表扫描[XXX_KONWERSJE_HISTORIA] 数据访问运算符的属性:3917次
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
SQL Server Management Studio默认将执行超时设置为0(无执行超时).
SQL Server Management Studio has execution timeout set to 0 by default (no execution timeout).
注意:如果此索引可以解决问题，则应尝试(1)在 ADD_DATE 上创建具有所有必需的索引( CREATE INDEX ... INCLUDE(...))列和(2)在这些表上创建唯一的聚集索引.
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.
这篇关于查询在运行时间较长的地方失败(仅在php下的mssql server 2005)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！