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

---

First thing I would do is check the index on PRSNumber, I assume there is an index on that field and the table is very large.

Adding an index to your new field will likely fix the problem (if that is the case).

The code to add an index:

CREATE INDEX IX_Purchases_ProjectCode 
ON dbo.Purchases (ProjectCode); 

Update:

I would also try adding the field as a varchar to eliminate the datatype change from the equation.

---

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.