Subject Re: [IBO] SQL Hourglass et. al.
Author Aage Johansen
Anthony Tanas wrote:
> Hello everybody,
> I'm new to IBObjects and to this list.
> I just converted my program over to the IBOBjects Tdataset module. It was
> amazingly fast and easy! I was so excited about jumping back on it today
> that I was up before the crack of 11 AM!


> My questions:
> 1) How can I reintroduce the "SQL Hourglass" behavior that I got with BDE
> components? Now I don't get an hour glass. In most places I can set
> Screen.Hourglass but I have a few databound grids with huge datasets that
> grind a bit when scrolling and get no hour glass at all.

Drop a TIB_SessionProps component on a form. You might need to set a
property (or two).

> 2) How much of a performance gain would I get by converting my executed SQL
> statements (INSERT, UPDATE, DELETE) that are currently in the Tquery
> equivalent into the component (I forget what it's called) with the "low
> overhead" SQL components.

Some, but not a lot (I would think). But you may find added functionality.

> 3) Please give me some pointers on indexing to increase efficiency. During
> most of my career as a corporate programmer we had a DBA who took care of
> the database stuff like this. Now I'm on my own. Is setting up foreign key
> contraints important for performance or just for data integrity? What's a
> good place to learn about stored procedures?

Use indexes to support the WHERE clause. And for joins (do use explicit
joins, don't use the old method of putting the join logic in the where
clause). Watch out for low-selectivity indexes, though.
Indexes for sorting (order by) is usually not a good idea.
You might find that Foreign Key constraints gives you an index with low
selectivity. It can be a problem in Fb/1.5, but less so in Fb/2.
Take a look at the plan for your queries, and ask on this list. There are
a few tricks tha can come in handy.

> Like for example right now if I delete a Billing Number (like an invoice) in
> my system, I first delete the billing items, and before that the payments
> towards each item (it's medical related so payments are for individual
> items)...and I do this in my client program by executing delete statements.
> Is this something that should be done on the server (I seem to recall
> something about ON DELETE CASCADE from my database class in college).
> On the other hand if I move functionality to the server then I am going to
> be more tightly attached to firebird. Is this a good bet?

Put Referential Integrity constraints in the database.
Helen's Firebird book may be the answer to a lot of questions.

Aage J.