Insert large set of data into a query without a loop in Coldfusion

Question

We have a page that has recently been timing out sporadically, It is really old code I never bothered updating, but now is the perfect time I guess.

What this code does is keep track of who gets an email sent to them from our email blast. The code:

1. Checks to see if they got the email already
2. Sends the email
3. Inserts into the email sent table that the member got this email.

Problem is that the query that marks the email as sent is inside a cfloop. I looking for advice on a better way to do this without putting the query into the cfloop, maybe some examples too.

It loops through about 6000 (a little under) contacts. Code is below.

<cfloop query="MembersWhoGetEMail"> 
    <cfquery name="queryname">
        INSERT INTO EmailsSent
            (EmailID, MemID)
        VALUES
            (<cfqueryparam value="#EmailID#" cfsqltype="CF_SQL_INTEGER">, 
             <cfqueryparam value="#MemID#" cfsqltype="CF_SQL_INTEGER">)
    </cfquery>
</cfloop>

The server we have is running MS SQL 2012

Answer 1

Looping over the data like that will grind the application server and cause the timeouts you describe.

Try this instead. Create a stored procedure and let the DB server do all the work rather then get the data from the server pull it into the Cold Fusion application server then send it back to the DB

I would use a cursor in my stored procedure

set nocount on;

declare @email varchar(20);
declare @memid int;

declare insertCursor cursor fast_forward for
(your first select statement here)

open insertCursor
fetch next from insertCursor into @email, @memid

while @@fetch_status = 0
Begin
 INSERT INTO EmailsSent
            (EmailID, MemID)
        VALUES
            (@email, @memid)

fetch next from insertCursor into @email, memid
end
close insertCursor
deallocate insertCursor

Then in my cfm page

<cfstoredproc procedure="myProcedure" datasource="myDSN">
  <cfprocresult name="myResult"> 
  </cfprocresult>
</cfstoredproc>

This will allow your DB to do all the work while all the CF server is doing is waiting for it to complete.

**Edited- After discussing with Leigh my answer is way off base. In sorts. This probably does needs to be done on the server as much as possible. If your server is set up to send email then it could be done completely on the server. Using the sp_send_dbmail stored procedure

Sends an e-mail message to the specified recipients. The message may include a query result set, file attachments, or both. When mail is successfully placed in the Database Mail queue, sp_send_dbmail returns the mailitem_id of the message. This stored procedure is in the msdb database.

And then you could schedule a job to run the procedure when required. The same as a CF task.

Answer 2

The big question is whether or not the MembersWhoGetEMail query is (or can be) written as a single SQL query. If so, then a INSERT INTO SELECT query is your answer. If not, then writing to a CSV and doing a BULK INSERT is probably your best best.

INSERT INTO SELECT

I'm going to make up a simple query for MembersWhoGetEMail, but it can be any query that return the data correctly for the INSERT portion.

<cfquery>
 INSERT INTO EmailsSent
     (EmailID, MemID)
 SELECT
     EmailID,
     MemID
 FROM
     thisTable
 JOIN
     anotherTable
  ON
      thisTable.field = anotherTable.field
  WHERE
      1 = 1
</cfquery>

The SELECT clause can be any SQL at all that returns the correct number of columns and in the matching order to the columns being inserted. The data types must match the inserted data, so if they don't then you will need to CAST() or CONVERT() on those columns in the SELECT statement.

This will run much faster than looping over a query and doing multiple inserts.

If you cannot get it into a SQL query, then you will have to do a BULK INSERT.

BULK INSERT

In that case, you will write a CSV file and run a BULK INSERT on it.

BULK
INSERT EmailsSent
FROM '#FullPathToFile#'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

This will be a file with commas separating every value and a line return for every row with the first row being the names of the columns in your table.

In your example, you are just inserting integers, but if you were inserting text, then you would also need to take care that your text didn't contain carriage returns or commas or else it would mess up the CSV file (if so, you might have to choose different delimiters).

All in all, INSERT INTO SELECT is the better solution if you can get your data from a single SQL query.

Answer 3

You can create a large select list and insert in batch. You may need to loop through in batches of a thousand. 6000 probably won't work because you can only pass 2100 parameters in a query

<cfquery>
  INSERT INTO EmailsSent (EmailID, MemID)
  <cfloop query="MemberWhoGetEmail">
    <cfset rowNumber = totalRow + indRow>
    <cfif rowNumber LTE totalRows>
      SELECT <cfqueryparam value="#MemberWhoGetEmail.EmailID#" cfsqltype="CF_SQL_INTEGER">, 
      <cfqueryparam value="#MemberWhoGetEmail.MemID#" cfsqltype="CF_SQL_INTEGER">
      <cfif indRow NEQ rowsPerInsert AND rowNumber NEQ totalRows> UNION ALL</cfif>
    </cfif>
  </cfloop>
</cfquery>

Alternatively as Dan suggests, you could modify the loop to eliminate the if statement.

<cfquery>
  INSERT INTO EmailsSent (EmailID, MemID)
  <cfloop query="MemberWhoGetEmail">
    SELECT <cfqueryparam value="#MemberWhoGetEmail.EmailID#" cfsqltype="CF_SQL_INTEGER">, 
    <cfqueryparam value="#MemberWhoGetEmail.MemID#" cfsqltype="CF_SQL_INTEGER">
    UNION ALL
  </cfloop>
  SELECT null, null WHERE 1 = 3
</cfquery>

Answer 4

How about writing the fields into a csv file with <cffile>, then BULK INSERT?

BULK 
INSERT Employee
FROM 'F:\\MyPublis\\CSVTest.csv' --location with filename
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

http://www.codeproject.com/Tips/775961/Import-CSV-or-txt-File-Into-SQL-Server-Using-Bulk