Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
johey
Starting Member
10 Posts |
 Posted - 2007-04-13 : 03:22:54
|
| Hello all,Everywhere I hear and read you should avoid using server side T-sql cursors. So I've followed this advice and most of the times the alternative (within a loop a query such as "select top 1 [fill variables] from [blabla] where ([pkField] > [lastPkFieldValue] or [pkField] is null)" - I guess you all know what I mean) is faster.But not always and I'm wondering why? On the other hand, to me it seems logical that executing a query one time and than looping the results _should be_ a lot faster than executing that (almost same) query over and over again?Next to this more general issue I have a specific and strange problem. Working with the cursor alternative, I've put a query in a loop. Just before and after the query I've written a print statement with the sysdate. And what do I see? Executing that query each time takes 45 secs! Okay, slow query I hear you all think. But that's not true, because when I execute the same query outside T-sql it takes less than 1 sec?!! Anybody?My eternal gratitude for any help!Regards,johey |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-04-13 : 04:11:22
|
| We can't conclude anything unless you post your code here. Also tell us about your table structures, indexes you have, where you are executing the query etc.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-04-13 : 10:01:59
|
| It is not cursors that are bad. It is looping that is bad, and cursors are one method of looping through data. By using a WHILE statement you are simply substituting one method of looping for another. Not uncommonly, you have learned that cursors are to be avoided but you have not learned WHY cursors are to be avoided.Switch to set-based operations.I once took over a job from a DBA who had written a data-loading process that ran daily. Before he left he boasted that he had not used any cursors in the process. When I looked in the code, he had used a WHILE loop instead. His process took six hours to complete every day. I rewrote it using set-based operations and it completed in six minutes. Like you, he knew WHAT, but he did not understand WHY.When rewriting loop-based code, I'm disappointed if I can't get a performance improvement of a least two orders of magnitude.e4 d5 xd5 Nf6 |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-04-13 : 10:12:07
|
| if i must loop i usually use FAST_FORWARD cursor instead of the while loop. they're comparable in terms of performance.Cursors are better for things like running totals and generaly for operations that require presorted rows.but that kind of stuff is usually better done in the front end anyway._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2007-04-13 : 14:33:39
|
| Odd's are your query is likley not written properly, and could be re-writen to outperform a cursor.Also a lot of people preffer the cursor, simply because it is a programming style they are used to if they come from .net. This is normally not the correct option, because a lot of the .net programming mentality needs to be completly re-evaluated in sql, because SQL really is a completly differant animal that is really not meant to function like a .net dataset. Post your query and your cursor, and I'm sure someone will be able to take a look at it. |
|
|
|
johey
Starting Member
10 Posts |
Posted - 2007-04-19 : 03:36:21
|
| Thanks all for answering!The cursor query is something like:select PPD.EMPLID,PPD.BIRTHDATE ,PPD.BIRTHPLACE,PPD.BIRTHCOUNTRY ,PPD.CITIZENSHIP_COLR,PPD.SEX ,PPD.LANG_CD ,PCB.COMPANY ,PCB.EFFDT ,PCB.END_DTfrom pers.ps_personal_data PPD ,pers.ps_contract_bel PCBwhere PCB.emplId + ' ' + convert(varchar,PCB.effdt) in( /* GET MOST RECENT CONTRACT */ select PCBX.emplId + ' ' + convert(varchar,max(PCBX.effdt)) from pers.ps_contract_bel PCBX group by PCBX.emplID)and PCB.emplId = PPD.emplIdand PCB.EFFDT <= @idRefDateand PCB.CONTR_DUR_BEL in ('01','02')order by PPD.EMPLID;That subquery could be replaced with an "exists", but I think it won't matter much. The first table only contains 2328 and the second 3128 records (the subquery itself also generates 2328 records).So, problem is: the query runs really fast (less than 1 second). But, when put in a loop (so not defined as a cursor), it each time takes 45 seconds. Only change in the query is this extra condition:" and (PPD.EMPLID > @lsCurEmplID or @lsCurEmplID is null)"(of course I've put an index on the emplID column). If I run that modified query outside my procedure, it also takes less than 1 sec. Blindman,This loop is one of many in a quite big conversion program. Perhaps it would indeed be better if I'd go for a set based solution, but it's a bit late now to rewrite the whole thing. :-(The procedure takes half an hour to generate 660000 records. For a temporary conversion program that's good enough.I've not invented this technique myself. I've picked it up at places like this: http://www.sql-server-performance.com/dp_no_cursors.aspRegards,johey |
|
|
|
|
|
|
|
|
cursor alternative isn't always faster?