SQLServerCentral / Discuss Content Posted by Clinton Herring / Article Discussions / Article Discussions by Author / Replacing Cursors and While Loops / Latest Posts

RE: Replacing Cursors and While Loops

Doug Hiwiller — Mon, 09 Apr 2007 14:00:00 GMT

Perhaps this has already been asked (or scoffed at for being 'undocumented') but have you considered a function using COALESCE to return your pivoted series?

CREATE TABLE Test (id INT,val VARCHAR(10))

INSERT Test (id, val) VALUES(56, 'run')INSERT Test (id, val) VALUES(56, 'jump')INSERT Test (id, val) VALUES(56, 'scale')INSERT Test (id, val) VALUES(57, 'fly')INSERT Test (id, val) VALUES(57, 'swim')INSERT Test (id, val) VALUES(57, 'crawl')

CREATE FUNCTION uf_pivot_series (@id INT)RETURNS VARCHAR(8000)BEGINDECLARE @return VARCHAR(8000)DECLARE @delimiter CHAR(2)SET @delimiter = ', 'SELECT @return = COALESCE(@return + @delimiter, '') + val FROM TestWHERE id = @id

RETURN @returnENDGOSELECT id,dbo.uf_pivot_series(id) as valFROM Test

RE: Replacing Cursors and While Loops

Vladimir Sim — Sun, 07 May 2006 10:48:00 GMT

Hi Herring,Below is my example to illustrate my issue.From your article, I know how to use function toupdate using select statement.But I can't think on how to insert new valuesusing select statement. Please illustrate withexample to guide me. How to convert my exampleinto using select statement inserting and updating..Thank you.declare cur_insert CURSOR FORselect ..... FROM where xxxxWhile(xxx)begin if(xxx) Insert into values(xxxx) else update end

RE: Replacing Cursors and While Loops

Clinton Herring — Sun, 07 May 2006 00:44:00 GMT

You can of course use the Values clause but if you want to call functions to generate some new data then use the select statement...insert into >table< (>columnlist<)select value1, value2, >function<, >etc. until column list is matched<

RE: Replacing Cursors and While Loops

Vladimir Sim — Sun, 07 May 2006 00:08:00 GMT

Hi Herring,I read with interest on this topic of the 'replacing cursor and while loop'.I faced the same problem on having lot of cursor which I replace with loop, but didn't solve my execution time.Your article really help me to look at different way to replace cursor with select statment. I know how to useselect for updating data using select method from yourarticle.But I cannot think of any good way to insert new datausing select statement. Do you have any good way toinsert new data using the select statement which I replacefrom cursor.Please advise. thank you

RE: Replacing Cursors and While Loops

Clinton Herring — Tue, 11 Apr 2006 06:58:00 GMT

Good thoughts Mark. Thanks for your input.

RE: Replacing Cursors and While Loops

colin.Leversuch-Roberts — Mon, 10 Apr 2006 06:43:00 GMT

It's just worth pointing out that by default how a cursor and while loop work are different from a transaction view point. A while will execute as a complete transaction - which might bring about its own problems. ( this assumes no explicit transactions defined )

Just an observation - I like replacing cursors with while loops but you do have to be careful some times.

RE: Replacing Cursors and While Loops

Mark Bolen — Fri, 07 Apr 2006 11:01:00 GMT

Here is how a highly available database would look. I work on databases with hundreds of thousands of users on databases unconceivable large to most. At my job location, we don't even have a nightly processing time (global app).

Whenever I read posts on this and most other sites, I have to keep in mind that they have a low concurrency of users. As a user base grows, most will have a an increasing number of problems they can't identify over time.

The following code is focused on removing cursors, actually a lot of code should be added for error checking, but for simplicity and keeping to the topic it is left out. While loops in our environment are a necessary evil to keep the number of rows being updated down to a small number so they can complete and release locks within 4 seconds (our apps rule; should actually be lower; perhaps 3). Think availablity first, then speed. At my work location, we even have variations of this that loop through rows in blocks of 5, 10, or whatever higher number still allows us to complete in less than 4 seconds. As you can see, it is a completely different mind set.

