LINQ expression trees and performance - Proposal

Question

LINQ expression trees and performance - Proposal

ronefrati

Old Hand

Points: 329
More actions
October 10, 2008 at 11:05 am

#194291

Wouldn't it be much more economical, performance wise, both on client side and server side that SQLserver LINQ provider would send to the server the expression tree generated at run time instead of SQL statements ?
Doing so will reduce CPU time on client to generate the SQL statement while reducing parsing and expression tree buildup to the server ?
( This suggestion does not imply abounding SQL statement support capabilities on server side , just another way to communicate an a more optimized method ).
Such a method would decrease the need for store procedure to improve performance.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

The database engine understands TSQL. It doesn't build a expression tree from the TSQL, it creates an optimized execution plan so that the database engine knows how best to pull the data from the physical storage, but that is it. Stored Procedures can and will outperform dynamically generated code like that created by LINQ because someone with experience, can tune the indexes for the procedure code, as well as perform optimizations to the TSQL itself where LINQ has to be a bit more universal. You won't find a ORM mapper that is perfect all the time, though they tend to be decent most of the time. There are cases though, where the TSQL simply isn't the most efficient and a experienced DBA or programmer can write a much more efficient stored procedure.

ronefrati Old Hand Points: 329 More actions · Answer 2

The main issue of my suggestion is not SP. Your comment does not relate to the advantage suggested ( Obviously relevant for future SQ Server )

On of the advantages of LINQ is a better support for application that does not know , at compile time, the exact quries it will performed.

For such an applications , my suggestion can increase performance.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

It is obvious from your post that you have no idea how the Query Engine in SQL Server works. The fact that CLR Integration was added to SQL Server 2005 and carried into SQL Server 2008 has nothing to do with how SQL Server processes queries or functions. TSQL is still the only way to interact with data in the database engine, and CLR is not a replacement in any way shape or form for TSQL. Microsoft's own recommendations are to do data access with TSQL and not in CLR inside SQL Server.

If you think that your idea has benefit, then recommend it on the SQL Server Connect site, where someone on the SQL Development Team at Microsoft will be able to comment and provide information to you regarding its feasibility.

http://connect.microsoft.com/sqlserver/

The proper way to move processing off of a client is to implement a multi-tiered application using WCF, webservices, or perhaps remoting, and defer intesive processing to the middle tier which then can pass the TSQL request from LINQ to the database.

ronefrati Old Hand Points: 329 More actions · Answer 4

Unfortunately It is obvious that you are hardheaded without dynamic thinking.

It obviously true that only TSQL statements are process by the SQL server engine. This is exactly what I'm suggesting to to enhance. I'm suggestion that the SQL server engine will also accept Expression Trees , BTW - SQL server builds inside his engine different type of expression tree (based on sql statements ) which are the basis to determine the execution plan.

( This process and more are done when compiling SP )

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

I'd recommend that you read up on ANSI standards for SQL. I might be hardheaded, but I understand what I am talking about. You also might pickup Kalen Delaney's book:

Inside Microsoft® SQL Server(TM) 2005: Query Tuning and Optimization http://www.amazon.com/Inside-Microsoft%C2%AE-SQL-Server-2005/dp/0735621969/ref=sr_1_2?ie=UTF8&s=books&qid=1223731099&sr=8-2

Like I said, the route to make the suggestion you have is connect feedback. However, I don't expect it to get traction with Microsoft unless you can muster up significant community support for it.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

ronefrati (10/11/2008)
Unfortunately It is obvious that you are hardheaded without dynamic thinking.

That was uncalled for. Please be respectful and polite to the other forum members, especially the ones you disagree with.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

ronefrati (10/11/2008)
I'm suggestion that the SQL server engine will also accept Expression Trees , BTW - SQL server builds inside his engine different type of expression tree (based on sql statements ) which are the basis to determine the execution plan.

I'm not sure that reproducing both the parser and the algebratiser (the component of the SQL engine that converts queries into expression trees) in the .net framework is feasible

Firstly, it would require that the the parser can handle SQL that's valid for all versions of SQL server that the LINQ can use. iirc, that's at least SQL 2000, 2005 and 2005. The parser just for one of those is complex enough.

It also means that the front end app would be responsible for checking existence of tables and columns, which is one of the things the parser is responsible for. That would require either caching on the front end or checking against the database. The former allows for the possibility of out-of-date metadata, the latter will increase the network traffic.

The adjusting of SQL to allow it to accept an expression tree is probably the easiest part. Adjusting the LINQ engine to generate that expression tree is probably a lot trickier.

That said, post the suggestion on connect and let's see what the dev team says. If you do post on connect, please post the url for the connect item here so we can follow up on it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ronefrati Old Hand Points: 329 More actions · Answer 8

