469,621 Members | 1,678 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,621 developers. It's quick & easy.

Stored Proc Optimization Help Needed

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)

Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE dbo.spStudentListReport  
  2.  @session_guid  char(120)  
  4. AS  
  6. -- Variables --  
  7. DECLARE @locations            nvarchar(4000),   
  8.                   @businessunits     nvarchar(4000),   
  9.                   @courses              nvarchar(4000),  
  10.                  @jobtitles              nvarchar(4000),   
  11.                  @roles                  nvarchar(4000),   
  12.                  @status                 char(1),   
  13.                 @startdate            datetime,  
  14.                  @enddate             datetime  
  17. -- Set Locations --  
  18. SET @locations  = (SELECT Field_id FROM tmp_ReportParams WHERE Session_guid = @session_guid AND Field_type='locations')  
  19. IF @locations = ' ' SET @locations = ' '  
  21. -- Create Temporary Local Table for Locations --  
  22. DECLARE @locTable TABLE (location_id char(6))  
  23. INSERT INTO @locTable SELECT value FROM dbo.fnc_split(@locations, ',')  
  26. -- Set Business Units --  
  27. SET @businessunits  = (SELECT Field_id FROM tmp_ReportParams WHERE Session_guid = @session_guid AND Field_type='businessunits')  
  28. IF @businessunits = ' ' SET @businessunits = ' '  
  30. -- Create Temporary Local Table for Business Units --  
  31. DECLARE @busTable TABLE (region char(3))  
  32. INSERT INTO @busTable SELECT value FROM dbo.fnc_split(@businessunits, ',')  
  35. -- Set Job Titles --  
  36. SET @jobtitles = (SELECT Field_id FROM tmp_ReportParams WHERE Session_guid = @session_guid AND Field_type='jobtitles')  
  37. IF @jobtitles = ' ' SET @jobtitles = ' '  
  39. -- Create Temporary Local Table for Job Titles --  
  40. DECLARE @jobTable TABLE (code char(12))  
  41. INSERT INTO @jobTable SELECT value FROM dbo.fnc_split(@jobtitles, ',')  
  44. -- Set Status --  
  45. SET  @status =  (SELECT Field_value FROM tmp_ReportParams WHERE Session_guid = @session_guid AND Field_type='status')  
  46. IF @status = ' ' SET @status = '  '  
  48. -- Set Roles --  
  49. SET @roles = (SELECT Field_id FROM tmp_ReportParams WHERE session_guid =  @session_guid AND Field_type='roles')  
  51. -- Create Temporary Local Table for Roles --  
  52. DECLARE @rolTable TABLE (role_id char(12))  
  53. INSERT INTO @rolTable SELECT value FROM dbo.fnc_split(@roles, ',')  
  55. -- Create Temporary User Job Title Table ---  
  56. DECLARE @tempJobTitles TABLE(user_id char(12), code char(12), description varchar(200), job_cat_id char(12), job_cat_name varchar(100))  
  58. INSERT INTO @tempJobTitles(user_id, code, description, job_cat_id, job_cat_name)  
  59. SELECT job.user_id,   
  60.        job.code,   
  61.        job.description,   
  62.        job.job_cat_id,   
  63.        cat.job_cat_name  
  64. FROM education_security.dbo.vw_getUserJobTitles job   
  65.      INNER JOIN dbo.vw_getAvailableJobCategories cat ON job.job_cat_id = cat.job_cat_id  
  66. WHERE job.code IN (SELECT value FROM dbo.fnc_split(@jobtitles, ','))  
  69. -- Create Temporary Location Table ---  
  70. 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))  
  72. INSERT INTO @tempAvailLocations (location_id, location_name, region, region_name, user_id, short_name)  
  73. SELECT reg.location_id,   
  74.        loc.location_name,   
  75.        reg.region,   
  76.        loc.region_name,   
  77.        reg.user_id,   
  78.        reg.short_name  
  79. FROM education_security.dbo.vw_getAvailableLocationsWithRegion reg   
  80.      INNER JOIN dbo.vw_getAvailableLocations loc ON reg.location_id = loc.location_id  
  81. WHERE (reg.location_id IN (SELECT value FROM dbo.fnc_split(@locations, ','))) OR   
  82.       (reg.region IN (SELECT value FROM dbo.fnc_split(@businessunits, ',')))  
  85. -- Select Data From A View --  
  86. SELECT RTRIM(usr.name_last) + ', '+ RTRIM(usr.name_first) + ' (' + RTRIM(usr.user_login) + ')' AS student_name,   
  87.                 dbo.fnc_GetStudentStatus(usr.discontinued_date, GETDATE()) AS status,   
  88.                 loc.region_name,   
  89.                 loc.location_name,   
  90.                 ttl.job_cat_name,   
  91.                 ttl.description,  
  92.                 rol.role_name  
  93. FROM   dbo.user_master_view usr   
  94.              INNER JOIN education_security.dbo.vw_getUsersAccessMaxLevel [max] ON usr.user_id = [max].user_id   
  95.              INNER JOIN education_security.dbo.role_master rol ON [max].max_level = rol.[level]  
  96.              LEFT OUTER JOIN @tempJobTitles ttl ON [max].user_id = ttl.user_id AND usr.user_id = ttl.user_id   
  97.              LEFT OUTER JOIN @tempAvailLocations loc ON usr.user_id = loc.user_id AND usr.home_location = loc.location_id  
  98. WHERE (usr.home_location IN (SELECT location_id FROM @locTable) OR   
  99.                 loc.region IN (SELECT region FROM @busTable)) AND   
  100.                 ttl.code IN (SELECT code FROM @jobTable) AND              
  101.                 dbo.fnc_GetCurrentStatus(@status, usr.discontinued_date,  GETDATE()) = @status AND   
  102.                rol.role_id IN (SELECT role_id FROM @rolTable)   
Jul 12 '07 #1
1 1592
There are a couple of things I would recommend.
One is not using views. Views are inherently slow. It's a pain, but write out the entire select instead of using the view.

Two, use the table hint 'with (no lock,readuncommitted)'. This is a dirty read. Use it if your reporting cycle and processing cycles are not on the same schedule.

Couple of other things you could do...
create a temp table with the # hint instead of @.
This will allow you to create an index on the # table. Depending on how many records are going into this table this may increase your performance.

Check your table indexes and make sure you have the appropriate indexes on the appropriate fields.

You can also create composite indexes at the start of your proc on some of those tables and then drop them at the end. You don't want too many indexes, this will slow down updates, inserts and deletes. However, you might be able to get away with creating temporary ones inside the proc.
Jul 12 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Jason Collins | last post: by
45 posts views Thread by John | last post: by
12 posts views Thread by Jason Huang | last post: by
reply views Thread by mirandacascade | last post: by
reply views Thread by devrayhaan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.