Solved

Program is slow using ADO.Net

Posted on 2004-08-02
15
294 Views
Last Modified: 2011-10-03
I am creating a program using VB.Net and an MS Access database.  I was originally using ADO because I was familar with it but now I would like to use ADO.Net.  To see how it worked I created the OleDbConnection and OleDbDataApter by using the DataForm Wizard.  That all worked fine, but the program is very slow at loading the dataset when I try to debug the program.  The database I am working with has over 12,000 records in it and I will be adding another database later to the project which will have at least double the records.  Is the amount of records in the database what is causing the program to load the dataset so slowly and is there a way to make it quicker?
0
Comment
Question by:amw22
  • 8
  • 6
15 Comments
 
LVL 14

Expert Comment

by:ptakja
ID: 11695837
Your issue could be related to the OLEDB connection & adapter.  Have you tried the ODBC adapter & connection?  That one is more efficient than OLEDB.

Also, define "very slow".  What kind of response time are you expecting?
0
 

Author Comment

by:amw22
ID: 11696143
Very slow means a takes about 20 seconds from the time the "Load" button is pushed until the data shows up in the text boxes.  Also, I have just tried the ODBC connection but I have not been able to get it to work.
0
 
LVL 14

Expert Comment

by:ptakja
ID: 11696183
Are you querying the database for all 12000 records?
0
 

Author Comment

by:amw22
ID: 11696230
I am querying the database for all 12000 records.  
0
 
LVL 14

Expert Comment

by:ptakja
ID: 11696341
This has been discussed before on this thread.  See it this helps you out:

http://www.experts-exchange.com/Databases/MS_Access/Q_20264154.html
0
 

Author Comment

by:amw22
ID: 11696426
I haven't had any problems with the speed using DAO and ADO.  I only ran into the problem when beginning to use ADO.Net.  Is ADO.Net not good to use when retrieving a large amound of data from a MS Access database?
0
 
LVL 14

Expert Comment

by:ptakja
ID: 11696529
Can you post some code so we can see what you are doing?
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:amw22
ID: 11697066
I haven't written any of the code myself.  I used the DataForm Wizard to create all of it because I was just trying to see how ADO.Net worked.  I have now gone through that code and deleted the Update, Insert, and Deleted functions in wrote because I don't need those and that cut the loading time down to about 10 seconds which is much better but still is not as fast as when I was using ADO.  I'm not sure which code you would like to see.  Here is some of the code that was written by the wizard in the Initialize function.

 <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.OleDbSelectCommand1 = New System.Data.OleDb.OleDbCommand
        Me.OleDbConnection1 = New System.Data.OleDb.OleDbConnection
        Me.OleDbDataAdapter1 = New System.Data.OleDb.OleDbDataAdapter
        Me.objmain = New WindowsApplication6.main

