Stored procedure from VB.Net takes ages

[sh211618]

Hello,

I have a stored procedure with a number of input parameters on SQL Server 2000. This stored procedure is accessing quite large tables and returning a dataset.

When I run this procedure on via SQL server Management Studio, the procedure is finished within a second, either with or without results.
When I run the same procedure from a VB.Net application, it takes ages to get the response back, if I don't change the commandtimeout property I even get timeout errors!
I've checked on SQL server, but nothing strange about locking or something like that can be seen.

Does anyone knows what is causing this delay and what can be done about it?

Thanks,
André

 

[chrissie1]

A large dataset?

The correct type of parameters?

The correct client?

Code?

Christiaan Baes
Belgium

My Blog

 

[sh211618]

Hi Christiaan,

The resulting dataset is not very large.
The coding for the stored procedure is;

CREATE PROCEDURE [dbo].[ulv_bcs_phtrc_GetEl4Documents]
(
	@cmpcode varchar(2000),
	@el1from varchar(72),
	@el1to varchar(72),
	@year smallint,
	@periodto smallint,
	@debitcreditind smallint
)
AS
BEGIN
	SET NOCOUNT ON;

	SELECT h.cmpcode, l.el4, sum(l.valuehome) as sumvalue
	FROM csdd001a.dbo.oas_dochead h, csdd001a.dbo.oas_docline l 
	where 
	h.cmpcode = l.cmpcode
    and h.doccode = l.doccode
	and h.docnum = l.docnum
    and h.cmpcode in ( select value from dbo.ulv_general_Split(@cmpcode,',') )
	and l.el1 between @el1from and @el1to
	and h.yr = @year  
    and h.period between 1 and @periodto
	and l.deb_cred_ind = @debitcreditind
    GROUP BY h.cmpcode, l.el4
end

I've created a XSD dataset calling to the procedure and a separate business logic layer calling the XSD dataset.
The coding for the business logic layer is:

Imports BCSPHTRC.CODAIntTableAdapters

Public Class El4DocumentsBLL
    Private _el As ErrorLog
    Private _ad As GetEl4DocumentsTableAdapter = Nothing
    Protected ReadOnly Property Adapter() As GetEl4DocumentsTableAdapter
        Get
            If _ad Is Nothing Then
                _ad = New GetEl4DocumentsTableAdapter()
            End If
            Return _ad
        End Get
    End Property

    ''' <summary>
    ''' Error log 
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Property el() As ErrorLog
        Get
            el = _el
        End Get
        Set(ByVal value As ErrorLog)
            _el = value
        End Set
    End Property

    Public Function GetEl4Documents(ByVal cmpcode As String, _
                                 ByVal el1from As String, _
                                 ByVal el1to As String, _
                                 ByVal Year As Short, _
                                 ByVal periodto As Short, _
                                 ByVal debitcreditind As Short _
                                 ) As CODAInt.GetEl4DocumentsDataTable
        Try
            Return Adapter.GetBalanceElement4(cmpcode, el1from, el1to, Year, periodto, debitcreditind)
        Catch ex As Exception
            If Not _el Is Nothing Then
                Dim sb As New System.Text.StringBuilder
                sb.AppendLine("Error while retrieving documents for company " & cmpcode)
                sb.AppendLine("Element 1 from: " & el1from & "Element 1 to: " & el1to & " Year: " & Year.ToString & " Period: " & periodto)
                sb.AppendLine("Error: " & ex.ToString)
                _el.AddLogEntry("El4Documents:GetEl4Documents", sb.ToString, 99)
            End If
            Return Nothing
        End Try

    End Function

End Class

As told the strange thing is that calling the procedure from SQL server management studio is giving results in a snap of the eyes and using the same parameters in for instance the preview function of the XSD dataset gives a timeout error.

 

[chrissie1]

So what happens if you fill a normal datatable with a simple adapter and command.

and what about this part?

Return Adapter.GetBalanceElement4(cmpcode, el1from, el1to, Year, periodto, debitcreditind)

