Subject Re: hung on isc_commit_retaining
Author maverickthunder
--- In, Aage Johansen <aagjohan@...> wrote:
> maverickthunder wrote:
> <<
> Hi Dimitry,
> Too see an example at home (using xp, I have a subset of the whole DB
> with 65,000,000 rows and db size of 5 gb) and doing a query with SQL
> Maestro for firebird:
> ...
> Now I execute: SELECT COUNT(*) FROM "Historico";
> Most of the time fbserver.exe was consuming about 20% cpu time (no
> other process except taskmgr consuming about 2%)
> Result: After 5 minutes I dedided to cancel the query.
> I don't know how to execute a PLAN query, but a single COUNT(*) takes
> too much time.
> >>
> COUNT needs to visit every record. Doing 65 million records in 5
> minutes requires a speed of 200+ records every millisecond. Is this
> a reasonable expectation?
> --
> Aage J.

Well, if I can do a copy of the whole 4gb file in 10 minutes, I think that the primary index is less in size, also it requires one IO per block (because it is only reading the db) and counting records in memory should be faster than writing to disk, also row size should be about 64+ bytes long (5 doubles plus timestamp plus 9 latin-1 chars) it fits about 1000 records per 64k read, I do not pretend the result to be instantly like it were stored in a var, simply there is a problem I cannot find.

Now I make a new query to take records in 1 day (195000 rows, and table has an index with only the date field) and it was stuck in isc_dsql_fetch from around 30 minutes using embedded library in my office computer with Vista.

I always heard about very big databases with very quick response times and taking almost 30 minutes to prepare to return the first row of a simple SELECT * WHERE "Date" BETWEEN '2009-01-01 00:00:00' AND '2009-01-01 23:59:59' and where the table has an index on the date field only, it is not very fast.