WARNING FOR PERFORMANCE FREAKS!

Results 1 to 2 of 2

Thread: WARNING FOR PERFORMANCE FREAKS!

  1. #1
    Lodewijk Guest

    Default WARNING FOR PERFORMANCE FREAKS!

    Hi there!<BR>I was testing my SQL queries on my MsAccess database and it seems that the method of selecting a Top N of a table by using derived tables is paintakingly slow (see article somewhere on 4guys about using derived tables to achieve this).<BR>Reason: an ORDER BY DESC uses a quicksort (C-style) to order the table (this is needed anyway if you want to return the N most "something" records) and the derived table method doesn&#039t have such and efficient sorting method....so from my point of view: try to avoid to have to use derived tables as much as possible.<BR><BR>Enormous performance gain (over 5 times as fast and of course bigger gains with increasing table sizes) if you use the TOP function of MsAccess.<BR>"SELECT TOP 10 * FROM your_table ORDER BY your_something DESC;"<BR><BR>Happy (fast) programming,<BR>Lodewijk

  2. #2
    Join Date
    Dec 1969
    Posts
    2,849

    Default RE: WARNING FOR PERFORMANCE FREAKS!

    The reason I authored the article on derived tables is that some datasource providers do not support the TOP keyword. Using derived tables is going to be slow, just like using subqueries is going to be a performance hit.<BR><BR>have a great day, and thanks for the tip! :)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •