Ayende @ Rahien

Ayende Rahien commented on UberProf new feature: Query Plan Cache Misuse

Thu, 21 Jan 2010 15:02:33 GMT

Thanks, I updated the documentations

gunteman commented on UberProf new feature: Query Plan Cache Misuse

Fri, 15 Jan 2010 09:54:43 GMT

Yeah, I know. It just sounded a bit wrong to me. High cache usage is something good, in my book. Maybe "higher " can be substituted for "inefficient", to remove any doubts

Ayende Rahien commented on UberProf new feature: Query Plan Cache Misuse

Fri, 15 Jan 2010 05:29:20 GMT

Gunteman, It is an issue of semantics, since we have more cache entries, for what is basically the same query.

gunteman commented on UberProf new feature: Query Plan Cache Misuse

Thu, 14 Jan 2010 22:00:49 GMT

I think the wording is a bit confusing.. "Different parameter sizes result in higher query plan cache usage". If anything, the cache usage is reduced. The cache size increases, though. Maybe "Different parameter sizes result in reduced use of cached query plans"?

Ayende Rahien commented on UberProf new feature: Query Plan Cache Misuse

Thu, 14 Jan 2010 03:31:17 GMT

tobi, NHibernate already does that when you put the constant as part of the query, yes.

tobi commented on UberProf new feature: Query Plan Cache Misuse

Wed, 13 Jan 2010 22:12:32 GMT

I wonder if you can make NHibernate not parameterize the value of constant parameters. This should be a performance improvement as the optimizer has more information. It also does not pollute the cache to much because at most as many slots are filled than you have distinct queries in your code.

Ayende Rahien commented on UberProf new feature: Query Plan Cache Misuse

Wed, 13 Jan 2010 16:39:37 GMT

Dmitry, Why wouldn't it be cached?

Dmitry commented on UberProf new feature: Query Plan Cache Misuse

Wed, 13 Jan 2010 16:38:47 GMT

Oren, Does your profiler actually checks execution plans? I doubt a query like INSERT INTO (field1, ... , @fieldN) VALUES (@value1, ... , @valueN) would have the execution plan cached.

Ayende Rahien commented on UberProf new feature: Query Plan Cache Misuse

Wed, 13 Jan 2010 16:38:05 GMT

Jarod, That is just an example, to show the problem, not talking about those specific queries

Jarod commented on UberProf new feature: Query Plan Cache Misuse

Wed, 13 Jan 2010 16:12:24 GMT

Ya, not for inserts. I was referring to this part of the post: ========================= Let us say that we issue two queries, to find users by name. (Note that I am using a syntax that will show you the size of the parameters, to demonstrate the problem). We can do this using the following queries. exec sp_executesql N'SELECT * FROM Users WHERE Username = @username', N'@username nvarchar(3)', @username=N'bob' exec sp_executesql N'SELECT * FROM Users WHERE Username = @username', N'@username nvarchar(4)', @username=N'john' This sort of code result in two query plans stored in the database query cache, because of the different parameter sizes.

Ayende Rahien commented on UberProf new feature: Query Plan Cache Misuse

Wed, 13 Jan 2010 16:06:51 GMT

Jarod, Note that I am doing INSERTs here, not queries, I don't think that you can compile that.

Jarod commented on UberProf new feature: Query Plan Cache Misuse

Wed, 13 Jan 2010 16:04:53 GMT

In EF, isnt this solved by compiling the query?

Ayende Rahien commented on UberProf new feature: Query Plan Cache Misuse

Wed, 13 Jan 2010 15:48:04 GMT

Dmitry, This result in the same alert as well: using (var db = new Entities(conStr)) { for (int i = 0; i < 5; i++) { db.AddToUsers(new Users { Username = new string('a', i) }); } db.SaveChanges(); }

Dmitry commented on UberProf new feature: Query Plan Cache Misuse

Wed, 13 Jan 2010 15:44:54 GMT

I did not realize it has this issue with binary data. I was talking about strings/decimals.

Ayende Rahien commented on UberProf new feature: Query Plan Cache Misuse

Wed, 13 Jan 2010 15:31:07 GMT

Dmitry, In other words, EF certainly have this issue.

Ayende Rahien commented on UberProf new feature: Query Plan Cache Misuse

Wed, 13 Jan 2010 15:30:19 GMT

Dmitry, The following EF 3.5 code is going to result in this alert: using (var db = new Entities(conStr)) { for (int i = 0; i < 5; i++) { db.AddToUsers(new Users { Password = new byte[i] }); } db.SaveChanges(); }

Dmitry commented on UberProf new feature: Query Plan Cache Misuse

Wed, 13 Jan 2010 15:27:53 GMT

@Frans, EF never had that bug. It has a different issue though. Constant query parameters are not parameterized. This is true in EF4 as well. Does anyone know if it can cause the same execution plan problem?

Ayende Rahien commented on UberProf new feature: Query Plan Cache Misuse

Wed, 13 Jan 2010 14:18:21 GMT

Rumburak, You set the parameter length (which is separate than the _value_ length. Frans, Yes, it is refering to this bug. Dmitry, Yes.

Dmitry commented on UberProf new feature: Query Plan Cache Misuse

Wed, 13 Jan 2010 14:13:52 GMT

I guess NH has finally been modified to set parameter length even when prepare_sql is false. I modified the source locally to do that a while ago.

Frans Bouma commented on UberProf new feature: Query Plan Cache Misuse

Wed, 13 Jan 2010 11:31:31 GMT

Isn't this due to a bug in EF and L2S which are solved in 4.0 and L2S for .net 4? Any sane o/r mapper would simply pass the length of the field to filter in the parameter (IMHO) (btw, the font in this textbox is really small in FF3.5)

rumburak commented on UberProf new feature: Query Plan Cache Misuse

Wed, 13 Jan 2010 11:27:29 GMT

Ok, so how to avoid variable length with varchars? Pad them with spaces or what?