"SELECT" question

[torturedmind]

Hello Sirs/Ma'ams,

If the 1st SQL statement is faster than the 2nd one, then why do people keep using the 1st?

Statement 1

SELECT * FROM (SELECT * FROM anytable) datsub1 ORDER BY 1 DESC

Statement 2

SELECT * FROM anytable ORDER BY 1 DESC

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[Beilstwh]

What makes you think that the first is faster then the second. The optimizer will simply rewrite the first to act like the second.

Bill
Lead Application Developer
New York State, USA

 

[torturedmind]

My apologies. Let me re-phrase my question - "Why is it that people use the 1st when the 2nd is much faster to write and much easier to understand?"

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[torturedmind]

The optimizer will simply rewrite the first to act like the second.

So this means that the optimizer will still need some time to re-write rather than directly executing without re-writing. If I put it in nested query, won't it affect performance?

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[Beilstwh]

Any query used is parsed by the optimizer. The reason for people using the first form is typically inexperience. The longer you program the more bad habits you (hopefully) will learn not to use. That being said there are many times when using a sub select is necessary. it is just in your example it makes no sense.

Bill
Lead Application Developer
New York State, USA

 

[torturedmind]

it is just in your example it makes no sense.

Well, I've seen such queries in our production server in the exact format. The only difference is the table name. The table used contains hundreds of thousands of records which produces high wait and high concurrency. I already informed the devs to change such SQLs to the 2nd format and hopefully see the changes in a few days to come.

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[ChrisHunt]

Let me re-phrase my question - "Why is it that people use the 1st when the 2nd is much faster to write and much easier to understand?"

That's a question you're going to have to ask those people, not us.


-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[torturedmind]

Thank you for the time Sir.

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."