Help : Complex SQL query

[Kumar]

Hi

Need a help in rewritting the folwoing SQL which is taking more than a
day to give resultset. Is there anything wrong in this SQL coding ?

SELECT DISTINCT 214 AS BATCH_ID,
TAB1.customer_sk,
TAB1.CUSTOMER_ID,
TAB1.product_line_sk,
COUNT(DISTINCT TAB1.ACCOUNT_SK) AS COUNT
FROM SCHEMA1.TABLE1 TAB1
WHERE TAB1.acc_opened IN (SELECT datetime
from SCHEMA1.TAB2

where day between (select day-30 from SCHEMA1.TAB2 where datetime =
(SELECT batch_date FROM SCHEMA2.TAB3 WHERE BATCH = 214)) and (select
day from SCHEMA1.TAB2 where datetime = (SELECT batch_date FROM
SCHEMA2.TAB3 WHERE BATCH = 214)))

GROUP BY TAB1.customer_sk, TAB1.CUSTOMER_ID, TAB1.product_line_sk
HAVING COUNT(DISTINCT TAB1.ACCOUNT_SK) > 1

[Blair Adamache]

What version and release of DB2? Which operating system?
Do the tables have accurate statistics (RUNSTATS)? Are any of the tables
partitioned (and what are the partitioning keys)? What indexes do you
have created? Have you tried the index advisor? How many rows are in
each table? The answers to these questions may help DB2 to optimize this
query without forcing you to hand-tune it.

Kumar wrote:

Hi

Need a help in rewritting the folwoing SQL which is taking more than a
day to give resultset. Is there anything wrong in this SQL coding ?

SELECT DISTINCT 214 AS BATCH_ID,
TAB1.customer_sk,
TAB1.CUSTOMER_ID,
TAB1.product_line_sk,
COUNT(DISTINCT TAB1.ACCOUNT_SK) AS COUNT
FROM SCHEMA1.TABLE1 TAB1
WHERE TAB1.acc_opened IN (SELECT datetime
from SCHEMA1.TAB2

where day between (select day-30 from SCHEMA1.TAB2 where datetime =
(SELECT batch_date FROM SCHEMA2.TAB3 WHERE BATCH = 214)) and (select
day from SCHEMA1.TAB2 where datetime = (SELECT batch_date FROM
SCHEMA2.TAB3 WHERE BATCH = 214)))

GROUP BY TAB1.customer_sk, TAB1.CUSTOMER_ID, TAB1.product_line_sk
HAVING COUNT(DISTINCT TAB1.ACCOUNT_SK) > 1

[Serge Rielau]

Try this one. I took out 2 joins.. should make a difference.

SELECT DISTINCT 214 AS BATCH_ID,
TAB1.customer_sk,
TAB1.CUSTOMER_ID,
TAB1.product_line_sk,
COUNT(DISTINCT TAB1.ACCOUNT_SK) AS COUNT
FROM SCHEMA1.TABLE1 TAB1
WHERE TAB1.acc_opened
IN (SELECT T1.datetime
from SCHEMA1.TAB2 T1,
SCHEMA1.TAB2 T2,
SCHEMA2.TAB3 T3
where T1.day between T2.day-30 AND T2.day
and T2.datetime = T3.batchdate
and BATCH = 214)
GROUP BY TAB1.customer_sk,
TAB1.CUSTOMER_ID,
TAB1.product_line_sk
HAVING COUNT(DISTINCT TAB1.ACCOUNT_SK) > 1
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Buck Nuggets]

ku***@tumkurcity.com (Kumar) wrote in message news:<2f**************************@posting.google. com>...

Hi

Need a help in rewritting the folwoing SQL which is taking more than a
day to give resultset. Is there anything wrong in this SQL coding ?

Well, here's a few things to try:
1. run stats on all tables and try again to see if your performance
improves
2. look at the results of an explain for bad things
3. put indexes on tab3.batch, tab2.datetime, tab1.acc_opened
4. replace date-oriented subselects with joins, ideally with a temp
table with 30 days of data that correspond to the range you want.
5. or replace date-oriented subselects with values from prior queries

oh, and get rid of the distincts - if you've got duplicates then
you're probably not grouping properly.

ken