Performance of SQL server cursor | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Performance of SQL server cursor

I have a stored procedure in one server and a DTS in another server which just calls the stored procedure. The logic of the stored procedure is as follows: I need to select data from one table and I need to insert those records into a table for a specified date range. For example:-
Date Range : 1/1/2006 and 1/27/2006.
I am having 10,000 records to select from the source table. I need to insert these records for all the days between 1/1/2006 and 1/27/2006. So for this I am using a cursor to do the functionality. When I exexuted the stored procedure from a DTS (remote server) it took nearly 1 hour and 5 minutes to complete the job. Whether there is some other way to achieve the functionality without the cursor. It took a long time because of the usage of cursor. Please do the needful.

Hi, Why don’t you use Transform data task???. Just go throught the given options in BOL. -Saravanan. -Saravanan.
Hi,<br />Is both server are in same network segment ?! <br />what about network speed ?<br />is their any job scheduled at the time ?<br /><br /><br />please do not duplicate posts :<br /<a target="_blank" href=http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=12588>http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=12588</a><br /><br /><img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br />Regards<br /><br />Hemantgiri S. Goswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami<br />
Both the servers are in the same network<br /><br />the database where we are going to insert records in the actual production server and i am calling from another server which is a replica of production server.<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by ghemant</i><br /><br />Hi,<br />Is both server are in same network segment ?! <br />what about network speed ?<br />is their any job scheduled at the time ?<br /><br /><br />please do not duplicate posts :<br /<a target="_blank" href=http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=12588>http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=12588</a><br /><br /><img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br />Regards<br /><br />Hemantgiri S. Goswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami<br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
Is this you wanted? Insert into table1(columns)
select columns from table2 where <condition> Madhivanan Failing to plan is Planning to fail
This is my stored procedure template:<br /><br />ALTER PROCEDURE InsertData (@l_FromDate as nvarchar(10), @l_ToDate As nvarchar(10)) as<br /> <br />BEGIN<br />SET NOCOUNT ON <br /><br />SET @Fromdate = @l_FromDate<br />SET @Todate = @l_ToDate<br /><br />SET @l_datediffCnt = datediff(day,@Fromdate,@Todate)<br />SET @l_Cnti= 0<br /><br />SET @l_year=year(@Fromdate)<br />SET @l_month=month(@Fromdate)<br /><br />IF len(@l_month)=1 <br />BEGIN<br /> SET @l_month=’0′ + @l_month <br />END<br /><br />DECLARE Cursor_date Cursor for <br /><br />SELECT fields list FROM #Temp_Product (This fields list does not contain date) <br />OPEN Cursor_date <br /><br />FETCH NEXT FROM Cursor_date<br />INTO list of fields<br /><br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br />WHILE (@l_datediffCnt &gt;= 0 )<br /> BEGIN<br />INSERT INTO table (fields list + date) values<br />(values list, DATEADD(DAY,@l_Cnti,@Fromdate))<br /><br />SET @l_datediffCnt = @l_datediffCnt – 1 <br />SET @[email protected]_Cnti+1<br /> END<br /> SET @l_Cnti= 0<br />SET @l_datediffCnt = datediff(day,@Fromdate,@Todate)<br />SET @l_CntSet = 1<br /><br />FETCH NEXT FROM Cursor_date<br />INTO list of fields<br />END<br />CLOSE Cursor_date<br />DEALLOCATE Cursor_date<br />END<br /><br />———————————————————————————-<br /><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by ndramkumar</i><br /><br />Both the servers are in the same network<br /><br />the database where we are going to insert records in the actual production server and i am calling from another server which is a replica of production server.<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by ghemant</i><br /><br />Hi,<br />Is both server are in same network segment ?! <br />what about network speed ?<br />is their any job scheduled at the time ?<br /><br /><br />please do not duplicate posts :<br /<a target="_blank" href=http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=12588>http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=12588</a><br /><br /><img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br />Regards<br /><br />Hemantgiri S. Goswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami<br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
Create an calendar table, populate it with all dates you will ever need and use that table for data set based solution.
]]>