Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

order by + rownum

 
Stary Kapec
Ranch Hand
Posts: 81
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I wonder if the following SELECT is correct


The strange thing is that the inner select, alone, executes in 60s
but the whole statement as above in only 2s.
Why is that and is it correct?
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Jasiek Motyka:
Hi,
I wonder if the following SELECT is correct


The strange thing is that the inner select, alone, executes in 60s
but the whole statement as above in only 2s.
Why is that and is it correct?


You're probably not timing what you think you are.

First, on many databases (particularly Oracle), there are 3 phases to processing a query, known as: parse, execute, fetch

"parse" is the work done for query setup, it is effectively the same for both of your queries.

"execute" is the work done to produce the internal result; it is effectively the same for both of your queries. It is dominated by the logical I/O to produce the result; in this case, the inner query, everything else is insignificant.

"fetch" is the work done to get the internal result and put it on the network for you to get; the nested version is going to be substantially faster because the work gets to stop when the rownum limit is reached.

However, with result sets of any size, one of the biggest factors is the network transfer time; I would guess the difference in network I/O accounts for the majority of the difference in the time.

By the way, with some database (such as Oracle), you can improve your network performance by using setFetchSize() of Statement (or PreparedStatement) to make the driver use bigger buffers (some databases already use really big buffers though, find out what yours is using before you play with this).
 
Stary Kapec
Ranch Hand
Posts: 81
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks ;)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic