Any way to narrow down bloat by looking at a hex dump?

  • Thread starter (Pete Cresswell)
  • Start date
P

(Pete Cresswell)

My app is going from about 5k to well over 100k in the course of a batch job.

None of the usual suspects seem tb behind it:
-------------------------------------------------
- No data at all in the app
- No temp tables in the app
- No links being created/modified during the process
- No other objects that I know of being created/modified during the process
- Every QueryDef/RecordSet .Open balanced out with .Close + Set = Nothing.
------------------------------------------------

I'm thinking that with a factor of over 20x, that the repetition should stand
out pretty clearly when looking at the app with a hex editor. Just don't know
what to look for and/or how to relate it to a possible cause.

Anybody?
 
L

Larry Daugherty

Hi Pete,

What does it do, what is the purpose of the application?

Does "Compact on Close" work?
 
A

Albert D. Kallal

Does the same happen when the application is a mde?

Also, if you are in fact *creating* queries in the querydef table, that
again would be a source of problems.

I would simply use in-line sql, or parameters and dump the idea of creating
queries.

So, check the above two issues....

Also, you don't mention what version, but the sp updates DID fix a number of
known serious bloat problems for some versions of ms-access (a2000 comes to
mind here).

So, in addition to the above two issues, have you installed the updates to
office, and also JET for your version of ms-access?
 
P

(Pete Cresswell)

Per Larry Daugherty:
What does it do, what is the purpose of the application?

Does "Compact on Close" work?
Click to expand...

It is used to compare various performance statistics of mutual funds.

Every fund/month/quarter has a return value.

For each one of those returns, among other things, we need to compute about 10
years worth of rolling annualized rates of return. Last time I put a counter
on a full-volume run, we did 1.2 million annualized ROR calcs - and that was
just quarterlies bc we hadn't found a source of monthlies yet.

Haven't tried "Compact on Close". Would expect it to work.... And because of my
deployment method, it would be simple to just tell my .BAT file to download a
fresh copy of the app each time it ran on a given PC..

But it seems to me like I'm doing something wrong....so I'd want to find the
root cause so I don't do it again.
 
P

(Pete Cresswell)

Per Albert D. Kallal:
Does the same happen when the application is a mde?
Click to expand...
Dunno. Sounds like I should give it a shot. If it didn't, what would that
suggest?
Also, if you are in fact *creating* queries in the querydef table, that
again would be a source of problems.
Click to expand...

I'm not 100% sure I'm on the same semantic square...but I would say not. I
have pre-existing parameter queries that I Set a local QueryDef to, feed it the
parameters, and the open a RecordSet. That local QueryDef gets .Closed and
Set=Nothing after each use.
I would simply use in-line sql, or parameters and dump the idea of creating
queries.
Click to expand...

I think that's next on my list tonite - only one or two queries involved SB a
low-cost option
So, check the above two issues....

Also, you don't mention what version, but the sp updates DID fix a number of
known serious bloat problems for some versions of ms-access (a2000 comes to
mind here).
Click to expand...
So, in addition to the above two issues, have you installed the updates to
office, and also JET for your version of ms-access?
Click to expand...

Happens at the client site, where I'm not sure what SP they're running - but
also on my own PC: "Microsoft Office-Access 2003 (1.5614.5606) Part of
Microsoft Office Professional Edition 2003".

OOPS! I don't see anything in the MS "About" dialog about any service pack.

So even if the raw SQL thing makes the problem go away, I'll install the latest
from my MSDN discs and revert to QueryDefs just to see...
 
P

(Pete Cresswell)

Per Albert D. Kallal:
I would simply use in-line sql, or parameters and dump the idea of creating
queries.
Click to expand...

Well, if nothing else, in-line SQL is faster.

25% faster than a parameterized QueryDef...or the .QueryDef is 35% slower,
depending on how you want to spin it.

I installed SP1. Usually checking for latest drivers/SPs is my first move in
something like this. I guess I just had it in the back of my mind that 2003 was
up-to-date. It wasn't....was sort of an afterthought install, since I've been
doing all work to-date under 2000.

