Good and Bad Practice in Store procedure

Question

Good and Bad Practice in Store procedure

leos1981

SSC Journeyman

Points: 93
More actions
July 11, 2005 at 3:06 am

#110572

Dear All,
I currently doing a stress test on one of the website project. The website is using ASP and VB Component with SQL Server 7.0 (Server Win 2000). The result of the stress test seem like not very well. (Actually is very BAD ) Some of the page take about 30 seconds to process and some page are failed. This is because some of the page require a lot of data access and processing to the database.
I am very new in store proceduce. but I heard that store proceduce can help me to improve the performance. (Currently, Most of the data access is using simple SQL queries) Before I start to use the store proceduce, can anyone tell me what is the best and bad practise and standard when using SP? I hope we can compile a summary of good/bad practise and standard for SP. At least, we can use it as a guide to create a new SP.
Actually, I had use some SP in the project. But some of them return errors during the stress test. (For normal testing, it is working!!) eg. Dead Lock, Process very slow, memory low, unknown error (2147467259) and etc. Even the SP is very simple. like SP below,
CREATE PROCEDURE pr_id_get
@strTranPrefix varchar(5)
AS
Declare @val int
SELECT @val = val FROM TABLEID
WHERE tran_prefix = @strTranPrefix
/* Increment the value */
UPDATE TABLEID SET val = val + 1 WHERE tran_prefix = @strTranPrefix
/* Return the result */
SELECT @val AS val
GO

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

leos1981 SSC Journeyman Points: 93 More actions · Answer 1

I found from some acticle said that we should include the begin, commit, rollback transaction in the SP. What is the good way to include it?

I create a SP with the standard like below. Please give me a comment. Thank you.

CREATE PROCEDURE MYSP_XXXXXXX

PARAM1 varchar(20)

AS

-- Initialize

BEGIN TRANSACTION

-- Declaration

Declare @ErrMsg as as varchar(3000)

-- Processing

SET @ErrMsg = 'Failed process Update XXXX table'

Update XXX set Name = '3333' Where id = '234'

IF @@error <> 0 GOTO ERROR_ROUTINE

..... continue other process .....

-- End Transaction

COMMIT TRANSACTION

ERROR_ROUTINE:

BEGIN

ROLLBACK TRANSACTION

SELECT -1 as ErrNo, @ErrMsg as ErrMessage

Return(-1)

END

DanKennedy Hall of Fame Points: 3736 More actions · Answer 2

In my opinion I would suggest looking more closely at the construction of the ASP pages before you start questioning the performance of the database. Certainly stored procedures will help increase performance to a degree but a much greater difference can be achieved by your developers correctly building their pages i.e. caching lookup/reference data aggressively, only retrieving object/case data when required e.g. validating within the application and not on the database, repopulating field values from current data instead of redirecting and therefore refetching the data, fetch multiple recordsets back in one statement rather than multiple network round trips etc

I think your developers working on these area would produce a much more noticeable performance improvement while you worked on moving the sql statements to stored procedures, indexing tables correctly and using transactions only where necessary.

Dan
www.firstcs.co.uk

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Also you might wanna look at the identity property to create a next id automatically. This will speed things up.

leos1981 SSC Journeyman Points: 93 More actions · Answer 4

Thank for your reply. I agree that good practise in ASP can improve the speed of the website. But some time, if the processing logic is very complex which require to access to a lot of table (more than 5 tables) and a lot of complex logic. Because of the project is very huge and had work by a lot of programmers (so that is a lot of different programming style in the asp code) and the code is very very long and confusing. So I hope I can simply it as I can. At least can solved some of the serious part. I had successfully convert some of them into SP and the speed increase is more than 10 multiply of the current speed. I am happy on this result. Just got a bit worry about the database lock, because i received quite a lot of lock errors and most of them is the SP.

leos1981 SSC Journeyman Points: 93 More actions · Answer 5

Yesterday morning i do a testing on the same SP. I create a testing asp page to call the SP 500 times. I open the three same asp page simultaneously, finally i received 2-4 lock error. After that i try comment the update statement and only leave the select statement. But the result is I received 10+ lock error