Re: Aggregate string concatenation efficiency problem

On Jan 30, 1:16 pm, Bruce <deluxeinformat...@xxxxxxxxx> wrote:
On Jan 30, 2:35 pm, JohnH <JohnHarri...@xxxxxxxxx> wrote:



On Jan 30, 12:29 pm, Rich P <rpng...@xxxxxxx> wrote:

Well, lets review what is going on to make sure I am straight on what is
happening:

1) The query runs fast in the Query Grid
2) The query runs slow in a Make Table query

If this is the case, then try this: make a copy of your query but
remove column5. Now run a make table query on this copy query. If the
make table query runs fast without column5, then we have narrowed it
down that column5 is the trouble maker.

If this is the case, then try creating a test query that only retrieves
the same data column5 retrieves. The problem may be in how you retrieve
the data for column5.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Rich,

Exactly. Well now at least we're finally on the same page. Column5
has been the culprit from the beginning, that's what I was pointing
out in my first post. Column5 runs a function to calculate its value,
and that function is a sort of pseudo-aggregate function.

I did a make-table without Column5 just in case I was insane and to
make things totally clear, and as expected, it ran fine.

If this is the case, then try creating a test query that only retrieves
the same data column5 retrieves. The problem may be in how you retrieve
the data for column5.

I don't know what you mean here. I can create a query with only
'Column5' but I don't know why you are suggesting I do that.- Hide quoted text -

- Show quoted text -

The issue appears to be that every time you reference rs!Column5, the
fConcatChild() function has to be reevaluated, and as written, this
function is going to be slow. You can check this behavior by setting
a breakpoint in the fConcatChild. You should notice that when your
code hits the 'for n-1 to columncount' loop that it will stop in the
fConcatChild() function when n = 5. That being said, you can probably
generate some significant performance gains by rewriting the
fConcatChild function to use a static database variable. E.g. instead
of

dim dbs as database

use:

static dbs as database
if dbs is nothing then set dbs = currentdb

At the very least, consider replacing currentdb with dbengine(0)(0)
which incurs much less overhead.

HTH,
Bruce

Thank you Bruce. I took your advice and changed the database object
to a static variable. Unfortunately the performance gain was
negligible.
.

Relevant Pages