Solved

SQL performnace...

Posted on 2013-01-10
13
309 Views
Last Modified: 2013-04-04
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
0
Comment
Question by:vvsrk76
  • 4
  • 4
  • 3
  • +2
13 Comments
 
LVL 21

Expert Comment

by:mcsween
ID: 38763793
What database engine is this, MSSQL or DB2?

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

Author Comment

by:vvsrk76
ID: 38763818
DB2 database.

Is Query looks ok?
0
 
LVL 18

Expert Comment

by:daveslash
ID: 38763823
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
 
LVL 18

Expert Comment

by:daveslash
ID: 38763829
Also, how many rows are in each table, and how long does the query actually take to run?
0
 

Author Comment

by:vvsrk76
ID: 38763964
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
 
LVL 18

Expert Comment

by:daveslash
ID: 38764082
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 38766544
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
 
LVL 18

Expert Comment

by:daveslash
ID: 38807719
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
 

Author Comment

by:vvsrk76
ID: 38808016
We are using the MQT's in DB2..

We are trying recreate the MQT.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 38842637
#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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 38866954
Hi, is this solved?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 38920691
Will you please decide if your question is answered.
0
 

Author Closing Comment

by:vvsrk76
ID: 39049473
Thank You
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ms sql stored procedure 22 87
Access Date Query 28 44
How can I exclude some wording in a like statement? 39 66
How to base a filter depending on fields contents? 15 48
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now