SQL Tips - Cross Apply vs Cross Join

I have a query at work with a CROSS APPLY that I found hard to read and incorrectly assumed was under performing. I tried rewriting it using an inner join to a table variable and realized I had decreased the performance four fold. My miscalculation came from my confusing CROSS APPLY with CROSS JOIN. I found a nice definition of the two provided by Pandian Sathappan.

CROSS JOIN
1.A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join.

2.The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table (N x M)

CROSS APPLY
1.The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query.

2.The table-valued function acts as the right input and the outer table expression acts as the left input.

3.The right input is evaluated for each row from the left input and the rows produced are combined for the final output.

Reference:
Pandian Sathappan

www.SQLServerbuddy.blogspot.com  

Some Ruby Rapid Prototyping Notes

How to articles
http://www.akitaonrails.com/2009/1/13/the-best-environment-for-rails-on-windows/#.UZW4q8ogacs

http://rubyonrails.org/download

IDE
http://www.aptana.com/downloads/start

Language Installer
http://rubyforge.org/projects/rubyinstaller/


Package Manager.
This is ruby gems. It seems to be included in ruby 2. You can install from rubygems.org/pages/download

A Text Editor
ftp://ftp.vim.org/pub/vim/pc/gvim72.exe

Rails
Using the command line package manager, issue $> gem install rails

MVC for Ruby, built into rails 3.
http://rubyonrails.org/merb

Add on management is done through Git. You need a tool to get through git. Use the IDE above.
Git Book
http://git-scm.com/book

ORM STUFF
https://www.ruby-toolbox.com/categories/orm
http://stackoverflow.com/questions/3609482/activerecord-talk-to-two-databases

Active Record may not be the best, but it is the best supported and widely used. Sequel may be the best functionally, with Datamapper being in the top three. Below is a basic note from the Datamapper dude on when yo use SQL vs ORM to code modeling.

accepted

I'm the DataMapper maintainer, and I think for complex reporting you should use SQL. While I do think someday we'll have a DSL that provides the power and conciseness of SQL, everything I've seen so far requires you to write more Ruby code than SQL for complex queries. I would much rather maintain a 5 line SQL query than 10-15 lines of Ruby code to describe the same complex operation. Please note I say complex.. if you have something simple, use the ORM's build-in finders. However, I do believe there is a line you can cross where SQL becomes simpler. Now, most apps aren't just reporting. You may have alot of CRUD type operations, for which an ORM is perfectly suited and far better than doing those things by hand. One thing that an ORM will usually provide is some sort of organization to your application logic. You can group code based around each model in the same file. It's usually there that I'll put the complex SQL query, rather than embedding it in the controller, eg: class User include DataMapper::Resource property :id, Serial property :name, String, :length => 1..100, :required => true property :age, Integer, :min => 1, :max => 130 def self.some_complex_query repository.adapter.select <<-SQL SELECT ... FROM ... WHERE ... ... more complex stuff here ... SQL endend Then I can just generate the report using User.some_complex_query. You could also push the SQL query into a view if you wanted to further cleanup this code. EDIT: By "view" in the above sentence I meant RDBMS view, rather than view in the MVC context. Just wanted to clear up any potential confusion. TERMS ruby - The interpreter itself. Run Ruby scripts or statements. gem - Ruby Package Manager. Great for automatically downloading or updating small Ruby modules like XML libraries, web servers, or even whole Ruby programs. irb - Interactive Ruby Prompt. This is an entire Ruby shell that will let you execute any Ruby code you want. You can load libraries, test code directly, anything you can do with Ruby you can do in this shell. Believe me, there is quite a lot that you can do with it to improve your Ruby development workflow [1]. ri - Quick shell access to Ruby documentation. You can find the RDoc information on nearly any Ruby Class or method. The same kind of documentation that you would find on the online ruby-docs. erb - Evaluates embedded Ruby in Ruby Templated documents. Embedded Ruby is just like embedding php into a document, and this is an interpreter for that kind of document. This is really more for the rails crowd. An alternative would be haml. rdoc - Generate the standard Ruby documentation for one of your Ruby classes. Its like Javadocs. It parses the Ruby source files and generates the standard documentation from special comments. testrb and rake. I'm not familiar enough with these. I'd love it if someone could fill these in!

Other DB stuff
http://guides.rubyonrails.org/migrations.html
http://lostechies.com/rayhouston/2008/05/03/connecting-activerecord-to-sql-server/

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/wiki/Using-TinyTds
gem install activerecord
gem install activerecord-sqlserver-adapter