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

[Bob Stone <bstone@fastenal.com>]

if you're creating the where clause dynamically, then yes, it will re-parse
the select statement many times, adding a lot of unnecessary overhead.
Also, when not using bind variables the SQL will sometime not use the index
that was made for that specfic SQL.  I forget the exact reasons for this,
but we recently went through a similar exercise with one of our DBAs.  We
had a quick process/report turn slow all of a sudden, because we changed
something and it stopped using a bind variable which made it  stop using the
index.  (also of concern was that reparsing of the same repetitive SQL over
and over).  If a procedure executes a fixed SQL, I don't think it reparses
it everytime.  (I'm not 100% on this, but quite sure).

I think rewriting it might solve your time issue.  (sorry if that's not the
answer you wanted to hear).

We're using an older verison of SQR and we're on Oracle 8i, so I couldn't
say if the problem we had is something fixed in later versions of
either...but because of the type of problem, I would assume it's still the
same.  

Another way to speed up the SQR running time is tuning the SQLs...making
sure they're using an index every time.  The actual processing of non-SQL
parts of SQRs is ususally negligible compared to IO/DB time.  You might
discuss this with your DBA, run a trace to find which statements in the SQR
are the slowest.  Perhaps there's one statement taking 60% of the time, and
could be tuned down to a matter of seconds?  We've run into that with our
reports/processes.  I dont know where you are on SQR tuning these reports,
so perhaps switching to non-dynamic SQL is the biggest time saver.  But to
answer your original question, yes, I think if you could rewrite them
non-dynamically that would save you parsing time (sometimes huge overhead,
depending on number of times SQL is run). 

-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of
Olga.Gal@bbh.com
Sent: Monday, April 04, 2005 10:39 AM
To: sqr-users@sqrug.org
Subject: [sqr-users] Dynamic SQL vs. Fixed SQL execution

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