Re: Aggregate string concatenation efficiency problem
- From: JohnH <JohnHarris34@xxxxxxxxx>
- Date: Wed, 30 Jan 2008 10:41:04 -0800 (PST)
On Jan 30, 10:24 am, JohnH <JohnHarri...@xxxxxxxxx> wrote:
On Jan 30, 10:16 am, Rich P <rpng...@xxxxxxx> 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.
.
- Follow-Ups:
- References:
- Aggregate string concatenation efficiency problem
- From: JohnH
- Re: Aggregate string concatenation efficiency problem
- From: Rich P
- Re: Aggregate string concatenation efficiency problem
- From: JohnH
- Aggregate string concatenation efficiency problem
- Prev by Date: Re: How convince Access to stop "fixing" my text?
- Next by Date: Access 2003 reports won't preview or print with Vista
- Previous by thread: Re: Aggregate string concatenation efficiency problem
- Next by thread: Re: Aggregate string concatenation efficiency problem
- Index(es):
Relevant Pages
|
