How to optimize this query without cursor? , I dont want use cursor , save my life please ; (:(

Question

0.00/5 (No votes)

See more:

, +

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

What I have tried:

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

Posted 17-Sep-16 19:39pm

mhd.sbt

Updated 17-Sep-16 21:20pm

Add a Solution

Comments

David_Wimbley 18-Sep-16 1:41am

Without any schema this doesnt make much sense. Im confused as why you would use a cursor when all you are doing is a select statement.

mhd.sbt 18-Sep-16 1:46am

i want just Remove this cursor , In Cursor Body I have Just Time Variable That I dont Find A Way To This (for example With Join).

David_Wimbley 18-Sep-16 1:46am

So if you just want to remove the cursor why not take everything between hte BEGIN and END statements of your cursor? It looks as though your TIME variable doesn't change?

mhd.sbt 18-Sep-16 1:52am

ok How Can I do this , In Each Iteration Time Variable Change With Cursor Loop ,
and can Be have diffrent Time , so , how to Change Above Query Without Cursor ,
have an idea, to change this query?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2016-09-17T20:39:00

Solution 1

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[^]

Posted 17-Sep-16 20:39pm

Maciej Los

Wendelius · Answer 2 · 2016-09-17T21:20:00

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

SQL

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

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.