SQL queries in .NET

Question

I normally use Entity Framework for my SQL queries which is great as it allows me to dynamically construct queries in a strongly-typed and maintainable fashion.
However, I'm on a project at the moment which utilises spatial queries. Most of my queries will output a resultset order by time or distance from a given co-ordinate. However, I have found that ordering by STDistance slows the query down 10 fold. (actually it slows down if I join on another table in addition to the "order by")

I have managed to optimize the query myself and got the performance back to where it should be, however, this query cannot be produced by Entity Framework.

So I could end up having a set of "order by time" queries generated by EF and then another set of "order by distance" queries as stored procedures in SQL Server. The trouble is that, basically, this latter set of queries would have to be created via string concatenation (either in an SP or C#).

I have been trying to get away from sql string concatenation for years now and these ORM frameworks are great for 99% of queries, however, I always find myself having to go back to string concanenation so I get the most optimal queries sent to the server. This is a maintenance nightmare though.

String concatenation was solved in ASP.NET with templating engines, which essentially allow you to build html strings. Does anyone know such a solution for SQL strings? Although it's a bit messy in some respects it would allow for the most optimal queries. In my view this would be better than

String concat in a stored proc
String concat in C#
Masses of duplicate code in stored procs covering all possible input parameters
LINQ queries which can create sub-optimal SQL

I'd love to know your thoughts about this general problem and what you think of my proposed solution.
thanks Kris

In my opinion, LINQ has been designed with performance very much in mind. Optimising queries has been done and can be used by LINQ. I would be inclined to stick with .OrderBY. — SkonJeet
– SkonJeet, Commented Mar 5, 2012 at 12:02
But using LINQ-to-Entities results in a query which is 10 times slower than an optimised SQL query. (> 1.2 seconds, rather than 170ms) — krisdyson
– krisdyson, Commented Mar 5, 2012 at 12:17

Dhwanil Shah · Accepted Answer · 2012-03-05 12:58:11Z

Have you checked out the latest Entity Framework Beta? It is supposed to have support for spatial data types.

Also, if you want to build SQL queries dynamically, check out PetaPoco's SQL Builder. Some examples from the site:

Example 1:

var id=123;
var a=db.Query<article>(PetaPoco.Sql.Builder
    .Append("SELECT * FROM articles")
    .Append("WHERE article_id=@0", id)
    .Append("AND date_created<@0", DateTime.UtcNow)
)

Example 2:

var id=123;
var sql=PetaPoco.Sql.Builder
    .Append("SELECT * FROM articles")
    .Append("WHERE article_id=@0", id);

if (start_date.HasValue)
    sql.Append("AND date_created>=@0", start_date.Value);

if (end_date.HasValue)
    sql.Append("AND date_created<=@0", end_date.Value);

var a=db.Query<article>(sql)

Example 3:

var sql=PetaPoco.Sql.Builder()
    .Select("*")
    .From("articles")
    .Where("date_created < @0", DateTime.UtcNow)
    .OrderBy("date_created DESC");

Morten Mertner · Accepted Answer · 2012-03-05 12:24:31Z

Maybe it would be an option to use a T4 template to generate the views (and corresponding queries). You could modify either the template or override the generated output selectively (although this may possibly also require you to modify the template).

Microsoft provides a T4 template for this purpose (assuming you're not using code-first, as I'm not sure the equivalent exists for that scenario).

Incidentally, pre-compiling the views also provides for faster startup as the views don't have to be generated at run-time.

b0rg · Accepted Answer · 2012-03-05 12:40:58Z

Hard to understand the question.

Any kind of EF orm will always be slower than handcrafted sql. So we're left with manually creating and managing those sql queries. This can be done

manually write the procs
write "smart procs" with sql string concatenation in them
use templating engine for generating those procs at compile time
Write your own linq to sql provider for runtime generation of queries

all those have upsides and downsides, but if you have good unit test coverage it should protect you from obvious errors where someone has renamed the field in the database.

Collectives™ on Stack Overflow

SQL queries in .NET

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related