my asp db update script is crashing my server.

[keak]

Hi there,
I am trying to update a larget postgres db (the table has around 40,000 records) but it always seems to be crashing when I run the script (I either get a prompt to download the .asp file at the end, or the browser displays the regular page not found error), when I check the db its partinally updated.

I am thinking if its just my system that is not able to handle that amount of query, or if I am doing something wrong in my code that I just can't figure out ....

any input is much appreciated

Server.ScriptTimeout = 36000000
Dim SQL, RS
SQL="select * from membersh order by mem_id "
set RS = sess_conn.execute(SQL)

dim SQL2, RS2, first, last

If not RS.eof then
RS.movefirst

Do

first = LCase (RS ("first_name"))
last = LCase (RS("last_name"))

SQL2="update membersh set login_id='" & first & last & "', login_pwd='password' where mem_id=" & RS("mem_id")
Response.write(SQL2&"<br>")
set RS2 = sess_conn.execute(SQL2)

count = count+1
        Rs.movenext
    Loop until RS.eof
End if

set rs=nothing
set conn=nothing

 

[rac2]

Do you see the SQL2 statement being written over and over again on the web page? I think it would be written about 40,000 times if the loop completed.

The recordset RS2 is not needed because the UPDATE query does not return any rows. If in fact your code is executing, it may be that the RS2 is repeatedly being destroyed and created which is an expensive, time-consuming process.

40,000 records is not a large table for most RDBMSs.

The loop is not necessary either. Simply execute this SQL statement.

UPDATE membersh SET
    login_id = lower(first || last), 
    login_pwd = 'password'

Also see

http://www.postgresql.org/docs/7.3/static/index.html

HTH