CURSOR PROBLEM , Database timeout, Multiple Users

[DarthMacgyver]

Hello,

I recently wrote a survey application. Each question is very similar.
The first questions gives me a problem when there are multiple people
taking the survey (The Database connection Timed out) I am using the
Data Access Application Blocks as ASP.NET (using VB.NET) and SQL 2000.

In there first question there can be up to 27 answers. So I figured
instead of making 27 different trips to the database I woulc just
concatenate my answers on the Stored Procedure and loop through them
and inserting them into my Answers table using a CURSOR. This all
worked great until I stress tested the application. I really think my
problem is that the CURSOR just has way to much overhead when multiple
people will be running the stored procedure.

What I am looking for is an alternative to using a cursor or if
someone could tell me what I am doing wrong (I am sure I am doing
something stupid :-)). Any help would be great.

Also the error I am getting in testing is:

Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.

I included the Stored procedure that I mentioned above.


CREATE PROCEDURE dbo.usp_InsertQ 1(
@UserName varchar(7),
@FinishedQuesti on int,
@A1 varchar(1)=0,@A 2 varchar(1)=0,@A 3 varchar(1)=0,@A 4
varchar(1)=0,@A 5 varchar(1)=0,@A 6 varchar(1)=0,@A 7 varchar(1)=0,@A 8
varchar(1)=0,@A 9 varchar(1)=0,@A 10 varchar(1)=0,@A 11 varchar(1)=0,@A 12
varchar(1)=0,
@A13 varchar(1)=0,@A 14 varchar(1)=0,@A 15 varchar(1)=0,@A 16
varchar(1)=0,@A 17 varchar(1)=0,@A 18 varchar(1)=0,@A 19
varchar(1)=0,@A 20 varchar(1)=0,@A 21 varchar(1)=0,@A 22
varchar(1)=0,@A 23 varchar(1)=0,@A 24 varchar(1)=0,
@A25 varchar(1)=0,@A 26 varchar(1)=0,@A 27 varchar(1)=0, @UserDepartment
varchar(50),@Co stCenter varchar(6)
)


AS
SET NOCOUNT ON

DECLARE @QuestionID char(11),
@NewSurveyID uniqueidentifie r,
@SurveyIDforIns ert int ,
@AnsNumber int,
@ConAnswers varchar(27),
@myAnswer int,
@RealUserDept int

SET @NewSurveyID = newid()
SET @AnsNumber = 1
SET @ConAnswers = (@A1 + @A2 + @A3 + @A4 + @A5 + @A6 + @A7 + @A8 + @A9
+ @A10 + @A11 + @A12 + @A13 + @A14 + @A15
+ @A16 + @A17 + @A18 + @A19 + @A20 + @A21 + @A22 + @A23 +
@A24 + @A25 + @A26 + @A27)

SELECT @RealUserDept = ccDepartmentID FROM t_CostCenters WHERE
ccCostCenter = @CostCenter

--Insert a new Survey since this is the First Question!
INSERT INTO t_Survey(
ID,
Name,
FinishedQuestio n,
DepartmentFK,
CostCenter)
VALUES(@NewSurv eyID,
@UserName,
@FinishedQuesti on,
@RealUserDept,
@CostCenter)

--Get the Newly Inserted Survey ID
SELECT @SurveyIDforIns ert =SurveyID FROM t_Survey WHERE ID =
@NewSurveyID

DECLARE c1 CURSOR FOR
SELECT qQuestionID
FROM t_Question
WHERE Left(qSequence, 3) = 'Q01'
ORDER BY qQuestionID

OPEN c1

FETCH NEXT FROM c1
INTO @QuestionID

WHILE @@FETCH_STATUS = 0
BEGIN

IF SUBSTRING(@ConA nswers, @AnsNumber, 1) = 0
BEGIN
SET @myAnswer = NULL
END
ELSE
BEGIN
SET @myAnswer = SUBSTRING(@ConA nswers, @AnsNumber, 1)
END

INSERT INTO t_Answers (
aSurveyFK,
aQuestionFK,
aAnswer)
VALUES(@SurveyI DforInsert,
@QuestionID,
@myAnswer
)


