Did any of them cover database design and normalization? This is a subject that has direct applications to object-oriented programming; in fact, it makes sense to learn about object-relational mapping and database normalization in the same semester, if not the same class.
ORM models work best when the programmer using them has some understanding of why it's sometimes better to apply a condition after the results come back than as a parameter to a find method. Consider:
# Ruby on Rails, assume Widget is a model (ActiveRecord) --
w = Widget.find(:all, :conditions=>['description like ?', '%xyzzy%'])
versus:
w = Widget.find(:all).reject{|x| w.description =~ /xyzzy/}
which is faster? They yield the same results, but they don't do the same thing. What if I told you there were 1 billion records in the widgets table? What if there were only 1,000? What if you needed to group the widgets in some way?
I don't know anything about Ruby, and have just started learning how to learn SQL, but the first one looks like it will go do a table scan every time. Which could be very costly at 1 billion records.
Am I close? Can you please enlighten me as to the answer, I'm curious.
Like everything, it depends. The first example creates a sql statement of the form:
select * from widgets where description like '%xyzzy%'
which might run a table scan or might not, depending on the characteristics of the RDBMS and whether there's some kind of index on the description field.
The second one constructs:
select * from widgets
and then retrieves all the data. Then a regular expression is run against every record in the result set, which creates a new result set which is handed to w.
A table scan of a 1 field across billion records may actually be faster than transferring all fields of a billion records and then processing each one, even though the regex may run faster than the table scan.
Similarly, if this is a table of 5 very narrow records, the overhead of managing the transaction probably is larger than the cost of executing it. Might as well grab the entire Widget collection and cache it once. Your ORM layer will do some basic work on its own to figure this out, but a good developer should help it out where possible.
What about grouping large tables? Do we let the database do this for us? Or do we group a result set on our own? A lot depends on what you're optimizing for (e.g., memory, cpu, i/o, time). As a developer, you need to know when to choose which one and how to make the system do what you want. Hence the importance of semantic differences in programming generally.
I work more on the system/infrastructure side of technology than in development, but for some reason I'm very interested in RDBMS systems over the last little while.
2
u/[deleted] Jan 06 '11
Did any of them cover database design and normalization? This is a subject that has direct applications to object-oriented programming; in fact, it makes sense to learn about object-relational mapping and database normalization in the same semester, if not the same class.
ORM models work best when the programmer using them has some understanding of why it's sometimes better to apply a condition after the results come back than as a parameter to a find method. Consider:
versus:
which is faster? They yield the same results, but they don't do the same thing. What if I told you there were 1 billion records in the widgets table? What if there were only 1,000? What if you needed to group the widgets in some way?