Max wrote:
> Carl Reynolds asked:
>
> > >Um, as far as I know, this is a problem with ANY SQL-based DB
> > (oracle et al).
> >
> > There's abysmal and then there's merely appalling. :)
> >
> > What surprised me is that a simple "select * from tablename" TQuery
> > performed so much better than an equivalent TTable, in Interbase. Can't
> > really compare this to other sql-based db's as they're not
> > available to me.
>
> We (Profax) have been all through this one and back again. The first
> culprit is the BDE itself. If you want to have a look and see how the BDE
> manages raw based table compatibility against a SQL back end, fire up the
> SQL Monitor and have a look at the gargabe that it generates!
Yup. Even aside from the selects for the table data itself, there are all
the selects fired on open to determine all the fields available, their
types, the indexes available, and their fields, etc. So you're not talking
about just the 'select * from table' but all the initial metadata selects as
well.
> In general the BDE creates and uses three (3) cursors to navigate through
> a SQL table, and the overhead of continually reparsing them, walking them
> and otherwise muching about gives you this massive slow down.
Another issue which stems from the BDE 3 cursor model (which is not that
uncommon in relational->navigational mapping) is the generation of selects
ordered by descending key fields. Now, some SQL engines can use their
indexes in either ascending or descending fashion, but for InterBase, you
need to create additional descending indexes or your performance on the
descending selects will suffer.
> The second hit is in the design of the TTable, which has various row table
> based assumptions that cause a lot of extra fetches from the back end. Not
> a problem if you are loading from a file system but the extra selects
> generated really kill performance.
Yup. Be wary of using lookup fields in grids (i.e. fkLookup) against SQL
TTables. The VCL will fire a query for _each_ visible row to fetch _each_
lookup value, even when multiple lookups come from the same source. The
number of SQL server round-trips just explodes.
> So the general solutions are to a) not use TTable against SQL backends, b)
> not use the BDE or c) write TDataSet derived classes that map better to
> the SQL model. The archives are full of this question getting bashed about
> is various permutations, and I would suggest some archive trolling for
> suggestions on better use of the BDE in this sort of situation.
Yup. I can't argue with Max on these suggestion seeing as we followed them
:-)
TTFN,
Paul.
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
