Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Stored Proc Optimization Help
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

1 Posts

Posted - 07/12/2007 :  11:15:10  Show Profile  Reply with Quote
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)  
-- 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)   


United Kingdom
12543 Posts

Posted - 07/12/2007 :  11:35:10  Show Profile  Visit nr's Homepage  Reply with Quote
Have a look at the query plan and see what it's doing.
Put select getdate() around every query so you can verify what takes a long time.

If it's only sometimes in live then log the start and end of the SP and the parameters to a table so you can rerun something that is slow.

Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000