Performance problem. Caused by compacting?

[jal]

I'm using C#.Net to populate an .MDB database, and invoke the DAO 3.6 library from C# to compact the file nightly.

I'm now having a performance issue, and I'm wondering if the nightly compacting is what triggered it. (I can't remember when I first began the nightly compacting).

I've deleted the .MDB file and recreated it from scratch (again with nightly compacting) but the performance issue remains.

Queries that used to average 10 seconds are now taking from 100 to 400 seconds, which is totally unacceptable.

So my question is, does compacting ruin the indexes? If so, what is the recommended code for rebuilding the indexes after each compact?

 

[DCrake]

Set the properties of the mdb to Compact on Close nd simply close the md to let Access perform the compact.

 

[DJkarl]

Make sure you are disposing of Access objects correctly, do you have any hidden instances of MSACCESS.EXE running, if so your program may not be letting go and over time this slows down the system.

 

[jal]

Thanks guys, but I'm not using Access. I'm using VB.Net (well, C#.Net actually). I'm not running MsAccess.Exe. I'm simply using the OledDB provider to populate an .MDB file by running INSERT commands.
I currently have ten of these files. (2 for each year starting from 2006). With 9 of the ten, searches are fast, about 1 second per DB, with the result that a SELECT query (a query which does a full search on all the databases) only takes about 10 seconds total when things are running normally.
But right now it's the 10th DB that's killing me. The search is taking over 100 seconds for this DB alone.
On hand I always keep an empty copy of the original empty DB, called BlankDB.MDB. So today I tried inserting all the records from the bad DB into an empty copy. The code I used was basically this
INSERT INTO Customers SELECT * FROM Customers IN 'C:\badDatabaser.mdb'

Well, here's the actual C# code:
foreach(string table in tablesInBlankDB )
{
if (table.StartsWith("MSYS")) continue;
if (!tablesInCorruptDb.Contains(table)) continue;
oQuery.Run("INSERT INTO " + table + " SELECT * FROM " + table + " IN '" + DB.Path + "'", cnMain, null);
}

But the new copy is just as slow. Shouldn't this code have caused a complete index-rebuild in the destination DB?

So here again I'm still stuck with 9 fast DBs and one slow one - and all ten of them originated from the same empty DB called BlankDB.MDB.

I guess the only thing I can think of try next is to abstain from compacting as much as possible (though some of these DBs have ten million records).

 

[DJkarl]

In your OP you said you were using the DAO 3.6 lib, in your follow up you say you are using the OleDb provider, which is it?

How is your oQuery object declared?

 

[jal]

In your OP you said you were using the DAO 3.6 lib, in your follow up you say you are using the OleDb provider, which is it?

How is your oQuery object declared?

Both. I use OleDB to insert records.

Oledb cannot compact, as far as I know. So I use DAO for that.


private bool funcCompact(string pathToDB, bool silently)
{
object MissingVal = System.Reflection.Missing.Value;
if (!File.Exists(pathToDB)) return false;
string dir = Path.GetDirectoryName(pathToDB);
//Compact to the following destination
string pathToDestDB = dir + "\\" + Path.GetFileNameWithoutExtension(pathToDB)
+ "-Compacted.mdb";
if (File.Exists(pathToDestDB)) File.Delete(pathToDestDB);
closeConnection(cnMain);
Trace.WriteLine("Attempting to compact at " + DateTime.Now.ToString());
object[] args = new object[] { pathToDB, pathToDestDB, MissingVal, MissingVal, MissingVal };
try
{
System.Type typeEngine = System.Type.GetTypeFromProgID("DAO.DBengine.36");
object engine = System.Activator.CreateInstance(typeEngine);
typeEngine.InvokeMember("CompactDatabase", BindingFlags.InvokeMethod, null, engine, args);
}
catch (Exception ex)
{
string warn = "WARNING: Failed to compact the DB. If this problem continues contact the programmer. The error was: " + ex.Message;
Trace.WriteLine(warn);
if (!silently) M = new Msg(warn, Colors.Red);
return false;
}
File.Copy(pathToDestDB, pathToDB, true);
File.Delete(pathToDestDB);
subDisplayFreeSpace();
return true;
}

 

[jal]

How is your oQuery object declared?

The class oQuery is a fairly long piece of code that adds parameters automatically and, for ease of debugging, reports any discrepancies (for instance if I added too many or too few parameters). This class is well-tested, though (I use it in other programs).

Here's why I think the problem lies in the compacting. Friday I ran code to continue populating the two DBS for the year 2010. The first DB completed population and was compacted. The second almost completed population and then got disconnected. This DB is still running fast. The compacted one is running slow.

The problem could be that this is a threaded application - DAO probably doesn't much like threading. This is likely the source of the corruption (I only thought of this a few minutes ago).

 

[jal]

I deleted the DB last night and then repopulated a new copy of BlankDB.mdb. I did not compact. It's running fast again. This is a pretty strong confirmation of where the problem is - the last two times I rebuilt this DB with compacting it ended up slow, but now without compacting it's fast again.

So the key question is whether I can get away with avoiding compacts as much as possible from now on...

 

[ghudson]

I suspect your coded method to compact the database is the problem. Do you have the same slowness problem if you use the Compact On Close option set within the database?

Compacting is only needed if you add/delete a lot of records.

 

[jal]

I suspect your coded method to compact the database is the problem. Do you have the same slowness problem if you use the Compact On Close option set within the database?

Compacting is only needed if you add/delete a lot of records.

The coding as such is probably fine, since all the DBs underwent compaction successfully at least once. But recently I started compacting nightly and one of the DBs became slow after compaction. I recently tried
rebuilding it and then compacting from the Access interface (just for kicks) but with the same result (i.e. slowness). So I guess it's not a "threading" issue either - there is apparently a flaw somewhere in the Jet compaction engine.

I'm going to try to get away without compacting. If this doesn't work, I'll post back to this thread.

Thanks everyone.

 

[The_Doc_Man]

Basically, as we would say in South Louisiana, you are barking up the wrong tree.

Compacting a database correctly should always improve matters. When Access does the compacting, the indexes normally get rebuilt correctly. I am less than perfectly comfortable with performing a DAO compaction without Access surrounding that environment.

If you have at least one copy of Access handy, take one of those databases and do three experiments. Make THREE COPIES of the DB

First, run some long-running query against an uncompacted DB. Time it.

Next, compact a different copy (of the same starting point) using Access. Run your query. Time it.

Now, compact a third copy (of the same starting point) using your C and DAO calls. Run your query. Time it.

I'm not going to say that Access is perfect in what it does. (Anyone who knows me would probably fall down ROTFL at that thought.) But your description does not make sense unless the Compact operation is failing for some reason in the C/DAO case.

Now, here's another thought. Not trying to make work for you, but I've seen this next suggestion work for really big databases. Before you compact, strip the indexes. Compact. Now re-establish the indexes. If the table in question is big enough, this might help.

 

[jal]

Basically, as we would say in South Louisiana, you are barking up the wrong tree.

Compacting a database correctly should always improve matters. When Access does the compacting, the indexes normally get rebuilt correctly. I am less than perfectly comfortable with performing a DAO compaction without Access surrounding that environment.

If you have at least one copy of Access handy, take one of those databases and do three experiments. Make THREE COPIES of the DB

First, run some long-running query against an uncompacted DB. Time it.

Next, compact a different copy (of the same starting point) using Access. Run your query. Time it.

Now, compact a third copy (of the same starting point) using your C and DAO calls. Run your query. Time it.

I'm not going to say that Access is perfect in what it does. (Anyone who knows me would probably fall down ROTFL at that thought.) But your description does not make sense unless the Compact operation is failing for some reason in the C/DAO case.

Now, here's another thought. Not trying to make work for you, but I've seen this next suggestion work for really big databases. Before you compact, strip the indexes. Compact. Now re-establish the indexes. If the table in question is big enough, this might help.

Well, you're right (as usual). Turns out I was indeed barking up the wrong tree. At first I thought I was right because the DB was running faster without compacting - but then, after last night's update (i.e. more INSERTs), it got slow again. About a half-hour ago I stumbled across a performance tip on a website, "For fields longer than a few chars, set Unicode Compression to true." So I just rebuilt the DB a few minutes ago (I copied all the data into a new BlankDB.mdb with Unicode Compression set to true).

It's running fast again, so maybe that was the problem all along. The weird thing is that the other 9 databases (some of which are larger than this one) have been running quite fast even with Unicode Compression set to false.

Well, I'm keeping my fingers crossed...

Thanks again, everyone !!!

 

[The_Doc_Man]

It may be less that you have unicode compression enabled or disabled than that you had mixed unicode / non-unicode situations. After all, character codes are just character codes. They compare equal (in the current mode) or not. But when Access first has to decide whether some element IS or IS NOT unicode before it can do the comparison, and perhaps then has to convert modes, that takes a LOT longer. The nine databases that are running faster must be uniform with respect to unicode/non-unicode (whether or not they were compressed with/without unicode compression enabled.)

 

[jal]

It may be less that you have unicode compression enabled or disabled than that you had mixed unicode / non-unicode situations. After all, character codes are just character codes. They compare equal (in the current mode) or not. But when Access first has to decide whether some element IS or IS NOT unicode before it can do the comparison, and perhaps then has to convert modes, that takes a LOT longer. The nine databases that are running faster must be uniform with respect to unicode/non-unicode (whether or not they were compressed with/without unicode compression enabled.)

Not sure I follow you. The structure of the DBs is the same accross all ten. I only designed one DB, an empty one which I called BlankDB.mdb. Then each time I needed to create a database, I simply copied BlankDb.mdb to the destination folder and then populated it. Therefore all ten DBs were uniform with respect to how unicode was set up.

All ten databases were running fast until recently when the tenth one suddenly began running slow. Nonetheless I sure hope that Unicode was somehow the problem here, because if this problem recurs, I can't think of any other solutions at the moment. I'm keeping my fingers crossed.

 

[jal]

Well, today it was slow again, so I took another look at your post.

Now, here's another thought. Not trying to make work for you, but I've seen this next suggestion work for really big databases. Before you compact, strip the indexes. Compact. Now re-establish the indexes. If the table in question is big enough, this might help.

I just tried it and it worked. I'm going to start doing this regularly, hopefully I'll get consistent performance. Thanks !!!

 

[jal]

BTW, there's another performance question in my mind. As stated, the search takes about one second per database when run from C#.Net using the .Net OleDB provider. However, if I run the same query from Access SQL view, it takes about 10 seconds for one database. This is not a problem for me, as I am using .Net rather than Access. I'm just curious about it - and this happens even if I first save the query and then double-click it from the Objects pane. Anyone know why?

One thing I haven't tried - running the query from an Access VBA module or form. I wonder if that would be any faster? Maybe I'll try that this week, just for kicks.

 

[jal]

BTW, there's another performance question in my mind. As stated, the search takes about one second per database when run from C#.Net using the .Net OleDB provider. However, if I run the same query from Access SQL view, it takes about 10 seconds for one database. This is not a problem for me, as I am using .Net rather than Access. I'm just curious about it - and this happens even if I first save the query and then double-click it from the Objects pane. Anyone know why?

One thing I haven't tried - running the query from an Access VBA module or form. I wonder if that would be any faster? Maybe I'll try that this week, just for kicks.

Well I did run that experiment - turns out the query runs fast from Access VBA even though it is slow from the object pane.


But my real issue on this thread was running it from .Net - which has been running fast since I started using DocMan's suggestion. This whole week I've been inserting thousands of records daily with no slowdowns at all.

Thanks again DocMan !!!

 

[jal]

Well, I hate to say this, but it turns out I didn't get consistent results over the long haul. On many, many days, the database turned out to be slow depite the daily routine of dropping the indexes, compacting, and then restoring the indexes.

Today I found another solution which I expect to be successful over the long haul - I noticed that if I only select from the large table (6 million records) the query runs fast. Normally I do the select as part of a join to the small table - but this two-table join has been slow (although it was slow for only one of the ten identical databases, incomprehensibly).

So today I decided to defer the join. I selected records from the large table into a temp table, and then joined the temp table to the small table. The result was BLAZINGLY fast (in fact this is the fastest performance I've seen for ANY of the databases at any time).

Just for kicks, I also tried yet another method, very similar, which turned out almost as fast as the temp table. It's similar to the temp table in the sense of creating a virtual table somewhat like the temp table, and then joins the small table to the virtual table, something like this:

Select * from
(
SELECT * from LargeTable WHERE Acct = @Acct
) as LT
INNER JOIN SmallTable as ST
ON ST.FileID = LT.FileID

 

[twoplustwo]

Glad you sorted it Jal.

I do something similar when I need to run analysis on a subset of a lot of records. Just make sure you compact at the end