Stored Proc Optimization Help | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Stored Proc Optimization Help

Hi everyone…this is a tough one. You guys have been such a help before, hopefully you can point me in the right direction now. I have a Report I am working on that uses a stored procedure to pass along all off the parameters to the reporting generator (Crystal Reports) and I was currently trying to optimize it for my users, but am not sure of the best way to go about doing it. It took me forever to get it to work as it is now. I believe the problem to be in the last block of code commented with “Select Data from View”. This returns all the values I need for the report, but I think I did it in such a way that its causing a big performance hit on the server when ran. Sometimes, it takes 3-5 minutes for the report to generate which is unacceptable. I know I shouldn#%92t be using functions in where clauses like I do, but other then that, I#%92m not sure where I can make improvements. I will post all of my code for this procedure. If anything needs to be explained, please let me know and I will inform you what I was attempting to do with the code. Any ideas/tips/help would be greatly appreciated. Thank you for your time. (Again, the last block of code is what I expect to be the time hog) ————————————————————————————————————- CREATE PROCEDURE dbo.spStudentListReport
@session_guid char(120) AS — Variables —
DECLARE @locations nvarchar(4000),
@businessunits nvarchar(4000),
@courses nvarchar(4000),
@jobtitles nvarchar(4000),
@roles nvarchar(4000),
@status char(1),
@startdate datetime,
@enddate datetime
— Set Locations —
SET @locations = (SELECT Field_id FROM tmp_ReportParams WHERE Session_guid = @session_guid AND Field_type=’locations’)
IF @locations = ‘ ‘ SET @locations = ‘ ‘ — Create Temporary Local Table for Locations —
DECLARE @locTable TABLE (location_id char(6))
INSERT INTO @locTable SELECT value FROM dbo.fnc_split(@locations, ‘,’)
— Set Business Units —
SET @businessunits = (SELECT Field_id FROM tmp_ReportParams WHERE Session_guid = @session_guid AND Field_type=’businessunits’)
IF @businessunits = ‘ ‘ SET @businessunits = ‘ ‘ — Create Temporary Local Table for Business Units —
DECLARE @busTable TABLE (region char(3))
INSERT INTO @busTable SELECT value FROM dbo.fnc_split(@businessunits, ‘,’)
— Set Job Titles —
SET @jobtitles = (SELECT Field_id FROM tmp_ReportParams WHERE Session_guid = @session_guid AND Field_type=’jobtitles’)
IF @jobtitles = ‘ ‘ SET @jobtitles = ‘ ‘ — Create Temporary Local Table for Job Titles —
DECLARE @jobTable TABLE (code char(12))
INSERT INTO @jobTable SELECT value FROM dbo.fnc_split(@jobtitles, ‘,’)
— Set Status —
SET @status = (SELECT Field_value FROM tmp_ReportParams WHERE Session_guid = @session_guid AND Field_type=’status’)
IF @status = ‘ ‘ SET @status = ‘ ‘ — Set Roles —
SET @roles = (SELECT Field_id FROM tmp_ReportParams WHERE session_guid = @session_guid AND Field_type=’roles’) — Create Temporary Local Table for Roles —
DECLARE @rolTable TABLE (role_id char(12))
INSERT INTO @rolTable SELECT value FROM dbo.fnc_split(@roles, ‘,’) — Create Temporary User Job Title Table —
DECLARE @tempJobTitles TABLE(user_id char(12), code char(12), description varchar(200), job_cat_id char(12), job_cat_name varchar(100)) INSERT INTO @tempJobTitles(user_id, code, description, job_cat_id, job_cat_name)
SELECT job.user_id,
FROM education_security.dbo.vw_getUserJobTitles job
INNER JOIN dbo.vw_getAvailableJobCategories cat ON job.job_cat_id = cat.job_cat_id
WHERE job.code IN (SELECT value FROM dbo.fnc_split(@jobtitles, ‘,’))
— Create Temporary Location Table —
DECLARE @tempAvailLocations TABLE(location_id char(6), location_name varchar(500), region char(3), region_name varchar(500), user_id char(12), short_name varchar(500)) INSERT INTO @tempAvailLocations (location_id, location_name, region, region_name, user_id, short_name)
SELECT reg.location_id,
FROM education_security.dbo.vw_getAvailableLocationsWithRegion reg
INNER JOIN dbo.vw_getAvailableLocations loc ON reg.location_id = loc.location_id
WHERE (reg.location_id IN (SELECT value FROM dbo.fnc_split(@locations, ‘,’))) OR
(reg.region IN (SELECT value FROM dbo.fnc_split(@businessunits, ‘,’)))
— Select Data From A View —
SELECT RTRIM(usr.name_last) + ‘, ‘+ RTRIM(usr.name_first) + ‘ (‘ + RTRIM(usr.user_login) + ‘)’ AS student_name,
dbo.fnc_GetStudentStatus(usr.discontinued_date, GETDATE()) AS status,
FROM dbo.user_master_view usr
INNER JOIN education_security.dbo.vw_getUsersAccessMaxLevel [max] ON usr.user_id = [max].user_id
INNER JOIN education_security.dbo.role_master rol ON [max].max_level = rol.[level]
LEFT OUTER JOIN @tempJobTitles ttl ON [max].user_id = ttl.user_id AND usr.user_id = ttl.user_id
LEFT OUTER JOIN @tempAvailLocations loc ON usr.user_id = loc.user_id AND usr.home_location = loc.location_id
WHERE (usr.home_location IN (SELECT location_id FROM @locTable) OR
loc.region IN (SELECT region FROM @busTable)) AND
ttl.code IN (SELECT code FROM @jobTable) AND
dbo.fnc_GetCurrentStatus(@status, usr.discontinued_date, GETDATE()) = @status AND
rol.role_id IN (SELECT role_id FROM @rolTable)

Did you run Profiler to find out which part of the procedure is taking so much time? You expect the last part to be taking time, but did you run the profiler to make sure about it? Satya
By all means start by replacing all the functions. dbo.fnc_split – does this take a string containing a comma-separated list, and return a single-column table?
In that case, using dynamic SQL would be worth a try, to see if it can improve performance. dbo.fnc_GetCurrentStatus – looks like perhaps a lookup function, which is usually a substitute for a join (then just do a join here), or a date comparison (then just do the date comparison here). Table variables – if they can contain a couple of hundreds of rows, then set an appropriate primary key for the table. If you need to join on a non-PK column, then use a temp table instead so you can add an index.
yes. Sometimes if the function is fairly short, you can paste the code from the function directly into the proc with some modifications. The speed difference will be shocking since UDFs are horrid in performance. If it is not a short function, it may not be possible. Michael
MCDBA "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends — if they’re okay, then it’s you!"