# Dbi -\> sqlrelay -\> mssql performance

**URL:** <https://rubytalk.org/t/dbi-sqlrelay-mssql-performance/6593>\
**Category:** ruby-talk\
**Created:** [27 June 2003 14:47 UTC](https://rubytalk.org/t/dbi-sqlrelay-mssql-performance/6593 "2003-06-27T14:47:36Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Raphael\_Bauduin](https://avatars.discourse-cdn.com/v4/letter/r/90ced4/32.png) [@Raphael\_Bauduin](https://rubytalk.org/u/Raphael_Bauduin)\
**Post date:** [27 June 2003 14:47 UTC](https://rubytalk.org/t/dbi-sqlrelay-mssql-performance/6593/1 "2003-06-27T14:47:36Z")

</div>

Hi,

We have to migrate an mssql database to Postgres and plan to do it with the  
help of Ruby. As we didn’t find DBI modules to access a mssql database, we  
installed a sqlrelay and access it with the correspondent dbd module.

Performance on select queries returning a lot of rows are not great. To  
execute the statement takes 5 minutes when retrieving 10000 rows.

Is there something wrong in our code? (we’re new Ruby converts 😉  
Can we boost performance with some config? From our search on the net,  
ther’s no issue with the speed of DBI, so we hope we can correct our  
problem.

Thanks.

Raph

Here’s the code:

####################### CODE ############################################  
require 'dbi’  
db=DBI.connect(“DBI:SQLRelay:host=blah;port=9000;socket=”,“user”,“pass”)

#prepare statement to send to mssql server  
stmt=db.prepare(“select top 10000 \* from customers”)

#next line takes 5 minutes when query selects 10000 rows.  
stmt.execute()

#We then print some rows  
print “the first row:\n"  
for i in stmt.fetch()  
print i+”,\t"  
end  
print “\n\n”

print “the next three rows:\n"  
for i in stmt.fetch\_many(3)  
for j in i  
print j+”,\t"  
end  
print "\n"  
end  
################## END OF CODE ##########################################

---

<div class="post-metadata">

**Author:** ![Stefan\_Schmiedl](https://avatars.discourse-cdn.com/v4/letter/s/7ab992/32.png) [@Stefan\_Schmiedl](https://rubytalk.org/u/Stefan_Schmiedl)\
**Post date:** [1 July 2003 12:08 UTC](https://rubytalk.org/t/dbi-sqlrelay-mssql-performance/6593/2 "2003-07-01T12:08:55Z")

</div>

> Hi,
> 
> We have to migrate an mssql database to Postgres and plan to do it with the  
> help of Ruby. As we didn’t find DBI modules to access a mssql database, we  
> installed a sqlrelay and access it with the correspondent dbd module.
> 
> Performance on select queries returning a lot of rows are not great. To  
> execute the statement takes 5 minutes when retrieving 10000 rows.

This is _waaaay_ too slow.

I am running an intranet (iis, mssql, ruby via cgi) on Windows  
which is definitely much faster, although I have no hard data  
at hand.

If you can, install ruby on the windows box and use an odbc  
connection to extract the data, this works really good.

> Is there something wrong in our code? (we’re new Ruby converts 😉

usually the time consuming part would be to transfer lots  
of data between the database and the client, but judging by  
the looks, you don’t do this.

Then, of course, how many customers do you have in the database?  
IIRC, using TOP on MSSQL requires you to use some ORDER BY clause,  
too.

regards,  
s.

> **···**
>
> On Fri, 27 Jun 2003 16:39:47 +0200, Raphael Bauduin [raphael.bauduin@be.easynet.net](mailto:raphael.bauduin@be.easynet.net) wrote:
> 
> > Can we boost performance with some config? From our search on the net,  
> > ther’s no issue with the speed of DBI, so we hope we can correct our  
> > problem.
> > 
> > Thanks.
> > 
> > Raph
> > 
> > Here’s the code:
> > 
> > ####################### CODE ############################################  
> > require ‘dbi’  
> > db=DBI.connect(“DBI:SQLRelay:host=blah;port=9000;socket=”,“user”,“pass”)
> > 
> > #prepare statement to send to mssql server  
> > stmt=db.prepare(“select top 10000 \* from customers”)
> > 
> > #next line takes 5 minutes when query selects 10000 rows.  
> > stmt.execute()
> > 
> > #We then print some rows  
> > print “the first row:\n”  
> > for i in stmt.fetch()  
> > print i+“,\t”  
> > end  
> > print “\n\n”
> > 
> > print “the next three rows:\n”  
> > for i in stmt.fetch\_many(3)  
> > for j in i  
> > print j+“,\t”  
> > end  
> > print “\n”  
> > end  
> > ################## END OF CODE ##########################################