Page 1 of 1
Tune a query with where clause that has a function
Posted: Sun Apr 14, 2013 10:54 pm
by jerome_rajan
Hi,
Our Db2 connector is extracting data from a database with a query that looks something like
Code: Select all
Select * from <tablename>
WHERE SUBSTR(ACCT_TYPE) IN (a,b,c,d)
AND bal<0;
The table has more than 50 million records. The connector takes a very long time to read this data. In 1.5 hours, it managed to read only 1.5 million rows. Any advise on how to tune this query? We have an index on ACCT_TYPE.
Posted: Sun Apr 14, 2013 11:47 pm
by chandra.shekhar@tcs.com
Jerome, try fetching whole data from the source and then use a filter stage and give the constraints in it.
Posted: Mon Apr 15, 2013 12:01 am
by jerome_rajan
Already tried that and the job is processing. Will report once done
Posted: Mon Apr 15, 2013 4:02 am
by ray.wurlod
What does SUBSTR with only one argument do? Are you forcing implicit CASTs in the IN list? What are a, b, c and d? Is ACCT_TYPE indexed?
Posted: Mon Apr 15, 2013 4:10 am
by jerome_rajan
I meant only to give a generic idea that I'm using a SUBSTR. The actual query consists of SUBSTR(ACCT_TYPE,1,2). Acct Type is a 10 character string. The first 2 characters represent product type which we are trying to filter in the IN list. I don't think we are forcing implicit CASTs since a,b,c,d are strings of length 2. Like I said earlier, ACCT_TYPE is indexed.
Posted: Mon Apr 15, 2013 7:24 am
by chulett
jerome_rajan wrote:I meant only to give a generic idea that I'm using a SUBSTR.
Just an FYI but that's never a good idea. When people post "generic" examples or "something like" whatever it is that they are actually doing we can spend time chasing our tails until what they are actually doing is clarified.
If DB2 is anything like Oracle, you've invalidated the use of the index by using a function on it. Oracle allows you to build a "Function Based Index" on the table which is a result of (for example) your substring so it can be leveraged by the query. Does DB2 have an equivalent?
Posted: Tue Apr 16, 2013 8:17 am
by prasannakumarkk
What percentage of total number of rows satisfy your condition. If the stage is fetching less number of rows then it means there are few records satisfying the condition. If that is not the case why dont you try the negation?