Simple.Data Default Generated Queries and Performance

Question

I am thinking of using Simple.Data Micro-ORM for my ASP.NET 4.5 website. However, there is something that I need to know before deciding whether to use it or not.

Let's take the following Join query for example:

var albums = db.Albums.FindAllByGenreId(1)
  .Select(
db.Albums.Title,
db.Albums.Genre.Name);

This query will be translated to:

select 
 [dbo].[Albums].[Title],
 [dbo].[Genres].[Name] 
from [dbo].[Albums] 
   LEFT JOIN [dbo].[Genres] ON ([dbo].[Genres].[GenreId] = [dbo].[Albums].[GenreId]) 
WHERE [dbo].[Albums].[GenreId] = @p1
@p1 (Int32) = 1

Let's assume that the 'Genres' table is a a table with thousands or even millions of rows. I think that it might be very inefficient to filter the data after the JOIN has taken place, which is what this query translated for in Simple.Date.

Would it be better to filter the data firs in the Generes table, which means create make a SELECT statement first and make the JOIN with that filtered table?

Wouldn't it be better to filter the data ahead of time?

Furthermore, is there an option to make that type of complex (JOIN on a filtered table) query using Simple.Data.

Need your answer to know if to proceed with Simple.Data, or damp it in favor of another micro-ORM.

Answer 1

You are confused about how SQL is interpreted and executed by the database engine. Modern databases are incredibly smart about the best way to execute queries, and the order in which instructions appear in SQL statements has nothing to do with the order in which they are executed.

Try running some queries through SQL Management Studio and looking at the Execution Plan to see how they are actually optimised and executed. Or just try the SQL you think would work better and see how it actually performs compared to what is generated by Simple.Data.

Answer 2

The sql that Simple.Data is generating is idomatic T-SQL, too be honest its what I would be writing if I was drafting the sql myself.

This sql allows Sql Server to optimise the execution plan which should mean the most efficient retrieval of data.

Answer 3

The beauty of Simple.Data is that if you have any doubts or issues with the sql it generates you can just call a stored proc:

db.ProcedureWithParameters(1, 2);