Thanks for the response Ilya - It's been over 10 minutes now and it's still running, normally the whole process of selecting the array ref and then updating the one field for each ID in the array ref takes less than 4 seconds.
I will try moving the prepare out of the loop and try bind. I don't think it has to do with DBI/DBD, I think it's more of my logic getting in the way. When I first read about FOR UPDATE , I thought it could be used in one statement. Have the SELECT , FOR UPDATE and the REST all in one query. But the example I found on the net showed the initial query, with the last line 'FOR UPDATE' then a while loop and the update query. Is it possible to write it as one long query? SELECT 'x' FROM 'h' WHERE 'z = g' FOR UPDATE TABLE.T SET 'z' = 'z+g' Thanks zack "Ilya Sterin" <[EMAIL PROTECTED]> wrote in message 000201c20093$bb296540$c06c1d41@unraveln6j6p21">news:000201c20093$bb296540$c06c1d41@unraveln6j6p21... > Well, one way to speed it up would be to get no prepare your statement > inside the loop:-) Rather do prepare outside and bind (call execute) > inside. But don't know how much it will speed it up (it will a lot) but > from the sound of your email it looks as if it will still be slow:-) > Now you need to define what you mean by slow? Not as fast as? What do > you expect? Then you might want to see if Oracle::OCI (if you are using > Oracle) would provide a better solution. > > In my experience slow is dependent on a lot of things, and rarely on > DBI/DBD itself. It's mostly dependent on how tuned is your db, your db > data structures, etc... > > Ilya > > > -----Original Message----- > > From: Zachary Buckholz [mailto:[EMAIL PROTECTED]] > > Sent: Tuesday, May 21, 2002 2:45 AM > > To: [EMAIL PROTECTED] > > Subject: Re: SELECT FOR UPDATE ok, but how to dereference > > > > > > Ok I was able to get it, perldoc perlref showed me. > > > > But this is very very SLOW. > > > > Am I doing it in an inefficient way or is something out of place? > > > > Thanks > > zack > > > > > > sub select_url() { > > > > my $select_query = "SELECT check.url_id AS url_id, > > check.url_timeout, url.url_protocol, url.url, > > url.contact_ids, MD5.MD5 , check.mc_id, check.lc_status, > > check.lc_MD5_status > > FROM monitor_check check > > LEFT OUTER JOIN monitor_url url > > ON check.url_id = url.url_id > > LEFT OUTER JOIN monitor_MD5 MD5 > > ON check.url_id = MD5.url_id > > WHERE ((active = 1) > > AND ((chk_freq + > > UNIX_TIMESTAMP(lc_epoch)) <= ?)) > > OR (lc_status = 0) > > FOR UPDATE"; > > > > my $update_query_statement = "UPDATE monitor_check > > SET lc_epoch = FROM_UNIXTIME(?) > > WHERE url_id = ?"; > > > > my @update_values; > > my ($update_query, $update); > > my $url_id; > > > > while (my $url_list = $dbh->selectall_arrayref($select_query, undef, > > $current_time) > > or die "Can't execute statement: $DBI::errstr") { > > > > @update_values = ($current_time, $url_list->[0][0]); > > $url_id = $url_list->[0][0]; print "$url_id $current_time\n"; > > > > $update_query = $dbh->prepare($update_query_statement) > > or die "Unable to prepare > > update_query_statement: $DBI::errstr"; > > > > $update = $update_query->execute(@update_values) > > or die "Unable to execute update_query: $DBI::errstr"; > > > > } > > > > > > return($url_list); > > > > } > >
