Can I make this Stored Proc more efficient?

R

Roy

Hey all, I'm a relative newcomer to asp.net and have 2 simple code
snippets below. Everything works fine, I'm just curious if there is a
more efficient way to do the job as the update takes quite a while.

Here's the proc:
****************************************
CREATE PROCEDURE [Update_Unassigned_VD]
@Recon char(10),
@Book nvarchar(50),
@Van nvarchar(5),
@ContNum nvarchar(8),
@VDN nvarchar(8),
@POE nvarchar(4),
@Shipname nvarchar(50),
@Saildate nvarchar(20),
@POD nvarchar(4),
@Carrier nvarchar(8),
@PCFN nvarchar(10),
@TCN nvarchar(20),
@Lastevent nvarchar(5),
@leloc nvarchar(50),
@leshipname nvarchar(50),
@ccity nvarchar(50),
@preBook nvarchar(50),
@preVan nvarchar(5),
@preCNUM nvarchar(8)
AS

SET NOCOUNT ON

UPDATE [first]
SET carrier_booking_nr = @Book,
van_owner = @Van,
tcon_container_num = @ContNum,
voydoc = @VDN,
poe = @POE,
Ship_Name = @Shipname,
minof315_event_date = @Saildate,
pod = @POD,
ocean_carrier_cd = @Carrier,
PCFN = @PCFN,
tcn = @TCN,
lastevent = @Lastevent,
lasteventloc = @leloc,
Lastevent_shipname = @leshipname,
consigneecity = @ccity,
Recon_Status = @Recon
WHERE carrier_booking_nr = @preBook AND
van_owner = @preVan AND
tcon_container_num = @preCNUM
GO
*****************************

Here's the vb code (this is the "update" portion of a datagrid, btw)
which calls the stored proc:
************************************

Sub FVDGrid_UpdateCommand(Sender As Object, E As
DataGridCommandEventArgs)
FVDConnection = New SqlConnection(blahblahblah)
Dim strRecon as String = CType(e.Item.Cells(2).Controls(0),
TextBox).Text
Dim strBook As String = CType(e.Item.Cells(3).Controls(0),
TextBox).Text
Dim strVan As String = CType(e.Item.Cells(4).Controls(0), TextBox).Text
Dim strContNum as String = CType(e.Item.Cells(5).Controls(0),
TextBox).Text
Dim strVDN as String = CType(e.Item.Cells(6).Controls(0), TextBox).Text
Dim strPoE as String = CType(e.Item.Cells(7).Controls(0), TextBox).Text
Dim strShipName as String = CType(e.Item.Cells(8).Controls(0),
TextBox).Text
Dim strSailDate as String = CType(e.Item.Cells(9).Controls(0),
TextBox).Text
Dim strPod as String = CType(e.Item.Cells(10).Controls(0),
TextBox).Text
Dim strCarrier As String = CType(e.Item.Cells(11).Controls(0),
TextBox).Text
Dim strPCFN as String = CType(e.Item.Cells(12).Controls(0),
TextBox).Text
Dim strTCN as String = CType(e.Item.Cells(13).Controls(0),
TextBox).Text
Dim strLastEvent As String = CType(e.Item.Cells(14).Controls(0),
TextBox).Text
Dim strLELoc as String = CType(e.Item.Cells(15).Controls(0),
TextBox).Text
Dim strLEShipName as String = CType(e.Item.Cells(17).Controls(0),
TextBox).Text
Dim strCCity as String = CType(e.Item.Cells(18).Controls(0),
TextBox).Text

Dim objCommand As SqlCommand = New SqlCommand("Update_Unassigned_VD",
FVDConnection)
Dim Cnt_Command As SqlCommand = New SqlCommand("Update_Counts",
FVDConnection)

