Run-time error '3183': (1 Viewer)

[frozbie]

Hi all,
Valuable comments again. Thank you all!

I've set the Union query to Union all; have added indexes on joining columns and have timed the whole process through several loops to get an idea of performance; also have checked at what point the database is increasing in size.

Interestingly, the front end is now reaching the 2Gb size limit, still processing the data quite happily, but then stops growing in size. I also tried running as a compiled accde and the same thing happened. However, when I tried running compact and repair on the front end, I received corruption warnings advising the VBA project had been lost. Glad I had been taking regular backups before running the tests!

There is one query which takes 10 seconds to run, but this is not affecting the size of the database. The other eight queries used in the process all run/load in 1/2 seconds or less.

I've narrowed down the point at which the database increases in size to one query (which I had changed to union all). This query DOES contain a MAX aggregate in addition to the union and has several nested sub queries and DateAdd functions.

It's not quite clear if this is the problem as even when stepping through the code slowly, the database does not increase in size when this query is initially run. But the code then steps through the resulting recordset and at different points during that sub process, the database consistently increases in size by 200kb per employee. Running in batches of 200 employees I then see an overall increase of 40,000Kb per batch. The amount of employees I need to cycle through, this easily breaches the 2Gb limit.

So, I think next step will be refactoring the process to see if I can prepare the data prior to processing to simplify the query that is run.

 

[frozbie]

Doc Man,

Thanks for sharing that scenario. I don't think it is applicable here though as I'm simply passing values to an INSERT statement:

cdb.Execute "INSERT INTO Table ( EMPID, BEGINDATE, DAY_NO, " _
  & "HOURS, STARTTIME, ENDTIME, DateOfSchedule) " _
  & "VALUES ('" & rst!EMPID & "', '" & rst!BEGINDATE & "', '" _
  & rst!DAY_NO & "', '" & rst!HOURS & "', '" & rst!StartTime & "', '" _
  & rstSDU!EndTime & "', '" & dtDateBeingBuilt & "');"

I'm fairly sure it is down to the speed this particular process is running at, trying to write to a backend database. Even though I'm testing on my C drive, I think the process sometimes runs faster than the table/db lock can be released. The error only occurs once every several thousand write operations.

 

[frozbie]

Hi Dave,

I'm importing anywhere between 200Mb and 1Gb data into the initial backend db for each month prior to processing. As an employee schedule may be set months/years previously, or may run through the month being processed, I decided I needed to store the max schedule from previous months and include this with any current month schedules to ensure there are no gaps at the beginning of the month.

After three months, I'm now starting the final process with a backend db that is just under 700Mb (the first month seemed to be the worst with 1.2Gb data)

