Procedure with cursor is much faster that without cursor(With memory table and a while loop).

Question

Procedure with cursor is much faster that without cursor(With memory table and a while loop).

Joy Smith San

SSC-Insane

Points: 24877
More actions
February 24, 2010 at 7:50 am

#143128

Dear All,
I have a doubt. Do we realy need to avoid using cursors..?
I have stored procedure written by some else a long back. It uses cursor. After reading "Eliminating Cursors" Article in this site, I tried to modify that procedure. I used table variable and put the data in it. Then took a counter and looped through, made a select from table variable every time.
But it's not working the way I expected.
Infact the one WITH cursor is working 10 times faster one WITHOUT CURSOR (With memory table and a while loop).
Is there a better way..? Please guide..
Thanks inadvance.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Simply replacing the cursor with another looping process will tend to not give you any gain at all. While you will hear "don't use cursors" commonly as a mantra on this board - ultimately, it's much more accurately said as "don't use single-row procedural behavior when you can use a set-based process instead".

Actually - within the "single row" set of options, CURSORS can be a fairly fast option. It's when you remove the "single-row" part that cursors start looking truly bad.

SQL Server tends to be VERY good at doing things in sets. That would stand to reason since it's essentially a set engine. It tends to perform badly when we force it to deal with one single row at a time, whether it's a cursor, WHILE loop, or any other similar process.

If you were to post what you're trying to do and what you might have so far - you should get some feedback on how to truly speed it up.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

Replacing a cursor with a while loop is not a hell of a lot better, the object is not to loop either by cursor or a while loop.

First off if you are using more than a tiny ( < 50 rows ish is my rule of thumb) use a temporary table. Not a table variable, if in doubt use a temporary table.

Secondly you need to analyse the code carefully and break it down into lumps of set based code.

If you post the code, you should get some suggestions on how to improve it.

Clear Sky SQL
My Blog[/url]

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 3

Thanks for the clarification.

Infact I was under the wrong impression.

Wel, I will check the procedure again.

Requirement is that, we need to loop through each record and check for a particular column.

based on that column values, we have to insert or update in diff tables.

I will try to post the actual code....later as its our client's server.

Wel, the number of records vary from 5000 to 1.2 million........mostly above a million.

Thanks

WayneS SSC Guru Points: 95460 More actions · Answer 4

Joy Smith San (2/24/2010)
Wel, the number of records vary from 5000 to 1.2 million........mostly above a million.
Thanks

If you can get us table definitions, test data and expected results (see the first link in my signature for how to do all of this), I think you'll be very pleased by what a set-based solution can do for you.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 5

haaaa...I lamost did it with a temp table and some joins.

tried with test data and performance is much better.

but have a doubt on left outer join.

I want to insert data into a table if it doesn't have matching records in the first table. but ther's a composite key

four fields ([Company Code], OrderNumber, OrderUpdateDate, OrderUpdateTime) together male a primary key. Please see below the query I wrote.

Is it correct..? Please help.

INSERT INTO #tmpHeader
(
[Company Code],[Vendor Order Number],[Order Type],[Hold Order Code]
,[Business Unit],[Customer P_O Number],[Update Date],[Update Time]
)
SELECTOrderCompanyCD,OrderNumber,OrderType,HoldOrderCode,BusinessUnit
,REFERENCE,ORderUpdateDate,OrderUpdateTime
FROM@SalesOrder SO
LEFT OUTER JOIN #tmpHeader TH ON (SO.OrderCompanyCD=TH.[Company Code]
ANDSO.OrderNumber=TH.[Vendor Order Number]
ANDSO.OrderUpdateDate=TH.[Update Date]
ANDSO.OrderUpdateTime=TH.[Update Time])
WHERE(
TH.[Company Code] IS NULL
ORTH.[Vendor Order Number] IS NULL
ORTH.[Update Date] IS NULL
ORTH.[Update Time] IS NULL
)

thanks.

WayneS SSC Guru Points: 95460 More actions · Answer 6

You will only need one of those where conditions, but you will need all 4 in the join between the tables. The query itself looks fine.

You should also test whether @SalesOrder would work faster as a temp table instead of as a table variable... the optimizer will treat this as only one row, even if there is a lot more, and can produce a query plan that isn't optimal. (you did mention 5000-1.2million rows in your previous post). All you should have to do is change the "Declare @SalesOrder table" to "CREATE TABLE #SalesOrder", and then do a search/replace to replace @SalesOrder to #SalesOrder

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 7

Yes..Will do that.

I just took on this server and this issue.

Thought memory tables would be faster, hence used it.

Thank you.

WayneS SSC Guru Points: 95460 More actions · Answer 8

Joy Smith San (2/25/2010)
Thought memory tables would be faster, hence used it.

You might want to check out this article[/url]

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 9

I agree... that's one of the better articles that explain some of the myths and actualities of Temp Tables and Table Variables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)