|
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. |
|
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. 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?
(26 Feb '20, 05:48)
samrae
Replies hidden
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.
(26 Feb '20, 07:25)
Volker Barth
> 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?
(26 Feb '20, 07:57)
Breck Carter
|
