How to reduce Data Adapter's fill time(VB.Net )

Hi guys,

I'm executing one SP(oracle 8i)...from vb.net code it's taking around 96 seconds to fill the datatable....while if i execute this SP from back end it takes 4 seconds(we can assume that queries inside this sp are optimized).......code to execute SP and fill datatable is like this.
Please let me know how can i reduce the fill time of datatable.

(Also let me know if you need more inputs from me)


        Public Function ExecuteStoredProcedure(ByVal strProcName As String, ByVal strInputParam As String, ByVal strOutputParam As String) As DataTable
            Dim oConn As OracleClient.OracleConnection = New OracleClient.OracleConnection()
            Dim oCmd As New OracleClient.OracleCommand()
            Dim trans As OracleTransaction
            Dim arr As Array
            Dim intCounter As Integer
            Dim strDataType As String
            Dim strParm As String
            Dim strValue As String
            Dim parmOracle As OracleParameter
            Dim da As OracleDataAdapter
            Dim dt As New DataTable()
            Try
                oConn.ConnectionString = oracleconstr 'Connection string
                oConn.Open()
                oCmd.Connection = oConn
                oCmd.CommandText = strProcName
                oCmd.CommandType = CommandType.StoredProcedure
                'Prepare Input Parameters
                If strInputParam.Length > 0 Then
                    If InStr(strInputParam, "|") > 0 Then
                        arr = Split(strInputParam, "|")
                        For intCounter = arr.GetLowerBound(0) To arr.GetUpperBound(0) Step 3
                            strParm = arr(intCounter)
                            strDataType = arr(intCounter + 1)
                            strValue = arr(intCounter + 2)
                            If strParm = "p_sortcolumn" AndAlso strValue = "Priority" Then
                                strValue = "PRIORIT_INDEX_CN"
                            End If
                            Select Case LCase(strDataType)
                                Case "number"
                                    strDataType = OracleClient.OracleType.Int32
                                Case "varchar"
                                    strDataType = OracleClient.OracleType.VarChar
                                Case "cursor"
                                    strDataType = OracleClient.OracleType.Cursor
                            End Select
                            parmOracle = New OracleParameter()
                            parmOracle.ParameterName = strParm
                            parmOracle.Value = strValue
                            parmOracle.OracleType = strDataType
                            parmOracle.Direction = ParameterDirection.Input
                            oCmd.Parameters.Add(parmOracle)
                            parmOracle = Nothing
                        Next
                    End If
                End If
                'Prepare Output Parameters
                If strOutputParam.Length > 0 Then
                    If InStr(strOutputParam, "|") > 0 Then
                        arr = Split(strOutputParam, "|")
                        For intCounter = arr.GetLowerBound(0) To arr.GetUpperBound(0) Step 2
                            strParm = arr(intCounter)
                            strDataType = arr(intCounter + 1)
                            Select Case LCase(strDataType)
                                Case "number"
                                    strDataType = OracleClient.OracleType.Int32
                                Case "varchar"
                                    strDataType = OracleClient.OracleType.VarChar
                                Case "cursor"
                                    strDataType = OracleClient.OracleType.Cursor
                            End Select
                            parmOracle = New OracleParameter()
                            parmOracle.ParameterName = strParm
                            parmOracle.OracleType = strDataType
                            parmOracle.Direction = ParameterDirection.Output
                            oCmd.Parameters.Add(parmOracle)
                            parmOracle = Nothing
                        Next
                    End If
                End If

                trans = oConn.BeginTransaction(IsolationLevel.Serializable)
                oCmd.Transaction = trans
                da = New OracleDataAdapter(oCmd)
                da.Fill(dt)
                trans.Commit()
                ExecuteStoredProcedure = dt
            Catch ex As Exception
                trans.Rollback()
                ExecuteStoredProcedure = Nothing
                Log(" Message from Execute SP: " & ex.Message.ToString() & " Source: " & ex.Source.ToString)
                Throw ex
            Finally
                oConn.Close()
                oConn = Nothing
                oCmd = Nothing
            End Try
        End Function

LVL 1
rushtosachinAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

timmyt851Commented:
If the dataset is large, then you can rewrite your SP to only pass the current page of data. Your SP will return the entire result list even if your datagrid is set to display a small number of records. This will cause unnecessary page load times because all of the data is sent to the web server. To avoid this, check out this article: http://www.dotnetjunkies.com/Article/975BE770-E5DC-4610-870B-A82BDB9B8845.dcik .

I have successfully used this on a couple datagrids and it is very fast even when there are thousands of records. I made some modifications to it to support sorting, let me know if you need the additional code.

TIM

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
rushtosachinAuthor Commented:
well...i m not using any datagrid here.....my pupose is to get all the data in one go.....after that i display records on filtering the data views.......it is becoz i did not want to hit the database again.......my client is happy with one time delay.....but navigation in between records shd be fast.........i'm using VB.net(it's client server app....using remoting here).........
timmyt851Commented:
I once thought that loading all the data into memory and minimizing database access would be better but after using this method I see that multiple db queries is actually quite fast and in the case of large numbers of records is actually much faster than the original method. You don't necessarily have to use a datagrid to use this method, all the SP needs are PageSize and CurrentPage parameters to know which records to return. A datagrid is just an easy way of performing this lookup. In my real world scenario I actually returned several queries for the dropdowns...outputting multiple select statements in the SP created multiple tables in my dataset, which I simply access by number. I know this is a little vague, let me know if you're interested and I'll give you an example. I was skeptical at first too but it ended up working really well.

TIM
hellswraithCommented:
If you are ok taking the hit the first time, you can cache the dataset in the cache object.  I don't think this is the best option though, but if you are foot firm with retrieving all of them, this would be your best bet because the results will stay in the server memory instead of pulling them from the DB every time you want to sort.  

I do think though, that you should take the advice from the others above, and use paging sp's to return only the data that is to be displayed.  A user can only look at so much data at one time anyway.
Bob LearnedCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: timmyt851 {http:#9735400}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

TheLearnedOne
EE Cleanup Volunteer
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.