but first of all fill a normal datatable so we can rule out the getting of the data. Strongly typed datatables are hard to debug.



Christiaan Baes
Belgium

My Blog

 

[sh211618]

I used the following code to fill a normal datatable:

Imports System.Data.SqlClient

Module MainModule

    Sub Main()
        Dim connectionString As String = _
          "Data Source=cgtsapp20320\cgtssql20008;Initial Catalog=CSDD001e;Integrated Security=True"

        Dim connection As SqlConnection = New SqlConnection(connectionString)
        connection.Open()
        Try
            Console.WriteLine(Now)
            Dim command As SqlCommand = New SqlCommand("ulv_bcs_phtrc_getel4documents", connection)
            command.Parameters.AddWithValue("@cmpcode", "RUNV")
            command.Parameters.AddWithValue("@el1from", "6222000")
            command.Parameters.AddWithValue("@el1to", "6222000")
            command.Parameters.AddWithValue("@year", 2007)
            command.Parameters.AddWithValue("@periodto", 12)
            command.Parameters.AddWithValue("@debitcreditind", 160)
            command.CommandType = CommandType.StoredProcedure
            command.CommandTimeout = 0

            Dim adapter As SqlDataAdapter = New SqlDataAdapter(Command)

            Dim table As DataTable = New DataTable
            adapter.Fill(table)
            Console.WriteLine(table.Rows.Count)
            Console.WriteLine(Now)
            Console.Read()
        Finally
            connection.Close()
        End Try

    End Sub

End Module
{/code]

It took 50 seconds before the stored procedure was finished. Using the same selections in SQL Server management studio give a result within 1 second.

 

[sh211618]

I am totally puzzled.

I changed the stored procedure a bit (replaced the line "and h.cmpcode in ( select value from dbo.ulv_general_Split(@cmpcode,',') )" with "h.cmpcode in ('RUNV','LUCR')" and everything worked fine and quick.

I changed it back to the original line and recreated the procedure and what a surprise: the procedure is fast as lightning!

So it looks like recreating the coding solved the problem.

Christiaan thanks for your time and help!

Regards,
André

 

[chrissie1]

Mmm, strange. Last attempt to find the slowest part.

Try a datareader. and loop throught the results. How many rows do you get?

And I suppose you run SSMS on your local machine.

BTW why is CMPCode varchar(2000)??? While you the code seems a lot shorter then that.

Christiaan Baes
Belgium

My Blog

 

[sh211618]

Christiaan,

Probably my last post crossed yours
I started trying to find the slowest part and thought it was in the split function, but after the recreation everything look ok....

Yes, I'm indeed running SSMS locally.

The cmpcode (which means company code) is varchar(2000) as we can have over 150 companies in the list. Most companies have a length of 4 postions, so I can decrease it to 1000, but I thought better safe than sorry.

 

[chrissie1]

I think you should go to the sql-server programming forum and ask what is the best method it could be that you have a parametersniffing problem (I think) but I'm not an expert.

Christiaan Baes
Belgium

My Blog

 

[SqueakinSweep]

I have encountered this problem too before, but only on SS2000, and I think it had something to do with the fact that VS thinks the SP has to be compiled at run time.

By editing it, and basically changing nothing at all, you can resolve the problem. (SS then recompiles the SP). I have yet to notice this behaviour with SS2005.



Sweep
...if it works, you know the rest..
Always remember that Google is your friend

 

[karsol7]

I am having the same type of problem that André had, but am using SQL Server 2005 and VB.Net 2008. When I run my stored procedure from SQL Server Management Studio, it takes just a second or two. But when I try to run it from my VB.Net code, it times out after 5 minutes. I have had the same thing happen as André; sometimes when I change the stored procedure, all the sudden, it runs quick. But then I make another change, and it gets real slow again. If someone could point me to any settings having to do with how SS recompiles (or fails to do so), and how I can make sure it always compiles the code whenever I change it, that would be a big help.

Karl