Aggregate string concatenation efficiency problem

I couldn't think of a good subject line for this one. I'll try to make
things succinct.

I have a "Quick Search" feature I've implemented that searches for
types of objects (Customers, Sales, etc) and returns a group of those
items that can be browsed through. I does this by grabbing a
predefined search query based on object type, running it, and
retreiving the data for the results listview from the query through
its aliases (AS Column1, Column2, etc)

The table relationship in focus is here:
Sales 1-->M ConsultantSales M<--1 Consultants

More than one Consultant can be listed on a Sale, so it is related as
such. When I run my query, I want each Sale to be returned as 1 row
with all data about Consultants present.

Since it is apparently impossible to create custom aggregate functions
(a string concatenation aggregate function, for instance), and I could
think of no other way to do this, I adapted Dev Ashish's code here:
http://www.mvps.org/access/modules/mdl0004.htm
in order to create a comma delimited string of Consultants for each
Sale.

Here is the final query I'm running:

SELECT Customers.CustomerID AS VBAObjectID, [Customers].[CustomerID] &
';' & [Sales].[SaleID] AS VBAOpenArgs, VehicleSales.VIN,
Customers.FullName AS Column1, Sales.SaleDate AS Column2,
VehicleSales.VehicleStockNumber AS Column3, [VehicleYear] & ' ' &
[VehicleMake] & ' ' & [VehicleModel] AS Column4, ...
/
fConcatChild("Consultants RIGHT JOIN ConsultantSales ON
Consultants.ConsultantID =
ConsultantSales.ConsultantID","SaleID","ConsultantFullName", "Long",
[Sales].[SaleID]) AS Column5
\
...., Format(Sales.SaleLastModified,'mm/dd/yyyy') AS Column6
FROM (Customers INNER JOIN Sales ON Customers.CustomerID =
Sales.CustomerID) INNER JOIN VehicleSales ON Sales.SaleID =
VehicleSales.SaleID
WHERE ((VehicleSales.VehicleStockNumber Like '*[Value]*') OR
(VehicleSales.VIN Like '*[Value]*'));

(fConcatChild is a function in Dev Ashish's code)


This query is applied to a recordset object in code. Here's the code:

Do Until rs.EOF
ItemTextDelimmed = vbNullString
For n = 1 To ColumnCount
ItemTextDelimmed = ItemTextDelimmed & Nz(rs("Column" & n)) & "@"
Next
ItemTextDelimmed = Left(ItemTextDelimmed, Len(ItemTextDelimmed) -
1)

'###These are objects of the cString class, a string builder class
for large strings
ItemTextArrayString.Append ItemTextDelimmed & "|"
OpenArgsArrayString.Append rs!VBAOpenArgs & "|"

rs.MoveNext
Loop


And now, with that preface, I can describe my problem. It is
intolerably slow. I'm ignorant as to the internals of Jet (well, dbs
period). What I observe is this: If I run the query, just run it,
it's quite fast. If I run it and walk through every row, it's fast.
(And my code, normally, is very fast). But as soon as I access the
data from Column5, the column in question, things slow way down. I
imagined that if the query ran and completed, all the data was
available at that moment, and no further time consuming db crunching
would be necessary, but that seems to be not the case.

Can someone help me understand why and maybe help me find my way
towards a solution? I've spent quite a bit of time on this and I feel
like I'm at a bit of an impasse.

Re: Aggregate string concatenation efficiency problem

>>
And now, with that preface, I can describe my problem. It is intolerably
slow.

Can someone help me understand why and maybe help me find my waytowards
a solution?
<<

Without knowing what "It" is this will be difficult to solve. But you
did mention that you are concatenating a large string from a Recordset
Object. If you have data stored in a table you are better off using the
table directly with your query.

What purpose does this large string serve? A shot in dark would suggest

