Hi,

The query mention below is taking long time to execute can any body help me
out in perfromance tuning of the query

exec sql
Declare Cursor CUR1
Select A.C1, A.C2,A.C3,A.C4
from Tbl1 A, Tbl2 B, Tbl3 C
Where
-- Join Predicates
( A.C1 = B.C1 AND
A.C2 = B.C2 AND
A.C1 = C.C1 AND
A.C2 = C.C2 )
--end predicates
AND (C.Region = CLGEN-REG)
AND (B.Date <> '00000000' )
AND
((B.Date < clgen-date)
or
(B.Date >= clgen-date
A.IND = 'Y'))
order by A.C1,A.C2,A.C3,A.C4
group by A.C1,A.C2,A.C3,A.C4
end-exec

All the tables have are accessed by the keys C1 and C2
and the table tbl1 is indexed by C1,C2,C3,C4

the Columns A.IND,C.Region and B.Date are not indexed and the DB2 plan is performing Table scan the Db2 table

Is that right to index this coulmns A.IND and Trig_dt.
A.IND will have only two value 'y' or 'N'.

How can I make my query give resultant data faster

What version and platform is DB2 in your environment?


Have you done RUNSTATS on the table - preferably with distribution statistics?

Do you really need the order by clause ? group by doesn't seem to be used.

It's difficult to tune SQL, when all you have is the SQL. What do you mean by a taking a long time? What would you consider an acceptable run time? How many rows are on each table? How current are the stats? What's the cardinality for C.Region, B.Date, and A.Ind? What are the existing indexes? Just guessing, I'd suggest considering an index on C.Region, B.Date, and maybe, just maybe, A.Ind, but the best anyone could do would be a guess.

What's the point of the GROUP BY clause? You don't do any aggregation, so this clause seems to be irrelevant to me.

What do you expect? No index means that you don't have fast access to qualifying rows. I would add indexes on the three columns and see how that works out. Maybe it doesn't deliver much benefits indexing A.IND.

Also, you seem to have a broken design if you check for something like "B.DATE <> '00000000'". Couldn't you use SQL NULL to represent non-existing values and also use the DATE data type?

Something else to consider is that "(col < :val or (col >= :val and ...))" is difficult for DB2 to evaluate using an index. You could consider how the query behaves if you use a query with a UNION ALL instead of the OR predicate.

It would be nice to see explain. I thing DB2 access plan engine is so smart to eliminate "group by" in this sample so this should not be the case of performance problem.

@praveenbl22: I would like to see explain, table definitions, index definitions, primary key definitions and foreign key definitions.

Yes, you are correct, of course. My question was more of a hint to revisit the SQL statement - because either the statement is not the true one to be executed, contains mistakes, or the one who wrote it has some misconceptions on SQL.

I agree. First think of solving SQL performance problem is understanding what it is doing. If it makes some redundant tasks, then rewrite SQL may help.

According to my experiences at least 50% times there is badly written SQL that causes performance problems. Then at 40% there is missing index and in 10% there is bad data model or doing the things that are just taking a lot of CPU power.