> >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.
Me either - hence AFAIK.
> Interbase is in a unique position in the market in that it's
> competing with
> both the really big players (Oracle et al) and the small local players
> (Paradox et al). To people debating whether to go with Oracle,
> for example,
> or Interbase, then the issues of support, cost, and performance on huge
> quantities of data are far more important than this discussion.
Very true. _personally_ (ie, not Inprise's stance, 'cos I dont know what it
is :) ) I wouldn't be included to use IB for really large DB's - ie,
multi-gigabyte, multi-hundred users. Oracle OWNES that space, has done for
years, and its earned that rep. (along with DB2 and the high-end informix
stuff - MS would like to think they do too, but I dont agree, as they are
limited to NT).
IB is, however, PERFECT for 99% of the people on this list - so is Paradox,
but its not fully SQL compliant, has multi-user locking issues etc (you all
know about them :) ). IB's #1 strength is the fact you can install it,
restore your Db backup into a real DB, and your away - no tweeking or
messing around. I look back at stuff I did a few years ago (POS system,
other stuff on contract), and it really would have been a LOT easier to do
it with IB.
> At the moment I'm among those at the other end of the market,
> those who have
> gone with a local sql database, Paradox or whatever, for speed and price
> reasons. Obviously if you're in that situation you're not going
> to want to
> switch to a real client-server database, no matter how many extra benefits
> you receive, if performance degrades by a factor of ten - and
> that's exactly
> what I found, going to a local Interbase database (it would be unfair to
> expect connection to an Interbase server on another machine to
> perform even
> as well as that).
Nah, I think you'd be supprised, but I think what you need to remember is,
the BDE allows you to port from a TTable-based IASM-flat-file
pseudo-SQL-into-QBE situation, where table scans and seeks are cheap, to a
fully blown SQL engine that does not perform well in that situation. It
makes SQL look like flat file (TTable), and the other way around (TQuery)
However, if you write your app right (ie, dont even contemplate using a
TTable, unless the table contains about 10 items or so :) ), then
performance is going to rock, even under heavy load from a good number of
users.
Example: I had a troublesome app (that I didn't write), still do, which
used the BDE initially. The developer (who shall remain nameless) had used
the BDE and TTables all over the place - and where he hadn't, he'd used
"select * from table;", and used client-side filters. It was viewing over a
33000 record product table (record size of about 8k, with blobs, so it was a
pretty big chunk of data). It took over 15 mins to load the product list,
tho filtering it was quick as hell once it actually loaded (users were in
and out of this app a lot, so loadtime was really important)
I converted it to use IBX-based TQueries, and instead of the search line
triggering changes as the users typed, it just did the search when they
pressed enter. Loadtime: instantaneious. Search time (select * from products
where productID starts with "string%" and BRANDID = "preset value") is under
2 seconds for anything remotly sensable.
Users are happy :).
It took me a day to convert it, and a day to test it and roll it out (not a
huge app). I plan to convert it to 3 tier at some stage, which I'm finding
is EVEN QUICKER. In that situation, tho, you have no data-bound controls
(neither did the 2nd version of the above app), which for me is just fine.
(back end is Java on Linux via CORBA)
>Your apples to apples comparison may
> not be apples to apples, but for comparitive purposes it's nett speed that
> matters.
Well, how about another analogy: compare my Toyota Corolla FX-GT (no
laughing!) (FoxPro/Pdox) to a 40-ton dirt moving truck (IB - ok, maybe only
10 ton :) ). My car is way quicker off the mark than the truck (duh!), but
it only takes 4 people. The truck would be the same speed off the mark with
20 people in the back - or 10 tons of rock (ok.....). BUT, if you tuned the
truck right, put a more appropriate gearbox in it, and maybe an intercooled
turbo or 3, the big diesel is going to smoke my little car for breakfast.
Paradox with TTable is quick. Paradox with TQuery is slower (it translates
to QBE, which is fairly quick). I dont know FoxPro.
IB is slow as hell with TTable, 'cos it does select * from table; and gets
all the records back before doing anything with them.
With TQueries is lightning fast if you dont do select * from table; (which
is stupid).
C/S is not a silver bullet for apps already written in flat-file, same way
that Web is not killer for data entry (its CRAP for data entry!). C/S is a
different way of thinking - and 3-tier is another differenet way all
together (think unlimited business rules before the data even comes back to
you!! I so selects that return 3-400 records, but I only get 50 back over
the wire....).
>
> Incidently, I've heard the argument that local performance with tables
> _should_ be bad with Interbase compared to that with a local sql dialect
> before. What it doesn't explain is why Interbase's performance
> with queries
> is so good! If it's just a matter of locality and indirection,
> they should
> both suffer to a similar degree. If Interbase ran half as fast
> using tables
> as it does using queries, I'd just shrug and go "well, TTables
> are rubbish -
> we knew that. At least we can use the old system until it gets
> rewritten".
> But one tenth as fast?! Do other sql dialects suffer this bad? It should
> be shouted from the rooftops...
Dunno - I only have access to a production Oracle system here, so I can't
test it, sorry. As I said before, _make sure_ you have all the cursors and
stuff set right in the BDE admin - make sure its all SERVER based, so you
only get exactly what you ask for.
Nic.
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