I think SP1 cut down some on the bloating, but I'll never know exactly bc I
didn't write the right numbers down... But in the test I just ran - reverting to
the .QueryDef, the bloating was very obvious even with a limited-volume test
under SP1. OTOH there was relatively little bloat under SP1 with inline SQL.

I'm going back and replace multiple calls to CurrentDB() with a single routine
that does it once and then passes back a stored pointer just to see if that
helps any with the small remaining growth.

Bottom line: parameterized .QueryDefs look to be expensive - both in execution
time and in bloat. Who'd of thunk it? I always assumed they'd be faster -
compiled once and all that.... Come to think of it, maybe feeding the
parameters triggers a recompile....
 
A

Albert D. Kallal

Bottom line: parameterized .QueryDefs look to be expensive - both in
execution
time and in bloat. Who'd of thunk it? I always assumed they'd be
faster -
compiled once and all that.... Come to think of it, maybe feeding the
parameters triggers a recompile....
Click to expand...

Yes, the performance paper on JET actually confirms the above. The paper
claims that some queries gain up to 40% when you dump the parameters, and
using in-line sql. (and, you can even fetch the raw sql from a query..and
add the conditions yourself).


strMySql = currentdb.QueryDefs("myquery").sql

currentdb.Execute strMySql

The reason why the in-line sql runs faster is that computers can performance
billions of operations per second, and thus the query compile time is
insignificant here. What matters is that the JET query optimizers makes good
choices, and when you have NO parameters, it can do a better job, as it does
not have to make so many (poor) guesses when parameters are present. I have
many times also experienced this increase, and as mentioned the white paper
on JET confirms this.
 
L

Larry Daugherty

Compact on Close is an option you'd set in the application.

As I was browsing here a couple of hours ago I saw a response to a thread
speculating that the dynamic creation of queries might be causing some
bloat. I thought that was aimed at you but don't see it in this thread.

Sometimes I don't pursue root causes if there are workable solutions. If
the "Compact on Close" solution does it for you I'd leave it at that.
Otherwise, I'd just get a fresh copy of the app each time. One other thing.
If it doesn't bloat beyond 100k, that's a piddling size for an Access
database. I wouldn't worry about it at all. If it's 100Meg I might look at
the other solutions.

HTH
 
P

(Pete Cresswell)

Per Larry Daugherty:
As I was browsing here a couple of hours ago I saw a response to a thread
speculating that the dynamic creation of queries might be causing some
bloat. I thought that was aimed at you but don't see it in this thread.
Click to expand...

Probably Yours Truly in comp.databases.ms-access.

Been shopping around in my despiration. I owe David Fenton there an
acknowledgement that his suggestion was right on the money...

Just waiting until tomorrow when I'll have some more test time behind whatever I
say.
 
P

(Pete Cresswell)

Per (Pete Cresswell):
I think SP1 cut down some on the bloating, but I'll never know exactly bc I
didn't write the right numbers down...
Click to expand...
I'm going back and replace multiple calls to CurrentDB()
Click to expand...

Nope. Looked promising with just a few thou calcs, but now I'm at about
175,000 and the app has grown from about 6k to about 18k. Seems to go up in 8k
chunks... Damn, I *know* I've been here before with another app under A2k or
a97...- but with 4k chunks....just can't find/remember it.
 
T

Tony Toews

Albert D. Kallal said:
Yes, the performance paper on JET actually confirms the above. The paper
claims that some queries gain up to 40% when you dump the parameters, and
using in-line sql.
Click to expand...

Interesting. I never knew that. I've always created SELECT queries
and then using inline SQL for the WHERE clauses or the UPDATEs just
because it's more convenient.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
P

(Pete Cresswell)

Per Tony Toews:
Interesting. I never knew that. I've always created SELECT queries
and then using inline SQL for the WHERE clauses or the UPDATEs just
because it's more convenient.
Click to expand...

More convenient?

Geeze.. yer a better man than I....

