Re: Same query returns different result when in a stored procedure

We only do this every 3 months, so we are trying to track down if any
configuration or patching caused the problem.

Thanks for your tip but I can't find reference to the maxdop(1) hint you
mentioned in BOL. Any reference on MS or other site?

"Arnie Rowland" wrote:

There are noted issues with parallelism and some complex queries will
operate more consistently by using the maxdop(1) hint.

Has there been a second CPU added recently? Or hyperthreading turned on?

--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous


"Terence Leung" <TerenceLeung@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FF700F5C-52A7-49B2-AC7B-B949203CDDD8@xxxxxxxxxxxxxxxx
We came across a very strange problem recently. The same query of
calculating
median when run within a stored procedure, produces different (wrong)
results, when it is run outside of a stored procedure, and that the
results
can be different every time the procedure is run.

We also found out that declaring TABLE variable in the sp, instead of
using
the SELECT...INTO #QBYQ syntax returns correct results. The really sucky
part
was that using the SELECT INTO syntax has been working for a year or so
without any problem. We are crossing our figures that using TABLE variable
won't exhibit the same behaviour. We suspect it is the temp table that
screws
up, but that doesn't explain why exactly the same syntax works when
outside
of a procedure.

I would like to find out whether there are documented deficiencies of
using
#QBYQ in various versions/patches of SQL server 2000. Our version is
8.00.2039 as returned by @@Version.

Here is the stored procedure:

CREATE procedure dbo.GetCourseQbyQStatisticsTest @Year smallint, @Session
smallint, @CourseCode varchar(3), @Language char(2) AS

DECLARE @ExaminerType varchar(16)
SELECT @ExaminerType =
CASE
WHEN @Language = 'EN' THEN 'PRIMARY%'
WHEN @Language = 'FR' THEN 'SECONDARY%'
WHEN @Language IS NULL THEN '%'
ELSE '%'
END


-- insert into temporary table based on question and ascending mark
sequence
SELECT
IDENTITY(INT, 1 ,1) AS ID,
q.LABEL AS QUESTION,
q.ORDER_NUMBER AS ORDERING,
q.MAX_QUESTION_MARK AS MAX_MARK,
qm.QUESTION_POINT_MARK AS MARK,
e.ENROLLMENT_ID,
p.EXAMINER_TYPE
INTO #QBYQ
FROM QUESTION_MARK qm, QUESTION q, ENROLLMENT e, EXAM_PAPER_POOL p
WHERE
e.ENROLLMENT_ID = qm.ENROLLMENT_ID
and q.QUESTION_ID = qm.QUESTION_ID
and e.POOL_ID = p.POOL_ID
and e.COURSE_YEAR = @Year
and e.COURSE_SESSION = @Session
and e.COURSE_CODE = @CourseCode
AND P.EXAMINER_TYPE LIKE @ExaminerType
and q.PARENT_QUESTION_ID is null
AND QM.QUESTION_POINT_MARK IS NOT NULL
and q.QUESTION_TYPE_CODE = 'EN'
AND e.PROCESSING_STATUS_CODE != 'NM'
ORDER BY QM.QUESTION_ID, QM.QUESTION_POINT_MARK

--Get the median for Q by Q
SELECT *
FROM (
SELECT QUESTION, MAX(ORDERING) AS ORDERING, AVG(MARK) AS MEDIAN FROM (
SELECT QM1.QUESTION, QM1.MARK, QM1.ORDERING,
--Generate a sequence for each question
SEQ = CASE WHEN QM1.ID = QM1.ID
THEN (SELECT COUNT(*) FROM #QBYQ AS QQ
WHERE QQ.QUESTION = QM1.QUESTION
AND QQ.ID < QM1.ID) + 1
END,
MEDIANROW = ((SELECT COUNT(*) FROM #QBYQ QQ2 WHERE QQ2.QUESTION =
QM1.QUESTION)+ 1)/2.0
from #QBYQ AS QM1) AS RAW
WHERE RAW.SEQ = FLOOR(RAW.MEDIANROW)
OR RAW.SEQ = ROUND(RAW.MEDIANROW,0)
GROUP BY QUESTION
) AS MEDIAN
ORDER BY ORDERING

DROP TABLE #QBYQ

The query used outside of the stored procedure was just simply translating
the parameters into direct DECLARE and SET statements:

DECLARE @Year smallint
DECLARE @Session smallint
DECLARE @CourseCode varchar(3)
DECLARE @Language char(2)

SET @Year = 2006
SET @Session = 3
SET @CourseCode = '123'
SET @Language = 'EN'

DECLARE @ExaminerType varchar(16)
SELECT @ExaminerType =
CASE
WHEN @Language = 'EN' THEN 'PRIMARY%'
WHEN @Language = 'FR' THEN 'SECONDARY%'
WHEN @Language IS NULL THEN '%'
ELSE '%'
END


-- insert into temporary table based on question and ascending mark
sequence
SELECT
IDENTITY(INT, 1 ,1) AS ID,
q.LABEL AS QUESTION,
q.ORDER_NUMBER AS ORDERING,
q.MAX_QUESTION_MARK AS MAX_MARK,
qm.QUESTION_POINT_MARK AS MARK,
e.ENROLLMENT_ID,
p.EXAMINER_TYPE
INTO #QBYQ
FROM QUESTION_MARK qm, QUESTION q, ENROLLMENT e, EXAM_PAPER_POOL p
WHERE
e.ENROLLMENT_ID = qm.ENROLLMENT_ID
and q.QUESTION_ID = qm.QUESTION_ID
and e.POOL_ID = p.POOL_ID
and e.COURSE_YEAR = @Year
and e.COURSE_SESSION = @Session
and e.COURSE_CODE = @CourseCode
AND P.EXAMINER_TYPE LIKE @ExaminerType
and q.PARENT_QUESTION_ID is null
AND QM.QUESTION_POINT_MARK IS NOT NULL
and q.QUESTION_TYPE_CODE = 'EN'
AND e.PROCESSING_STATUS_CODE != 'NM'
ORDER BY QM.QUESTION_ID, QM.QUESTION_POINT_MARK

--Get the median for Q by Q
SELECT *
FROM (
SELECT QUESTION, MAX(ORDERING) AS ORDERING, AVG(MARK) AS MEDIAN FROM (
SELECT QM1.QUESTION, QM1.MARK, QM1.ORDERING,
--Generate a sequence for each question
SEQ = CASE WHEN QM1.ID = QM1.ID
THEN (SELECT COUNT(*) FROM #QBYQ AS QQ
WHERE QQ.QUESTION = QM1.QUESTION
AND QQ.ID < QM1.ID) + 1
END,
MEDIANROW = ((SELECT COUNT(*) FROM #QBYQ QQ2 WHERE QQ2.QUESTION =
QM1.QUESTION)+ 1)/2.0
from #QBYQ AS QM1) AS RAW
WHERE RAW.SEQ = FLOOR(RAW.MEDIANROW)
OR RAW.SEQ = ROUND(RAW.MEDIANROW,0)
GROUP BY QUESTION
) AS MEDIAN
ORDER BY ORDERING

DROP TABLE #QBYQ




.

Relevant Pages
Loading