Oracle Count(1) vs Count(*)

Well, it might have been an everlasting discussion about which one of these to use, count(1) or count(*).

I guess, this article of Thomas Kyte already clarified the situation long long ago (well, for IT industry 2005 is long ago anyway, especially given the speed at which we are moving.)

Essentially, what askTom says that, count(*) is better than count(1) since count(1) translates to count(*) internally anyway. I wonder then, why would someone want to use count(1) anyway.

There is at least one more step involved in getting to the actual result. And there is another possible tweak, count(1) has to evaluate an expression as well, "count(1) where 1 is not null". Though its a tautology equivalent, it has to be evaluated nonetheless.

Further, there was some misconception about how the result is returned, whether its read from the data dictionary, this view or table or something like that. I dont think so. The result is calculated at the exact run time,when the query is run, and it actually goes ahead and counts the records in the table.

Should set the record straight...

in reference to: Ask Tom "COUNT(*) Vs COUNT(1) on tables with CLOB..." (view on Google Sidewiki)

Developing a Rails application using an existing database

This is the latest challenge for me. A database exists with real data in there, and I have to develop a rails application around that.

Initially we needed the basic CRUD screens for some tables.  Being lazy (i m really proud of that), I set out finding if there a solution that generates the forms (read views) for the existing tables/models.

I have already managed to generate models/schema.rb using another gem. This is called magic_model.  Read more about that here

Then google helped me find this another gem called scaffold_form_generator which generates the necessary views/forms for a given model.  However, there need to be some improvements required on that (I think).  perhaps I would contribute something (if I find out enough on how to do that)

Well, for the moment, I am struggling with handling of the missing special meaning column from the legacy tables. Will continue writing on this...

Making rails talk to SQL Server Express Edition

I had zero exposure to sqlserver before today. With the help of a dba and google :) I finally achieved the communication.  Here's what I did to achieve this -

Make an odbc connection - pointing to the db server, preferably make a sql server authentication user

Install the following gems

1. activerecord-odbc-adapter (2.0)
2. activerecord-sqlserver-adapter (2.3.8)
3. dbd-odbc (0.2.5)
4. dbi (0.4.5)
5. deprecated (2.0.1)

Further, I copied odbc.so and odbc_utf8.so files from http://www.ch-werner.de/rubyodbc/i386-msvcrt-ruby-odbc.zip to .../ruby/1.8/i386-msvcrt even though exact directory was not found in the path. So, the files were eventually copied to ...\Ruby187\lib\ruby\1.8\i386-mingw32

My database.yml file looks like this -

development:
  adapter: sqlserver
  mode: ODBC
  dsn:
  username: myUserName
  password: myPassword

winscp vs cuteftp

I recently had an experience of transferring relatively larger files from a windows box to linux one.

I had some files which were ranging from 2.1 GB to 4.x GB. The company traditionally uses cuteftp to transfer (read ftp) files across servers. So I started with that anyway.

However, all my transfer attempts were failing. After transferring 2 GB of data, cuteftp would end the transfer and give away some or other error message. I figured that this was happening only to files which were larger in size. And this transfer consumed more than hour or so before it failed.

Google couldnt help a lot, so, finally i got down to winscp. And, it worked out so well. Not only the transfers didnt fail, but they finished within minutes, instead of the hours and hours spent by cuteftp.

Perhaps its some setting somewhere in cuteftp that I couldnt find, but the whole experience has left me more inclined towards the open source community.

Thanks a ton guys .. :)

in reference to: Download WinSCP from SourceForge.net (view on Google Sidewiki)

Trying to become a Data warehousing architect... !!!

If someone wants to become a DW Architect, there is a lot of knowledge required.  Some of the different perspectives (one from MS not specifically for DW, rather enterprise) and another on DW specifically..

Although I think its strange already that someone has to think about the kind of questions being asked in such interview, there are things posted on these lines.  i believe its more of a discussion on thought process and approach rather than question-answer session in such an interview. Still, presenting an excerpt, outlining some of the interesting topics that might turn up in a discussion for this kind of position.

1. Describe advantages of the CIF architecture versus the bus architecture with conformed dimensions. Which would fit best in our environment given [some parameters they give you] and why
2. Describe snow-flaking
3. Describe fact-less fact tables.
4. Draw a star schema of our business
5. Describe common optimization techniques applied at the data model level
6. How do you handle data rejects in a warehouse architecture?
7. Describe common techniques for loading from the staging area to the warehouse when you only have a small window.
8. How do you load type 1 dimensions
9. How do you load type 2 dimensions, and how would you load it given our [insert business particularity]
10. How would you model unbalanced hierarchies
11. How would you model cyclic relations
12. What major elements would you include in an audit model?
13. How would you implement trace-ability ?

Questions picked up from http://arstechnica.com/civis/viewtopic.php?f=20&t=320772

One of the Microsoft guys wrote about possible feathers in the cap of an Enterprise Architect -  http://blogs.msdn.com/b/nickmalik/archive/2006/09/09/ea-interviews.aspx