Aggregate string concatenation efficiency problem
- From: JohnH <JohnHarris34@xxxxxxxxx>
- Date: Wed, 30 Jan 2008 09:47:39 -0800 (PST)
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.
.
- Follow-Ups:
- Prev by Date: Re: Delete Option grayed out
- Next by Date: Re: ShellExecute API
- Previous by thread: ShellExecute API
- Next by thread: Re: Aggregate string concatenation efficiency problem
- Index(es):
Relevant Pages
|
