We have a table called
| PRSNumber | ... | ... | ProjectCode | | PRJCD-00001 | | | PRJCD | | PRJCD-00002 | | | PRJCD | | PRJCD-00003 | | | PRJCD | | PRJX2-00003 | | | PRJX2 | | PRJX2-00003 | | | PRJX2 |
ProjectCode is the prefix of
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:
varchar(11)and is the primary key
ProjectCodeis now indexed, still no luck
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
SELECT * FROM Purchases WHERE left(Purchases.PRSNumber, 5) = @ProjectCode