Select * from your query Where _
someting In ('" & largeString & "'"

If this is close, how about

Select * from your query Where _
Someting In (Select fldX From tbl1 Where fldY = 'somethingelse'"

Or

Select t1.* From query t1
Where Exists (Select * From tbl1 t2 Where t2.fldx = t1.fldx)


Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Aggregate string concatenation efficiency problem

On Jan 30, 10:16 am, Rich P wrote:
> And now, with that preface, I can describe my problem. It is intolerably
> slow.
>
> Can someone help me understand why and maybe help me find my waytowards
> a solution?
> <<
>
> Without knowing what "It" is this will be difficult to solve. But you
> did mention that you are concatenating a large string from a Recordset
> Object. If you have data stored in a table you are better off using the
> table directly with your query.
>
> What purpose does this large string serve? A shot in dark would suggest
>
> Select * from your query Where _
> someting In ('" & largeString & "'"
>
> If this is close, how about
>
> Select * from your query Where _
> Someting In (Select fldX From tbl1 Where fldY = 'somethingelse'"
>
> Or
>
> Select t1.* From query t1
> Where Exists (Select * From tbl1 t2 Where t2.fldx = t1.fldx)
>
> Rich
>
> *** Sent via Developersdexhttp://www.developersdex.com***

Thank you for your reply Rich, but it doesn't address what I'm
inquiring about, and that's probably due to the difficulty I had
drawing it up and explaining the relevant information.

The details of my implementation (such as the large strings) are
incidental. They're merely boundary conditions that I included to
give an idea of the space my problem exists in and to eliminate
potential suggestions that wouldn't work for my particular needs.

My issue is the lethargy that the database runs into when, while
enumerating the recordset, I read the field from that query that was
populated through the fConcatChild function. My confusion lies in
why, internally, Access slows down so much when it tries to read that
field, even though the query, when run, seems to finish right away.
My request is that someone might help me understand the problem and
perhaps suggest a way through it.

Re: Aggregate string concatenation efficiency problem

On Jan 30, 10:24 am, JohnH wrote:
> On Jan 30, 10:16 am, Rich P wrote:
>
>
>
> > And now, with that preface, I can describe my problem. It is intolerably
> > slow.
>
> > Can someone help me understand why and maybe help me find my waytowards
> > a solution?
> > <<
>
> > Without knowing what "It" is this will be difficult to solve. But you
> > did mention that you are concatenating a large string from a Recordset
> > Object. If you have data stored in a table you are better off using the
> > table directly with your query.
>
> > What purpose does this large string serve? A shot in dark would suggest
>
> > Select * from your query Where _
> > someting In ('" & largeString & "'"
>
> > If this is close, how about
>
> > Select * from your query Where _
> > Someting In (Select fldX From tbl1 Where fldY = 'somethingelse'"
>
> > Or
>
> > Select t1.* From query t1
> > Where Exists (Select * From tbl1 t2 Where t2.fldx = t1.fldx)
>
> > Rich
>
> > *** Sent via Developersdexhttp://www.developersdex.com***
>
> Thank you for your reply Rich, but it doesn't address what I'm
> inquiring about, and that's probably due to the difficulty I had
> drawing it up and explaining the relevant information.
>
> The details of my implementation (such as the large strings) are
> incidental. They're merely boundary conditions that I included to
> give an idea of the space my problem exists in and to eliminate
> potential suggestions that wouldn't work for my particular needs.
>
> My issue is the lethargy that the database runs into when, while
> enumerating the recordset, I read the field from that query that was
> populated through the fConcatChild function. My confusion lies in
> why, internally, Access slows down so much when it tries to read that
> field, even though the query, when run, seems to finish right away.
> My request is that someone might help me understand the problem and
> perhaps suggest a way through it.

I just realized where a lot of the confusion may lie. My subject line
is
"Aggregate string concatenation efficiency problem." and then where I
show my code for looping through the recordset I perform a string
concatenation. By "string concatenation" I'm referring to the
fConcatChild function that creates a comma delimited string for all
subrecords (all Consultants for each Sale). Here's an example of a
returned fields from the query for one row:

VBAObjectID : 3541
VBAOpenArgs : 3541;7078
Column1 : Maynard, Robert
Column2 : 7/3/2003
Column3 : N45664Z
Column4 : 2000 Cadillac Escalade
Column5 : Peter Wilmoth, Mike Britt
Column6 : 7/6/2003

[Column 5] is the field in question.

Re: Aggregate string concatenation efficiency problem

OK. So far, what I am gathering is that you are performing an operation
that involves a query and also involves a string that is being
concatenated and that something is happening very slowly.

I hope I don't sound condescending here, but what is it that is
happening very slowly? The string concatenation operation? Or when you
run your original query?

Does this large string have anything to do with the original query? Or
is the string used in another operation and that operation is executing
very slowly?

The required information would be to explain exactly what is happening
slowly and what you are trying to accomplish - example: I need to run a
query to which generates a list of ... This query runs very slowly.

or

I need to run a query which generates of list of ..., and I am creating
a large concatenated string to be used as part of the Where clause in
this query. The string generation is very slow.

Right now the challenge is to get in to RDBMS-speak (speak database
language). Once we are both on the same page we can solve your problem.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Aggregate string concatenation efficiency problem

On Jan 30, 11:03 am, Rich P wrote:
> OK. So far, what I am gathering is that you are performing an operation
> that involves a query and also involves a string that is being
> concatenated and that something is happening very slowly.
>
> I hope I don't sound condescending here, but what is it that is
> happening very slowly? The string concatenation operation? Or when you
> run your original query?
>
> Does this large string have anything to do with the original query? Or
> is the string used in another operation and that operation is executing
> very slowly?
>
> The required information would be to explain exactly what is happening
> slowly and what you are trying to accomplish - example: I need to run a
> query to which generates a list of ... This query runs very slowly.
>
> or
>
> I need to run a query which generates of list of ..., and I am creating
> a large concatenated string to be used as part of the Where clause in
> this query. The string generation is very slow.
>
> Right now the challenge is to get in to RDBMS-speak (speak database
> language). Once we are both on the same page we can solve your problem.
>
> Rich
>
> *** Sent via Developersdexhttp://www.developersdex.com***

Okay Rich,

I addressed most of these requests for clarification in my first
post. If you read it carefully you will understand what I'm talking
about. Like here:

"What I observe is this: If I run the query, just run it,
it's quite fast. If I run it and loop through every row, it's fast.
(And my code, normally, is very fast). But as soon as I access the
data from Column5, the column in question, things slow way down. I
imagined that if the query ran and completed, all the data was
available at that moment, and no further time consuming db crunching
would be necessary, but that seems to be not the case. "

Further clarification. If I run the query in a query design window,
or open a recordset for that query and loop through all the records,
it's very fast. The specific problem occurs when reading data from
the field "Column5." I will illustrate this below.

I have no issue with my code. It's used in production and is quite
fast. The string concatenation that I perform with the string builder
class in that loop, for example, is very fast. You said:

> Does this large string have anything to do with the original query? Or
> is the string used in another operation and that operation is executing
> very slowly?

The answer is no. My problem is accessing the value of Column5, the
field in that query that runs the fConcatChild (which is an entirely
different string concatenation function). Did you look over Dev
Ashish's code?


> The required information would be to explain exactly what is happening
> slowly and what you are trying to accomplish - example: I need to run a
> query to which generates a list of ... This query runs very slowly.

This is what I'm saying.

Let me illustrate the exact problem in a very simple way. If I run my
code, as shown in my first post, but then ignore Column5 (the
fConcatChild field), there is no speed problem:

Do Until rs.EOF
ItemTextDelimmed = vbNullString
For n = 1 To ColumnCount
If n<>5 then '## IGNORE COLUMN 5
ItemTextDelimmed = ItemTextDelimmed & Nz(rs("Column" & n))
& "@"
End If
Next
ItemTextDelimmed = Left(ItemTextDelimmed, Len(ItemTextDelimmed) -
1)

'###These are objects of the cString class, a string builder class
for large strings
ItemTextArrayString.Append ItemTextDelimmed & "|"
OpenArgsArrayString.Append rs!VBAOpenArgs & "|"

rs.MoveNext
Loop

So once again, I've done the work. I've isolated the part of the
problem that's resulting in a performance hit. Why is accessing that
field in the recordset so slow, even if the query seems to finish
right away?

Re: Aggregate string concatenation efficiency problem

Hi John,

Now I am with you (sort of). I can't tell what is causing your
operation to run slow when you are trying to read Column5. But what I
would do for a starter is to append the data from your query to a temp
table:

Select * Into tblX From yourQuery

Now all of the query data is in the temp table which I called tblX. Try
reading column5 from this table.

Select column5 from tblx

Then try running your code against tblx instead of your query. If you
still have the same problem -- it will be much easier to figure out what
is going on because we have simplified the platform from a complex query
to a simple table.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Aggregate string concatenation efficiency problem

On Jan 30, 11:50 am, Rich P wrote:
> Hi John,
>
> Now I am with you (sort of). I can't tell what is causing your
> operation to run slow when you are trying to read Column5. But what I
> would do for a starter is to append the data from your query to a temp
> table:
>
> Select * Into tblX From yourQuery
>
> Now all of the query data is in the temp table which I called tblX. Try
> reading column5 from this table.
>
> Select column5 from tblx
>
> Then try running your code against tblx instead of your query. If you
> still have the same problem -- it will be much easier to figure out what
> is going on because we have simplified the platform from a complex query
> to a simple table.
>
> Rich
>
> *** Sent via Developersdexhttp://www.developersdex.com***

I did as you suggested. The make-table query was slow to run, and in
fact the extra time introduced into my query by 'Column5' is roughly
equal to the amount of time it took to complete the make-table query.
When I ran my code against the table, it ran fast, as would then be
expected.

So I'm at a loss because, full circle, I don't know how the database
works internally. To summarize: I can run my query and see all its
results in a datasheet, or run the query in vba and loop through all
the records and it's blazingly fast. But when I try to actually
access the data (i.e. store it to a variable) it takes a lot of time.
This also happens when the database physically allocates a new table
in the make-table query.

Any ideas?

Re: Aggregate string concatenation efficiency problem

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 Developersdex http://www.developersdex.com ***

Re: Aggregate string concatenation efficiency problem

On Jan 30, 12:29 pm, Rich P 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.

Re: Aggregate string concatenation efficiency problem

What we want to check by creating a query that only retrieves the data
for column5 is this:

1) does this query of only column5 run OK by itself?
2) does this query run a make table query OK?

If 2) has slow results like the original query, then we need to look at
your function and see what we can improve on. Could you show the code
for your function? and then show how you invoke it?


Select yourFunction(arg1, arg2, ...) As column5 From tblwhatever Join
tblwhatever2 On ... Where...

Public Sub yourFunction(arg1, arg2, ...)
...
End Function




Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Aggregate string concatenation efficiency problem

On Jan 30, 2:35=A0pm, JohnH wrote:
> On Jan 30, 12:29 pm, Rich P wrote:
>
>
>
>
>
> > Well, lets review what is going on to make sure I am straight on what is=

> > happening:
>
> > 1) =A0The query runs fast in the Query Grid
> > 2) =A0The query runs slow in a Make Table query
>
> > If this is the case, then try this: =A0make a copy of your query but
> > remove column5. =A0Now run a make table query on this copy query. =A0If =
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. =A0The problem may be in how you retrie=
ve
> > the data for column5.
>
> > Rich
>
> > *** Sent via Developersdexhttp://www.developersdex.com***
>
> Rich,
>
> Exactly. =A0Well now at least we're finally on the same page. =A0Column5
> has been the culprit from the beginning, that's what I was pointing
> out in my first post. =A0Column5 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. =A0The problem may be in how you retrie=
ve
> > the data for column5.
>
> I don't know what you mean here. =A0I 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 =3D 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 =3D currentdb

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

HTH,
Bruce

Re: Aggregate string concatenation efficiency problem

On Jan 30, 1:16 pm, Bruce wrote:
> On Jan 30, 2:35 pm, JohnH wrote:
>
>
>
> > On Jan 30, 12:29 pm, Rich P 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.

Re: Aggregate string concatenation efficiency problem

On Jan 30, 1:01 pm, Rich P wrote:
> What we want to check by creating a query that only retrieves the data
> for column5 is this:
>
> 1) does this query of only column5 run OK by itself?
> 2) does this query run a make table query OK?
>
> If 2) has slow results like the original query, then we need to look at
> your function and see what we can improve on. Could you show the code
> for your function? and then show how you invoke it?
>
> Select yourFunction(arg1, arg2, ...) As column5 From tblwhatever Join
> tblwhatever2 On ... Where...
>
> Public Sub yourFunction(arg1, arg2, ...)
> ..
> End Function
>
> Rich
>
> *** Sent via Developersdexhttp://www.developersdex.com***

