• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • Last Modified:

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.  
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
            aJob = Split(pJob, "-")
            pJob = aJob(0)
            If aJob.Length > 1 Then
                sSuffix = aJob(1)
                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
            rdrJob = sqlCmd1.ExecuteReader()
            If rdrJob.HasRows() Then
                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()
                sqlCmd2.Parameters.AddWithValue("@Item", enJob.Item)
                rdrCo = sqlCmd2.ExecuteReader
                If rdrCo.HasRows Then
                    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
                End If
            End If
        Catch ex As Exception
            enJob.RC = 1
            enJob.ErrorMsg = ex.Message
        End Try
        Return enJob
    End Function

Open in new window

1 Solution
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.
keith6194Author Commented:

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now