SET @AnsNumber = @AnsNumber +1

FETCH NEXT FROM c1
INTO @QuestionID
END

CLOSE c1
DEALLOCATE c1
GO

[Tim]

Re: CURSOR PROBLEM , Database timeout, Multiple Users

You have several alternatives.

You could extend the query timeout - but this is pushing the problem under
the carpet.

You could investigate what Locking you are causing that is stopping the
concurrent execution of other copies of this SP. Look in Enterprise Manager
/ Management / Current Activity / Locks or use sp_lock - you may be locking
the t_Survey or t_Answers tables inadvertantly. Without changing anything
else, this line of investigation is likely to give the best answers...

Cursors and temp tables are the bain of system performance, so if you can
get rid of the cursor and in general not use them your designs will be much
better...

OR

I would be tempted to look at the feasability of rather than passing in all
answers as separate parameters, passing them in as one VARCHAR and storing
that value after perhaps some massaging. EG if it is too short, IE not all
questions answered, pad it out with '_' chas or someother value that is not
a space to indicate NULL. Don't use trailing spaces, they will get lobbed
off.

Amend the rest of the schema to get answers by ordinal position from the
answer string, not from an individual record. This will only be possible if
you can know the ordinal of the question... in each survey, and the survey
is fixed once defined.

Your system could then become extensible as you would only need 1 SP to
store any type of answer set, and your current SP becomes much simpler -
either way.

AND

If SurveyID is an identity column, you could use the SCOPE_IDENTITY( ) value
instead of this select:
[color=blue]
> --Get the Newly Inserted Survey ID
> SELECT @SurveyIDforIns ert =SurveyID FROM t_Survey WHERE ID =
> @NewSurveyID[/color]

Dont use @@IDENTITY... read BOL to find out why.

- Tim


