SQL performnace...

What database engine is this, MSSQL or DB2?

You need to add an index to the IVM.MAKENAME column

DB2 database.

Is Query looks ok?

Since your select looks valid, let's make sure you have the appropriate indexes (and primary keys) in place.

CATENTRY.CATENTRY_ID
IMC_ABC_PARTNO.CATENTRY_ID
IMC_ABC_PARTNO.ABC_NO
IMCCRP.IMC_APPLICATION.APPLICATION_NUMBER
IMCCRP.IMC_APPLICATION.BASE_VEHICLE_ID
IMC_VEHICLE_MQT.MAKENAME, IMC_VEHICLE_MQT.BASEVEHICLEID

Of course, you'd find A LOT of useful information on performance by doing a Visual Explain on the query.

Create those indexes (or ensure they already exist), do a Visual Explain, and report back to us how it works for you.

HTH,
DaveSlash


Regards,
DaveSlash

Also, how many rows are in each table, and how long does the query actually take to run?

Thank you so much for time..

I have  indexes on all tables..
IMC_APPLICATION table has 2 million records

IMC_ABC_PARTNO : 70K records

Catentry : 122K records

IMC_VEHICLE_MQT :14295 records

SQL execution time: 2 seconds.

I want to be reduce the sql execution time.We have so many calls for this query.I do not want to lock the tables for 2 sceonds. It is locking the other transactions..

When you say "I have  indexes on all tables", does that mean you have all the indexes I suggested?

Also, what did you find out when you did Visual Explain?

Hi!

I would try these indexes if you don't have them.

On table IMC_ABC_PARTNO  the index (CATENTRY_ID, ABC_NO)
On table IMC_APPLICATION the index (APPLICATION_NUMBER, BASE_VEHICLE_ID)
On table IMC_VEHICLE_MQT the index (MAKENAME, BASEVEHICLEID)

Having index on only one column is often not enough for complex queries.
Hope this helps.

Regards,
     Tomas Helgi

Hi vvsrk76,

It's been several days since we heard from you. Did you get your query to perform acceptably? If so, how?

-- DaveSlash

We are using the MQT's in DB2..

We are trying recreate the MQT.

Hi, is this solved?

Will you please decide if your question is answered.

Thank You