SQL performnace...

The SQL is taking long time for me. Please help me in this.

SELECT   Distinct CAT.CATENTRY_ID,
         CAT.PARTNUMBER
FROM     CATENTRY CAT
         JOIN IMCCRP.IMC_ABC_PARTNO ABC ON CAT.CATENTRY_ID = ABC.CATENTRY_ID
         JOIN IMCCRP.IMC_APPLICATION AN ON AN.APPLICATION_NUMBER = ABC.ABC_NO
         JOIN IMCCRP.IMC_VEHICLE_MQT IVM ON IVM.BASEVEHICLEID = AN.BASE_VEHICLE_ID
WHERE    IVM.MAKENAME = 'Honda'

Thanks for your time..

Thanks
Krishna
vvsrk76Asked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
#1: AVOID using "distinct" (if you can)
#2: try reversing the table relationships (small to large)

SELECT /* and avoid distinct if you can */
      CAT.CATENTRY_ID
    , CAT.PARTNUMBER
/* try upending the table relationships */
from IMCCRP.IMC_VEHICLE_MQT IVM
     JOIN IMCCRP.IMC_APPLICATION AN ON IVM.BASEVEHICLEID     = AN.BASE_VEHICLE_ID
     JOIN IMCCRP.IMC_ABC_PARTNO ABC ON AN.APPLICATION_NUMBER = ABC.ABC_NO 
     join CATENTRY CAT              ON ABC.CATENTRY_ID       = CAT.CATENTRY_ID
WHERE IVM.MAKENAME = 'Honda'

Open in new window

0
 
mcsweenSr. Network AdministratorCommented:
What database engine is this, MSSQL or DB2?

You need to add an index to the IVM.MAKENAME column
0
 
vvsrk76Author Commented:
DB2 database.

Is Query looks ok?
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Dave FordSoftware Developer / Database AdministratorCommented:
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
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
Also, how many rows are in each table, and how long does the query actually take to run?
0
 
vvsrk76Author Commented:
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..
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
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?
0
 
Tomas Helgi JohannssonCommented:
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
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
Hi vvsrk76,

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

-- DaveSlash
0
 
vvsrk76Author Commented:
We are using the MQT's in DB2..

We are trying recreate the MQT.
0
 
PortletPaulfreelancerCommented:
Hi, is this solved?
0
 
PortletPaulfreelancerCommented:
Will you please decide if your question is answered.
0
 
vvsrk76Author Commented:
Thank You
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.