"DarthMacgy ver" <daniel.hynes@b cnepa.com> wrote in message
news:1165ca0.03 09091355.44e3c9 4d@posting.goog le.com...[color=blue]
> Hello,
>
> I recently wrote a survey application. Each question is very similar.
> The first questions gives me a problem when there are multiple people
> taking the survey (The Database connection Timed out) I am using the
> Data Access Application Blocks as ASP.NET (using VB.NET) and SQL 2000.
>
> In there first question there can be up to 27 answers. So I figured
> instead of making 27 different trips to the database I woulc just
> concatenate my answers on the Stored Procedure and loop through them
> and inserting them into my Answers table using a CURSOR. This all
> worked great until I stress tested the application. I really think my
> problem is that the CURSOR just has way to much overhead when multiple
> people will be running the stored procedure.
>
> What I am looking for is an alternative to using a cursor or if
> someone could tell me what I am doing wrong (I am sure I am doing
> something stupid :-)). Any help would be great.
>
> Also the error I am getting in testing is:
>
> Timeout expired. The timeout period elapsed prior to completion of
> the operation or the server is not responding.
>
> I included the Stored procedure that I mentioned above.
>
>
> CREATE PROCEDURE dbo.usp_InsertQ 1(
> @UserName varchar(7),
> @FinishedQuesti on int,
> @A1 varchar(1)=0,@A 2 varchar(1)=0,@A 3 varchar(1)=0,@A 4
> varchar(1)=0,@A 5 varchar(1)=0,@A 6 varchar(1)=0,@A 7 varchar(1)=0,@A 8
> varchar(1)=0,@A 9 varchar(1)=0,@A 10 varchar(1)=0,@A 11 varchar(1)=0,@A 12
> varchar(1)=0,
> @A13 varchar(1)=0,@A 14 varchar(1)=0,@A 15 varchar(1)=0,@A 16
> varchar(1)=0,@A 17 varchar(1)=0,@A 18 varchar(1)=0,@A 19
> varchar(1)=0,@A 20 varchar(1)=0,@A 21 varchar(1)=0,@A 22
> varchar(1)=0,@A 23 varchar(1)=0,@A 24 varchar(1)=0,
> @A25 varchar(1)=0,@A 26 varchar(1)=0,@A 27 varchar(1)=0, @UserDepartment
> varchar(50),@Co stCenter varchar(6)
> )
>
>
> AS
> SET NOCOUNT ON
>
> DECLARE @QuestionID char(11),
> @NewSurveyID uniqueidentifie r,
> @SurveyIDforIns ert int ,
> @AnsNumber int,
> @ConAnswers varchar(27),
> @myAnswer int,
> @RealUserDept int
>
> SET @NewSurveyID = newid()
> SET @AnsNumber = 1
> SET @ConAnswers = (@A1 + @A2 + @A3 + @A4 + @A5 + @A6 + @A7 + @A8 + @A9
> + @A10 + @A11 + @A12 + @A13 + @A14 + @A15
> + @A16 + @A17 + @A18 + @A19 + @A20 + @A21 + @A22 + @A23 +
> @A24 + @A25 + @A26 + @A27)
>
> SELECT @RealUserDept = ccDepartmentID FROM t_CostCenters WHERE
> ccCostCenter = @CostCenter
>
> --Insert a new Survey since this is the First Question!
> INSERT INTO t_Survey(
> ID,
> Name,
> FinishedQuestio n,
> DepartmentFK,
> CostCenter)
> VALUES(@NewSurv eyID,
> @UserName,
> @FinishedQuesti on,
> @RealUserDept,
> @CostCenter)
>
> --Get the Newly Inserted Survey ID
> SELECT @SurveyIDforIns ert =SurveyID FROM t_Survey WHERE ID =
> @NewSurveyID
>
> DECLARE c1 CURSOR FOR
> SELECT qQuestionID
> FROM t_Question
> WHERE Left(qSequence, 3) = 'Q01'
> ORDER BY qQuestionID
>
> OPEN c1
>
> FETCH NEXT FROM c1
> INTO @QuestionID
>
> WHILE @@FETCH_STATUS = 0
> BEGIN
>
> IF SUBSTRING(@ConA nswers, @AnsNumber, 1) = 0
> BEGIN
> SET @myAnswer = NULL
> END
> ELSE
> BEGIN
> SET @myAnswer = SUBSTRING(@ConA nswers, @AnsNumber, 1)
> END
>
> INSERT INTO t_Answers (
> aSurveyFK,
> aQuestionFK,
> aAnswer)
> VALUES(@SurveyI DforInsert,
> @QuestionID,
> @myAnswer
> )
>
>
> SET @AnsNumber = @AnsNumber +1
>
> FETCH NEXT FROM c1
> INTO @QuestionID
> END
>
> CLOSE c1
> DEALLOCATE c1
> GO[/color]

[Morgan]

Re: CURSOR PROBLEM , Database timeout, Multiple Users

In addition to Tim's answers,

You may want to consider moving the row-based operations to the middle-tier,
or at a minimum, a class in your ASP.Net application.

From an untried, 10,000 ft perspective... Store the question answers in
cookies, then place all of the cookies (since this is a known depth) in a
collection and pass the collection to your middle-tier method. Of course
there are other issues to be resolved, such as a question being re-answered.
This will also give you some control to test for a question being answered,
by checking existence of the cookie.

For example, have 2 methods in the middle-tier/class;
1. Insert the survery and retrieve the survey id
2. With the survey id, insert the answers by iterating the collection &
executing the survey for each question answered.

