This content has been marked as final. Show 4 replies
What you are suggesting is going to take the same length of time and be just as hard on your server.
What are all these methods you are calling and why do they have to have to be done record by record?
I have a program that manages hotel representatives.
Each representative has a number of hotels that he is assigned to, and several agencies can be assigned to each hotel.
We have around 350 hotels and around 1200 agencies in all.
Take the worst case scenario (I've tested with much less than that and I got a JRUN Error).
Let's say that 1 representative does ALL Agencies on ALL hotels.
Therefore you need to loop each hotel and assign each agency to it so that you can record them.
The code attached below shows the basic process that occurs.
When I run it with more than 10 hotels, the program crashes. That's why I wanted to process it in batches of 10 i.e. process the 1st 10 records, send a message to the user that the database is processing the request and then when it completes everything, he gets a message e.g. All records successfully saved and logged.
What do you propose as an alternative solution to process all those records without causing too much performance degradation?
It seems like you should be able to accomplish most of this in your SQL query with a well thought-out INSERT/SELECT statement.
INSERT INTO tbl_rep_hotel_agency
SELECT '#variables.repID#' as id_rep, id_hotel, id_agency
FROM hotel, agency
WHERE id_hotel in (<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#myHotelList#" list="yes">)
AND id_agency in (<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#myAgencyList#" list="yes">)
Without a JOIN keyword, that should give you a cartesian join (every hotel & every agency) which actually seems like what you want.
The only caveat is you need to make sure every field of your destination (INSERT INTO) table is present in the SELECT portion of your INSERT/SELECT statement (though you can use field aliases to get around that requirement if you need to).
I'll give it a try.