| Author |
Topic |
|
jeroena
Yak Posting Veteran
76 Posts |
 Posted - 2005-02-16 : 05:39:55
|
| Hi,Business Objects generates a query which looks something like:SELECT ...FROM ...WHERE table_a.a = (SELECT MAX(b) FROM table_b WHERE c=d) AND table_a.a = (SELECT MAX(b) FROM table_b WHERE c=d) AND table_a.a = (SELECT MAX(b) FROM table_b WHERE c=d)Okay, I know I should do someting about that in my universe, BUT:the SQL Server (2000) query optimizer doesn't detect, that i'm putting in the same condition three times! Should it, and why not? It really freaks out on this, and the query keeps on running forever. When I put in the condition just once, it runs in a few seconds. The subquery contains a simple lookup in a few-row table. I just would suspect putting in the same condition more than once, wouldn't effect the query-plan at all.Thnx!Jeroen. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-02-16 : 05:48:15
|
it acctually doesn't recognize it. it takes each condition and applies it. so if you do it 3 times it will apply it 3 times.to know that would probably need some fuzzy logic  run this in QA and look at the exec plan. and then try all 3 conditions.use northwindselect * from orderswhere employeeid = (select max(employeeid) from orders) --and employeeid = (select max(employeeid) from orders) --and employeeid = (select max(employeeid) from orders)Go with the flow & have fun! Else fight the flow  |
 |
|
|
jeroena
Yak Posting Veteran
76 Posts |
Posted - 2005-02-16 : 05:52:36
|
Exactly! Well, I guess "Stupid query = Stupid Plan" applies here...  Thnx. J. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-16 : 08:08:34
|
| I haven't used Business Objects in a while (v3.something) but it was ALWAYS creating bad queries. If it's not set up properly you get all kinds of crap. Funny thing is that if someone is competent enought to configure it right, they probably wouldn't bother using Business Objects anyway. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-02-16 : 09:54:55
|
| use a stored procedure, don't let BO create a query for you ....- Jeff |
|
|
|
Xerxes
Aged Yak Warrior
666 Posts |
Posted - 2005-02-16 : 10:03:18
|
quote:
Originally posted by jsmith8858
use a stored procedure, don't let BO create a query for you ....- Jeff
I second that, Jeff! SPROCS are the way to go. Semper fi, Xerxes, USMC(Ret.)-------------------------------------------------------------------------Once a Marine Programmer Analyst ALWAYS a Marine Programmer Analyst |
|
|
|
jeroena
Yak Posting Veteran
76 Posts |
Posted - 2005-02-16 : 10:11:04
|
| We have a data warehouse environment in different Business Objects universes to build reports on a central data warehouse database. How on earth can you use sp's to provide the query's for all reports a user might want? |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-02-16 : 11:00:03
|
| That is true, for ad-hoc reporting, you may have no other option. I generally agree with you on that. Some solutions are to provide the users with Views they can use as building blocks, and/or to make sure they understand at least the basics of SQL so they can manually edit the statmenets generated by the Business Objects query designer.I guess the best thing you can do is, if a particular report does take along time to run and uses lots of resources, set up a system whereby users feel comfortable asking the DBA for assistance and in those cases you can quick cut and paste their generated SQL and then them back something more efficient, and/or create a specific stored proc for those cases and show them how to use it in a report.The key thing is a) anyone building reports at least has a basic understanding of SQLb) You have set up anything you can do to make things easier for the users using Viewsc) Users feel comfortable and it is easy for them to approach the DBA for help with SQL statements if they need tweaking/adviced) a clear and simple procedure is set up for which users can request (or you should require) stored procs to be created for complex reportsAS always, communication + education is key.- Jeff |
|
|
|
jeroena
Yak Posting Veteran
76 Posts |
Posted - 2005-02-17 : 02:00:35
|
| Hi Jeff,What we try to do, is build a dimensional model (star schemes with fact and dimension tables). On that model we build universes. This should result in a model where all complex calculations are done by the transformation proces which runs each night (which are in fact a lot of stored procedures :). The dimensional model itself is optimized for report queries.I agree that it helps tremendously if users have some basic knowledge of how things work under the hood. However, I don't think any organisation should accept information supplied by systems in which users have to paste SQL from their DBA's into query panes. Let alone letting users modify the SQL statements themselves.But I agree, in small organisations, with a few complex reports and no special reporting tools, sp's are the way to go...Jeroen. |
|
|
|
|
Not so clever plan for a not so clever query