It behaves the same way as stand alone. Here is the stripped down
query:

=======START QUERY========
SELECT Sales.SaleID, fConcatChild("Consultants RIGHT JOIN
ConsultantSales ON Consultants.ConsultantID =
ConsultantSales.ConsultantID","SaleID","ConsultantFullName", "Long",Sales.SaleID)
AS Column5
FROM Sales;
=======END QUERY========


And the function the query calls is here:

=======START CODE========
Public Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) _
As String

'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish

'Returns a field from the Many table of a 1:M relationship
'in a semi-colon separated format.
'
'Usage Examples:
' ?fConcatChild("Order Details", "OrderID", "Quantity", _
"Long", 10255)
'Where Order Details = Many side table
' OrderID = Primary Key of One side table
' Quantity = Field name to concatenate
' Long = DataType of Primary Key of One Side Table
' 10255 = Value on which return concatenated Quantity
'
' Set a reference to DAO

Static db As DAO.Database
If db Is Nothing Then Set dbs = DBEngine(0)(0)

Dim rs As DAO.Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String
On Error GoTo Err_fConcatChild

varConcat = Null
Set db = CurrentDb
strSQL = "Select " & strFldConcat & " From " & strChildTable
strSQL = strSQL & " Where "

Select Case strIDType
Case "String":
strSQL = strSQL & strIDName & " = '" & varIDvalue & "'"
Case "Long", "Integer", "Double": 'AutoNumber is Type Long
strSQL = strSQL & strIDName & " = " & varIDvalue
Case Else
GoTo Err_fConcatChild
End Select

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Are we sure that 'sub' records exist
With rs
If .RecordCount <> 0 Then
'start concatenating records
Do While Not rs.EOF
varConcat = varConcat & rs(strFldConcat) & ", "
.MoveNext
Loop
End If
End With

fConcatChild = Left(varConcat, Len(varConcat) - 2)

Exit_fConcatChild:
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Set db = Nothing
Exit Function

Err_fConcatChild:
Resume Exit_fConcatChild
End Function

=======END CODE========

Re: Aggregate string concatenation efficiency problem

On Jan 30, 3:55=A0pm, JohnH wrote:
> On Jan 30, 1:01 pm, Rich P wrote:
>
>
>
>
>
> > What we want to check by creating a query that only retrieves the data
> > for column5 is this:
>
> > 1) does this query of only column5 run OK by itself?
> > 2) does this query run a make table query OK?
>
> > If 2) has slow results like the original query, then we need to look at
> > your function and see what we can improve on. =A0Could you show the code=

> > for your function? =A0and then show how you invoke it?
>
> > Select yourFunction(arg1, arg2, ...) As column5 From tblwhatever Join
> > tblwhatever2 On ... =A0Where...
>
> > Public Sub yourFunction(arg1, arg2, ...)
> > ..
> > End Function
>
> > Rich
>
> > *** Sent via Developersdexhttp://www.developersdex.com***
>
> It behaves the same way as stand alone. =A0Here is the stripped down
> query:
>
> =======3DSTART QUERY========
> SELECT Sales.SaleID, fConcatChild("Consultants RIGHT JOIN
> ConsultantSales ON Consultants.ConsultantID =3D
> ConsultantSales.ConsultantID","SaleID","ConsultantFullName", "Long",Sales.S=
a=ADleID)
> AS Column5
> FROM Sales;
> =======3DEND QUERY========
>
> And the function the query calls is here:
>
> =======3DSTART CODE========
> Public Function fConcatChild(strChildTable As String, _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 strIDName As String, _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 strFldConcat As String, _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 strIDType As String, _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 varIDvalue As Variant) _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 As String
>
> 'This code was originally written by Dev Ashish
> 'It is not to be altered or distributed,
> 'except as part of an application.
> 'You are free to use it in any application,
> 'provided the copyright notice is left unchanged.
> '
> 'Code Courtesy of
> 'Dev Ashish
>
> 'Returns a field from the Many table of a 1:M relationship
> 'in a semi-colon separated format.
> '
> 'Usage Examples:
> ' =A0 ?fConcatChild("Order Details", "OrderID", "Quantity", _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 "Long", 10255)
> 'Where =A0Order Details =3D Many side table
> ' =A0 =A0 =A0 OrderID =A0 =A0 =A0 =3D Primary Key of One side table
> ' =A0 =A0 =A0 Quantity =A0 =A0  = Field name to concatenate
> ' =A0 =A0 =A0 Long =A0 =A0 =A0 =A0  = DataType of Primary Key of One S=
ide Table
> ' =A0 =A0 =A0 10255 =A0 =A0 =A0 =A0 =3D Value on which return concatenated=
Quantity
> '
> ' Set a reference to DAO
>
> Static db As DAO.Database
> If db Is Nothing Then Set dbs =3D DBEngine(0)(0)
>
> Dim rs As DAO.Recordset
> Dim varConcat As Variant
> Dim strCriteria As String, strSQL As String
> =A0 =A0 On Error GoTo Err_fConcatChild
>
> =A0 =A0 varConcat =3D Null
> =A0 =A0 Set db =3D CurrentDb
> =A0 =A0 strSQL =3D "Select " & strFldConcat & " From " & strChildTable
> =A0 =A0 strSQL =3D strSQL & " Where "
>
> =A0 =A0 Select Case strIDType
> =A0 =A0 =A0 =A0 Case "String":
> =A0 =A0 =A0 =A0 =A0 =A0 strSQL =3D strSQL & strIDName & " =3D '" & varIDva=
lue & "'"
> =A0 =A0 =A0 =A0 Case "Long", "Integer", "Double": =A0 =A0'AutoNumber is Ty=
pe Long
> =A0 =A0 =A0 =A0 =A0 =A0 strSQL =3D strSQL & strIDName & " =3D " & varIDval=
ue
> =A0 =A0 =A0 =A0 Case Else
> =A0 =A0 =A0 =A0 =A0 =A0 GoTo Err_fConcatChild
> =A0 =A0 End Select
>
> =A0 =A0 Set rs =3D db.OpenRecordset(strSQL, dbOpenSnapshot)
>
> =A0 =A0 'Are we sure that 'sub' records exist
> =A0 =A0 With rs
> =A0 =A0 =A0 =A0 If .RecordCount <> 0 Then
> =A0 =A0 =A0 =A0 =A0 =A0 'start concatenating records
> =A0 =A0 =A0 =A0 =A0 =A0 Do While Not rs.EOF
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 varConcat =3D varConcat & rs(strFldConcat)=
& ", "
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 .MoveNext
> =A0 =A0 =A0 =A0 =A0 =A0 Loop
> =A0 =A0 =A0 =A0 End If
> =A0 =A0 End With
>
> =A0 =A0 fConcatChild =3D Left(varConcat, Len(varConcat) - 2)
>
> Exit_fConcatChild:
> =A0 =A0 If Not rs Is Nothing Then
> =A0 =A0 =A0 =A0 rs.Close
> =A0 =A0 =A0 =A0 Set rs =3D Nothing
> =A0 =A0 End If
> =A0 =A0 Set db =3D Nothing
> =A0 =A0 Exit Function
>
> Err_fConcatChild:
> =A0 =A0 Resume Exit_fConcatChild
> End Function
>
> =======3DEND CODE========- Hide quoted text -
>
> - Show quoted text -

