July 12, 2007 at 9:36 am
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)
July 12, 2007 at 10:09 am
If you generate the execution plan (paste the code into Query Analyser and press CTRL-L or select Query and then Display Estimated Execution plan on the menu), it will tell you where the bottleneck is.
There are lots of other things that the execution plan can tell you (like what index is being used for each table select etc) which could help you as well.
There is a text version (set showplan_all on) which you might need to post back here.
J
July 12, 2007 at 11:13 am
There are several things in the code that could be leading to the performance problems.
Regards,
Rubes
July 13, 2007 at 1:24 pm
Actually, you might try increasing the number of function calls in your WHERE clause. You are creating temp tables and then populating them with a list you generate from a function call. Why not use the list directly?
Change
usr.home_location IN (SELECT location_id FROM @locTable)
to
usr.home_location IN (dbo.fnc_split(@locations, ','))
The function being deterministic, SS should call it once and store the result. I would think (hope) that SS does this more efficiently than storing the result in a temp table and executing a SELECT for each row. However, I don't have the time to test it so I would be interested to know if it makes a difference.
Also, I hate seeing xTRIM calls in a SELECT. If there is a possibility the data might have superfluous white space around it, why don't you do the xTRIMming during INSERT/UPDATE and know for all the subsequent SELECTs that the data is clean? The same for UPPER and LOWER. Pick a condition your data should be in (if possible, of course) and set it to that condition as it enters your database.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply