First Time a SELECT execute it is very slow....

[pwatel]

Hello
I will try to make this long story as short as possible.

I insert in a newly created (empty) Firebird Database about 25,000 records and the main table is has about 33 fields (the TITLES table below). I first deactivate indexes then reactivate them.
The first time I load the data it takes an enormous amount of time. There after when I close and re-run the same application that file behaves as expected i.e. it selects the data in a reasonable amount of time. I have included your profiler data. I even made a test to avoid server cache
effect, to reboot the machine re-run it and load again with ok time.
1st time
Complete
22/07/2010 10:23:15 42,453 SQL Execute: SELECT T.* FROM TITLES T
2nd time (may be cache effect) but I close the application
Complete
22/07/2010 10:25:51 1,172 SQL Execute: SELECT T.* FROM TITLES T
3rd time (after a reboot)
Complete
22/07/2010 10:28:14 2,922 SQL Execute: SELECT T.* FROM TITLES T

And if I do a back up and restore
Prior the file is 39392Kb after it is 30912Kb and load in very little time
Complete
22/07/2010 10:48:58 1,640 SQL Execute: SELECT T.* FROM TITLES T

So I do not get it
1) Why there is with the same code and the same file a big select difference in time ONLY the first time ever to be used
2) Why after a backup/restore it is instant
3) How is there 9 meg of difference in before and after the backup/restore and what can I do to eliminate it:
- I do not perform any deletion
- I use auto update may be I should stay away from it and because of commit retaining o r how can I set it to commit period.

It feels as if the database is "corrupted" for a while, sorts itself out recover and then all is fine and behaves normally from then on....

I am at a loss here please help
Thank you regards
Philippe Watel

[Dimon]

1. This behaviour can be connected with memory allocation by Firebird on inserting data. Please, try to use third-party components (like ADO) or applications, and check if the problem still exists.

2. The difference of database size before/after backup/restore can be connected with memory allocation too. And this behaviour doesn't depend on IBDAC comopnents.

[pwatel]

The difference of size is probably due to commit vs commit/retaining

I did some more tests with backup/restores which are strange too:
wheter I use your back/restore services or 3rd party tool I get the same behaviour.

Usually the back up is very quick but thre it stoles and get stuck as well when it writes that table the first time, but it works eventualy.
If I do it again it is very very fast.

Do you you have any idea what is going on or you could indicate me a Firebird forum where I can ask.

I have bee using FB for years and I have never seen such behaviour

I do about 25000 insert and a commit (not retaining for speed) every 500 would it be the cause ? or what is the best way to fill that table

Thanks
Regards
PW

[Dimon]

Try to use the TIBCLoader component that serves for fast loading data to the database. You can find more detailed information about this component in the IBDAC help.

You can ask this question on https://forums.embarcadero.com

[pwatel]

As Usual (and that is something you should look at)

your help on this component is just a listing of the properties /methods you have to our disposal. but how do you use it ???????????any example ?,code samples? , even (if we are lucky) a demo for the stuff does not always cut it because it is a finished snapshot of something that works and does NOT show you the steps to get in any details.
I'd settle for a reference to a forum entry. It seems you are taking as a model Delphi 2009+ help...
Regards
PW
PS I still would love how to use the stuff f it could solve my problem

[Dimon]

You can look at the detail example of using the TIBCLoader component in the Loader demo of the IBDACDemo.