Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Please suggest efficient SQL query for given requirement

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
kunal jain

New User


Joined: 19 May 2011
Posts: 59
Location: India

PostPosted: Wed Jul 31, 2013 5:35 pm    Post subject: Please suggest efficient SQL query for given requirement
Reply with quote

Hi,

Please advise SQL cost efficient SQL query for the below requirement:

Table structure :

Employee Table
CLASS CHAR(2) NOT NULL,
MARKS DECIMAL(3,0) NOT NULL,
STUDENT_NAME CHAR (30) NOT NULL

Requirement:
To get student details with highest marks from each class.

Please suggest.[/u]
Back to top
View user's profile Send private message

kunal jain

New User


Joined: 19 May 2011
Posts: 59
Location: India

PostPosted: Wed Jul 31, 2013 6:29 pm    Post subject: Re: Please suggest efficient SQL query for given requirement
Reply with quote

Any reply please
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Wed Jul 31, 2013 6:33 pm    Post subject:
Reply with quote

Less than 1 hour after posting you are asking for replies when response are made by people IF they feel like it. If you want a quick response then go and pay someone for it.

I suggest no-one replies to this until Friday.
Back to top
View user's profile Send private message
kunal jain

New User


Joined: 19 May 2011
Posts: 59
Location: India

PostPosted: Wed Jul 31, 2013 6:41 pm    Post subject:
Reply with quote

Apologies icon_cry.gif for my last reply.

I should have refrain from posting quick reply, i will ensure to be more patient going forward.

Awaiting all valuable response to this.
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 645
Location: Pennsylvania

PostPosted: Wed Jul 31, 2013 7:02 pm    Post subject:
Reply with quote

This requirement feels like a homework assignment to me.

The purpose of the such an assignment is for the Student to use their brain and figure it out for THEMSELVES.

Soliciting an answer from the internet is not the intent of such assignment.

If this is actually work related, then the employee is overpaid.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Wed Jul 31, 2013 8:17 pm    Post subject: Reply to: Please suggest efficient SQL query for given requi
Reply with quote

Hi Kunal,

What you have tried so far with this query?

What are the difficulties you are facing?

Regards,
Chandan
Back to top
View user's profile Send private message
kunal jain

New User


Joined: 19 May 2011
Posts: 59
Location: India

PostPosted: Thu Aug 01, 2013 1:18 pm    Post subject:
Reply with quote

Tried below query & got desired output, however performance of this query is very bad(taking 25-30 seconds approx) probably due to high volume of table(about 600,000).
Thus please assist me in optimizing below Db2 SQL query.

Note: Table & column names have changes to maintain data integrity.

SELECT CLASS, MARKS, STUDENT_NAME, STUDENT_SEX
FROM STUDENT
WHERE (CLASS, MARKS) IN
( SELECT CLASS, MAX(MARKS)
FROm STUDENT
GROUP BY CLASS
)
AND
CLASS > ' ' condition needed to eliminate blank values of CLASS
ORDER BY CLASS ASC
WITH UR

Thank you in advance for help !
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Thu Aug 01, 2013 1:58 pm    Post subject:
Reply with quote

Just try to EXPLAIN the query and see which table causing the issue

Also can't you club CLASS the condition in Subquery itself?

Reagards,
Chandan
Back to top
View user's profile Send private message
kunal jain

New User


Joined: 19 May 2011
Posts: 59
Location: India

PostPosted: Thu Aug 01, 2013 2:03 pm    Post subject:
Reply with quote

There is only one table which is STUDENT

I also tried to put condition WHERE CLASS> ' ' into Sus-query but same result.

Please advise
Back to top
View user's profile Send private message
kunal jain

New User


Joined: 19 May 2011
Posts: 59
Location: India

PostPosted: Fri Aug 02, 2013 2:12 pm    Post subject:
Reply with quote

Does anyone has improvement suggestion on above query then please let me know
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Aug 02, 2013 3:26 pm    Post subject:
Reply with quote

is there an index? more than one?
what was the result of the explain?
Back to top
View user's profile Send private message
kunal jain

New User


Joined: 19 May 2011
Posts: 59
Location: India