Took me almost a half hour to cobble together that inline SQL and debug all my
little errors - like parenthesis and such.....(as opposed to about 20 seconds to
create the functional equivalent in the query builder UI.

Maybe it gets quicker with experience....
 
T

Tony Toews

(Pete Cresswell) said:
More convenient?

Geeze.. yer a better man than I....

Took me almost a half hour to cobble together that inline SQL and debug all my
little errors - like parenthesis and such.....(as opposed to about 20 seconds to
create the functional equivalent in the query builder UI.

Maybe it gets quicker with experience....
Click to expand...

No, no. I create the basic query with all the joins as a SELECT query
pulling in all the records and give it a name. Then, in VBA code, I
setup the query based on that query with the WHERE clause. Or the
Action query with the WHERE clause.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
P

(Pete Cresswell)

Per Tony Toews:
No, no. I create the basic query with all the joins as a SELECT query
pulling in all the records and give it a name. Then, in VBA code, I
setup the query based on that query with the WHERE clause. Or the
Action query with the WHERE clause.
Click to expand...

So, something like:
---------------------------------------------------
1) Create a query. e.g. qryHumongousQueryWithLotsOfJoins

2) In VBA code:

mySQL = "SELECT qryHumongousQueryWithLotsOfJoins.* WHERE FundID=" & myFundID
 
T

Tony Toews

(Pete Cresswell) said:
So, something like:
---------------------------------------------------
1) Create a query. e.g. qryHumongousQueryWithLotsOfJoins

2) In VBA code:

mySQL = "SELECT qryHumongousQueryWithLotsOfJoins.* WHERE FundID=" & myFundID
---------------------------------------------------
?

If so, I like it. Keeps the heavy lifting in named entities where I can keep
track of them more easily.
Click to expand...

Exactly.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
T

Tony Toews

(Pete Cresswell) said:
2) In VBA code:

mySQL = "SELECT qryHumongousQueryWithLotsOfJoins.* WHERE FundID=" & myFundID
Click to expand...

Actually I start with
"SELECT qryHumongousQueryWithLotsOfJoins.* FROM
qryHumongousQueryWithLotsOfJoins WHERE FundID=" & myFundID"
as that is exactly what the query builder creates. But I will then
remove the first "qryHumongousQueryWithLotsOfJoins." leaving just the
*.

So it looks more like
"SELECT * FROM qryHumongousQueryWithLotsOfJoins WHERE FundID=" &
myFundID".

The only time remove the query name from before the * doesn't work is
when doing delete or update queries which have multiple tables in
them.

Note that I find myself using INSERT query instead of recordset
inserts if I'm only adding one record at a time. Fewer lines of code.
If, however I'm adding them in a loop then I use recordset inserts as
I figure it's more efficient. Not that I've actually done timing
tests. <smile>

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
P

(Pete Cresswell)

Per Tony Toews:
So it looks more like
"SELECT * FROM qryHumongousQueryWithLotsOfJoins WHERE FundID=" &
myFundID".
Click to expand...
The only time remove the query name from before the * doesn't work is
when doing delete or update queries which have multiple tables in
them.
Click to expand...

I don't really understand the ins and outs of SQL coding - mainly I just
slavishly copy/modify what MS Access generates for me. Always bothered me what
it came up with stuff like
SELECT [tblWhatever].[NameFirst], [tblWhatever].[NameLast] FROM tblWhatever...

seems to me like with only one table and no goofey field names it sb

SELECT NameFirst, NameLast FROM tblWhatever....

But I never felt like I knew enough to mess with it.

Note that I find myself using INSERT query instead of recordset
inserts if I'm only adding one record at a time. Fewer lines of code.
If, however I'm adding them in a loop then I use recordset inserts as
I figure it's more efficient. Not that I've actually done timing
tests.
Click to expand...

I have and you are correct...and it's not just a *little* faster...it's a *lot*
faster.
 
T

Tony Toews

(Pete Cresswell) said:
Always bothered me what
it came up with stuff like
SELECT [tblWhatever].[NameFirst], [tblWhatever].[NameLast] FROM tblWhatever...

seems to me like with only one table and no goofey field names it sb

SELECT NameFirst, NameLast FROM tblWhatever....

But I never felt like I knew enough to mess with it.
Click to expand...

I always shorten those. Unless the field names are duplicate among
tables, which they never are due to my naming standards, this is
almost never a problem. There is one situation where I have to leave
the table name sin but I misremember what it is right now.
I have and you are correct...and it's not just a *little* faster...it's a *lot*
faster.
Click to expand...

Glad to hear my suspicions on this matter were correct. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Top