Re: Aggregate string concatenation efficiency problem

On Jan 30, 3:39 pm, Rich P <rpng...@xxxxxxx> 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.
.

Relevant Pages

  • RE: Query: Combination of Group By, Top 10, Sorting...
    ... I changed the SalesHistory_SUM query ... This query totals all sales not included in the customer top 5. ...
    (microsoft.public.access.queries)
  • att: Tom Ellison: How to compare last years to todays numbers from one table?
    ... Thank you very much for your tips, took a while to get it working but here a copy of the query I managed to patch together: ... What you'd have remaining is Customer / Sales / Prev Sales. ... >Tom Ellison ...
    (microsoft.public.access.queries)
  • Re: Finding records
    ... One query method that may work: ... FROM (Customers as C INNER JOIN Sales as S ... I have three tables, one is Customer, one is Item and one is Sales. ...
    (microsoft.public.access.queries)
  • Re: How to compare last years to todays numbers from one table?
    ... You are absolutely right about my sample not to include prev and present period. ... As well as concolidating periods (e.g. customer A all sales in Month Jan or all sales in 2004) to facilitate reading ... I think I learned more than I was asking for :-) Never thought of the nested querying or adding a table twice to a query. ... What you'd have remaining is Customer / Sales / Prev Sales. ...
    (microsoft.public.access.queries)
  • Re: [fw-wiz] How to Save The World
    ... _EXACTLY_ that kind of garbage. ... people who don't understand a sales cycle. ... stuff only "convinces" a customer who's already convinced (one way ... by hundreds of list members emailing me, "yeah, I buy stuff from ...
    (Firewall-Wizards)