MVSFORUMS.com

rameshb · Posted: Mon Aug 27, 2007 1:40 pm Post subject: performance tuning for sql query

hi

i have one Sql query i need to improve the performance of the query

kolusu · Posted: Mon Aug 27, 2007 1:50 pm Post subject:

rameshb,

We need more information on the indexes to be able to help you. Also post your sql so that it is readable and easily understandable

ex : like

dbzTHEdinosauer · Posted: Mon Aug 27, 2007 1:57 pm Post subject:

To add to Kolusu's list of 'what in the world is this?', what do you mean by

rameshb · Posted: Mon Aug 27, 2007 2:48 pm Post subject:

i am sorry the query looks like this

kolusu · Posted: Mon Aug 27, 2007 2:56 pm Post subject:

Rameshb,

try answering the other questions posted by me and dbz
_________________
Kolusu
www.linkedin.com/in/kolusu

dbzTHEdinosauer · Posted: Mon Aug 27, 2007 3:10 pm Post subject:

rameshb,

OK, it is a four table join. You need an EXPLAIN.
The construct of the 4 tables and relationships between the 4 tables is only something that you know. You are the only one that knows what indexes exist.

Indexes, size of tables, the join types - only an EXPLAIN can tell you that.
Is Visual Explain available on your DB2 release? (which version of db2 are you on?).

There are all kinds of things that can be done, maybe. It depends.
Is this SQL imbedded in an application or is it a Stored Procedure?
Is this for online or batch?
How often is it executed?
How long is it taking now? (yeah, I know, too long....)
How big is the box - what type - what OS - what else is running in the DB2 space?

try to answer a few of these questions. Important questions to answer are:
Online or batch
Imbedded or Stored Procedure
Frequency
Info on your box (computer).
VSN of DB2!!!!!!!!!!

I am not avoiding your question. I (nor anyone else) have any idea where to start with suggestions.

Need more info!!!
_________________
Dick Brenholtz
American living in Varel, Germany

rameshb · Posted: Mon Aug 27, 2007 4:29 pm Post subject:

Hi kolusu,

V_I_TO_ASN ,

V_T_CUTOFF_DATE

input variables to the Stored procedure

we are trying to insert using this sql into temporary table

there are no indexes on any of the the tables

any more info needed please let me know

Luc Orient · Posted: Mon Aug 27, 2007 4:34 pm Post subject:

In general in a join of 2 or more tables, the columns that are to be joined must be indexed ...

Of course, it's a general idea ...

As mentionned, it would be better to have the result of an EXPLAIN of your query (the PLAN_TABLE if you prefer ...)

Luc Orient · Posted: Mon Aug 27, 2007 4:44 pm Post subject:

dbzTHEdinosauer · Posted: Mon Aug 27, 2007 11:57 pm Post subject:

rameshb,

Though the following may sound like nagging, lack of information prohibits any type of productive suggestions. Like coding in COBOL, a good habit is to 'Fully qualify all references'. The four way implicit JOIN could possibly be optimized with different JOIN syntax or sub-selects. But, we have no idea which table contains these columns:

I_REQ_SRV
I_TYP_REQ_SRV
T_CTE_TRG
I_PRI
I_TO_ASN
I_LCK
I_DOM_UDT_LST_IFO
I_UDT_LST_IFO
T_UDT_LST_IFO

and in the where clause, the following is impossible to attempt to optimize:
C_ASN
I_TO_ASN
V_I_TO_ASN

So, full column qualification along with answers to the following will help us in providing some productive suggestions:

1. Are all four of these tables temporary?
2. what is the expected row count of each table?
3. What SQL was used to populate the temporary table(s)?
4. Are all predicate columns of the same datatype?

I realize that this seems like a great volume of info to provide, but there is more. You mentioned inserting?

Also, a general flow of the Stored Procedure would help. Does not make sense to optimize only part of the SP.

What language is the SP? is this SPL?

Good morning, by the way.
_________________
Dick Brenholtz
American living in Varel, Germany