Archives of the TeradataForum

Message Posted: Fri, 13 Sep 2002 @ 20:30:58 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		All in all

From:		Anantaraman, Kumaran (K.)

Hi,

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.

Question:

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 generates

explain select * from db1.test where ( col_country_code in ('usa', 'can')) or ( ('all') in ('usa', 'can') ) ;

`Explanation --------------------------------------------------`
	`1)`	`First, we lock a distinct db1."pseudo table" for read on a RowHash to prevent global deadlock for db1.test.`
	`2)`	`Next, we lock db1.test for read.`
	`3)`	`We do an all-AMPs RETRIEVE step from db1.test by way of an all-rows scan with a condition of ("('all'= 'all') OR ((db1.test.col_country_code = 'usa') OR (db1.test.col_country_code = 'can'))") into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with no confidence to be 1,539 rows. The estimated time for this step is 0.19 seconds.`
	`4)`	`Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.`
	`->`	`The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.19 seconds.`

SQL 2 - The good one

explain
select
  *
from
  db1.test
where
   ( col_country_code in ('usa', 'can'))
;

`Explanation --------------------------------------------------`
	`1)`	`First, we do a single-AMP RETRIEVE step from db1.test by way of the unique primary index "db1.test.col_country_code = 'can'" with no residual conditions into Spool 1, which is built locally on that AMP. The size of Spool 1 is estimated to be 1 row. The estimated time for this step is 0.03 seconds.`
	`2)`	`Next, we do a single-AMP RETRIEVE step from db1.test by way of the unique primary index "db1.test.col_country_code = 'usa'" with no residual conditions into Spool 1, which is built locally on that AMP. The size of Spool 1 is estimated to be 2 rows. The estimated time for this step is 0.03 seconds.`
	`3)`	`Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.`
	`->`	`The contents of Spool 1 are sent back to the user as the result statement 1. The total estimated time is 0.06 seconds.`

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!>

Thanks !

Kumaran Anantaraman


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference