Subject Re: [firebird-support] Re: commit after insert is very slow
Author Martijn Tonies

> HUGELY exaggerated ?? you thing that the firebird engine is soo smart
> enought to sort in < 100 ms thousand of thousand of records ??
> ok, i will gave you this simple exemple.

What indices do you have on the below table?

> we have a database of house, and field NB_room, surface, price, etc..
> table House
> ID_owner,
> ID_agent,
> creation_date,
> id_place,
> NB_room,
> surface,
> price
> the result can be ordered by
> creation_date
> id_place
> nb_room (asc or desc)
> surface (asc or desc)
> price (asc or desc)
> etc.
> the table have more than 10 000 000 records (actually many more)
> now we need to retrieve any kind of subset in any of the previous
> available order by, and this in lower than 100 ms !
> how do you want to do that without index in all possible order by
> combination ? it's will never work if you not do this and you will always
> have a case where the subset will be to big to be sort faster in memory
> (and the result can take more than 30 seconds to appear)
> for exemple, what plan you will use for
> where Agent = xxx and nb_room > 2 and nb_room < 3 and id_location=yyy
> order by price desc
> and now for
> where Agent = xxx and nb_room > 30 and id_location=yyy order by price desc
> the only way i found is to use a plan that use the index on the Order by +
> max as possible on filter
> for exemple below it's will be
> where Agent = xxx and nb_room > 30 and id_location=yyy order by agent
> desc, id_location_desc, price desc
> plan order index (IDagent_IDlocation_Price, nb_room)
> and don't thing it's can be in any other way, because it's simply cannot,
> except if you can know before to do the query the number of items that the
> query will return to you (to know if you need to use the index on the
> filter or on the order by)

With regards,

Martijn Tonies
Upscene Productions

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum: