[cfaussie] Re: cfindex on CFMX7 with 1 record queries
Pat - I saw a posting of yours elsewhere dated January of 06 regarding slow verity indexing. After searching your name, I found this cfaussie thread. I have encountered exactly the same cfindex problem. It takes waaay too long. I've been looking for the cause, but have developed a response. Here's what I've learned and done: - It takes only instants longer to index a record set of dozens of rows. This indicates pretty clearly that it's not the indexing itself that's taking all the time, but rather, some gateway function to the indexing process. - This problem is happening at a company where I use shared hosting services: intermedia.net. They are clueless (and somewhat ambivalent) about the problem. - Often, I get a tag timeout error message from the cfm page if there's a cfoutput tag after the call to cfindex. Sooo, - I've developed a very effective but shockingly complex system of queueing index requests and using a scheduled script to process them. This offloads the indexing from the UI so users don't get errors. The scheduled process calls itself recursively to avoid the need for loops that timeout. It also aggregates similar requests to take advantage of the huge time savings when multiple recs are indexed at once. This allows me to load up the queue with lots of requests when needed (to rebuild/refresh, for instance). What a pain. I'd be happy to share further if that's of value to you. I'd love to know the CAUSE, though... - Don Coxe --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: cfindex on CFMX7 with 1 record queries
I'm still confused. You compare an update of 1 row to an update of 100 rows. Normally cfindex is slower when you compare the following: You have 100 rows to update. Option A: Loop over each row and insert one at a time. Option B: Insert all at once as a query. Does this describe what you are doing? On 5/23/07, Pat Branley [EMAIL PROTECTED] wrote: when you say not super fast, but fast how long would you expect a 1 record index to take ? on our servers this can be 10 seconds and upwards per CFINDEX call. hence the need to call cfindex via a schedule. but if i do the same cfindex call with 100 rows it may only take 20-30 seconds. this causes a problem when i need to re-index a whole site, (which is infrequent i know) because i cant use my schedule with a one-at-a-time cfindex loop like i could in 6.1 to re-index as its waay to slow (a cfindex on 6.1 was like 1-2 seconds for a 1 row query). So i guess i need to change my schedules so that i minimise the amount of calls to CFINDEX. eg. if i need to re-index 1 row then i call CFINDEX and take the hit in the scheduled task, but if i need to re- index 100 i still have the same number of cfindex calls because i combine the data into 1 big query. Does that sound like a better solution ? Does anyone else on the list notice the performance hit with CFINDEX calls ? maybe its our setup causing the problem... Pat On May 23, 2:21 pm, Raymond Camden [EMAIL PROTECTED] wrote: On 5/22/07, Pat Branley [EMAIL PROTECTED] wrote: So do you know of a 'best practice' methodology for dealing with verity indexing of database content ? I'm not aware of such a document. I know that when I teach Verity I talk about the importance of keeping your index in sync with your db. So for example, any Create/Edit/Delete operation needs to ensure it updates th ecollection. Ive never really seen anywhere that does a mass update of the index since most of the time only 1 record changes per cycle of a scheduled task. Right, and that _should_ work fine. But you were doing a mass index one row at a time, right? normally we would update the index after you have saved a record to the database. However, because of the overhead of calling cfindex, its too slow to call on page save. So we call it via a scheduled task. the schedule normally looks like (abstractly): Again though - didn't you say you were doing _lots_ of updates? If you update your index with ONE record, it should be fast. Not super fast, but fast. -r -- === Raymond Camden, Camden Media Email: [EMAIL PROTECTED] Blog : ray.camdenfamily.com AOL IM : cfjedimaster --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: cfindex on CFMX7 with 1 record queries
I've done a few presentations on Verity in the past. CF7 had a major update, hence the reason there are a lot of new features in CF7 in terms on Verity. As for this particular point, I guess I woul dhave always assumed that one update versus N would be quicker. On 5/22/07, Pat Branley [EMAIL PROTECTED] wrote: Hi All After all this time (see my initial post on 22 Nov 2005!) i think i may have discovered why the verity indexing process appears painfully slow in CF7 vs CF6.1. See the thread below: http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?catid=143threadid=1009996enterthread=y Notice that the post by the team member mentions the 'record-at-a- time' vs bulk processing of verity 5.5. After some initial testing with my indexing schedules i can see this working. For example, in our systems after the user updates a record the system then puts a flag in the table that this record requires re-indexing. The scheduled task then loops over all the records that require re-indexing calling CFINDEX separately on each row. If i change this process to call CFINDEX only once for all the rows in the whole table, the time taken is slightly slower than that of a cfindex on 1 row, but massively faster than the combined time to process all the rows individually. What is slightly annoying is that its taken a year for me to find this information. Nobody seems to blog much about verity and theres no recommended way you should work with CFINDEX listed in the docs. Considering this is a pretty big change from the functionality in CF6.1, I would have thought someone would have documented it somewhere? Or i have I just missed some blindly obvious piece of information thats on page 1 of cfdocs ? Pat -- === Raymond Camden, Camden Media Email: [EMAIL PROTECTED] Blog : ray.camdenfamily.com AOL IM : cfjedimaster --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: cfindex on CFMX7 with 1 record queries
On 5/22/07, Pat Branley [EMAIL PROTECTED] wrote: So do you know of a 'best practice' methodology for dealing with verity indexing of database content ? I'm not aware of such a document. I know that when I teach Verity I talk about the importance of keeping your index in sync with your db. So for example, any Create/Edit/Delete operation needs to ensure it updates th ecollection. Ive never really seen anywhere that does a mass update of the index since most of the time only 1 record changes per cycle of a scheduled task. Right, and that _should_ work fine. But you were doing a mass index one row at a time, right? normally we would update the index after you have saved a record to the database. However, because of the overhead of calling cfindex, its too slow to call on page save. So we call it via a scheduled task. the schedule normally looks like (abstractly): Again though - didn't you say you were doing _lots_ of updates? If you update your index with ONE record, it should be fast. Not super fast, but fast. -r --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: cfindex on CFMX7 with 1 record queries
when you say not super fast, but fast how long would you expect a 1 record index to take ? on our servers this can be 10 seconds and upwards per CFINDEX call. hence the need to call cfindex via a schedule. but if i do the same cfindex call with 100 rows it may only take 20-30 seconds. this causes a problem when i need to re-index a whole site, (which is infrequent i know) because i cant use my schedule with a one-at-a-time cfindex loop like i could in 6.1 to re-index as its waay to slow (a cfindex on 6.1 was like 1-2 seconds for a 1 row query). So i guess i need to change my schedules so that i minimise the amount of calls to CFINDEX. eg. if i need to re-index 1 row then i call CFINDEX and take the hit in the scheduled task, but if i need to re- index 100 i still have the same number of cfindex calls because i combine the data into 1 big query. Does that sound like a better solution ? Does anyone else on the list notice the performance hit with CFINDEX calls ? maybe its our setup causing the problem... Pat On May 23, 2:21 pm, Raymond Camden [EMAIL PROTECTED] wrote: On 5/22/07, Pat Branley [EMAIL PROTECTED] wrote: So do you know of a 'best practice' methodology for dealing with verity indexing of database content ? I'm not aware of such a document. I know that when I teach Verity I talk about the importance of keeping your index in sync with your db. So for example, any Create/Edit/Delete operation needs to ensure it updates th ecollection. Ive never really seen anywhere that does a mass update of the index since most of the time only 1 record changes per cycle of a scheduled task. Right, and that _should_ work fine. But you were doing a mass index one row at a time, right? normally we would update the index after you have saved a record to the database. However, because of the overhead of calling cfindex, its too slow to call on page save. So we call it via a scheduled task. the schedule normally looks like (abstractly): Again though - didn't you say you were doing _lots_ of updates? If you update your index with ONE record, it should be fast. Not super fast, but fast. -r --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