'OleDbSelectCommand1
        '
        Me.OleDbSelectCommand1.CommandText = "SELECT  DATA, DE, DOTCODE, DOTTITLE, STRENGTH, SVP FROM XWALKV43II"
        Me.OleDbSelectCommand1.Connection = Me.OleDbConnection1
        '
       
        'OleDbConnection1
        '
        Me.OleDbConnection1.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
        "ocking Mode=1;Data Source=""C:\Documents and Settings\HP Authorized Custom\My Doc" & _
        "uments\JobCad.Net\Good_Job_Cad\Xwalk.mdb"";Jet OLEDB:Engine Type=5;Jet OLEDB:Glob" & _
        "al Bulk Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System datab" & _
        "ase=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Mode=S" & _
        "hare Deny None;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Local" & _
        "e on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;" & _
        "Jet OLEDB:Encrypt Database=False"
        '
        'OleDbDataAdapter1
        '
       
        Me.OleDbDataAdapter1.SelectCommand = Me.OleDbSelectCommand1
        Me.OleDbDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "XWALKV43II", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("AC", "AC"), New System.Data.Common.DataColumnMapping("AT1", "AT1"), New System.Data.Common.DataColumnMapping("BA", "BA"), New System.Data.Common.DataColumnMapping("C", "C"), New System.Data.Common.DataColumnMapping("CENS90CD", "CENS90CD"), New System.Data.Common.DataColumnMapping("CENS90TL", "CENS90TL"), New System.Data.Common.DataColumnMapping("CL", "CL"), New System.Data.Common.DataColumnMapping("CO", "CO"), New System.Data.Common.DataColumnMapping("COLLCODE", "COLLCODE"), New System.Data.Common.DataColumnMapping("CR", "CR"), New System.Data.Common.DataColumnMapping("CV", "CV"), New System.Data.Common.DataColumnMapping("CW", "CW"), New System.Data.Common.DataColumnMapping("DATA", "DATA"), New System.Data.Common.DataColumnMapping("DE", "DE"), New System.Data.Common.DataColumnMapping("DOTCODE", "DOTCODE"), New System.Data.Common.DataColumnMapping("DOTTITLE", "DOTTITLE"), New System.Data.Common.DataColumnMapping("E1", "E1"), New System.Data.Common.DataColumnMapping("EL", "EL"), New System.Data.Common.DataColumnMapping("EX", "EX"), New System.Data.Common.DataColumnMapping("F", "F"), New System.Data.Common.DataColumnMapping("FA", "FA"), New System.Data.Common.DataColumnMapping("FE", "FE"), New System.Data.Common.DataColumnMapping("FI", "FI"), New System.Data.Common.DataColumnMapping("FV", "FV"), New System.Data.Common.DataColumnMapping("G", "G"), New System.Data.Common.DataColumnMapping("GEDL", "GEDL"), New System.Data.Common.DataColumnMapping("GEDM", "GEDM"), New System.Data.Common.DataColumnMapping("GEDR", "GEDR"), New System.Data.Common.DataColumnMapping("HA", "HA"), New System.Data.Common.DataColumnMapping("HE", "HE"), New System.Data.Common.DataColumnMapping("HI", "HI"), New System.Data.Common.DataColumnMapping("HO", "HO"), New System.Data.Common.DataColumnMapping("INDTTL", "INDTTL"), New System.Data.Common.DataColumnMapping("K", "K"), New System.Data.Common.DataColumnMapping("KN", "KN"), New System.Data.Common.DataColumnMapping("M", "M"), New System.Data.Common.DataColumnMapping("MPSMS1", "MPSMS1"), New System.Data.Common.DataColumnMapping("MPSMS2", "MPSMS2"), New System.Data.Common.DataColumnMapping("MPSMS3", "MPSMS3"), New System.Data.Common.DataColumnMapping("MV", "MV"), New System.Data.Common.DataColumnMapping("N", "N"), New System.Data.Common.DataColumnMapping("NE", "NE"), New System.Data.Common.DataColumnMapping("P", "P"), New System.Data.Common.DataColumnMapping("PEOPLE", "PEOPLE"), New System.Data.Common.DataColumnMapping("Q", "Q"), New System.Data.Common.DataColumnMapping("RA", "RA"), New System.Data.Common.DataColumnMapping("RE", "RE"), New System.Data.Common.DataColumnMapping("RELATED", "RELATED"), New System.Data.Common.DataColumnMapping("ROLLUP", "ROLLUP"), New System.Data.Common.DataColumnMapping("S1", "S1"), New System.Data.Common.DataColumnMapping("ST", "ST"), New System.Data.Common.DataColumnMapping("STRENGTH", "STRENGTH"), New System.Data.Common.DataColumnMapping("SURMATCD", "SURMATCD"), New System.Data.Common.DataColumnMapping("SVP", "SVP"), New System.Data.Common.DataColumnMapping("TA", "TA"), New System.Data.Common.DataColumnMapping("TEMPA", "TEMPA"), New System.Data.Common.DataColumnMapping("TEMPD", "TEMPD"), New System.Data.Common.DataColumnMapping("TEMPE", "TEMPE"), New System.Data.Common.DataColumnMapping("TEMPI", "TEMPI"), New System.Data.Common.DataColumnMapping("TEMPJ", "TEMPJ"), New System.Data.Common.DataColumnMapping("TEMPP", "TEMPP"), New System.Data.Common.DataColumnMapping("TEMPR", "TEMPR"), New System.Data.Common.DataColumnMapping("TEMPS", "TEMPS"), New System.Data.Common.DataColumnMapping("TEMPT", "TEMPT"), New System.Data.Common.DataColumnMapping("TEMPU", "TEMPU"), New System.Data.Common.DataColumnMapping("TEMPV", "TEMPV"), New System.Data.Common.DataColumnMapping("THINGS", "THINGS"), New System.Data.Common.DataColumnMapping("TS", "TS"), New System.Data.Common.DataColumnMapping("TX", "TX"), New System.Data.Common.DataColumnMapping("V", "V"), New System.Data.Common.DataColumnMapping("VI", "VI"), New System.Data.Common.DataColumnMapping("WE", "WE"), New System.Data.Common.DataColumnMapping("WORKFLD1", "WORKFLD1"), New System.Data.Common.DataColumnMapping("WORKFLD2", "WORKFLD2"), New System.Data.Common.DataColumnMapping("WORKFLD3", "WORKFLD3"), New System.Data.Common.DataColumnMapping("WT", "WT")})})
        '
        'objmain
        '
        Me.objmain.DataSetName = "main"
        Me.objmain.Locale = New System.Globalization.CultureInfo("en-US")
