Problems with cursor selecting from a view
-
OK just had a really weird thing happen at a client's -- a sproc with a read only, forward only cursor selecting from a view. The view returns something like 26000 records in about 3 seconds from within QA. Within the sproc, it hangs at the first fetch after the OPEN CURSOR stmt. Running the sproc code from within QA exhibits the same behaviour. Traces reveal nothing, debugging the sproc just confirms the hang at the fetch stmt.
Our client, who is a sharp developer in his own right, did some digging aound and got the cursor to work only when he removed the ORDER BY clause from within the view.
Incorporating the ORDER BY clause in the select stmt in the cursor declaration worked ok, too, so his original logic for the sproc was preserved.
Weird, huh? Anyone ever see this or something similar happen? We have used ORDER BY clauses in views for a while and have never run into issues.
Vik
-
Why r u using a cursor to select 26k rows in a SP?
-
-Can you post the @@fetch_status ? or it doesn't even gets there?
-Order by should never be used in views. Those are just tricks that harm performance you can always use the order by outside of the view.
-Can you post the cursor's declare statement ?
* Noel -
noeld,
The sproc never gets past the first fetch. Here's the declaration:
Declare CreateTicketsCursor Cursor Local Forward_Only Read_Only For Select CustomerId, BranchesId, CustInvoiceGroupId, CustTicketGroupId, CalculationType, ChainId, MonthEnd, CustomerServiceId, InvoiceAmount, ServiceAmount, RenewalAmount, Scored From vCreateTickets Where (JanFreq > @v_JanMax Or FebFreq > @v_FebMax Or MarFreq > @v_MarMax Or AprFreq > @v_AprMax Or MayFreq > @v_MayMax Or JunFreq > @v_JunMax Or JulFreq > @v_JulMax Or AugFreq > @v_AugMax Or SepFreq > @v_SepMax Or OctFreq > @v_OctMax Or NovFreq > @v_NovMax Or DecFreq > @v_DecMax)
During debugging, the where clause was replaced with a simpler one returning only one row, with the same "hanging" result. The variable list was also replaced with just one column returned, again with the same results.
As far as cursors go, the read_only, forward_only options should make it relatively zippy, esp'ly when compared to the default dynamic cursor type.
Thx for any input.
Vik
-
You still didn't answer the real question. Why the )(&?(*& r u fetching 26k rows in a cursor in a sp. I can't imagine that there's no way around this process.
-
>>Within the sproc, it hangs at the first fetch after the OPEN CURSOR stmt.
How do you *know* it hangs ? What are you using to diagnose this ? Maybe it's just behaving normally, where normal in the case of a 26K row cursor is veeeeerrrryyyy sllllooooowwwwwyyyy
-
Remi,
Of course, there are many ways around this process. Don't be so quick to judge my situation, however.
The decision to fetch so many rows within a sproc was made in order to perform some further intensive processing on each row. In this case, considering the processing involved, the current state of the database, the role of this sproc in the overall application's purpose, code maintainability, and the relatively low impact of read_only, forward_only cursors in relation to the cursor's overall usefulness, it ain't so bad, ok? Once we got it working, the sproc took less than 3 minutes to run, for a process that will eventually run exactly once a month during off hours.
And having to with a client who is also a developer and wants input as to the development and design of his own database presents it's own set of challenges.
Now that hopefully this justification has satisfied you, I would appreciate some insight on determining why an ORDER BY clause in a view would have such an effect on a cursor declaration.
Vik
-
Can't answer your question as I avoid cursor like the pest. Good luck... btw I also avoid order by in views. Nice to see best practices pay off
. -
PW,
1. Removing the ORDER BY clause magically made everything work again.
2. We had just recently done a similar test, different database, same situation, same cursor type, approx'ly 80k records processed in < 3 minutes.
Yeah, yeah, yeah, I've heard all the bad stuff about cursors and discourage their use whenever I can. And yeah, cursors are slow and resource intensive, but they ain't THAT slow. And it shouldn't take minutes to retrieve the first row. If anything, the whole cursor loop should dog it. In the second point above, the default dynamic cursor type took over an hour to run. Going to a read_only, forward_only cursor brought the time down by a factor of 25.
Vik
-
By Looking at the declaration of your cursor you have created a DYNAMIC type and needless to say that is far from optimal (From BOL:The data values, order, and membership of the rows can change on each fetch). Would you mind to change the FORWARD_ONLY, READ_ONLY to FAST_FORWARD and try again?
hth
* Noel -
>> And it shouldn't take minutes to retrieve the first row.
That depends on many factors.
An ORDER BY potentially causes the optimizer to select a different query execution plan. Depending on indexing, fragmentation, currency of the statistics on the indexes etc, the optimizer may choose a less than optimal plan. In the case of an ordered result set, you can't get the 1st row until ALL the rows have been retrieved and sorted, and if the underlying execution plan for ALL those rows uses an inefficient method, you will see a large wait time for the 1st row.
-
>> We had just recently done a similar test, different database, same situation, same cursor type, approx'ly 80k records processed in < 3 minutes.
I would bet a set based can do it in seconds
* Noel -
Finally someone else pointed out the obvious
. -
noeld,
A DYNAMIC cursor? OK.. I thought that FAST_FORWARD was equivalent to FORWARD_ONLY READ_ONLY, but I just noticed that the description for FORWARD_ONLY states that unless STATIC, KEYSET, or DYNAMIC is specified, the cursor is declared as DYNAMIC.
Shoulda read more closely...
Thanks, noeld.
Vik
-
And if you keep reading more carefully under the FAST_FORWARD it says:
Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled
* Noel
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply