stored procedure takes too much time to execute

N

nicholas

I have a page that changes some data in an sql-server database: it inserts
the categories for a selected product.
The user checks some categories in a tree (with checkboxes).
These are the categories where the selected product belongs to.
These checked categories are listed in a textbox named "theIDs" (ex:
25,116,420)
In the texbox named "chk_counter" there is the number of selected
categories: in this example: 3
So, in the table, I first delete all records containing the productID and
then I insert all the checked categories.

So, if the ID of the product is 14 these records will be inserted:

productID categoryID
14 25
14 116
14 420

Now, all this works, but it is very very very long.
On my broadband connection, it takes up to 15 seconds.
On a normal connection (ex: GPRS) it can take more than a minute...

I don't understand why it takes so long??

I put my code and the stored procedures below.
If you could have a look and tell me what could be the reason, I would
really appreciate it !

Thx a lot,
Nic


Here is my code when the user clicks on the insert button:
=======================================

Sub insert_new_content (sender As Object, e As EventArgs)

Dim MyConnectionString as String =
ConfigurationSettings.AppSettings("ConnectionString")
Dim myConnection As SQLConnection
myConnection = New SQLConnection(MyConnectionString)
myConnection.Open()

'delete all the productscats
Dim myCommand0 As New SqlCommand("spdeleteproductscats", myConnection)
myCommand0.CommandType = CommandType.StoredProcedure
Dim Parameter0 As SqlParameter

Parameter0 = New SqlParameter("@productID", SqlDbType.Int, 4)
Parameter0.Direction = ParameterDirection.Input
Parameter0.Value = ctype(request.querystring("productID"),integer)
myCommand0.Parameters.Add(Parameter0)

myCommand0.ExecuteNonQuery

'insert all the selected productscats
Dim i as integer
Dim mycounter = ctype(chk_counter.text,integer)
Dim MyString, MyArray
MyString = theIDs.text
MyArray = Split(MyString,",")

For i=0 to mycounter-1