While this resembles what you're doing in the cursor, you remove the ado.net
timeout (to a degree, the option is still there) and place all of the logic
in the middle-tier while giving additional control as to the # of times the
insert occurs. Instead of assuming all questions have been answered (if
they're not all required), you can loop through the objects in the
collection, and only insert what has been answered.

-Morgan

"Tim" <Tim@NoSpam> wrote in message
news:uc4GCEzdDH A.828@TK2MSFTNG P11.phx.gbl...[color=blue]
> You have several alternatives.
>
> You could extend the query timeout - but this is pushing the problem under
> the carpet.
>
> You could investigate what Locking you are causing that is stopping the
> concurrent execution of other copies of this SP. Look in Enterprise[/color]
Manager[color=blue]
> / Management / Current Activity / Locks or use sp_lock - you may be[/color]
locking[color=blue]
> the t_Survey or t_Answers tables inadvertantly. Without changing anything
> else, this line of investigation is likely to give the best answers...
>
> Cursors and temp tables are the bain of system performance, so if you can
> get rid of the cursor and in general not use them your designs will be[/color]
much[color=blue]
> better...
>
> OR
>
> I would be tempted to look at the feasability of rather than passing in[/color]
all[color=blue]
> answers as separate parameters, passing them in as one VARCHAR and storing
> that value after perhaps some massaging. EG if it is too short, IE not all
> questions answered, pad it out with '_' chas or someother value that is[/color]
not[color=blue]
> a space to indicate NULL. Don't use trailing spaces, they will get lobbed
> off.
>
> Amend the rest of the schema to get answers by ordinal position from the
> answer string, not from an individual record. This will only be possible[/color]
if[color=blue]
> you can know the ordinal of the question... in each survey, and the survey
> is fixed once defined.
>
> Your system could then become extensible as you would only need 1 SP to
> store any type of answer set, and your current SP becomes much simpler -
> either way.
>
> AND
>
> If SurveyID is an identity column, you could use the SCOPE_IDENTITY( )[/color]
value[color=blue]
> instead of this select:
>[color=green]
> > --Get the Newly Inserted Survey ID
> > SELECT @SurveyIDforIns ert =SurveyID FROM t_Survey WHERE ID =
> > @NewSurveyID[/color]
>
> Dont use @@IDENTITY... read BOL to find out why.
>
> - Tim
>
>
> "DarthMacgy ver" <daniel.hynes@b cnepa.com> wrote in message
> news:1165ca0.03 09091355.44e3c9 4d@posting.goog le.com...[color=green]
> > Hello,
> >
> > I recently wrote a survey application. Each question is very similar.
> > The first questions gives me a problem when there are multiple people
> > taking the survey (The Database connection Timed out) I am using the
> > Data Access Application Blocks as ASP.NET (using VB.NET) and SQL 2000.
> >
> > In there first question there can be up to 27 answers. So I figured
> > instead of making 27 different trips to the database I woulc just
> > concatenate my answers on the Stored Procedure and loop through them
> > and inserting them into my Answers table using a CURSOR. This all
> > worked great until I stress tested the application. I really think my
> > problem is that the CURSOR just has way to much overhead when multiple
> > people will be running the stored procedure.
> >
> > What I am looking for is an alternative to using a cursor or if
> > someone could tell me what I am doing wrong (I am sure I am doing
> > something stupid :-)). Any help would be great.
> >
> > Also the error I am getting in testing is:
> >
> > Timeout expired. The timeout period elapsed prior to completion of
> > the operation or the server is not responding.
> >
> > I included the Stored procedure that I mentioned above.
> >
> >
> > CREATE PROCEDURE dbo.usp_InsertQ 1(
> > @UserName varchar(7),
> > @FinishedQuesti on int,
> > @A1 varchar(1)=0,@A 2 varchar(1)=0,@A 3 varchar(1)=0,@A 4
> > varchar(1)=0,@A 5 varchar(1)=0,@A 6 varchar(1)=0,@A 7 varchar(1)=0,@A 8
> > varchar(1)=0,@A 9 varchar(1)=0,@A 10 varchar(1)=0,@A 11 varchar(1)=0,@A 12
> > varchar(1)=0,
> > @A13 varchar(1)=0,@A 14 varchar(1)=0,@A 15 varchar(1)=0,@A 16
> > varchar(1)=0,@A 17 varchar(1)=0,@A 18 varchar(1)=0,@A 19
> > varchar(1)=0,@A 20 varchar(1)=0,@A 21 varchar(1)=0,@A 22
> > varchar(1)=0,@A 23 varchar(1)=0,@A 24 varchar(1)=0,
> > @A25 varchar(1)=0,@A 26 varchar(1)=0,@A 27 varchar(1)=0, @UserDepartment
> > varchar(50),@Co stCenter varchar(6)
> > )
> >
> >
> > AS
> > SET NOCOUNT ON
> >
> > DECLARE @QuestionID char(11),
> > @NewSurveyID uniqueidentifie r,
> > @SurveyIDforIns ert int ,
> > @AnsNumber int,
> > @ConAnswers varchar(27),
> > @myAnswer int,
> > @RealUserDept int
> >
> > SET @NewSurveyID = newid()
> > SET @AnsNumber = 1
> > SET @ConAnswers = (@A1 + @A2 + @A3 + @A4 + @A5 + @A6 + @A7 + @A8 + @A9
> > + @A10 + @A11 + @A12 + @A13 + @A14 + @A15
> > + @A16 + @A17 + @A18 + @A19 + @A20 + @A21 + @A22 + @A23 +
> > @A24 + @A25 + @A26 + @A27)
> >
> > SELECT @RealUserDept = ccDepartmentID FROM t_CostCenters WHERE
> > ccCostCenter = @CostCenter
> >
> > --Insert a new Survey since this is the First Question!
> > INSERT INTO t_Survey(
> > ID,
> > Name,
> > FinishedQuestio n,
> > DepartmentFK,
> > CostCenter)
> > VALUES(@NewSurv eyID,
> > @UserName,
> > @FinishedQuesti on,
> > @RealUserDept,
> > @CostCenter)
> >
> > --Get the Newly Inserted Survey ID
> > SELECT @SurveyIDforIns ert =SurveyID FROM t_Survey WHERE ID =
> > @NewSurveyID
> >
> > DECLARE c1 CURSOR FOR
> > SELECT qQuestionID
> > FROM t_Question
> > WHERE Left(qSequence, 3) = 'Q01'
> > ORDER BY qQuestionID
> >
> > OPEN c1
> >
> > FETCH NEXT FROM c1
> > INTO @QuestionID
> >
> > WHILE @@FETCH_STATUS = 0
> > BEGIN
> >
> > IF SUBSTRING(@ConA nswers, @AnsNumber, 1) = 0
> > BEGIN
> > SET @myAnswer = NULL
> > END
> > ELSE
> > BEGIN
> > SET @myAnswer = SUBSTRING(@ConA nswers, @AnsNumber, 1)
> > END
> >
> > INSERT INTO t_Answers (
> > aSurveyFK,
> > aQuestionFK,
> > aAnswer)
> > VALUES(@SurveyI DforInsert,
> > @QuestionID,
> > @myAnswer
> > )
> >
> >
> > SET @AnsNumber = @AnsNumber +1
> >
> > FETCH NEXT FROM c1
> > INTO @QuestionID
> > END
> >
> > CLOSE c1
> > DEALLOCATE c1
> > GO[/color]
>
>[/color]

