question

I have altered it to include hardcoded variables when I pulled this chunk of code from the stored procedure. I ran it independent of the application with the same result. I need one insert statement and one update statement to execute if the required conditions are met per record share processed. The result is that two insert statements are executing for each share processed and I don’t understand how to trace how this is happening. The first is sort of lost in a sense. -- Declare variables for the fields of the tables. DECLARE @CurrentMetricID Int = 3090758; DECLARE @CurrentSharedMetricID Int; DECLARE @NewMetricID Int = 3090778; DECLARE @NewSharedMetricID Int; IF (EXISTS(SELECT b.SharedMetricID FROM Shared_Metrics b WHERE b.MetricID = @CurrentMetricID and b.nextRecordID is NULL )) BEGIN --METRIC PUBLISH FIRST DECLARE cr_metric_first CURSOR FOR SELECT sm.SharedMetricID FROM Shared_Metrics sm WHERE sm.MetricID = @CurrentMetricID AND sm.nextRecordID is NULL; OPEN cr_metric_first FETCH NEXT FROM cr_metric_first INTO @CurrentSharedMetricID; WHILE @@FETCH_STATUS = 0 BEGIN -- Metric Publishing First INSERT INTO Shared_Metrics(MetricID, ScorecardID, SortOrder, MetricOrder, CategoryOrder, RptCurrentGroup, CreatedBy, DateCreated, UpdatedBy, DateUpdated, isDeleted,ReportPeriodStart,nextRecordID) SELECT @NewMetricID, NULL, sm.sortorder, ISNULL(sm.MetricOrder, 1), ISNULL(sm.CategoryOrder, 1), sm.RptCurrentGroup, 'System', GetDate(), NULL, NULL, 'N', NULL, NULL FROM Metric_Instance mi INNER JOIN Shared_Metrics sm on mi.MetricID = sm.MetricID WHERE mi.MetricID = @CurrentMetricID AND mi.MetricDisposition <> 'Suspended' AND mi.isDeleted <> 'Y' AND sm.isDeleted <> 'Y'; SELECT @NewSharedMetricID = scope_identity(); -- Check for errors IF @@ERROR <> 0 BEGIN select 'error'; -- Determine if the cursors exists and deallocate them. IF (SELECT CURSOR_STATUS('global','cr_metric_first')) >= -1 BEGIN CLOSE cr_metric_first; DEALLOCATE cr_metric_first; END; -- ROLLBACK TRANSACTION the transaction ROLLBACK TRANSACTION -- Raise an error and return RAISERROR ('Publishing Error: Cannot insert new shared metric record for next reporting period.', 16, 1) RETURN END; -- of Error -- Update with the new SharedMetricID UPDATE Shared_Metrics SET nextRecordID = @NewSharedMetricID WHERE MetricID = @CurrentMetricID AND SharedMetricID = @CurrentSharedMetricID; IF @@ERROR <> 0 BEGIN select 'error'; -- Determine if the cursors exists and deallocate them. IF (SELECT CURSOR_STATUS('global','cr_metric_first')) >= -1 BEGIN CLOSE cr_metric_first; DEALLOCATE cr_metric_first; END; -- ROLLBACK TRANSACTION the transaction ROLLBACK TRANSACTION -- Raise an error and return RAISERROR ('Publishing Error: Cannot update shared metric NextRecordID.', 16, 1) RETURN END; -- of Error FETCH NEXT FROM cr_metric_first INTO @CurrentSharedMetricID; END;-- Metric Publishing First CLOSE cr_metric_first; DEALLOCATE cr_metric_first; END; --METRIC PUBLISH FIRST After analyzing the data, the first shared record inserted does not proceed onto the update statement. A second insert must be immediately following it and that created record is the information taken and used in the update statement. When the second share in the list is processed, the same thing happens. Since only the second record inserted is moving onto the update statement, the first is basically lost to the application. It’s key field is never linked back to the old record (Slowly Changing Dimension). When the application runs, it pulls information from this table and those records not linked back are never added to the list of metrics to display on the screen to the user. It appears that everything is functional in the application but those forgotten records that are never linked back are still being processed and carried over to the next month because the code looks them up by MetricID. The first month the share is created and only one record exists for each shared metric record in the table. After the second month, there is now one extra record not being picked up by the application for each share processed but still taking up memory and processing power. After the third month, there are 2 records extra per record shared. After the fourth, there are 4. It grows exponentially. This is per record shared and since nothing is displayed by the application, the only reason I noticed is because the amount of records being pulled and processed, causing a result of double the intended amount, started to slow down the server. It was as if there was an endless loop because of how fast this dropped processing power. I finally caught on to why the app seemed to freeze and then timeout after the code was trying to process over 65K shares for just one shared record.