Process recordset in batch of 10

[Guest]

Hi,

I've got a query that returns more than 1000 records.

Now, for each record, there are several methods to be called and much processing to be done.

If I just do

<cfloop query="queryName">

<!--- Invoke all methods here --->

</cfloop>

This will take too long and will cause major performance issues on the server.

Therefore I want to process the recordset in batches of 10 (I've tested with 10 records and it's fine).

i.e. Loop 1st 10 records -> for each record call the appropriate methods, etc. When 1st 10 are finished, proceed to the next 10.

Any idea how to do this please?

Thanks and regards,
Yogesh Mahadnac

[Dan_Bracuk]

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?

[Guest]

Hi Dan,

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?

Regards,
Yogesh Mahadnac

[insuractive]

It seems like you should be able to accomplish most of this in your SQL query with a well thought-out INSERT/SELECT statement.

Something like:

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

[Guest]

Thanks Michael!

I'll give it a try.

Regards,
Yogesh