ListFirst, ListRest in SQL Server

So, this has probably been done a million times before, but I needed ColdFusion-style ListFirst and ListRest functions for creating a stored procedure in a database that violates First Normal Form. <sigh/>

Note that the behavior varies slightly from CF. The delimiter is forced to be a comma, and multiple consecutive delimiters are treated as multiple elements. (CF eating 3 commas at a time always annoyed me.)

So, here's the DDL for them:

CREATE FUNCTION [dbo].[listFirst] (@list nvarchar(4000))
RETURNS nvarchar(4000)
AS BEGIN
   DECLARE @pos int
   DECLARE @ret nvarchar(4000)
   SET @pos = charindex(',', @list)
   IF @pos > 0
       SET @ret = left(@list, @pos-1)
   ELSE
       set @ret = @list
   RETURN @ret
END


CREATE FUNCTION [dbo].[listRest] (@list nvarchar(4000))
RETURNS nvarchar(4000)
AS BEGIN
   DECLARE @pos int
   DECLARE @ret nvarchar(4000)
   SET @pos = charindex(',', @list)
   IF @pos > 0
       SET @ret = substring(@list, @pos+1, len(@list)-@pos)
   ELSE
       SET @ret = ''
   RETURN @ret
END

As a side note, it just occurred to me that you can probably work around the multiple consecutive delimiter thing by adding whitespace, then trimming:

<cfloop list="#replace(myList, ",", ", ", "ALL")#" index="item">
   <cfset item=trim(item) />
   ...
</cfloop>

Why didn't I think of that years ago?

Automatically Fix Case Sensitivity Issues

When you're converting from Windows, with a case-insensitive file system, to just about any other operating system, you'll likely run into file naming issues.

If you have a file named MyInclude.cfm, and you try to use it in ColdFusion as <cfinclude template="myInclude.cfm" /> (note the lowercase "m"), this will work fine in Windows, but then fail on the new OS.

I've written a small template to automagically repair most of these problems. and you can download it here: http://www.cfprimer.com/download.cfm?ffFile=_referenceFixer.cfm

To plagarize mercilessly from its comments:

Purpose:

 Fix file and directory name references in files, for transitioning from Windows (with case
 insensitive filenames) to another OS (eg Linux) with case sensitive file names.

 NOTE: If you have two different files (perhaps in different directories) with the same name,
 but different case, this will not work as well as hoped.  (All references will get set to 
 one or the other case.)
 
Use:

 Place _referenceFixer.cfm in a document directory and load.
 Template will start from its current directory and proceed to find all file and directory 
 names in that directory and its subdirectories.
 
 If "updateFiles" is set to True, the files will be rewritten and references fixed.
 
 "fileExtensions" should be set to list the files whose references should be checked
 and (if updateFiles is true) fixed, eg. ".cfm,.html".
 
 Templates beginning with an underscore character ("_") will be skipped.
 
 Do NOT leave this on production servers..!

ColdFusion 8 Professional with Windows IIS7 64-Bit

ColdFusion 8 will run on 64-bit Windows with a 64-bit JRE, but only if you pay for Enterprise.  While the ColdFusion 8 32-bit installer will run on Windows 64, the IIS connector fails miserably.  I worked around this problem by using the IIS Connector from the 64-bit installation with a ColdFusion instance running with its 32-bit installation.

Fist, install the "normal" ColdFusion 32-bit version, but select "Built-in web server (Development use only)" on the Configure Web Servers/Websites screen:

Next, install the 64-bit version of ColdFusion, with the following options:

• On the "Install Type" screen, choose "Developer Edition":
  
  
• On the "Subcomponent Installation" screen, install no complementary services.
  
  
• On the Installation Directory screen, choose "ColdFusion64" (or any dir other than where you installed 32-bit ColdFusion)
  
  
• On the Configure Web Servers/Websites screen, select "All IIS websites" (which is the default).

