Firebird SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesFirebird SQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old August 16th, 2006, 08:21 AM
geekyde geekyde is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Aug 2006
Posts: 2 geekyde User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 12 m 18 sec
Reputation Power: 0
SELECT FIRST 20 SKIP x .... Alternatives ?

I got a table with 21 columns (7x INTEGER; 5x FLOAT, the rest are varchars)
The table has about 40000 rows.

My Query:
SELECT FIRST 20 SKIP x * FROM TableName ORDER BY field1 ASC
There is an index on field1.
(All fields have an index. The field used in "ORDER BY" could be any field in the table. The sorting order is not definite too.)

This query is generated by a Table-Control and x increases as the user scrolls down (the Table-Control always shows 20 Entries at a time)

Some timings:
x=20 ~2,5s
x=40 ~2,5s
x=100 ~2,5s
x=140 ~2,5s
x=13680 ~ 2,6s
x=37960 ~ 2,7s

It seems that Firebird always creates a result-set for the whole query and afterwards filters out the non-needed rows, it also seems that the result-sets of the previous queries are not held in cache for faster filtering in subsequent SELECT FIRST SKIP's...

I am a firebird-noob ;D
Can someone give me a hint on how to speed it up ?

Reply With Quote
  #2  
Old August 16th, 2006, 08:31 AM
pabloj's Avatar
pabloj pabloj is offline
Modding: Oracle MsSQL Firebird
Dev Shed God 6th Plane (7500 - 7999 posts)
 
Join Date: Jun 2001
Location: Outside US
Posts: 7,906 pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level) 
Time spent in forums: 2 Months 3 Weeks 3 Days 7 h 8 m 35 sec
Reputation Power: 279
I don't think that Firebird fetches the whole resultset and then filters out non needed rows if it can use an index to perform the order by, I'd check the explain plan to see if index is used, should be something like:
Quote:
Preparing query: select first 10 skip 2 * from country order by country asc
Prepare time: 00:00:00.
PLAN (COUNTRY ORDER RDB$PRIMARY1)
Note that if you are ordering by a field in descending order and the index is ascending no index will be used, see
Quote:
Preparing query: select first 10 skip 2 * from country order by country desc

Prepare time: 00:00:00.
PLAN SORT ((COUNTRY NATURAL))

I don't think that Firebird has a cache to hold query results and then I don't think that it could be used to prefetch parts of a resultset.

Note: examples are based on EMPLOYEE sample database.

Note that there is an item with medium priority in the roadmap related to index usage
Quote:
Bi-directional indices
Allow reversed index navigation to use ASC-indices for DESC sorting and vice versa.

Last edited by pabloj : August 16th, 2006 at 08:49 AM.

Reply With Quote
  #3  
Old August 19th, 2006, 04:12 PM
geekyde geekyde is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Aug 2006
Posts: 2 geekyde User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 12 m 18 sec
Reputation Power: 0
Thanks.

Problem solved: Stupid mistake ;D
I connected to the wrong database, the wrong database did not have an index on the important fields ;D

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesFirebird SQL Development > SELECT FIRST 20 SKIP x .... Alternatives ?


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 4 hosted by Hostway
Stay green...Green IT