PostPosted: Fri Aug 02, 2013 5:43 pm    Post subject:
Reply with quote

yes there are 5 indexes defines on the table.
Explain recommends to create index for column on which MAX function is applied.

On the query perspective, I would like to know can there be any better query of achieving above result ?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Aug 02, 2013 8:42 pm    Post subject:
Reply with quote

Hello,

If the EXPLAIN says the query would benefit from the addition of another index, you should create the new index and test . . .

If that does Not help, then look at other alternatives.
Back to top
View user's profile Send private message
Eshwar CICS

New User


Joined: 18 May 2011
Posts: 47
Location: India

PostPosted: Thu Aug 08, 2013 7:45 am    Post subject: Reply to: Please suggest efficient SQL query for given requi
Reply with quote

As an alternative, try using OLAP functions (DENSE_RANK and PARTITION BY).
Back to top
View user's profile Send private message
kunal jain

New User


Joined: 19 May 2011
Posts: 59
Location: India

PostPosted: Thu Aug 08, 2013 11:16 am    Post subject:
Reply with quote

dick scherrer wrote:
Hello,

If the EXPLAIN says the query would benefit from the addition of another index, you should create the new index and test . . .

If that does Not help, then look at other alternatives.


Hi Dick,

You would be aware adding index requires a detailed impact study considering volumes of records, index maintenance redundancy etc..
Also, Explain usually says to add index for columns on which aggregate function is used.
In my case, adding index is not feasible as we already have 5 indexes on table.

Guys, would request you all to emphasize on query optimization only as we really cannot add/change indexes on table.
Back to top
View user's profile Send private message
kunal jain

New User


Joined: 19 May 2011
Posts: 59
Location: India

PostPosted: Thu Aug 08, 2013 11:21 am    Post subject: Re: Reply to: Please suggest efficient SQL query for given r
Reply with quote

Eshwar CICS wrote:
As an alternative, try using OLAP functions (DENSE_RANK and PARTITION BY).


Hi Eshwar,

We dont have functions like DENSE_RANK, PARTITION BY in DB2.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Thu Aug 08, 2013 12:15 pm    Post subject:
Reply with quote

Hi Kunal,

I am not sure which DB2 version you are using, but we do have these fuctions available in DB2.

Please check with shop if these fuctions are present or not

Regards,
Chandan
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Aug 08, 2013 8:23 pm    Post subject:
Reply with quote

Hello,

Quote:
Guys, would request you all to emphasize on query optimization only as we really cannot add/change indexes on table.
Sorry, but that makes NO sense. . . One of the most powerful ways to optimize a query is to provide the needed direct access to the data. Saying "We can't" only means someone does not want to.

Quote:
adding index is not feasible as we already have 5 indexes on table.
Is 5 some kind of magic number on your system? Many tables have twice that and then some . . .

It all depends . . . You should do some testing with the recommended index addition and see the results.

Keep in mind that a row is added only once but may be read many times and in different ways.
Back to top
View user's profile Send private message
kunal jain

New User


Joined: 19 May 2011
Posts: 59
Location: India

PostPosted: Fri Aug 09, 2013 11:56 am    Post subject:
Reply with quote

dick scherrer wrote:
Hello,

Quote:
Guys, would request you all to emphasize on query optimization only as we really cannot add/change indexes on table.
Sorry, but that makes NO sense. . . One of the most powerful ways to optimize a query is to provide the needed direct access to the data. Saying "We can't" only means someone does not want to.

Quote:
adding index is not feasible as we already have 5 indexes on table.
Is 5 some kind of magic number on your system? Many tables have twice that and then some . . .

It all depends . . . You should do some testing with the recommended index addition and see the results.

Keep in mind that a row is added only once but may be read many times and in different ways.

Thanks Dick, for your response. I will coorinate with DBA in our shop to see if Index can be added.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
This topic is locked: you cannot edit posts or make replies. Mainframe Developer requirement in Ku... capricorn Mainframe Jobs 0 Sun Oct 23, 2016 3:49 pm
No new posts Efficient sorting chandracdac DFSORT/ICETOOL 5 Sat Oct 22, 2016 3:23 am
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us