Hi Jeremy Thanks for the feedback. As usual you were spot on. Here are the original benchmarks:
AR BENCHMARK RESULTS Requests per second: 182.08 [#/sec] (mean) Time per request: 54.922 [ms] (mean) SEQUEL BENCHMARK RESULTS Requests per second: 8.72 [#/sec] (mean) Time per request: 1146.993 [ms] (mean) There *are* a number of date columns in that table, in fact the table has a lot of columns, over 100 in fact and a number of those are date columns. After Jeremy's suggestions: SEQUEL BENCHMARK RESULTS (LIMITED RESULT SET) Requests per second: 87.35 [#/sec] (mean)Time per request: 114.479 [ms] (mean) SEQUEL BENCHMARK RESULTS (TYPE CONVERSION OFF) Requests per second: 180.55 [#/sec] (mean) Time per request: 55.385 [ms] (mean) SEQUEL BENCHMARK RESULTS (TYPE CONVERSION OFF *AND* LIMITED RESULT SET) Requests per second: 188.93 [#/sec] (mean) Time per request: 52.930 [ms] (mean) So turning type conversion off made a massive difference. The cost of this appears to be - well - type conversion. Here is a sample row comprising a SQL Server int, varchar, tinyint, datetime and bit field, in that order. First the row data is printed, and then the class for each object in the row hash: TYPE CONVERSION ON 09:19:23,049 INFO [stdout] (http-127.0.0.1-127.0.0.1-8080-11) 77 Asdasd 1 2011-11-18 11:40:59 +0000 false 09:19:23,049 INFO [stdout] (http-127.0.0.1-127.0.0.1-8080-11) Fixnum String Fixnum Time FalseClass TYPE CONVERSION OFF 09:21:33,232 INFO [stdout] (http-127.0.0.1-127.0.0.1-8080-11) 77 Asdasd 1 2011-11-18 11:40:59.61 false 09:21:33,232 INFO [stdout] (http-127.0.0.1-127.0.0.1-8080-11) Fixnum String Fixnum Java::JavaSql::Timestamp FalseClass It seems the only type that is affected by this is the datetime column. Does this mean if I leave type conversions off I will need to manually covert my Java::JavaSql::Timestamp to a string every time I need to use it? I will try the master branch too at your suggestion. Cheers, Ciaran On Nov 22, 8:45 pm, Jeremy Evans <[email protected]> wrote: > On Nov 22, 5:52 am, Ciaran Archer <[email protected]> wrote: > > > > > > > > > > > Hi there > > > We've been evaluating the use of Sequel with SQL Server, and as poart > > of this we've been benchmarking Sequel against AR. > > > We're using JRuby, and Torquebox as our application server for all > > tests. We are using Sinatra as our web framework and Apache Bench to > > run the tests. A SQL Server 2008 R2 instance is being used for the > > tests. > > > Our benchmark test is very simple, return all rows in a table and > > print them to the standard output. The test table has 72 rows. > > > AR BENCHMARK RESULTS > > > Requests per second: 182.08 [#/sec] (mean) > > Time per request: 54.922 [ms] (mean) > > > SEQUEL BENCHMARK RESULTS > > > Requests per second: 8.72 [#/sec] (mean) > > Time per request: 1146.993 [ms] (mean) > > > I've extracted the relevant Sequel code, > > here:https://gist.github.com/1385695 > > > I am trying to understand why Sequel seems to be doing so poorly and > > I'm hoping someone can tell me where I might be introducing an issue, > > or where I might be able to improve things. > > One area where ActiveRecord can perform better than Sequel is where > you are selecting fields you don't need. This is because Sequel does > typecasting on retrieval, while ActiveRecord does typecasting on > access. You appear to only be using the firstname field in the query, > so you can try: > > def self.get_all > dataset.select(:firstname).filter("clientid > 0") > end > > Since you are also using JDBC, you can try turning off type conversion > in the Sequel jdbc driver: > > @sia_db.convert_types = false > > You can also try the master branch, which has significant speedups for > typecasting in the jdbc driver. > > If I had to guess, I would guess there are date fields in the table > that you are retrieving but not accessing. Ruby's Date class (pre > 1.9.3 stdlib, and JRuby is either 1.8.7 or 1.9.2 stdlib) is > exceptionally slow, and it could cause the difference in performance > that you are seeing. > > In general, if you want to see _why_ something is slow, you need to > profile the code. For MRI, you can use ruby-prof to do this. I know > there are also profilers for JRuby, but I'm not familiar with them. > > Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en.
