如何在没有游标的情况下优化此查询，我不想用光标，请拯救我的生命; （:( [英] How to optimize this query without cursor? , I dont want use cursor , save my life please ; (:(

问题描述

DECLARE cr CURSOR FAST_FORWARD FOR
SELECT [CardNo],[Time] FROM 
@Data
ORDER BY [Time]
OPEN cr
FETCH NEXT FROM cr INTO @CardNo, @Time
WHILE (@@FETCH_STATUS = 0)
BEGIN
		SELECT  CASE WHEN details.[EffectiveDate] = currentDetails.[CurrentEffectiveDate] THEN 1 ELSE 0 END AS 
		IsCurrent,details.PersonnelBASeId, details.EffectiveDate, details.CardNo
		INTO ##tmp
		FROM           tblPersonnelDetails AS details LEFT OUTER JOIN
									 (SELECT        PersonnelBASeId, MAX(EffectiveDate) AS CurrentEffectiveDate
									   FROM            tblPersonnelDetails
									   WHERE        (ISNULL(Deleted, 0) = 0) AND (EffectiveDate <= @Time) 
									   GROUP BY PersonnelBASeId) AS currentDetails ON details.PersonnelBASeId = currentDetails.PersonnelBASeId
		WHERE        (ISNULL(details.Deleted, 0) = 0)  ORDER BY EffectiveDate DESC,PersonnelBaseId DESC
END
END
CLOSE cr
DEALLOCATE cr

我尝试了什么：



在tsql中优化查询，

i不要使用Cursor，它非常慢

What I have tried:

optimize query in tsql ,
i dont want Use Cursor , Its very Slow

推荐答案

我建​​议阅读有关CTE的内容。请参阅：

使用common_table_expression（Transact-SQL） [ ^ ]

< a href =https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx>使用公用表格式 [ ^ ]

< a href =https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx>使用公用表表达式的递归查询 [ ^ ]

CP KB上的CTE [ ^ ]

I'd suggest to read about CTE. See:
WITH common_table_expression (Transact-SQL)[^]
Using Common Table Expressions[^]
Recursive Queries Using Common Table Expressions[^]
CTE's on CP KB[^]

不确定我是否正确阅读了该语句，但似乎你没有在任何地方使用 @CardNo 。您只使用 @Time ，因此该语句将从 tblPersonnelDetails 获取所有行，实际上是多次，多次找到 @Data 的行，具体取决于 EffectiveDate 。



因此，如果你想根据最大时间列获取行，可能只需要

Not sure if I read the statement correctly but it seems that you don't use @CardNo anywhere. You only use @Time so the statement would fetch all the rows from tblPersonnelDetails, actually multiple times, as many times as row is found for @Data depending on EffectiveDate.

So if you want to fetch the rows based on max of Time column, perhaps just

SELECT  CASE 
           WHEN details.[EffectiveDate] = currentDetails.[CurrentEffectiveDate] THEN 1
           ELSE 0 
        END AS IsCurrent,
        details.PersonnelBASeId, 
        details.EffectiveDate, 
        details.CardNo
INTO ##tmp
FROM tblPersonnelDetails AS details 
LEFT OUTER JOIN
(   SELECT PersonnelBASeId, 
           MAX(EffectiveDate) AS CurrentEffectiveDate
    FROM   tblPersonnelDetails
    WHERE   (ISNULL(Deleted, 0) = 0) 
    AND (EffectiveDate <= (SELECT MAX([Time]) FROM @Data)) 
    GROUP BY PersonnelBASeId) AS currentDetails 
    ON details.PersonnelBASeId = currentDetails.PersonnelBASeId
WHERE (ISNULL(details.Deleted, 0) = 0)  
ORDER BY EffectiveDate DESC,PersonnelBaseId DESC

但是据说这感觉有点好笑。如果您只想获取特定cardno的时间，只需将相关性添加到内部查询。

But as said that feels a bit funny. If you intend to fetch only the times for a specific cardno, just add the correlation to the inner query.

这篇关于如何在没有游标的情况下优化此查询，我不想用光标，请拯救我的生命; （:(的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！