vb 6 showing timeout expired while executing stored procedure

Question

when i tried to execute this stored procedure it is giving me timeout expired error here is my stored procedure

ALTER procedure [dbo].[sp_rptExpMed]
(
  @Stocname as varchar(100),
  @date varchar(40),
  @Mode int
)
as
begin
if @Mode=1
    begin
    select DISTINCT  Em.*,M.DrugName,m.Category
    --,rate 
    from ExpiryMed Em inner join medicinaldrugs M on Em.Drugid=M.drugId
    LEFT join Purchase22011_2012 P on em.DrugID = P.ItemID and Em.batch=p.BatchNo
    order by M.DrugName,Em.Batch
    end
if @Mode=2
    begin
    select DISTINCT Em.*,M.DrugName,m.Category
    --,rate
     from ExpiryMed Em inner join medicinaldrugs M on Em.Drugid=M.drugId
    LEFT join Purchase22011_2012 P on em.DrugID = P.ItemID and Em.batch=p.BatchNo
    where ExpDate<@date
    order by M.DrugName,Em.Batch
    end
end

what is wrong in this. can anyone suggest me a way to avoid this timeout expired error

Answer 1

There could be the case that the table(s) you are querying from in your procedure hold a lock due to open/uncommitted transaction on them. Try using a NOLOCK table hint along with the table name like

select DISTINCT  Em.*,M.DrugName,m.Category
from ExpiryMed Em WITH (NOLOCK)
inner join medicinaldrugs M WITH (NOLOCK) on Em.Drugid=M.drugId
LEFT join Purchase22011_2012 P WITH (NOLOCK) on em.DrugID = P.ItemID 
and Em.batch=p.BatchNo
order by M.DrugName,Em.Batch

You can as well set the transaction isolation level to READ UNCOMMITTED