Dataset times out while query runs immediately

Question

I am running a query directly, it is trivial in nature:

SELECT * FROM [dbo].[vwUnloadedJobDetailsWithData] WHERE JobId = 36963

When I run this from Management studio the query doesn't even take a second. When I run it from within the table adapter it times out. I have fixed this multiple times, but the fix is ludicrous. If I delete the table adapter from my xsd file and recreate it the query time matches that of management studio for about two days, but I have to redeploy which is asinine.

Any insight into what could be causing this would be greatly appreciated. I've seen another question about this but the solution involving set arithabort on before the query had no effect for me.

Edit: It was asked that I show my code for calling the query. Now this happens when I go into my xsd file and just do preview data as well, but for sake of clarity, here it is:

using (TEAMSConnection connection = new TEAMSConnection())
{  
    connection.OpenConnection();

    _JobDetailsDAO jobDetailDao= new _JobDetailsDAO(connection);
    return jobDetailDao.GetUnloadedJobDetailsByJobId(jobId);

}

On disposal of connection the database connection is closed. using this line of code:

if (_DBConnection != null && _DBConnection.State == ConnectionState.Open)
    _DBConnection.Close();

Edit2: I ran a trace and here are the set options that are being set

set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed

I went and added that to the query that I generated in management studio and it still ran in less than a second. I even copied the query exactly as in the trace.

exec sp_executesql N'SELECT * FROM [dbo].[vwUnloadedJobDetailsWithData] WHERE JobID = @JobId',N'@JobId int',@JobId=36963

and it is still less than a second return time. I am so very confused.

Thanks, Josh

Answer 1

the most likely scenarion why this would be happening is the difference in SET options between ssms and ado.net. that difference causes (re)building of execution plans that might not be optimal.

Answer 2

Alright, well I could not find any solution that would continue to allow me to use the dataset, so I went straight to using the SqlDataAdapter in code rather than using the auto generated TableAdapters.

According to the trace it performs the exact same query, but so far it works. It may not in two days, but for now it works it seems.

Answer 3

Just trying to think loudly: Maybe there is a lock caused by another process/person? Is there anybody who updates the same row at the same time? Is there anybody who opens the table from Management studio or Query Analyzer with Open Table feature and plays with the filters? Try looking for locks using sp_who2

Answer 4

Some thoughts:

What I'd call parameter sniffing for stored proc. Try the OPTION (RECOMPILE) hint, so your sent SQL looks like this:

exec sp_executesql
    N'SELECT *
         FROM [dbo].[vwUnloadedJobDetailsWithData]
         WHERE JobID = @JobId
         OPTION (RECOMPILE)',
    N'@JobId int',
    @JobId=36963

Explanation: When a query plan is produced and cached, it may be a bad, atypical value. Say JobID is usually very selective, but for that one execution it's not. When you run the query the next plan the cached plan is wrong for the next selective JobId. A plan will be recompiled for various reasons, but the value on recompilation matters.

Otherwise, what is the exact datatype of Jobid? If it's smallint, then the column will be converted to int in the parameterised query. When using a constant it will be smallint. Make sure the type is defined correctly: this matters in SQL code.