Siusic Dot Com

When it comes to technical problem related to my field, I have a tendency to remember and mark down on my notes everything I have question on. Whenever I have time I will come back to them and try to find the answer.

One of the answers I wanted to fine out is a job interview question I was asked when I did a phone interview with First American Title (NYSE: FAF) . That was in May last year when Encore Credit (NYSE: ECR) finished its second round layoff. I wasn’t one of them by the way thanks to my boss.

I did poor in the phone interview. I was walking out of the office building

to my car while I had four members of their DB team waiting on the other side of the phone, and then I slam my car door too hard. I heard they were laughing on the other side. That was kind of awkward for me. Anyway the thing that failed my interview wasn’t that. I believe that was because a technical question. One of their DB team member asked me, if there was a database table that contained one million rows and you needed to updated 100,000 rows of them, how would you finished this task without locking the table?

My first answer to the question was that you can do a “select * into temptable from TheTable (nolock)” to save a copy of the original data. Once you were done you can do a join update to the temporary copy of the table. Once you finished the join update you can build index if there was any and then you can do a rename operation to switch the temporary table to be the production one. Obviously I didn’t know very well the context of the question so they added that the table was accessed and updated constantly by a 24/7 web site application. So I can not save a temporary copy of it because the minute I finish saving it the data already changed.

I thought over it for a while and told them my second answer. That was to break down the 100,000 rows into smaller batches and then do a join update to the table directly. They didn’t tell me whether it was right or wrong and they raped up the phone interview in a hurry after that. I was wondering did I give them the wrong answer. If I was given a third chance to answer the question. I would tell them to do a loop to update the table one record after another so to keep the lock at the row level. I didn’t fine any documentation to answer that question but I think I have presented them with the right concept. That was to keep the update small.

Anyway that might be the question cost my First American Title job. First American Title is huge company and I thought it should be pretty good to work for them. Of course you never know. I am ok with Local.com for now. By the way Local.com stock went up almost 50% today. I have no clue what was going on.

Another interesting question they asked was what was the maximum number of records in the database tables you were dealing with? I was telling them 24 million. If they ask me now I would tell them over a billion. I guess that will certainly impress them very much.

update:
I did some research on the interview question recently and I believe I didn’t provide the best answer althought I think the solution I provided could be a viable soluction. I now believe the best soluction is to specify a lock hint in the update query to avoid the whole table being locked.

Related Posts:

What is Fragmentation and How to Defragment a Table Being Used Consistently?

Task Manager Has Been Disabled by Your Administrator - Malware Behavior?

How to Find a Good Job?

A CommVault Galaxy QiNetix Bug Experienced in Restoring SQL Server Database

T-SQL Query to Get Database Size