Very slow Queries/Strange behaviour upon selecting fields

[jwiebe]

Hi everyone!

I'm experiencing a very very strange behaviour when executing certain SQL being connected to an ASE 12.5.

Two tables connected with a very simple join, querying for '*' (or table1.*, table2.*) is very quick, querying for any specific fields is simply not usable.

Execution time of the following statement is < 1.5s:

SELECT table1.*, table2.* FROM
schema1.table1 as table1,
schema2.table2 as table2
WHERE table1.a_id = table2.b_id

Execution time of the following statement is unknown, but definitely > 30MIN:
SELECT table1.a_id, table2.b_id FROM
schema1.table1 as table1,
schema2.table2 as table2
WHERE table1.a_id = table2.b_id


I do not understand this, I am a simpled minded man.

I tried the following things:
* using other colums
* using only columns from one DB
* using jdbc-connections (SquirrelSQL, own written pice of software)
* using ODBC-Connections (didn't really work in either case, but honestly I'm not too much into that)

The tables /do/ have 'some' entries (one ~50k, the other a couple of millions), but I simply don't understand this behaviour (and it keeps me from working).

Thanks for your help and/or remarks,

Joris!

 

[mrmitchmitchell]

Is this a 1 to 1 relationship?
(One entry in table1 directly correlates to 1 entry in table2?)

Run this
SELECT table1.a_id, count(table2.b_id) as TOTAL FROM
schema1.table1 as table1,
schema2.table2 as table2
WHERE table1.a_id = table2.b_id
group by table1.a_id
order by TOTAL desc;

You can reverse this to make sure it's 1 to 1 the other direction too.

If it IS one to one, there's no reson this should be slow as long as the id's are the Primary Keys or indexed properly.
You could rewrite your select as a left outer join, but it's basically the same as what you have.

SELECT table1.a_id, table2.b_id FROM
schema1.table1 as table1
LEFT OUTER JOIN
schema2.table2 as table2
ON table1.a_id = table2.b_id