[fpc-pascal] Looking for a Firebird book?

[Matt Emson]

Michael Van Canneyt wrote:
> On Wed, 9 Apr 2008, Matt Emson wrote:
>
>   
>> Michael Van Canneyt wrote:
>>     
>>> On Wed, 9 Apr 2008, Marco van de Voort wrote:
>>>
>>>   
>>>       
>>>> IIRC Mass insertion could bring down the db (slow to an effective DOS to
>>>> other users) unless you commit the transaction every 10000 items or so.
>>>>     
>>>>         
>>> Not in my experience: over 600.000 records inserted in 1 transaction.
>>> Maybe with interbase this was so ?
>>>   
>>>       
>> If you bring a UDF in to the equation, it will happen. Easily.
>>     
>
> That could be. I don't use them. 
>   

With the InterBase model, good move.

> My point of view is that a database is for storage, not for logic...

Ah, this is basic use of resources. The benefit of Stored Procs is speed 
of execution. You are thinking in BDE terms - database stores data, I 
retrieve data, I manipulate/display data, I write changes and new data 
to database. This model works well until performance is essential. In an 
inherently single threaded framework (such as the VCL and most other 
libraries with a GUI), database access is costly. Using Stored Procs to 
do data selection, manipulation and insertion/updating hands that task 
to the database engine. In an RDBMS, this is extremely beneficial to the 
client code. The server needs to be beefier, but the GUI client machines 
can be Pentium 100MHz machines. Using the BDE style of database access 
(thinking Paradox and DBase here) you need to do all processing on the 
client, or at least in a process distinct from the data storage layer. 
I've worked on a system that used a home grown BTree based filing system 
instead of a database and it's much the same. Writing a server process 
to sit in the middle of your clients and load balance the database 
access is a magnitude more dificult than using the database systems in 
built load balancing mechanisms.

Try this:

Test 1: Create a database, add two identically structured tables. Write 
a stored proc to insert in to, update and select the contents of table 
1. Write a client app that runs timed operations to add 10,000,000 
records in to each table and then read them back. (use your imagination, 
either all in one go or mixed mode.) I promise you, the stored proc 
version will be faster, because the engine will cache the compiled 
stored proc.

Test 2: Add a second table that controls table 1 and 2. This has 2 
columns, like an enumeration. The UID points to a value that dictates 
the use of the data in Tables 1 and 2. Now write code that only returns 
a subset of the tables given the rules in table 3. This can be as simple 
as an exclusion (e.g. Table 3 contains 4 rows, 1, 1 : 2, 0 : 3, 1: 4, 0) 
where table 1 and 2 join table 1 and the second column is used to 
determine inclusion (boolean logic) so that a column in table 1 or 2 
must match the joined columns value. In the  stored proc version, this 
is a simple "select" operation on he client. In the  other version, the 
logic now sits more heavily on the client.

You could go on like this. The more complex the look up in, the more 
strain the client process is put under (or middle tier.) In the RDBMS 
version, the load is balanced because the database engine is explicitly 
designed to handle this model.

I'm not trying to preach to you, as your position is one of taste, but 
the logic you use is not as sound as you imply.

M