Subject Re: [firebird-support] Firebird migration to increase perfomances
Author Svein Erling Tysvær
>> There can be lots of reasons for a database application being slow. If
>> the database have huge tables, and the application is written thinking
>> in terms of tables as opposed to datasets, then things will be slow
> An interesting comment, to me anyway, regarding tables vs datasets. I'm
>no SQL guru, I just use Firebird as the backend of an app I'm writing,
>but would you mind elaborating on what you've said. Just some
>"buzzwords"/key words I should use on a google search would be greatly

Sure, Andrew, this is nothing complicated at all.

Once upon a time I used Paradox, a desktop database that I consider quite OK for small tables and single users. However, tables with one hundred thousand rows or so made things slow down considerably.

Using Firebird the Paradox way, i.e. loading all records and all fields every time is a bad idea except for tiny lookup tables. Firebird is good at finding the fields of the records you're interested in - including joining tables or doing some minor calculations upon the fields. Using Firebird simply as a place to store tables and doing all selection in code in your app rather than using a WHERE clause will make things slower - you may compare it to me prefering to take what I want to eat out of the fridge rather than take everything out of the fridge, find what I want to eat and put the rest back into the fridge. Similarly, I prefer to name the fields I need, rather than use SELECT * (there are exceptions, of course).

Generally speaking, a result set should only contain the data that you require for whatever you're doing, and the smaller the result set is, the better.

Using Firebird the client/server way with appropriate indexing, it doesn't normally matter all too much how big tables and databases are, although I must admit that I'm only used to working with tables with a few million rows, and have no experience with really big databases.