--------------------

Declare @tmp_id intDeclare @prev_id intDeclare @tmp_values varchar(20)

Set LOCK_TIMEOUT = 12000 -- assuming all code in app is tested to have -- no update take longer than 4 seconds Set @prev_id = -1 -- assumes all ID's are positive in nature

While Exists ( SELECT TOP 1 @tmp_id = id from DB.dbo.OutPut_tbl WITH(NOLOCK) Where id > @prev_id -- retrieve next row > than the previous order by id -- key data ; preferable clustered ; -- order by id to maintain -- a reference point )BEGIN --Direct column pivot into a variable -- I don't know enough about the column_out field; but lets hope we never -- increase in size greater than 20; declarations and table field would -- need to be changed if that -- was possible. SELECT @tmp_values = @tmp_values + convert(varchar(20), column_data) + ',' FROM DB.dbo.many_tbl WITH(NOLOCK) WHERE id = @tmp_id UPDATE DB.dbo.OutPut_tbl WITH(ROWLOCK) -- assuming that this will take -- less than 4 seconds -- otherwise create another -- while exists loop SET column_out = Left(@tmp_values,20) WHERE id = @tmp_id

SET @prev_id = @tmp_id -- move our pointer to the next row

END -- While Exists

RE: Replacing Cursors and While Loops

Mark Bolen — Fri, 07 Apr 2006 08:21:00 GMT

I hope there are not many people hitting this table. This may be OK as long as it is a nightly run that you know you are the only code running against it.

However long it runs, it will be locked during this process. Although fast, for databases with a lot of people expecting to update it, it would be unacceptable unless you can get it to run in less than 4 seconds (you last said 26 seconds I believe) and lock timeout's are set well above that. Speed is not always the only consideration unless you are running exclusively! In which case I would put a table lock hint on it to speed it up even more. I would recommend taking a little longer time to make sure you don't have a table lock for a half minute.

Mark

RE: Replacing Cursors and While Loops

Phil.Nicholas — Fri, 07 Apr 2006 05:32:00 GMT

Just a couple of things I've found useful. If you have to loop generally it's quicker to use a while loop selecting each record individually rather than a cursor. Also if you find you have very large SQL statement it often runs much quicker broken up into smaller ones. If each calculation can be broken up into individual set based queries I'm sure you'll improve the performance.

RE: Replacing Cursors and While Loops

Jeswanth Jaishanker — Mon, 25 Jul 2005 00:14:00 GMT

Happend to see this while browsing . thought it might be relevant here. have a look

http://www.sqlteam.com/item.asp?ItemID=2368

Jeswanth

RE: Replacing Cursors and While Loops

Clinton Herring — Tue, 12 Jul 2005 07:15:00 GMT

All,I just yesterday used the techique at a contract client with similar results although not quite as dramatic as the one I wrote about. To be fair though, there is a limit to how well this may work. After I wrote the article I came across another update that was being handled with a cursor. Within the cursor 18 columns had to be updated while pivoting data. I found I only needed 7 functions to perform the 18 updates but by the time all thoses calls were made for 2.2 milllion records, the new process clock out at the same speed as the old one. I tested individual calls and they were very short but that many had an accumulative affect. The good side though is that the code is so much simpler and easier to read that I plan to replace the old code anyway. Have fun building your functions.

RE: Replacing Cursors and While Loops

Clinton Herring — Tue, 12 Jul 2005 07:09:00 GMT

You are correct for the code I listed in the article. I did change it in the final results here at work to remove the last comma.

RE: Replacing Cursors and While Loops

Kenneth Lee — Mon, 11 Jul 2005 17:07:00 GMT

