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
No responses yet.