1

We currently have a very simple query that returns the last row of a table for a given account.

SELECT TOP 1 * FROM tbl WHERE AccountID = @AccountID ORDER BY tblID DESC;

When filling a DataTable from a DataAdapter with this query using the parameter, it times out when there is no data in the table for that account. If there is data in table, it runs fine.

SqlCommand cmd = new SqlCommand(con);
cmd.commandText = "SELECT TOP 1 * FROM tbl WHERE AccountID = @AccountID ORDER BY tblID DESC"

cmd.Parameters.Add(new SqlParamter("@AccountID", 123));
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);  // TIMES OUT

If we change the query so that it doesn't use a parameter, it runs without issue.

"SELECT TOP 1 * FROM tbl WHERE AccountID = 123 ORDER BY tblID DESC"

The table is quite large (millions of rows), but the query without the paramter runs almost instantly.

Any advice would be appreciated

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
Trevor Watson
  • 415
  • 1
  • 8
  • 20
  • 1
    How long does it take in SSMS if you use the parameter there? – Tim Schmelter Jul 15 '13 at 15:08
  • Tim makes a good point. If the query takes forever in SSMS with the parameter, it seems that for some reason the assignment is problematic. If it is fast, then my gut instinct dictates the the `SqlDataAdapter` is causing the issue and you may want to swap it with a `SqlDataReader` like `dt.Load(cmd.ExecuteReader());` – Evan L Jul 15 '13 at 15:10
  • DECLARE (at)aid INT; SET (at)aid = 5798 SELECT top 1 * from tbl where AccountID = @aid order by tblID desc; in SSMS runs in < 00:00 – Trevor Watson Jul 15 '13 at 15:15
  • 2
    What if you specify the type of the parameter explicitely? `cmd.Parameters.Add("@AccountID", typeof(int)).Value = 123;`. Maybe it cannot be inferred since `SqlDbType` is also convertible to `int` implicitely and theres [another constructor](http://msdn.microsoft.com/en-us/library/h8f14f0z.aspx) that takes it. – Tim Schmelter Jul 15 '13 at 15:16
  • Still the same result, times out if no data, runs if it has data or if the parameter is removed from the query. – Trevor Watson Jul 15 '13 at 15:23
  • Thinking through it, this makes sense IF IT IS NOT USING THE INDEX on the field, or the index has something wrong with it. It is timing out because it has to loop through all rows when there is no data, but stops when there is data. The index should prevent this though, or make the lookup faster. But it's not using it. – Trevor Watson Jul 15 '13 at 15:53

0 Answers0