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’t be using functions in where clauses like I do, but other then that, I’m 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, job.code, job.description, job.job_cat_id, cat.job_cat_name 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, loc.location_name, reg.region, loc.region_name, reg.user_id, reg.short_name 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, loc.region_name, loc.location_name, ttl.job_cat_name, ttl.description, rol.role_name 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) 
Stored Proc Optimization Help
Posted - 2007-07-12 : 11:15:10
