----- Original Message -----
From: "Mark Patterson" <mpatterson@...>
Newsgroups: egroups.ib-support
To: <ib-support@yahoogroups.com>
Sent: Wednesday, October 16, 2002 09:05 PM
Subject: [ib-support] Full join unusably slow


> Hi,
>
> I was trying to add a field to indicate whether the user had selected a
field or
> not. To make it transient and not takeup space in the table I added a
table
> sm_selected with one field called id. The presence of value in sm_selected
means
> that the corresponding record in sawmembers has been selected by the user
and is
> hightlighted on his screen.
>
> The join query:
> select auto_number, id, station_id from sawmembers
> full join sm_selected on id = auto_number
> where status < 3
> order by station_id

Ask your self something. you need all fields from the "sm_selected", you can
use a simple join.
if not
-put on the select statement only the fields needed.
-check the index,becarefully with the order is ASC or not y both fields
joined.
-use some tools to optimize the Query, for example marathon.

>
> takes 100 s and returns, 38343 records
>
> The old-style query:
> select auto_number, station_id from sawmembers
> where status < 3
> order by station_id
>
> took 5 seconds
>
> select count(*) from sawmembers gives 426767
>
> The script to create the sm_selected table is:
>
> CREATE TABLE SM_SELECTED (
> ID INTEGER NOT NULL PRIMARY KEY
> )
>
> Any ideas about getting the speed back? I don't see a fundamental reason
for it
> to take so long. It must be doing things in the wrong order.
>
> TIA,
>
> Mark
>
> Regar
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>