Performance on Database connect in vb.net webservice connecting to Sql Server 2000

I'm writing my first webservice in visual studio 2005 using vb.net.   The web service is called by a mobile computer running windows mobile 5.0.   The web service will retrieve information from a sql server 2000 database.  
Was looking for best practices and tips on code the connection.   Looking mainly at a way to speed up the first call which takes 10 seconds to complete.   Seems to work fine after that.  Code for webservice is attached.  
thanks.
Public Class CEInternalWebServices
    Inherits System.Web.Services.WebService
    Dim conSyteline As New SqlConnection
 
    <WebMethod()> _
    Public Function GetJobInfo(ByVal pJob As String) As DataTypesVB.Enumerations.Job
        Dim enJob As New DataTypesVB.Enumerations.Job
        Dim rdrJob, rdrCo As SqlDataReader
        Dim sSuffix As String
        Dim sTmp As String
        Dim aJob(0) As String
 
        Try
            aJob = Split(pJob, "-")
            pJob = aJob(0)
            If aJob.Length > 1 Then
                sSuffix = aJob(1)
            Else
                sSuffix = "0000"
            End If
        Catch ex As Exception
            enJob.RC = 1
            enJob.ErrorMsg = ex.Message
            Return enJob
        End Try
 
        'Trim zeros for display purposes back on pda
        sTmp = pJob.TrimStart("J")
        enJob.JobNbr = "J" + sTmp.TrimStart("0")
 
        conSyteline.ConnectionString = CommonFunctions.DatabaseConnect("Syteline")
 
        Dim sqlCmd1, sqlCmd2 As New SqlCommand
        sqlCmd1.Connection = conSyteline
        sqlCmd2.Connection = conSyteline
        sqlCmd1.CommandText = "select job.item, jr.oper_num, jr.wc, jr.qty_received, isnull(il.loc, 0) as loc from [JobRoute] jr WITH (NOLOCK) inner join [job] with (NOLOCK) on jr.[job] = job.[job] and jr.[suffix] = job.[suffix] left join itemloc il with (NOLOCK) on il.item = job.item and rank = 1 and perm_flag = 1 where jr.[wc] = 'STK' and jr.[job] = @Job and jr.[suffix] = @Suffix and jr.[complete] = 0"
        sqlCmd2.CommandText = "select TOP 1 [Co_Num], isnull(([qty_ordered] - [qty_shipped]),0) as Qty, [Due_Date] from [coitem] with (NOLOCK) where [item] = @Item and [stat] <> 'C' and [qty_ordered] <> [qty_shipped] order by due_date asc "
 
        sqlCmd1.CommandType = CommandType.Text
        sqlCmd1.CommandType = CommandType.Text
        sqlCmd1.Parameters.AddWithValue("@Job", pJob)
        sqlCmd1.Parameters.AddWithValue("@Suffix", sSuffix)
        enJob.RC = 0
        Try
            conSyteline.Open()
            rdrJob = sqlCmd1.ExecuteReader()
            If rdrJob.HasRows() Then
                rdrJob.Read()
                enJob.Item = rdrJob.Item(0).ToString()
                enJob.OperNum = rdrJob.Item(1).ToString()
                enJob.Workcenter = rdrJob.Item(2).ToString()
                enJob.Qty = rdrJob.Item(3).ToString()
                enJob.Location = rdrJob.Item(4).ToString()
                rdrJob.Close()
                sqlCmd2.Parameters.AddWithValue("@Item", enJob.Item)
                rdrCo = sqlCmd2.ExecuteReader
                If rdrCo.HasRows Then
                    rdrCo.Read()
                    sTmp = rdrCo.Item(0).ToString.TrimStart("C")
                    enJob.CoNum = "C" + sTmp.TrimStart("0")
                    enJob.QtyDue = rdrCo.Item(1).ToString
                    enJob.DueDate = rdrCo.Item(2).ToString
                    rdrCo.Close()
                End If
            Else
                rdrJob.Close()
            End If
        Catch ex As Exception
            enJob.RC = 1
            enJob.ErrorMsg = ex.Message
        Finally
            conSyteline.Close()
        End Try
        Return enJob
    End Function

Open in new window

keith6194Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ChrisedeboCommented:
Hi there,

Looks like some good code you've written there. My first suggestion is to put that SQL on the server in the form of a Stored Procedure. Stored procedures are precompiled and run much more efficiently than bog standard SQL code thrown at the server. You will be able to pass in the parameters (eg @ajob) in a similar way. Also, indexes are a DBA's best friend, well, properly tuned ones at least. Check out the indexes used and create new ones to encompass the fields you are joining and fields with criteria dependencies. Don't create a clustered index on a field which is likely to be updated a lot.

I don't think you are doing anything wrong in code, with the exception of running SQL instead of stored Procedures. But a well tuned database could mean the difference between 10 seconds and 10miliseconds....

I think there will always be a slight delay the first time a webservice is called. But the above steps should help reduce the time considerably.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
keith6194Author Commented:
Chrisedebo,

Thanks,   I did change them to stored procedures,  this had a minimal performance boost which is helpful,   however the first call still takes 9seconds to load and then it is < 1sec on each subsequent calls.  As far as indexes,  I have no control over since it's a third party applicatiion,  this doesn't seem to be an issue though since subsequent calls (different information passed so no cache is being used) are fine.     I probablly need to look into my call to the webservice or some settings on sql server or iis to solve this issue.   Thanks for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.