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.<x-tad-bigger>
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
</x-tad-bigger><x-tad-bigger>WITH IMAGINATION
</x-tad-bigger><x-tad-bigger>Planning, Implementation and Management of Web Applications
</x-tad-bigger><x-tad-bigger>
</x-tad-bigger><x-tad-bigger>160 Pacific Highway North Sydney NSW Australia 2060</x-tad-bigger><x-tad-bigger>
</x-tad-bigger><x-tad-bigger>phone +</x-tad-bigger><x-tad-bigger>�</x-tad-bigger><x-tad-bigger>612 9929 9229</x-tad-bigger><x-tad-bigger> </x-tad-bigger><x-tad-bigger>fax +</x-tad-bigger><x-tad-bigger> </x-tad-bigger><x-tad-bigger>612 9460 4770</x-tad-bigger><x-tad-bigger>�
</x-tad-bigger><x-tad-bigger>web -</x-tad-bigger><x-tad-bigger> </x-tad-bigger><x-tad-bigger> </x-tad-bigger><x-tad-bigger>email -</x-tad-bigger><x-tad-bigger> </x-tad-bigger><x-tad-bigger>[EMAIL PROTECTED]
</x-tad-bigger>
