473,320 Members | 1,979 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Select Query - Looking for a better query

Here is a small testcase of the problem which I'm facing in prod env.

db2 =describe table tab1

Column Type Type
name schema name Length
Scale Null

------------------------------ --------- ------------------ --------
----- ----
-
VIN SYSIBM INTEGER 4
0 Yes

SOURCE_SYS_CODE SYSIBM INTEGER 4
0 Yes

SEQ_NUM SYSIBM INTEGER 4
0 Yes

3 record(s) selected.
db2 =select * from tab1

VIN SOURCE_SYS_CODE SEQ_NUM
----------- --------------- -----------
11 100 1
21 200 1
21 200 2
31 100 1
31 100 2
31 100 3

6 record(s) selected.
My requirement is to get only the following rows in the o/p [ select
only the combination of VIN & SOURCE_SYS_CODE which has the max seq_num
]

VIN SOURCE_SYS_CODE SEQ_NUM
----------- --------------- -----------
11 100 1
21 200 2
31 100 3
So I used the query
select VIN,SOURCE_SYS_CODE,max(seq_num)as seq_num from tab1 group by
VIN,SOURCE_SYS_CODE

However this query is behaving poorly in our prod env. Where tab1 is a
logically partitioned table on 7 partitions and VIN is the partitioning
key.

Access plan in prod is as below
Total Cost: 9.04494e+06
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
3.96437e+08
DTQ
( 2)
9.04494e+06
1.6326e+06
|
5.66339e+07
GRPBY
( 3)
8.94897e+06
1.6326e+06
|
5.66339e+07
TBSCAN
( 4)
8.94296e+06
1.6326e+06
|
5.66339e+07
SORT
( 5)
7.9601e+06
1.10822e+06
|
5.66339e+07
IXSCAN
( 6)
1.07497e+06
583829
|
5.66339e+07
INDEX: SYSIBM

Any help is greatly appreciated.

Thanks, Sam.

Jul 12 '06 #1
3 1348
Ian
Sam Durai wrote:
Here is a small testcase of the problem which I'm facing in prod env.

[...]

So I used the query
select VIN,SOURCE_SYS_CODE,max(seq_num)as seq_num from tab1 group by
VIN,SOURCE_SYS_CODE

However this query is behaving poorly in our prod env. Where tab1 is a
logically partitioned table on 7 partitions and VIN is the partitioning
key.
Not sure why you think the query is *behaving* poorly. With the
information you provided there aren't really any options for a
better access path.

Poor performance is a different matter, and may be related to things
like physical tablespace design (esp. the system temp tablespace),
bufferpools, or sort memory (you are sorting approx 56M rows x 16
bytes).
Jul 12 '06 #2
I'm not sure. But, followings might be worth trying.
Because, I expect the possibility that DB2 first get result pairs of
(Vin, Seq_num) only by using the Index. Then get directly corresponding
Source_sys_code value from the table.
So, table scan can be eliminated.

CREATE UNIQUE INDEX tab1_1 ON tab1
(Vin, Seq_num);

SELECT Vin, Source_sys_code, Seq_num
FROM (SELECT Vin, Source_sys_code, Seq_num
, MAX(Vin) OVER(ORDER BY Vin, Seq_num
ROWS BETWEEN 1 FOLLOWING
AND 1 FOLLOWING) Next_Vin
FROM tab1) Q
WHERE Vin <Next_Vin
OR Next_Vin IS NULL
;

Jul 13 '06 #3
I'm sorry very much confusing you.
Please forget my previous post.
I didn't read following condition.
Where tab1 is a
logically partitioned table on 7 partitions and VIN is the partitioning
key.
Jul 13 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Nuno | last post by:
Is there any SQL Error? Or I have to use Select case in VB code to control SQL instead. Thank you for any ans. Nuno
3
by: Ian T | last post by:
Hi, I've got what I think (probably incorrectly) should be a simple SELECT : Two colums with data like col1 col2 1 50 1 51 2 50
4
by: jimh | last post by:
I'm not a SQL expert. I want to be able to write a stored procedure that will return 'people who bought this product also bought this...'. I have a user table that links to a transaction table...
10
by: serge | last post by:
Using "SELECT * " is a bad practice even when using a VIEW instead of a table? I have some stored procedures that are identical with the difference of one statement in the WHERE clause. If I...
1
by: Andrew McNab | last post by:
Hi folks, I have a problem with an MS Access SQL query which is being used in an Access Report, and am wondering if anyone can help. Basically, my query (shown below) gets some records from a...
3
by: Dan V. | last post by:
How can I use real SQL on a DataTable? i.e. not array of rows using a filter... as in DataTable.Select. I read at : microsoft.public.dotnet.framework.adonet "As others have posted: There is no...
5
by: Silvio Matthes | last post by:
Hello, I'm new to the list and did not find a suitable answer to my question so here it is: I try to select the rows of a table where the content of a varchar-column is empty ('') and...
22
by: Rickster66 | last post by:
As Instructed this is a new thread regarding my original post: "Select Only 10 Columns Going Back" I'm sorry for the late response. I've been gathering up information and carefully with as much...
2
by: paulmitchell507 | last post by:
I think I am attempting a simple procedure but I just can't figure out the correct syntax. My asp (classic) page runs a SELECT query to obtain dates and ID's from 2 tables uSQL = "SELECT...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.