View previous topic :: View next topic
|
Author |
Message |
arunsoods
New User
Joined: 13 Jul 2016 Posts: 35 Location: India
|
|
|
|
Hi Team,
While executing below query I am getting 8175.5351 as cost when i am checking through EXPLAIN in BMCADM.
EXEC SQL
SELECT MAX(SEQ_ID)
INTO :DCLTB99-TRANS.SEQ-ID
:SOF-TRANS-SEQ-ID
FROM TEST_TABLE
END-EXEC
Do we have any way by which we can optimize the query so that it can costs lesser.
Thanks and Regards |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
What do you want to do with that MAX value? Can't you store the value somewhere when you know it, rather than lazily rummaging about for the highest one? |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
Why are you using SQL to put it into 2 variables? Put it into one and then copy it to the other in your program. Have you got an index on that table? Probably not - add one. |
|
Back to top |
|
|
arunsoods
New User
Joined: 13 Jul 2016 Posts: 35 Location: India
|
|
|
|
Thanks Bill and Nic for replying...
Nic - If i create INDEX as below on the table
CREATE INDEX INX_SEQ_ID
ON test_table (SEQ_ID)
and then run this query
EXEC SQL
SELECT MAX(SEQ_ID)
INTO :DCLTB99-TRANS.SEQ-ID
:SOF-TRANS-SEQ-ID
FROM TEST_TABLE
END-EXEC
Will this reduce the cost...??
Bill - "Can't you store the value somewhere when you know it, rather than lazily rummaging about for the highest one?."
By the above do you mean that if I select all SEQ_ID using below query.
SELECT SEQ_ID FROM TEST_TABLE
and store the values in a PS file and then applying SORT to find the highest...
Will this work...???
Please suggest |
|
Back to top |
|
|
arunsoods
New User
Joined: 13 Jul 2016 Posts: 35 Location: India
|
|
|
|
Bill Woodger wrote: |
Can't you store the value somewhere when you know it, rather than lazily rummaging about for the highest one? |
By the above do you mean that if I select all SEQ_ID using below query.
SELECT SEQ_ID FROM TEST_TABLE
and store the values in a PS file and then applying SORT to find the highest...
Will this work...??? |
|
Back to top |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012 Posts: 316 Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
Quote: |
Will this reduce the cost...?? |
Quote: |
Will this work...??? |
what stops you from trying? |
|
Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009 Posts: 1788 Location: Bloomington, IL
|
|
|
|
arunsoods wrote: |
Nic - If i create INDEX as below on the table
CREATE INDEX INX_SEQ_ID
ON test_table (SEQ_ID)
and then run this query
EXEC SQL
SELECT MAX(SEQ_ID)
INTO :DCLTB99-TRANS.SEQ-ID
:SOF-TRANS-SEQ-ID
FROM TEST_TABLE
END-EXEC
Will this reduce the cost...?? |
Almost certainly.
Quote: |
Bill - "Can't you store the value somewhere when you know it, rather than lazily rummaging about for the highest one?."
By the above do you mean that if I select all SEQ_ID using below query.
SELECT SEQ_ID FROM TEST_TABLE
and store the values in a PS file and then applying SORT to find the highest...
Will this work...???
|
I believe that what Mr. Woodger meant was to run the query once, and then to reuse either DCLTB99-TRANS.SEQ-ID OR SOF-TRANS-SEQ-ID , whichever you decide to derive directly from the DB2 table. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
1. Avoid hitting the same query multiple times in the same call.
2.if the table is every day populated by a batch then insert or update a new row with the max count so that online don't have to do the same work again and again. This is what I guess Bill say's.
3. As long as you have a index you could try order by desc clause "with ur and fetch first 1 row only or optimize for 1 row only"
4.Look for OLAP, if that does the trick.
5 BMC cost is not true alway so try executing the query and see for issues
6 Have you spoken to DBA as yet? |
|
Back to top |
|
|
arunsoods
New User
Joined: 13 Jul 2016 Posts: 35 Location: India
|
|
|
|
Hi Team,
Thanks for the support I am getting reduced cost using following query.
Code: |
SELECT SEQ_ID
INTO :DCLTB996-SOF-TRANS.SEQ-ID
:W770-SOF-TRANS-SEQ-ID
FROM TEST_TABLE
ORDER BY SEQ_ID DESC
FETCH FIRST ROW ONLY
WITH UR |
Using MAX() was increasing the cost. |
|
Back to top |
|
|
Kerry Ropar
New User
Joined: 14 Sep 2016 Posts: 25 Location: Australia
|
|
|
|
arunsoods wrote: |
I am getting reduced cost |
You can still get better results if you introduce an index on table. |
|
Back to top |
|
|
arunsoods
New User
Joined: 13 Jul 2016 Posts: 35 Location: India
|
|
|
|
I have tried using indexes too.
But I am not getting Authorization Error.
"SQLCODE = -551, ERROR: YOU DOES NOT HAVE THE PRIVILEGE TO
PERFORM OPERATION CREATE INDEX ON OBJECT TEST_TABLE".
Secondly i want to ask if a field is already a Primary Key do I still need to make index for that...????
In above case it is one of the Primary key. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Quote: |
"SQLCODE = -551, ERROR: YOU DOES NOT HAVE THE PRIVILEGE TO
PERFORM OPERATION CREATE INDEX ON OBJECT TEST_TABLE". |
Why you do yourself, don't you have any DBA's at your site?
Quote: |
Secondly i want to ask if a field is already a Primary Key do I still need to make index for that...????
In above case it is one of the Primary key |
It is must to have unique index. |
|
Back to top |
|
|
arunsoods
New User
Joined: 13 Jul 2016 Posts: 35 Location: India
|
|
|
|
Thanks Team for the support i am getting the required results now... |
|
Back to top |
|
|
|