# Speed up ado updates

**URL:** <https://www.ruby-forum.com/t/speed-up-ado-updates/124240>\
**Category:** Ruby\
**Created:** [December 26, 2007, 11:35pm UTC](https://www.ruby-forum.com/t/speed-up-ado-updates/124240 "2007-12-26T23:35:38Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [December 26, 2007, 11:35pm UTC](https://www.ruby-forum.com/t/speed-up-ado-updates/124240/1 "2007-12-26T23:35:38Z")

</div>

I have a script that uses ado to update a database. The script works  
but it is very slow.

require ‘win32ole’

data\_source = “C:\path\F1Backup.sdf”  
prefix = “E:\user”

begin  
db = WIN32OLE.new(“ADODB.Connection”)  
db.open(“Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data  
Source=#{data\_source}”)  
rescue Exception =\> ex  
puts ex.to\_s  
exit  
end

sql\_string=\<\<End\_of\_String  
Select FilePath from catalog WHERE ParentDirectory Is Null  
End\_of\_String

rs = db.execute(sql\_string)

rs.MoveFirst  
while !rs.eof  
rs.fields.each do |field|  
file\_path = field.value.to\_s.gsub("’", “’’”)  
string = file\_path.split(’\_\_–’)[0].gsub(prefix,’’)  
parent\_directory = string.slice(0,string.rindex("  
")).insert(1,’:’)  
sql\_update = “Update catalog set  
ParentDirectory=’#{parent\_directory}’ WHERE FilePath=’#{file\_path}’”  
puts sql\_update  
db.execute(sql\_update)  
end  
rs.MoveNext  
end

Any ideas on how to make it run faster?

thanks,

Luis

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [December 27, 2007, 1:00am UTC](https://www.ruby-forum.com/t/speed-up-ado-updates/124240/2 "2007-12-27T01:00:44Z")

</div>

On Dec 26, 2007 11:34 PM, removed\_email\_address@domain.invalid [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> db.open("Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data  
> rs = db.execute(sql\_string)  
> puts sql\_update  
> db.execute(sql\_update)  
> end  
> rs.MoveNext  
> end
> 
> Any ideas on how to make it run faster?
> 
> thanks,
> 
> Luis

If you run the script under ruby-prof -p graph \<your\_script\_name.rb\>  
you’ll find out what parts  
of the script take the most of the time. You’ll find out whether you  
should optimize the ruby part, or the query itself (ADO part).

Here you’ll find explanation of the ruby-prof output:

> **[ruby-prof and call graphs](http://on-ruby.blogspot.com/2006/08/ruby-prof-and-call-graphs.html)**
>
> Sorry it's been so long since my last post. I've been on vacation for the last week, and Internet access wasn't a big part of it. I did ge...

Notes:

1. I’d make data\_source and prefix constants (capital letters, this  
might actually hurt the performance – it depends on whether const  
lookup is faster than local var lookup or the other way round – you  
can find out yourself using Benchmark class)

2. You need to escape \ in string literals

DATA\_SOURCE = “C:\path\F1Backup.sdf”  
PREFIX = “E:\user\”

1. you might get some performance gains by making some of the  
gsub/inserts in place (“!” versions – gsub!, you’d need to change  
your code to accomodate for the change)

2. something might be gained by replacing prefix/PREFIX with a Regex  
and fixing its start (PREFIX = /^E:\user\/), when it indeed is a  
prefix  
(again, verify my guess with Benchmark)

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [December 27, 2007, 2:46pm UTC](https://www.ruby-forum.com/t/speed-up-ado-updates/124240/3 "2007-12-27T14:46:42Z")

</div>

removed\_email\_address@domain.invalid wrote:

> I have a script that uses ado to update a database. The script works  
> but it is very slow.
> 
> …
> 
> Any ideas on how to make it run faster?
> 
> thanks,
> 
> Luis

On the select query end of things, you might want to try using the  
Recordset object’s GetRows method, which returns all records as an array  
of columns. Then use Ruby’s transpose method to convert it to an array  
of rows. This may (or may not) be faster than moving from record to  
record via ADO.

rows = recordset.GetRows.transpose  
rows.each do |row|  
…  
end

On the update query end of things, you may want to consider using  
transactions to submit your updates as a batch rather than one at a  
time. With SQL Server and ADO, I think you do this with SQL commands but  
am not certain of the exact syntax. If you have a large number of  
updates/inserts, transactions might save significant time.

These suggestions may or may not help, but I thought I’d pass them  
along.

David

> **[Ruby on Windows](http://rubyonwindows.blogspot.com)**
>
> On using the Ruby programming language on the Microsoft Windows platform

> **[Ruby on Windows](http://rubyonwindows.blogspot.com/search/label/sqlserver)**
>
> On using the Ruby programming language on the Microsoft Windows platform

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [December 27, 2007, 10:57pm UTC](https://www.ruby-forum.com/t/speed-up-ado-updates/124240/4 "2007-12-27T22:57:01Z")

</div>

On Dec 27, 7:46 am, David M. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> > Luis  
> > end  
> > David
> 
> [http://rubyonwindows.blogspot.comhttp](http://rubyonwindows.blogspot.comhttp)://rubyonwindows.blogspot.com/search/label/sqlserver
> 
> –  
> Posted viahttp://www.ruby-forum.com/.

The transactions seem to speed up the process a bit but only on  
smaller database tables. I had a database table with ~10,000 records  
and it was about 30% faster with transactions. However, on a much  
larger table ~400,000 records it seems to take just as long.

thanks,

Luis