Solved

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

Posted on 2003-11-12
6
1,020 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 4

Accepted Solution

by:
timmyt851 earned 125 total points
ID: 9735400
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
ID: 9735940
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
ID: 9736050
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
ID: 9757273
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
ID: 10267803
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

752 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