Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 7 of 7 total |
Disappointing Performance |
Wed, Dec 19 2012 12:28 PM | Permanent Link |
James Dooley | Over the past several days I've been evaluating ElevateDB by building up a fairly straight forward little demo database similar to the Chinook database over on CodePlex and was getting on fine until I decided to create a simple view showing: Artists -> Albums -> tracks, as follows:
SELECT a.*, b.*, t.* FROM Artist a, Album b, Track t WHERE (a.Id = b.ArtistId) AND (b.Id = t.AlbumId) Very simple really, just primary keys and foreign keys and yet it takes 14 minutes to execute the select! There are 275 artists, 347 albums and 3503 tracks, so not really that big. My question is how can I see an execution plan for this query and how can I start optimising it? I've already executed the OPTIMIZE command on each table involved with no impact. TheNiftyCoder.com |
Wed, Dec 19 2012 1:42 PM | Permanent Link |
Terry Swiers | Hi James,
I'm sure someone will give a more elegant response, but here is what I see. Without specifically telling the engine how to link, your query is having to read through the album table for every artist, and then for each match between those two tables it has to read through the entire track table to find the matches on the tracks. Rework your query to use joins between the tables and you should see a huge difference as it can use the indexes on those fields rather than doing a brute force search. SELECT a.*, b.*, t.* FROM Artist a join Album b on a.Id = b.ArtistId join Track t on b.Id = t.AlbumId --------------------------------------- Terry Swiers Millennium Software, Inc. http://www.1000years.com http://www.atrex.com --------------------------------------- |
Wed, Dec 19 2012 3:30 PM | Permanent Link |
Raul Team Elevate | As Terry said use proper join statement or currently what you have is a
massive cross join on 3 tables (likely millions of rows if any of the tables is non-trivial in size). I would also suggest that you have indexes on relevant fields (ones used in join) if you don't already. When using EDBManager just make sure "request execution plan" is selected and in addition to result set tab you get a Execution Plan tab. Table optimize rebuilds indexes and frees up empty space in table file but it's unlikely to improve your query much at this stage. Raul On 12/19/2012 12:28 PM, James Dooley wrote: > Over the past several days I've been evaluating ElevateDB by building up a fairly straight forward little demo database similar to the Chinook database over on CodePlex and was getting on fine until I decided to create a simple view showing: Artists -> Albums -> tracks, as follows: > > SELECT a.*, b.*, t.* > FROM Artist a, Album b, Track t > WHERE (a.Id = b.ArtistId) AND (b.Id = t.AlbumId) > > Very simple really, just primary keys and foreign keys and yet it takes 14 minutes to execute the select! There are 275 artists, 347 albums and 3503 tracks, so not really that big. > > My question is how can I see an execution plan for this query and how can I start optimising it? I've already executed the OPTIMIZE command on each table involved with no impact. > TheNiftyCoder.com > |
Wed, Dec 19 2012 5:01 PM | Permanent Link |
James Dooley | Hi Guys,
Thanks for the quick response, you were right on the money! Can't understand how I missed the 'request execution plan', I was looking on every dialog for such an option and it was staring me in the face! Although I'm a bit concerned that it was not able to figure this one out itself. I'm running the same set of queries over 4 database products and this is the first time that ElevateDB has blown out..... the others have died horrible deaths on more than a few occasions... so it is still ahead! best regards, James PS - am I allowed to mention competitors on this forum??? |
Thu, Dec 20 2012 3:43 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | James
>PS - am I allowed to mention competitors on this forum??? Yes, and you can even tell us where they are better (or worse) than ElevateDB. The only thing I know that will get Tim venting smoke from his ears is when people try to get into the contrived speed tests arguments. One of the nice things about these newsgroups is the lack of censorship (add in helpfulness and a willingness to discuss things). Roy Lambert [Team Elevate] |
Thu, Dec 20 2012 10:00 AM | Permanent Link |
Adam Brett Orixa Systems | James
Good luck with the trialing EDB (is unbeatable in my opinion btw, but I am interested to hear about competitors as I have really only used Firebird & MS-Access in anger & touched MS-SQL) I regularly write systems where 100,000s of data rows have to be queried in joined tables (i.e. millions of combined rows). EDB regularly gives me results back in a few seconds or less. I do often use pre-written VIEWS to help with this ... but still EDB has the capability to do it really well. As a general comment I would say that a key is "to make the biggest table smaller as early in the query process as possible" i.e. in your case your "tracks" table will be the biggest. Say you are looking for tracks written in 2001. By leading with the tracks table in the FROM & querying down to just the 2001 tracks as the very first step. Then join to Album & Group. In this way the already concise Tracks dataset is created once and then has Album & Group data appended (using primary keys on each of these tables which is bound to be fast). If you go the other way (I believe) EDB has to do the WHERE retrieval for the 2001 for each Group/Album. SELECT T.*, A.* G.* FROM Tracks T LEFT JOIN Albums A ON A.ID = T.AlbumID LEFT JOIN Groups G ON G.ID = A.GroupID WHERE T.YearReleased = '2001' ... I may be out of date with how EDB does its query optimization, but switching some of my queries around in this way had a huge, huge impact for me a few years back & I now use this method as standard. |
Wed, Jan 2 2013 6:31 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | James,
<< Although I'm a bit concerned that it was not able to figure this one out itself. >> When you don't use JOIN clauses in the FROM clause (SQL-92 joins), but rather use SQL-89 joins in the WHERE clause, EDB does not even attempt to optimize them. This is mentioned in the FAQ here: http://www.elevatesoft.com/supportfaq?action=view&category=edb&question=joins_where_clause and in the manual here: http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=SELECT =========================== Note: If you specify multiple tables in the FROM clause without specifying JOIN clauses between all of them, then the tables without applicable JOIN clauses will be joined using a CROSS JOIN, which is a join that joins every row from the source table to every row in the target table. This produces a cartesian product of both tables, and even very small tables can result in very large result sets, so one should be careful to ensure that join conditions are always specified for all tables in the SELECT statement. WHERE Clause -------------------- The WHERE clause is used to filter the rows output into the result set after the rows have been filtered using any join expressions that may be present. The WHERE clause can contain any valid boolean SQL expression. Note: Aggregate functions such as the MIN, MAX, or SUM functions cannot be used anywhere in the WHERE clause. Also, do not specify joins in the WHERE clause according to the outdated SQL-89 SQL standard. Use the SQL-92 or higher standard JOIN syntax mentioned above instead. ElevateDB will not optimize any joins that are specified in the WHERE clause. =========================== If you have any other questions, please let me know. Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Friday, April 26, 2024 at 06:09 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |