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

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

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
>

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

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]

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.

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