Sybase SQL Anywhere 17 DISTINCT behaviour

[former_SQLA_member1694902]

We recently upgraded from 11 to 17 (I know) and I've hit a really weird problem with a very simple query.

I'm running a query joining two tables and fetching DISTINCT values from the second table.

The tables are:

CREATE TABLE "DBA"."GenericInfoLinks" (
    "LINKID" INTEGER NOT NULL DEFAULT AUTOINCREMENT,
    "PARENTID" INTEGER NOT NULL,
    "PARENTTYPEID" INTEGER NOT NULL,
    "PROPERTYID" INTEGER NOT NULL,
    "VALUEID" INTEGER NOT NULL,
    "BlobId" INTEGER NOT NULL,
    PRIMARY KEY ( "LINKID" ASC )
) IN "system";

approx rows: 8.6m

and

CREATE TABLE "DBA"."GenericInfoValues" (
    "VALUEID" INTEGER NOT NULL DEFAULT AUTOINCREMENT,
    "VALUESTRING" CHAR(1000) NOT NULL UNIQUE,
    PRIMARY KEY ( "VALUEID" ASC )
) IN "system";

approx rows: 1.2m

The query I'm running was:

SELECT 
    DISTINCT 
        GIV.ValueString
FROM 
    GenericInfoLinks GIL
JOIN
    GenericInfoValues GIV ON GIL.ValueID = GIV.ValueId
WHERE 
    GIL.PropertyId = 129

on 11 this would return almost instantly with a few results (there are only 10-30 rows that match the criteria at any time). On 17 it never (read: I gave up after 2 hours) returned a result.

If I change the query to:

SELECT 
    DISTINCT 
        GIV.ValueString,
        GIL.ValueID,
        GIV.ValueId
FROM 
    GenericInfoLinks GIL
JOIN
    GenericInfoValues GIV ON GIL.ValueID = GIV.ValueId
WHERE 
    GIL.PropertyId = 129

it returns instantly with results.

If I change the query to (swapping the order of the ID columns):

SELECT 
    DISTINCT 
        GIV.ValueString,
        GIV.ValueId,
        GIL.ValueID

FROM 
    GenericInfoLinks GIL
JOIN
    GenericInfoValues GIV ON GIL.ValueID = GIV.ValueId
WHERE 
    GIL.PropertyId = 129

It never returns.

I've changed the query to work now but I just don't understand what the problem is. I feel like I'm missing some fundamental or subtle behaviour of DISTINCT that I haven't hit before.

Any help would be appreciated.

[VolkerBarth]

Hm, isn't there a FOREIGN KEY declared on GenericInfoLinks.ValueID (as this seems to be a referencing GenericInfoValues)? - I'm asking that because the difference might result from a table scan in the last query.

The query plans should show the difference between the two latter queries.

[former_SQLA_member1694902]

I added a foreign key in my test DB and all permutations of the query returned results as expected. So, this is about the difference in behaviour between the server "knowing" where to find the related records and having to search for them?

[VolkerBarth]

I don't know the details of the query optimizer but I guess your understanding seems reasonable. FWIW, if you declare a foreign key, it will automatically use the index built on the PK column so it really "knows" it can read both columns over the same physical access.

[Breck_Carter]

> I added a foreign key

Are you sure that foreign key did not exist in the V11 databases?

Was the V17 database created by upgrading the V17 database?

Have you looked at the graphical plan with statistics for the V11 query?