472,952 Members | 2,079 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,952 software developers and data experts.

Performance Question: Cursor-based vs Set-based

I was researching opinions on using cursors in stored procedures and found a thread http://www.thescripts.com/forum/thread143091.html discussing (generally) why not to use them.

I took a cursor-based solution and took a stab at writing the equivalent to it as a set-based procedure. I then timed each process using Query Profiler by executing each 20 consecutive times in Query Analyzer (i.e. EXECUTE sp_CalculateRewards and EXECUTE sp_CalculateRewards2) With respect to data size and row counts, of the approximately 12,000 rows in the customer table, only 85 records meet the criteria for processing.

Perhaps my set-based solution is way off, but the cursor based solution consistently ran in the 500ms range (avg 525ms), while my set-based one ran (progressively better) in cycles of 4000ms, 2000ms, 600ms, 4000ms...etc... repeating this pattern over the 20 repeated executions.

My two questions are this. First, does my set-based approach seem reasonably correct or is there a better way. Second, has anyone seen the same anomaly with respect to the timings and is there a reason for it?

Thanks for your input!

The cursor based solution: delete all records from reporting table...open cursor for those customers from the customer table with an enrollment date...calculate sales for prior 365 days from the enrollment date and then year to date sales since enrollment date...build an INSERT statement and EXECUTE the statement thus inserting the customer_id, customer_name, and respective sales figures back into the reporting table...and continue for each customer_id in the cursor resultset.

The set-based version:

DELETE FROM RewardsTable

INSERT RewardsTable (customer_id, customer_name, enrollment_date, sales_base, ytd_sales, percent_to_goal) SELECT customer_id, customer_name, cert_date, 0, 0, 0) FROM Customer WHERE ISDATE(cert_date) = 1

SELECT rt.customer_id CASE WHEN sum(inv.invoice_amount) IS NULL THEN 0 ELSE sum(inv.invoice_amount) END [sales_base] INTO #rt_sales_base FROM RewardsTable rt LEFT OUTER JOIN invoice_view inv WITH (NOLOCK) ON rt.customer_id = inv.customer_id WHERE inv.invoice_date >= rt.enrollment_date - 365 AND inv.invoice_date <= rt.enrollment_date

SELECT rt.customer_id CASE WHEN sum(inv.invoice_amount) IS NULL THEN 0 ELSE sum(inv.invoice_amount) END [ytd_sales] INTO #rt_ytd_sales FROM RewardsTable rt LEFT OUTER JOIN invoice_view inv WITH (NOLOCK) ON rt.customer_id = inv.customer_id WHERE inv.invoice_date >= rt.enrollment_date AND inv.invoice_date <= rt.enrollment_date + 365

UPDATE RewardsTable SET sales_base = tmp.sales_base FROM RewardsTable INNER JOIN #rt_sales_base tmp ON RewardsTable.customer_id = tmp.customer_id

UPDATE RewardsTable SET ytd_sales = tmp.ytd_sales FROM RewardsTable INNER JOIN #rt_ytd_sales tmp ON RewardsTable.customer_id = tmp.customer_id

DROP TABLE #rt_sales_base
DROP TABLE #rt_ytd_sales
Apr 21 '06 #1
1 7361
I did some additional testing. I populated an enrollement_date in for all customers such that all ~12000 customer records would be processed, and notice that the CURSOR-based stored procedure was no longer faster than the set-based one. So I now have a third question. At some point the dataset became large enough that the optimations chosed on the execution path passed up the cursor based solution. Is it fair to say that (if your not worried about running in a non-MS SQL environment) the CURSORS on smaller sets of data hold a performance advantage?
Apr 22 '06 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Mage | last post by:
Hello, I started to write my PostgreSQL layer. I tried pyPgSQL and PyGreSQL. I made a *very minimal* performance test and comparsion with the same thing in php. Table "movie" has 129 record and...
by: Michael Beumers | last post by:
Hello NG I've defined a cursor like the following in my COBOL Programm: DECLARE testc CURSOR FOR SELECT ... FROM ... WHERE field1 LIKE :hostvariable1 field2 LIKE :hostvariable2
by: Andrew Werden | last post by:
I've inherited some code that calls SYSIBM.SQLPROCEDURES to validate user provided parameters and text prior to executing a stored procedure. This code was written on an early UDB release (V6?)...
by: Joachim Klassen | last post by:
Hi all, first apologies if this question looks the same as another one I recently posted - its a different thing but for the same szenario:-). We are having performance problems when...
by: Filip Stas | last post by:
I would like to have some information about the cooperation between DB2 Connect for .Net en DB2 data base for z/OS390. We have a scenario where a .net client will call a DB2 Stored Procdure...
by: P. Adhia | last post by:
Sorry for quoting an old post and probably I am reading out of context so my concern is unfounded. But I would appreciate if I can get someone or Serge to confirm. Also unlike the question asked in...
by: heming_g | last post by:
i see in this forum to make this routine using "DELETE FROM (SELECT 1 FROM <name> WHERE <condition> FETCH FIRST <n> ROWS ONLY) Prepare this one ONCE then EXECUTE in the loop. " it works . ...
by: Rajesh Jha | last post by:
I'm analyzing a query in which "SUBSTR' parameter is used. I replaced it by LIKE parameter. I tested the query in SPA(SQL Perfromance Analyzer) & found a saving of 50% in cost & 37.8% in CPU time. ...
by: marc_r_bertrand | last post by:
To all asp/db pros: The quiz code below works. But there is a problem when too many questions are answered (radio buttons clicked). I am not an asp pro. So, is there a pro out there or an...
by: oravm | last post by:
Hi, I re-write a query and used bulk collect to improve the performance of the batch process. The query below has NO compile error but when execute query there is error 'ORA-01403: no data...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.