OK. So based on your example, you already have the list of ID’s to work with. My suggestion would be:
- fire the bulk update query
- use your ids to pull a new query or orm array and loop it to:
— thread each iteration
—fire off your elastisearch update and annotation methods
re-join all threads and wrap up
In the above scenario, you make two trips to the database instead of thousands, plus you take advantage of threading to asynchronously handle the API calls to elastisearch and your annotations.
Yes, you lose the single ORM record postInsert functionality, but you dramatically reduce the overall time and resources it takes to handle the bulk update.On October 2, 2015 at 9:38:49 AM, David Sedeño (@David_Sedeno) wrote:
Well, for example, we need to update a elasticsearch with the new data, anotate the changes (this is not the culprit of the slowness, if we don’t do that the transactions are slow too).
regards,
2015-10-01 20:56 GMT+02:00 Jon Clausen <@Jon_Clausen>:
IMHO that kind of massive operation is worth writing some extra SQL to handle any of those post-inserts. Sure, there’s some duplication of functionality, but it’s probably a necessary evil. Can you give me an example of a postInsert that needs to be done that couldn’t be done in the multi-update query?
On October 1, 2015 at 12:42:37 PM, David Sedeño (@David_Sedeno) wrote:
Yes I know its a lot of load, but ORMExecuteQuery doesn’t fire the ORM events. That’s is my problem… I would need some hack to do the postinsert actions.
2015-10-01 18:17 GMT+02:00 Jon Clausen <@Jon_Clausen>:
Updating thousands of records via iterator is going to be incredibly expensive and you should never put that kind of load on your server for something you could more effectively do in one transaction. In your example below, you’re running thousands of SELECT queries and then thousands of UPDATE queries, each with its own transaction.
You’re better off running a single update query (ORMExecuteQuery()) using an IN (list) statement. You can still transaction the bulk update. ORM is a valuable tool in your toolbox, but you’ll still need SQL to handle issues like you’ve outlined below.
On October 1, 2015 at 11:43:00 AM, David Sedeño (@David_Sedeno) wrote:
Hi,
We have some tasks that applys to a thousands of records. We are migrating to from plain sql to orm.
We need that this updates fires the model orm ‘postupdate’ event so I need to apply transactions individually (can’t use ormexecutequery), but this transactions are really slow compared to an sqlquery (about 10x slow).
for (id in list)
{
obj = EntityLoadByPk(‘myclass’, id);
transaction {
obj.property = X;
}
}
We split the thounsands of items in list of 100 elements, so the list var in the code only have 100 items.
I think that the open transaction/close transaction is very costly, but I don’t know another way to do it that fires the event.
There are another way to do it more efficient ?
Best regards,
David Sedeño
CTO at todocoleccion.net
–
See Lucee at CFCamp Oct 22 & 23 2015 @ Munich Airport, Germany - Get your ticket NOW - http://www.cfcamp.org/
See Lucee at CFCamp Oct 22 & 23 2015 @ Munich Airport, Germany - Get your ticket NOW - http://www.cfcamp.org/
You received this message because you are subscribed to a topic in the Google Groups “Lucee” group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/lucee/yzWC_lprk6U/unsubscribe.
To unsubscribe from this group and all its topics, send an email to lucee+unsubscribe@googlegroups.com.
To post to this group, send email to lucee@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/etPan.560d5c94.6d4d7e7e.1244d%40jonclausen-mbp.local.
For more options, visit https://groups.google.com/d/optout.
–
David Sedeño Fernández
Zoconet SL
david.sedeno@todocoleccion.net
Tel. 657 384 329
See Lucee at CFCamp Oct 22 & 23 2015 @ Munich Airport, Germany - Get your ticket NOW - http://www.cfcamp.org/
See Lucee at CFCamp Oct 22 & 23 2015 @ Munich Airport, Germany - Get your ticket NOW - http://www.cfcamp.org/
You received this message because you are subscribed to a topic in the Google Groups “Lucee” group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/lucee/yzWC_lprk6U/unsubscribe.
To unsubscribe from this group and all its topics, send an email to lucee+unsubscribe@googlegroups.com.
To post to this group, send email to lucee@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/etPan.560d81d1.88f59e6.1244d%40jonclausen-mbp.local.
For more options, visit https://groups.google.com/d/optout.
–
David Sedeño Fernández
Zoconet SL
david.sedeno@todocoleccion.net
Tel. 657 384 329
See Lucee at CFCamp Oct 22 & 23 2015 @ Munich Airport, Germany - Get your ticket NOW - http://www.cfcamp.org/
You received this message because you are subscribed to the Google Groups “Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+unsubscribe@googlegroups.com.
To post to this group, send email to lucee@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/CAPJegvqff3q%2BNZkJSzWYqhSCCpQr003pFVFNHSFZBz%2BxNf4MiA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.