Archives of the TeradataForum

Message Posted: Thu, 20 Nov 2002 @ 22:15:56 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Jim,

For long running and large answerset queries it is best to persist the resultsets to a table (create table as select ... with data). You only do the "hard" work once, and you can get to whatever page of output very easily by hitting this results table. Hint: Add a csum(1,1) or the like sequence field to your select so you can reliably pick up what records belong on page x of the output - also make the PI of the table this sequence field so that you are assured excellent distribution regardless of the fields in the query).

The above is good for handling large resultsets, however if the query is long running then you are in danger of it timing out your HTTP connection with the webserver, and another approach is needed.

Typically you have to build some infrastructure so you can store the request in a table (the request table), get some non-webserver hosted "daemon" code to frequently read this table and spawn threads to run any newly added queries - perl is great for this sort of thing. Once the query complete the request table is updated with the new request status ("Complete"), and the tablename of the results table.

Meanwhile the user who submitted the query via the web interface has been watching a webpage refresh itself periodically alerting the user to the request status. When the status has been set to complete the CGI script then outputs page one from querying the results table to get records 1 thru 20 (or whatever). Alternatively the user simply checks a "query status" page periodically (or the next day) and clicks on their query when its status is complete to view the results.

This infrastructure approach has the added benefit of users being able to easily schedule reports which are then effectively cached for all users (careful with security though!). It is a bit more complex than simply re-executing the query n times (one for each page), but much more database, webserver and client browser friendly and for this reason it is how most web based query tools work.

Regards,

Duncan Heard

	<Prev	Next>		<<First	<Prev	Next>	Last>>