I'm sorry, I should have been clearer. You must also remove the line
'Set db =3D Currentdb' after 'varConcat =3D Null' as this defeats the
whole purpose of declaring db as a static variable. The 'If db is
nothing...' line causes this variable to be initialized only once.
Your code initializes it every time it is called.

Also, your Exit_fConcatChild: section should *not* set db =3D nothing
since you want db to remain static and thus be available unchanged the
next time fConcatChild() is called. Try this and the performance
gains should be much more substantial.

Bruce

Re: Aggregate string concatenation efficiency problem

On Jan 30, 2:26 pm, Bruce wrote:
> On Jan 30, 3:55 pm, JohnH wrote:
>
>
>
> > On Jan 30, 1:01 pm, Rich P wrote:
>
> > > What we want to check by creating a query that only retrieves the data=

> > > for column5 is this:
>
> > > 1) does this query of only column5 run OK by itself?
> > > 2) does this query run a make table query OK?
>
> > > If 2) has slow results like the original query, then we need to look a=
t
> > > your function and see what we can improve on. Could you show the code=

> > > for your function? and then show how you invoke it?
>
> > > Select yourFunction(arg1, arg2, ...) As column5 From tblwhatever Join
> > > tblwhatever2 On ... Where...
>
> > > Public Sub yourFunction(arg1, arg2, ...)
> > > ..
> > > End Function
>
> > > Rich
>
> > > *** Sent via Developersdexhttp://www.developersdex.com***
>
> > It behaves the same way as stand alone. Here is the stripped down
> > query:
>
> > =======3DSTART QUERY========
> > SELECT Sales.SaleID, fConcatChild("Consultants RIGHT JOIN
> > ConsultantSales ON Consultants.ConsultantID =3D
> > ConsultantSales.ConsultantID","SaleID","ConsultantFullName", "Long",Sales=
..Sa=ADleID)
> > AS Column5
> > FROM Sales;
> > =======3DEND QUERY========
>
> > And the function the query calls is here:
>
> > =======3DSTART CODE========
> > Public Function fConcatChild(strChildTable As String, _
> > strIDName As String, _
> > strFldConcat As String, _
> > strIDType As String, _
> > varIDvalue As Variant) _
> > As String
>
> > 'This code was originally written by Dev Ashish
> > 'It is not to be altered or distributed,
> > 'except as part of an application.
> > 'You are free to use it in any application,
> > 'provided the copyright notice is left unchanged.
> > '
> > 'Code Courtesy of
> > 'Dev Ashish
>
> > 'Returns a field from the Many table of a 1:M relationship
> > 'in a semi-colon separated format.
> > '
> > 'Usage Examples:
> > ' ?fConcatChild("Order Details", "OrderID", "Quantity", _
> > "Long", 10255)
> > 'Where Order Details =3D Many side table
> > ' OrderID =3D Primary Key of One side table
> > ' Quantity =3D Field name to concatenate
> > ' Long =3D DataType of Primary Key of One Side Table
> > ' 10255 =3D Value on which return concatenated Quantity
> > '
> > ' Set a reference to DAO
>
> > Static db As DAO.Database
> > If db Is Nothing Then Set dbs =3D DBEngine(0)(0)
>
> > Dim rs As DAO.Recordset
> > Dim varConcat As Variant
> > Dim strCriteria As String, strSQL As String
> > On Error GoTo Err_fConcatChild
>
> > varConcat =3D Null
> > Set db =3D CurrentDb
> > strSQL =3D "Select " & strFldConcat & " From " & strChildTable
> > strSQL =3D strSQL & " Where "
>
> > Select Case strIDType
> > Case "String":
> > strSQL =3D strSQL & strIDName & " =3D '" & varIDvalue & "'"
> > Case "Long", "Integer", "Double": 'AutoNumber is Type Long
> > strSQL =3D strSQL & strIDName & " =3D " & varIDvalue
> > Case Else
> > GoTo Err_fConcatChild
> > End Select
>
> > Set rs =3D db.OpenRecordset(strSQL, dbOpenSnapshot)
>
> > 'Are we sure that 'sub' records exist
> > With rs
> > If .RecordCount <> 0 Then
> > 'start concatenating records
> > Do While Not rs.EOF
> > varConcat =3D varConcat & rs(strFldConcat) & ", "
> > .MoveNext
> > Loop
> > End If
> > End With
>
> > fConcatChild =3D Left(varConcat, Len(varConcat) - 2)
>
> > Exit_fConcatChild:
> > If Not rs Is Nothing Then
> > rs.Close
> > Set rs =3D Nothing
> > End If
> > Set db =3D Nothing
> > Exit Function
>
> > Err_fConcatChild:
> > Resume Exit_fConcatChild
> > End Function
>
> > =======3DEND CODE========- Hide quoted text =
-
>
> > - Show quoted text -
>
> I'm sorry, I should have been clearer. You must also remove the line
> 'Set db =3D Currentdb' after 'varConcat =3D Null' as this defeats the
> whole purpose of declaring db as a static variable. The 'If db is
> nothing...' line causes this variable to be initialized only once.
> Your code initializes it every time it is called.
>
> Also, your Exit_fConcatChild: section should *not* set db =3D nothing
> since you want db to remain static and thus be available unchanged the
> next time fConcatChild() is called. Try this and the performance
> gains should be much more substantial.
>
> Bruce

