Just a question really.
Do you use Interbase? Do you also spend a bunch of time trying to figure
out which indicies to create? and are they only to support performance in
your stored procs? Do you wish you could use cursors in your stored procs?
Would you like to be able to use a stored proc or UDF to give you the value
in a computed field?
I've been surfing today, and have found some amazing performance boosts
available (server side) to programs accessing Interbase tables. Yes! you
can have cursors in your SPs and Yes you can use SPs and UDFs to provide
computed field values.
I realize that this is probably the wrong list to post on, but I'm going for
maximum exposure as I think that NZ DUG participants often use Interbase and
often have these problems.
The solutions are not mine, and I take no credit for them, but amazingly
they are there.
Have you ever wanted to do (in Interbase)
create cursor my_cursor as select * from my_table where my_field = a_value;
while not my_cursor.eof do
update my_table set my_new_field = my_old_field + 1
where my_table.cursorvalue = my_cursor.value
I've always thought that Interbase did not support this kind of construct,
but have found out that it does (and works really fast and doesn't require
any extra indicies, in fact works just as fast if there is not even a
primary key on the table)
Likewise
CreateTable purchase_order(
id integer,
order_date date,
supplier integer,
status computed by select status from sp_purchase_order_status( id )
)
can also work. This would let you compute the status as pending,
part-received, or fully received however you liked using the status of the
associated lines.
Using a couple of these (poorly documented but documented nonetheless)
tricks, I've managed to build a search facility to find all products whose
description contains both "SH" for shoe and "BLK" for black. In a database
with nearly 200,000 products, 1844 record identifiers are returned in 0.04
seconds. Searching for customers who live in Gore and have an initial E and
whose name starts with "Glen" and whose title is "MRS" yields 5 records out
of 65,000 just as quickly.
Anyone interested in a chat on how to not only make Interbase scream, but
also shriek?
The solutions are not SQL generic, they work only with Interbase. If you
use IBX or IBO you're probably already tied to Interbase as a server, you
just didn't know how fast it could really be. They _will_ work with the
BDE, but if you are still using the BDE to access Interbase, then you're
probably not interested in IB specific or performance oriented solutions.
Apologies if this is _WAY_ off-topic for you.
Trevor
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/