[lindawie]

Re: CURSOR PROBLEM , Database timeout, Multiple Users

DarthMacgyver,
[color=blue]
> I recently wrote a survey application. Each question is very
> similar. The first questions gives me a problem when there are
> multiple people taking the survey (The Database connection Timed
> out) I am using the Data Access Application Blocks as ASP.NET
> (using VB.NET) and SQL 2000.
>
> In there first question there can be up to 27 answers. So I
> figured instead of making 27 different trips to the database I
> woulc just concatenate my answers on the Stored Procedure and
> loop through them and inserting them into my Answers table using
> a CURSOR. This all worked great until I stress tested the
> application. I really think my problem is that the CURSOR just
> has way to much overhead when multiple people will be running the
> stored procedure.
>
> What I am looking for is an alternative to using a cursor or if
> someone could tell me what I am doing wrong (I am sure I am doing
> something stupid :-)). Any help would be great.
>
> Also the error I am getting in testing is:
>
> Timeout expired. The timeout period elapsed prior to completion
> of the operation or the server is not responding.[/color]

You don't need a cursor for this. You can insert all answers in one
fell swoop. This will be much faster than inserting rows
individually and should address your performance problems. Make sure
you have helpful indexes on all tables.

Just guessing at the table structures...

create table t_Survey (
SurveyID int identity,
ID uniqueidentifie r,
Name varchar(7),
FinishedQuestio n int,
DepartmentFK int,
CostCenter varchar(6)
)
create table t_Answers (
aSurveyFK int,
aQuestionFK int,
aAnswer int
)
go
create table t_Question (
qQuestionID int,
qSequence varchar(6)
)
insert t_Question select 1, 'Q01'
insert t_Question select 2, 'Q01'
insert t_Question select 3, 'Q01'
insert t_Question select 4, 'Q01'
insert t_Question select 5, 'Q01'
insert t_Question select 6, 'Q01'
insert t_Question select 7, 'Q01'
insert t_Question select 8, 'Q01'
insert t_Question select 9, 'Q01'
insert t_Question select 10, 'Q01'
insert t_Question select 11, 'Q02'
insert t_Question select 12, 'Q02'
insert t_Question select 13, 'Q02'
insert t_Question select 14, 'Q02'
go

