|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
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 ? |
|
#2
|
|||||
|
|||||
|
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:
Quote:
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:
__________________
My blog Tutorials about OSS databases, DBMonster ... Contribute to OSS Development, fill bug reports! Developer Shed eSupport Commented my.ini/my.cnf (ADD YOUR OWN CONFIG TRICK) An introduction to database normalization Natural or Surrogate key Custom ordering for your results Correlated and uncorrelated subqueries Don't turn your outer joins into inner joins Random data (with a bias) Last edited by pabloj : August 16th, 2006 at 08:49 AM. |
|
#3
|
|||
|
|||
|
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 |
![]() |
| Viewing: Dev Shed Forums > Databases > Firebird SQL Development > SELECT FIRST 20 SKIP x .... Alternatives ? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|