I was poking around looking for outliers in our multi-database test
suite and I found the following Firebird 2 query that was
significantly slower (the average of a week's worth of nightly runs)
than the other 10 SQL engines I test (including three Microsoft Jet
versions):

2007-01-31 15:31:38.122: <QUERY>
2007-01-31 15:31:38.122: SELECT "Staples"."Prod Type1" AS "Prod Type1",
2007-01-31 15:31:38.122: "Staples"."Prod Type2" AS "Prod Type2",
2007-01-31 15:31:38.122: "Staples"."Prod Type3" AS "Prod Type3",
2007-01-31 15:31:38.122: "Staples"."Prod Type4" AS "Prod Type4",
2007-01-31 15:31:38.122: "Staples"."Product Name" AS "Product Name"
2007-01-31 15:31:38.122: FROM "Staples"
2007-01-31 15:31:38.122: GROUP BY 1,
2007-01-31 15:31:38.122: 2,
2007-01-31 15:31:38.122: 3,
2007-01-31 15:31:38.122: 4,
2007-01-31 15:31:38.122: 5
2007-01-31 15:31:38.122: </QUERY>
2007-01-31 15:31:38.137: <PLAN>
2007-01-31 15:31:38.137: PLAN SORT ((Staples NATURAL))
2007-01-31 15:31:38.137: </PLAN>
2007-01-31 15:31:43.090: [Time] Getting the records took 1.7665 sec.
2007-01-31 15:31:43.106: [Count] Query returned 1932 records.

The thing that struck me as odd was that all 5 columns are indexed
and there is no aggregation, yet the optimiser decided to use a
natural ordering.

The table has 54860 rows and the columns are all VARCHAR(50) UTF8/
UNICODE except the last which is twice as wide. The columns are
really levels of a dimensional hierarchy, so there is a lot of
affinity in the groupings.

Is this a case where a compound index would help? It strikes me as
very strange that FB2 is slower at performing this query than Jet is
on a 54860 line csv file...

TIA,
________________________________________________________
Richard Wesley Senior Software Developer Tableau
Software
Visit: http://www.trytableau.com/now.html