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 keyProjectCode
is nvarchar(10)
ProjectCode
is indexedUpdate
ProjectCode
is now indexed, still no luckI 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.