Decay On Net

WARNING: Full of technical terms.

Or rather, I tried to reduce the technical terms. But I seems like instead of making it easier to understand, I made it harder to understand. Oh well.... Just take it as another DK's ranting.

-----------------------------

There was a major error in office today.

Usually, it is ok for us to omit certain field in our SQL selection. That field is not really required if we run the sql script in the correct server. Keyword "IF".

So what happen on Thursday was that someone raised a datapatch and omit that field. And the coordinator made an error and run the script on the wrong server, thus causing wrong rolls to be updated.

Before you start point your finger at PITA, I would like to say is, this is not done by PITA. He did have some involvement actually. He suggested adding an additional field in the SQL query. And by chance, that field actually reduced the number of roll that are wrongly updated. The thing I was wondering is... why didn't he advice them to add another field which will totally prevent error from happening even if someone run in the wrong server. Oh well.... whatever.

Can't really blame whose fault. Everyone's action, if by itself, will not cause an error. It is only when everything combine together and all the conditions are right will it cause an error. I blame Murphy for visiting us. I'm still amazed by how everything fit into the puzzle so nicely that it causes an error. Rightfully it shouldn't cause an error at all. It seems that all the condition are just nice to create this error.

I guess we all have to be more careful. Murphy will strike anywhere, anytime....

---------------

I was rushing to fix the error. To speed up my job, I asked a colleague from another team to help me do an extraction. I gave her 20,000 number and ask her to help extract the records. But she only came back with 1000plus records found. One look and I know something is wrong.

If she gave me 19,000plus records, I can still understand cause not all the numbers will have a record in the database. But 1000 plus is confirm wrong. So I took a look at her SQL query...... HOW the heck did that query run in the first place? She put all the 20k number in a single 'in' statement!!

I can't remember is it oracle can only support 1000 rows in a 'in' statement or the performance of the SQL will drop if exceed 1000 rows. But confirm her method won't work. And I wonder how she managed to get 1000 plus records returned.

Put all 20,000 number in 1 'in' statement? If so easy, I would have done it myself lor.

Instead of asking her to re-do, I did the extraction myself. Insert the 20,000 numbers in a temp table first and use the in statement to refer to the temp table. I guess it would be faster than asking her to re-do the extraction.

Oh, and it is not PITA this time.

The irony about the whole thing is, she just went for a SQL performance tuning course last week. What a waste of money. Should send me to that course instead.

Posted by DK on Saturday, April 21, 2007 at 2:40 AM