Hi Tim,

I'm finding that VIEWS seem to be running significantly slower than simply
executing the underlying SELECT query. If anything I was expecting the
results to be the opposite way round!!!

Here's a test database :-
http://files.pioneersoftware.co.uk/temp/edb_testdb.7z

Here's the VIEW (you will need to run this SQL to create the view on the
above database) :-
-------------------------------------------------
create view view_invlookup as

select inv.id, invoicenum, datetime, total,
coalesce(sum(alloc.amount), 0) as amtpaid,
coalesce((sum(total)/count(inv.id))-sum(alloc.amount), 0) as outstanding,
app_id, inv.per_id, patient_per_id, per.title, per.firstname,
per.middlename, per.lastname, per.knownas,
(coalesce(per.title,'') || '||' || coalesce(per.firstname,'') || '||' ||
coalesce(per.middlename,'') || '||' || per.lastname || '||' ||
coalesce(per.knownas,'')) as _recipient
from inv
inner join per on (inv.per_id = per.id)
left join alloc on (inv.id = alloc.inv_id)
group by inv.id, invoicenum, datetime, total, app_id, inv.per_id,
patient_per_id,
per.title, per.firstname, per.middlename, per.lastname, per.knownas;
-------------------------------------------------

The raw SELECT query runs in around 3 seconds every time.

"select * from view_invlookup" on the other hand takes >6 seconds.

As a point of interest, in EDB Manager, preparing the SQL SELECT is almost
instant and then executing the query takes about 3 secs, while the VIEW
takes about 4-5 seconds to PREPARE and then 1-2 seconds to execute.

Hope you can help!

Hedley,

<< I'm finding that VIEWS seem to be running significantly slower than
simply executing the underlying SELECT query. If anything I was expecting
the results to be the opposite way round!!! >>

Is this something that you're just noticing with B9 or B10 that you didn't
see with B8 ?  Or is this the first time that you've run executed these
views ?  Also, are executing the query with RequestSensitive=True or False ?

<< As a point of interest, in EDB Manager, preparing the SQL SELECT is
almost instant and then executing the query takes about 3 secs, while the
VIEW takes about 4-5 seconds to PREPARE and then 1-2 seconds to execute. >>

This is normal - the view has to be executed, which is where the initial few
seconds comes from, and then it has to be read to satisfy the query.  If
you're using RequestSensitive=False, then the entire result set from the
view will be copied (again) to another temporary table used for the result
set for the current query.

--
Tim Young
Elevate Software
www.elevatesoft.com

I hadn't ticked RequestSensitive (so I guess it was false?) but that would
the same for both the select query and the view.

> Is this something that you're just noticing with B9 or B10
> that you didn't see with B8?

Good question - I just installed EDB Mgr 2.0b8 on a different PC for testing
and it's roughly the same results. VIEWs take ages to prepare and then are
slightly faster than the SELECT statement.

This means that overall, using VIEWs is a lot slower, presumably unless I
hold a load of pre-prepared parameterized views?

To be honest, I'm not using parameterized queries or views because with
DBISAM (and PGSQL) there's minimal overhead in preparing queries or views :-

In other words, when executing the same query over again, I tend to do this
:-

select * from x where id='a' [execute]
select * from x where id='b' [execute]
select * from x where id='c' [execute]

rather than...

select * from x where id=ram1
[set param1 to value 'a' and execute]
[set param1 to value 'b' and execute]
[set param1 to value 'c' and execute]

Am I going to have to change this approach in order to get acceptable
performance out of EDB VIEWs?

Thanks.

Hedley,

<< I hadn't ticked RequestSensitive (so I guess it was false?) but that
would the same for both the select query and the view. >>

No, not at all.  You're not understanding how views work.  A view acts just
like a table, so when you issue:

SELECT * FROM MyView

the MyView view needs to be opened (executed).  That will take whatever
amount of time it would normally take to execute the underlying SELECT that
the view is defined as.

Now, when you execute the above with RequestSensitive=False, then you're
essentially telling EDB to copy the entire contents of the MyView view to a
static, temporary table instead of just reading the result set of the
underlying SELECT statement for the MyView view, which is what
RequestSensitive=True would give you.

<< This means that overall, using VIEWs is a lot slower, presumably unless I
hold a load of pre-prepared parameterized views? >>

Is a lot slower compared to what ?  Opening a view will take the same amount
of time that it takes to execute the equivalent query.

Also, you cannot parameterize views in EDB as of now.

One thing I can look into doing for you is to have the KeepTablesOpen
property also work with views.  That would allow views to stay prepared/open
across queries that open and close them.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks for the explanation Tim, so for best performance we ideally want
RequestSensitive = true?

