>(André wrote): I have one intensively used ClassicServer André's suggestion to use classic server on a computer with multiple processors/cores sounds like the simplest way to circumvent your problem (provided things work better nowadays, in ancient days we had to set CPUAffinity to only use one processor due to problems with Windows constantly switching between processors, myself I've never used classic server). At least I think that you either use SuperServer, very old hardware or have set CPUAffinity to use only one processor on your server.
(the rest are some comments regarding Red Octobers various emails) >Each time the stored proc is run it has to delete about 30K records and add >just over a million >records. Sometimes, a solution can be to make small updates frequently rather than large updates rarely. Don't know whether this is possible in your situation or whether all data naturally come in large batches. >There are a ton of indexes that are being updated on each insert. Before creating an index, one should consider if it would have adverse effects, it can be the case that an index is good for one situation, but ruins another. "Ton of indexes" sounds to me as you have too many, but a TON in database terms is of course quite a subjective measure ;o) Particularly, if you have lots of composite indexes, I would consider whether it could be beneficial to replace them by having individual indexes on the fields (well, possibly one or two compound indexes if they are critical). Combining 2 fields is not bad if a field is only the first field in one index (could be slightly quicker, but would be slightly more difficult to read the plan). However, if you have 3 fields and use them in compound indexes, then you could potentially have 6 indexes to cover all combinations, if you have 4 fields, I assume the potential number of useful indexes to increase to 24, with 5 fields 120 indexes and so on. Although no-one would create all such combinations, you can easily see how the indexes can multiply if you use composite indexes. Hence, I prefer to only have single field indexes for fields that require indexing and hardly ever use composite indexes. One of the benefits of Firebird, is that it can use several indexes for one table within a query, many other databases cannot and require composite indexes. >The PLAN seems ok. Nothing is coming out as "NATURAL". All correct indexes >are being used. NATURAL is sometimes the best option, and brilliant indexes can be lousy for certain queries. E.g. SELECT * FROM TableA A JOIN TableB B on A.ID < B.ID WHERE A.ID = (select min(B2.ID) FROM TableB B2) would probably use an index for B.ID, but with such a query, that would be considerably worse than NATURAL (the index would be used to eliminate ONE record). >Further on my suggestion about PAUSEME: It would be cool to have a built in >variable named >CYCLE. So, a stored proc could be written like: > >FOR SELECT GIGIDY FROM GOBELY INTO :PAR1 DO > BEGIN > > IF CYCLE MOD 1000 = 0 THEN PAUSEME; > END Maybe you could have an input parameter in your stored procedure signaling a starting point and then only process 1000 records before calling EXIT? That way, the pausing would be in your Delphi application and not Firebird. >Do (or anyone) have a suggestion as to how I can tell the stored proc to not >bring my server to >it's knees for 3 full minutes? 3 minutes to insert 1 million records sounds quite normal. HTH, Set
