Re: Aggregate string concatenation efficiency problem

On Jan 30, 11:03 am, Rich P <rpng...@xxxxxxx> 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?
.

Relevant Pages

  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)
  • Too Few Parameters Problem w/Query
    ... I am trying to run a concatenate function in a query and I'm having a ... Domain As String, _ ... ' Description: A generic "concatenation" routine. ... Dim strConcatenate As String ...
    (microsoft.public.access.gettingstarted)
  • Re: Too Few Parameters Problem w/Query
    ... when I run the query it grinds for about an hour and then says there ... Domain As String, _ ... ' Description: A generic "concatenation" routine. ... Dim strConcatenate As String ...
    (microsoft.public.access.gettingstarted)
  • Re: Need help with Code Please!!!
    ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)
  • Re: Need help with Code Please!!!
    ... putting the query SQL itself in here maybe it will help you understand what I ... the actual string that gets built at the end of the SQ1 build process. ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ... Dim qd As QueryDef 'object ref to query item ...
    (microsoft.public.access.formscoding)