Adam wrote:

>
>
> --- In firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>, Lee Jenkins <lee@...> wrote:
> >
> >
> > Hi all,
> >
> > I'm currently working on a re-write of an existing application. In
> this
> > application, I need to pull small subsets of data from a table that
> will have
> > many records in it (2 million eventually?) but I may only need to
> grab say, 3,5
> > or maybe 15 records and I need to get them fast.
> >
> > The existing app was originally written against an MS Access
> database. Because
> > of this, current "days" records would be held in temporary tables
> until end of
> > day was run and they were transferred to a historic table. This was
> necessary
> > to avoid 1) pulling all those records over the network to query
> locally with
> > access database engine and 2) to avoid having to search through that
> many
> > records to find just a few. When we ported the app over to
> firebird, it was
> > necessary to stick with this model.
> >
> > Now that I'm re-writing the app, I want to use just one table.
> While having to
> > pull thousands upon thousands of records over the pipe to query them
> locally is
> > no longer a concern, I'm concerned about the server having to query
> through that
> > many records to find just a few.
> >
> > PK's in the database are based on a static local ID and a database wide
> > generator that produces (Varchar(35) PK's like this:
> >
> > LocalID-Generator value.
> >
> > IE:
> > 555-1
> > 555-2
> > 555-3
> > ....
> > 555-2220303030
> > ,etc.
> >
> > FK are the same way.
> >
> > I'll be using mostly a compound index using a FK with a format like
> that shown
> > above along with a DateTime field. Can I expect reasonable speed
> here to pull
> > these few records out of so many?
>
> Lee,
>
> Providing the queries you anticipate are well served by indices on the
> table, this will not be a problem. Firebird will read the ind(ex/ices)
> from disk (or probably from a memory cache), build a bitmap of where
> the particular records of interest are then read them in storage order.
>
> But download FlameRobin and use the test data generator to fill a test
> database with 2 million records then see for yourself.
>
> Adam
>

Adam: I did download FlameRobin, its kind of a pain since it seems hard coded to
100K records at a time and I didn't see a setting to increase that anywhere, I
could have missed it.

Anyway, I put 1,000,000 records in the table and tested the following SQL:

SELECT
s.oid
,s.sale_number
,s.pos_sale_number
,s.sale_type
,s.sale_state
FROM
SALE s
WHERE
s.USER_OID = '3-5555'
AND
s.SYSTEM_DATE = '6/19/2008'
ORDER BY
s.SALE_NUMBER;

With the following results:

PLAN (S ORDER IDX_SALE_SALENUMBER INDEX (IDX_SALE_USER_OID))


Executing...
Done.
1887 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 26 index, 0 seq.
Delta memory: 924 bytes.
Execute time: 00:00:00.

It returned like 18 records that matched the criteria from the 1million +
records in the table so I'd say that was pretty damn fast. I tested in the
application too, where I need to display the user's current sales from today and
it was very, very fast. Coupled with a nice little "Please Wait..." message
that displays for like .5 seconds for subjective speed and I say it looks like
its safe to use one table like I had hoped to do.

Nice.

--
Warm Regards,

Lee