[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
Re: [sqr-users] Dynamic SQL vs. Fixed SQL execution
Dear Olga,
as I said we had a similar problem with the performance of our reports, we
rewrote the warehouse. Now we denormalize the data 4 times a day and serve
the reports instantly. No joins, no aggregates, everything is
precalculated.
I think that by tuning SQL/SQR you'll not be able tosignificantly speed up
the performance. At least we made this experience, which forced us to
rethink the warehouse structure.
Regards,
______________________________________
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 "This list is for discussion about
vtchenko=unvienna the SQR database reporting language
.org@sqrug.org from Hyperion Solutions."
<sqr-users@sqrug.org>
cc
Monday, 4 April
2005 18:22 Subject
Re: [sqr-users] Dynamic SQL vs.
Fixed SQL execution
Please respond to
"This list is for
discussion about
the SQR database
reporting
language from
Hyperion
Solutions."
<sqr-users@sqrug.
org>
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
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users