[thelist] huge MySQL query in ASP without stored procedures

[Richard Bennett]

On Monday 08 May 2006 20:47, Canfield, Joel wrote:
> our call record db is on MySQL 4.1 so as far as I know we can't use
> stored procedures. any tips on running a 400-line query in an ASP page?
> It was originally a Crystal Reports query provided by the vendor.

>done: http://www.spinhead.com/callquery.txt

hi, 
I also manage these kinds of database, and would never use such an inefficient 
(IMO) selection, especially on a table containing tens of millions of 
records, as it will tie-up your session table for ages.

But... vendor tie-in can be a problem of course...

Normally i would select the traffic required by date from the session table 
into a temp table, as that is a quick select, and then do all the grouping on 
that.

But you can also try this: keep everything as it is, and add:

CREATE TABLE IF NOT EXISTS myresults 

I front of the select statement in the text file.
Then run this from the commandline:

mysql -udbusername -pdbpassword databasename < /yourpath/callquery.txt

all on one line, changing  dbusername, dbpassword and databasename as needed.
This would basically save the results into the table myresults, where you 
could load them into your asp script. 

Just to give you an idea though, you'd probably need to tweak that before it 
works properly.

Cheers,
Richard