--- In firebird-support@yahoogroups.com, "Waner Miranda"
<wanermiranda@g...> wrote:

>
> Hi,
> I have a difficulty with cross tabs, the procedures build many

rows, then

> show these slowly. Which the best way to show and use, cross tabs

with FB?

>
> Waner Miranda
> Brz

I am not sure whether you are talking about using say union or whether
you are talking about joins. My guess is joins since you expect it to
perform quicker.

Firstly, you should only join to a field in another table if that
field is indexed (primary and foreign key fields are indexed for you).

Presume your tables look something like that.

Workplace
(
ID
Name
Address
)

Employee
(
ID
WorkPlaceID
Name
Address
)

ID is defined as a primary key in both tables
WorkPlaceID is defined as a foreign key to WorkPlace.ID

You may want to create a cross tab that looks something like

EmployeeName, WorkPlaceName

and displays the all Employees with an ID < 100. The most efficient
way would be:

Select
e.Name as EmployeeName,
w.Name as WorkPlaceName
from Employee e
left join WorkPlace w on (e.WorkPlaceID = w.ID)
where e.ID < 100

If you have a look at the query plan, it will only need to consider
100 records from each table. It will use the primary key index of
employee to narrow down the IDs and the foreign key index to make the
join happen efficiently.

You can use something like IBPlanalyzer to see how your query is being
executed. You just need to design your query in such a way that it
makes it easy for the optimiser to perform efficiently.

If it is slow, my guess is that you are not joining on an indexed
field, or the optimiser is not selecting the best path.

Adam