I noticed if I have a view that groups rows, and I call that view with a Where clause, it appears the Where clause is executed *after* the rows are grouped by the view. This becomes quite slow if I am summarizing a small segment of a large table. I would like to see it execute the Where clause on the large table first, then group it.

I have a view with hourly data "v_HourlyData" and the view "v_DailyData" will group it into daily data by summarizing the hourly data.

Example:

Select * from v_DailyData where Batch='ABC';

will give me the daily data for Batch 'ABC'.

Even though v_HourlyData has an index for the Batch column (from the underlying table) and the view v_DailyData groups by "Batch,..", it takes 20x longer than if I had just copied the view definition for v_DailyData and added the Where clause directly to it. Then it will use he Where clause *before* it groups the data.

So the example query above, appears to group all of the rows in the table "HourlyData" *then* it will use the Where clause to return the grouped rows for Batch='ABC'. Unfortunately HourlyData has a million rows in it with dozens of batches and I only need to group the rows for one batch.

1) Is there a way to use a Where clause on a grouped View that is optimized?
2) Or should I forget using a grouped View and rewrite it as a single grouped query?

TIA

Barry
v3.12B

Barry


I'd say the view is working exactly as it should.

A view is the equivalent of a temporary table formed with the query you specified. If I created a temporary table and then performed some sql on it I would be mighty pissed off if the query went back to the base data. I would be equally astounded is a table started using an index from another table

Long way to say you're better off having a specific query ie move the WHERE clause inside the view, or just grumbling quietly to yourself about the time taken.

Roy Lambert

Roy,

>A view is the equivalent of a temporary table formed with the query you specified.<
A view is not a temporary table unless it is an insensitive view similar to what you expect with an insensitive table.

> If I created a temporary table and then performed some sql on it I would be mighty pissed off if the query went back to the base data. I would be equally astounded is a table started using an index from another table
<

Well, prepare to be astounded. Are you sitting down?

If I have a view vSimple defined as "select * from Table1" and I execute "Select * from vSimple where Batch='ABC' order by Batch", then EDB will use the Batch index for Table1 to filter and sort the view *BEFORE* it returns the results. Don't believe me? Create a view on a 100k row table and then select 10 rows from the view using an index and you will get the results back almost instantly. It is not going to return 100k rows to a temporary table and then search that for the 10 rows you are looking for.

But in this case:
If the view vSum is defined as 'Select Sum(Num1) from Table1 Group by Batch'

"Select * from vSum where Batch='ABC'", it will group all of the batches *then* does the filter looking for Batch='ABC'. I'm saying the Where clause should be used by the view *before* the grouping takes place. The results are always going to be the same, but one will be 20x faster than the other.

Barry

grumble... grumble... grumble...

On 1/22/2015 11:44 AM, Barry wrote:

> If I have a view vSimple defined as "select * from Table1" and I execute "Select * from vSimple where Batch='ABC' order by Batch", then EDB will use the Batch index for Table1 to filter and sort the view *BEFORE* it returns the results. Don't believe me? Create a view on a 100k row table and then select 10 rows from the view using an index and you will get the results back almost instantly. It is not going to return 100k rows to a temporary table and then search that for the 10 rows you are looking for.
> But in this case:
> If the view vSum is defined as 'Select Sum(Num1) from Table1 Group by Batch'
>
> "Select * from vSum where Batch='ABC'", it will group all of the batches *then* does the filter looking for Batch='ABC'. I'm saying the Where clause should be used by the view *before* the grouping takes place. The results are always going to be the same, but one will be 20x faster than the other.

It's always worthwhile to submit a feature request fort Tim to look into
this.

However i can see that the EDB analysis logic would get complicated
rather quickly - how would EDB "know" the results would be same ?

If the view is based on trivial select only with no where or aggregate
clauses then one could easily do a "pass-thru" query to source table.

In this specific case your 2nd query results would be same either way
but i'm thinking one could come up with a scenario where results would
differ (i.e. query of view would be different than EDB trying to merge
the view SQL and query SQL into single sql to apply direct to table).

I'm guessing current logic is taking the safe route in case of
non-trivial queries.

Raul

Barry

>
>Well, prepare to be astounded. Are you sitting down?
>
>If I have a view vSimple defined as "select * from Table1" and I execute "Select * from vSimple where Batch='ABC' order by Batch", then EDB will use the Batch index for Table1 to filter and sort the view *BEFORE* it returns the results. Don't believe me? Create a view on a 100k row table and then select 10 rows from the view using an index and you will get the results back almost instantly. It is not going to return 100k rows to a temporary table and then search that for the 10 rows you are looking for.

I'll believe you - can't see any reason for you to lie however, I think you've hit a difference between a sensitive and an insensitive query. I don't know where the WHERE clause is being applied ie within or outside the view. I'm guessing the latter but because you have a sensitive query inside the view it seems the same.

>But in this case:
>If the view vSum is defined as 'Select Sum(Num1) from Table1 Group by Batch'

>"Select * from vSum where Batch='ABC'", it will group all of the batches *then* does the filter looking for Batch='ABC'. I'm saying the Where clause should be used by the view *before* the grouping takes place. The results are always going to be the same, but one will be 20x faster than the other.

Here you have an insensitive query inside the view.

>grumble... grumble... grumble...


That's right nice and quiet now


Roy Lambert

Raul


>It's always worthwhile to submit a feature request fort Tim to look into
>this.
>
>However i can see that the EDB analysis logic would get complicated
>rather quickly - how would EDB "know" the results would be same ?

My opinion exactly - are we heading down the MS clippy route?

>If the view is based on trivial select only with no where or aggregate
>clauses then one could easily do a "pass-thru" query to source table.

From what Barry says a simple "SELECT * FROM table" does perform like this - and without testin I suspect its the same for all sensitive queries.

Roy Lambert