While reviewing LINQ TO SQL with a colleague recently I discovered yet another "hickup" in v 1.0 of LINQ TO SQL. The first couple can be found here.
The DBA's are going to love this. I believe that once they see this bit of information they will have a little more firepower against their cutting edge developers using LINQ TO SQL (Me included).
So what is this latest hickup? My colleague wanted to know how the code generated by LINQ TO SQL is executed against the server. I showed him in profiler that it uses parameterized queries (a good thing) and of course executes the stored procedures correctly the same way. He was satisfied with this.
Then he asked a question that I didn't even think of (most developers don't); what do the caching plans look like? I had no idea and had to have him remind me of the syntax. For those who don't know its:
select sql, * from master..syscacheobjects
To make it easier you can put a wildcard search (where sql like '%usp_individual_searchbylastname%') to only return the cache plans for your specific proc.
I have a simple form that has a "Last Name" text box on it. When I click the search button LINQ TO SQL executes my stored procedure "usp_individual_searchbylastname".
When I ran that search my cache plan looked pretty good. Well they did the first time I ran the proc through LINQ TO SQL. The second, third, fourth, etc times I executed my procs through LINQ TO SQL it looked worse and worse. Ultimately I searched 5 different last names and unfortunately got 5 different cache plans. Here is what I found in the syscacheobjects SQL column when I ran the SQL statement above:
(@p0 nvarchar(16),@RETURN_VALUE int output)
EXEC @RETURN_VALUE = [dbo].[usp_individual_searchbylastname] @lastname = @p0
(@p0 nvarchar(4),@RETURN_VALUE int output)
EXEC @RETURN_VALUE = [dbo].[usp_individual_searchbylastname] @lastname = @p0
(@p0 nvarchar(8),@RETURN_VALUE int output)
EXEC @RETURN_VALUE = [dbo].[usp_individual_searchbylastname] @lastname = @p0
(@p0 nvarchar(9),@RETURN_VALUE int output)
EXEC @RETURN_VALUE = [dbo].[usp_individual_searchbylastname] @lastname = @p0
(@p0 nvarchar(6),@RETURN_VALUE int output)
EXEC @RETURN_VALUE = [dbo].[usp_individual_searchbylastname] @lastname = @p0
Notice anything weird about the first parameter p0 (it's defined as @lastname nvarchar(50) in the stored procedure)? The size of the parameter is being determined NOT by the actual parameter definition, rather it's being determined by the length of the string value being passed to the LINQ TO SQL generated function. So instead of getting 1 good cache plan I'm getting five. What's worse is that this has the potential to have 50 different plans.
If that wasn't bad enough the same thing happened to my insert proc as well. I have a form that allows me to insert a record into the individual table. When I save the record the proc "usp_individual_insert" is executed. I inserted two records and like before got two different plans.
(@p0 nvarchar(2),@p1 nvarchar(4),@p2 nvarchar(1),@p3 nvarchar(5),@p4 nvarchar(4000),@p5 nvarchar(14),@p6 varchar(4),@p7 varchar(4),@p8 ntext,@p9 int,@p10 int output,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[usp_individual_insert] @Salutation = @p0, @FirstName = @p1, @MiddleName = @p2, @LastName = @p3, @Suffix = @p4, @EmailAddress = @p5, @PasswordHash = @p6, @PasswordSalt = @p7, @Demographics = @p8, @customerid = @p9, @individualid = @p10 OUTPUT
(@p0 nvarchar(2),@p1 nvarchar(5),@p2 nvarchar(7),@p3 nvarchar(5),@p4 nvarchar(4000),@p5 nvarchar(26),@p6 varchar(4),@p7 varchar(4),@p8 ntext,@p9 int,@p10 int output,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[usp_individual_insert] @Salutation = @p0, @FirstName = @p1, @MiddleName = @p2, @LastName = @p3, @Suffix = @p4, @EmailAddress = @p5, @PasswordHash = @p6, @PasswordSalt = @p7, @Demographics = @p8, @customerid = @p9, @individualid = @p10 OUTPUT
This has the potential to have just an ungodly number of cache plans. There are 8 different nvarchar or varchar parameters defined on this proc with lengths between 8 and 50. You do the math.
At this point if they don't fix this problem (and the stupid refresh problem) I'm going to say that I may have to substitute LINQ TO SQL with something else. My current project uses a facade class that interfaces with the LINQ TO SQL Datacontext class so it won't be that difficult to swap it out with something like subsonic. Although one of the guys who created subsonic is on the LINQ TO SQL team.
I'm hoping that the LINQ TO SQL team fixes these issues and does it soon.
UPDATE: SubSonic doesn't parameterize their queries therefore they do not generate syscacheobject records.
UPDATE #2: Somebody pointed out that "hickup" is mispelled. I told them it was on purpose to further empasize the problems I found. I guess it didn't come across as obvious. Next time I'll use cRaZy CaSe. Ok... Maybe not.
Technorati Tags: LINQ,LINQ TO SQL,.NET 3.5
posted @ Tuesday, February 19, 2008 6:51 PM