SQL Server Performance Forum – Threads Archive
Help with slow performing cursor
I have a cursor based SQL update that has been running for ever. Is there a more efficient way to accomplish the same using Trnsact-SQL instead of the cursor below. Any help in this matter will be greatly appreciated.<br /><br /><font size="3"><font face="Courier New"><font color="red">DECLARE @l_oldest_effdate int <br />DECLARE @FolderID varchar(239)<br /><br />DECLARE cFOLDERIDS CURSOR FOR<br />SELECT DISTINCT a50<br />FROM f_sw.doctaba<br />WHERE a50 <> ‘N/A’ or a50 is not NULL and f_docclassnumber=2<br />OPEN cFOLDERIDS<br /><br />FETCH NEXT FROM cFOLDERIDS INTO @FolderID<br />WHILE @@FETCH_STATUS = 0<br /><br />BEGIN<br /> — get the oldest eff date for this folderid<br /> SELECT @l_oldest_effdate = MIN(a3<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br /> FROM f_sw.doctaba<br /> WHERE a50 = @FolderID and a38 is not NULL<br /><br /> UPDATE f_sw.doctaba<br /> SET a51 = ‘Y'<br /> WHERE a50 = @FolderID<br /> AND a38 = @l_oldest_effdate;<br /> FETCH NEXT FROM cFOLDERIDS INTO @FolderID<br />END<br /><br />CLOSE cFOLDERIDS<br />DEALLOCATE cFOLDERIDS</font id="red"></font id="Courier New"></font id="size3">Don’t user CURSOR. Use SET BASED.<br />Try this.<br /><pre id="code"><font face="courier" size="2" id="code">UPDATE d<br />SET a51 = ‘Y'<br />FROM f_sw.doctaba d<br />INNER JOIN <br />(<br /> SELECT DISTINCT a50 as FolderID<br /> FROM f_sw.doctaba<br /> WHERE a50 <> ‘N/A’ or a50 is not NULL and f_docclassnumber = 2<br />) f<br />ON d.a50 = f.FolderID<br />INNER JOIN<br />(<br /> SELECT l_oldest_effdate = MIN(a3<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br /> FROM f_sw.doctaba<br /> WHERE a38 is not NULL<br />) m<br />ON d.a50 = m.a50<br />AND d.a38 = m.l_oldest_effdate</font id="code"></pre id="code"><br /><br /><hr noshade size="1"><br /><font size="1">KH</font id="size1">
Thanks for the reply . But it does not work. I get a SQL syntax error as below Server: Msg 207, Level 16, State 3, Line 1
Invalid column name ‘a50’.
Chakmur,<br />Include the a50 column in the "SELECT l_oldest_effdate = MIN(a3<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> FROM f_sw.doctaba WHERE a38 is not NULL". Use<br /> SELECT a50, l_oldest_effdate = MIN(a3<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> FROM f_sw.doctaba WHERE a38 is not NULL <br /> AND (a50 <> ‘N/A’ or a50 is not NULL and f_docclassnumber = 2)<br />GROUP BY a50<br /><br />I added the a50 to improve performance (that is if you have millions of records and there are index, otherwise, you can ommit that part).<br /><br />
]]>