RE: [sqr-users] Dynamic SQL vs. Fixed SQL execution

["the dragon" <ceprn@hotmail.com>]

You can see how many times the sql compiles, as well as how many times it 
executes, and the number of rows returned by using the -s parameter when you 
run the program.

peace,
clark 'the dragon' willis



PSA: Salary <> Slavery. If you earn a salary, your employer is renting your 
services for 40 hours a week, not purchasing your soul. Your time is the 
only real finite asset that you have, and once used it can never be 
recovered, so don't waste it by giving it away.

I work to live; I don't live to work.

"Time is the coin of your life. It is the only coin you have, and only you 
can determine how it will be spent. Be careful lest you let other people 
spend it for you."

Carl Sandburg
(1878 - 1967)

----Original Message Follows----

We're using SQR ver 8.1 with Oracle 9i in the datawarehousing environment, 
which produce reports with huge amount of data and run VERY slow.

Our SQR programs construct the Where clause and Order By clause dynamically, 
based on user's selection, and execute the same Select statement many times 
within the same job. This makes me think that the slow performance is 
possibly caused in part by
repeating parsing of these dynamically constructed Select statements.

Before taking a dramatic decision of changing the reports to have only fixed 
Select statements with bind variables ONLY in the Where clause, I would like 
to get assured that this approach will in fact eliminate unnecessary parsing 
(compilation) of SQL
statements. My question is:

Is the SQR  compiling each SQL statement before executing it, or would it do 
it only for a newly executed SQL. In other words, if my program will 
repeatedly call a routine that executes a fixed SQL statement, will this 
statement get parsed every time it's
being executed, or only the first time.

Also, are there any other ways to speed up that time of processing?

Any advice is greatly appreciated.



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