| Subject | Re: commit after insert is very slow |
|---|---|
| Author | svanderclock |
| Post date | 2010-03-03T16:30:32Z |
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.
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)
stephane
ok, i will gave you this simple exemple.
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)
stephane
--- In firebird-support@yahoogroups.com, Milan Babuskov <milanb@...> wrote:
>
> svanderclock wrote:
> > and 30 index for one table is not look too much exagerated :(
>
> For 10-column table it is HUGELY exaggerated.
>
> Maybe you should give us the DDL for table and some sample queries that
> would run slow (and approx. amount of data in table) and we could help
> you fix the real problem, not the side-effect.
>
> --
> Milan Babuskov
>
> ==================================
> The easiest way to import XML, CSV
> and textual files into Firebird:
> http://www.guacosoft.com/xmlwizard
> ==================================
>