At this point, you may be able to hit http://127.0.0.1/CFIDE/administrator/index.cfm to configure your ColdFusion instance.  If not, check the following items:

• Run regedt32, and verify that the ImagePath attribute in the ColdFusion 8 Application Server service (HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\ColdFusion 8 Application Server) is set to "C:\ColdFusion8\runtime\bin\jrunsvc.exe"
• Ensure that the "ColdFusion 8 Application Server" service is started.  If there's a problem, open a command prompt, CD \Coldfusion8\bin, and run CFSTART.  Look for errors.
• Open C:\ColdFusion8\runtime\servers\coldfusion\SERVER-INF\jrun.xml.  Search for "JRunProxyService"; make sure the "deactivated" attribute is "false" and that the "port" attribute is "51011".  (If you need to fix either of these, ColdFusion must be restarted.)
• Make sure the 64-bit connector is also pointing to 51011: in the file C:\ColdFusion64\runtime\lib\wsconfig\1\jrun_iis6_wildcard.ini file, the "bootstrap" entry should be set to "bootstrap=127.0.0.1:51011".  If you need to fix this, IIS must be restarted.

Note that you'll never actually start the 64-bit instance, but it must be left in place because you're using its web server connector.

As a side note-- if you try to start ColdFusion 8 Professional in a 64-bit JVM (with either installation type), ColdFusion will revert to a "Developer" license.

Update: If the above doesn't work for you, see Jason Holden's Post on the same subject.

ColdFusion: Fast Query to File Export

A semi-common data processing task is to take the result of a query and write it to a file. People usually do this in ColdFusion something like this:

---

<cfsetting requesttimeout="1200" enablecfoutputonly="yes">
<cfset tab = chr(9)>

<cfquery datasource="Northwind" name="qProducts">
  SELECT p1.ProductID, p1.ProductName, case when p2.productname> 'B' then p2.productName else null end productName2
  FROM Products p1
  CROSS JOIN Products p2
  CROSS JOIN Products p3
  CROSS JOIN Products p4
  WHERE p1.ProductName <> 'foo'
  ORDER BY p1.ProductID
</cfquery>

<cfloop query="qProducts">
  <cfset line = ProductID
        & tab & ProductName
        & tab & ProductName2>
  <cffile action="append"
        file="/out.txt"
        output="#line#"
        addNewLine="yes"
    >
</cfloop>
<cfsetting enablecfoutputonly="no">

---

This works fine for small result sets. In my case, however, I Cartesian-joined the Northwind "Products" table to itself a few times, so that this query returns 35 million rows; the page above runs for ~3 minutes on my laptop and then dies with a java.lang.OutOfMemoryError.

I'd previously written a set of ColdFusion tags that could access JDBC directly from ColdFusion and process the resultset one row at a time, thereby avoiding out-of-memory errors:

---

<cfsetting requesttimeout="1200" enablecfoutputonly="yes">
<cfset tab = chr(9)>

<!--- cf_jdbcQuery will return a java ResultSet object instead of a ColdFusion Query object --->
<cf_jdbcquery datasource="Northwind" name="qProducts"><cfoutput>
  SELECT p1.ProductID, p1.ProductName, case when p2.productname> 'B' then p2.productName else null end productName2
  FROM Products p1
  CROSS JOIN Products p2
  CROSS JOIN Products p3
  CROSS JOIN Products p4
  WHERE p1.ProductName <> 'foo'
  ORDER BY p1.ProductID
</cfoutput></cf_jdbcquery>

<cf_jdbcqueryloop query="qProducts">
  <cfset line = qProducts.ProductID
        & tab & qProducts.ProductName
        & tab & qProducts.ProductName2>
  <cffile action="append"
        file="/out.txt"
        output="#line#"
        addNewLine="yes"
    >
</cf_jdbcqueryloop>
<cfsetting enablecfoutputonly="no">

---

This ran for 20mins (until it hit the timeout),writing about 37MB to the output file. Still... 20 minutes? I think we can do better.

So, I wrote a Java class to do this in a very small loop, to see the difference in speed. The Java source file ("FastResultsetToFile.java") is this:

---

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

/**
* @author Daryl Banttari
* Released to the public domain.  Do with it what you will!
*/
public class FastResultsetToFile {

  public static final byte[] CRLF = { 13, 10 };

  /**
   * Very quickly sends data from an any size query to an output file.
   *
   * @throws IOException
   * @throws SQLException
   */
  public static int exportResultsetToFile(ResultSet rs, String fileName, String delim, String charset) throws IOException, SQLException {
      int rowcount = 0;
      File file = new File(fileName);
      byte[] delimBytes = delim.getBytes(charset);
      byte[] CRLF = "\r\n".getBytes(charset);
      BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(file));
      try {
          ResultSetMetaData rsmd = rs.getMetaData();
          int numCols = rsmd.getColumnCount();
          // iterate over resultset
          while (rs.next()) {
              // write each column
              for (int i = 1; i <= numCols; ++i) {
                  String s = rs.getString(i);
                  if (s != null) {
                      out.write(s.getBytes(charset));
                  }
                  if (i == numCols) {
                      // end of row, write CRLF
                      out.write(CRLF);
                  }
                  else {
                      // write delimiter between data
                      out.write(delimBytes);
                  }
              }
              rowcount++;
          }
      }
      finally {
          if (out != null) {
              out.flush();
              out.close();
          }
          if (rs != null) {
              rs.close();
          }
      }
      return rowcount;
  }

}

---

To compile this, save the FastResultsetToFile.java to disk, then type "javac FastResultsetToFile.java". This will produce a file called FastResultsetToFile.class. Depending on how you have ColdFusion installed, the file should be placed in either:

\coldfusionmx\wwwroot\WEB-INF\classes

or

\JRun4\servers\cfusion\cfusion-ear\cfusion-war\WEB-INF\classes

The ColdFusion page looks like this:

---

<!--- cf_jdbcQuery will return a java ResultSet object instead of a ColdFusion Query object --->
<cf_jdbcQuery datasource="Northwind" name="qProducts">
  SELECT p1.ProductID, p1.ProductName, case when p2.productname> 'B' then p2.productName else null end productName2
  FROM Products p1
  CROSS JOIN Products p2
  CROSS JOIN Products p3
  CROSS JOIN Products p4
  WHERE p1.ProductName <> 'foo'
  ORDER BY p1.ProductID
</cf_jdbcQuery>

<cfset application.fastFileWriter = createObject("java", "FastResultsetToFile")>
<cfset application.fastFileWriter.exportResultsetToFile(qProducts, "/out.txt", chr(9), "UTF-8")>

Done.

---

This completes, writing all 35,153,041 rows into a 1.3 GB file in about 5 minutes. Not bad! If we'd let cf_jdbcQueryLoop complete, it'd have taken nearly 12 hours (based on time and file size ratio).

While you can do a lot right inside ColdFusion, it's important to remember that it's not the right tool for every job.

Multiple ColdFusion Instances NEQ "Faster"

There's an oft-cited technote indicating that running with multiple instances somehow performs much better than running with a single instance, but no testing methodology is cited on the technote.

In my experience load testing, in the general case, the opposite is true-- you get better performance with fewer instances.  In the specific case where you're doing a very large number of very small requests, the single-threaded nature of the datasource connection pool becomes a bottleneck, but it's easier (and less resource intensive) to work around that specific issue by using a modest number of identically-configured datasources, and choosing a datasource randomly at the start of the request.

If you're actually memory constrained, then you're better off using a single large 64-bit instance vs. a bunch of small 32-bit instances.

(I proved via load testing to one customer that their app had 10-15% better throughput when three of their four instances were disabled, and the techie that had pushed for Enterprise + multiple instances said, and I quote, "I don't believe you."  Believe?  A result that changes consistently and predictably when one variable changes somehow involves "belief"?  What's happened to /science/ in this country?)