At 10:18 AM 25/11/2007, you wrote:

>I have been reading the firebird book by Helen.
>
>I have read that views use the indexes of the tables that were used to make
>up the view.
>
>If I make a view that uses a view are the indexes used to make the second
>view?

Indexes are not used to "make a view". However, whenever views are involved in joins, the underlying indexes will be used IF
1) they can be used AND
2) it would be useful to use them.

>The reason I'm asking is I have a sql query (in ibo objects) that does a
>query on a view and joins it with a query on a table. This combined query
>is very slow (7 to 20 seconds) even though the individual parts are quite
>fast (<1s each)
>
>I am not a guru at databases yet..., and I know that joining a table to
>another increases the amount of data returned.

Joining doesn't "increase the amount of data returned". It retrieves data *fields* from more than one table, but it is seldom good practice to retrieve all fields from all tables. The point of querying tables is to get only the data you want to work with. Indexes are often involved in joins, so the better the indexing on the fields specified in join criteria, the better your queries perform. In terms of number of rows, this is (and always should be) driven by a healthy WHERE clause, since this where the optimizer will make good use of good indexes.

Index usage in Firebird is "pragmatic" - unlike some file-based databases that you might have worked with (Paradox, Access, MySQL, etc.) which are rule-driven. The Firebird optimizer certainly has some rules that it applies to the paths it is creating. It uses two other methods as well as the INDEX method for determining the fastest path. It also refers to index statistics, although these can become out-of-date on systems where the database is never logged out of.

>I guess what I'm really asking is ... would a stored procedure speed up a
>table -> view join. I wrote the view in the first place because the data is
>too complicated for me to get the data required back in a single query.

It can - if the conditions are right. Sometimes, for difficult output set requirements, having a SP loop through a set and perform a single-row join operation on each input record in turn will be incrementally faster than tortuously joining views. You still need effective indexes, of course, because any selects and sorts performed during execution of the SP will be optimized.

Asking a question like "Is grass a good food?" won't get any useful answers. Grass is great for sheep and cows but humans, cats and dogs don't thrive on it! :-)

Why don't you show us the elements of the slow query, along with the plans, and let the SQL gurus help you to improve it?

./heLen