ASP.NET slower than ASP

Question

User1570828477 posted

Hello.

As the subject say, I'm working on an ASP.NET project.

The DBMS assigned is Oracle 10g.

There is a previous version or project made in ASP.

So the problem is: In my ASP.NET project the queries are executed so much slower than in ASP. The queries are the same in both cases, even in the ASP.NET project the queries are in stored procedures they are slower and I really don't know why, because it is supposed that SP's are faster than SQL Injection. 

Thank you in advance :)

Answer 1

User-451260051 posted

Can you post the code you are using?

Answer 2

User-1694870838 posted

Hi,

we can't guess what issue in your project without you codes,you can create index to improve your query speed if it is a big number of datas in your table.

Best Regards,

Damon

Answer 3

User269602965 posted

Use OracleDataAccess (ODP.NET) and not ASP.NET default System.Data.OracleClient

ODP.NET is much faster and System.Data.OracleClient is deprecated (no longer supported) by Microsoft.

 

Answer 4

User1570828477 posted

I'm using the Oracle.DataAccess provided by Oracle.

And the code in .Net:

This is the method that I use in the webform to call the method in my MethodsLib (which has all the methods that calls the stored procedures)

Private Sub Consult()
        Dim access As New MethodsLib.RestrictedAccess()
        Try
            _data = acess.consultexped(_ced, _exn, _idJ, _idM, _mat, _idTip)
        Catch ex As MethodsLib.BdException
            RegEr.RegError(ex)
            Response.Redirect("../Error.aspx", True)
        End Try
    End Sub

And this is the method in MethodsLib:

Public Function consultexped(ByVal ced As String, ByVal exn As String, ByVal idJ As Integer, ByVal idM As Integer, ByVal mat As String, ByVal idTip As Integer) As DataTable
        Dim lista As New DataTable()
        Dim connection As New OracleConnection(ConnectionString)
        Dim command As New OracleCommand()
        Dim dataAdapter As New OracleDataAdapter()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "SP_CONSULTEXPED"
        command.Parameters.Add("PIN_CED", OracleDbType.Int32, ParameterDirection.Input).Value = ced
        command.Parameters.Add("PIN_EXPN", OracleDbType.Varchar2, ParameterDirection.Input).Value = exn
        command.Parameters.Add("PIN_IDJ", OracleDbType.Int32, ParameterDirection.Input).Value = idJ
        command.Parameters.Add("PIN_IDM", OracleDbType.Int32, ParameterDirection.Input).Value = idM
        command.Parameters.Add("PIN_MAT", OracleDbType.Varchar2, ParameterDirection.Input).Value = ma
        command.Parameters.Add("PIN_IDTIP", OracleDbType.Int32, ParameterDirection.Input).Value = idTip
        command.Parameters.Add("POUT_LISTA", OracleDbType.RefCursor, ParameterDirection.Output)
        dataAdapter.SelectCommand = command
        Try
            connection.Open()
            dataAdapter.Fill(lista)
        Catch ex As OracleException
            RegistroDeErrores.RegistrarError(ex)
            Throw New BdException("Error al realizar consulta a la base de datos.", ex)
        Finally
            connection.Close()
            connection.Dispose()
            command.Dispose()
            dataAdapter.Dispose()
        End Try
        Return lista
    End Function

And I don't know why, in this SP I have a select, defining exactly the fields in contrast with the ASP classic that the query is a select *. 

I'd been debugging and I found that when it executes the dataAdapter.fill(lista) takes a looooot of time (sometimes minutes for a query that it is executed in seconds in ASP classic).

I executed the SP in the Oracle SQLDeveloper Client and did not take so much time like when it is called by .NET. Sorry for my english.

Answer 5

User269602965 posted

I find using an Oracle stored procedure in PL/SQL is slower than a direct use of SQL statement in server side application .NET code.

Especially is the PL/SQL statement processes business rules, or worse, has LOOPs (PL/SQL loops are sooooo slllloooowwww)

I process my business rules in .NET (fast) and use SQL statements to fill application objects.

You can gain additional speed avantage using SQL Oracle Analytics and 11g PIVOT clause.