I've had similar experiences with improving cursors. I had to look at someone's report because they weren't getting good data from it anymore. I ran the existing proc on my machine just to see what it would produce. 5 minutes later I hadn't gotten a result, and I had started to read what they were doing and understood why they weren't getting fast results, but still didn't understand what they were trying to do. 10 minutes after that I understood what they were trying to do, their proc is still running. I open up a new window and start getting answers piecemeal.

If I execute this select I'll get all the users they are interested in. Yep. If I join this information I'll find out the offices they are involved in. Yep. Hmmm, how am I going to include all the related offices. Oh, this should do it, but this could loop indefinitely, before I start this, put a different stop in the loop. (Came in handy when I blew the data comparison later and caused the infinite loop.)

Anyway, by the end of the day, I had reproduced the report information (including the information the original proc was dropping.) that they wanted. I left my workstation up overnight because I still hadn't gotten an answer from my machine in 6 hours of running.

Next morning I killed the 20 hour process, wrote up a new proc and got a report in 30 seconds. I heard the old processes ran in 4 hours on the faster production machine for 1 DB and 2 hours for another DB. (Custom processes for each DB.) My proc gets reports from both DBs in 28 seconds.

RE: Replacing Cursors and While Loops

Kenneth Lee — Mon, 11 Jul 2005 16:42:00 GMT

After the pivot the results data would look like this...ID Result-- ----------------56 run, jump, scale

No, not really, it should look like:

ID Result-- ----------------56 run,jump,scale,

That's 15 characters long, and if the return statement was:

return substring(@value,1,LEN(@value)-1)

you could drop the comma at the end. That's assuming your string including the last ',' character was 20 or under characters long.

RE: Replacing Cursors and While Loops

Clinton Herring — Mon, 11 Jul 2005 10:53:00 GMT

This doesn't pivot the results. I wanted the the data from several matching rows in the Many table to come back as a single value of data separated by commas in the results table.Suppose I had the following Many table...ID DATA-- ------56 run56 jump56 scaleAfter the pivot the results data would look like this...ID Result-- ----------------56 run, jump, scale

RE: Replacing Cursors and While Loops

Maurice Pelchat — Mon, 11 Jul 2005 10:35:00 GMT

The problem is not really in the cursor itself. It is in the fact that operations are not set-oriented. Doing 1 million inserts is not the same at all as doing a single insert based on a select that returns 1 million rows and insert them in a single operation.

I converted recently a transact-sql batch that was doing about 8 million inserts. The execution time was about 8 hours. By making a big query using derived tables and case statement to apply some conversion/extraction logic in the query, and union all to bring together rows obtains under disjoin logic context, this time reduced from 8 hours to 30 minutes (a 16 to 1 improvement). By using such query it was also possible to reduce logging by doing a select INTO.

Even without doing a SELECT INTO, SQL Server optimize log writes operations. It seems that there is a lot less logging required when doing such massive single insert than when doing it row by row.

Cursors are an easy way to describe a job, but the net result is that it ends in row by row reads & writes. For example a single row read use almost all the time suitable indexes, it they exists, which may seems good. But it is very not good if you finally read all the rows from the table this way. When you match a lot of rows using a join SQL Server may evaluate that using an index is not good, and the match is going to be done with a better suited joining algorithm for this type of job. There is another optimization gain possible there.

The only problem with this approach, is that you have to work harder to define the big “do it all” query.

RE: Replacing Cursors and While Loops

Clinton Herring — Mon, 11 Jul 2005 10:18:00 GMT

Stephen,In my actual code the data needed to be order in the pivoted result set. If one doesn't care about the order then your correct, the order by is not needed.Clint

RE: Replacing Cursors and While Loops

Yelena Varshal — Mon, 11 Jul 2005 08:49:00 GMT

Hi,

Good Article!

In my opinion cursor usage should depend on "How Often". Cursors provide an easy programming solution when the extensive row processing should be done. If something should be done only a couple of times like in one time data migration tasks, I ususally opt for cursors. If something has to run every day and takes a lot of time than the set approach should be researched and maybe some database architecture changes including de-normalizing the database should be done.

