Here is a simple stored procedure in company database that does a search and returns results in less than a second:
CREATE PROCEDURE [dbo].[WorkOrder_Search](@Description varchar(250))
AS
Begin
SELECT Order_id,Dept_Code,Graphic_Number,[Description],Quantity,Date_Received as [Recv. Date],Date_Due as [Due Date],
Date_Delivered as [Delivered Date],proof_sent_date as [Proof Date],print_date as [To Print],
dbo.func_grOrder_GetAssigned(Order_id) as [Assign To],Status,Note, Master_Cal_ID
From grGraphic_Order
where [Description] like '%' + @Description + '%'
end
But it used to be a really "BIG" stored procedure as shown below that did the same search and took about 5 minutes to return results. It did return the same results as the above does.
CREATE PROCEDURE [dbo].[WorkOrder_search](@Description varchar(250))
AS
begin
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF
SET NoCount ON
Declare @sql varchar(8000)
Declare @Orig_1 varchar(1000)
Declare @Declare varchar(200),
@From varchar(100),@Into varchar(50),@Groupby varchar(300),
@Orderby varchar(400),@Select varchar(400),@Select1 varchar(400),@Select2 varchar(300),
@cursor1 varchar (500),@cursor2 varchar(4000),@create varchar(4000)
set @Orig_1 = ''
Create table #Assignto_temp(Order_id int, Assignto_names varchar(500))
Insert into #Assignto_temp(Order_id )
Select gro.order_id
From grassignto gro
Inner join wfpmauser wf
On gro.user_id = wf.user_id
group by gro.order_id
declare @order_number int, @Assignto varchar(255), @List_names varchar(255),@order_number_prev int
declare assignto Cursor
Local
Forward_only
for
Select gro.order_id,Wf.First_name+' '+Wf.Last_name as Assignto
From grassignto gro
Inner join wfpmauser wf
On gro.user_id = wf.user_id
order by order_id
open assignto
Fetch Next from assignto into @order_number, @Assignto
While @@Fetch_Status=0
begin
select @List_names=Assignto_names from #Assignto_temp where order_id=@order_number
if @List_names is null
begin
update #Assignto_temp
set Assignto_names=@Assignto
where order_id=@order_number
end
else
begin
update #Assignto_temp
set Assignto_names=@list_names + ',' + @Assignto
where order_id=@order_number
end
Fetch Next from assignto into @order_number, @Assignto
end
close assignto
deallocate assignto
Set @Declare ="Declare @Orig_1 varchar(1000),@Originator varchar(100),@order_id int,@Orig varchar(300) set @Orig_1 = '' "
Set @Select = " SELECT distinct gro.* "
Set @Into = "Into #tmp1 "
Set @From = "FROM grGraphic_Order Gro left join grOriginator gr on gro.order_id = gr.order_id "
Set @Orderby = "ORDER BY Gro.Graphic_number,Gro.dept_code Desc "
Set @Select2 = " SELECT Order_id,Dept_Code ,Graphic_Number,Description,Quantity,Date_Received as [Recv. Date],
Date_Due as [Due Date],Date_Delivered as [Delivered Date],proof_sent_date as [Proof Date],print_date as [To Print],
Status,Note, master_cal_id [Master Calendar ID] From #tmp1 ORDER BY dept_code,Graphic_number "
Set @Create = "Create table #Assignto_temp(Order_id int, Assignto_names varchar(500))
Insert into #Assignto_temp(Order_id )
Select gro.order_id
From grassignto gro
Inner join wfpmauser wf
On gro.user_id = wf.user_id
group by gro.order_id
declare @order_number int, @Assignto varchar(255), @List_names varchar(255),@order_number_prev int
declare assignto Cursor
Local
Forward_only
for
Select gro.order_id,Wf.First_name+' '+Wf.Last_name as Assignto
From grassignto gro
Inner join wfpmauser wf
On gro.user_id = wf.user_id
order by order_id
open assignto
Fetch Next from assignto into @order_number, @Assignto
While @@Fetch_Status=0
begin
select @List_names=Assignto_names from #Assignto_temp where order_id=@order_number
if @List_names is null
begin
update #Assignto_temp
set Assignto_names=@Assignto
where order_id=@order_number
end
else
begin
update #Assignto_temp
set Assignto_names=@list_names + ',' + @Assignto
where order_id=@order_number
end
Fetch Next from assignto into @order_number, @Assignto
end
close assignto
deallocate assignto
SELECT #tmp1.Order_id,Dept_Code ,Graphic_Number,Description,Quantity,Date_Received as [Recv. Date],
Date_Due as [Due Date],Date_Delivered as [Delivered Date],proof_sent_date as [Proof Date],print_date as [To Print],
isnull(tmp.assignto_names,'Not Assigned') as [Assign To],Status,Note, #tmp1.Master_Cal_ID
From #tmp1 LEFT Join #Assignto_temp tmp ON #tmp1.order_id = tmp.order_id ORDER BY Dept_Code,Date_Received Desc"
Select @sql=@Declare + @Select + @Into + @From + ' where Description like '
+ char(39) + '%' + @Description + '%' + char(39)+ @Orderby + @create
Exec(@sql)
end
TOMZ_KV
modified 13-Apr-13 8:57am.
|