Hi All,

I need help on optimizing query runtime.
I am using huge tables around 90 lacs,5 lacs, etc records table.
I have tried all indexes , etc. normal stuff to optimize.
Please guide me on this..


The tables which i am using has so many fields, but the join on which fields i m doing, and thats the only which i can apply join.
common fields are as below:

Table 1 - Company No, Item No (43,000 records)
Table 2 - Company no., Facility no, Item No, Order No,Work Center No (90 Lacs Records)
Table 3- Company No, Facility No, Order No (2 Lacs Records)
Table 4- Company No,Facility No, Work Center No(1500 records)

I've Applied all proper joins on all tables.
Can u plz help me on this?

I am just preparing Query in Crystal SQL Designer.

and as a database I am using DB2.

awaiting for your reply.



Regards,
Dhaval J

what indexes do you have on those tables? What inputs are you giving in the query?
Dave

HI Dave,

I m using indexing on transaction date of Table 2(having 90 lacs records) as i m passing date range in report parameters.
as well as i have used indexes on other table on fields on which i have applied join in where condition in query.

Hope this will help u.


Thanks,
Dhaval J

Sounds like you have what is needed. One thing we have used in the past was a UDF that turned a date range into a table of all the dates in the range. This allowed us to match on columns past the date on the index of our starting table.

For instance, the index had columns of date, type, status. With the following SQL we got matching 1 col on the index:

Whereas, something like the following SQL would get us matching all 3 columns on the index:
Dave Nance