CODEWITHSUNDEEP
sqlsap-ase
Andreas Dolk
Andreas Dolk

Reputation: 114767

while loop with cursor and dynamic SQL doesn't terminate

I like to write a procedure that return the name of each table, that has a row with specific id. I other words, the tables have a column 'id' which is of type varchar and contains an uuid. After doing some research, I chose the following approach (simplified, focussing on the problem that I can't solve/understand):

-- get a cursor for all foo table names that have an id column
DECLARE table_name_cursor CURSOR FOR
SELECT a.name
FROM sysobjects a, syscolumns b 
WHERE a.id = b.id 
    AND a.name like 'Foo%'
    AND b.name = 'id'

GO

-- define some variables
DECLARE @current_table_name VARCHAR(100)
DECLARE @id_found VARCHAR(100)

OPEN table_name_cursor
FETCH table_name_cursor INTO @current_table_name
WHILE @@SQLSTATUS = 0
BEGIN
    EXEC ('SELECT @id_found = id from ' + @current_table_name + " where id = '@id_param'") -- @id_param will be passed with the procedure call
    select @current_table_name  
    FETCH table_name_cursor INTO @current_table_name
END

-- clean up resources
CLOSE table_name_cursor
DEALLOCATE table_name_cursor

It works as expected, when the size of the cursor is fairly low (~20 tables in my case) but if the cursor size grows, then the procedure never terminates.

It smells like a resource problem but my white belt in Sybase-Fu doesn't help finding the answer.

Question: why does it stops working with 'too many' cursor rows and is there a way to get it working with this approach?

Is there an alternative (better) way to solve to real problem (running queries on all tables)? This is not intended to be used for production, it's just some sort of dev/maintenance script.

Upvotes: 0

Views: 1254

Answers (1)

markp-fuso
markp-fuso

Reputation: 34134

It might help to have some context around your comment "it stops working", eg, does the proc return unexpectedly, does the proc generate a stack trace, is it really 'stopped' or is it 'running longer than expected'?

Some basic monitoring should help figure out what's going on:

  • does sp_who show the cursor process as being blocked (eg, by other processes that have an exclusive lock on data you're querying)
  • do periodic queries of master..monProcessWaits where SPID =<spid_of_cursor_process> show any events with largish amounts of wait time (eg, high wait times for disk reads; high wait times for network writes)
  • do periodic queries of master..{monProcessStatement|monProcessObject} where SPID = <spid_of_cursor_process> show cpu/wait/logicalreads/physicalreads increasing?

I'm guessing some of your SELECTs are running against largish tables with no usable index on the id column, with the net result being that some SELECTs are running expensive (and slow) table and/or index scans, possibly having to wait while large volumes of data are pulled from disk.

If my guess is correct, the MDA tables should show ever increasing numbers for disk waits, logical/physical reads, and to a lesser extent cpu.

Also, if you are seeing large volumes of logical/physical reads (indicative of table/index scans), the query plan for the currently running SELECT should confirm the use of a table/index scan (and thus the inability to find/use an index on the id column for the current table).

For your smaller/faster test runs I'm guessing you're hitting either a) smaller tables where table/index scans are relatively fast and/or b) tables with usable indexes on the id column (and thus relatively fast index lookups).

Something else to consider ... what application are you using to make your proc call?

I've lost track of the number of times where a user has run into some ... shall I say 'funky' issues ... when accessing ASE; with the issue normally being tracked back to a configuration or coding issue with the front-end/client application.

In these circumstances I suggest the user run their query(s) and/or procs via the isql command line tool to see if they get the same 'funky' results; more often than not the isql command line session does not show the 'funky' behavior, thus pointing to an issue with whatever application/tool the user is has been using to access ASE.

NOTE: By isql command line tool I mean exactly that ... the command line tool ... not to be confused with wisql or dbisql or any other point-n-click GUI tool (many of which do cause some 'funky' behavior under certain scenarios).

NOTE: Even if this turns out to be a client-side issue (as opposed to an ASE issue), the MDA tables can often pinpoint this, eg, monProcessWaits might show a large amount of wait time while waiting for output (to the client) to complete; in this scenario sp_who would also show the spid with a status of send sleep (ie, ASE is waiting for client to process the last result set sent by ASE to the client).

Upvotes: 1

Related Questions