How does that work in ADO.NET? I notice a "RequestSensitive" property on the
EDBCommand object, but I use EDBDataAdapter when I'm retrieving data, e.g.

       DataTable tbl = new DataTable();
       using (EDBDataAdapter adap = new EDBDataAdapter("select * from
myview", Connection))
       {
         adap.Fill(tbl);
       }

Can "RequestSensitive" help in this scenario?

> Is a lot slower compared to what ?  Opening a view will take the same
> amount of time that it takes to execute the equivalent query.

Using the EDB_Test database (see link on the original post) and the
"view_invlookup" (also in original post) here are my results :-

[averages taken over 5 attempts]
-----------------------------------
RequestSensitive = false
-----------------------------------
Full SELECT query = 3.2 seconds
select * from VIEW = 6.0 seconds [3.5 seconds to prepare, 2.5 to execute]

-----------------------------------
RequestSensitive = true
-----------------------------------
Full SELECT query = 3.9 seconds
select * from VIEW = 3.4 seconds [3 seconds to prepare, 0.4 to execute]

DBISAM 4 (same database)
Full SELECT query = 2.2 seconds

Clearly I need to be using RequestSensitive = true on all queries which use
VIEWs. If you can let me know how to implement that with ADO.NET, that would
be great - thanks.

> Also, you cannot parameterize views in EDB as of now.
Bummer.

> One thing I can look into doing for you is to have the KeepTablesOpen
> property also work with views.  That would allow views to stay
> prepared/open across queries that open and close them.

Ooohh YES PLEASE! Anything which will enhance performance would be greatly
appreciated.

I've rolled out my app with EDB to a few customers and already the feedback
is that it's more 'robust' and 'solid' than with DBISAM (using ContextSoft's
ADO provider), but it is also running a fair bit slower - especially reports
and large database grids (which is where the VIEWs come in).

Regards,

Hedley

Hedley,

<< Thanks for the explanation Tim, so for best performance we ideally want
RequestSensitive = true? >>

Yes, especially when querying views that are not updateable, i.e. can only
generate an insensitive result set.

<< How does that work in ADO.NET? I notice a "RequestSensitive" property on
the EDBCommand object, but I use EDBDataAdapter when I'm retrieving data,
e.g.

       DataTable tbl = new DataTable();
       using (EDBDataAdapter adap = new EDBDataAdapter("select * from
myview", Connection))
       {
         adap.Fill(tbl);
       }

Can "RequestSensitive" help in this scenario? >>

Yes, but you'll need to set the RequestSensitive property to True for the
SelectCommand property before calling the Fill method.

<< I've rolled out my app with EDB to a few customers and already the
feedback is that it's more 'robust' and 'solid' than with DBISAM (using
ContextSoft's ADO provider), but it is also running a fair bit slower -
especially reports and large database grids (which is where the VIEWs come
in). >>

If you're using RequestSensitive=False, then that is the cause, especially
with the views.  BTW, another way around this issue in ADO.NET would be to
simply pass the table name in as the command text, and then set the
SelectCommand's CommandType property to TableDirect.  When you do this, the
view will simply be opened like a table, and it is very quick.

I'll also see about expand the constructors for the EDBDataAdapter so that
these types of properties can be set in the constructor.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hmmm... Well, I'm still seeing incredibly poor VIEW performance in ADO.NET -
even with "RequestSensitive=true".

A lookup VIEW (with RequestSensitive=true) takes 13.5 seconds.
Executing its source SELECT SQL takes 0.7 seconds.

As I need to nail this down asap (it's holding me back from releasing), I
thought the best thing was to create a sample project and provide you with
the database (I know you love this!

Here's the database :-
http://files.pioneersoftware.co.uk/temp/TestDB.zip

Here's the C# test project :-
http://files.pioneersoftware.co.uk/temp/EDB_VIEW_Test.7z

Just hit the "Run Test" button in the app and it will perform and time 2
operations - the SQL SELECT and the VIEW SELECT. It displays both results
(identical) but as you'll see, the VIEW takes about 25 times longer(!).

Thanks,

Hedley

Oops - that TestDB file is a 7z, not a zip - the link should be :-
http://files.pioneersoftware.co.uk/temp/TestDB.7z

Sorry

Hedley,

<< Hmmm... Well, I'm still seeing incredibly poor VIEW performance in
ADO.NET - even with "RequestSensitive=true".

A lookup VIEW (with RequestSensitive=true) takes 13.5 seconds. Executing
its source SELECT SQL takes 0.7 seconds. >>

First of all, you're not comparing apples to apples.  You're comparing a
query that *includes* the WHERE clause against a query that is querying a
view (another query) that *doesn't* contain a WHERE clause.   This results
in a situation where the view is executed first, generating a result set
with 15,000+ rows, and then the query is applied to this result set.   An
equivalent set of queries would actually be this:

using (EDBConnection con = new EDBConnection(sb.ToString()))
  {
 con.Open();

 string sql = @"create temporary table Temp AS
       select inv.id, invoicenum, datetime, total,
       coalesce(sum(alloc.amount), 0) as amtpaid,
       coalesce((sum(total)/count(inv.id))-sum(alloc.amount), 0) as
outstanding,
       app_id, inv.per_id, patient_per_id, per.title, per.firstname,
       per.middlename, per.lastname, per.knownas,
       (coalesce(per.title,'') || '||' || coalesce(per.firstname,'') ||
'||' ||
       coalesce(per.middlename,'') || '||' || per.lastname || '||' ||
       coalesce(per.knownas,'')) as _recipient
       from inv
       inner join per on (inv.per_id = per.id)
       left join alloc on (inv.id = alloc.inv_id)
       group by inv.id, invoicenum, datetime, total, app_id, inv.per_id,
       patient_per_id,
       per.title, per.firstname, per.middlename, per.lastname, per.knownas
WITH DATA;";

 int st = Environment.TickCount;

 using (EDBCommand cmd = new EDBCommand())
 {
   cmd.Connection = con;
   cmd.CommandText = sql;
   cmd.ExecuteNonQuery();
 }

 sql = "select * from Temp where app_id = " + app_id.ToString();

 DataTable tbl = new DataTable();
 using (EDBDataAdapter adap = new EDBDataAdapter(sql, con))
 {
   adap.SelectCommand.RequestSensitive = true;
   adap.Fill(tbl);
   dataGridView1.DataSource = tbl;
 }
 label1.Text = "SQL SELECT took " + ((int)(Environment.TickCount -
st)).ToString() + "ms";
  }

  // test the VIEW
  using (EDBConnection con = new EDBConnection(sb.ToString()))
  {
 con.Open();

 string sql = "select * from view_invlookup where app_id = " +
app_id.ToString();

 int st = Environment.TickCount;
 DataTable tbl = new DataTable();
 using (EDBDataAdapter adap = new EDBDataAdapter(sql, con))
       {
         adap.SelectCommand.RequestSensitive = true;
         adap.Fill(tbl);
         dataGridView2.DataSource = tbl;
       }
       label2.Text = "VIEW took " + ((int)(Environment.TickCount -
st)).ToString() + "ms";
     }

Do you understand the difference ?   The straight SQL can benefit from being
able to push the WHERE clause condition down onto the source tables *prior*
to executing the joins, whereas the view has to execute the joins for the
entire set of source tables *first*, and then the WHERE clause is applied to
that result set.

This is how views work - they are shared among multiple query instances, so
it isn't possible to push down the WHERE conditions into the view execution
without causing the view to be re-executed every time it is opened, which
then defeats the purpose of having a view act like a table and only
materialized once at open table.

--
Tim Young
Elevate Software
www.elevatesoft.com

>> Do you understand the difference?

Yes, I now understand how EDB handles VIEWs. Please forgive my ignorance in
this matter Tim.

>> This is how views work

Do you mean "this is how they work in EDB"? Although I don't have anywhere
near your experience, with other engines I've used (PGSQL, MySQL, SQL
Server), I have never noticed that using a VIEW + WHERE clause has come with
any kind of performance hit like EDB does.

Just in case I was going mad, I tried the same queries on the same database
in PostgreSQL. This was not to make a PGSQL vs EDB comparison (which would
be unfair), but rather to see if VIEWs with WHERE clauses were slower in
PGSQL too.

PGSQL : select * from VIEW <no where> = 0.515 secs [~16000 rows]
PGSQL : select * from VIEW with WHERE = 0.016 sec [1 row] <<<

PGSQL : select <FULL SQL> <no where> = 0.532 secs [~16000 rows]
PGSQL : select <FULL SQL> with WHERE = 0.016 sec [1 row]

(using EDB Mgr with RequestSensitive=true)
EDB : select * from VIEW <no where> = ~4 secs [~16000 rows]
EDB : select * from VIEW with WHERE = ~4 secs [1 row] <<<

EDB : select <FULL SQL> <no where> = ~4 secs [~16000 rows]
EDB : select <FULL SQL> with WHERE = 0.16 secs [1 row]

So I guess PGSQL must treat VIEWs + WHEREs differently under the hood?

Anyway - I do have some good news!

You may recall that I previously mentioned that I had coded some clever
jiggery-pokery with DBISAM so that I could use 'views' (after a fashion)
with DBISAM.
[more info:
http://www.elevatesoft.com/newsgrp?action=searchopenmsg&group=16&msg=8613]

I simply adapted that code to work with EDB, i.e :-

- intercept "SELECT x from VIEW" statements before they are executed
- get the VIEW source using EDBConnection.GetSchema("views")
- substitute in the original SQL
- adjust the WHERE clause column names accordingly

Problem solved!

I am curious as to when you would recommend the use of VIEWs in EDB? (The
only time I can think of is if you're executing the exact same SELECT SQL
over-and-over and if you hold on to the prepared VIEW query?)

Lastly, you mentioned :-
>> One thing I can look into doing for you is to have the KeepTablesOpen
>> property also work with views.  That would allow views to stay
>> prepared/open across queries that open and close them.

Would that help the scenario when you're JOINing a query to one or more
VIEWs? (I do that quite a lot and it's still a bit slow).

Many thanks again for your excellent support.

Look forward to your thoughts,

Hedley.