Dim myCommand As New SqlCommand("spaddproductscats", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim Parameter As SqlParameter

Parameter = New SqlParameter("@productID", SqlDbType.Int, 4)
Parameter.Direction = ParameterDirection.Input
Parameter.Value = ctype(request.querystring("productID"),integer)
myCommand.Parameters.Add(Parameter)

Parameter = New SqlParameter("@categoryID", SqlDbType.Int, 4)
Parameter.Direction = ParameterDirection.Input
Parameter.Value = MyArray(i)
myCommand.Parameters.Add(Parameter)


myCommand.ExecuteNonQuery
Next

myConnection.Close()

tblform.visible = false
processing.visible=true
lbldone.text = "Modifications done !"

End Sub
============ end of code =================



Here are the stored procedures:
=====================

======== spaddproductscats ===========
CREATE PROCEDURE spaddproductscats

(@productID int, @categoryID int)

AS

-- insert all the new selected categories
INSERT INTO tbl_productscategories (productID, categoryID) SELECT
@productID ,@categoryID

Return
GO
========== end of spaddproductscats ===========



=========== spdeleteproductscats ============
CREATE PROCEDURE spdeleteproductscats

@productID int

AS

-- delete all the previous selected categories
DELETE FROM tbl_productscategories

WHERE tbl_productscategories.productID = @productID

Return
GO
============ end of spdeleteproductscats ===========
 
H

Hans Kesting

nicholas said:
I have a page that changes some data in an sql-server database: it
inserts the categories for a selected product.
The user checks some categories in a tree (with checkboxes).
These are the categories where the selected product belongs to.
These checked categories are listed in a textbox named "theIDs" (ex:
25,116,420)
In the texbox named "chk_counter" there is the number of selected
categories: in this example: 3
So, in the table, I first delete all records containing the productID
and then I insert all the checked categories.

So, if the ID of the product is 14 these records will be inserted:

productID categoryID
14 25
14 116
14 420

Now, all this works, but it is very very very long.
On my broadband connection, it takes up to 15 seconds.
On a normal connection (ex: GPRS) it can take more than a minute...

I don't understand why it takes so long??

I put my code and the stored procedures below.
If you could have a look and tell me what could be the reason, I would
really appreciate it !

Thx a lot,
Nic

Try and insert some trace statements, or step through it in the debugger,
to see where the delay is. A different response time for different connection
speeds points to data travelling between client and server. How big is
your ViewState? How complicated is the (result-)page? Does Firefox
take just as long as IE? (I have seen situations where FF renders much faster,
processing on the server should be identical)

Your sp calls should not take this long. One thing you could change:
Re-use the command object of the "add" call, by placing everything ouside
the loop, only replacing the value of the categoryId-parameter inside the loop
(and of course, the call to ExecuteNonQuery).

Hans Kesting
 
N

nicholas

Hans Kesting said:
Try and insert some trace statements, or step through it in the debugger,
to see where the delay is. A different response time for different connection
speeds points to data travelling between client and server. How big is
your ViewState?

The viewstate seems normal.
How complicated is the (result-)page?

The result page is that same page. Some things are hidden and a
"Modifications ok" text appears.
Does Firefox
take just as long as IE? (I have seen situations where FF renders much faster,
processing on the server should be identical)

In firefox the page does not render faster. Same problem.
Your sp calls should not take this long. One thing you could change:
Re-use the command object of the "add" call, by placing everything ouside
the loop, only replacing the value of the categoryId-parameter inside the loop
(and of course, the call to ExecuteNonQuery).

I tried this, but I'm getting errors: too many arguments, etc.
How would you do that?

THX !
Nic
 
P

Phillip Ian

It's probably not the cause of your slowdown, but here's what Hans was
talking about re: re-using the command and param objects.

Dim myCommand As New SqlCommand("spaddproductscats"­, myConnection)
myCommand.CommandType = CommandType.StoredProcedure

Dim prodid As SqlParameter
prodid = New SqlParameter("@productID", SqlDbType.Int, 4)
prodid.Direction = ParameterDirection.Input
prodid.Value = ctype(request.querystring("pro­ductID"),integer)
myCommand.Parameters.Add(prodid)

Dim catid As SqlParameter
catid = New SqlParameter("@categoryID", SqlDbType.Int, 4)
catid.Direction = ParameterDirection.Input
myCommand.Parameters.Add(catid)

For i=0 to mycounter-1
catid.Value = MyArray(i)
myCommand.ExecuteNonQuery
Next
 
N

nicholas

Thx.
It's indeed not the reason for the slowdown, but it works and its better
code.

I tried to insert some time stamps in the code on several places:
response.write(now())
But the time is the same for each timestamp.
weird...

thx again.

It's probably not the cause of your slowdown, but here's what Hans was
talking about re: re-using the command and param objects.

Dim myCommand As New SqlCommand("spaddproductscats"­, myConnection)
myCommand.CommandType = CommandType.StoredProcedure

Dim prodid As SqlParameter
prodid = New SqlParameter("@productID", SqlDbType.Int, 4)
prodid.Direction = ParameterDirection.Input
prodid.Value = ctype(request.querystring("pro­ductID"),integer)
myCommand.Parameters.Add(prodid)

Dim catid As SqlParameter
catid = New SqlParameter("@categoryID", SqlDbType.Int, 4)
catid.Direction = ParameterDirection.Input
myCommand.Parameters.Add(catid)

For i=0 to mycounter-1
catid.Value = MyArray(i)
myCommand.ExecuteNonQuery
Next
 
G

Guest

Nicholas
why don't u send only ProductID and entire categories string to stored
proc and do whatever operations u want there itself...which if of simpler.
See if u have 3 categories and one product ID..3 times data has tobe
transferred from Business layer/Presentation layer to Database ...which is
depends on network speed...
try writing stored procedure which capture prodID,catID's...
write logic to split each catID and insert....
 
N

nicholas

Yes, I could try that, but I'm not sure that this is the reason.
Even when I insert just 1 category, which means there is only 1 connection
with the database, it takes much too long to be executed.

But thx anyway for your help.
Nic
 
J

John.Net

Nic,
Once the page has completed, view the source and see how big the
viewstate block is. You may find that you are passing a lot of
unnecessary information. Any control that does not need viewstate you
can turn it off. If you want to test your page timing, use a couple of
labels on your form and timestamp one as soon as possible during
page_load, then set the other one as late as possible. Verify your
watch against the system time, then load the page and check the times
against your watch. You might find that the page is building quickly
on the server, and it just takes a while to load and render in the
client.

Good Luck.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,899
Latest member
RodneyMcAu

Latest Threads

Top