exec sp_executesql slower than straight query?

[Bohica69]

I've run into a bizzare problem that I can't seem to resolve: I have a query that I'm moving from ASP to ASP.NET and changing it to a paramaterized query at the same time. For some reason the paramterized query is MUCH slower than the non paramaterized query.

Here's a simplified example of the original query (in ASP)

var SQL = "SELECT * FROM Table WHERE ID =" + ValueVariable

Here's how I changed it in (ASP.NET)

var SQL = "SELECT * FROM Table WHERE ID = @ValueVariable"

then I set the parameters and run the query.... For some reason the code time's out running the query. So I fire up profiler and grab watch what's happening. Here's the code that get's sent to database server

exec sp_executesql N'SELECT * FROM Table WHERE ID = @ValueVariable, N'@ValueVariable INT', @ValueVariable=1234

I take this and feed it to SQL management studio.. It takes 43 seconds to run (ouch)

I then take that and modify it to this:

DECLARE @ValueVariable INT
SET @ValueVariable = 1234

SELECT * FROM Table WHERE ID = @ValueVariable

This takes under a second to return the same record set.

Does anybody have ANY ideas why there would be this huge difference in speed? I've never seen this happen before. I've never seen a difference in performance between the exec sp_executesql and just running the straight query. This is on SQL Server 2k5.

Mike, do you have any ideas?

Thanks.

 

[Sgraffite]

Could it be related to this? http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx

 

[Bohica69]

Thanks for the link, but I don't think that's the case here - I'm running this against our test database server and we don't have a whole lot of load on the server. I did a statistics update just before I started testing and the parameters I was using where the same all the time.

I'm still stumped by this. I've done this type of things many times and have never seen this happen.

Typically what I do is write a query in SQL managment studio as a paramertized query, test it, make sure I'm returning the results I want then cut and paste it into a C# string variable, then just setup the parameters and fire it off from code.

This is driving me nuts (and it's a short trip )

 

[bobbydole]

Can you look at the execution plan for both of them to see how sql server is handling the queries?

 

[Pwyl_The_Destroyer]

I'm curious:
Why are you building SQL strings in your C# application instead of using stored procedures?

 

[Bohica69]

Can you look at the execution plan for both of them to see how sql server is handling the queries?

This gets weirder and weirder. The execution plans for both methods look are different but the final subtree costs are very close - 1.670 for the faster one and 1.64 for the slower one. The slower one has a Lazy Table Spool step in it that has 8544 actual rewinds vs 5019 actual rewinds in a similar step for the faster one. Now, I'm far from an expert in reading execution plans, but from looking at the plan that looks like that's where the big hit is.

I'm curious:
Why are you building SQL strings in your C# application instead of using stored procedures?

I'm in the middle of a very large project to replace a current system with a new one. Our timeline is tight, and I don't have a huge amount of time. Right now I'm just trying to get the GUI seperated from the database calls - that's why this stuff is moving into the middle tier. After this project is done, at some time in the future all this stuff is going to be replaced anyway, so it's just as expedient to stick it in the middle tier "As Is" and deal with it when we redesign the database - It's a corner I'm cutting, I'll admit, but life is full of trade off's.

I reread the blog post that Sgraffite posted and came up with a solution: I simply rewrote my SQL string to be exactly the same as the one that runs faster so the SQL now looks like this

string SQL = 
Declare @InputValue INT
SET @Value = @InputValue
SELECT *
FROM Table
WHERE ID = @Value

That solved the problem. Still very odd IMHO, but it works, and right now, that's all I really care about.

Thanks guys

 

[mikeblas]

I take this and feed it to SQL management studio.. It takes 43 seconds to run (ouch)

That statement won't compile; you're missing a tick in the string for the first parameter. Is that just a typo? I want to make sure we've got the right statement so we're not barking up the wrong tree.

The way I'd go about figuring out what's going on is:

1) get sp_help for the table in question; see exactly what the table definition is. Particularly, what's the type of the ID column, and what indexes are on it? Is Table a table, or a view? Indexed view? Is partitioning involved?

2) Review the C# code to see what data type and value is being bound .

3) Get the query plan for the slow statement.

