Hi,

I have a query that seems to be suspiciously slow, and I just wanted to
confirm that I was doing everything right.

I have two tables, one called Tickets, and the other called Names.

The Tickets table has 4200 records, and the names table has only 24.

If I execute the SQL:

select  T.ID, T.Ticket
from Tickets T

The query executes immediately (all 4200 records). (DBSys shows 0 seconds).

However, If I execute the SQL:

select  T.ID, T.Ticket
from Tickets T
left outer join Names S on (S.ID = T.FROMID)

The query takes 1.3 seconds to load.

Both S.ID and T.FromID are indexed (not compressed).

I'm using DBISam 4.21b11

Are joins supposed to slow queries down that much?

Thanks & Regards

Adam.

--
There is a principle which is a bar against all information, which cannot
fail to keep a man in everlasting ignorance-- that principle is contempt
prior to investigation." - Herbert Spencer, British philosopher.

Can you post the query plan?

"Adam H." <ahairsub4@rREMOVEMEspamSTOPPER.jvxp.com> wrote in message
news:D8A9C3FA-0DDC-4C6C-9BFE-FE58F03727D5@news.elevatesoft.com...
> Hi,
>
> I have a query that seems to be suspiciously slow, and I just wanted to
> confirm that I was doing everything right.
>
> I have two tables, one called Tickets, and the other called Names.
>
> The Tickets table has 4200 records, and the names table has only 24.
>
> If I execute the SQL:
>
> select  T.ID, T.Ticket
> from Tickets T
>
> The query executes immediately (all 4200 records). (DBSys shows 0
> seconds).
>
> However, If I execute the SQL:
>
> select  T.ID, T.Ticket
> from Tickets T
> left outer join Names S on (S.ID = T.FROMID)
>
> The query takes 1.3 seconds to load.
>
> Both S.ID and T.FromID are indexed (not compressed).
>
> I'm using DBISam 4.21b11
>
> Are joins supposed to slow queries down that much?
>
> Thanks & Regards
>
> Adam.
>
> --
> There is a principle which is a bar against all information, which cannot
> fail to keep a man in everlasting ignorance-- that principle is contempt
> prior to investigation." - Herbert Spencer, British philosopher.
>
>

Hi Clive,

> Can you post the query plan?

Sure, see below.

BTW - I upgraded dbsys to 4.24, but didn't seem to make any difference.

Regards

Adam.

============================================================================
====
SQL statement (Executed with 4.24 Build 1)
============================================================================
====

select  T.ID, T.Ticket
from Tickets T
left outer join Names S on (S.ID = T.FROMID)

Tables Involved
---------------

Tickets (T) table opened shared, has 4225 rows
Names (S) table opened shared, has 26 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

Join Ordering
-------------

The driver table is the Tickets table (T)

The Tickets table (T) is joined to the Names table (S) with the LEFT OUTER
JOIN
expression:

T.FROMID = S.ID

Optimizer will attempt to re-order the joins to a more optimal order
Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the
optimizer to leave the joins in their declared order

The joins are already in optimal order and cannot be optimized any further

Join Execution
--------------

Costs ARE NOT being taken into account when executing this join
Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force
the
optimizer to consider costs when optimizing this join

The expression:

T.FROMID = S.ID

is OPTIMIZED

============================================================================
====
>>>>> 4225 rows affected in 1.406 seconds
============================================================================
====

Hi Clive,

Upon a second look, I think I found out what possibly may be the cause.

I notice that the query runs 'instantly', but only when I have it set to a
Live Result Set. When I turn this feature off, then the 'quick' query,
instead of running instantly - takes around 1.25 seconds to complete.

Of course, queries with joins can't be returned as a live result set, so I'm
assuming that this particular query will normally take 1.25 seconds to
complete, to return the 4200 odd records, so in effect, maybe the queries
not slow at all, and it's moreso the other way around, that the live result
set setting is quick.  

Best Regards

Adam.

--
There is a principle which is a bar against all information, which cannot
fail to keep a man in everlasting ignorance-- that principle is contempt
prior to investigation." - Herbert Spencer, British philosopher.

Did you try using this option

JOINOPTIMIZECOSTS

At the end of the query ?.
"Adam H." <ahairsub4@rREMOVEMEspamSTOPPER.jvxp.com> wrote in message
news:20945118-C45D-414F-8C89-32A8B8869CE6@news.elevatesoft.com...
> Hi Clive,
>
> Upon a second look, I think I found out what possibly may be the cause.
>
> I notice that the query runs 'instantly', but only when I have it set to a
> Live Result Set. When I turn this feature off, then the 'quick' query,
> instead of running instantly - takes around 1.25 seconds to complete.
>
> Of course, queries with joins can't be returned as a live result set, so
> I'm
> assuming that this particular query will normally take 1.25 seconds to
> complete, to return the 4200 odd records, so in effect, maybe the queries
> not slow at all, and it's moreso the other way around, that the live
> result
> set setting is quick.  
>
> Best Regards
>
> Adam.
>
> --
> There is a principle which is a bar against all information, which cannot
> fail to keep a man in everlasting ignorance-- that principle is contempt
> prior to investigation." - Herbert Spencer, British philosopher.
>
>

Hi Clive,

In this situation, joinoptimizecosts doesn't seem to make much difference.

Best Regards

Adam.

It does seem slow.. Hopefully tim has an answer

Cheers
Clive.
"Adam H." <ahairsub4@rREMOVEMEspamSTOPPER.jvxp.com> wrote in message
news:900F3658-CB0C-4D29-B7C0-3EE461A22E3D@news.elevatesoft.com...
> Hi Clive,
>
> In this situation, joinoptimizecosts doesn't seem to make much difference.
>
> Best Regards
>
> Adam.
>
>

Hi Clive,

> It does seem slow.. Hopefully tim has an answer

I just realise, I should have added this is over a 100mb network, and not
locally. In my original post, I was confused, because the 1st query ran
lightning fast, but the 2nd one didnt - until I realised about the live
result property. (Got me again dang nabit!

I'm not sure if a couple of fields, with 4000 records over 100mb lan should
run any quicker or not, so I would be interested if anyone else has an
input, but I'm relatively satisfied that I found the problem. (Being that I
was comparing with a live result query)

Best Regards

Adam.

--
There is a principle which is a bar against all information, which cannot
fail to keep a man in everlasting ignorance-- that principle is contempt
prior to investigation." - Herbert Spencer, British philosopher.

Got me again dang nabit!

You turning Texan on us mate? <bg>

--
Best regards

Steve

"Adam H." <ahairsub4@rREMOVEMEspamSTOPPER.jvxp.com> wrote in message
news:6FB937C6-9D72-42C8-8A67-BB3694E4A71A@news.elevatesoft.com...

Adam,

<< Of course, queries with joins can't be returned as a live result set, so
I'm assuming that this particular query will normally take 1.25 seconds to
complete, to return the 4200 odd records, so in effect, maybe the queries
not slow at all, and it's moreso the other way around, that the live result
set setting is quick.  >>

Bingo !  Couldn't have said it better myself.  It's very much like the
OpLocks issue - the issue is not that having multiple users is particularly
slow, it's that having a single user is particularly fast.  There's a
certain overhead to creating the result set and populating it for canned
result sets.

--
Tim Young
Elevate Software
www.elevatesoft.com