Timeout Error - SQL sever DB optimization

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Timeout Error - SQL sever DB optimization

Author

Topic

hiteshsqlserver
Starting Member

1 Post

Posted - 2004-09-11 : 10:16:22

Hi there!,

I am getting the time out error on my page "cwp_editor.asp" as below

Microsoft OLE DB Provider for ODBC Drivers error '80040e31'
[Microsoft][ODBC SQL Server Driver]Timeout expired

Basically on above page I have some text fields and one HTML editor (just like textarea) box...When I am entering a lot of content into that HTML editor box then I am getting above error. Some times I left blank that box then also I am getting the same error. I am wonder how?

My DB design for the table is...

cwebpage_id int 4 ....auto number field + primary key
group_id int 4 ...foreign key
cwp_name nvarchar 250
cwp_active tinyint 1
cwp_highlightlink tinyint
cwp_showlink tinyint 1
modify_date datetime 8
cwp_showcontent tinyint 1
cwp_contents text 16......here I am storing the content
cwp_priority bigint 8

==

Somebody has said to me that you should create another table and put two fields as below...& remove conetnt field from the master table
cwebpage_id int 4 ....foreign key
cwp_contents text 16...content field

but in this way, I will have to make change at may palces and it's a really a lot of work for me.

Can I do anything here so with less effort I can resolve the issue?

When I select/update the data, I use stored procedure here.

SP used at the time of select query
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CREATE PROCEDURE get_custom_webpages
@group_id int,
@cwebpage_id int,
@multipledata int /* 1= Single Data, 2 = Group of Data*/
AS
IF @multipledata = 1
BEGIN
SELECT cwebpage_id,
cwp_name,
cwp_active,
cwp_highlightlink,
cwp_showlink,
CONVERT(VARCHAR(50),modify_date,101),
cwp_showcontent,
cwp_contents
FROM custom_webpages
WHERE group_id = @group_id
AND (cwebpage_id = @cwebpage_id OR @cwebpage_id = 0)
ORDER BY cwp_name
END
IF @multipledata = 2
BEGIN
SELECT cwebpage_id,
cwp_name,
cwp_active,
cwp_highlightlink,
cwp_showlink,
CONVERT(VARCHAR(50),modify_date,101),
cwp_showcontent,
cwp_contents
FROM custom_webpages
WHERE group_id = @group_id
ORDER BY cwp_priority DESC
END
GO

SP used at the time of upate query
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CREATE PROCEDURE save_custom_webpages
@operation_code int, /*1 = insert, 2 = update*/
@cwebpage_id int,
@group_id int,
@cwp_name nvarchar(250),
@cwp_active tinyint,
@cwp_highlightlink tinyint,
@cwp_showlink tinyint,
@cwp_showcontent tinyint,
@cwp_contents text,
@cwp_priority bigint
AS

DECLARE
@success_code int

SELECT @success_code = 100

BEGIN TRAN

IF @operation_code = 1/*insert*/
BEGIN

INSERT INTO custom_webpages (
group_id,
cwp_name,
cwp_active,
cwp_highlightlink,
cwp_showlink,
cwp_showcontent,
cwp_contents,
cwp_priority
)
VALUES(
@group_id,
@cwp_name,
@cwp_active,
@cwp_highlightlink,
@cwp_showlink,
@cwp_showcontent,
@cwp_contents,
@cwp_priority
)

IF @@error != 0
BEGIN
SELECT @success_code = 200
GOTO ROLLBACK_AREA
END

SELECT @cwebpage_id = @@identity

END /*insert*/
ELSE IF @operation_code = 2 /*update*/
BEGIN

UPDATE custom_webpages
SET
cwp_name = @cwp_name,
cwp_active = @cwp_active,
cwp_highlightlink = @cwp_highlightlink,
cwp_showlink = @cwp_showlink,
cwp_showcontent = @cwp_showcontent,
cwp_contents = @cwp_contents,
modify_date = getdate()
WHERE cwebpage_id = @cwebpage_id

IF @@error != 0
BEGIN
SELECT @success_code = 200
GOTO ROLLBACK_AREA
END

END /*update*/
ELSE IF @operation_code = 3 /*Delete*/
BEGIN
DELETE FROM custom_webpages_subpages
WHERE cwebpage_id = @cwebpage_id

DELETE FROM custom_webpages
WHERE cwebpage_id = @cwebpage_id

IF @@error != 0
BEGIN
SELECT @success_code = 200
GOTO ROLLBACK_AREA
END
END /*Delete*/

COMMIT TRAN

SELECT @success_code, @cwebpage_id
return

ROLLBACK_AREA:
ROLLBACK TRAN

SELECT @success_code, @cwebpage_id
return
GO

===============

Can anybody help to me so I can correct my work with less efforts?
I will be really thankful to you.

Thanks.
Hitesh Patel.

Hitesh Patel

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-11 : 10:49:41

Doesn't look like it can be much faster than you have here.
You could get rid of the explicit transaction for insert and update - it doesn't do anything except hold locks longer than necessary.
Dor the delete you need to check @@error after each delete not just the last one (the second will reset the code from the first).

If the insert times out then theres not a lot you can do with the query - does this take too long when run on it's own or is it being blocked by other proceses. Is it the insert that takes too long or the network?
If it's the insert then you'll have to increase the timeout, write the data in chuncks or change the database structure or hardware configuration.

Try putting code in the SP to log the start and end time of calls so you can see how long the SP takes.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources