Skip to Content
author's profile photo Former Member
Former Member

Keys

Post Author: Jon80

CA Forum: General Feedback

Since querying is more efficient when using keys, I thought I'd update the objects in my universe (right click on object > keys). When i try to select a key manually (as in the case of complex data views using derived sql), the select statement does not seem to be generated correctly.

This happens even though parsing returns no errors. On the other hand automatic detection of keys (on MS SQL 2005) works fine. Any idea how to update the queries manually?

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Dec 03, 2007 at 02:44 PM

    Post Author: jsanzone

    CA Forum: General Feedback

    Jon,

    I think you just have to trust that the SQL Query Optimizer will internally optimize the query at runtime. My workmates and I too have an internal conflict when looking at the resultant SQL code after building a report thinking that the primary table should be joining to the reference table and not the other way around. However, when the query enters into the server it is optimized at that point and then runs, so that with few exceptions, and though a convoluted looking query to you and I is "corrected" when it runs. One of the limitations to this (and thankfully not a persistent occurrence with Business Objects but with other applications) is how an OR and IN are handled. For instance, you want to find people who are right-handed or left-handed so you and I would create code such as: where [MYTABLE.DEXTERITY] in ('RIGHT','LEFT'); however, in certain other applications it might come out as: where [MYTABLE.DEXTERITY] = 'RIGHT' or [MYTABLE.DEXTERITY] = 'LEFT', etc, etc. The use of the IN capability is far superior to OR, especially when the list can be twenty items long, --all those OR statements will get bogged down and will take the query longer to run, and the optimizer will not "catch" this poor construct. So, I say run the report as generated and see how long it takes, then perhaps capture the SQL, re-configure to what you think is best and run the SQL in another application (Excel, Access, or direct) and note how long that takes, and chances are the two timings will be pretty close providing that all other things server-wise remain the same.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.