With objCommand
..CommandType = CommandType.StoredProcedure
..Parameters.Add (New SqlParameter("@Recon", SqlDbType.char,10)).Value =
strRecon.Trim
..Parameters.Add (New SqlParameter("@Book",
SqlDbType.nvarchar,50)).Value = strBook.Trim
..Parameters.Add (New SqlParameter("@Van", SqlDbType.nvarchar,5)).Value
= strVan.Trim
..Parameters.Add (New SqlParameter("@ContNum",
SqlDbType.nvarchar,8)).Value = strContNum.Trim
..Parameters.Add (New SqlParameter("@VDN", SqlDbType.nvarchar,8)).Value
= strVDN.Trim
..Parameters.Add (New SqlParameter("@POE", SqlDbType.nvarchar,4)).Value
= strPoE.Trim
..Parameters.Add (New SqlParameter("@Shipname",
SqlDbType.nvarchar,50)).Value = strShipName.Trim
..Parameters.Add (New SqlParameter("@Saildate",
SqlDbType.nvarchar,20)).Value = strSailDate.Trim
..Parameters.Add (New SqlParameter("@POD", SqlDbType.nvarchar,4)).Value
= strPod.Trim
..Parameters.Add (New SqlParameter("@Carrier",
SqlDbType.nvarchar,8)).Value = strCarrier.Trim
..Parameters.Add (New SqlParameter("@PCFN",
SqlDbType.nvarchar,10)).Value = strPCFN.Trim
..Parameters.Add (New SqlParameter("@TCN", SqlDbType.nvarchar,20)).Value
= strTCN.Trim
..Parameters.Add (New SqlParameter("@Lastevent",
SqlDbType.nvarchar,5)).Value = strLastEvent.Trim
..Parameters.Add (New SqlParameter("@leloc",
SqlDbType.nvarchar,50)).Value = strLELoc.Trim
..Parameters.Add (New SqlParameter("@leshipname",
SqlDbType.nvarchar,50)).Value = strLEShipName.Trim
..Parameters.Add (New SqlParameter("@ccity",
SqlDbType.nvarchar,50)).Value = strCCity.Trim
..Parameters.Add (New SqlParameter("@preBook",
SqlDbType.nvarchar,50)).Value = strPreBook
..Parameters.Add (New SqlParameter("@preVan",
SqlDbType.nvarchar,5)).Value = strPreVan
..Parameters.Add (New SqlParameter("@preCNUM",
SqlDbType.nvarchar,8)).Value = strPreCNUM
End With

FVDConnection.Open()
objCommand.ExecuteNonQuery()
Cnt_Command.ExecuteNonQuery()
FVDConnection.Close()

FVDGrid.EditItemIndex = -1
FVDGrid.DataBind()

Bind_FVD_Grid()
End Sub
 
K

Karl Seguin

Roy:

Couple things I noticed:

What do:
FVDGrid.DataBind()
Bind_FVD_Grid()
those do? Just by the name I'd expect Bind_FVD_Grid() to be doing a
DataBind() on FVDGrid...so maybe that's being called twice when it
shouldn't?

My guess is that you can combine: Update_Unassigned_VD and Update_Counts
into a single Sproc call, but without knowing what Update_Counts does,
that's a guess

I'd be interested in knowing what's taking the time. Is it specifically the
update SPROC ? have you profiled it? Have you looked at the execution plan?
If [first] is a large table, you might need to look at a better indexing
strategy.

That's all i got...

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/


Roy said:
Hey all, I'm a relative newcomer to asp.net and have 2 simple code
snippets below. Everything works fine, I'm just curious if there is a
more efficient way to do the job as the update takes quite a while.

Here's the proc:
****************************************
CREATE PROCEDURE [Update_Unassigned_VD]
@Recon char(10),
@Book nvarchar(50),
@Van nvarchar(5),
@ContNum nvarchar(8),
@VDN nvarchar(8),
@POE nvarchar(4),
@Shipname nvarchar(50),
@Saildate nvarchar(20),
@POD nvarchar(4),
@Carrier nvarchar(8),
@PCFN nvarchar(10),
@TCN nvarchar(20),
@Lastevent nvarchar(5),
@leloc nvarchar(50),
@leshipname nvarchar(50),
@ccity nvarchar(50),
@preBook nvarchar(50),
@preVan nvarchar(5),
@preCNUM nvarchar(8)
AS

SET NOCOUNT ON