Yelena

RE: Replacing Cursors and While Loops

Mike C — Mon, 11 Jul 2005 08:23:00 GMT

In addition to what was already stated about creating a temp table to hold intermediate results, if your calculations are conditional using IF..ELSE constructs, you might look at converting them to CASE expressions and/or modifying your WHERE clauses.

Without seeing your DDL it's impossible to get much more detailed than that.

RE: Replacing Cursors and While Loops

Mike C — Mon, 11 Jul 2005 08:12:00 GMT

Great article. A lot of people make the mistake of trying to use SQL as a procedural language instead of a declarative language. It's hard for some to think in terms of SQL set-based statements as opposed to iterative looping and cursors.

I'd estimate that cursors could be eliminated and replaced with set-based SQL statements 99.9999% of the time that they are used.

RE: Replacing Cursors and While Loops

Ninja's_RGR'us — Mon, 11 Jul 2005 07:59:00 GMT

It would be best if you started a new thread for this question. You will get more attention to it and you'll be able to provide more details.Could you provide the table definition (even if you change the names). Some sample data and the expected outputs. I'm thinking at the moment that you could do a first select statement that would do the 100 calculations and insert that data in a temp table. Then you could do your updates/inserts using that temp table. It should run 10/50 times faster that way.

RE: Replacing Cursors and While Loops

Anubis-237029 — Mon, 11 Jul 2005 07:51:00 GMT

Recently I have had to create an admin task that needs to be run on our 24gb database each day. Unfortunately due to our company security I cannot show the DDL but will do my best to explain...

In this task (50000 lines of TSQL) I have had to use several cursors to complete the task. This task is broken down into several smaller or sub tasks to perform the entire operation.

Though I admit I have not had the chance to fully analyze the task as yet to identify scans and the like (which I will get to shortly) however I wish to try and rid myself of the cursors first.

In one 'sub' task I managed to do this by converting a cursor into a simple insert statement:

Insert Into dbo.tblSelect col1, col2, col3From dbo.workingtbl

This managed to cut time down from 11 minutes to 3. Working with approx 500,000 rows in the sql above.

The remaining cursors are becoming a little hard to work our. In the cursor I open approximately 20 variables which are used throughout the loop..

Declare #x cursor forselect col1, col2... col20from dbo.tableorder by col1fetch next from #x into @var1, @var2... @var20while..... (you all know the rest)In the while loop above I perform close to 100 calculations, 5 to 10 updates and inserts etc....

With this many calculations and updates/inserts could anyone suggest a better method of this loop without using a cursor? Or perhaps this is one of those isolated cases where a cursor is best?

Either way, any suggestions would be great!

RE: Replacing Cursors and While Loops

rschaeferhig — Mon, 11 Jul 2005 05:53:00 GMT

If you don't believe this example take a look at the example I posted which Noeld and Remi helped me convert:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=197948

This went from 2 hours to 6:46 to 0:26 in the final iteration!!

RE: Replacing Cursors and While Loops

Stephen Bailey — Mon, 11 Jul 2005 03:16:00 GMT

I can't see the purpose of the ORDER BY clause. If you were to remove this from your select statement, you may find that you improve the performance. It may also be the case that the outer loop has an ORDER BY clause that can also be removed.

Profile the procedure to determine what indexes are being used on the tables. Ensure that the indexes in use are the most efficient. You may be able to improve the performance yet again by creating covering indexes.

RE: Replacing Cursors and While Loops

Jeswanth Jaishanker — Mon, 11 Jul 2005 01:09:00 GMT

Try this code. you might save remaining 12 Minutes also..

UPDATE BSET b.id = A.idFROM dbo.many_tbl A inner join dbo.OutpUt_tbl B ON A.id = B.ID

Jeswanth

Replacing Cursors and While Loops

Clinton Herring — Wed, 29 Jun 2005 16:16:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/cherring/replacingcursorsandwhileloops.asp