[Lazarus] Web development and XML database (Mattias)

[michael.vancanneyt at wisa.be]

On Thu, 17 Nov 2011, Juha Manninen wrote:

> 2011/11/17 <michael.vancanneyt at wisa.be>
>
>> I really don't see why you think SQL databases are slow. We have lots of
>> queries running on our system, huge amounts of updates, and Firebird does
>> an extremely good job of it, and we do almost only multi-table select
>> queries. Concurrency is not an issue at all with their multi-generational
>> architecture - no locking is needed.
>>
>
> I believe your DBs are fast but I have seen really slow ones. I think the
> complexity of queries, the amount of data and amount of users are the key
> factors.
>
> The delayed response of many web-sites is caused by slow DB access, not by
> slow program code or slow network connection.

Agreed.

>
> In a big company I was involved with PDM, eMatrix object DB which was built
> on top of Oracle relational DB. It created mystical table structure for the
> objects. Normal queries took seconds but if your query would return tens of
> thousands of items it practically halted and the process had to be killed.
> Another DB was an Oracle ERP. It also had complex table structure and lots
> of data, and queries typically took seconds. It was running in a computer
> that was the fastest in our country when it was bought!
> The table indexes could not be wrong because many people had optimized them.

That is a VERY dangerous statement :-)

>
> I in fact created a solution for browsing and searching the PDM data
> quickly, using VirtualTreeView + data dumps read into memory.
> In many cases the reading should be optimized and fast because it happens
> often. Writing can be slow, in most cases nobody cares.

You should have started with this :)

I don't think we ever run a query that takes a second to complete. 
Often we join up to 15 tables in a single query.

I think that the mapping objects <-> queries is at fault. Many of these
object <-> rdbms generating tools produce the most horrible tables and
queries; You can optimize all you want, but if it is bad to start with,
optimization will not help.

I suggest you try tiOPF. We use it. It allows you to design your tables 
as you want them, and tiOPF allows you to fine tune every query which 
loads objects from the database. It takes some work, but the end result is
super fast.

Some common sense is all it takes.

Michael.