4) Get the query plan for the faster statement.

4) Figure out what the slow statement is doing by examining the query plan, or by comparing it to the faster statement.

If you need help analyzing the plans, save them off and send them to me and I can hep out. There's really no reason (at all!) for you to have to write the query you ended up using in post #5.

The slower one has a Lazy Table Spool step in it that has 8544 actual rewinds vs 5019 actual rewinds in a similar step for the faster one.

Something is very wrong; perhaps you're not giving us the whole story. A simple select statement like the one you're showing has no excuse to execute a rewind in any circumstances. Please let me know what query you're really executing, and what the execution plas actually are.

 

[Bohica69]

Mike, yes you are right, for brevity sake I was not sending the real query - here it is (be kind, I didn't write it)

For various reasons I've always been loath to post complete cut and paste code to forums of any type (especially when it's code that's in production, in this case, a few names have been changed)

Where can I send the execution plans?

I've determined the problem doesn't lie in the C# code, but it's somewhere in SQL server that's not happy with what I'm doing.

Thanks for the help

This version runs in sub second time

DECLARE @RSID int, @RegionID int

SET @RSID=2405
SET @RegionID=34

SELECT DISTINCT     listing_profile.lender_id                    
, listing_profile.name                    
, 0 AS Fee                    
, listing_profile.bill_acct                    
, (		select top 1 lp.lender_id                        
		from listing_profile LP
        INNER JOIN w_regio_n ON LP.lender_id = w_regio_n.lender_id
               and w_regio_n.region_id = @RegionID
               and listing_profile.bill_acct = LP.bill_acct) as LenderID                    
, ISNULL(x.active, 'false') AS active    
FROM listing_profile    
INNER JOIN w_regio_n ON listing_profile.lender_id =w_regio_n.lender_id     
LEFT OUTER JOIN lc.dbo.lender L ON listing_profile.lender_id = L.lenderID AND RSID = @RSID     
LEFT OUTER JOIN (   Select    ID                                 
							, bill_acct
                            , CAST('true' AS VARCHAR(6)) AS active                          
					From lc.dbo.lender
	                Where RSID = @RSID) X ON x.bill_acct = listing_profile.bill_acct   	    
WHERE w_regio_n.region_id = @RegionID       
  and listing_profile.bill_acct In (select bill_acct 
									from retail_select)     
ORDER BY listing_profile.name asc

This is the version that takes 43 seconds

exec sp_executesql N'SELECT DISTINCT     listing_profile.lender_id                    , listing_profile.name                    , 0 AS 
Fee                    , listing_profile.bill_acct                    , ( select top 1 lp.lender_id                        from 
listing_profile LP                        INNER JOIN w_regio_n ON LP.lender_id = w_regio_n.lender_id                            and 
w_regio_n.region_id = 34                            and listing_profile.bill_acct = LP.bill_acct) as LenderID                    
, ISNULL(x.active, ''false'') AS active    FROM listing_profile    INNER JOIN w_regio_n ON listing_profile.lender_id = 
w_regio_n.lender_id     LEFT OUTER JOIN lc.dbo.lender L ON listing_profile.lender_id = L.lenderID AND RSID = 2405     LEFT 
OUTER JOIN (   Select    ID                                 , bill_acct                                , CAST(''true'' AS VARCHAR(6)) AS 
active                          From lc.dbo.Lender                           Where RSID = 2405) X ON x.bill_acct = 
listing_profile.bill_acct   	    WHERE w_regio_n.region_id = 34      and listing_profile.bill_acct In (select bill_acct from 
retail_select)     ORDER BY listing_profile.name asc '

 

[mikeblas]

Mike, yes you are right, for brevity sake I was not sending the real query - here it is (be kind, I didn't write it)

As you must understand, I can't help if you don't give the real query you're executing. The queries that you're really using are far more interesting than just SELECT FROM WHERE, and lots more code path and complexity in the server comes into play.

For various reasons I've always been loath to post complete cut and paste code to forums of any type (especially when it's code that's in production, in this case, a few names have been changed)

I can understand that -- but, again, without accurate and complete information, I can't help. If you want, you can send the queries and information to me privately (though I'd still rather post an answer publicly) because it helps more people. If you can't do that, then you probably should use paid support mechanims or hire a consultant; in these cases you can attach an NDA (or one applies).

Where can I send the execution plans?

YGPM.

This version runs in sub second time

How big are the involed tables, in rows? Are the statistics on the tables current?

 

[Bohica69]

Mike,

I'll be sending the things you've asked for shortly. I totally agree with posting a public answer as I feel it give's back to the community.

The last queries I sent were acurate, I changed the the db names is all. The ones I'm sending will be complete.

I'm really interested to see what you think, this is driving me nuts.

 

[mikeblas]

Which version of SQL Server are you using?

In your email, you indicated that you thought the stats were up to date. I'm thinking the issue is a stats problem; that the parameterized query takes a bad stats sample and comes up with the poor execution plan. When was the last time you rebuilt stats? I'd like to know for sure about the state of the stats before I do more investigation.

 

[Bohica69]

SQL server 2005.

Ran sp_createstatistics
Ran Queries, no change in results

Ran UPDATE STATISTICS on the tables that are in the query.
No change in results.

Should I run DBCC CHECKDB?

 

[Bohica69]

For those of you that are interested here's Mike's response.

I'd like to thank Mike for all his help with this. It's given me some more insight on how the QO works.

...when you write SQL, the parser makes an "algebrized" tree. That's just a bone-simple, completely unoptimized, naieve way to get the right result set. It might run in not bad time, or it might run in hours. Usually, for hours.

The query optimizer then figures out, based on a bunch of rules, which way would be fastest. The rules it uses are sometimes really simple; it knows, for instance, that A JOIN B is the same as B JOIN A. It knows lots more complicated rules, too. (WAAY! more complicated rules.) It iteratively and recursively applies all these rules, elimiating possibilities, making itself promises about reuslts, and expectations, and so on. And then it either gives up and says that's the best it can do -- because it spent enough time -- or it finds something it's pretty sure it can't beat, and goes with that.

The input to the rules includes a few things. Some of them are static, like how long SQL server thinks it will take to execute a given operator. That's programmed, and you can't see it or change it.

One other obvious one is row count. If I get all the rows from TableA, how many rows do I get? That's very important; if A has more rows than B, I'd rather to A JOIN B. But if Table B has more rows, I'd rather do B JOIN A. Row count is important in choosing join order (A JOIN B vs B JOIN A), and choosing join operator (A HASH JOIN B vs. A MERGE JOIN B, vs. A NESTED LOOPS JOIN B, and so on).

Since we don't always get all the rows, one interesting idea is estimating how many rows a filter might give us. Sometimes, the answer is obvious and exact. WHERE A.Col = 3 either gives one or zero rows if we have a unique index on A.Col, for example. Other times, the answer is a guess; WHERE Name LIKE 'Joe%' might give zero or a billion rows, and nobody knows.

That's where statistics come in. SQL Server creates these over indexed columsn, and can auto create them in other cases. The statistics will help the server guess how many rows come out of a subtree of the query (or, the whole damn query tree). They're just an estimate, but they are a very important part of the query.

You're not doing something as simple as A JOIN B, but you're doing something that has a JOIN in it. So the right JOIN operator has to be chosen. A and B aren't tables; they're subtrees of operators of the rest of the query. The QO has to use estimates for results of each subtree to decide which order to use, as well as which operator to use.

So here's what you're facing: the estimate for your query is different when the specifier in the WHERE clause is a numeric constant, than when it's a parameter. That should be obvious; if SQL Server knows the key you're looking for is 1234, then it can look for that information in the stats table and it should get a decent estimate. If it knows the parameter is @binidngname, then it can't give much of an estimate; and falls back on a guess!

You can fix your query by:

1) rewriting it. I haven't studied what it really is meant to do, but the subselects bother me.
2) Splitting the query up. This is an extreme form of rewriting it.
3) adding join operator hints. This means using WITH (MERGE JOIN) on some of the operators, say, and forcing the operator type you see in the faster query plan.

I need to go spend some time (when i have some) and figure out how to rewrite the query.