Is Cursor Really Not Good?

Question

Is Cursor Really Not Good?

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Charmer SSChampion Points: 14903 More actions · Answer 1

GSquared (4/24/2012)
Okay, for clarification, is all this is doing is concatenating text together into a single text string, and then putting that in another table?
If so, you definitely don't need a cursor.
Or am I misundertanding the needs here?

Yes ...absolutely you are correct..and some how i found something from google and i made this script....it seems it works good...can you take a look at this script, please..?

SELECT

P.JurisID ,'INC' ModCode,P.IncidentIDRef AS ModIDRef,'C' RecType ,0 SeqNo,

'**'+ DIDESC + '** ' + REPLACE(REPLACE(LEFT(D.FDNAR,LEN(D.FDNAR)-1),'',''),'</P&gt','') As Comment

,P.DIDCU AS CreatedPFIDRef ,P.EntDttm AS CreatedDttm,p.DIDOC#

FROM PSDOCI p

CROSS APPLY (SELECT FDNARR + '' AS [text()]

FROM PSFDOC

WHERE FDDOC# = p.DIDOC#

ORDER BY P.jurisid , P.INCIDENTIDREF, P.DIDOC#, CAST(FDLINE AS Int)

FOR XML PATH(''))D(FDNAR)

WHERE DIFILE = 'PLINCD'

and (not DIDESC in ('cad system narrative', 'dispatch narrative')

and

not DIDESC like 'Converted Cad Comment%')

Thanks,
Charmer

Dwain Camps SSC Guru Points: 86978 More actions · Answer 2

I think you've gotten the idea.

Don't wait for us to desk check it. Run the SELECT (maybe with a TOP 100 or something) and look at the output results. Is that what you want to INSERT?

If so, add the INSERT I suggested in my step#1.

Then time it when it runs and please post those results once you get it working, so that people don't only have my *100%* promise that it will be faster.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Charmer SSChampion Points: 14903 More actions · Answer 3

GSquared,

i have attached sample output of the script...and i high lightened the rows where i have problem...

if ModIDRef has duplicates, again comment column has to be concatenated...and seqno = 1 else 0..

i don't know how to make this condition. and i don't know whether this would be possible or not...that is why i went for cursor...

could you give me some ideas without going for cursor to achieve this..?

if you don't understand my concept, please let me know...

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 4

dwain.c (4/24/2012)
I think you've gotten the idea.
Don't wait for us to desk check it. Run the SELECT (maybe with a TOP 100 or something) and look at the output results. Is that what you want to INSERT?
If so, add the INSERT I suggested in my step#1.
Then time it when it runs and please post those results once you get it working, so that people don't only have my *100%* promise that it will be faster.

yes..but still needs to do something..please take a look at my sample output that i have attached here...

Thanks,
Charmer

GSquared SSC Guru Points: 260824 More actions · Answer 5

The problem rows have DIDOC# values. Your subquery (the Cross Apply query) requires that they be the same as per the first item in the Where clause. You may need to review the Where clause.

You're definitely moving in the right direction. We can only help to a certain extent, because we don't know the business-needs that the data represents, but what we can help with we will.

I was going to suggest the For XML Path trick, but you found it before I got that far.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Charmer SSChampion Points: 14903 More actions · Answer 6

GSquared (4/25/2012)
The problem rows have DIDOC# values. Your subquery (the Cross Apply query) requires that they be the same as per the first item in the Where clause. You may need to review the Where clause.
You're definitely moving in the right direction. We can only help to a certain extent, because we don't know the business-needs that the data represents, but what we can help with we will.
I was going to suggest the For XML Path trick, but you found it before I got that far.

yes GSquared, you are absolutely correct....you got the problem....i figured out what was the problem last day.....

i am working on it...but no idea to accomplish that...:(

but i am completely satisfied with XML path..:satisfied:

Tons of thanks to you...

Thanks,
Charmer