June 13, 2008 at 9:01 am
I just converted a little cursor script that generates ‘sp_grantdbaccess’ commands from syslogins.
The cursor script ran about 7 seconds, but the converted script runs in less than a second.
I know it is a small test, but the difference is very significant.
I would like to see some more tests before we dismiss the possible benefits of this technique.
Test, Test, Test.
June 13, 2008 at 9:01 am
Mike C (1/17/2006)
SQL is set-based....
I hope you don't mind... but I printed that post out in a large font and hung in my cube... the next time a programmer comes to me and wants to know why his
SELECT
value1 = (SELECT column1 from table1 b where a.key = b.fkey)
...
FROM
basetable a
runs like crap... I will point to it.. 🙂
Thanks! you've made my friday.
June 13, 2008 at 9:10 am
I've been trying to get around this problem too. I tried using the OUTPUT clause of the INSERT statement, but it didn't help me. The OUTPUT clause can only output actual inserted values. I wanted it to correlate the newly generated IDENTITY values with a value in the FROM clause of the INSERT. That's a syntax error!
In the case I'm working on, I'll probably opt for a cursor because the number of rows involved per correlated identity insert will likely never exceed 10.
Still, using a cursor makes me feel... dirty... 😀
André Cardoso (1/17/2006)
On a different note, how do you approach eliminating a cursor for "batch" inserting correlated items and the parent item uses an identity as PK (for example, inserting a batch of orders and the corresponding orderItems)?
I have two approaches, but neither is very ellegant:
Another approach is with the help of triggers, but I'm not very fond of using triggers
June 13, 2008 at 9:16 am
jcrawf02 (6/13/2008)
Michael MacGregor (6/13/2008)
The trick is to return a resultset of the new IDs for the UI to use, and that's easy as pi.Not being able to resist . . .
precisely how easy is pi? :hehe:
Sorry should have said "...easy as pi :-J"
Or -)
Cheers!
MTM
June 13, 2008 at 9:18 am
to prove a point, we had a contractor for a third party app who wrote an SP that took 6 hours to run!! I eliminated the cursor using the above technique and got the SP to work in less than a minute. Call it a miracle or be a Cursor hugger, but this process works. Secondly, Cursors are by default Global which 90% of folks miss out on. Creating a table variable is in the memory, not in the tempdb and hence much more efficient. Of course if you have truckload of data, you better use a #Table.
June 13, 2008 at 9:20 am
abhay.kulkarni (6/13/2008)
Creating a table variable is in the memory, not in the tempdb and hence much more efficient. Of course if you have truckload of data, you better use a #Table.
Not true!
Table variables are very much created in tempdb!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 13, 2008 at 9:35 am
In the while loop, looping through the memory table with the row count can be problematic at times.
For example; Make the table a real table not a memory table and not a temporary table.
Then by using the below example code in the while loop instead of the row count the process will remember where it left off.
This now gives you the ability to have the looping procedure be restartable from where it left off previously.
This example is pseudo code but you get the idea.
CREATE Procedure ProcName (@RestartStep VARCHAR(20))
AS
--@RestartStep parameter is optional
IF @RestartStep = 'STEP-010'
BEGIN
GOTO STEP-010
END
ELSE
IF @RestartStep = 'STEP-020'
BEGIN
GOTO STEP-020
END
STEP-000: -- First step
-- What ever step one does declare global variables set them...etc.
STEP-010: -- Loop through data
IF NOT EXISTS(SELECT name FROM sysobjects WHERE name = 'Item_Table')
BEGIN
CREATE Item_Table
(
primary_key INT IDENTITY(1,1) NOT NULL --THE IDENTITY STATEMENT IS IMPORTANT!
, item_category_id INT
, order_id INT
)
END
IF NOT EXISTS(SELECT Primary_Key FROM Item_Table)
BEGIN
INSERT INTO Item_Table
SELECT -- Same SELECT statement as that for the CURSOR
it.item_category_id
,ord.order_id
FROM dbo.item_categories it
INNER JOIN dbo.orders ord
ON ord.item_category_id = it.item_category_id
WHERE ord.order_date >= '1-sep-05'
and it.isSuspended != 1
END
DECLARE @PrimKey INT
WHILE EXISTS(SELECT TOP Primary_Key FROM Item_Table)
BEGIN
SET @PrimeKey = (SELECT TOP 1 Primary_Key FROM Item_Table)
--process the row of data as needed.
DELETE @Item_Table WHERE Primary_Key = @POrimeKey
END
IF NOT EXISTS(SELECT Primary_Key FROM Item_Table)
BEGIN
DROP TABLE Item_Table
END
STEP-020: -- Next step processes
--Next Step here
June 13, 2008 at 9:44 am
Agreed to a point.
Often times there is not enough time to completely study and re-write someone elses work in set based logic and a fix is needed immediately.
You must agree that eliminating the cursor in this manner alleviates long blocking/locking times that can be caused by using a cursor especially in long running procedures. Allowing user access between row by row processing thereby not having to wait for the cursor to be deallocated to release its locks.
Also be careful with transactions, make them small and to the point for the same blocking/locking reason.
But as usual we can argue all day because there is always a better way, right?
June 13, 2008 at 9:50 am
abhay.kulkarni (6/13/2008)
Creating a table variable is in the memory, not in the tempdb and hence much more efficient.
Absolutely NOT true... please see Q4/A4 in the following URL. In fact, read the whole thing... table variables do not and cannot be made to use statistics... that's part of the reason why you really need to keep table variable usage small...
http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2008 at 10:06 am
dugsmith (6/13/2008)
thereby not having to wait for the cursor to be deallocated to release its locks.
"Firehose" cursors do not keep the same types of locks... in fact, they're pretty much non-blocking just like a temp table and While loop.
Nope... I'm definately not an advocate of cursors... just making a statement. I've never written a cursor except to show that they're slow... just like a temp table and While loop are slow... and I've never seen a temp table/While loop replacement beat a cursor... they both kill performance.
Most people perceive a temp table and While loop to be quicker than a cursor. They may have even measured both for performance. Converting a Cursor to a Temp Table/While loop is really a waste of time. The reason why it appears to run faster is because you've essentially built a "Firehose" cursor which is just as fast. Instead of wasting all the time to do the conversion, just change the cursor to a "Firehose" cursor... just add FAST_FORWARD to the cursor declaration.
Best thing to do is to write set based code from the "Git"... then you don't have to worry about stuff like this. And, even if you're under the gun, if you don't take the time to convert RBAR procs to set based when you have to make any change to a proc, then you'll never get to it.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2008 at 10:11 am
Jeff Moden (6/13/2008)
Best thing to do is to write set based code from the "Git"... then you don't have to worry about stuff like this. And, even if you're under the gun, if you don't take the time to convert RBAR procs to set based when you have to make any change to a proc, then you'll never get to it.
Yup - like test-driven dev and inline documentation, being under the gun doesn't alleviate the need to do it "right". Although I certainly don't claim to be perfect at either, the "touch it once" principle tends to pay off for me, substantially better than the "I'll slap this together and do something better later". Like Jeff mentions, "later" often turns into "never".
As for me - While loops only seem to help me when I'm trying to throttle down something big, without having to go to a true RBAR scenario. So - not single-row operations, but still, not exactly set-based since you're kind of getting involved in the HOW part of the operation (which is usually one of the things you want to stay out of).
For example - walking a big update through a big table without swamping the server, and I decide to restrict it to 50,000 rows at a time. Kind of set-based, kind of not, but still a heck of a lot faster than RBAR.
----------------------------------------------------------------------------------
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?
June 13, 2008 at 10:18 am
Don't have time to go through all 15 pages of posts, but I fail to see where this is quicker on larger datasets.
For instance I just performed a similar operation on a dataset with about 63000 rows and am retrieving 10 columns of data.
My cursor version runs in 4 seconds. Using the in memory table and a while loop took 9 minutes.
All I am doing is reading the values into variables and manually summing one of those variables as each row is retrieved (I know I could use sum(), I was just running a test on a large dataset).
Even with smaller sets of data (sub 1000), the cursor was far outperforming the while loop. I can definitely say the issue was not with the insert into the memory table as the performance on that was exceptional. The extended processing was in the while loop itself.
June 13, 2008 at 10:22 am
Chris.Strolia-Davis (6/13/2008)
while (select count(*) from @table) > 0begin
select top 1 'do something', @id=id from @table
delete from @table where id=@id
end
Do you have an actual example of that working? Last time I tested, you couldn't mix variable assignement with something else in the same select. For example...
DECLARE @ID INT
SELECT TOP 1 'Do something', @ID=N
FROM dbo.Tally
... causes the following error...
Msg 141, Level 15, State 1, Line 3
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2008 at 10:26 am
dbishop (6/13/2008)
As a rule, I don't like to use them and try to avoid them as the 'easy way out,' but to say you NEVER use them and you HATE them show that you don't know SQL as well as you could (and I certainly don't either).
How very ironic 😀 I found that people that don't know SQL as well as they could, are the ones that use cursors and While loops for all the wrong things. :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2008 at 10:53 am
Please keep in mind - I believe that the original article was looking at SQL Server 2000. It's always possible that better options are available under 2005, or that 2005 somehow improved cursor performance.
R David Francis
Viewing 15 posts - 136 through 150 (of 296 total)
You must be logged in to reply to this topic. Login to reply