I then move the schedules in batches of 200 employees (I've played around with different batch numbers - smaller seems better) to a separate table to run the process.

 

[frozbie]

Hi

I was going to write: "The ballooning database / database bloat may be resolved sufficiently.

Thanks to Pat Hartman for her reply on this topic: http://www.access-programmers.co.uk/forums/showthread.php?t=48759

I had replaced standard

With rst
     .AddNew
     !EMPID = rst2!EMPID
     ...
     .Update
End With

with

cdb.Execute "INSERT INTO table ( EMPID, ...) " _
     & "VALUES (value 1, value 2, ... )"

In order to try and speed up the process and prevent the backend database ballooning in size.

However, I ran a comparison test over 200 iterations for each type of write operation and observed that rst.update increased the front end by 16Kb, while the cdb.Execute "INSERT INTO... increased the front end size by 720Kb.

A massive difference when I am writing approximately 25,000 X 50 records.

I've gone back to using rst.update and while I'm only a fifth of the way into the process, the database size increase is on track to be less than 300,000Kb. (From a starting position of 1,560Kb)

Still a bloat, but one that can be managed by compact and repair without danger of corruption.

I am now wondering if my use of multiple tables for pre-processing, processing and completed processing can be refactored to further reduce bloat, though this may be outside the scope of my current project."

But...

While the front end bloat is manageable, I'm now seeing the backend database (being used to store the final output and the recipient of the rst.update) ballooning in size.

So, executing SQL bloats the front end, executing rst.update bloats the backend...

I can handle this by checking database size during the process and ending the process if size reaches a trigger point, then getting the user to manually compact and repair. Not ideal, but then Access probably wasn't designed to process high volumes of data requiring freed memory to be re-used.

Thanks all for your thoughts and advice.

Mark

 

[gemma-the-husky]

if you take this input statement

cdb.Execute "INSERT INTO Table ( EMPID, BEGINDATE, DAY_NO, " _
& "HOURS, STARTTIME, ENDTIME, DateOfSchedule) " _
& "VALUES ('" & rst!EMPID & "', '" & rst!BEGINDATE & "', '" _
& rst!DAY_NO & "', '" & rst!HOURS & "', '" & rst!StartTime & "', '" _
& rstSDU!EndTime & "', '" & dtDateBeingBuilt & "');"

then it looks like your date and times are being stored as text, otherwise this would not work.

Not that this is necessarily a problem, but a text date will use a lot more space than a native true date/time. (8 bytes for date plus time)

I think it is just virtually impossible for us to pinpoint exactly what is going on and why you are running out of space.


by the way, Pat Hartman is a her, not a him!

 

[frozbie]

Hi Dave,

Thanks for pointing out my error re Pat. I guess I know more Patricks than Patricias... I've been told my own choice of name also belongs to a Mexican dog... So much for my attempt to be unique...

The dates are stored in the table as dates. I have taken to passing in date parameters in text format 'dd/mmm/yyyy' to avoid US/UK format causing any swapping days and months around.

I did the full calculation on how much page space is potentially being generated:
25,000 employees times an average of 50 schedules per employee time 4Kb page size = 5 Gb

If Access actually managed free space and allowed it to be re-used, this would not be an issue. As it is, the recommendation I will make to the client is to move to using SQL Server or Oracle as a backend.

 

[The_Doc_Man]

There is a "gotcha" in what you just said. ORACLE doesn't clean up automatically either. Don't know about SQL Server, but for ORACLE, if you want to reclaim space, you have to do something almost as drastic as what you do for Access. ORACLE will expand its container files when you expand your usage. The database will still bloat. I'm not sure but I don't think that the space no longer in use is actually reclaimed without a command to compress each container file. We used to have a big ORACLE DB (Enterprise Server product) and it would become a serious pig if you let it run for a couple of weeks.

I regret to say that the only way you can reclaim table space in a running DB is if you never delete it in the first place. Instead, you mark it as "used" or "not used" and then have a routine to find the next free "slot" and fill it in. I.e. "roll your own" memory manager in order to keep track of what you aren't using at the moment.

 

[gemma-the-husky]

Hi Dave,

Thanks for pointing out my error re Pat. I guess I know more Patricks than Patricias... I've been told my own choice of name also belongs to a Mexican dog... So much for my attempt to be unique...

The dates are stored in the table as dates. I have taken to passing in date parameters in text format 'dd/mmm/yyyy' to avoid US/UK format causing any swapping days and months around.

I did the full calculation on how much page space is potentially being generated:
25,000 employees times an average of 50 schedules per employee time 4Kb page size = 5 Gb

If Access actually managed free space and allowed it to be re-used, this would not be an issue. As it is, the recommendation I will make to the client is to move to using SQL Server or Oracle as a backend.

surely you cannot update/insert a datetime into a table with a text format? That would give you an error, wouldn't it?

 

[frozbie]

Re: Run-time error '3183': Fixed

Thanks again to The Doc Man, Dave and SpikePL who posted advice on this thread. Several things have made a huge difference to the process including refactoring queries to reduce use of sub queries and reducing use of multiple tables to store temporary data.

The final big improvement in the process was down to advice from Allen Browne who advises that the Access Name AutoCorrect feature should be turned off:

http://allenbrowne.com/bug-03.html

When I did this on the front and back end databases, the ballooning of the front end was reduced by a factor of ten - from an estimated 5Gb to 500 Mb. Still not pretty, but well within Access's 2Gb limit. Also, the process speed increased by a factor of three and the other error I was getting (3073 - operation must use an updateable query) was also minimized.

Mark