Archives of the TeradataForum
Message Posted: Fri, 13 Sep 2002 @ 20:30:58 GMT
I am facing an issue where Business Objects (BO) creates a badly formatted SQL which leads to poor performance. I sort of understand why the BO guys create such reports, but I want to know if we can code BO so that it does not write out bad SQL.
Here is the background: BO provides a list of values (lov) on screen to the user. Lets take example of country codes. Users can pick one country, many countries or all countries. However, if the list of countries is large, we do not want to burden the user from picking all countries (even with shift select). So BO guys DEFINE the object so that users can type ALL, and the SQL that BO (object) generates is in the format of SQL1 below. This is a common 'trick' in BO coding. In SQL1, the user has actually picked only 2 countries. The SQL1 is bad, because it stops Teradata from using the PI. This leads to serious issues for complex queries. I have seen queries that don't complete for 30 mins, and when I remove the redundant 'all' clauses from the where, it runs in 1 min.
1) how can we code BO so it will not write the ' or 'all' in (
2) how do other tools (crystal reports, microstrategy) handle such situations?
3) If we cannot stop BO from generating such SQL, what tuning can we do to help Teradata
--- reference --- create table db1.test ( col_country_code char(3) ) unique primary index (col_country_code ) ; collect stats on db1.test index (col_country_code) ; help statistics db1.test ; Date Time Unique Values Column Names 02/09/12 13:46:33 15,362 col_country_code
SQL1 - The bad one that BO generatesexplain select * from db1.test where ( col_country_code in ('usa', 'can')) or ( ('all') in ('usa', 'can') ) ;
SQL 2 - The good one
explain select * from db1.test where ( col_country_code in ('usa', 'can')) ;
SQL 3 - The ugly one
When we join this with other big tables, things get ugly. I have had many cases, when a query did not complete after 10 mins, and when I remove the 'all' clauses, it runs in 1 min!>
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|