end sub
0
 
LVL 14

Expert Comment

by:ptakja
ID: 11697170
How about where you load your dataset?  Typically, when you create with the wizard you need to add 1 line of code to fill your dataset...something like this:

Me.OleDbDataAdapter1.Fill(MyDataSet)

I would try deleteing all the tableMappings the wizard generated.

If you're up for it, try creating establishing the connection & filling the dataset by writing your own code.  You could try something like the following:

Dim Conn As New OleDbConnection
Conn.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
        "ocking Mode=1;Data Source=""C:\Documents and Settings\HP Authorized Custom\My Doc" & _
        "uments\JobCad.Net\Good_Job_Cad\Xwalk.mdb"";Jet OLEDB:Engine Type=5;Jet OLEDB:Glob" & _
        "al Bulk Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System datab" & _
        "ase=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Mode=S" & _
        "hare Deny None;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Local" & _
        "e on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;" & _
        "Jet OLEDB:Encrypt Database=False"

Dim Adapter As New OleDbDataAdapter("SELECT * FROM XWALKV43II", Conn)
Dim ds As New DataSet

Call Adapter.Fill(ds)




0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11697958
try compacting and repairing your database from the tools menu in Access.

Regards,

Aeros
0
 

Author Comment

by:amw22
ID: 11698537

This is the code created to load the dataset

Dim objDataSetTemp As WindowsApplication6.main
        objDataSetTemp = New WindowsApplication6.main
        Try
            'Attempt to fill the temporary dataset.
            Me.FillDataSet(objDataSetTemp)
        Catch eFillDataSet As System.Exception
            'Add your error handling code here.
            Throw eFillDataSet
        End Try
        Try
            'Empty the old records from the dataset.
            objmain.Clear()
            'Merge the records into the main dataset.
            objmain.Merge(objDataSetTemp)
         Catch eLoadMerge As System.Exception
            'Add your error handling code here.
            Throw eLoadMerge
        End Try

