Ok, so I have this query that takes 2-5 seconds to run within Sql Management Studio. But when I am running it via my .net application, It exceeds the CommandTimeout of 5 minutes, every time.
I know the .net code works, because this same code (below) is executing other queries fine, and is delivering results.
public DataTable ExecuteQuery()
{
DataTable result = new DataTable();
FoSqlConn con = new FoSqlConn(ConnectionToUse, ApplicationName); // connection string factory
List<string> parameterNames = GetAllParametersFromQueryString(QueryString);
using (SqlConnection sqlCon = new SqlConnection(con.GetConnectionString()))
{
using (SqlCommand cmd = new SqlCommand(QueryString, sqlCon))
{
if (DefaultTimeout.HasValue == true)
{
cmd.CommandTimeout = DefaultTimeout.Value;
}
foreach (string paramName in parameterNames)
{
if (Context.ParameterExists(paramName))
{
cmd.Parameters.AddWithValue(paramName, Context.GetParameterByName(paramName));
}
else
{
cmd.Parameters.AddWithValue(paramName, DBNull.Value);
}
}
sqlCon.Open();
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
adapter.Fill(result);
}
if (sqlCon.State == ConnectionState.Open)
{
con.CloseConnection();
}
}
}
return result;
}
Below is the query, but with tables renamed:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
IF (@StartDate IS NULL)
BEGIN
SET @StartDate = DATEADD(Day, -60, GETDATE())
END
IF (@EndDate IS NULL)
BEGIN
SET @EndDate = DATEADD(DAY, -1, GETDATE())
END
SELECT
P.ProductionObjDimId
,Dim.ProductionObjSourceId
,Dim.Name as WellName
,Dim.CurrentStatus
,Dim.ApiCode
,Dim.CurrentType
,Dim.StateProvidenceCode
FROM
ProductionDetail as P with(nolock)
JOIN DataWarehouse.dbo.ProductionObjMetaData as M
ON P.ProductionObjDimId = M.ProductionObjDimId
AND M.OperationType = 1
JOIN DataWarehouse.dbo.ProductionObjDim as Dim
ON P.ProductionObjDimId = Dim.ProductionObjDimId
LEFT OUTER JOIN ProductionForecastingLinksView as Forcast
ON Dim.ProductionObjSourceId = Forcast.comp_sk
AND Forcast.StartDate <= @EndDate
AND Forcast.EndDate > @StartDate
LEFT OUTER JOIN DataWarehouse.dbo.ForecastingUpload as Upload
ON Forcast.propnum = Upload.ForecastingWellSourceId
AND Upload.StartDate <= @EndDate
AND Upload.EndDate > @StartDate
AND (Upload.UploadDaily = 1 OR Upload.UploadMonthly = 1)
WHERE
P.ProductionDate >= @StartDate
AND
Upload.ForecastingWellSourceId IS NULL
GROUP BY
P.ProductionObjDimId
,Forcast.propnum
,Dim.ProductionObjSourceId
,Dim.Name
,Dim.CurrentStatus
,Dim.ApiCode
,Dim.CurrentType
,Dim.StateProvidenceCode
Having
SUM(P.GrossOilProduction) > 0
OR SUM(P.GrossGasSale) > 0
order by WellName
Please help, I am at a total loss for why this one query is having issues.
UPDATE (old, found while the below is interesting, it is not causing the problem) So I ran the trace, looking for the query, it showed up when I ran the query manually, but when I ran it through the code, it wasn't appearing at all, and got the same error message. So i really looked at the connection string, and I noticed something strange. While the user name and password were being passed, the SqlConnection object's ConnectionString property was missing the Password. I don't know if that points to a solution, but I am very confused now.
UPDATE #2 I didn't let the trace run long enough. I was able to capture the call that was super long.
exec sp_executesql N'SET TRANSACTION ISOLATION...[SAME CODE AS ABOVE]' ,@StartDate=NULL,@EndDate=NULL
Running this exact query, I am getting the same result (it actually completes, it just takes 5+ minutes running it via this method, rather than the 3 seconds running the query directly). Note I did try to run the query with the parameters specified as nvarchar(4000) as well, but just running the query in Sql management studio works fine.
UPDATE #3 I have updated the statistics for all the tables that are joined within the query, no luck. The sp_executeSQL query still takes close to 5 minutes (its about 30 seconds less than before the rebuild of the statistics). At this point I am at a loss. Any Ideas?
UPDATE #4 finally found the solution! the problem was due to "Parameter Sniffing" I was using if conditions prior to my result generating query. the execution plan engine was assuming that the parameters, when passed as null, would hit the query as null, which is not the case. They always would have a value. To correct the problem, i removed the if conditions at the beginning of the query, and placed ISNULL checks where ever the parameter was used. this notified the execution plan of my intentions, and the sp_executeSQL call executed the same speed as my Sql Management Studio execution. Thank you all for your help!
