From: Stefan Schmiedl <s@...>
Date: 2003-07-01T21:08:55+09:00
Subject: Re: dbi -> sqlrelay -> mssql performance

On Fri, 27 Jun 2003 16:39:47 +0200,
Raphael Bauduin <raphael.bauduin@be.easynet.net> wrote:
> 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.

> 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 ##########################################