SQL Server Performance

Avoiding Temporary Tables......Best Alternative

Discussion in 'Performance Tuning for DBAs' started by krajdba, Feb 22, 2005.

  1. krajdba New Member

    Hi Gurus,

    In one of my Stored Procedure I have several Temporary tables i.e, #RptDetails etc.
    In some of the Queries of the SP I am joining the Base table with the Temporary
    table.

    The SP is doing very bad, it is very Slow.

    Can someone suggest me an alternative to Temporary tables.

    urgent please.

    Thanks



    raj
  2. mmarovic Active Member

    Slowness doesn't have to be caused by temp tables. However, if you really want to avoid temp tables you can use either:

    - derived tables
    - table variables
    - table functions

    If you post your sp you may expect more help.
  3. ranjitjain New Member

    There is no alternative to temporary table, but its bad, i feel when using a lot of.

    To remove such temp tables, one thing i can suggest is to optimise ur query, scan ur SP to find loopholes, which u can debug and replace them by storing data in variables instead of tables.


    group similar queries by joins and refine ur SP.
  4. Adriaan New Member

    Check the definition of your temporary table. For starters, if you're not declaring a primary key or any indexes, and the table contains a considerable amount of rows, then performance will suffer.<br /><br />You can also use derived tables, which are basically like <b>SELECT TMP.* FROM (SELECT * FROM MyTable WHERE &lt;criteria&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /> AS TMP</b> instead of <b>SELECT * FROM MyTable</b>. You can use this technique to break up complex criteria for multi-table joins. To run this type of query, SQL Server will often create temporary working tables, including proper indexes.
  5. Chappy New Member

    What is the execeution plan of the stored proc ? This will tell you the area that is really taking the time

    Also dont create your temp tables using SELECT INTO, as this requires SQL server to go off and grab a schema blueprint in order to create the temp table. More often than not you know the structure of your temp table and so use CREATE TABLE and then INSERT INTO which will provide a small performance boost
  6. Luis Martin Moderator

    May you need to optimize those store procedures.
    I mean: if you have something like:

    insert into #RpDetails select ............

    You can copy and paste from select to end into QA and see execution plan to find if there is any indexs to create. Also you can take help with ITW in QA.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  7. krajdba New Member

    Hi Gurus,

    Here is the SP,Do you think If I create the base table instead of #tempPrjDetails
    temporary table whill the Performance Boost....?

    Urgent Please....!!

    Create PROCEDURE [dbo].[USP_UpdateProjAccLaborChargeType]
    @InvoiceNo varchar(20)
    as
    begin

    update Project_Accounting
    Set Spent_To_Date=0 where project_id = 0

    CREATE TABLE #tempPrjDetails
    (
    ProjectId INT,
    ProjectLaborAmt numeric(18,0)
    )
    INSERT INTO #tempPrjDetails
    Select TSD.Project_Id as ProjectId,
    isnull(sum(isnull(dbo.UDF_GetBilledUnits(TSD.TimeSheet_Id,TSD.Project_Id,TSD.Type_Id,CJO.Contractor_Id,TSD.Job_Title),0) *
    isnull(dbo.UDF_CalcUnitPrice(TSD.TimeSheet_Id ,TSD.Project_Id,TSD.Type_Id, TS.Contractor_Id,TSD.Job_Title),0) +
    isnull(dbo.UDF_CalcBilledOTAmt(TSD.TimeSheet_Id ,TSD.Project_Id,TSD.Type_Id, TS.Contractor_Id,TSD.Job_Title),0) ),0)
    as ProjectLaborAmt
    from
    TimeSheet_Expense TS (nolock) inner join TimeSheet_Detail TSD (nolock) on TS.Time_Expense_Id = TSD.TimeSheet_Id
    inner join Contractor_Job_Order CJO (nolock) on CJO.Contractor_Id = TS.Contractor_Id and
    CJO.job_order_suffix_id = (select max(job_order_suffix_id) from contractor_job_order CJ (nolock)
    inner join Valid_Values VV (nolock) on CJ.Job_Order_Status_Id = VV.Valid_Value_Id and VV.programmer_use1 = 'Approved'
    where CJ.job_order_id = CJO.job_order_id and CJ.contractor_id = CJO.contractor_id)
    and TSD.Project_Id = CJO.Project_Id
    where TSD.TimeSheet_Id in (Select isnull(TimeSheet_Id,0) from Invoice_Detail (nolock) where
    Invoice_Number = @InvoiceNo and TimeSheet_Id is not null )
    group by TSD.Project_Id
    --End
    UPDATE Project_Accounting
    SET Spent_To_Date = isnull(Spent_To_Date,0) + isnull(#tempPrjDetails.ProjectLaborAmt,0)
    FROM #tempPrjDetails
    where Project_Accounting.Project_Id = #tempPrjDetails.ProjectId and Project_Accounting.Charge_Type_Id =
    (Select Valid_Value_Id from Valid_Values VV (nolock) inner join Data_Items DI (nolock) on
    VV.Data_Item_Id = DI.Data_Item_Id and DI.Data_Item_Name = 'ChargeType' and VV.programmer_use1='Labor')
    --
    /*update Project_Accounting
    Set Spent_To_Date = isnull(Spent_To_Date,0) + isnull(A.ProjectLaborAmt,0)
    from (Select TSD.Project_Id as ProjectId,
    isnull(sum(isnull(dbo.UDF_GetBilledUnits(TSD.TimeSheet_Id,TSD.Project_Id,TSD.Type_Id,CJO.Contractor_Id),0) *
    isnull(dbo.UDF_CalcUnitPrice(TSD.TimeSheet_Id ,TSD.Project_Id,TSD.Type_Id, TS.Contractor_Id),0) +
    isnull(dbo.UDF_CalcBilledOTAmt(TSD.TimeSheet_Id ,TSD.Project_Id,TSD.Type_Id, TS.Contractor_Id),0) ),0)
    as ProjectLaborAmt
    from
    TimeSheet_Expense TS (nolock) inner join TimeSheet_Detail TSD (nolock) on TS.Time_Expense_Id = TSD.TimeSheet_Id
    inner join Contractor_Job_Order CJO (nolock) on CJO.Contractor_Id = TS.Contractor_Id and
    CJO.job_order_suffix_id = (select max(job_order_suffix_id) from contractor_job_order CJ (nolock)
    inner join Valid_Values VV (nolock) on CJ.Job_Order_Status_Id = VV.Valid_Value_Id and VV.Valid_Value = 'Approved'
    where CJ.job_order_id = CJO.job_order_id and CJ.contractor_id = CJO.contractor_id)
    and TSD.Project_Id = CJO.Project_Id
    where TSD.TimeSheet_Id in (Select isnull(TimeSheet_Id,0) from Invoice_Detail (nolock) where
    Invoice_Number = @InvoiceNo and TimeSheet_Id is not null )
    group by TSD.Project_Id) as A
    where Project_Accounting.Project_Id = A.ProjectId and Project_Accounting.Charge_Type_Id =
    (Select Valid_Value_Id from Valid_Values VV (nolock) inner join Data_Items DI (nolock) on
    VV.Data_Item_Id = DI.Data_Item_Id and DI.Data_Item_Name = 'ChargeType' and VV.Valid_Value='Labor')*/

    CREATE TABLE #tblprjDetails1
    (
    ProjectId INT,
    ProjectLaborAmt numeric(18,0)
    )
    INSERT INTO #tblprjDetails1
    Select TSD.Project_Id as ProjectId,
    isnull(sum(isnull(dbo.UDF_GetBilledUnits(TSD.TimeSheet_Id,TSD.Project_Id,TSD.Type_Id,CJO.Contractor_Id,TSD.Job_Title),0) *
    isnull(dbo.UDF_CalcUnitPrice(TSD.TimeSheet_Id ,TSD.Project_Id,TSD.Type_Id, TS.Contractor_Id,TSD.Job_Title),0) +
    isnull(dbo.UDF_CalcBilledOTAmt(TSD.TimeSheet_Id ,TSD.Project_Id,TSD.Type_Id, TS.Contractor_Id,TSD.Job_Title),0) ),0)
    as ProjectLaborAmt
    from
    TimeSheet_Expense TS (nolock) inner join TimeSheet_Detail TSD (nolock) on TS.Time_Expense_Id = TSD.TimeSheet_Id
    inner join Contractor_Job_Order CJO (nolock) on CJO.Contractor_Id = TS.Contractor_Id and
    CJO.job_order_suffix_id = (select max(job_order_suffix_id) from contractor_job_order CJ (nolock)
    inner join Valid_Values VV (nolock) on CJ.Job_Order_Status_Id = VV.Valid_Value_Id and VV.programmer_use1 = 'Approved'
    where CJ.job_order_id = CJO.job_order_id and CJ.contractor_id = CJO.contractor_id)
    and TSD.Project_Id = CJO.Project_Id
    where TSD.TimeSheet_Id in (Select isnull(TimeSheet_Id,0) from Invoice_Detail (nolock) where
    Invoice_Number = @InvoiceNo and TimeSheet_Id is not null )
    group by TSD.Project_Id

    If Exists(Select 1 from project_Accounting inner join #tblprjDetails1 on project_Accounting.Project_id = #tblprjDetails1.ProjectId where project_Accounting.Charge_Type_Id=(Select Valid_Value_Id from Valid_Values VV (nolock) inner join Data_Items DI (nolock) on
    VV.Data_Item_Id = DI.Data_Item_Id and DI.Data_Item_Name = 'ChargeType' and VV.Valid_Value='Labor'))
    Begin
    UPDATE Project
    SET Total_Spent_To_Date = isnull(Total_Spent_To_Date,0) + isnull(#tblprjDetails1.ProjectLaborAmt,0)
    FROM #tblprjDetails1
    WHERE Project.Project_Id = #tblprjDetails1.ProjectId
    End

    /*Update Project
    Set Total_Spent_To_Date = isnull(Total_Spent_To_Date,0) + isnull(A.ProjectLaborAmt,0)
    from
    (Select TSD.Project_Id as ProjectId,
    isnull(sum(isnull(dbo.UDF_GetBilledUnits(TSD.TimeSheet_Id,TSD.Project_Id,TSD.Type_Id,CJO.Contractor_Id),0) *
    isnull(dbo.UDF_CalcUnitPrice(TSD.TimeSheet_Id ,TSD.Project_Id,TSD.Type_Id, TS.Contractor_Id),0) +
    isnull(dbo.UDF_CalcBilledOTAmt(TSD.TimeSheet_Id ,TSD.Project_Id,TSD.Type_Id, TS.Contractor_Id),0) ),0)
    as ProjectLaborAmt
    from
    TimeSheet_Expense TS (nolock) inner join TimeSheet_Detail TSD (nolock) on TS.Time_Expense_Id = TSD.TimeSheet_Id
    inner join Contractor_Job_Order CJO (nolock) on CJO.Contractor_Id = TS.Contractor_Id and
    CJO.job_order_suffix_id = (select max(job_order_suffix_id) from contractor_job_order CJ (nolock)
    inner join Valid_Values VV (nolock) on CJ.Job_Order_Status_Id = VV.Valid_Value_Id and VV.Valid_Value = 'Approved'
    where CJ.job_order_id = CJO.job_order_id and CJ.contractor_id = CJO.contractor_id)
    and TSD.Project_Id = CJO.Project_Id
    where TSD.TimeSheet_Id in (Select isnull(TimeSheet_Id,0) from Invoice_Detail (nolock) where
    Invoice_Number = @InvoiceNo and TimeSheet_Id is not null )
    group by TSD.Project_Id) as A
    where Project.Project_Id = A.ProjectId*/
    end

    Thanks



    raj
  8. mmarovic Active Member

    Start proc with next statetments:


    Create PROCEDURE [dbo].[USP_UpdateProjAccLaborChargeType]
    @InvoiceNo varchar(20)
    as
    begin

    CREATE TABLE #tempPrjDetails
    (
    ProjectId INT primary key clustered,
    ProjectLaborAmt numeric(18,0)
    )

    CREATE TABLE #tblprjDetails1
    (
    ProjectId INTprimary key clustered,
    ProjectLaborAmt numeric(18,0)
    )

    update Project_Accounting
    Set Spent_To_Date=0
    where project_id = 0
    Let us know if it improved performance. I don't have much time for explanations right now, I am sure peers will help. If perfromance is still not sufficient, profile proc execution and let us know which statement is slowest one, so we can focus on the most critical part.
  9. krajdba New Member

    Hi Gurus,

    Here is another Big One,My main concern is don't you think we are putting extra Burden on the server
    by having this Temporary tables.Do you think by creating base tables of these Temporary tables
    I may get Great performance improvement.

    I have Test data right now,But when Rows increase into Millions I guess Temporary tables will
    slow down the server because the SP has to process millions of rows.Like this I have several
    other SPs with Temporary table.Imagine if all or most of them are executed then what happens to
    my server.....?

    Please help. Urgent......


    ALTER PROCEDURE [dbo].USP_Report_VendorPerformance
    (
    @DteFrom AS VARCHAR(10)=NULL,
    @DteTo AS VARCHAR(10)=NULL,
    @UserId AS VARCHAR(10),
    @UserType AS VARCHAR(50),
    @varVendorContactsortAS VARCHAR(100),
    @varVendorsortAS VARCHAR(100),
    @varEvaluatorsort AS VARCHAR(100),
    @ReportIdAS VARCHAR(10)
    )
    AS
    BEGIN
    DECLARE @VendorContactId AS varchar(8000)
    DECLARE @VendorId AS varchar(8000)
    DECLARE @EvaluatorId AS varchar(8000)
    SELECT @VendorContactId=VendorContactId FROM RptVendorPerformance WHERE ReportId=@ReportId
    SELECT @VendorId=VendorId FROM RptVendorPerformance WHERE ReportId=@ReportId
    SELECT @EvaluatorId=EvaluatorId FROM RptVendorPerformance WHERE ReportId=@ReportId

    DECLARE @AssistantIds VARCHAR(8000)
    --DECLARE #tblUserIds TABLE (UserID INT)
    CREATE TABLE #rptDetails
    (
    ReportIdINT,
    Vendor_Name VARCHAR(64),
    [Vendor Contact Name] VARCHAR(100),
    Evaluator VARCHAR(100),
    Period_From VARCHAR(10),
    Period_To VARCHAR(10),
    Date_entered VARCHAR(10),
    Notes VARCHAR(350),
    Vendor_rating_id INT,
    Performance_Criteria VARCHAR(512),
    valid_value VARCHAR(512),
    comapany VARCHAR(100)
    )
    CREATE TABLE #tblUserIds(UserID INT)
    CREATE TABLE #tblVendorContactids(vcid INT)
    CREATE TABLE #tblVendorids(VID INT)
    CREATE TABLE #tblEvaluatorids(EID INT)
    DECLARE @unlimitedPrivilege BIT
    DECLARE @StrLen AS INT
    DECLARE @AvgLen AS INT
    DECLARE @CurIndex1 AS INT
    DECLARE @CurIndex2 AS INT
    DECLARE @CurIndex3 AS INT
    DECLARE @CurIndex4 AS INT
    DECLARE @varTempUserId AS VARCHAR(10)
    DECLARE @varValueUserId AS VARCHAR(10)
    DECLARE @varOrderby VARCHAR(1000)
    DECLARE @varVendorcontact as VARCHAR(100)
    DECLARE @varVendorName as VARCHAR(100)
    DECLARE @varEvaluator as VARCHAR(100)
    DECLARE @intVendorcontact as int
    DECLARE @intVendorName as int
    DECLARE @intEvaluator as int
    DECLARE @vartmpVendorContactId AS VARCHAR(200)
    DECLARE @varvalVendorContactId AS VARCHAR(20)
    DECLARE @vartmpVendorId AS VARCHAR(200)
    DECLARE @varvalVendorId AS VARCHAR(20)
    DECLARE @vartmpEvaluatorId AS VARCHAR(200)
    DECLARE @varvalEvaluatorId AS VARCHAR(200)
    DECLARE @strqry AS VARCHAR(8000)
    SET @strqry=''
    DECLARE @strCond AS VARCHAR(3000)
    SET @strCond=''
    SET @CurIndex1=1
    SET @CurIndex2=1
    SET @CurIndex3=1
    SET @CurIndex4=1
    IF DATALENGTH(@varVendorContactsort)>0
    BEGIN
    SET @varVendorcontact=substring(@varVendorContactsort,0,charindex('@',@varVendorContactsort))
    SET @intVendorcontact=substring(@varVendorContactsort,charindex('@',@varVendorContactsort)+1,len(@varVendorContactsort))
    END
    IF DATALENGTH(@varVendorsort)>0
    BEGIN
    SET @varVendorName=substring(@varVendorsort,0,charindex('@',@varVendorsort))
    SET @intVendorName=substring(@varVendorsort,charindex('@',@varVendorsort)+1,len(@varVendorsort))
    END
    IF DATALENGTH(@varEvaluatorsort)>0
    BEGIN
    SET @varEvaluator=substring(@varEvaluatorsort,0,charindex('@',@varEvaluatorsort))
    SET @intEvaluator=substring(@varEvaluatorsort,charindex('@',@varEvaluatorsort)+1,len(@varEvaluatorsort))
    END
    set @varOrderby='order by '

    if @intVendorcontact < @intVendorName
    BEGIN
    if @intEvaluator > @intVendorName
    BEGIN
    print 'ff'
    print @intVendorcontact
    print @intVendorName
    --set @varorderby='first leseer is' +cast(@intVendorcontact as varchar)+'second lesser is'+cast(@intVendorName as varchar)
    SET @varorderby=@varorderby+' [Vendor Contact Name] '+ @varVendorcontact +' , '+ 'vendor_name '+ @varVendorName +' ,Evaluator '+ @varEvaluator
    END
    else if @intEvaluator < @intVendorName
    SET @varorderby=@varorderby+' [Vendor Contact Name] '+ @varVendorcontact +' ,Evaluator '+ @varEvaluator+' , '+ 'vendor_name '+ @varVendorName

    else
    if @intVendorcontact>@intEvaluator
    BEGIN
    print 'ss'
    print @intEvaluator
    print @intVendorcontact
    --set @varorderby='first leseer is' +cast(@intEvaluator as varchar)+'second lesser is'+cast(@intVendorcontact as varchar)
    SET @varorderby=@varorderby+' Evaluator '+@varEvaluator + ' , '+'[Vendor Contact Name] '+ @varVendorcontact +', vendor_name '+@varVendorName
    END
    END
    ELSE
    BEGIN
    IF @intVendorName > @intEvaluator
    BEGIN
    print 'tt'
    print @intEvaluator
    print @intVendorName
    --set @varorderby='first leseer is' +cast(@intEvaluator as varchar)+'second lesser is'+cast(@intVendorName as varchar)
    SET @varorderby=@varorderby+' Evaluator '+@varEvaluator +', '+'vendor_name '+@varVendorName +' ,[Vendor Contact Name] '+@varVendorcontact
    END
    ELSE
    BEGIN
    print @intVendorcontact
    print @intVendorName
    IF @intEvaluator > @intVendorcontact
    SET @varorderby=@varorderby+' vendor_name '+@varVendorName+' , '+'[Vendor Contact Name] '+@varVendorcontact +' ,Evaluator '+@varEvaluator
    ELSE
    SET @varorderby=@varorderby+' vendor_name '+@varVendorName+' , '+ 'Evaluator '+ @varEvaluator+' ,' + '[Vendor Contact Name] ' +@varVendorcontact
    END

    END

    print @varOrderby

    SET @AvgLen = 1000

    SELECT @unlimitedPrivilege=unlimited_Privilege FROM dbo.user_report_subscription WHERE [user_id]=@UserId
    print @unlimitedPrivilege
    SELECT @AssistantIds=dbo.UDF_GetAssistantIds(@UserId)
    --SET @AssistantIds=REPLACE(@AssistantIds,',',''',''')
    SET @StrLen = DATALENGTH(@AssistantIds)
    --SET @AssistantIds=REPLACE(@AssistantIds,',',''',''')
    WHILE (@CurIndex1 <= @StrLen)
    BEGIN
    --this is for @AssistantIds
    SET @varValueUserId=''
    SET @varTempUserId = SUBSTRING(@AssistantIds,@CurIndex1,@AvgLen)
    IF(CHARINDEX(',',@varTempUserId) = 0) -- For the last value
    SET @varValueUserId = SUBSTRING(@varTempUserId,1, LEN(@varTempUserId))
    ELSE IF(CHARINDEX(',',@varTempUserId) <> 1) --Necessary to handle zero LEN values
    SET @varValueUserId = SUBSTRING(@varTempUserId,1,CHARINDEX(',',@varTempUserId) - 1)
    SET @CurIndex1 = @CurIndex1 + LEN(@varValueUserId) + 1
    INSERT INTO #tblUserIds(UserID) VALUES (@varValueUserId)

    END
    IF DATALENGTH(@VendorId) > 0
    BEGIN
    SET @StrLen = DATALENGTH(@VendorId)
    WHILE (@CurIndex2 <= @StrLen)
    BEGIN
    SET @varvalVendorId=''
    SET @vartmpVendorId = SUBSTRING(@VendorId,@CurIndex2,@AvgLen)
    IF(CHARINDEX(',',@vartmpVendorId) = 0) -- For the last value
    SET @varvalVendorId = SUBSTRING(@vartmpVendorId,1, LEN(@vartmpVendorId))
    ELSE IF(CHARINDEX(',',@vartmpVendorId) <> 1) --Necessary to handle zero LEN values
    SET @varvalVendorId = SUBSTRING(@vartmpVendorId,1,CHARINDEX(',',@vartmpVendorId) - 1)
    SET @CurIndex2 = @CurIndex2 + LEN(@varvalVendorId) + 1
    print @varvalVendorId
    INSERT INTO #tblVendorids(Vid) VALUES (@varvalVendorId)
    END
    END
    IF DATALENGTH(@EvaluatorId) > 0
    BEGIN
    SET @StrLen = DATALENGTH(@EvaluatorId)
    WHILE (@CurIndex3 <= @StrLen)
    BEGIN
    SET @varvalEvaluatorId=''
    SET @vartmpEvaluatorId = SUBSTRING(@EvaluatorId,@CurIndex3,@AvgLen)
    IF(CHARINDEX(',',@vartmpEvaluatorId) = 0) -- For the last value
    SET @varvalEvaluatorId = SUBSTRING(@vartmpEvaluatorId,1, LEN(@vartmpEvaluatorId))
    ELSE IF(CHARINDEX(',',@vartmpEvaluatorId) <> 1) --Necessary to handle zero LEN values
    SET @varvalEvaluatorId = SUBSTRING(@vartmpEvaluatorId,1,CHARINDEX(',',@vartmpEvaluatorId) - 1)
    SET @CurIndex3 = @CurIndex3 + LEN(@varvalEvaluatorId) + 1
    print @varvalEvaluatorId
    INSERT INTO #tblEvaluatorids(Eid) VALUES (@varvalEvaluatorId)
    END
    END
    SET @CurIndex4=1
    IF DATALENGTH(@VendorContactId) > 0
    BEGIN
    SET @StrLen = DATALENGTH(@VendorContactId)
    WHILE (@CurIndex4 <= @StrLen)
    BEGIN
    SET @varvalVendorContactId=''
    SET @vartmpVendorContactId = SUBSTRING(@VendorContactId,@CurIndex4,@AvgLen)
    IF(CHARINDEX(',',@vartmpVendorContactId) = 0) -- For the last value
    SET @varvalVendorContactId = SUBSTRING(@vartmpVendorContactId,1, LEN(@vartmpVendorContactId))
    ELSE IF(CHARINDEX(',',@vartmpVendorContactId) <> 1) --Necessary to handle zero LEN values
    SET @varvalVendorContactId = SUBSTRING(@vartmpVendorContactId,1,CHARINDEX(',',@vartmpVendorContactId) - 1)
    SET @CurIndex4 = @CurIndex4 + LEN(@varvalVendorContactId) + 1
    print 'rama'
    print @varvalVendorContactId
    INSERT INTO #tblVendorContactids(vcid) VALUES (@varvalVendorContactId)
    END
    END
    print @AssistantIds
    IF @unlimitedPrivilege=1
    BEGIN
    INSERT INTO #rptDetails
    SELECT DISTINCT @ReportId,dbo.vendor.vendor_name,
    ((dbo.vendor_contact.last_name) +', '+(dbo.vendor_contact.first_name)) AS [Vendor Contact Name],
    (SELECT LOWER(([User].last_name) + ', ' + ([User].first_name)) FROM [User] WHERE [User_Id]=dbo.vendor_performance.created_by) AS Evaluator,
    CONVERT(VARCHAR(20),dbo.vendor_performance.period_from,101) AS period_from,
    CONVERT(VARCHAR(20),dbo.vendor_performance.period_to,101) AS period_to,
    CONVERT(VARCHAR(20),dbo.vendor_performance.created_on,101) AS [Date Entered],
    dbo.vendor_performance.notes,
    dbo.vendor_performance.vendor_rating_id,
    dbo.valid_values.valid_value AS [Performance_Criteria],
    VV.valid_value,
    (SELECT Company From [user] where user_id in (SELECT Invoice_name FROM general_parameters GP inner join [User] U on GP.Invoice_id=U.[User_Id] Where U.[User_Id]=@UserId))AS company
    FROM dbo.vendor
    LEFT OUTER JOIN dbo.vendor_contact ON dbo.vendor.vendor_id = dbo.vendor_contact.vendor_id
    LEFT OUTER JOIN dbo.vendor_performance ON dbo.vendor.vendor_id = dbo.vendor_performance.vendor_id AND
    dbo.vendor_contact.vendor_contact_id = dbo.vendor_performance.vendor_contact_id
    LEFT OUTER JOIN dbo.valid_values ON dbo.vendor_performance.performance_criteria_id=dbo.valid_values.valid_value_id
    LEFT OUTER JOIN dbo.valid_values AS VV ON dbo.vendor_performance.vendor_rating_id =VV.valid_value_id
    WHERE 1=1 AND (dbo.vendor_contact.vendor_contact_id IN (SELECT VCID FROM #tblVendorContactids) OR @VendorContactId IS NULL OR @VendorContactId='')
    AND (dbo.vendor.vendor_id IN (SELECT VID FROM #tblVendorids) OR @VendorId IS NULL OR @VendorId='')
    AND (dbo.vendor_performance.created_by IN (SELECT [User_Id] FROM [User] WHERE [User_Id] IN(SELECT EID FROM #tblEvaluatorids)) OR @EvaluatorId IS NULL OR @EvaluatorId='')
    AND (dbo.vendor_performance.created_on >= CONVERT(VARCHAR(10),@DteFrom,101) OR @DteFrom IS NULL OR @DteFrom='')
    AND (dbo.vendor_performance.created_on <= CONVERT(VARCHAR(10),@DteTo,101) OR @DteTo IS NULL OR @DteTo='' )
    --AND (dbo.vendor.created_by IN(SELECT UserID FROM #tblUserIds) OR @UserId IS NULL OR @UserId='')

    END
    ELSE
    BEGIN
    INSERT INTO #rptDetails
    SELECT DISTINCT @ReportId,dbo.vendor.vendor_name,
    ((dbo.vendor_contact.last_name) +', '+(dbo.vendor_contact.first_name)) AS [Vendor Contact Name],
    (SELECT LOWER(([User].last_name) + ', ' + ([User].first_name)) FROM [User] WHERE [User_Id]=dbo.vendor_performance.created_by) AS Evaluator,
    CONVERT(VARCHAR(20),dbo.vendor_performance.period_from,101) AS period_from,
    CONVERT(VARCHAR(20),dbo.vendor_performance.period_to,101) AS period_to,
    CONVERT(VARCHAR(20),dbo.vendor_performance.created_on,101) AS [Date Entered],
    dbo.vendor_performance.notes,
    dbo.vendor_performance.vendor_rating_id,
    dbo.valid_values.valid_value AS [Performance_Criteria],
    VV.valid_value,
    (SELECT Company From [user] where user_id in (SELECT Invoice_name FROM general_parameters GP inner join [User] U on GP.Invoice_id=U.[User_Id] Where U.[User_Id]=@UserId))AS company
    FROM dbo.vendor
    LEFT OUTER JOIN dbo.vendor_contact ON dbo.vendor.vendor_id = dbo.vendor_contact.vendor_id
    LEFT OUTER JOIN dbo.vendor_performance ON dbo.vendor.vendor_id = dbo.vendor_performance.vendor_id AND
    dbo.vendor_contact.vendor_contact_id = dbo.vendor_performance.vendor_contact_id
    LEFT OUTER JOIN dbo.valid_values ON dbo.vendor_performance.performance_criteria_id=dbo.valid_values.valid_value_id
    LEFT OUTER JOIN dbo.valid_values AS VV ON dbo.vendor_performance.vendor_rating_id =VV.valid_value_id
    WHERE 1=1 AND (dbo.vendor_contact.vendor_contact_id IN (SELECT VCID FROM #tblVendorContactids) OR @VendorContactId IS NULL OR @VendorContactId='')
    AND (dbo.vendor.vendor_id IN (SELECT VID FROM #tblVendorids) OR @VendorId IS NULL OR @VendorId='')
    AND (dbo.vendor_performance.created_by IN (SELECT [User_Id] FROM [User] WHERE [User_Id] IN(SELECT EID FROM #tblEvaluatorids)) OR @EvaluatorId IS NULL OR @EvaluatorId='')
    AND (dbo.vendor_performance.created_on >= CONVERT(VARCHAR(10),@DteFrom,101) OR @DteFrom IS NULL OR @DteFrom='')
    AND (dbo.vendor_performance.created_on <= CONVERT(VARCHAR(10),@DteTo,101) OR @DteTo IS NULL OR @DteTo='' )
    AND (dbo.vendor.created_by IN(SELECT UserID FROM #tblUserIds) OR @UserId IS NULL OR @UserId='')
    END
    EXEC('SELECT t.Vendor_Name,t.[Vendor Contact Name],t.Evaluator,
    t.Period_From,t.Period_To,t.Date_entered,
    t.Notes,t.Vendor_rating_id,t.Performance_Criteria,t.valid_value,t.comapany,
    r.VendorContactName AS RptVendorContactName,
    r.VendorName AS RptVendorName,
    r.EvaluatorName AS RptEvaluatorName
    FROM #rptDetails t,RptVendorPerformance R WHERE R.ReportId=t.ReportId '+ @varOrderby)
    END


    Thanks






















    raj
  10. FrankKalis Moderator

    Am I missing something, or is there really no CREATE INDEX statement?
    Also, CHARINDEX isn't the fastest solution.
    Also, scalar UDFs *are* performance killers on larger tables.
    Also, an IN clause with a whole lot of JOINs and another nested IN clause.
    Also,...

    Why are you expecting good performance here anyway? I doubt, that your temp tables are the performance killers here. It might be helpful when you post DDL of all involved tables, sample data and expected output. Your program logic isn't clear to me.

    --
    Frank
    http://www.insidesql.de
  11. krajdba New Member

    Hi Gurus,

    I Guess Derived Tables are the best solution.
    I got Terrific Performance gain.

    Thanks All.



    raj
  12. sainidaljit New Member

    what you can do is create clustered or non-clustered indexes on the temporary tables. May be you will get some perfomance boost.

    Thanks

    DJ
  13. Adriaan New Member

    quote:Originally posted by krajdba

    Hi Gurus,

    I Guess Derived Tables are the best solution.
    I got Terrific Performance gain.

    Thanks All.



    raj
    Nice to know that derived tables provide a performance boost, but I'm sure if you implement Frank's suggestions then performance will improve even further.

Share This Page