--- In firebird-support@yahoogroups.com, Richard Wesley <hawkfish@...>
wrote:

>
> 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...
>

Richard,

Why do you need to group by unless you have aggregates or a having clause?

It is quite possible an optimisation noone has considered writing
because it seems at first glance to me to be unusual query.

Adam