Thank you very much. I will try this out.
Karen R. Harker, MLS UT Southwestern Medical Library 5323 Harry Hines Blvd. Dallas, TX 75390-9049 214-648-8946 http://www.utsouthwestern.edu/library/ >>> [EMAIL PROTECTED] 7/24/2003 9:23:58 AM >>> 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 ----------------------------------------------- To post, send email to [EMAIL PROTECTED] To unsubscribe: Send UNSUBSCRIBE to [EMAIL PROTECTED] To subscribe / unsubscribe: http://www.dfwcfug.org
