The follwing proc is running nearly 1min.44sec ,i wud like to run it more faster.. content may be mispelled its just an example ALTER PROCEDURE spimportemp @dbname varchar (30),@FromDate char(10)AS SET NOCOUNT ON declare @sql varchar(8000)declare @DateError int select @DateError = isdate(@FromDate)if @DateError = 0 begin raiserror('You have entered an invalid date',16,1) return end elsebegin create table #t (EmpNo varchar (30),EmpName varchar(100),Date char(10),ErrorMessage text )insert into #t EXEC spjobdet @dbname,@FromDate IF @@ROWCOUNT > 0 begin raiserror('Errors in job',16,1) return end else begin truncate table #t insert into #t EXEC spsaldet @dbname,@FromDate end IF @@ROWCOUNT > 0 begin raiserror('Errors in ToDo',16,1) return end else beginBegin transaction Truncate table tbljobTruncate table tblsalSET @sql=' DECLARE @office varchar(30),@jobref int,@email varchar(50) select @office=office from EmpRep.dbo.tbloffice where office=' +QUOTENAME(@dbname,'''') +'select @jobref=pdcircuit from EmpRep.dbo.tbloffice where office=' +QUOTENAME(@dbname,'''') +'select @email=email from EmpRep.dbo.tbloffice where office=' +QUOTENAME(@dbname,'''') +' INSERT INTO EmpRep.dbo.tbljob ( 36 columns...... ) SELECT 36 columns...... FROM '+@dbname+'.dbo.emp1 e1 LEFT OUTER JOIN '+@dbname+'.dbo.emp2 e2 on e1.empid=e2.empidWHERE e1.date>=' +QUOTENAME(@FromDate,'''') +' and e.empid not in (select empid from EmpRep.dbo.tbljob) INSERT INTO EmpRep .dbo.tblsal ( 27 columns..... ) SELECT 27 columns...... FROM...some joins EXEC (@sql) commit transaction --end drop table #tend
First of all run procedure after enabling the query plan and starting SQL profiler and see which part of the procedure is taking long time and check how the indexes being used. If there are no proper indexes add them, you can use also use DTA. You are calling 2 or more procedures in the main proc and make sure they are tuned...