No, I'm sorry. I just got back from lunch and am drowsy. I wasn't
even paying attention. Here's the code as I have it now:

=======3DSTART CODE========
Public Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) _
As String

Static db As DAO.Database
If db Is Nothing Then Set db =3D DBEngine(0)(0)

Dim rs As DAO.Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String
On Error GoTo Err_fConcatChild

varConcat =3D Null
strSQL =3D "Select " & strFldConcat & " From " & strChildTable
strSQL =3D strSQL & " Where "

Select Case strIDType
Case "String":
strSQL =3D strSQL & strIDName & " =3D '" & varIDvalue & "'"
Case "Long", "Integer", "Double": 'AutoNumber is Type Long
strSQL =3D strSQL & strIDName & " =3D " & varIDvalue
Case Else
GoTo Err_fConcatChild
End Select

Set rs =3D db.OpenRecordset(strSQL, dbOpenSnapshot)

'Are we sure that 'sub' records exist
With rs
If .RecordCount <> 0 Then
'start concatenating records
Do While Not rs.EOF
varConcat =3D varConcat & rs(strFldConcat) & ", "
.MoveNext
Loop
End If
End With

fConcatChild =3D Left(varConcat, Len(varConcat) - 2)

Exit_fConcatChild:
If Not rs Is Nothing Then
rs.Close
Set rs =3D Nothing
End If
Exit Function

Err_fConcatChild:
Resume Exit_fConcatChild
End Function
=======3DEND CODE========

I hate to say it, but the performance is only barely better, but still
way outside what I need.

Re: Aggregate string concatenation efficiency problem

>>
fConcatChild("Consultants RIGHT JOIN ConsultantSales ON
Consultants.ConsultantID =
ConsultantSales.ConsultantID","SaleID","ConsultantFullName", "Long",
[Sales].[SaleID]) AS Column5
<<

OK. I see your function call here. It looks like you are trying to
concatenate a consultant name. How about making this change:

...
FROM ((Customers INNER JOIN Sales ON Customers.CustomerID =
Sales.CustomerID) INNER JOIN VehicleSales ON Sales.SaleID =
VehicleSales.SaleID) Join Consultants t5 on t5.ID =
Customers.ConsultantID?

then replace fConcatChild with

Select ..., (t5.FirstName & ' ' & t5.LastName) As Column5...

or if this returns duplicates then

Select Distinct ..., (t5.FirstName & ' ' & t5.LastName) As Column5...

If the consultants table can't be joined to customers or vehicles
tables, you need to add a column so that you can join this table. I
will guess that there are many consultants to each customer? Then if a
customer has 10 consultants, you should have 10 rows for that customer.
This would be using 3rd Normal Form of the normalizing model (which is
what RDBMS is all about).

Note: (whith all due respect) if a sql statement is using a function
that calls a "Loop" -- that function needs to be scrapped. Loops should
be avoided in sql statements. Why? Do you have all day for your query
to run? The whole concept of normalization is to prevent redundant data
processing.

Once you have your resultset, then you can manipulate your data.


Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Aggregate string concatenation efficiency problem

I think I am starting to see the problem. Well, actually, I know what
the essential problem is -- you are calling a function that is using a
loop. If you have a lot of records, that loop will be called for each
record. Thus, Loops in sql is a bad thing.

Issue: Column5 -- it looks like you want to list multiple consultants
for each customer - on the same record. If this is the case then you
are defeating the whole concept of Normalization/RDBMS. If you are
trying to list customer information to include each consultant that has
interacted with the customer, you should retrieve a distinct row for
each consultant for each customer rather than concatenate a bunch of
names in the query. Here is my pseudo code for your query

Select t1.CustName, ... , t5.Consultant, ... From ((Customers t1 Join
tblB t2 on t1.ID = t2.ID) Join Vehicles t3 On t1.ID = t3.ID) Join
Consultants t5 On t1.ID = t5.ID Where t1.CustWhatever = 'something' And
...

If this returns duplicate records then use the "Distinct" keyword

Select Distinct t1.CustName, ... , t5.Consultant, ... From ((Customers
t1 Join tblB t2 on t1.ID = t2.ID) Join Vehicles t3 On t1.ID = t3.ID)
Join Consultants t5 On t1.ID = t5.ID Where t1.CustWhatever = 'something'
And ...

Once you have the resultset then you can manipulate your data for
presentation. If you can't join the Consultants table then you need to
re-evaluate the design of your application, and make it so that you can
join the required tables. The concept here is something about 3rd
Normal form. The goal is to prevent data duplication and redundant data
processing.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Aggregate string concatenation efficiency problem

