Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
waveform
Yak Posting Veteran
93 Posts |
 Posted - 2011-11-16 : 09:15:56
|
Sorry in advance for the verbosity. :)I've been reading up on query plans and how to minimise duplicate plans being created by SQL Server for the same basic query. For example, if I understand correctly, sending both these query strings will result in 2 different query plans:"SELECT FirstName FROM Members WHERE LastName = 'Lee'""SELECT FirstName FROM Members WHERE LastName = 'MacGhilleseatheanaich'"Using a stored procedure avoids this, as the query plan is the same, and "LastName" is passed as a variable, eg:CREATE PROCEDURE sp_myStoredProcedure @LastName varchar(100)ASSELECT FirstName FROM Members WHERE LastName = @LastNameGo Now, my question is whether the same applies to the Command object (eg. SQLClient.SQLCommand in ADO.NET). The reason I ask is that string parameters don't have a defined max length, as in the code above. Take the following code:MyCmd.CommandText = "SELECT FirstName FROM Members WHERE LastName = @LastName"MyCmd.Parameters.AddWithValue("@LastName", "Lee")Then later:MyCmd.Parameters.Clear()MyCmd.Parameters.AddWithValue("@LastName", "MacGhilleseatheanaich")Since @LastName hasn't been declared to SQL Server as having a defined maximum length, will SQL Server create a new query plan for every different value? |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-11-16 : 11:03:47
|
| why don't you want to use stored procedures? |
 |
|
|
waveform
Yak Posting Veteran
93 Posts |
Posted - 2011-11-16 : 11:26:32
|
| For this project, it's easier to maintain the DAL's code than maintain SPs. It's not a pre-planned system, there will be lots of changes during development so this is just easier and quicker.SPs can be done later, but for now dev time is (apparently) of the essence. Not my call I'm afraid.Does that win me an answer? :) |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
|
|
waveform
Yak Posting Veteran
93 Posts |
Posted - 2011-11-17 : 01:02:36
|
| Thanks, perhaps I should add the reason I ask is from reading how LINQ2SQL in .NET 3.5 can miss the cache by defining different parameter lengths. See http://damieng.com/blog/2009/12/13/sql-server-query-plan-cache.LINQ creates a query and sets string parameter length to the exact length of the input. So the next use of the same LINQ statement, with a different sized string parameter, creates another SQL statement defining the length differently. Apparently (from what I read) this can cause SQL Server to not use the previously cached query.So... since string length isn't defined at all when using a Command object, would it not share the same problem? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-11-17 : 09:42:04
|
| I'm not an expert in ADO.NET, but in the old ADO, length was always explicitly defined. Not as the length of the parameter passed, but the length that the database stored it.Example: say I have defined lastName as VARCHAR(32), and pass in 'Johnson' I would still define the paramater as adVarChar with a length of 32.Also, as I understand it, there have been some changes in LINQ in the latest version (another subject I'm not an expert in -- I'm a DBA not a developer). But, LINQ since it's inception has long been a DBAs worst enemy, creating inefficient and non-reusable queries.I have to disagree with you that using stored procedures will slow down the development process. How much effort does it take to type "CREATE PROC <proc name>" at the top of the queries you're already writing? Another huge advantage to stored procedures is that your DBA can review and optimize them without recompiling the app or deploying any front end code. This is huge.Final thought (for now) -- while query plan reuse is a good thing, it isn't even close to one of the most important performance factors. Good efficient queries and proper indexing is where to focus your energies first when speaking of performance. |
|
|
|
waveform
Yak Posting Veteran
93 Posts |
Posted - 2011-11-17 : 10:24:34
|
| Thanks Russell you puts things into perspective nicely. And you're right, I hadn't considered the string parameter max length is assumed to be whatever it's defined as in the db *slaps forehead*.Being the opposite - a developer not a dba (and without a dba on this project) - it's easy to start worrying about stuff that should come later in db optimisation if it's even necessary. Thanks for that, it's good to know it doesn't make a huge difference and to focus on the other stuff. Sometimes one needs to be told that. :) |
|
|
|
waveform
Yak Posting Veteran
93 Posts |
Posted - 2011-11-17 : 10:36:46
|
| Just for future reference, someone just pointed me to this article, which does say that using SQLCommand (particularly SqlParameterCollection.AddWithValue) can lead to different plans being created for different parameter string lengths.http://msdn.microsoft.com/en-us/magazine/ee236412.aspx#id0070056"By not specifying the length when you're using ParameterCollection.AddWithValue, you can have as many different queries in the plan cache as you have different string lengths."Very interesting! The take-away is, when using an SQLCommand with string parameters, always specify the length of the parameter (AddWithValue doesn't let you do that, so the recommendation is don't use it), and same applies to the precision and scale of decimal parameters. Otherwise plan pollution can result.Of course - and the article mentions this - using Command (or LINQ etc) instead of SPs takes away a lot of the ability of DBAs to optimise queries, as they're not going to check the developer's code.Though I'd venture to say DBAs will just have to start doing that now that ORMs are so widely used. But I'm not 100% sold on them.. the right tool for the right job I think.The reason I say SPs slow me down, is simply being the only dev, having to flip between SQL Server and my IDE does slow me down and makes debugging less convenient. Specs change and it's simply easier to edit my DAL classes than both the DAL *and* the SPs. Conversion to SPs, like other optimisation, can come later. :)The "fix" they've made in LINQ in .NET 4 is rather inelegant.. it simply creates string parameters as VarChar(4000), then ups it if the string is actually longer. Still, that's potentially only 2 plans instead of potentially 200. :) |
|
|
|
|
|
|
|
|
ADO.NET Commands and query plans