UPDATE [first]
SET carrier_booking_nr = @Book,
van_owner = @Van,
tcon_container_num = @ContNum,
voydoc = @VDN,
poe = @POE,
Ship_Name = @Shipname,
minof315_event_date = @Saildate,
pod = @POD,
ocean_carrier_cd = @Carrier,
PCFN = @PCFN,
tcn = @TCN,
lastevent = @Lastevent,
lasteventloc = @leloc,
Lastevent_shipname = @leshipname,
consigneecity = @ccity,
Recon_Status = @Recon
WHERE carrier_booking_nr = @preBook AND
van_owner = @preVan AND
tcon_container_num = @preCNUM
GO
*****************************

Here's the vb code (this is the "update" portion of a datagrid, btw)
which calls the stored proc:
************************************

Sub FVDGrid_UpdateCommand(Sender As Object, E As
DataGridCommandEventArgs)
FVDConnection = New SqlConnection(blahblahblah)
Dim strRecon as String = CType(e.Item.Cells(2).Controls(0),
TextBox).Text
Dim strBook As String = CType(e.Item.Cells(3).Controls(0),
TextBox).Text
Dim strVan As String = CType(e.Item.Cells(4).Controls(0), TextBox).Text
Dim strContNum as String = CType(e.Item.Cells(5).Controls(0),
TextBox).Text
Dim strVDN as String = CType(e.Item.Cells(6).Controls(0), TextBox).Text
Dim strPoE as String = CType(e.Item.Cells(7).Controls(0), TextBox).Text
Dim strShipName as String = CType(e.Item.Cells(8).Controls(0),
TextBox).Text
Dim strSailDate as String = CType(e.Item.Cells(9).Controls(0),
TextBox).Text
Dim strPod as String = CType(e.Item.Cells(10).Controls(0),
TextBox).Text
Dim strCarrier As String = CType(e.Item.Cells(11).Controls(0),
TextBox).Text
Dim strPCFN as String = CType(e.Item.Cells(12).Controls(0),
TextBox).Text
Dim strTCN as String = CType(e.Item.Cells(13).Controls(0),
TextBox).Text
Dim strLastEvent As String = CType(e.Item.Cells(14).Controls(0),
TextBox).Text
Dim strLELoc as String = CType(e.Item.Cells(15).Controls(0),
TextBox).Text
Dim strLEShipName as String = CType(e.Item.Cells(17).Controls(0),
TextBox).Text
Dim strCCity as String = CType(e.Item.Cells(18).Controls(0),
TextBox).Text

Dim objCommand As SqlCommand = New SqlCommand("Update_Unassigned_VD",
FVDConnection)
Dim Cnt_Command As SqlCommand = New SqlCommand("Update_Counts",
FVDConnection)

With objCommand
.CommandType = CommandType.StoredProcedure
.Parameters.Add (New SqlParameter("@Recon", SqlDbType.char,10)).Value =
strRecon.Trim
.Parameters.Add (New SqlParameter("@Book",
SqlDbType.nvarchar,50)).Value = strBook.Trim
.Parameters.Add (New SqlParameter("@Van", SqlDbType.nvarchar,5)).Value
= strVan.Trim
.Parameters.Add (New SqlParameter("@ContNum",
SqlDbType.nvarchar,8)).Value = strContNum.Trim
.Parameters.Add (New SqlParameter("@VDN", SqlDbType.nvarchar,8)).Value
= strVDN.Trim
.Parameters.Add (New SqlParameter("@POE", SqlDbType.nvarchar,4)).Value
= strPoE.Trim
.Parameters.Add (New SqlParameter("@Shipname",
SqlDbType.nvarchar,50)).Value = strShipName.Trim
.Parameters.Add (New SqlParameter("@Saildate",
SqlDbType.nvarchar,20)).Value = strSailDate.Trim
.Parameters.Add (New SqlParameter("@POD", SqlDbType.nvarchar,4)).Value
= strPod.Trim
.Parameters.Add (New SqlParameter("@Carrier",
SqlDbType.nvarchar,8)).Value = strCarrier.Trim
.Parameters.Add (New SqlParameter("@PCFN",
SqlDbType.nvarchar,10)).Value = strPCFN.Trim
.Parameters.Add (New SqlParameter("@TCN", SqlDbType.nvarchar,20)).Value
= strTCN.Trim
.Parameters.Add (New SqlParameter("@Lastevent",
SqlDbType.nvarchar,5)).Value = strLastEvent.Trim
.Parameters.Add (New SqlParameter("@leloc",
SqlDbType.nvarchar,50)).Value = strLELoc.Trim
.Parameters.Add (New SqlParameter("@leshipname",
SqlDbType.nvarchar,50)).Value = strLEShipName.Trim
.Parameters.Add (New SqlParameter("@ccity",
SqlDbType.nvarchar,50)).Value = strCCity.Trim
.Parameters.Add (New SqlParameter("@preBook",
SqlDbType.nvarchar,50)).Value = strPreBook
.Parameters.Add (New SqlParameter("@preVan",
SqlDbType.nvarchar,5)).Value = strPreVan
.Parameters.Add (New SqlParameter("@preCNUM",
SqlDbType.nvarchar,8)).Value = strPreCNUM
End With

