querydef.SQL property - db bloat

[beatrice1981]

Hi,

Please help

I'm working on a code to loop through the dao.recordset and use single record values as a criteria to set querydef.SQL property then run saved PT query, insert PT query result
into a table and MoveNext.
Code actually works but I can't make my peace with huge db bloat.
I've run several tests and finally deleted most of the code to find the place where IT happens, I hope someone can explain it.

The code below is actually useless for me right now, but this part alone is responsible for ~90% of dbsize after execution.


Dim db1 As DAO.Database
Dim qdef1 As DAO.QueryDef
Dim RST1 As DAO.Recordset

Set db1 = CurrentDb
Set qdef1 = db1.QueryDefs("QDEF01") ‘saved querydef (PT)
Set RST1 = db1.OpenRecordset("SELECT DISTINCT a, b, c , d FROM TAB1")

Do Until RST1.EOF = True

qdef1.SQL = "select field1, field2, field3, field4, field5, field6 FROM SomeTab " '<- evil, evil Originally there's a whole WHERE clause, but that's irrelevant at the moment

qdef1.Close

RST1.MoveNext
Loop

RST1.Close


Size of db before running (110k recordset) ~40MB, after = ~ 450MB!!!!

I've came up with an idea to replace my original qdef1.SQL string with a simple 'SELECT * from SomeTab' and that was it! Result = dbsize still ~40MB.
It seems like a string itself makes a huge difference, but is it even possible? I need to put a full SQL string into a querydef to make it work but how should I proceed from this point
Am I missing something here? Any ideas will be appreciated.

 

[Ranman256]

Ok, you are altering the Querydef a hundred times and saving. Why?
If this a select query,what is its function? You don't need to save the query to run it.
Esp if the data doesn't go anywhere.

Saving this a hundred times could make the db bigger.
Instead, just execute the SQL, no Querydef needed.

 

[jdraw]

What exactly are you trying to do in plain English --no jargon and no Access terms?

 

[beatrice1981]

It's a pass-through query. I need some data from ODBC database server for every record in my recordset.
I'm altering querydef in every iteration because i use current record values to change criteria in WHERE clause and then insert the single query result into Access table.
The question is - why saving this string as querydef.SQL increases size of my frontend:
qdef1.SQL = "select field1, field2, field3, field4, field5, field6 FROM SomeTab"
and this one doesn't:
qdef1.SQL= "SELECT * from SomeTab"

I can't use the latter as i need some calculated fields in result of a query, as well as WHERE clause.

 

[jdraw]

How about an example --real data.

I think you could give us a description in plain English with recordset or ODBC etc.
How would you tell an 8 year old or an eighty year old granny?

 

[sneuberg]

I tried to replicate your problem and my database only grew to 114 MB (120,098,816 bytes). Probably because I didn't have the where clause.

This doesn't surprise me. Same thing happens if you add 145 K of records and delete them. You don't get the space back until you run compact and repair.

What I don't understand it why you are creating a querydef for each query. Isn't there some way of using parameters in your query? Could you share the rest of this query (WHERE clause) with us ?

 

[beatrice1981]

Thank you all for your time.

@ jdraw - hope you don't mind, but to an 8-year-old it would go like this

Let's say I have a list of 50 POKEMON NAMES in access table. There's an external datasource with all pokemon data.
My task is to create and populate another Access table that contains:

Field 1 - NAME from 1st Access table
Field 2 - data of pokemon with that name (incl. weight of this pokemon divided by 2) from external source

@ sneuberg

How I understand it - I'm not creating a querydef for each pass-through query, just have a permanent p-t querydef saved in my database and I’m simply changing its SQL string for every record in recordset.
I thought that it’s the best way as opposed to creating and deleting querydef every time.
I’ve created a function with record values as parameters, but function only creates a string and the result is the same btw – 450MB so I’ve omitted it. What function did you have in mind, what parameters, actions? I can’t image a function that would not require using querydef for a pass-through query in this procedure L


To make sure that is not just my current db issue - I’ve just created an empty Access database, imported 100 000 records of random data into a table, created ‘empty’ querydef for a PASS-THROUGH query and ran this code below. No insert actions, just simple changing the sql strings for a querydef. DB size before = 4,5MB, after = 416MB. Then I compacted db and used the same code but with a simple “select * from SomeTab" instead of more complex SQL statement. Result after = 6MB!.
As you can see – code below just replaces one string with another, nothing else. Why the bloat then when string is more complex (or just longer)? L

