SQL Server Performance

store proc running very slow

Discussion in 'Performance Tuning for DBAs' started by Reddy, Jan 4, 2008.

  1. Reddy New Member

    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
  2. MohammedU New Member

    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...
  3. satya Moderator

    Also check what kind of indexes are associated on the tables within these SPs.

Share This Page