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 ***
.

Relevant Pages

  • RE: loops
    ... I have the following piece of code which checks the customer and product ... I have now realised that I need to enter a qty for price breaks so the loop ... Loop While Not C Is Nothing And C.Address firstAddress ...
    (microsoft.public.excel.programming)
  • RE: loops
    ... I have the following piece of code which checks the customer and product ... I have now realised that I need to enter a qty for price breaks so the loop ... Loop While Not C Is Nothing And C.Address firstAddress ...
    (microsoft.public.excel.programming)
  • Re: Looking for a way to convert dec to bin
    ... 'do stuff including exit sub ... For each Customer in Customers ... or For Each loop, I see something that I expect to run from start to finish. ... use a loop that's a couple of lines shorter or longer, ...
    (microsoft.public.vb.general.discussion)
  • RE: do until
    ... loop can go to the next record otherwise it keeps looping through the first ... I have a table called ACH that I search for a customer id and find out ... if that customer has passthru, ... Set rsCustomer = dbType.OpenRecordset("Customer", ...
    (microsoft.public.access.modulesdaovba)
  • Re: Two quick RS-485 questions
    ... > plan to stick an RS485 driver on an AVR, ... proper 'driver' at the TX side while all receivers are merely cheap ... > I and the customer have metric truckloads of the stuff lying about. ... With 2400 bps and a current loop you could even use fence wire. ...
    (comp.arch.embedded)