Optimize Store Procedure

  • djacobos

    Right there with Babe

    Points: 781

    I have a single Win R2 2008/64Bit/7.50GB of RAM SQL Server 2012 running and am using a WebApp running on a different server to query,run reports,etc.

    What I am trying to accomplish:

    A user generates a report based on a date and the results returns data from several tables. What I did was create new tables such as SnapshotsA, SnapshotsB, etc and the report queries from these tables vs. the real tables (A, B) because the report should be pulling data "AS OF" a specific date, what values was in the database for that day.

    Anyhow, as I insert data into the Snapshots tables, the tables keep growing and growing and its taking about 1 minute to return about 121,000 records. If there is a different way of doing this, let me know.

    My Query is long.

    ALTER PROCEDURE [dbo].[GetMasterReport]

    @Date varchar(25) = ''

    AS

    BEGIN

    SET NOCOUNT ON

    -- Format the date into a datetime

    DECLARE @endDate datetime;

    if ISNULL(@Date,'') = ''

    Begin

    set @endDate = GETDATE();

    End

    Else

    Begin

    set @endDate = @Date + ' 23:59:59';

    End

    -- determine which snapshot date to search by

    DECLARE @snapshotStartDate date = (SELECT TOP 1 SnapshotInstitutions.SnapshotDateTime FROM SnapshotInstitutions)

    DECLARE @snapshotDateTime date = @snapshotStartDate;

    PRINT '1 - ' + CAST( SYSDATETIME() AS nvarchar) + ' '; --+ --CAST( SYSTEMDATE() AS nvarchar)

    -- if search date is greater than the snapshot date, then

    -- set the snapshot date to the search date

    IF @Date > @snapshotStartDate

    BEGIN

    SET @snapshotDateTime = @Date

    END

    CREATE TABLE #InstitutionsTempFL

    (

    InstitutionID int,

    OpeidNumber nvarchar(8),

    ParentOpeidNumber nvarchar(8),

    IsAdditionalLocation char(5) DEFAULT '',

    InstitutionName nvarchar(200),

    TradeName nvarchar(200),

    InstitutionType nvarchar(30),

    Status nvarchar(300) DEFAULT '',

    StatusDate nvarchar(25) DEFAULT '',

    Since nvarchar(25) DEFAULT '',

    DateSubmitted nvarchar(25)DEFAULT '',

    SignatureSASignedDate nvarchar(25)DEFAULT '',

    SignatureDODSignedDate nvarchar(25)DEFAULT '',

    StreetAddress1 nvarchar(50)DEFAULT '',

    StreetAddress2 nvarchar(50)DEFAULT '',

    City nvarchar(50)DEFAULT '',

    State nvarchar(50)DEFAULT '',

    Zip nvarchar(200)DEFAULT '',

    TelephoneNumber nvarchar(30)DEFAULT '',

    TelephoneNumberExt nvarchar(4) DEFAULT '',

    FaxNumber nvarchar(30)DEFAULT '',

    Website nvarchar(100)DEFAULT '',

    DoDMouPocFirstName nvarchar(50)DEFAULT '',

    DoDMouPocLastName nvarchar(50)DEFAULT '',

    DoDMouPocTitle nvarchar(50)DEFAULT '',

    DoDMouPocDepartment nvarchar(50)DEFAULT '',

    DoDMouPocTelephoneNumber nvarchar(30)DEFAULT '',

    DoDMouPocTelephoneNumberExt nvarchar(4) DEFAULT '',

    DoDMouPocEmail nvarchar(100)DEFAULT '',

    DoDMouPocSince nvarchar(25)DEFAULT '',

    ComplaintPocFirstName nvarchar(50)DEFAULT '',

    ComplainPocLastName nvarchar(50)DEFAULT '',

    ComplaintPocTitle nvarchar(50)DEFAULT '',

    ComplaintPocDepartment nvarchar(50)DEFAULT '',

    ComplaintPocTelephoneNumber nvarchar(30)DEFAULT '',

    ComplaintPocTelephoneNumberExt nvarchar(4) DEFAULT '',

    ComplaintPocEmail nvarchar(100)DEFAULT '',

    ComplaintPocSince nvarchar(25)DEFAULT '',

    SigningAuthorityFirstName nvarchar(50)DEFAULT '',

    SigningAuthorityLastName nvarchar(50)DEFAULT '',

    SigningAuthorityTitle nvarchar(50)DEFAULT '',

    DesignatedSigningTitle nvarchar(150)DEFAULT '',

    LetterOfDesignationDocumentLink nvarchar(200)DEFAULT '',

    SigningAuthorityDepartment nvarchar(50)DEFAULT '',

    SigningAuthorityTelephoneNumber nvarchar(30)DEFAULT '',

    SigningAuthorityTelephoneNumberExt nvarchar(4) DEFAULT '',

    SigningAuthorityEmail nvarchar(100)DEFAULT '',

    SigningAuthoritySince nvarchar(25) DEFAULT '',

    FirstCreatedAccountFirstName nvarchar(50)DEFAULT '',

    FirstCreatedAccountLastName nvarchar(50)DEFAULT '',

    FirstCreatedAccountEmail nvarchar(100)DEFAULT '',

    FirstCreatedAccountSince nvarchar(25) DEFAULT '',

    SignatureSAAreYouSocMember char(5) DEFAULT '',

    TitleIVCompliant char(5) DEFAULT '',

    AccredAgencyCode nvarchar(50) DEFAULT '',

    AccredAgencyDescription nvarchar(300) DEFAULT '',

    AccreditationType nvarchar(100) DEFAULT '',

    SchType nvarchar(50) DEFAULT '',

    MouDuration char(1) DEFAULT '',

    MouDurationWaiverDocumentlink nvarchar(1000) DEFAULT'',

    EligInd nvarchar(1) DEFAULT '',

    CertCd nvarchar(1) DEFAULT '',

    FdslpAppro nvarchar(1) DEFAULT '',

    TuitionRatesLink nvarchar(1000) DEFAULT '',

    UploadTaRatesDateStamp nvarchar(25) DEFAULT '',

    TuitionRatesLinkAdministrator nvarchar(1000) DEFAULT '',

    UploadTaRatesAdministratorDateStamp nvarchar(25) DEFAULT '',

    CertificationStatementLink nvarchar(1000) DEFAULT '',

    CertificationStatementDateStamp nvarchar(25) DEFAULT '',

    IsClassroomLearning char(5) DEFAULT '',

    IsDistanceLearning char(5) DEFAULT '',

    IsCorrespondenceLearning char(5) DEFAULT '',

    IsMouSigned bit

    )

    -- Table That gets the status history to search for

    CREATE TABLE #SearchHistoryFL

    (

    SnapshotInstitutionID int,

    InstitutionID int,

    OpeidNumber nvarchar(8),

    Status int,

    StatusDate datetime

    )

    PRINT '2 - ' + CAST( SYSDATETIME() AS nvarchar) + ' '; --+ --CAST( SYSTEMDATE() AS nvarchar)

    -- Insert institutions; all execept those with a status of 0 will be selected

    INSERT INTO #SearchHistoryFL

    SELECT SnapshotInstitutions.SnapshotInstitutionID, SnapshotInstitutions.InstitutionID, SnapshotInstitutions.OpeidNumber,

    SnapshotInstitutionStatusHistory.Status, SnapshotInstitutionStatusHistory.Since

    FROM SnapshotInstitutions

    INNER JOIN SnapshotInstitutionStatusHistory ON SnapshotInstitutionStatusHistory.InstitutionID = SnapshotInstitutions.InstitutionID

    AND SnapshotInstitutionStatusHistory.SnapshotInstitutionStatusHistoryID =

    (SELECT TOP 1 SnapshotInstitutionStatusHistoryID FROM SnapshotInstitutionStatusHistory WHERE InstitutionID = SnapshotInstitutions.InstitutionID AND SnapshotInstitutionStatusHistory.SnapshotDateTime = @snapshotDateTime AND SnapshotInstitutionStatusHistory.Since <= @endDate

    ORDER BY InstitutionStatusHistoryID DESC)

    WHERE SnapshotInstitutions.SnapshotDateTime = @snapshotDateTime

    PRINT '3 - ' + CAST( SYSDATETIME() AS nvarchar) + ' '; --+ --CAST( SYSTEMDATE() AS nvarchar)

    ---------------------------------------------------------

    --Notes: All Statuses will be returned

    ---------------------------------------------------------

    ---------------------------------------------------------

    -- Insert Main Locations

    ---------------------------------------------------------

    INSERT INTO #InstitutionsTempFL

    (

    InstitutionID,OpeidNumber,ParentOpeidNumber,IsAdditionalLocation,InstitutionName,TradeName,InstitutionType, Status,StatusDate,Since,DateSubmitted,SignatureSASignedDate

    ,SignatureDODSignedDate,StreetAddress1,StreetAddress2,City,State,Zip,TelephoneNumber,TelephoneNumberExt,FaxNumber,Website

    ,DoDMouPocFirstName,DoDMouPocLastName,DoDMouPocTitle,DoDMouPocDepartment,DoDMouPocTelephoneNumber,DoDMouPocTelephoneNumberExt

    ,DoDMouPocEmail,DoDMouPocSince,ComplaintPocFirstName,ComplainPocLastName,ComplaintPocTitle,ComplaintPocDepartment,ComplaintPocTelephoneNumber

    ,ComplaintPocTelephoneNumberExt,ComplaintPocEmail, ComplaintPocSince,SigningAuthorityFirstName,SigningAuthorityLastName,SigningAuthorityTitle,DesignatedSigningTitle,LetterOfDesignationDocumentLink

    ,SigningAuthorityDepartment,SigningAuthorityTelephoneNumber,SigningAuthorityTelephoneNumberExt,SigningAuthorityEmail,SigningAuthoritySince,FirstCreatedAccountFirstName

    ,FirstCreatedAccountLastName,FirstCreatedAccountEmail,FirstCreatedAccountSince

    ,SignatureSAAreYouSocMember,TitleIVCompliant,AccredAgencyCode,AccredAgencyDescription,AccreditationType,SchType,MouDuration,MouDurationWaiverDocumentlink,EligInd

    ,CertCd,FdslpAppro,

    TuitionRatesLink,UploadTaRatesDateStamp,

    TuitionRatesLinkAdministrator,UploadTaRatesAdministratorDateStamp,

    CertificationStatementLink,CertificationStatementDateStamp,IsClassroomLearning,IsDistanceLearning

    ,IsCorrespondenceLearning, IsMouSigned

    )

    SELECT

    SnapshotInstitutions.InstitutionID

    ,SnapshotInstitutions.OpeidNumber

    ,SnapshotInstitutions.OpeidNumber

    ,'XXX'

    ,InstitutionName

    ,TradeName

    ,'XXXX'

    ,CASE #SearchHistoryFL.Status

    WHEN 1 THEN 'AAAA'

    WHEN 2 THEN 'BBBB'

    END As Status

    ,ISNULL(CONVERT(char,#SearchHistoryFL.StatusDate,101),'')

    ,CONVERT(varchar(10),SnapshotInstitutions.Since,101) + ' ' + RIGHT(CONVERT(char(20),SnapshotInstitutions.Since,109),8) + ' ' + RIGHT(CONVERT(char(20),SnapshotInstitutions.Since,0),3)

    ,CONVERT(varchar(10),DateSubmitted,101) + ' ' + RIGHT(CONVERT(char(20),DateSubmitted,109),8) + ' ' + RIGHT(CONVERT(char(20),DateSubmitted,0),3)

    ,ISNULL((Select TOP 1 CONVERT(varchar(10),tblSignatures.SASignedDate,101) + ' ' + RIGHT(CONVERT(char(20),tblSignatures.SASignedDate,109),8) + ' ' + RIGHT(CONVERT(char(20),tblSignatures.SASignedDate,0),3) ),'')

    ,ISNULL((Select TOP 1 CONVERT(varchar(10),tblSignatures.DODSignedDate,101) + ' ' + RIGHT(CONVERT(char(20),tblSignatures.DODSignedDate,109),8) + ' ' + RIGHT(CONVERT(char(20),tblSignatures.DODSignedDate,0),3) ),'')

    ,StreetAddress1

    ,StreetAddress2

    ,City

    ,State

    ,Zip

    ,SnapshotInstitutions.TelephoneNumber

    ,SnapshotInstitutions.TelephoneNumberExt

    ,FaxNumber

    ,Website

    ,ISNULL((pointOfContact1.FirstName),'')

    ,ISNULL((pointOfContact1.LastName),'')

    ,ISNULL((pointOfContact1.Title),'')

    ,ISNULL((pointOfContact1.Department),'')

    ,ISNULL((pointOfContact1.TelephoneNumber),'')

    ,ISNULL((pointOfContact1.TelephoneNumberExt),'')

    ,ISNULL((pointOfContact1.Email),'')

    ,ISNULL((CONVERT(varchar(10),pointOfContact1.Since,101) + ' ' + RIGHT(CONVERT(char(20),pointOfContact1.Since,109),8) + ' ' + RIGHT(CONVERT(char(20),pointOfContact1.Since,0),3)),'')

    ,ISNULL((pointOfContact2.FirstName),'')

    ,ISNULL((pointOfContact2.LastName),'')

    ,ISNULL((pointOfContact2.Title),'')

    ,ISNULL((pointOfContact2.Department),'')

    ,ISNULL((pointOfContact2.TelephoneNumber),'')

    ,ISNULL((pointOfContact2.TelephoneNumberExt),'')

    ,ISNULL((pointOfContact2.Email),'')

    ,ISNULL((CONVERT(varchar(10),pointOfContact2.Since,101) + ' ' + RIGHT(CONVERT(char(20),pointOfContact2.Since,109),8) + ' ' + RIGHT(CONVERT(char(20),pointOfContact2.Since,0),3)),'')

    ,ISNULL((pointOfContact3.FirstName),'')

    ,ISNULL((pointOfContact3.LastName),'')

    ,ISNULL((pointOfContact3.Title),'')

    ,DesignatedSigningTitle

    ,ISNULL(institutionDocumentType2.FilePath,'')

    ,ISNULL((pointOfContact3.Department),'')

    ,ISNULL((pointOfContact3.TelephoneNumber),'')

    ,ISNULL((pointOfContact3.TelephoneNumberExt),'')

    ,ISNULL((pointOfContact3.Email),'')

    ,ISNULL((CONVERT(varchar(10),pointOfContact3.Since,101) + ' ' + RIGHT(CONVERT(char(20),pointOfContact3.Since,109),8) + ' ' + RIGHT(CONVERT(char(20),pointOfContact3.Since,0),3)),'')

    ,ISNULL((pointOfContact4.FirstName),'')

    ,ISNULL((pointOfContact4.LastName),'')

    ,ISNULL((pointOfContact4.Email),'')

    ,ISNULL((CONVERT(varchar(10),pointOfContact4.Since,101) + ' ' + RIGHT(CONVERT(char(20),pointOfContact4.Since,109),8) + ' ' + RIGHT(CONVERT(char(20),pointOfContact4.Since,0),3)),'')

    ,ISNULL((Select TOP 1 CASE tblSignatures.AreYouSocMember WHEN 1 THEN 'True' WHEN 0 THEN 'False' END ),'')

    ,CASE TitleIVCompliant WHEN 1 THEN 'True' WHEN 0 THEN 'False' END

    ,ISNULL(AccredAgencyCode,'')

    ,ISNULL(AccredAgencyDescription,'')

    ,ISNULL(AccreditationType,'')

    , CASE SnapshotFeedEDData.SchType

    WHEN 1 THEN 'AAA'

    WHEN 2 THEN 'BBB'

    WHEN 3 THEN 'CCC'

    ELSE ''

    END

    ,MouDuration

    ,ISNULL(institutionDocumentType1.FilePath,'')

    ,ISNULL(EligInd,'')

    ,ISNULL(CertCd,'')

    ,ISNULL(FdslpAppro,'')

    ,ISNULL(tuitionRatesType1.FilePath,'')

    ,CASE WHEN ISNULL(tuitionRatesType1.FilePath,'') = ''

    THEN (CONVERT(varchar(10),tuitionRatesType1.Since,101) + ' ' + RIGHT(CONVERT(char(20),tuitionRatesType1.Since,109),8) + ' ' + RIGHT(CONVERT(char(20),tuitionRatesType1.Since,0),3)) ELSE '' END

    ,ISNULL(tuitionRatesType2.FilePath,'')

    ,CASE WHEN ISNULL(tuitionRatesType2.FilePath,'') = ''

    THEN (CONVERT(varchar(10),tuitionRatesType2.Since,101) + ' ' + RIGHT(CONVERT(char(20),tuitionRatesType2.Since,109),8) + ' ' + RIGHT(CONVERT(char(20),tuitionRatesType2.Since,0),3)) ELSE '' END

    ,ISNULL(SnapshotInstitutions.CertificationStatement,'')

    ,CASE WHEN ISNULL(SnapshotInstitutions.CertificationStatement,'') = '' THEN ''

    ELSE CONVERT(varchar(10),CertificationStatementDateStamp,101) + ' ' + RIGHT(CONVERT(char(20),CertificationStatementDateStamp,109),8) + ' ' + RIGHT(CONVERT(char(20),CertificationStatementDateStamp,0),3) END

    ,CASE IsClassroomLearning WHEN 1 THEN 'True' WHEN 0 THEN 'False' END

    ,CASE IsDistanceLearning WHEN 1 THEN 'True' WHEN 0 THEN 'False' END

    ,CASE IsCorrespondenceLearning WHEN 1 THEN 'True' WHEN 0 THEN 'False' END

    ,CASE WHEN tblSignatures.DODSignedDate is not null and

    dbo.SnapshotInstitutions.Status IN (SELECT id FROM dbo.[CommaListIntoTable]((SELECT ListOfStatus FROM dbo.ParticipatingInstitutionStatuses)) )--(SELECT ListOfStatus FROM dbo.ParticipatingInstitutionStatuses ))

    THEN 1 ELSE 0 END

    FROM dbo.SnapshotFeedEDData

    INNER JOIN dbo.SnapshotInstitutions ON dbo.SnapshotFeedEDData.OpeidNumber = dbo.SnapshotInstitutions.OpeidNumber

    INNER JOIN #SearchHistoryFL ON dbo.SnapshotInstitutions.SnapshotInstitutionID = #SearchHistoryFL.SnapshotInstitutionID

    OUTER APPLY

    (

    Select TOP 1 DODSignedDate, SASignedDate, AreYouSocMember From dbo.SnapshotInstitutionSignatures Where dbo.SnapshotInstitutionSignatures.InstitutionID = dbo.SnapshotInstitutions.InstitutionID and dbo.SnapshotInstitutionSignatures.SnapshotDateTime = @snapshotDateTime Order By Since Desc

    ) tblSignatures

    OUTER APPLY

    (

    Select TOP 1 FirstName,LastName,Title,Department,TelephoneNumber,TelephoneNumberExt,Email,Since From dbo.SnapshotInstitutionPointOfContacts Where dbo.SnapshotInstitutionPointOfContacts.InstitutionID = dbo.SnapshotInstitutions.InstitutionID and dbo.SnapshotInstitutionPointOfContacts.SnapshotDateTime = @snapshotDateTime AND TYPE = 1 Order By Since Desc

    ) pointOfContact1

    OUTER APPLY

    (

    Select TOP 1 FirstName,LastName,Title,Department,TelephoneNumber,TelephoneNumberExt,Email,Since From dbo.SnapshotInstitutionPointOfContacts Where dbo.SnapshotInstitutionPointOfContacts.InstitutionID = dbo.SnapshotInstitutions.InstitutionID and dbo.SnapshotInstitutionPointOfContacts.SnapshotDateTime = @snapshotDateTime AND TYPE = 2 Order By Since Desc

    ) pointOfContact2

    OUTER APPLY

    (

    Select TOP 1 FirstName,LastName,Title,Department,TelephoneNumber,TelephoneNumberExt,Email,Since From dbo.SnapshotInstitutionPointOfContacts Where dbo.SnapshotInstitutionPointOfContacts.InstitutionID = dbo.SnapshotInstitutions.InstitutionID and dbo.SnapshotInstitutionPointOfContacts.SnapshotDateTime = @snapshotDateTime AND TYPE = 3 Order By Since Desc

    ) pointOfContact3

    OUTER APPLY

    (

    Select TOP 1 FirstName,LastName,Title,Department,TelephoneNumber,TelephoneNumberExt,Email,Since From dbo.SnapshotInstitutionPointOfContacts Where dbo.SnapshotInstitutionPointOfContacts.InstitutionID = dbo.SnapshotInstitutions.InstitutionID and dbo.SnapshotInstitutionPointOfContacts.SnapshotDateTime = @snapshotDateTime AND TYPE = 4 Order By Since Desc

    ) pointOfContact4

    OUTER APPLY

    (

    Select TOP 1 FilePath, Since From dbo.SnapshotInstitutionTuitionRates ta Where ta.OpeidNumber = dbo.SnapshotInstitutions.OpeidNumber and ta.SnapshotDateTime = @snapshotDateTime AND UploadUserType = 2 Order By Since Desc

    ) tuitionRatesType1

    OUTER APPLY

    (

    Select TOP 1 FilePath, Since From dbo.SnapshotInstitutionTuitionRates ta Where ta.OpeidNumber = dbo.SnapshotInstitutions.OpeidNumber and ta.SnapshotDateTime = @snapshotDateTime AND UploadUserType = 2 Order By Since Desc

    ) tuitionRatesType2

    OUTER APPLY

    (

    SELECT TOP 1 FilePath FROM SnapshotInstitutionDocuments Where OpeidNumber = dbo.SnapshotInstitutions.OpeidNumber and SnapshotInstitutionDocuments.SnapshotDateTime = @snapshotDateTime and DocumentType = 1 Order By Since Desc

    ) institutionDocumentType1

    OUTER APPLY

    (

    SELECT TOP 1 FilePath FROM SnapshotInstitutionDocuments Where OpeidNumber = dbo.SnapshotInstitutions.OpeidNumber and SnapshotInstitutionDocuments.SnapshotDateTime = @snapshotDateTime and DocumentType = 2 Order By Since Desc

    ) institutionDocumentType2

    --WHERE dbo.SnapshotInstitutions.Status > 0 and dbo.SnapshotFeedEDData.IsSubCampus = 0 and dbo.SnapshotFeedEDData.SnapshotDateTime = @snapshotDateTime

    WHERE dbo.SnapshotFeedEDData.SnapshotDateTime = @snapshotDateTime

    PRINT '4 - ' + CAST( SYSDATETIME() AS nvarchar) + ' '; --+ --CAST( SYSTEMDATE() AS nvarchar)

    ---------------------------------------------------------

    -- Insert Additional Locations

    ---------------------------------------------------------

    INSERT INTO #InstitutionsTempFL

    (

    OpeidNumber,ParentOpeidNumber,IsAdditionalLocation,InstitutionName,InstitutionType,Since,StreetAddress1,StreetAddress2,City,State,Zip

    ,TitleIVCompliant,AccredAgencyCode,AccredAgencyDescription,AccreditationType,SchType,EligInd,CertCd,FdslpAppro

    ,Status, StatusDate,DateSubmitted,SignatureSASignedDate,SignatureDODSignedDate, MouDuration, IsMouSigned

    )

    SELECT

    SnapshotFeedEDData.OpeidNumber

    ,#InstitutionsTempFL.OpeidNumber

    ,'True'

    ,SnapshotFeedEDData.InstitutionName

    ,'Additional Location'

    ,#InstitutionsTempFL.Since

    ,SnapshotFeedEDData.StreetAddress1

    ,SnapshotFeedEDData.StreetAddress2

    ,SnapshotFeedEDData.City

    ,SnapshotFeedEDData.State

    ,SnapshotFeedEDData.Zip

    ,'True'

    ,ISNULL(SnapshotFeedEDData.AccredAgencyCode,'')

    ,ISNULL(SnapshotFeedEDData.AccredAgencyDescription,'')

    ,ISNULL(SnapshotFeedEDData.AccreditationType,'')

    , CASE SnapshotFeedEDData.SchType

    WHEN 1 THEN 'AAA'

    WHEN 2 THEN 'BBB'

    WHEN 3 THEN 'CCC'

    ELSE ''

    END

    ,ISNULL(SnapshotFeedEDData.EligInd,'')

    ,ISNULL(SnapshotFeedEDData.CertCd,'')

    ,ISNULL(SnapshotFeedEDData.FdslpAppro,'')

    ,#InstitutionsTempFL.Status

    ,#InstitutionsTempFL.StatusDate

    ,#InstitutionsTempFL.DateSubmitted

    ,#InstitutionsTempFL.SignatureSASignedDate

    ,#InstitutionsTempFL.SignatureDODSignedDate

    ,#InstitutionsTempFL.MouDuration

    ,#InstitutionsTempFL.IsMouSigned

    FROM dbo.SnapshotFeedEDData

    INNER JOIN #InstitutionsTempFL ON #InstitutionsTempFL.OpeidNumber = dbo.SnapshotFeedEDData.ParentOpeidNumber

    WHERE SnapshotFeedEDData.IsSubCampus = 1 AND SnapshotFeedEDData.TitleIVCompliant = 1 AND SnapshotFeedEDData.SnapshotDateTime = @snapshotDateTime

    PRINT '5 - ' + CAST( SYSDATETIME() AS nvarchar) + ' '; --+ --CAST( SYSTEMDATE() AS nvarchar)

    /* Drop the cloumns that are not needed */

    ALTER TABLE #InstitutionsTempFL DROP COLUMN InstitutionID

    PRINT '6 - ' + CAST( SYSDATETIME() AS nvarchar) + ' '; --+ --CAST( SYSTEMDATE() AS nvarchar)

    SELECT * FROM #InstitutionsTempFL --ORDER BY ParentOpeidNumber, OpeidNumber

    PRINT '7 - ' + CAST( SYSDATETIME() AS nvarchar) + ' '; --+ --CAST( SYSTEMDATE() AS nvarchar)

    DROP TABLE #InstitutionsTempFL

    PRINT '8 - ' + CAST( SYSDATETIME() AS nvarchar) + ' '; --+ --CAST( SYSTEMDATE() AS nvarchar)

    SET NOCOUNT OFF

    END

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    djacobos (2/18/2016)


    Anyhow, as I insert data into the Snapshots tables, the tables keep growing and growing

    Yes, that's what happens when you insert data in a table. You should also delete data for old snapshots thaht are no longer needed, so that the tables can stop growing.

    If you still experience slow performance after that, look at the execution plans to see if the large snapshot tables are being scanned completely, or if index seeks are used to directly find only the rows that are needed. Also look for key lookups and/or rid lookups with a high execution count.

    You posted a 352-line stored procedure; I am sure you understand that I cannot justify spending several hours of unpaid time on that. If you need more specific help then the above, then ask a question that is more targeted and provide more information, as follows:

    1. Pinpoint one single query in the stored procedure that is causing the most problems.

    2. Isolate the query - replace temporary tables created in previous steps with permanent tables so you can run it stand-alone.

    3. Try to simplify the query as much as possible. (Sometimes simplification automaGically solves the issue, if that happens take a step back to the most simplified version that still has the bad performance).

    4. Prepare a post for SQLServerCentral.com that includes CREATE TABLE statements for all the tables involved (leave out columns not used in the query, but do include all indexes and constraints for the remaining columns), the rowcounts of all tables involved, and an actual execution plan (attached to the post as a .sqlplan file - some people post screenshots but the actual answers are usually in the properties and we need the .sqlplan to see that).

    5. Also include a thorough description of things you already tried before you had to give up and ask for help. So that we do not waste our time repeating things you already did.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Luis Cazares

    SSC Guru

    Points: 183568

    Most of your OUTER APPLY derived tables can be rewritten as Cross Tabs to reduce the reads on your snapshot tables. I can't give an exact query as I don't have anything to be sure that it would work correctly.

    You should seriously consider using table alias to reduce the length of your code and improve readability.

    This seems to be the same as your step 2, which is eliminating the need to read a table twice.

    -- Insert institutions; all execept those with a status of 0 will be selected

    INSERT INTO #SearchHistoryFL

    SELECT

    si.SnapshotInstitutionID,

    si.InstitutionID,

    si.OpeidNumber,

    sh.Status,

    sh.Since

    FROM SnapshotInstitutions si

    CROSS APPLY (SELECT TOP 1

    sh.Status,

    sh.Since

    FROM SnapshotInstitutionStatusHistory ish

    WHERE InstitutionID = si.InstitutionID

    AND ish.SnapshotDateTime = @snapshotDateTime

    AND ish.Since <= @endDate

    ORDER BY ish.InstitutionStatusHistoryID DESC) sh

    WHERE si.SnapshotDateTime = @snapshotDateTime;

    For more help on performance, follow Hugo's advice and read this article: http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • djacobos

    Right there with Babe

    Points: 781

    Thanks for the tips. I ended up going a different route instead which will eliminate my database from growing. What I did is generate an excel report around midnight and when the users are running reports they will be pointing to the reports pre-generated.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply