Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query Fast and Efficient (merged 5)
SQL Query Fast and Efficient (merged 5) [message #383288] Wed, 28 January 2009 02:09 Go to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Hi. Please go through the below two queries, both the queries will give the same output. I would like to know is there any tool or syntax to check or analyse which query is fast and efficient? Please help

1.  
select subjid,  max(sysbp1) ||' Systolic' "Highest_BP"
from subject_vitals
where sysbp1 is not null
and diabp1 is not null
group by subjid
having max(sysbp1) > max(diabp1)
union 
select subjid, max(diabp1) ||' Diastolic' "Highest_BP"
from subject_vitals
where sysbp1 is not null
and diabp1 is not null
group by subjid
having max(diabp1) > max(sysbp1);

2.
select subjid,
case when max(sysbp1) > max(diabp1) then max(sysbp1)||' Systolic'
when max(diabp1) > max(sysbp1) then max(diabp1)||' Diastolic'
else 'nothing'
end Highest_BP
from subject_vitals
group by subjid;


Edited by Mod to remove data as requested by OP

[Updated on: Sat, 11 April 2009 01:29] by Moderator

Report message to a moderator

Re: SQL Query Fast and Efficient (merged 5) [message #383297 is a reply to message #383288] Wed, 28 January 2009 02:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why posting the same question 5 times in 10 minutes?
Check the forum BEFORE reposting if you have any problem.

Regards
Michel
Re: SQL Query Fast and Efficient (merged 5) [message #383303 is a reply to message #383288] Wed, 28 January 2009 03:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Firstly, the two queries don't return the same results. Try having a null value in either of the columns for a row, and see what you get.

The first query fetches all the rows in the table twice, whereas the second query does a single pass through the table - all other things being equal, the second query will run quicker.
Re: SQL Query Fast and Efficient (merged 5) [message #383407 is a reply to message #383288] Wed, 28 January 2009 15:21 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
ygsunilkumar wrote on Wed, 28 January 2009 03:09
I would like to know is there any tool or syntax to check or analyse which query is fast and efficient? Please help



You can have a look at EXPLAIN PLAN or AUTOTRACE.
Previous Topic: How can u speed up a DROP TABLE process
Next Topic: Driving Table
Goto Forum:
  


Current Time: Thu Dec 08 14:32:50 CST 2016

Total time taken to generate the page: 0.18676 seconds