[thelist] Effcient ASP Script?

[Wade Armstrong]

on 11/25/02 12:45 PM, Adam Slesinger at badlanners at hotmail.com wrote:
> I am writing a page that selects all records with company names and then
> updates the type of those records if they are the first 2000 records.  The
> database has a total of 13,000 records.  When I run this on my server, it
> takes a long time, and then times out.
As others have asked, why the first 2000 only? Do you want to eventually
update all 13,000 records, only do them in chunks? Why do you want to ignore
the other 11,000?

If you just wanted to update all the records, you could do
UPDATE registrants SET type=1 WHERE company <> ''
or
UPDATE registrants SET type=1 WHERE company IS NOT NULL

Which is effectively what you're doing, except you're only doing it to 2000
records. This update wouldn't require using a Recordset, so you'd save a lot
of memory, and wouldn't require a loop, so you'd save a lot of time. Your
SELECT is pretty ambiguous, anyway - you can't be sure that it will return
the records in the same order every time unless you do an ORDER BY or GROUP
BY, so you could be grabbing any 2000 records with your SELECT and are
effectively updating 2000 random records with your current loop.

Speaking of a Recordset and memory, whew, a Recordset with 2000 rows is
going to be large! Try:

SQL = "SELECT company FROM registrants WHERE company IS NOT NULL"
rstemp. Open SQL, conn
arrtemp = rstemp.GetRows
rstemp.Close
Set rstemp = Nothing

This will grab the contents of the recordset into an array, then close and
destroy the recordset. This saves memory, plus VBScript is way faster
working with arrays than with recordsets. You can loop through the array
with:

For n = LBound(arrtemp) To UBound(arrtemp)
    'Do your stuff here
Next 'n

Note that I specified the columns to fetch in the SQL query. Specifying the
column names, rather than SELECT *, is faster. It's also necessary with
GetRows.

For more on GetRows, try looking it up at 4guysfromrolla.com.

On more thing: When you loop through the array, or Recordset, and issue your
UPDATE, you're updating each record in the database with that company name.
If your company name is not a primary key (or otherwise guaranteed unique),
you're potentially issuing a whole bunch of redundant UPDATE commands. Try:
SELECT DISTINCT company FROM registrants WHERE company IS NOT NULL
which grabs each company name only once, and will at least only issue one
UPDATE for that company name.

Wade


>
> My 2nd question is - is my code efficient?  I wouldn't think that it would
> take so long for this process to run, but it only gets through about 1400
> records before the timeout happens.
>
> Any help is appreciated!
>
> Thanks,
> adam
>
> set conn = server.createObject("ADODB.Connection")
> conn.open Session("connection")
> set rstemp = server.createobject("ADODB.Recordset")
>
> SQL = "SELECT * FROM registrants WHERE company <> ''"
> rstemp.open SQL, conn
>
> do while not rstemp.eof
>   strCount= strCount+ 1
>
>   if strCount <= 2000 and strCount > 0 then
>       'Update Type
>       set typeUpdate= conn.execute("UPDATE registrants SET type= 1 WHERE
> company = '" & rstemp("company") & "'")
>   end if
>
> rstemp.moveNext
> loop
>
> conn.close
> set conn = nothing

--