Re: [sqr-users] sqr tuning question

["Christopher Wanko" <WankoC@wyeth.com>]

>>> 
Hello,(Oracle, Unix)I have a select statement that takes around 7 seconds to 
complete in SQL*PLUS.  In my SQR I use the sql statement and do lots of 
processing for each row returned(total of couple thousand rows).  
>>>

Your answer is right there.

If you've ever coded a stored procedure, you know that cursor processing is 
time-consuming.  Executing it on the server still requires examination of row 
data to determine a processing path.  With SQR, you choose to perform this 
cursor processing on the client rather than the server.

So, to speed things up for you, you can write SQL UPDATE or INSERT statements 
to perform (some of) the processing for you - IIF you can perform those 
operations on a set.  My guess is you can't, so there's another technique you 
can use.  Select all the rows at once and store them in an array or in a file.  
Perform your row-by-row processing in SQR as you might any procedural language 
(COBOL, C), and then post the results back as a single INSERT or UPDATE, as 
needed.

This is the technique that shops with big (million-plus row) data processing 
tasks do.

-Chris




_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users