Ann W. Harrison wrote:

> Kjell Rilbe wrote:
>>I need to make the selects and "order history" inserts as fast as
>>possible. These will use up to hundreds or even thousands of where
>>conditions based on multiple master columns and the single child table
>>column. The most common case will probably be 10-100 criteria on 1-3
>>columns. Most of the columns are very selective, but not all.
>
> That's where I stopped reading. Sorry, but thousands of criteria are
> going to be a problem. Is there some way to combine values so you have
> more - or more complex - tables that can be accessed with a dozen or so
> criteria?

:-) You should have more faith in Firebird! I just tested it once with
450 criteria and once with 800 criteria. The test with 450 criteria
executed in a couple of seconds. The one with 800 criteria took like
5-10 minutes. But the test with 800 criteria isn't very likely in a
real-life situation because it contained criterion sets that would match
all records, e.g. all zipcodes. With real-life criteria the high
selectivity of most columns will probably ensure that the queries return
pretty fast. I also realize that perhaps "thousands" was a bit of an
exaggeration. But the user will probably be able to select up to around
1000-1500 criteria.

Yes, there are some optimizations I could do. For example, SNI codes
(codes describing companies' line of business) are ordered into a
hierarchy so that I could use prefix matching in many cases, e.g. SNI
like '123%'. This would sure reduce the number of criteria but not the
number of matching records.

>>How should I configure my DB?
>>
>>It will be running on a Windows 2003 Server, Pentium 4 without
>>hyperthreading, 1 Gbyte RAM, single 7200 rpm harddisk. The only other
>>application is IIS + an ISAPI + my server application. These consume no
>>more than 100 Mbyte RAM.
>
> I'd be tempted to look at classic, but not before getting over the
> "thousands of criteria" problem.

It seems to be runnig nicely on super server. Why classic?

> A better answer is that larger pages
> reduce index depth and that's good for performance.

Ah, that was new to me. Thanks.

>>(My DB operations are currently completely serialized.)
>
> That's nice. Why?

Because I have a single server application serving all my users (via web
server) and it does so in a serialized manner due to BoldExpress (and
Bold). All user queries will be very similar, so I don't really see a
reason not to have them wait in queue for one another. But I will
reconsider if it seems to be a problem of course. It shouldn't be too
difficult.

>>Page buffers? Is this the same as the firebird.conf setting
>>"DefaultDbCachePages"?
>
> Yes. There's a knee at around 10K cache pages where more pages make the
> database slower. V2 absolutely fixes that - 1.5 may also - someone will
> chime in.

Thank, I'll do some testing & tuning.

>>SortMemBlockSize? SortMemUpperLimit?
>
> What are you going to sort, how big is it, and how important is sort
> performance?

Well, I won't actually. Sort anything I mean. :-) So the sort buffers
aren't used internally for anything, like joining? Guess not, with
appropriate indices. ...which I will have, of course.

Thank you for your answers Ann!
Kjell
--
--------------------------------------
Kjell Rilbe
Adressmarknaden AM AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64