Speed
Erik Bryn
I noticed that a query which returns ~1500 rows takes a significant
amount of time to process all the rows returned. The data from the
query itself returns in <150ms (yes, it could be faster, and will be),
but it's taking 2.31s for the ActiveRecord call to return.
Anyone else experiencing the same kind of speeds? I imagine all the
extra overhead isn't probably in the driver itself, but within the
dependent libraries (ODBC, RubyODBC, DBI).
- Erik
nanda
pretty slow. Then I switched to sqlserver adapter and its 3/4 times
faster, even though it uses dbi underneath. It's still pretty slow but
at least faster than straight dbi. I think you should try some of the
queries using dbi directly and see what kind of performance you get.
-nanda
Ken Collins
Without digging into a great amount of detail my comments:
The adapter has always been a "dancing elephant" to me. By dancing, I mean it's doing what it should for ActiveRecord and passing all the tests. Because we have to jump thru some hoops to make that happen, constant string analysis on the queries, regexs, etc. it could be considered more expensive than other adapters and hence the term an "elephant". I have done what I can when I noticed big slow downs, like caching column information, but in general it is what it is and that for 95% of the user base it is just fine and non-noticable. Obviously my work uses it in production under some real heavy load and I have not yet had any complaints. I'm open to tested patches for speed tho if any optimizations are found.
I'd say that a lag like that in your case sounds like something else outside of the adapter. For instance, if those 1500 rows have big text columns and "the wire" is slow to transfer all that data from the SQL Server box to the client, then your looking at a) a network issue or b) just doing too much. Really, only you can tell what is happening, but that's just my first guess.
- Ken
> --
> You received this message because you are subscribed to the Google Groups "Rails SQLServer Adapter" group.
> To post to this group, send email to rails-sqlse...@googlegroups.com.
> To unsubscribe from this group, send email to rails-sqlserver-a...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/rails-sqlserver-adapter?hl=en.
>
Erik Bryn
I've ran the query directly through DBI and simply fetched all the
results into an array of hashes. The time that took was 1.92s versus
2.07s using ActiveRecord. My conclusion is DBI is slow, maybe because
it has to build the results objects using pure Ruby.
I suspect JRuby and ActiveRecord-JDBC w/ jTDS may provide better performance.
- Erik
Erik Bryn
from the C library- RubyODBC. The query wasn't much faster than DBI. I
guess RubyODBC is the culprit.
Ken Collins
I would enjoy hearing more when you find out. I'm curious if the data from query is related to the speed to, for instance text columns, etc.
- Ken
Joe Rafaniello
I have heard generalizations of the native Linux postgresql gem &
active record feeling a few times faster than unixodbc, freetds,
rubyodbc, and activerecord sqlserver adapter on the same code but have
not had a chance to do serious comparisons and delve deeper.
It would great to know that I'm not the only seeing this and if
there's any areas in the config of the layers or adapter to target.
Joe
Erik Bryn
faster than DBI. It definitely is faster.
Here are times for the same query (which returns about 1500 rows)
being ran using all three libraries.
ODBC: 0.140000 0.020000 0.160000 ( 0.580160)
DBI: 0.370000 0.020000 0.390000 ( 0.782248)
AR: 1.000000 0.050000 1.050000 ( 1.267606)
As you can see, ActiveRecord is a lot slower. DBI slows down things too.
- Erik
Erik Bryn
directly. The big query (~1500 result rows with large text fields) has
seen a 28% improvement in speed with this change. I tested some simple
queries and they're 40% faster than the adapter using DBI. My app
definitely feels a lot snappier now.
- Erik
Ken Collins
Erik / All,
> I modified the adapter to remove the DBI dependency and use RubyODBC
> directly. The big query (~1500 result rows with large text fields) has
> seen a 28% improvement in speed with this change. I tested some simple
> queries and they're 40% faster than the adapter using DBI. My app
> definitely feels a lot snappier now.
This is great news and I'd be happy to stripping out the DBI/DBD-ODBC stuff. Here are my comments to your earlier questions off list.
> Just curious how you'd feel about removing DBI as a dependency for the
> adapter. Working directly with RubyODBC would definitely optimize the
> adapter. I've started hacking on the adapter code to remove the DBI
> requirement.
When I took over the development of the adapter I knew that I did not want to intertwine low level transport code to the adapter's code. I felt it was like erubius ruby for ruby's sake. So I never used ODBC's transaction support, etc. I tried to alway just use SQL Server SQL code to get things done and abstract DBI away as far as I could.
The goal has always been to allow different transports to be used. For instance, I've heard from the IronRuby guys before about .Net's newer/native ODBC transport and always told them that it would be easy to incorporate a :mode to allow for that because everything was always abstracted away. Alas I never heard back from them. That aside, the point is still the same, transport agnostic is where I am at and I have no issues with that at all.
Now here is the important part. I'll take a look at your code and run the test suite against 2000/2005/2008 as I do and see where that get's us. I'll also just benchmark the test suite just for the fun of it. I propose that if this works out we just kill the DBI/DBD-ODBC dependencies and core extensions. I have no code anywhere in my big fat day job app that uses this stuff and for good reason. The core adapter for us should just be straight rubyODBC and if anyone ever wants to add different transports like straight FreeTDS, .Net ODBC, etc, then that will just be a different mode. So in short, I say we do this if tests pass. Thoughts? Is anyone out there hooking into DBI?
- Ken
Erik Bryn
Glad you're receptive to the idea of removing the DBI layer. I've been
testing my modified adapter quite a bit and haven't found any ideas.
On Thu, Feb 4, 2010 at 6:25 AM, Ken Collins <k...@metaskills.net> wrote:
> Now here is the important part. I'll take a look at your code and run the test suite against 2000/2005/2008 as I do and see where that get's us. I'll also just benchmark the test suite just for the fun of it. I propose that if this works out we just kill the DBI/DBD-ODBC dependencies and core extensions. I have no code anywhere in my big fat day job app that uses this stuff and for good reason. The core adapter for us should just be straight rubyODBC and if anyone ever wants to add different transports like straight FreeTDS, .Net ODBC, etc, then that will just be a different mode. So in short, I say we do this if tests pass. Thoughts? Is anyone out there hooking into DBI?
I'm really curious to hear about the test suite benchmarks. The full
test suite runs in 3m32s on my machine. I forgot to mention - there is
one failing test related to bigints.
I hope you see as large of a performance boost as I have. I'm really
excited for how fast my apps are performing now. It feels at least
like a 30% increase in performance across the board.
My patch was written with the intent of ODBC being the sole transport,
so we'll have to fix that in order to accommodate the ADO folks, who
will still need DBI.
I've toyed with the idea of writing a FreeTDS Ruby extension, looked
for FreeTDS driver code for other languages. My C-fu is rather weak,
but I imagine that'd give us the best performance we could ask for.
- Erik
Erik Bryn
> Ken-
>
> Glad you're receptive to the idea of removing the DBI layer. I've been
> testing my modified adapter quite a bit and haven't found any ideas.
>
Sorry - I meant to say I have been testing my modded adapter and
haven't found any *problems*.
- Erik
Ken Collins
All tests passing in 2000. My tests typically take around 134 seconds or so to complete, under that new code without DBI, it's on average of 117 seconds, so that's about 13% better?
> I'm really curious to hear about the test suite benchmarks. The full
> test suite runs in 3m32s on my machine. I forgot to mention - there is
> one failing test related to bigints.
> I hope you see as large of a performance boost as I have. I'm really
> excited for how fast my apps are performing now. It feels at least
> like a 30% increase in performance across the board.
>
> My patch was written with the intent of ODBC being the sole transport,
> so we'll have to fix that in order to accommodate the ADO folks, who
> will still need DBI
Well I'm for the core of the adapter doing straight ODBC and I do not see an issue for those that want to use DBI in their other stuff, they can feel free to do so, include it in their project, etc. Relying the adapter to do so will not be happing soon.
I am making changes to the connection cases and testing things, give me a few days to work this over. I do not want ODBC being the only mode and I want to change the code a bit around to not force that.
- Ken
Ken Collins
Erik (anyone else),
> I've toyed with the idea of writing a FreeTDS Ruby extension, looked
> for FreeTDS driver code for other languages. My C-fu is rather weak,
> but I imagine that'd give us the best performance we could ask for.
Anyone still toying around with writing a ruby wrapper for FreeTDS?
- Ken
Erik Bryn
What would be the big win in having a Ruby FreeTDS driver -
performance? Unicode support? I wonder if just contributing patches to
Ruby-ODBC would a better alternative.
- Erik
Clifford Heath
> Anyone still toying around with writing a ruby wrapper for FreeTDS?
I had another attempt with FFI recently, but the combination of sparse
documentation for FFI and the complexity of some of the FreeTDS
structures (even a string is a struct) beat me back - I just didn't have
long enough to spend on it.
I could do it easily in C, but that would have to be done differently
for
each Ruby variant (except JRuby, which is covered already), and I'm
not prepared to, for example, shut Rubinius out.
However, for someone who doesn't care about that, it's still a pretty
achievable option.
On 21/08/2010, at 6:38 AM, Erik Bryn wrote:
> What would be the big win in having a Ruby FreeTDS driver -
> performance? Unicode support? I wonder if just contributing patches to
> Ruby-ODBC would a better alternative.
Every layer of conversion is another layer where bugs can hide, where
conversions can change results, and where functionality can be lost or
hidden. Direct FreeTDS support would be awesome, getting rid of all
the opinions and metamodels involved in DBI, ODBC, etc...
Clifford Heath, Data Constellation, http://dataconstellation.com
Agile Information Management and Design.
Ken Collins
I've only found one thing wrong with RubyODBC and that is in the works. I mention a ruby FreeTDS wrapper for pure speed and curiosity only. When it comes down to it, the transport mode for the adapter only needs two interfaces, a #do and #run equivalent. The #run being the more complex assuming there is something like a statement handle that needs to iterated over for column and row information.
- Ken
Erik Bryn
working on a Ruby FreeTDS driver. I contacted him and he posted his
current code to GitHub:
http://github.com/dparnell/freetds4ruby
He mentioned he originally was using libtds, but apparently that
interface isn't available anymore, so the code needs to be ported to
libct or libsybdb.
- Erik
KD.Gun...@zwick-edelstahl.de
odbc and it Would Be interesting to have a stripped down Version (As
the C sources are hard to Read with Functions over 400+ Lines )
But a FreeTDS wrapper Runs only on Linux, what about the Windows
Users?
There are several People Running Ruby-odbc without AR, so I Would
prefer to contribute to Ruby-odbc
Ken Collins
Sent from my iPad
Erik Bryn
managed to get it to run queries and return results (limited to string
columns right now).
I'm trying to figure out how to convert the non-string libct-based
data types (ints, bools, floats, etc.) to C data types so I can pass
them along to Ruby. It sounds like Clifford may have some experience
with this stuff - any help with this would be appreciated! It's
probably something really easy, I'm just not that experienced with C.
Once the data type conversion stuff is figured out we should have
something functional enough to test the adapter on. It could be by the
end of the weekend!
- Erik
Erik Bryn
Erik Bryn
working now... Latest code is up at:
http://github.com/ebryn/freetds4ruby
I've also added a FreeTDS mode in my adapter fork:
http://github.com/ebryn/activerecord-sqlserver-adapter/tree/2-3-stable
So, amazingly, I've managed to get this new FreeTDS adapter working
inside Rails...
I'd be interested to hear if anyone else can build it and give it a try...
- Erik
Ken Collins
Exciting! I'll take a look today. Will also be in the IRC channel if you want to chat. I'm there most of the time now.
- Ken