Search code examples
sql-server-2005timeoutexception

ASP.NET / SQL Server - Timeout expired while searching

We have a table called Purchases:

| PRSNumber   | ... | ... | ProjectCode |
| PRJCD-00001 |     |     | PRJCD       |
| PRJCD-00002 |     |     | PRJCD       |
| PRJCD-00003 |     |     | PRJCD       |
| PRJX2-00003 |     |     | PRJX2       |
| PRJX2-00003 |     |     | PRJX2       |

Note: ProjectCode is the prefix of PRSNumber.

Before, when there is no ProjectCode field in the table, our former developers use this query to search for purchases with specific supplier:

select * from Purchases where left(PRSNumber,5) = @ProjectCode

Yes, they concatenate the PRSNumber in order to obtain and compare the ProjectCode. Although, the code above works fine regardless of the table design.

But when I added a new field, the ProjectCode, and use this query:

select * from Purchases where ProjectCode = @ProjectCode

I receive this exception:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I can't believe, that the first query, which needs concatenation before the compare, is faster than the second one which has to do nothing but compare. Can you please tell me why is this happening?

Some information which might be helpful:

  • PRSNumber is varchar(11) and is the primary key
  • ProjectCode is nvarchar(10)
  • Both query works fine in SQL Server Management Studio
  • First query works in ASP.NET website, but the second does not
  • ProjectCode is indexed
  • The table has 32k rows

Update

  • ProjectCode is now indexed, still no luck
Solution

  • I set the CommandTimeout property of my SqlCommand higher instead of making the query faster. It didn't solve the speed but solved the timeout issue.