CREATE PROCEDURE dbo.usp_InsertQ 1(
@UserName varchar(7),
@FinishedQuesti on int,
@A1 varchar(1) = null, @A2 varchar(1) = null,
@A3 varchar(1) = null, @A4 varchar(1) = null,
@A5 varchar(1) = null, @A6 varchar(1) = null,
@A7 varchar(1) = null, @A8 varchar(1) = null,
@A9 varchar(1) = null, @A10 varchar(1) = null,
@A11 varchar(1) = null, @A12 varchar(1) = null,
@A13 varchar(1) = null, @A14 varchar(1) = null,
@A15 varchar(1) = null, @A16 varchar(1) = null,
@A17 varchar(1) = null, @A18 varchar(1) = null,
@A19 varchar(1) = null, @A20 varchar(1) = null,
@A21 varchar(1) = null, @A22 varchar(1) = null,
@A23 varchar(1) = null, @A24 varchar(1) = null,
@A25 varchar(1) = null, @A26 varchar(1) = null,
@A27 varchar(1) = null,
@UserDepartment varchar(50),
@CostCenter varchar(6)
)
as
set nocount on

declare @NewSurveyID uniqueidentifie r,
@SurveyIDforIns ert int,
@RealUserDept int

SET @NewSurveyID = newid()

-- temporary
SELECT @RealUserDept = @CostCenter
--SELECT @RealUserDept = ccDepartmentID
--FROM t_CostCenters WHERE ccCostCenter = @CostCenter

--Insert a new Survey since this is the first question.
INSERT INTO t_Survey(
ID,
Name,
FinishedQuestio n,
DepartmentFK,
CostCenter)
VALUES (@NewSurveyID,
@UserName,
@FinishedQuesti on,
@RealUserDept,
@CostCenter)

--Get the newly inserted SurveyID
SELECT @SurveyIDforIns ert = SurveyID
FROM t_Survey WHERE ID = @NewSurveyID

insert t_Answers (aSurveyFK, aQuestionFK, aAnswer)
select @SurveyIDforIns ert, aQuestionFK, cast(aAnswer as int)
from (
select 1, @A1 union all select 2, @A2 union all
select 3, @A3 union all select 4, @A4 union all
select 5, @A5 union all select 6, @A6 union all
select 7, @A7 union all select 8, @A8 union all
select 9, @A9 union all select 10, @A10 union all
select 11, @A11 union all select 12, @A12 union all
select 13, @A13 union all select 14, @A14 union all
select 15, @A15 union all select 16, @A16 union all
select 17, @A17 union all select 18, @A18 union all
select 19, @A19 union all select 20, @A20 union all
select 21, @A21 union all select 22, @A22 union all
select 23, @A23 union all select 24, @A24 union all
select 25, @A25 union all select 26, @A26 union all
select 27, @A27) answers(aQuesti onFK, aAnswer)
join t_Question
on qQuestionID = aQuestionFK
and left(qSequence, 3) = 'Q01'
return @@error
go


exec usp_InsertQ1 @UserName = 'xxx', @FinishedQuesti on = 10,
@A1 = 2, @A3 = 4, @A7 = 1, @A10 = 3,
@UserDepartment = 'IT', @CostCenter = '4711'

select * from t_Survey
--select * from t_Question
select * from t_Answers
go
drop procedure usp_InsertQ1
drop table t_Survey
drop table t_Answers
drop table t_Question


Linda