March 6, 2008 at 9:26 am
hello, here i return for another problem thats been causing me hassle,
I am not sure if i am going about it in the best way eather.
My plan was to call a SP from a SP for each row in a query.
Here is a rundown.
I need a summary table of a users progress across several types of course e.g
USERNAME Course1Progress Course2Progress Course3Progress
Chris1/20 2/301/30
Tim2/15 2/315/12
John1/22/2 4/4
The users course length are not always the same, as you can see.
I have a SP that accepts a userId, and returns a set out OUPUTS that looks like this:
@course1Progress @course1length @course2Progress @course2Length @course3Progress @course3Length
Now, what i need is (somehow) be able to execute that stored procedure for each user in a seperate query and return its dataset
any help is greatly appreciated, and if i am going down the wrong route any pointers would be great.
Here is the SP i have so far
USE [a2omProDev]
GO
/****** Object: StoredProcedure [dbo].[sp_get_bomb_progress] Script Date: 03/06/2008 16:30:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[sp_get_bomb_progress]
-- Add the parameters for the stored procedure here
--INPUT VALUES
@userId int = 154,--user id
--@mindId int = 4,--mind id
--RETURN VALUES
@mindProgress int = 0OUTPUT,--number of mind modules compelted
@mindLength int = 0OUTPUT,--total number of mind courses on the course
@eLearningProgress int = 0OUTPUT,
@eLearningLength int = 0OUTPUT,
@btecProgress int = 0OUTPUT,
@btecLength int = 0OUTPUT
AS
BEGIN
DECLARE @mindId int, @btecId int, @eLearningId int
-- GET THE MIND ID
SELECT @mindId = tbl_a2om_bomb.mindId, @btecId = tbl_a2om_bomb.btecId, @eLearningId = tbl_a2om_bomb.eLearningId
FROM tbl_users
INNER JOIN tbl_licenses on tbl_users.licenseId = tbl_licenses.licenseId
INNER JOIN tbl_a2om_bomb on tbl_licenses.bombId = tbl_a2om_bomb.bombId
WHERE tbl_users.userId = @userId
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
/**************************/
/*MIND */
/**************************/
-- GET THE MINDCOURSE LENGTH
SET @mindLength = (
SELECT COUNT(*)
FROM tbl_el_course_curriculum
WHERE tbl_el_course_curriculum.courseId = @mindId)
-- GET THE USERS PROGRESS ON THIS COURSE
SET @mindProgress = (
SELECT COUNT(DISTINCT tbl_el_module_results.moduleId)
FROM tbl_el_module_results
INNER JOIN tbl_el_course_curriculum ON tbl_el_course_curriculum.moduleId = tbl_el_module_results.moduleId AND tbl_el_course_curriculum.courseId = tbl_el_module_results.courseId
WHERE tbl_el_module_results.courseId = @mindId AND tbl_el_module_results.userId = @userId
AND (score >= passMark))
/**************************/
/*EL */
/**************************/
-- GET THE MINDCOURSE LENGTH
SET @eLearningLength = (
SELECT COUNT(*)
FROM tbl_el_course_curriculum
WHERE tbl_el_course_curriculum.courseId = @eLearningId)
-- GET THE USERS PROGRESS ON THIS COURSE
SET @eLearningProgress = (
SELECT COUNT(DISTINCT tbl_el_module_results.moduleId)
FROM tbl_el_module_results
INNER JOIN tbl_el_course_curriculum ON tbl_el_course_curriculum.moduleId = tbl_el_module_results.moduleId AND tbl_el_course_curriculum.courseId = tbl_el_module_results.courseId
WHERE tbl_el_module_results.courseId = @eLearningId AND tbl_el_module_results.userId = @userId
AND (score >= passMark))
/**************************/
/*BTEC */
/**************************/
-- GET THE MINDCOURSE LENGTH
SELECT @btecLength = COUNT(*)
FROM tbl_btec_course
INNER JOIN tbl_btec_unit ON tbl_btec_unit.btecId = tbl_btec_course.btecId
INNER JOIN tbl_btec_learning_outcomes ON tbl_btec_learning_outcomes.unitId = tbl_btec_unit.unitId
INNER JOIN tbl_btec_assessment_tasks ON tbl_btec_assessment_tasks.learningOutcomeId = tbl_btec_learning_outcomes.learningOutcomeId
WHERE tbl_btec_course.btecId = @btecId
-- GET THE USERS PROGRESS ON THIS COURSE
SELECT @btecProgress = COUNT(*)
FROM tbl_btec_assessment_results
WHERE tbl_btec_assessment_results.userId = @userId
END
March 6, 2008 at 10:28 am
That's called RBAR (reebar) processing and means Row-By-Agonizing-Row. It's definately not the way to go in most cases.
Instead, you need to focus on the results that you want as a set. You need to pivot your course data so that the rows of courses become columns and then aggregate the user results within the pivoted data. If you get the query right, you shouldn't even have to store it in a seperate table, but instead can simply query for the result. You can search for examples of performing a pivot on the site. Here's one of the first [/url]that came up in a search I ran.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 7, 2008 at 4:17 am
ok, i after much reading i came up with another solution
is this approach better?
I moved the SP into a table-valued function that returns a table containing the course progress
I then called this function in a select statement using a cross apply call like..
SELECT * FROM tbl_users
cross apply
fn_user_getUserProgress(tbl_users.userId) t
WHERE tbl_users.organisationId = 1
works much better, as i can join tables to my data (i was getting stuck at that point with my SP)
March 7, 2008 at 4:52 am
I think you're still doing the same (inefficient) algorithm but with more convenient syntax. As suggested you should try to think of the overall result that you want - it's a complex one in this case! You'll need aggregates and some sort of pivot.
Perhaps you could do a small bit of code, then copy/paste that and put a bit more code around it, etc, etc so that you're building "Russian dolls" of SQL sets to get closer to your end result.
However, if you aren't doing it for many rows and your performance is acceptable, and you don't have much time, then be happy with what you've achieved 🙂
March 7, 2008 at 5:36 am
I agree. If you're processing 15 rows of data and it doesn't matter that it takes 10 seconds, walk away a winner.
If you're trying to build a large scale, repeatable, scalable process, then you really do need to try to explore enough TSQL to get to a set based solution rather than trying over & over to approach it through RBAR.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 7, 2008 at 5:42 am
ok, i have to move on from this and get working on the actuall app - but i WILL keep learning SQL - i promise.
A question however:
using SQL Server managment studio to test some queries, is the timer (00:00:00) next to the row count how long the procedure takes to run?
Becuase it sits at zero for the query, and the data is returned instantly from this function.
At the moment i have only 20 users, this might go up to about 1000 or so.
Is that timer accurate? seems to be
March 7, 2008 at 8:14 am
The timer is fairly accurate. If you want accurate timings, either use profiler to monitor your queries, or you can run SET STATISTICS TIME ON before you run the query, and the times will be written to hte messages tab.
Just remember, on small row sets, even the most inefficient method will run fairly fast.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply