Even without using FOR XML you can output directly to XML from SQL Server... We had a
process where I'm working currently which had been taking 3 hours to generate xml
content to send to another outsourcing company.
The reason is this: In the beginning as you're building the first couple thousand
records of xml content, CF is pretty quick about it, generating each record at under a
second. As the xml string becomes longer and longer, it takes cf longer and longer to
find the end of the string to append the next record. (iirc there's a similar issue
with cffile action="append")... So once you've processed over 10k records it's solidly
taking over 1 second per record and still slowing...
So what'd we do? Generate xml with a view...
For example:
CREATE VIEW dbo.v_xml_departments AS
SELECT '<department departmentid="' + dbo.xmlformat() + '">'
+ CASE LEN(TRIM(ISNULL(othercolumn,''))) WHEN 0 THEN ''
ELSE '<otherelement>' + dbo.xmlformat(othercolumn)
+ '</otherelement>' END + '</department>'
AS xmlinformation
FROM ourtable
dbo.xmlformat is a SQL2k user defined function which does this
set xml = replace(xml,'&','&');
set xml = replace(xml,'''',''');
set xml = replace(xml,'"','"');
set xml = replace(xml,'<','<');
set xml = replace(xml,'>','>');
return xml;
this method gives us complete control over the xml returned by the view, then we use
cf to turn it into a complete xml packet
<cfquery name="rs" datasource="ourdsn">
SELECT * FROM v_xml_departments
</cfquery>
<cfsavecontent variable="xmlpacket">
<?xml version="1.0" charset="utf-8"?>
<departments>
#arraytolist(rs["xmlinformation"],"")#
</departments>
</cfsavecontent>
With 65k records in a reasonably complex schema, this method reduced processing time
to generate the xml from 3hrs to 10 minutes. :) The query takes about 1 minute, the
xml generation (the arraytolist function call) takes about 9 minutes.
ymmv & hth :)
s. isaac dealey 972-490-6624
new epoch http://www.turnkey.to
lead architect, tapestry cms http://products.turnkey.to
tapestry api is opensource http://www.turnkey.to/tapi
team macromedia volunteer http://www.macromedia.com/go/team
certified advanced coldfusion 5 developer
http://www.macromedia.com/v1/handlers/index.cfm?ID=21816
> Did you know you can make SQL return a recordset in XML
> format? Simply
> append 'FOR XML' statement at the end of your SQL select
> statement and
> the result is an XML document. The 'FOR XML' statement
> has 3 modes:
> AUTO, RAW, and EXPLICT. There's also stiplulations on
> what type of SQL
> Select statement you can use 'FOR XML'. I would check SQL
> Server Books
> and search for 'FOR XML clause'. This is for SQL Server
> 2000. Not sure
> if SQL Server 7 supports 'FOR XML'
> HTH - Tom
> -----Original Message-----
> From: Karen Harker
> [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 24, 2003 8:58 AM
> To: [EMAIL PROTECTED]
> Subject: Processing time question
> I'm working on a program that effectively exports data
> from SQL Server
> into an XML file. All I've done is simply hard-code the
> tags, and
> plugged in values based on the appropriate database
> columns. This data
> is set as a variable and then the variable is saved as a
> file on the
> server. Eventually, I would like to FTP that file to
> another server.
> My problem is file size and processing time. I've
> estimated that there
> will be about 9MB of data. I've been successful in
> outputting the data
> into multiple files of up to 100 records each. This
> typically did not
> take very long to process (15-30 seconds). In the end,
> however, we
> would like to have the data in one large file. When I try
> to process the
> entire dataset all at once, it takes much too long (I've
> timed out at
> 1000 seconds!). I've tested it at processing 1000 records
> (there are
> approx 4800 records) and I was able to get 3 sets loaded
> before timing
> out at 500 seconds. So it seems that the processing time
> rises almost
> exponentially with the number of records per file.
> My questions are:
> 1) Why is that?
> and
> 2) What can we do?
> We would like to get this exporting/transfer/importing
> automated as much
> as possible because the data will have to be refreshed at
> least once a
> day, if not more frequently. Also, importing the multiple
> files could
> cause problems with the target resource.
> Thanks.
> Karen R. Harker, MLS
> UT Southwestern Medical Library
> 5323 Harry Hines Blvd.
> Dallas, TX 75390-9049
> 214-648-8946
> http://www.utsouthwestern.edu/library/
> -----------------------------------------------
> To post, send email to [EMAIL PROTECTED]
> To unsubscribe:
> Send UNSUBSCRIBE to [EMAIL PROTECTED]
> To subscribe / unsubscribe: http://www.dfwcfug.org
> -----------------------------------------------
> To post, send email to [EMAIL PROTECTED]
> To unsubscribe:
> Send UNSUBSCRIBE to [EMAIL PROTECTED]
> To subscribe / unsubscribe: http://www.dfwcfug.org
-----------------------------------------------
To post, send email to [EMAIL PROTECTED]
To unsubscribe:
Send UNSUBSCRIBE to [EMAIL PROTECTED]
To subscribe / unsubscribe: http://www.dfwcfug.org
