Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 14 total |
VIEWs running slower than queries in 2.02b10 |
Thu, Mar 12 2009 9:17 AM | Permanent Link |
"Hedley Muscroft" | 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! |
Thu, Mar 12 2009 12:21 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Thu, Mar 12 2009 12:53 PM | Permanent Link |
"Hedley Muscroft" | 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. |
Thu, Mar 12 2009 1:08 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Thu, Mar 12 2009 1:36 PM | Permanent Link |
"Hedley Muscroft" | 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 |
Thu, Mar 12 2009 6:55 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Fri, Mar 13 2009 6:06 AM | Permanent Link |
"Hedley Muscroft" | 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 |
Fri, Mar 13 2009 6:08 AM | Permanent Link |
"Hedley Muscroft" | Oops - that TestDB file is a 7z, not a zip - the link should be :-
http://files.pioneersoftware.co.uk/temp/TestDB.7z Sorry |
Fri, Mar 13 2009 4:02 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Fri, Mar 13 2009 8:54 PM | Permanent Link |
"Hedley Muscroft" | >> 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. |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, May 7, 2024 at 06:25 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |