I'm currently working on a stored procedure (sql server 2014) and could use a second set of eyes to make sure it's efficient, The SP returns different logging data we collect throughout a users session on our site, essentially a home grown custom (and much simpler than) google analytics system.

Here's a brief summary of what's going on in the SP so far:

• a temporary table (#tmp) is created, and then populated with the results of another SP (this SP contains all of the basic logging data that we tracked way before I came along). I cannot currently modify this SP as it's called elsewhere in our site, so I do need to populate the results in a temp table and add on to it.

• After it's populated, I add some additional columns to the #tmp table to take advantage of the new fields that have been added into the logging functionality

• Next (and the place I'm not sure is the best way to go), I update the groupID column. Essentially, I want to match up the groupID from the LogOfQueries table to the records already in the #tmp table. This is taking between 7-10 seconds to execute this update, which is a lot slower than I expected (for comparison, if I just run the Admins_History SP, it runs in under a second)

Both tables have a an ID column that links them to each other (visitID = visitLogID). I've tried doing a simple select * from #tmp statement, instead of implementing the paging in the dynamic sql creation, and I get the same results, between 7-10 seconds.

The sql variables are all defined either as parameters of local variables elsewhere in this SP, I didn't include them simply to make it easier to read.

here's what I got so far:

CREATE TABLE #tmp (
        VisitID int, [Date] datetime, Zone nvarchar(50), [User] nvarchar( 100 ), Account nvarchar( 50 ), [Event] nvarchar( 50 ), Details nvarchar( 1000 ),
        ServerName nvarchar( 25 )
    );
    insert into #tmp 
        exec Admins_History @AdminID, @timeFrom, @timeTo , @eventMask, N'', @orderBy;

    --// add some new columns to the temp table to get more data from the different logging tables
    alter table #tmp add groupID int;
    alter table #tmp add classID int;
    alter table #tmp add keyword nvarchar( 256 );
    alter table #tmp add mode bit;

    --// now pull the additional data
    update #tmp set groupID = ( select lq.groupID from LogOfQueries lq where lq.VisitLogID = #tmp.VisitID );

    declare @sql nvarchar( max );
    set @sql = 'WITH Data1 AS ( select *, ROW_NUMBER() over (order by ' + @orderBy + ' ' + @orderDir + ') as ''RowNumber'' from #tmp ) ' + 
                'select *, ( SELECT MAX( RowNumber ) FROM Data1 ) AS ''TotalRows'' from Data1 where RowNumber BETWEEN ' + @rangeTxt + ' ' +
                'order by ' + @orderByIndex + ' ' + @orderDir;

     exec( @sql );

again, I'm 99% sure it's the update statement that's slowing things down, and it even doesn't update with the correct groupIDs.

Any help is greatly appreciated :)

Cheers