Sub justtocheck()

Dim db1 As DAO.Database
Dim qdef1 As DAO.QueryDef
Dim RST1 As DAO.Recordset

Set db1 = CurrentDb
Set qdef1 = db1.QueryDefs("QDEF01") 'permanent querydef with ODBC connection string saved id my db
Set RST1 = db1.OpenRecordset("SELECT DISTINCT a, b, c, d FROM TAB1")

Do Until RST1.EOF = True

qdef1.SQL = "select field1, field2, sum(coalesce(field5, 0)-coalesce(field6)) FROM SomeTab " & _
"WHERE field1='" & RST1!a & "' and field2='" & RST1!b & "' group by 1,2,3,4"

'qdef1.SQL = "SELECT * FROM SomeTab"

‘insert statement here

qdef1.Close

RST1.MoveNext
Loop

RST1.Close

End Sub



I will have to give up and try different approach soon, but I’m wondering what is the reason in this case. :banghead:

Good day to all!

 

[The_Doc_Man]

The reason for BLOAT is because you actually DO create new copies of the query but don't delete the older ones. See, the way that Access typically does things like this is, it builds the new string, then changes the pointer from the old string to the new string. But it can't DELETE the old string (that is no longer referenced by the SQL pointer) until you do a compact & repair. If you run that look 100 times, you get 100 values for the string referenced by the SQL property. But you get 99 dangling prior values.

I'm a little light on pass-through queries, but I think it is possible to use DAO databases and the .Execute method even if you wanted a pass-through query. However, it should ALSO be possible to try something like this:

qdef1.SQL = "select field1, field2, sum(coalesce(field5, 0)-coalesce(field6)) FROM SomeTab group by 1,2,3,4"

Now open a recordset to qdef1, which you open in a way that defines it as a pass-through, and then use

RST1.FindFirst "[Field1]= " & somevalue & " AND [Field2] = " & someothervalue

I.e. take the where clause out of the query

Alternatively, do a JOIN between TAB1 and SomeTab ON SomeTab.Field1 = TAB1.a and SomeTab.Field2 = TAB1.b, and then enumerate THAT recordset.

 

[sneuberg]

@ sneuberg
What function did you have in mind, what parameters, actions? I can’t image a function that would not require using querydef for a pass-through query in this procedure

Sorry. I've never used a pass through query. I should have read a little about pass through queries before suggesting using parameters. I now see that the way you are doing this is the way Microsoft suggests do it here. This really stinks.

Doc's idea of a join looks interesting but if TAB1 is an Access table in your front end I wonder if that's possible. If that's the case can you move TAB1 to the server?

 

[Frothingslosh]

What about setting up a view, importing it as a linked table, and then running a local parameter query against the view? That would allow you to avoid the bloat, anyway.

Edit: I'm about to grab some lunch, so more when I get back, but looking at what you're doing, you may simply need to write a query with a double join between SomeTab and Tab1.

 

[beatrice1981]

Thank you all for looking into this. I'll check your suggestions first thing in the morning.

@ The_Doc_Man - how would you explain no bloat for "select * from SomeTab"? I just don't get it

ps. "select field1, field2, sum(coalesce(field5, 0)-coalesce(field6)) FROM SomeTab group by 1,2,3,4" - my mistake, should be "group by 1,2"

 

[The_Doc_Man]

how would you explain no bloat for "select * from SomeTab"? I just don't get it

It isn't that the query is as noted in your quoted string. It is what you do with it later that makes a difference. When you replace "SELECT field1, field2, yada-yada-yada...." with another "SELECT field1, field2, different-yada-yada-yada..." then the behavior is as I suggested.

If you don't replace your "SELECT * FROM SomeTab" inside the loop, you are not forcing a new string reference into the SQL pointer. OR... I've seen some oddball cases where I reloaded an SQL string with the same exact string in a .ROWSOURCE only to find that the previous contents including previous selections were still intact. Perhaps the code inside Access detects that the change really wasn't a change and doesn't bother.

I admit I'm at least partially guessing because let's face it, the innards of Access are still very much like a black box - with decent documentation about the ways to use it, but still a black box.