On Jan 30, 3:39 pm, Rich P wrote:
> I think I am starting to see the problem. Well, actually, I know what
> the essential problem is -- you are calling a function that is using a
> loop. If you have a lot of records, that loop will be called for each
> record. Thus, Loops in sql is a bad thing.
>
> Issue: Column5 -- it looks like you want to list multiple consultants
> for each customer - on the same record. If this is the case then you
> are defeating the whole concept of Normalization/RDBMS. If you are
> trying to list customer information to include each consultant that has
> interacted with the customer, you should retrieve a distinct row for
> each consultant for each customer rather than concatenate a bunch of
> names in the query. Here is my pseudo code for your query
>
> Select t1.CustName, ... , t5.Consultant, ... From ((Customers t1 Join
> tblB t2 on t1.ID = t2.ID) Join Vehicles t3 On t1.ID = t3.ID) Join
> Consultants t5 On t1.ID = t5.ID Where t1.CustWhatever = 'something' And
> ..
>
> If this returns duplicate records then use the "Distinct" keyword
>
> Select Distinct t1.CustName, ... , t5.Consultant, ... From ((Customers
> t1 Join tblB t2 on t1.ID = t2.ID) Join Vehicles t3 On t1.ID = t3.ID)
> Join Consultants t5 On t1.ID = t5.ID Where t1.CustWhatever = 'something'
> And ...
>
> Once you have the resultset then you can manipulate your data for
> presentation. If you can't join the Consultants table then you need to
> re-evaluate the design of your application, and make it so that you can
> join the required tables. The concept here is something about 3rd
> Normal form. The goal is to prevent data duplication and redundant data
> processing.
>
> Rich
>
> *** Sent via Developersdexhttp://www.developersdex.com***

Yes Rich, normally you're absolutely correct. What's humorous about
the situation is that I spent a day of research trying to come up with
this very solution to my problem. When I came up with it, I realized
that this fConcatChild function would be calling a loop *every single
time* it returned a record, but since you cannot create a true custom
aggregate function (such as Group By sConcat(ConsultantFullName)), it
was what seemed like my only option. When I drew up the query and
code, and ran it in the query design window, it went snap crackle pop
and I had all my results. I thought things were humming nicely until
I implemented the query in my code, started accessing Column5, and
took the huge performance hit. That's why this post.

The issue is this: My QuickSearch functionality allows the user to
search, in this case, in Sales, a VIN or Stock#, and what is returned
in the custom application workflow I've written is a group of matching
sales. This group of sales is represented as line items in a listview
at the top, that the user can look through and select from. (The
Columns returned by my query populate the columns of the listview.) If
my search query returns more than one record for each Sale, because
there are multiple ConsultantSales, then I get more than one lineitem
for that sale, which is confusing to the user and obscures the number
and uniqueness of sales. This is why I wanted to get a comma
delimited string for the Consultants who are listed on that sale, so
it shows everything together:

Column1 : Maynard, Robert
Column2 : 7/3/2003
Column3 : N45664Z
Column4 : 2000 Cadillac Escalade
Column5 : Peter Wilmoth, Mike Britt <------
Column6 : 7/6/2003

Now I could violate the rules of normalization and data duplication
and create a field in Sales called SaleConsultantsString and then keep
it filled with the data I need. But I won't do that, obviously. :)

I could also set up Select Case statements throughout my vba code and
handle exceptions for different objects (Customers, Sales,
Consultants, Credit Unions, Contacts, Dealers, etc). But I won't do
that either.

So that's the why of it. I appreciate your help, and you've probably
followed me as far as you can along with this ride. Thanks again.

Re: Aggregate string concatenation efficiency problem

Hi John,

Your problem is interesting to me - challenging (I hate these kinds of
problems :). I would still go with the idea of getting multiple rows of
consultants in the resultset -- and then do the string concatenation.

Sub DisplaySearchResult()
Dim RS As DAO.Recordset, str1 As String
'--in this sample your query has a normalized column5
'--and say it returns 3 rows -- 3 consultants for the
'--given customer - save resultset to a temp table
'--which you have already created - is persistent
DoCmd.RunSql "Insert Into temp Select * from yourQuery Where..."
Set RS = CurrentDB.OpenRecordset("Select * From temp")
'--for all 3 rows column1 will be 'Dave Smith'
'--no need to iterate here
column1 = RS!Colulmn1
...
column4 = RS!column4
'--now we iterate for each of the consultants
Do While Not RS.EOF
str1 = str1 & RS!Column5 & ", "
RS.MoveNext
Loop
RS.Close
column5 = str1
End Sub

This way you are looping against the resultset instead of the source
data. The resultset should be much smaller than the source data.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Aggregate string concatenation efficiency problem

On Jan 30, 8:58 pm, Rich P wrote:
> Hi John,
>
> Your problem is interesting to me - challenging (I hate these kinds of
> problems :). I would still go with the idea of getting multiple rows of
> consultants in the resultset -- and then do the string concatenation.
>
> Sub DisplaySearchResult()
> Dim RS As DAO.Recordset, str1 As String
> '--in this sample your query has a normalized column5
> '--and say it returns 3 rows -- 3 consultants for the
> '--given customer - save resultset to a temp table
> '--which you have already created - is persistent
> DoCmd.RunSql "Insert Into temp Select * from yourQuery Where..."
> Set RS = CurrentDB.OpenRecordset("Select * From temp")
> '--for all 3 rows column1 will be 'Dave Smith'
> '--no need to iterate here
> column1 = RS!Colulmn1
> ..
> column4 = RS!column4
> '--now we iterate for each of the consultants
> Do While Not RS.EOF
> str1 = str1 & RS!Column5 & ", "
> RS.MoveNext
> Loop
> RS.Close
> column5 = str1
> End Sub
>
> This way you are looping against the resultset instead of the source
> data. The resultset should be much smaller than the source data.
>
> Rich
>
> *** Sent via Developersdexhttp://www.developersdex.com***

Rich,

That is a good idea, and preferential to processing the entire table
every time a search is done. It brings us back, however, to the
unpalatable alternative of making exceptions in my search code for
different objects (Select Cases). But I think that's probably my best
option at this point.

In a true search form, elsewhere in my application, I already solved
this very problem, and could customize the way the data was returned
because the search form itself was specific to the object being
searched. Here's a slightly hacked but I think fairly efficient way
to do it: (air code to follow)

Dim SalesUB as Long
Dim SalesLB as Long

