Last post Jan 24, 2011 09:10 AM by grundebar
Jan 20, 2011 06:49 AM|mates|LINK
I have this sqldatasource:
<asp:sqldatasource id="SqlDataSourceX" runat="server" connectionstring="<%$ ConnectionStrings:MyConn%>" providername="System.Data.SqlClient"
selectcommand="SELECT ItemNum, MIN(DateAdded) as [DateAdded]
FROM Items WITH (NOLOCK)
WHERE [DateAdded] between @DateFrom AND @DateTo
GROUP BY ItemNum">
<asp:controlparameter name="DateFrom " controlid="DatePickerFrom" propertyname="DateTimeValue" type="DateTime">
<asp:controlparameter name="DatumTo" controlid="DatePickerTo" propertyname="DateTimeValue" type="DateTime">
When I call select in codebehind, after 30sec it throws timeout expired SqlException:
GROUP BY ItemNum',N'@DateFrom datetime,@DateTo datetime',@DateFrom='2011-01-14 00:00:00',@DateTo='2011-01-21 00:00:00'
again , this select took up to 3 sec to be executed. In application I tried to use SqlConnection and SqlTableAdapter instead of SqlDataSource, with the same query, and again it took up to 3 sec.
If I choose tighter interval (e.g '2011-01-20 00:00:00' - '2011-01-21 00:00:00') , there is no timeout exception, so I know that sqldatasource select is executed correctly. So the conclusion is that sql server is not using index while executing
query sent from sqldatasource, but is using index whenever else. I have also checked the index statistics on sql server and it is like that. In profiler i can see slight difference between this two cases. As for SqlDataSource, in events table, in first column
"EventClass" after 30 sec, there is this: "RPC:Completed" and application throws timeout. If i use SqlConnection and SqlTableAdapter then at the same place there is this: "SQL:BatchStarting" and "SQL:BatchCompleted" and application does not throws timeout(the
index has been used).
Does anybody know what is so special on SqlDataSource connection, that Sql Server performs differently compared to other types of connections? Thank you for any advice!
sqldatasource timeout expired table sql server index usage using
Jan 20, 2011 12:50 PM|grundebar|LINK
this thread for some potential reasons why your index is ignored. Whenever you execute a sql statement against a table, SQL Server does the best it can to pull the data in the most efficient manner possible based on the columns selected, your statement
where clause, and any parameters you use. As the thread mentions, there are several factors that come in to play. These include statistics on your index, stored plans for the sql statement, the parameters supplied, and optimizer settings. There is one method
you can use to trick the optimizer by using a stored procedure like this:
CREATE PROC usp_GetItems (
Declare @_DateFrom datetime, @_DateTo datetime
Select @_DateFrom = @DateFrom, @_DateTo = @DateTo
SELECT ItemNum, MIN(DateAdded) as [DateAdded]
FROM Items WITH (NOLOCK)
WHERE [DateAdded] between @_DateFrom AND @_DateTo
GROUP BY ItemNum
When you execute sql in sql server, an optimized plan is saved (depending on your settings of course). If you run a statement with parameters that provide a very bad optimization and the plan is stored using those parameters, then all subsequent calls to that
sql will use the poorly optimized plan (that may or may not use an index). By overriding the proc parameters with local params, the plan will be stored without assumptions about those parameters and the best plan will be implemented each time according to
the supplied parameters.
Some other questions you need to ask about your index: What columns are indexed (ItemNum or DateAdded or both)? Are you using a covering index? Have you checked your index fragmentation?
Jan 21, 2011 05:06 AM|mates|LINK
I cant believe it but it is working. At first I created the sotred procedure with no tricking. I thought this might be working as long as sql server acts better with precompiled sp than random queries. But to my surprise, the index was still not used. Then
I did what you suggested and suddenly the index was used. I just dont understand why. This all "not using index" stuff is here only if Im using SqlDataSource to retrieve data(wheter command type of this SqlDataSource is plain query or stored procedure). If
I use simple SqlCommand and SqlDataAdapter or just execute this query in Management Studio, everything works great. How is this related to optimized planing on slq server?
If you run a statement with parameters that provide a very bad optimization and the plan is stored using those parameters, then all subsequent calls to that sql will use the poorly optimized plan...
I did not run any other statement with any other parameter values than that in my first post. How is sql server creating and storing optimized plans for some query or sp, if sometime it decides to use index(SqlCommand, Management studio) and other time it
decides not to use index(SqlDataSource) and all with the exact same query after all?
By overriding the proc parameters with local params, the plan will be stored without assumptions about those parameters and the best plan will be implemented each time according to the supplied parameters
So you say that sql server has stored some plan for my stored procedure and my parameters, that does not include using the index, and by changing the parameters im actually saying the sql server to create completely new plan. But what kind of "bad plan"
is stored on sql server for my query(or SP)? How is that possible that sql server decides to use this bad plan if SqlDataSource is used and decides to use new plan if other type of connection is made?
Anyhow, thank you for your comments, you helped me a lot.
Jan 24, 2011 09:10 AM|grundebar|LINK
Here is an article that might shed some light on why it behaved the way it did. In essence what you have done is made your sql generic enough that a fairly well optimized plan can
be cached and any parameters you supply will use that plan. The benefit is that you get a blanket approach to the plan that covers most scenarios; downside, some fall outside of that group.
I hope this helps at least a little bit.