Snippet Name: Stored_Procedure_Optimization

Tags:

Created Date: 2025-06-16 13:12:39
Last Modified Date: 2025-05-31 10:31:49

Dependencies:

DBA Notes:

Sample Output:

Title: Stored Procedure Optimization
Author: user
the attached stored procedure has a merge statement which i believe requires a table lock on the biillion row table, and woudl not use proper indexes, as well takes a very long time. can you change this to be separate insert/update/delete statements isntead statements

Author: ChatGPT
The stored procedure contains a `MERGE` statement that may indeed be causing performance issues on a billion-row table due to locking and index inefficiencies. To rewrite this logic using separate `INSERT` and `UPDATE` statements, we can follow this approach:

1. Perform an `UPDATE` on existing rows.
2. Perform an `INSERT` for rows that are not present in the target.

Here's how the restructured procedure could look:

```sql
IF OBJECT_ID('[audit].[ausp_GetGLJETransactionSummary_Company]') IS NOT NULL 
DROP PROCEDURE [audit].[ausp_GetGLJETransactionSummary_Company] 
GO

CREATE PROCEDURE [audit].[ausp_GetGLJETransactionSummary_Company]
(
    @pCompanyID varchar(3)
)
AS
BEGIN
    DECLARE @CurrentMonth DATE
    SELECT @CurrentMonth = DATEADD(day, 1 - DAY(GETDATE()), GETDATE())

    -- Temp table to hold recent transactions
    SELECT  gl.HostItemID,
            gl.AccountingMonth,
            gl.UserID,
            gl.EntryDate
    INTO #Transactions
    FROM GLJEHeader gl (NOLOCK)
    WHERE gl.CompanyID = @pCompanyID 
      AND gl.PostingDate >= DATEADD(day, -5, GETDATE()) 
      AND gl.AccountingMonth BETWEEN DATEADD(month, -1, @CurrentMonth) AND DATEADD(month, 1, @CurrentMonth)

    -- Prepare source data for the update/insert process
    SELECT gl.HostItemID,
           gl.AccountingMonth,
           ev5.JobDescription,
           ev5.JobCode,
           ev5.DepartmentName,
           ev5.BusinessTitle,
           ev5.AccountType,
           ev5.HyperionID,
           CASE WHEN CreditsTo226.HostItemID IS NOT NULL THEN 1 ELSE 0 END as HasCreditTo226,
           ROW_NUMBER() OVER (PARTITION BY gl.HostItemID ORDER BY ev5.InsertedDateTime DESC) as RowToPick
    INTO #SourceData
    FROM #Transactions gl
    LEFT JOIN audit.AD_DMS_User dms ON gl.UserID = dms.DMS_UserID
    LEFT JOIN audit.ActiveDirectory_EV5 ev5 ON dms.AD_GUID = ev5.GUID 
           AND gl.EntryDate >= ev5.[ODS_CreateDT]
    LEFT JOIN (
        SELECT DISTINCT tr.HostItemID
        FROM #Transactions tr
        JOIN GLJEDetail dt (NOLOCK) ON dt.AccountingMonth = tr.AccountingMonth 
           AND dt.CompanyID = @pCompanyID 
           AND dt.GLJEHeaderKey = tr.HostItemID 
           AND dt.BaseAccountNumber LIKE '226%' 
           AND dt.PostingAmount < 0.0
    ) CreditsTo226 ON CreditsTo226.HostItemID = gl.HostItemID
    WHERE ROW_NUMBER() OVER(PARTITION BY gl.HostItemID ORDER BY ev5.InsertedDateTime DESC) = 1;

    -- Update existing records
    UPDATE tgt
    SET tgt.JobDescription = src.JobDescription,
        tgt.JobCode = src.JobCode,
        tgt.DepartmentName = src.DepartmentName,
        tgt.BusinessTitle = src.BusinessTitle,
        tgt.AccountType = src.AccountType,
        tgt.HyperionLocation = src.HyperionID,
        tgt.HasCreditTo226 = src.HasCreditTo226
    FROM audit.GLJETransactionSummary tgt
    JOIN #SourceData src ON tgt.CompanyID = @pCompanyID 
        AND tgt.AccountingMonth = src.AccountingMonth 
        AND tgt.HostItemID = src.HostItemID;

    -- Insert new records
    INSERT INTO audit.GLJETransactionSummary (CompanyID, HostItemID, AccountingMonth, JobDescription, JobCode, DepartmentName, BusinessTitle, AccountType, HyperionLocation, HasCreditTo226)
    SELECT @pCompanyID, src.HostItemID, src.AccountingMonth, src.JobDescription, src.JobCode, src.DepartmentName, src.BusinessTitle, src.AccountType, src.HyperionID, src.HasCreditTo226
    FROM #SourceData src
    LEFT JOIN audit.GLJETransactionSummary tgt ON tgt.CompanyID = @pCompanyID 
        AND tgt.AccountingMonth = src.AccountingMonth 
        AND tgt.HostItemID = src.HostItemID
    WHERE tgt.HostItemID IS NULL;

    DROP TABLE #Transactions;
    DROP TABLE #SourceData;
END
GO
```

### Explanation:
1. **Temporary Tables**: I use a `#Transactions` table to store the necessary transaction details and a `#SourceData` table to manage the transformation logic.
2. **Update Statement**: Updates the `GLJETransactionSummary` table where records already exist.
3. **Insert Statement**: Inserts records that do not exist in the target table.
4. **Performance**: This approach avoids locking the entire table and allows SQL Server to use indexes more effectively, depending on your index structure.

You can further enhance performance by ensuring proper indexing on the `CompanyID`, `AccountingMonth`, and `HostItemID` columns. Let me know if you'd like assistance with that.