|
View previous topic :: View next topic
|
| Author |
Message |
Nidhi_P
New User
Joined: 21 Oct 2009
Posts: 6
Location: Pune, India
|
|
|
|
Hi,
My requirement is to fetch the last updated record from a table. I'm using a singleton select. Firstly, i used ORDER BY with FETCH FIRST 1 ROW ONLY and this worked fine. But, as it was increasing CPU consumption and taking longer time, i used MAX function to fetch the record with the latest TIMESTAMP and this too worked fine and took lesser time to run i.e. 45-50% less time than using ORDER BY.
My question is why ORDER BY takes such a longer time than MAX function? can anyone please explain me in a bit detail? I'm new to DB2. |
|
| Back to top |
|
 |
arvind.m
Active User
Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad
|
|
|
|
Nidhi, a question for you
Is the records fetched by method 1 (ORDER BY with FETCH FIRST 1 ROW ONLY) and method 2 (used MAX function to fetch the record with the latest TIMESTAMP) are same??? |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
one of these days people may just learn to use proper terminology.
records have nothing to do with DB2 tables - the term is ROWS.
with the ORDER BY and FETCH FIRST,
your result table contains all columns in the select clause,
delimited by your WHERE clause which are sorted.
whereas the result table for the MAX function contains 1 row.
so, you could say the MAX function saves you a sort.
if the table is large, that would be a significant resource cost. |
|
| Back to top |
|
|
Nidhi_P
New User
Joined: 21 Oct 2009
Posts: 6
Location: Pune, India
|
|
|
|
| Quote: |
Is the records fetched by method 1 (ORDER BY with FETCH FIRST 1 ROW ONLY) and method 2 (used MAX function to fetch the record with the latest TIMESTAMP) are same???
|
Yes, both the methods give the same result.
| Quote: |
| records have nothing to do with DB2 tables - the term is ROWS. |
Dick, I meant to say the latest record from a set of resulted rows. I'll be more careful with the terms i use.
I'm confused how exactly the MAX works.
1. Does it perform a scan of all the resulted rows from WHERE clause? Does it use forward Scan or reverse scan?
2. The column I'm performing MAX on (TIMESTAMP) is the last column in the INDEX. Does position matter? If the column is the only column in the INDEX or the first column in INDEX, no sorting will happen. But, if not, then will there be any sorting? Please help me to understand how this search actually works.
I referred many previous posts on MAX function and also referred IBM manuals, all i got was just a basic understanding of MAX. Please help. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Quote: |
| Dick, I meant to say the latest record from a set of resulted rows. I'll be more careful with the terms i use. |
yet you miss-used the term record, once again.
the point about terminology
(and it is prevalent in this forum - miss-use of the word JCL is another example)
is that you can not find anything in 'searches' about apples if your search key is oranges.
using the general term latest is an error, everything depends on ORDER.
Now, back to your question.
How a MAX function 'technically works' is something that I don't know or care about.
I do know that SQL performance is improved when DB2 is not forced to do table scans.
Lesson here is that high impact SQL should be based around good indexing (normally).
Ignoring the effect of the WHERE clause(s):
A MAX function will build a result table based on the results of interrogating either indexes or table scans
(the direction of the scan? I have no idea)
- the result table will be comprised of a row containing the MAX value -
when you ORDER BY, db2 will build a result table based on all rows that qualify (from the where clause that I told you to ignore).
thus is can be surmised that the time necessary for db2 to create/build
a result table of 1 row
would be quicker than a result table of more than one row.
analyzing the plan_table entry generated from an EXPLAIN of the sql
will tell you how db2 has decided to generate the result table.
Obviously, if the timestamp is the primary column of an index,
the search is rather easy.
The converse is also obvious:
If the timestamp column is not the primary column of an index, a scan of the index or table is required.
The decision of which to scan will be made by DB2
(the reason can be explained by somebody with intimate knowledge of DB2 code
whom you are not going to find in a forum like this.
More likely scanning a doctorial thesis)
and (again) the plan_table will indicate the decision that was made by DB2 at the time of the EXPLAIN. |
|
| Back to top |
|
|
Nidhi_P
New User
Joined: 21 Oct 2009
Posts: 6
Location: Pune, India
|
|
|
|
| Quote: |
yet you miss-used the term record, once again.
|
Oops! I did it again... 
I checked the PLAN_TABLE. It indicates IndexOnly Access. I also learnt that the column i'm using MAX on is a low order column in INDEX and hence, it needs to scan the entire Index to find the MAX value. But, anyways it's better than the table scan.
Thanks Dick, EXPLAIN helped me to understand the query performance. As this is the first time i'm working with DB2, i was not aware of EXPLAIN, PLAN_TABLE etc. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
EXPLAIN, PLAN_TABLE
are the third tools in your arsenal in SQL problem solving;
first is SQL Ref manual for syntax;
second is the error code manual.
The IBM - DB2 Product Family Library
is a good starting place to find manuals and other links to DB2 documentation.
this website (Download button at top of this page)
provides a slick little thing called Mainframe Abend Assist.
I suggest downloading the free version.
probably requires coordination with your PC security folks,
but it is worth the bs.
Only thing better is Quick Ref, which costs. |
|
| Back to top |
|
|
Nidhi_P
New User
Joined: 21 Oct 2009
Posts: 6
Location: Pune, India
|
|
|
|
| Yes, I have the Abend assist installed in my PC. Thanks for providing the link to DB2 manuals and other stuffs, Dick. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
Nidhi,
also, congratulations on using EXPLAIN.
you are one of the few who managed to do this on your own (at least without another question on the forum). |
|
| Back to top |
|
|
Nidhi_P
New User
Joined: 21 Oct 2009
Posts: 6
Location: Pune, India
|
|
|
|
| Yes, i managed to use EXPLAIN by myself but I must say it's because you guys have explained it very nicely in previous posts. Thanks to you guys for putting efforts and time. |
|
| Back to top |
|
|
|
|
