|
I was considering using BCP to export the data - it
seems to be able to export 1,000,000 rows in a minute or less.
The part I'm not sure of is how to have WiTango
trigger an event to start the export but not have it wait for the database to
respond. I then thought perhaps I would write a procedure on the DB that
would .zip and email the export to the user - but I don't want the WiTango
session waiting for the data to be exported and inevitably time
out.
----- Original Message -----
Sent: Wednesday, August 18, 2004 6:03
PM
Subject: Re: Witango-Talk: Large data
exports with WiTango
Dave,
You don't want 1,000,000 rows being returned to
resultset. You can use custom sql to create an 'export' table with as many
columns as is required, plus an export_status column. Use a second query to
fill the table with the required data. Within a transaction block, retrieve
10000 records, mark the export_status column, and close the transaction block.
Write the data to disk Repeat. The number of records you retrieve will need to
be tuned to the capabilities of the server.
It may be suitable to
consider a cron job that runs every few minutes and builds the file and when
the export table is completely exported, send an email to the user to say the
file is ready for pickup. Alternatively, have the user sit on a page that
refreshes every so often with the file size/ export progress. In general, once
the chunks of data that you are processing get 'large', you need to move users
expectations away from 'on demand' (ie, within 20 seconds), and towards
scheduled processing. It also helps to not make the server totally
unresponsive for other users. 'Large' is just a little more than what the
server can cope with while still meeting service levels.
Be aware of
your settings for QUERYTIMEOUT and ITEMBUFFERSIZE.
You can also use the
various bulk export/import utilities to handle the movement of the data out
the database to the file system. You won't be able to do any processing of the
data though. Once you have a file in the file system, it should be trivial for
the user to download it.
On MS SQL you have either BCP or DTS
Import/Export, and on Oracle, SQL Loader.
Use a batch file to call the
utility, and once it has returned, you can then update the user's html
page.
Regards,
Jason.
On 19/08/2004, at 10:24 AM, Dave
Machin wrote:
I need to write a witango app to export a data set of unknown
size and width. I'm expecting that the export might be 100,000 to 1,000,000
rows and 20 to 40 columns. The client wants an online application to use to
request the data and then have the resulting csv file download to his/her
computer.
I would like to write an application that could wait
indefinitely for the export to be completed by the database (giving the user
an option to cancel waiting) and when the export completes the application
points the browser to a URL of the exported data. Has anyone done anything
like this before?
Dave Machin
E-Mail.
[EMAIL PROTECTED]
Tel. 805.614.0123 x 30
Address: 3201 Airpark
Drive, Suite 104
Santa Maria, CA
93455________________________________________________________________________TO
UNSUBSCRIBE: Go to
http://www.witango.com/developer/maillist.taf
WITH
IMAGINATION
Planning,
Implementation and Management of Web Applications
160
Pacific Highway North Sydney NSW Australia 2060
phone
+ 612
9929 9229 fax + 612
9460 4770
web
- www.wi.com.au
email - [EMAIL PROTECTED]
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
|