set rs=db.openrecordset("SELECT SaleID FROM Sales ORDER BY SaleID;)
SalesLB=rs!SaleID
rs.movelast
SalesUB=rs!SaleID
rs.close

Dim aHits() as Byte
redim aHits(SalesLB to SalesUB)

Dim aItemText() as Long
redim aItemText(SalesLB to SalesUB)

'My query will return normalized data, i.e. 1 record for each
consultant on the sale
set rs=db.openrecordset([My query])

dim SaleID as long
Do until rs.EOF
SaleID =rs!SaleID
If aHits(SaleID )=0 Then
aHits(SaleID)=1

ItemTextDelimmed = vbNullString
For n = 1 To ColumnCount
ItemTextDelimmed = ItemTextDelimmed & Nz(rs("Column" & n)) &
"@"
Next
ItemTextDelimmed = Left(ItemTextDelimmed, Len(ItemTextDelimmed) -
1)

aItemText(SaleID)=ItemTextDelimmed
OpenArgsArrayString.Append rs!VBAOpenArgs & "|"
Else
'Code to append additional consultant to ItemTextDelimmed entry
in the array
End if
rs.movenext
Loop

'Code to loop through aItemText and create my ItemTextArrayString
object, ignoring empty array space

By way of explanation:
In small installations (less than millions of records) it's fairly
economical to create an array with every possible primary key from the
table you want to check 'duplicates' against (duplicates in this case
are multiple SaleIDs for each ConsultantSale). Then, to check if a
SaleID has already been added to the final data (you're seeing another
record with another Consultant for the Sale), you merely check the
byte array aHits to see if the value for that slot has been changed to
a 1. If it has, you append Column5 to the aItemText array. Finally I
can walk the aItemText array to pull out all non-blank entries and
create my ItemTextArrayString object.

I suppose that this is what I'll end up doing, even though it's
slightly messy and even though it will require me to break the mold I
created for this QuickSearch function.

Any thoughts?

Re: Aggregate string concatenation efficiency problem

Greetings,

Well, one more thought I had was this: In trying to simplify things --
going back to referential integrity/normalization...

you could pull straight forward data from the query into a temp table
and then pull the column5 data (the consultants into another temp table
and associate the tables. Actually, these tables would not be temporary
- they would be fixed tables but the data would be temporary. The idea
here (assuming Access 2002 or greater) is that with the table
relationship you get a little + symbol at the left side of each row in
the primary table which when you click the + it expands the detail table
(which would be the temp table containing the list of consultants).

This way you don't even have to parse/concatenate anything. You have an
automatic list. You could join/relate tempA and tempB on SaleID when in
tempA you have only 1 record for each saleID and in tempB you would have
as many records for a saleID as there were consultants for that saleID.
Now your search feature would look something like this:

Sub Search
Docmd.RunSql "Insert Into tempA Select * From queryWithNoColumn5 Where
criteria = '" & txtCriteria & "'"
Docmd.RunSql "Insert Into tempB Select * From queryWithColumn5 Where
criteria = '" & txtCriteria & "'"
Me.Requery
Me.Refresh
End Sub

So if you have a subform displaying the search results all the user
needs to do to see the related consultants for a sale is to click/expand
the + on a given record. No looping involved at all.

The goal here is 6 lines of code vs 106 lines of code. Or to take it
one step further - you could have 2 subforms instead of doing the
relationship thing

subform1 displays tempA data. subform2 would display tempB data for a
selected record in subformA. When a user selects a record in subform1
then subform2 gets requeried and displays the corresponding consultants
for the selected record.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Aggregate string concatenation efficiency problem

On Jan 31, 12:57 pm, Rich P wrote:
> Greetings,
>
> Well, one more thought I had was this: In trying to simplify things --
> going back to referential integrity/normalization...
>
> you could pull straight forward data from the query into a temp table
> and then pull the column5 data (the consultants into another temp table
> and associate the tables. Actually, these tables would not be temporary
> - they would be fixed tables but the data would be temporary. The idea
> here (assuming Access 2002 or greater) is that with the table
> relationship you get a little + symbol at the left side of each row in
> the primary table which when you click the + it expands the detail table
> (which would be the temp table containing the list of consultants).
>
> This way you don't even have to parse/concatenate anything. You have an
> automatic list. You could join/relate tempA and tempB on SaleID when in
> tempA you have only 1 record for each saleID and in tempB you would have
> as many records for a saleID as there were consultants for that saleID.
> Now your search feature would look something like this:
>
> Sub Search
> Docmd.RunSql "Insert Into tempA Select * From queryWithNoColumn5 Where
> criteria = '" & txtCriteria & "'"
> Docmd.RunSql "Insert Into tempB Select * From queryWithColumn5 Where
> criteria = '" & txtCriteria & "'"
> Me.Requery
> Me.Refresh
> End Sub
>
> So if you have a subform displaying the search results all the user
> needs to do to see the related consultants for a sale is to click/expand
> the + on a given record. No looping involved at all.
>
> The goal here is 6 lines of code vs 106 lines of code. Or to take it
> one step further - you could have 2 subforms instead of doing the
> relationship thing
>
> subform1 displays tempA data. subform2 would display tempB data for a
> selected record in subformA. When a user selects a record in subform1
> then subform2 gets requeried and displays the corresponding consultants
> for the selected record.
>
> Rich
>
> *** Sent via Developersdexhttp://www.developersdex.com***

That's an idea but it doesn't apply to the requirements of my
interface design. My database adheres to normalization standards.
I'm talking about a specific case with respect to the interface where
all summary information relevant to a Sale needs to be displayed as a
single listitem for previewing purposes, regardless of the underlying
data structures.

I appreciate your help with this. Through hashing it out with you
I've decided upon the best solution for my needs.

These problems certainly help expand one's understanding of the issues
involved in database design. :)