FVDConnection.Open()
objCommand.ExecuteNonQuery()
Cnt_Command.ExecuteNonQuery()
FVDConnection.Close()

FVDGrid.EditItemIndex = -1
FVDGrid.DataBind()

Bind_FVD_Grid()
End Sub
Click to expand...
 
R

Roy

Awesome Karl, thanks!
You're right on the extra Databinds, I stripped those out.
You're also right on combining those two procs... don't know why that
hadn't occurred to me before.

And your mentioning "profiling" and "execution plan" makes me realize
how much I still have to learn about sql server... How do I profile it
and look at the exec plan? Do you have a link handy?

Thanks again.
 
K

Karl Seguin

I don' thave one handy...but start up SQL Profiler, go "File" --> "New" -->
"Trace" enter an admin username/password to your database

go to "Events" and make sure only "Stored Procedure:RPC:Completed" is in the
right box and click "run"

go to your page and make that sproc execute...you should see it (and
possibly others) in the the profiler. It will also include a time in
milliseconds. If it's a long time (say more than 300ms) click on it and
copy the command it executed....paste it in Query Analyzer, under "Query"
select "Show Execution Plan" and run the query...you'll see anew tab at
the bottom called "Execution Plan"

It's kinda messy figuring that out :) but look for Table Scans which is
typically a sign that you need some more indexes...

Karl
 
R

Roy

Great, I'll give it a try!

BTW, a quick question on the index which you may know... When I execute
stored proc "x" and lets say I have 4 indexes on the table that "x"
works on, how does "x" decide which index to use (assuming that all 4
indexes include the fields in the stored proc's WHERE clause)?

Thanks for the input!
 
K

Karl Seguin

Roy,
This is outside my field of expertise :) I just know how to read basic
execution plans and attempt to make them better :)

Karl
 
S

Scott Allen

Hi Roy:

It gets to be quite a complicated topic actually and quite
interesting. SQL Server has a cost based optimizer that looks at a
number of factors, including how selective the index is and how much
disk IO it will take to move through the index. In my experience SQL
really likes covering indexes (indexes that include all the columns
needed in the query) - probably because they will be the most IO
efficient (no need to follow a bookmark back to the original data to
grab columns that are not part of the index).

I have a little introductory article on the subject:
http://odetocode.com/Articles/237.aspx
 

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

Similar Threads

Am I blind? 5
Specified cast is not valid error 1
Uploading Large data 1
Too many arguments error 1
I made a blockchain and want to make a cryptocurrency, but my code doesn't verify hash of each block 2
Update based on SQ LDynamic Stored Proc Problem 2
Interesting Stored Procedure Problem.. 3
Problem with Large data 1

Members online

Total: 304 (members: 2, guests: 302)
Robots: 467

Forum statistics

Threads
473,967
Messages
2,570,148
Members
46,694
Latest member
LetaCadwal

Latest Threads

Top