February 18, 2016 at 4:03 pm
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
(
InstitutionIDint,
OpeidNumber nvarchar(8),
ParentOpeidNumbernvarchar(8),
IsAdditionalLocationchar(5) DEFAULT '',
InstitutionNamenvarchar(200),
TradeNamenvarchar(200),
InstitutionTypenvarchar(30),
Statusnvarchar(300) DEFAULT '',
StatusDatenvarchar(25) DEFAULT '',
Sincenvarchar(25) DEFAULT '',
DateSubmittednvarchar(25)DEFAULT '',
SignatureSASignedDatenvarchar(25)DEFAULT '',
SignatureDODSignedDatenvarchar(25)DEFAULT '',
StreetAddress1nvarchar(50)DEFAULT '',
StreetAddress2nvarchar(50)DEFAULT '',
Citynvarchar(50)DEFAULT '',
Statenvarchar(50)DEFAULT '',
Zipnvarchar(200)DEFAULT '',
TelephoneNumbernvarchar(30)DEFAULT '',
TelephoneNumberExtnvarchar(4) DEFAULT '',
FaxNumbernvarchar(30)DEFAULT '',
Websitenvarchar(100)DEFAULT '',
DoDMouPocFirstNamenvarchar(50)DEFAULT '',
DoDMouPocLastNamenvarchar(50)DEFAULT '',
DoDMouPocTitlenvarchar(50)DEFAULT '',
DoDMouPocDepartmentnvarchar(50)DEFAULT '',
DoDMouPocTelephoneNumbernvarchar(30)DEFAULT '',
DoDMouPocTelephoneNumberExtnvarchar(4) DEFAULT '',
DoDMouPocEmailnvarchar(100)DEFAULT '',
DoDMouPocSincenvarchar(25)DEFAULT '',
ComplaintPocFirstNamenvarchar(50)DEFAULT '',
ComplainPocLastNamenvarchar(50)DEFAULT '',
ComplaintPocTitlenvarchar(50)DEFAULT '',
ComplaintPocDepartmentnvarchar(50)DEFAULT '',
ComplaintPocTelephoneNumbernvarchar(30)DEFAULT '',
ComplaintPocTelephoneNumberExtnvarchar(4) DEFAULT '',
ComplaintPocEmailnvarchar(100)DEFAULT '',
ComplaintPocSincenvarchar(25)DEFAULT '',
SigningAuthorityFirstNamenvarchar(50)DEFAULT '',
SigningAuthorityLastNamenvarchar(50)DEFAULT '',
SigningAuthorityTitlenvarchar(50)DEFAULT '',
DesignatedSigningTitlenvarchar(150)DEFAULT '',
LetterOfDesignationDocumentLinknvarchar(200)DEFAULT '',
SigningAuthorityDepartmentnvarchar(50)DEFAULT '',
SigningAuthorityTelephoneNumbernvarchar(30)DEFAULT '',
SigningAuthorityTelephoneNumberExtnvarchar(4) DEFAULT '',
SigningAuthorityEmailnvarchar(100)DEFAULT '',
SigningAuthoritySincenvarchar(25) DEFAULT '',
FirstCreatedAccountFirstNamenvarchar(50)DEFAULT '',
FirstCreatedAccountLastNamenvarchar(50)DEFAULT '',
FirstCreatedAccountEmailnvarchar(100)DEFAULT '',
FirstCreatedAccountSincenvarchar(25) DEFAULT '',
SignatureSAAreYouSocMemberchar(5) DEFAULT '',
TitleIVCompliantchar(5) DEFAULT '',
AccredAgencyCodenvarchar(50) DEFAULT '',
AccredAgencyDescriptionnvarchar(300) DEFAULT '',
AccreditationTypenvarchar(100) DEFAULT '',
SchTypenvarchar(50) DEFAULT '',
MouDurationchar(1) DEFAULT '',
MouDurationWaiverDocumentlinknvarchar(1000) DEFAULT'',
EligIndnvarchar(1) DEFAULT '',
CertCdnvarchar(1) DEFAULT '',
FdslpAppronvarchar(1) DEFAULT '',
TuitionRatesLinknvarchar(1000) DEFAULT '',
UploadTaRatesDateStampnvarchar(25) DEFAULT '',
TuitionRatesLinkAdministratornvarchar(1000) DEFAULT '',
UploadTaRatesAdministratorDateStampnvarchar(25) DEFAULT '',
CertificationStatementLinknvarchar(1000) DEFAULT '',
CertificationStatementDateStampnvarchar(25) DEFAULT '',
IsClassroomLearningchar(5) DEFAULT '',
IsDistanceLearningchar(5) DEFAULT '',
IsCorrespondenceLearningchar(5) DEFAULT '',
IsMouSignedbit
)
-- Table That gets the status history to search for
CREATE TABLE #SearchHistoryFL
(
SnapshotInstitutionID int,
InstitutionIDint,
OpeidNumbernvarchar(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 #InstitutionsTempFLON #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
February 19, 2016 at 3:19 am
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.
February 19, 2016 at 10:01 am
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/
February 29, 2016 at 2:24 pm
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply