Slowing Data Access

[GrexD]

I'm in VS2005 accessing an Access 03 mdb file via OLEDb.

I have a series of 6 groups of tables that are structured the same but have slightly different names. In a loop I create a set of SQL statements and then use the Replace() function to modify the table names so I can run the same query on all six sets of tables. This is fairly common for me and I do this a lot in Access with Access VBA. No problems.

One of the things I need to do is create a make table. If I just let the code run freely in a loop, moving through all 6 sets of tables, I don't get the desired results. If I slow down the code with a break line it runs as expected, even if it is only paused for a second.

I guess I need to pause the program flow to give the make table query time to run. What would be the best way to do this?

Greg

 

[cjard]

I don't get the desired results

Please expand

 

[GrexD]

Well, the records are changing so I can never expect to get the same results. I do know that one of the groups of tables is a test database. I know that my query should return 2 records from that database everytime. No one is changing those records so I should always get them. Sometimes I do and some times I don't.

I can run the same query 5 times in a row over a 2 minute period and it doesn't always return the records from the test tables. The other tables I would expect to return different results at different times of the day, but even those should not change that much in such a brief period.

This problem started when I started doing the make table query. This table that is made from the MT query is used in the main query. I use ExecuteNonQuery to run the MT query. What I'm not doing at this point is checking the return value to find out how many rows were affected. I'm going to implement that today.

What seems to be happening is that the MT query is not fully executing before the next query that relies on it is run. That is my working theory. I had just assumed that the ExecuteNonQuery call wouldn't return until it had fulled executed.

 

[cjard]

It shouldnt return untill the query has finished, true.. There's something way way back in my memory that I too experienced this issue; access seemed to be lazy in carrying out DML operations - some internal write caching algorithm I think. I think it may have been solved by wrapping the DML up in a transaction, and committing the transaction.. If I can think of any relevant search keywords, i'll let you know

Other qualified smart people mention the write caching:

It's not connection pooling. It's JET. JET does not write to the disk file
until it's bored (idle). As long as there is activity (adding more rows,
selecting, changing), it does not flush its local cache to the disk. This
helps query performance. If you open a Transaction and perform the changes
it forces JET to write immediately, but then your interactive performance
goes into the gutter.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
William (Bill) Vaughn's Musings
Beta V Corporation Website
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.

from usenet: ConnectionState problem - managing connections to an Access database - .NET ASP


The best solution to your problem would actually be to use a proper database SQLServer Express is free..

 

[GrexD]

Swing and a miss. Unfortunately, transaction processing does not work...well, the transactions worked but it didn't change the results. I'm not even sure what the problem is at this point. The return value from the ExecuteNonQuery says it is affecting the correct number of rows. Still, the damn thing only works when I slow it down.

Very frustrating....

   Public Sub WriteToTrackingDB(ByVal sSQL As String)

        If sUserName = "" Or sPassword = "" Then
            MsgBox("Enter a user name, password first.", MsgBoxStyle.Information, "Need more information")
        Else
            Dim dbAuthFax As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
 DB_PATH & ";Persist Security Info=True;Password=" & sPassword & ";User 
ID=" & sUserName & ";Jet OLEDB:System database=H:\xxxxxx.mdw;Jet 
OLEDB:Database Password=" & sPassword)

            Try
                dbAuthFax.Open()
                Dim OleDBTrans As OleDbTransaction = dbAuthFax.BeginTransaction()
                Dim SQLInsert As New OleDbCommand(sSQL, dbAuthFax, OleDBTrans)

                SQLInsert.Transaction = OleDBTrans

                SQLInsert.Connection = dbAuthFax

                Dim k As Long = SQLInsert.ExecuteNonQuery()
                Debug.Print(k & " - " & sSQL)
                OleDBTrans.Commit()

            Catch ex As Exception
                MsgBox(Err.Number & " - " & ex.Message)
            End Try

            dbAuthFax.Close()

        End If
    End Sub

 

[GrexD]

I just noticed that the following is being written to the immediate window at some point when the queries are running. I'm not doing a Debug.Print so when is it being written and when isn't the Try/Catch catching it? The errors are somewhat vague, so who knows if this is the culprit. I output the SQL strings to the debug window and run them from with-in Access. Everything works fine. If I slow the program down with MsgBox calls everything works fine.

A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll

 

[cjard]

I don't see any SELECT operations?

 

[cjard]

Re the errors, see the menu:

Debug >> Exceptions
Tick "Thrown" next to CLR exceptions


If you dont see the menu option, customize your menu or toolbar. Exceptions is in Debug group, or try the C# key combo: Ctrl+d, e

 

[GrexD]

I don't see any SELECT operations?

it is passed in ....

Public Sub WriteToTrackingDB(ByVal sSQL As String)


This has gotten to the point of being absurd. Even with a 6 second delay I sometimes have problems. The error is being thrown on the "ExecuteReader" call because it can't find the table. After it runs though, when I go to the MDB file, the table is there with the expected number of rows. if I run the make table query directly in the MDB file it runs in less 2 seconds. One some of the groups of tables it is almost instant. The MT returns 2 columns and so far, at the most it is 8 rows.

I'm getting to the point of hating OLEDB and VS2005. I've spent about 6 hours over the last 2 days on this and I think I'm going to need to scrap this whole process and start over. I'm going to need to go out to a 10 second delay to get it to work. With 6 groups of tables that is a minute of looking at an hour glass. That is unacceptable.

 

[GrexD]

So, after all of my bitching and moaning I went to lunch and came up with the solution.

I use one connection to run the make table query. After it has run I open a new connection for the datareader. This gives me a fresh look at the database with the new table. I close the datareader connection after each iteration of the loop and then open a new connection at each new start of the loop. This seems to give me a fresh look at the database every time. It takes about 3 or 4 seconds to run all queries on all 6 groups of tables.

Whew!

 

[cjard]

While it's probably not the first time I've said this, it maybe the first time I said it to you - if you'd done your data access the way Microsoft wanted you to, it probably wouldnt have happened (tableadapter code generated by MS's designer always opens and closes connections unless you open them before you begin)

Glad you got it working.. Sorry I didnt advocate copying Microsoft sooner; I will do in future
Too bad that open/close connection on Access has such a massive overhead. If youre really starting to hate Access and VS2005, I recommend replacing the faulty component with something better. i.e. ditch Access and use a real database. Jet has always been a toy-town database intended for use by those whose know it's awful quirks (e.g. every MS product, by MS, that uses it..)