 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / January 2008Aggregate string concatenation efficiency problem
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. 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 > And now, with that preface, I can describe my problem. It is intolerably > slow. [quoted text clipped - 26 lines] > > *** 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. > > And now, with that preface, I can describe my problem. It is intolerably > > slow. [quoted text clipped - 43 lines] > 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. 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 > 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 [quoted text clipped - 24 lines] > > *** 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? Hi John, Now I am with you (sort of). I can't tell what is causing your operation to run slow when you are trying to read Column5. But what I would do for a starter is to append the data from your query to a temp table: Select * Into tblX From yourQuery Now all of the query data is in the temp table which I called tblX. Try reading column5 from this table. Select column5 from tblx Then try running your code against tblx instead of your query. If you still have the same problem -- it will be much easier to figure out what is going on because we have simplified the platform from a complex query to a simple table. Rich > Hi John, > [quoted text clipped - 18 lines] > > *** Sent via Developersdexhttp://www.developersdex.com*** I did as you suggested. The make-table query was slow to run, and in fact the extra time introduced into my query by 'Column5' is roughly equal to the amount of time it took to complete the make-table query. When I ran my code against the table, it ran fast, as would then be expected. So I'm at a loss because, full circle, I don't know how the database works internally. To summarize: I can run my query and see all its results in a datasheet, or run the query in vba and loop through all the records and it's blazingly fast. But when I try to actually access the data (i.e. store it to a variable) it takes a lot of time. This also happens when the database physically allocates a new table in the make-table query. Any ideas? Well, lets review what is going on to make sure I am straight on what is happening: 1) The query runs fast in the Query Grid 2) The query runs slow in a Make Table query If this is the case, then try this: make a copy of your query but remove column5. Now run a make table query on this copy query. If the make table query runs fast without column5, then we have narrowed it down that column5 is the trouble maker. If this is the case, then try creating a test query that only retrieves the same data column5 retrieves. The problem may be in how you retrieve the data for column5. Rich > Well, lets review what is going on to make sure I am straight on what is > happening: [quoted text clipped - 14 lines] > > *** Sent via Developersdexhttp://www.developersdex.com*** Rich, Exactly. Well now at least we're finally on the same page. Column5 has been the culprit from the beginning, that's what I was pointing out in my first post. Column5 runs a function to calculate its value, and that function is a sort of pseudo-aggregate function. I did a make-table without Column5 just in case I was insane and to make things totally clear, and as expected, it ran fine. > If this is the case, then try creating a test query that only retrieves > the same data column5 retrieves. The problem may be in how you retrieve > the data for column5. I don't know what you mean here. I can create a query with only 'Column5' but I don't know why you are suggesting I do that. What we want to check by creating a query that only retrieves the data for column5 is this: 1) does this query of only column5 run OK by itself? 2) does this query run a make table query OK? If 2) has slow results like the original query, then we need to look at your function and see what we can improve on. Could you show the code for your function? and then show how you invoke it? Select yourFunction(arg1, arg2, ...) As column5 From tblwhatever Join tblwhatever2 On ... Where... Public Sub yourFunction(arg1, arg2, ...) .. End Function Rich > What we want to check by creating a query that only retrieves the data > for column5 is this: [quoted text clipped - 16 lines] > > *** Sent via Developersdexhttp://www.developersdex.com*** It behaves the same way as stand alone. Here is the stripped down query: =======START QUERY======== SELECT Sales.SaleID, fConcatChild("Consultants RIGHT JOIN ConsultantSales ON Consultants.ConsultantID = ConsultantSales.ConsultantID","SaleID","ConsultantFullName","Long",Sales.SaleID) AS Column5 FROM Sales; =======END QUERY======== And the function the query calls is here: =======START CODE======== Public Function fConcatChild(strChildTable As String, _ strIDName As String, _ strFldConcat As String, _ strIDType As String, _ varIDvalue As Variant) _ As String 'This code was originally written by Dev Ashish 'It is not to be altered or distributed, 'except as part of an application. 'You are free to use it in any application, 'provided the copyright notice is left unchanged. ' 'Code Courtesy of 'Dev Ashish 'Returns a field from the Many table of a 1:M relationship 'in a semi-colon separated format. ' 'Usage Examples: ' ?fConcatChild("Order Details", "OrderID", "Quantity", _ "Long", 10255) 'Where Order Details = Many side table ' OrderID = Primary Key of One side table ' Quantity = Field name to concatenate ' Long = DataType of Primary Key of One Side Table ' 10255 = Value on which return concatenated Quantity ' ' Set a reference to DAO Static db As DAO.Database If db Is Nothing Then Set dbs = DBEngine(0)(0) Dim rs As DAO.Recordset Dim varConcat As Variant Dim strCriteria As String, strSQL As String On Error GoTo Err_fConcatChild varConcat = Null Set db = CurrentDb strSQL = "Select " & strFldConcat & " From " & strChildTable strSQL = strSQL & " Where " Select Case strIDType Case "String": strSQL = strSQL & strIDName & " = '" & varIDvalue & "'" Case "Long", "Integer", "Double": 'AutoNumber is Type Long strSQL = strSQL & strIDName & " = " & varIDvalue Case Else GoTo Err_fConcatChild End Select Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot) 'Are we sure that 'sub' records exist With rs If .RecordCount <> 0 Then 'start concatenating records Do While Not rs.EOF varConcat = varConcat & rs(strFldConcat) & ", " .MoveNext Loop End If End With fConcatChild = Left(varConcat, Len(varConcat) - 2) Exit_fConcatChild: If Not rs Is Nothing Then rs.Close Set rs = Nothing End If Set db = Nothing Exit Function Err_fConcatChild: Resume Exit_fConcatChild End Function =======END CODE======== > > What we want to check by creating a query that only retrieves the data > > for column5 is this: [quoted text clipped - 113 lines] > > - Show quoted text - I'm sorry, I should have been clearer. You must also remove the line 'Set db = Currentdb' after 'varConcat = Null' as this defeats the whole purpose of declaring db as a static variable. The 'If db is nothing...' line causes this variable to be initialized only once. Your code initializes it every time it is called. Also, your Exit_fConcatChild: section should *not* set db = nothing since you want db to remain static and thus be available unchanged the next time fConcatChild() is called. Try this and the performance gains should be much more substantial. Bruce > > > What we want to check by creating a query that only retrieves the data > > > for column5 is this: [quoted text clipped - 126 lines] > > Bruce No, I'm sorry. I just got back from lunch and am drowsy. I wasn't even paying attention. Here's the code as I have it now: =======START CODE======== Public Function fConcatChild(strChildTable As String, _ strIDName As String, _ strFldConcat As String, _ strIDType As String, _ varIDvalue As Variant) _ As String Static db As DAO.Database If db Is Nothing Then Set db = DBEngine(0)(0) Dim rs As DAO.Recordset Dim varConcat As Variant Dim strCriteria As String, strSQL As String On Error GoTo Err_fConcatChild varConcat = Null strSQL = "Select " & strFldConcat & " From " & strChildTable strSQL = strSQL & " Where " Select Case strIDType Case "String": strSQL = strSQL & strIDName & " = '" & varIDvalue & "'" Case "Long", "Integer", "Double": 'AutoNumber is Type Long strSQL = strSQL & strIDName & " = " & varIDvalue Case Else GoTo Err_fConcatChild End Select Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot) 'Are we sure that 'sub' records exist With rs If .RecordCount <> 0 Then 'start concatenating records Do While Not rs.EOF varConcat = varConcat & rs(strFldConcat) & ", " .MoveNext Loop End If End With fConcatChild = Left(varConcat, Len(varConcat) - 2) Exit_fConcatChild: If Not rs Is Nothing Then rs.Close Set rs = Nothing End If Exit Function Err_fConcatChild: Resume Exit_fConcatChild End Function =======END CODE======== I hate to say it, but the performance is only barely better, but still way outside what I need. 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 > 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 [quoted text clipped - 31 lines] > > *** 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. Hi John, Your problem is interesting to me - challenging (I hate these kinds of problems :). I would still go with the idea of getting multiple rows of consultants in the resultset -- and then do the string concatenation. Sub DisplaySearchResult() Dim RS As DAO.Recordset, str1 As String '--in this sample your query has a normalized column5 '--and say it returns 3 rows -- 3 consultants for the '--given customer - save resultset to a temp table '--which you have already created - is persistent DoCmd.RunSql "Insert Into temp Select * from yourQuery Where..." Set RS = CurrentDB.OpenRecordset("Select * From temp") '--for all 3 rows column1 will be 'Dave Smith' '--no need to iterate here column1 = RS!Colulmn1 .. column4 = RS!column4 '--now we iterate for each of the consultants Do While Not RS.EOF str1 = str1 & RS!Column5 & ", " RS.MoveNext Loop RS.Close column5 = str1 End Sub This way you are looping against the resultset instead of the source data. The resultset should be much smaller than the source data. Rich > Hi John, > [quoted text clipped - 30 lines] > > *** Sent via Developersdexhttp://www.developersdex.com*** Rich, That is a good idea, and preferential to processing the entire table every time a search is done. It brings us back, however, to the unpalatable alternative of making exceptions in my search code for different objects (Select Cases). But I think that's probably my best option at this point. In a true search form, elsewhere in my application, I already solved this very problem, and could customize the way the data was returned because the search form itself was specific to the object being searched. Here's a slightly hacked but I think fairly efficient way to do it: (air code to follow) Dim SalesUB as Long Dim SalesLB as Long set rs=db.openrecordset("SELECT SaleID FROM Sales ORDER BY SaleID;) SalesLB=rs!SaleID rs.movelast SalesUB=rs!SaleID rs.close Dim aHits() as Byte redim aHits(SalesLB to SalesUB) Dim aItemText() as Long redim aItemText(SalesLB to SalesUB) 'My query will return normalized data, i.e. 1 record for each consultant on the sale set rs=db.openrecordset([My query]) dim SaleID as long Do until rs.EOF SaleID =rs!SaleID If aHits(SaleID )=0 Then aHits(SaleID)=1 ItemTextDelimmed = vbNullString For n = 1 To ColumnCount ItemTextDelimmed = ItemTextDelimmed & Nz(rs("Column" & n)) & "@" Next ItemTextDelimmed = Left(ItemTextDelimmed, Len(ItemTextDelimmed) - 1) aItemText(SaleID)=ItemTextDelimmed OpenArgsArrayString.Append rs!VBAOpenArgs & "|" Else 'Code to append additional consultant to ItemTextDelimmed entry in the array End if rs.movenext Loop 'Code to loop through aItemText and create my ItemTextArrayString object, ignoring empty array space By way of explanation: In small installations (less than millions of records) it's fairly economical to create an array with every possible primary key from the table you want to check 'duplicates' against (duplicates in this case are multiple SaleIDs for each ConsultantSale). Then, to check if a SaleID has already been added to the final data (you're seeing another record with another Consultant for the Sale), you merely check the byte array aHits to see if the value for that slot has been changed to a 1. If it has, you append Column5 to the aItemText array. Finally I can walk the aItemText array to pull out all non-blank entries and create my ItemTextArrayString object. I suppose that this is what I'll end up doing, even though it's slightly messy and even though it will require me to break the mold I created for this QuickSearch function. Any thoughts? Greetings, Well, one more thought I had was this: In trying to simplify things -- going back to referential integrity/normalization... you could pull straight forward data from the query into a temp table and then pull the column5 data (the consultants into another temp table and associate the tables. Actually, these tables would not be temporary - they would be fixed tables but the data would be temporary. The idea here (assuming Access 2002 or greater) is that with the table relationship you get a little + symbol at the left side of each row in the primary table which when you click the + it expands the detail table (which would be the temp table containing the list of consultants). This way you don't even have to parse/concatenate anything. You have an automatic list. You could join/relate tempA and tempB on SaleID when in tempA you have only 1 record for each saleID and in tempB you would have as many records for a saleID as there were consultants for that saleID. Now your search feature would look something like this: Sub Search Docmd.RunSql "Insert Into tempA Select * From queryWithNoColumn5 Where criteria = '" & txtCriteria & "'" Docmd.RunSql "Insert Into tempB Select * From queryWithColumn5 Where criteria = '" & txtCriteria & "'" Me.Requery Me.Refresh End Sub So if you have a subform displaying the search results all the user needs to do to see the related consultants for a sale is to click/expand the + on a given record. No looping involved at all. The goal here is 6 lines of code vs 106 lines of code. Or to take it one step further - you could have 2 subforms instead of doing the relationship thing subform1 displays tempA data. subform2 would display tempB data for a selected record in subformA. When a user selects a record in subform1 then subform2 gets requeried and displays the corresponding consultants for the selected record. Rich > Greetings, > [quoted text clipped - 41 lines] > > *** Sent via Developersdexhttp://www.developersdex.com*** That's an idea but it doesn't apply to the requirements of my interface design. My database adheres to normalization standards. I'm talking about a specific case with respect to the interface where all summary information relevant to a Sale needs to be displayed as a single listitem for previewing purposes, regardless of the underlying data structures. I appreciate your help with this. Through hashing it out with you I've decided upon the best solution for my needs. These problems certainly help expand one's understanding of the issues involved in database design. :) fConcatChild("Consultants RIGHT JOIN ConsultantSales ON Consultants.ConsultantID = ConsultantSales.ConsultantID","SaleID","ConsultantFullName","Long", [Sales].[SaleID]) AS Column5 << OK. I see your function call here. It looks like you are trying to concatenate a consultant name. How about making this change: .. FROM ((Customers INNER JOIN Sales ON Customers.CustomerID = Sales.CustomerID) INNER JOIN VehicleSales ON Sales.SaleID = VehicleSales.SaleID) Join Consultants t5 on t5.ID = Customers.ConsultantID? then replace fConcatChild with Select ..., (t5.FirstName & ' ' & t5.LastName) As Column5... or if this returns duplicates then Select Distinct ..., (t5.FirstName & ' ' & t5.LastName) As Column5... If the consultants table can't be joined to customers or vehicles tables, you need to add a column so that you can join this table. I will guess that there are many consultants to each customer? Then if a customer has 10 consultants, you should have 10 rows for that customer. This would be using 3rd Normal Form of the normalizing model (which is what RDBMS is all about). Note: (whith all due respect) if a sql statement is using a function that calls a "Loop" -- that function needs to be scrapped. Loops should be avoided in sql statements. Why? Do you have all day for your query to run? The whole concept of normalization is to prevent redundant data processing. Once you have your resultset, then you can manipulate your data. Rich > > Well, lets review what is going on to make sure I am straight on what is > > happening: [quoted text clipped - 33 lines] > > - Show quoted text - The issue appears to be that every time you reference rs!Column5, the fConcatChild() function has to be reevaluated, and as written, this function is going to be slow. You can check this behavior by setting a breakpoint in the fConcatChild. You should notice that when your code hits the 'for n-1 to columncount' loop that it will stop in the fConcatChild() function when n = 5. That being said, you can probably generate some significant performance gains by rewriting the fConcatChild function to use a static database variable. E.g. instead of dim dbs as database use: static dbs as database if dbs is nothing then set dbs = currentdb At the very least, consider replacing currentdb with dbengine(0)(0) which incurs much less overhead. HTH, Bruce > > > Well, lets review what is going on to make sure I am straight on what is > > > happening: [quoted text clipped - 56 lines] > HTH, > Bruce Thank you Bruce. I took your advice and changed the database object to a static variable. Unfortunately the performance gain was negligible. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.