I have this piece of code inside a stored procedure. The stored procedure executes around 28k times, but the number of re-compilations is around 170k. I think this piece of code may be one of the reasons for the recompilations.
My guess is because an index is being created on the temp table after the data is inserted, it's causing a recompile. What do you think about it? What are some other reasons why this SP may be getting recompiled so many times?
-- Create a temporary table to store user activity logs
CREATE TABLE #tmpUserLogs (
log_id INT PRIMARY KEY IDENTITY(1,1),
session_id UNIQUEIDENTIFIER,
activity_status UNIQUEIDENTIFIER );
-- Create sample session data
DECLARE @UserSessions TABLE (
session_id UNIQUEIDENTIFIER PRIMARY KEY,
user_id INT,
status_category INT,
session_type_id INT,
approval_flag INT,
activity_status UNIQUEIDENTIFIER,
is_restricted BIT,
timestamp_last_updated DATETIME );
-- Create sample user profiles
DECLARE @UserProfiles TABLE (
user_id INT PRIMARY KEY,
session_id UNIQUEIDENTIFIER );
-- Create sample activity status data
DECLARE @ActivityStatus TABLE (
activity_status UNIQUEIDENTIFIER PRIMARY KEY );
-- Create sample status filter data
DECLARE @StatusFilter TABLE (
status_id UNIQUEIDENTIFIER PRIMARY KEY );
-- Insert mock data
INSERT INTO @UserSessions VALUES
(NEWID(), 101, 1, 2, 1, NEWID(), 0, GETDATE()),
(NEWID(), 102, 2, 1, 0, NEWID(), 1, GETDATE()),
(NEWID(), 103, 3, 3, 1, NEWID(), 0, GETDATE());
INSERT INTO @UserProfiles
VALUES (101, (SELECT TOP 1 session_id FROM @UserSessions));
INSERT INTO @ActivityStatus VALUES (NEWID());
INSERT INTO @StatusFilter VALUES (NEWID());
-- Define parameters
DECLARE @applySecurityCheck BIT = 0;
DECLARE @startIndex INT = 0;
DECLARE @batchSize INT = 10;
-- Insert user activity logs into the temp table
INSERT INTO #tmpUserLogs
SELECT u.session_id, a.activity_status
FROM @UserSessions u
INNER JOIN @UserProfiles up ON up.session_id = u.session_id
INNER JOIN @ActivityStatus a ON a.activity_status = u.activity_status
INNER JOIN @StatusFilter sf ON
(@applySecurityCheck = 0 AND sf.status_id = u.activity_status)
OR
(
@applySecurityCheck = 1
AND u.is_restricted = 1
AND sf.status_id = u.activity_status
)
ORDER BY u.timestamp_last_updated DESC
OFFSET @startIndex ROWS
FETCH NEXT @batchSize ROWS ONLY;
/* Create an index on session_id */
CREATE NONCLUSTERED INDEX idx_session
ON #tmpUserLogs(session_id) INCLUDE (activity_status);
Asked by lifeisajourney
(751 rep)
Feb 3, 2025, 08:25 PM
Last activity: Feb 5, 2025, 05:24 AM
Last activity: Feb 5, 2025, 05:24 AM