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

[Olga.Gal@bbh.com]

Dear Wjatscheslaw,

To produce the report we access huge wide tables (millions of rows) that are 
joined by indexed columns. The queries are carefully tuned and executed fairly 
quickly outside the SQR.

The processing starts with an outer (driving) query, which supposed to be the 
slowest one. It would return back thousands of rows, and then 90 percent of the 
selected rows are processed further: for each processed rows the SQR then 
selects additional
information from 6 other queries that are dynamically constructed (dynamic 
SQL). These queries access the data by Primary Key, and are very fast when 
executed outside SQR.

It takes over an hour to generate a report of about 650 such entities. By 
monitoring the progression of the report it looks like the driving query (the 
slowest one) is returning the list of entities fairly quickly, but it still 
takes too long to finish
the report, which brought me to think that the problem is not really in the 
data access.



                                                                                
                                                                                
                                                                                
               
             Wjatscheslaw KRAVTCHENKO <Wjatscheslaw.Kravtchenko@unvienna.org>   
                                                                                
                                                                                
               
             Sent by: sqr-users-bounces+olga.gal=bbh.com@sqrug.org              
                                                                                
                                                                                
               
                                                                                
                                                                                
                                                                                
            To 
                                                                                
                               "This list is for discussion about the SQR 
database reporting language   from Hyperion Solutions." <sqr-users@sqrug.org>   
                     
             04/04/2005 11:50 AM                                                
                                                                                
                                                                                
            cc 
                                                                                
                                                                                
                                                                                
               
                                                                                
                                                                                
                                                                                
       Subject 
                                                 Please respond to              
                               Re: [sqr-users] Dynamic SQL vs. Fixed SQL 
execution                                                                       
                      
               "This list is for discussion about the SQR database reporting  
language from Hyperion                                                          
                                                                                
                 
                                         Solutions." <sqr-users@sqrug.org>      
                                                                                
                                                                                
               
                                                                                
                                                                                
                                                                                
               
                                                                                
                                                                                
                                                                                
               
                                                                                
                                                                                
                                                                                
               
                                                                                
                                                                                
                                                                                
               




Dear Olga,

is the SQR forming the SQL slow or is the response from the database slow?
How slow is it? Wha amount of data are we dealing with?

We had a similar scenario on Sybase 12.5 transactional data. Do you have
any samples of the SQL stmts ? How is the Warehouse structured?

Anticipating your reply.
______________________________________
Wjatscheslaw Kravtchenko (Mr.)
ProFi System Manager Assistant
ProFi Technical Team
Information Technology Service
Division for Management
United Nations Office on Drugs and Crime

Tel:  (+43-1) 26060-4153
Websites: www.unov.org, www.unodc.org
______________________________________
Impossible is not a fact, only an opinion



             Olga.Gal@bbh.com
             Sent by:
             sqr-users-bounces                                          To
             +wjatscheslaw.kra         sqr-users@sqrug.org
             vtchenko=unvienna                                          cc
             .org@sqrug.org
                                                                   Subject
                                       [sqr-users] Dynamic SQL vs. Fixed
             Monday, 4 April           SQL execution
             2005 17:38


             Please respond to
             "This list is for
             discussion about
             the SQR database
                 reporting
               language from
                 Hyperion
                Solutions."
             <sqr-users@sqrug.
                   org>










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



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



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