And this is the code to fill the dataset
Public Sub FillDataSet(ByVal dataSet As WindowsApplication6.main)
        dataSet.EnforceConstraints = False
        Try
            'Open the connection.
            Me.OleDbConnection1.Open()
            'Attempt to fill the dataset through the OleDbDataAdapter1.
            Me.OleDbDataAdapter1.Fill(dataSet)
        Catch fillException As System.Exception
            'Add your error handling code here.
            Throw fillException
        Finally
            'Turn constraint checking back on.
            dataSet.EnforceConstraints = True
            'Close the connection whether or not the exception was thrown.
            Me.OleDbConnection1.Close()
        End Try
    End Sub

I will try to delete some of the table mappings but I will need most of those fields later when I get this figured out.  I was just testing by using a few of the fields on the forms.  I will also try to code the connection and and fill the dataset myself when I get a chance.  I really appreciate all the help you are giving me on this.
       
0
 
LVL 14

Expert Comment

by:ptakja
ID: 11699884
Wait a sec.... what is WindowsApplication6.Main???

Dim objDataSetTemp As WindowsApplication6.main
        objDataSetTemp = New WindowsApplication6.main

That should be like this:

Dim objDataSetTemp As DataSet
        objDataSetTemp = New DataSet
0
 
LVL 14

Expert Comment

by:ptakja
ID: 11699892
The dataset, when properly filled will have the field names as part of the dataset.  You can refer to the fields in a table by name or index as:

objDataSet.Tables(0).Rows(1)("Field").Value
0
 

Author Comment

by:amw22
ID: 11707598
Here is the code I am working with now.  I stopped trying to use what the DataForm wizard created and I would like to code it all my self.  Here is what I am doing.

Dim Conn As New OleDb.OleDbConnection
        Conn.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
        "ocking Mode=1;Data Source=""C:\Documents and Settings\HP Authorized Custom\My Doc" & _
        "uments\JobCad.Net\Good_Job_Cad\Xwalk.mdb"";Jet OLEDB:Engine Type=5;Jet OLEDB:Glob" & _
        "al Bulk Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System datab" & _
        "ase=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Mode=S" & _
        "hare Deny None;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Local" & _
        "e on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;" & _
        "Jet OLEDB:Encrypt Database=False"

        Dim Adapter As New OleDb.OleDbDataAdapter("SELECT * FROM XWALKV43II", Conn)

        Adapter.Fill(ds, "XWALKV43II")
         TextBox1.DataBindings.Add("Text", ds, "XWALKV43II.DOTCODE")
        TextBox5.DataBindings.Add("Text", ds, "XWALKV43II.DOTTITLE")
        TextBox2.DataBindings.Add("Text", ds, "XWALKV43II.GEDR")
        TextBox3.DataBindings.Add("Text", ds, "XWALKV43II.GEDL")
        TextBox4.DataBindings.Add("Text", ds, "XWALKV43II.GEDM")
        Assign_Current_Labels()

Right now it takes about 7 seconds for the data set to load.  Do you think that is the fastest I will be able to get it to go with the amount of records in the database which is over 12,000?  Also it seems to work fine without using the datarow and table stuff.  What is the purpose of using that and how does that work?
0
 
LVL 14

Accepted Solution

by:
ptakja earned 250 total points
ID: 11707872
The dataset is ideally set up to bind to a grid control.  Since you are doing the databinding to textboxes, the only way to do it is the way you have it set up.

From the looks of your code, you can't get more efficient.  That is the minimum amount of code you need to accomplish what you are doing.

When do you execute this code?  Something you may want to consider is loading your dataset when the app is initializing.  For example, pop up a spash screen that the user can look at while your dataset is loading up.  Then when that is complete, do the databinding operations.

Another option is to pull the records in a separate thread.  That won't make the fetch any faster, but it will allow your main UI to remain responsive to the user while the fetch is happening.

The percieved sluggish response could be a limitation of the OLEDB adapter and Access.  The SQL Server adapter is optimized for speed for connections to SQL Server 2000 databases.  Likewise for the Oracle adapter.

Hope that helps.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 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

19 Experts available now in Live!

Get 1:1 Help Now