For your request:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=374892

ronefrati Old Hand Points: 329 More actions · Answer 9

In regards to: "I'm not sure that reproducing both the parser and the algebratiser (the component of the SQL engine that converts queries into expression trees) in the .net framework is feasible"

It is not my intention, No modification is require at the .NET side (except from the SQL data provider which will send the ET (Expression Tree) he will receive from the already generated ET by the LINQ at compile time and enhanced at run time .

My intention is that SQL server will use those ET (maybe with additional statistics information ) to build the execution plan.

Therefor you statement that follow is irrelevant:

"Firstly, it would require that the the parser can handle SQL that's valid for all versions of SQL server that the LINQ can use. iirc, that's at least SQL 2000, 2005 and 2005. The parser just for one of those is complex enough."

With regards to:

"It also means that the front end app would be responsible for checking existence of tables and columns, which is one of the things the parser is responsible for. "

No, the input ET will be validated with schema at the parser does.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

ronefrati (10/11/2008)
In regards to: "I'm not sure that reproducing both the parser and the algebratiser (the component of the SQL engine that converts queries into expression trees) in the .net framework is feasible"
It is not my intention, No modification is require at the .NET side (except from the SQL data provider which will send the ET (Expression Tree) he will receive from the already generated ET by the LINQ at compile time and enhanced at run time .
My intention is that SQL server will use those ET (maybe with additional statistics information ) to build the execution plan.

So either the SQL data provider will have to generate an expression tree identical to the one that the SQL parser and algebratiser would generate (which would require a great deal of info on the schema of the target tables, as well as the ability to expand views if necessary) or the SQL engine would have to implement a second 'parser' that can validate and bind the expression tree. Your comment that SQL would validate the input expression tree against the schema indicates the latter is what you have in mind.

Why do you think that parsing is a bottleneck? You say

Such a method would decrease the need for store procedure to improve performance.

but the performance improvements of stored procedures (which are minimal in 2005 and higher) aren't due parsing. Stored procs have to be parsed, bound and optimised just like ad-hoc code, unless their execution plan is in cache. Adhoc code is also parsed, bound and optimised if a matching and reusable plan is not in cache.

Adhoc code is less likely than stored procs to have matching plans in cache, which is the reason why procs are often slightly faster. The slowest portion of the parse, bind and optimise process is the optimise. The other two are trivial in comparison.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 11

GilaMonster (10/11/2008)
but the performance improvements of stored procedures (which are minimal in 2005 and higher) aren't due parsing. Stored procs have to be parsed, bound and optimised just like ad-hoc code, unless their execution plan is in cache. Adhoc code is also parsed, bound and optimised if a matching and reusable plan is not in cache.
Adhoc code is less likely than stored procs to have matching plans in cache, which is the reason why procs are often slightly faster. The slowest portion of the parse, bind and optimise process is the optimise. The other two are trivial in comparison.

This is the point I was trying to make with this statement:

Stored Procedures can and will outperform dynamically generated code like that created by LINQ because someone with experience, can tune the indexes for the procedure code, as well as perform optimizations to the TSQL itself where LINQ has to be a bit more universal. You won't find a ORM mapper that is perfect all the time, though they tend to be decent most of the time.

All things are equal when the same code is run. I have seen more than a few LINQ generated SQL Statements that just don't perform compared to a manually rewritten statement using "tricks of the trade." Especially when RBAR statements get generated by LINQ.

There are always two different groups on subjects like this post. Developers see a dual-quad core processor SQL Server with 64GB of RAM and would love to offload any processing that they can to that server. DBA's think about the cost of building a server of that size as well as the cost to upgrade that server when compared to the cost of building a smaller application server. I would rather see a smaller app server handle certain processing over my SQL Server doing it. It is much easier to add another app server than it is to change out a SQL Server.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

Actually, I see this as a fairly interesting proposal, particularly as a path-forward starting point for allowing some kind of dynamic querying while preventing injection threats.

The problem is that at the moment, there are substantial gaps on both the client and the server-side for implementing something like this. I have been working of some of my own stuff for implementing something similar, but not with LINQ (which I am not up to speed on yet). However, a Microsoft implemented enhancement like this would be a welcome feature that would help to address a number of serious deficiencies.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 13

rbarryyoung (10/26/2008)
Actually, I see this as a fairly interesting proposal, particularly as a path-forward starting point for allowing some kind of dynamic querying while preventing injection threats.

LINQ already allows dynamic querying while preventing injection threats. At least in my own experience testing it, it always passed parameterized queries to the database engine.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

Could you give me a pointer to an example of how this is done, Jonathan? I think that we are probably talking about different things, but I do not know enough about LINQ yet to say...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]