Solved

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

Posted on 2003-11-12
6
976 Views
Last Modified: 2008-09-12
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

0
Comment
Question by:rushtosachin
6 Comments
 
LVL 4

Accepted Solution

by:
timmyt851 earned 125 total points
Comment Utility
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
0
 
LVL 1

Author Comment

by:rushtosachin
Comment Utility
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).........
0
 
LVL 4

Expert Comment

by:timmyt851
Comment Utility
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
0
 

Expert Comment

by:hellswraith
Comment Utility
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.
0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
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
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now