NOT IN Vs. NOT EXISTS

June 30, 2010
For some time, I have been pondering the use of NOT EXISTS in my subquery statements.

A friend of mine guided me towards this method of excluding certain records quite a while ago. So I have been using NOT EXISTS but did not quite fully understand the difference between NOT IN and NOT EXISTS.

In my particular situation a query with a four table join needed some optimizing plus certain records excluded from the query.

I played around a bit and found that using NOT EXISTS produced a substantial difference in performance. Using NOT IN on the where clause of the query took in this case 9 minutes and 35 seconds.

Using NOT EXISTS produced the results in exactly 688 milliseconds.

This is a monumental difference.

I also got a very clear understanding of what’s going on when I came across this post: NOT EXISTS vs NOT IN.

While the post ascertains that in certain circumstances, there is no difference between the two, the best thing is to test both. I will forever keep this in mind. Thanks to my colleague Bill, to Gail from SQL in the Wild and a mention to the O’Reilly book Oracle Performance Tuning which also directs you in this path. The book is authored by Mark Gurry and Peter Corrigan.

So this is what we’re talking about:

The fight between not exists and not in!

Who wins this match?

——————————————————————–
Select [columns]

From [table1]

Join [table2] on [table1.column] = [table2.column]
Join [table3] on [table1.column] = [table3.column]
Join [table4] on [table1.column] = [table4.column]

Where condition1 = ‘x’
And table1.column NOT IN ( Select [column]
From [table1] tableAlias
Where tableAlias.column = ‘xvalue’)

That took a whopping 9:35 minutes to complete.

Whereas the query below, took all of 688 milliseconds!
———————————————————————-
Select [columns]

From [table1]

Join [table2] on [table1.column] = [table2.column]
Join [table3] on [table1.column] = [table3.column]
Join [table4] on [table1.column] = [table4.column]

Where condition1 = ‘x’
And NOT EXISTS ( Select ‘xvar’
From [table1] tableAlias
Where tableAlias.column = ‘xvalue’
And tableAlias.column = table1.column)

I think in this case, we know who the winner is!

Advertisements

Scattered Chatter ll

December 14, 2009

Some time ago, I related the following:

My office environment and its upside

I work in an office where cubicles are spread out as far as the eye can see. This environment affords the opportunity to talk to your team mates and hash out project issues without having to go all over the place looking for a specific person.

The Down Side

The down side is all the distractions generated by lots of people either shouting across cubicles, phones ringing, and yes, team mates talking to each other.

Cubicle conference calls are the death knell of productive programming. For quite some time now, I and others have noticed the amount of scattered chatter that breaks our concentration. Once you pay attention to something like this, every time you hear it, not noticing it becomes a task. A very distracting one. Just like a leaky faucet!

What do to about it

If you are in the same boat, here are some ideas that may help.

  1. Put headphones on
  2. Check email twice a day
  3. Tell everyone
  4. Turn spam filtering on
  5. Banish Growl
  6. Create IM office hours
  7. Silence your phone
  8. Eat lunch out of the office
  9. Become an early bird
  10. Clear your desktop

Want to know more, go here: Eliminating Distractions

My current solution

Since then I have obtained at least three pairs of headphones, together with binaural beats for my iPod and other apps for the iPhone! I’m currently using iBrainWaves on my iPhone together with my Bose Noise Cancelling head phones (QC 15).

They are not cheap and they cost even more due to the fact that I bought two other pairs of headphones, one in-ear, another over the ear which was quite effective by just putting them over your ears but not comfortable to wear all day.

The QC 15s are in the middle of the road. Computer and fan noise are noticeably absent. Talk across the cubicle as well as phone ringing is muted.

I have gained some control over my environment once again and can now focus on the task at hand. I was not this sensitive to chatter before. I just seem to loose my concentration while programming, designing, troubleshooting code when conversations around me get going.

I’m curious, is anyone out there is experiencing lost focus and being distracted from the task at hand by your surroundings? What you’re doing about it? Have I become affected with ADD all of a sudden?

If you happen to stumble upon this note, share your thoughts. I’m open to suggestions.

Update: 12/30/2009

My headphone use has increased. The chatter is gone to the point that when I have them on, folks have to come and tap me on the shoulder to get my attention.  That can be good or bad. Will I here the fire alarm when it goes off? Everyone in the office leaves and I will not know about it? 🙂


On Twitter So Far!

November 25, 2009

After some ambivalence I set up my Twitter account as I mentioned on a previous post.

What I would do different? Research a little bit more before taking the plunge. Paying attention to the settings a little more would have saved me some grief. Let me explain.

A couple of things seemed to re-enforce what my feelings of uncertainty. One was my fault. I was not paying attention, yes I was a little exited (Im really worried now, too much of a geek excitement). When I setup my blog integration with Twitter I put what I thought was my user name. Some places my user name is a combination of first name last name (there are quite a few I can come up with).

I put in one of those user name combinations and all of a sudden I have ten followers. How did that happen so fast? Especially since one of the followers was this woman’s photo who represented the NaughtyGirl account that we dont really want to have in our profiles for professional reasons. After rechecking I realized that in Twitter the name I used was for someone else who had the same name combination. Ok, I fixed that and putt my real Twitter name which is @ottonote.

The other day, I mentioned to a couple of my colleagues about my foray into Twitter and they seemed visible upset. All about how it was a big waste of time. Lesson learned, ask what people’s opinion is about Twitter before you tell them your handle, the perception that your just tinkering wasting time is really strong out there. 🙂

Ok, so there is a third thing. Once I was setup and ‘twitting’ I felt pretty good. Simple to use, nothing to it! Now I’m exploring how to best use it. Lo and behold, I get a text message at 1:45 AM, yes I said AM! It was from one of the conference trainers. Now I have to explain to my wife why I’m getting texted at such an ungodly hour by a woman at that! 🙂 Lesson learned, direct Twitter messages go to your text messaging service and e-mail if you allow it. And yes I did click that option.

Controlling when you get messages from your Twitter account is easy, if you pay attention to the settings. Ive now set mine up so I don’t get anything past 10:00 PM and before 7:00 AM. My beauty rest is very important to me.

Another thing I did, after the fact, was research Twitter Etiquette. It is known as Twittequete, yep a new word is born! Googling Twitter Etiquette gives you many results which I think are worth reading. Here are a few of them (the list will grow I’m sure):

Ten Twitter Rules

Twitter Etiquette

Ten Commandments

13 Golden Rules (added 12/28/2009, another excellent post on using Twitter).

Additionally I’ve found more ways to use Twitter professionally, the following are very good posts on the subject. I’m only a couple of years behind! 🙂

Ways You Can Use Twitter

Twitter Toolset

One User’s Experience

Finding a job (Added on January 2, 2010)

How Amy Blankenship from InsideRIA uses social media (Added on January 25, 2010)

So as you may eventually conclude, Twitter is very simple to use, which makes it simple to abuse and do dumb things with it. Already my mistakes when up to four!

I’ve now created lists to group tweets by subject and I feel a little more in control. Like spices in your food, I’ll be using it sparingly so as not to overwhelm myself or others.

There are many soft rules, but one that is really solid and set in stone and you should never violate is this one: never, never, ever say anything that will embarrass you, your family, something about your boss. Remember everything put on the Net is there for ever. And anyone can read it! And some one will and they’ll call you on it! This goes for e-mail, blogs and whatever else we can think of.

Below is my customized Twitter background.


Windows 7 discomfiture!

November 4, 2009

I have been running Windows 7 for quite a while. Beta, RC, RTM and all these months went very smoothly.

For the first time, frustration and Windows 7 came to the fore. I lost my virtual machine. Specifically Windows 7 crashed and was never able to recover. I had antivirus software installed so I do not suspect any electronic skulduggery.

The antivirus software installed on this particular box was McAfee’s Beta 4.0. I did notice that I had some trouble updating the software and Windows would lock up often when trying to update the virus definitions.

Being the I have the OS running on a virtual box may have something to do with it but I chose to quickly re-install as all recovery attempts failed. I have a new install now and everything is back to normal.

I’ll keep track of how many times this happens in the environment I’m working.


Almost 100…

October 1, 2009

That is 97 things a project manager should know.

I recently participated in a webinar that dealt with the subject of what I project manager should know, or at least consider to insure success of his/her projects.

It was quite a lot of information and yet useful. There is a book of the same title: 97 Things Every Project Manager Should Know.  While the presentation in the eyes of some was lackluster, I did my best to try to get the most out of it.

I’m an application developer, but definitely take the role of Project Manager often enough to warrant exploring this subject in depth. Every project has it successes and failures and sometimes the entire project bombs.  Following most tips if not all will most assuredly make our lives easier.

Some things just stand out. The question was asked: Where are the users? In typical fashion we plan, design, code and when we’re ready to unveil our wonderful creation, it surprises everyone. Is not what they expected!  Along the way users should be included and know fully what’s going on. It’s amazing how many times, at least part of the user groups, by circumstance or happenstance are excluded from the design process.

Egos are bruised, folks are upset, and the project needs to be rescued. An application may finally go into production, but at what cost? How much work after going live is devoted to fixing or changing things? That measure is a tell tale sign of what was left out in the design process.

So off to explore those 97 things I go!


Virtual Computing for the Developer

August 13, 2009

One of the many facets of development involves testing you applications, programs, games. Whatever grand program you have created to improve the world (at least your company’s bottom line), needs testing.

Development machines are somewhat different than what users at large may have. In a corporate environment, regular desktops will probably be locked down and many restrictions will be placed on users ability to install, download and many other things.

Having more than one desktop on hand is costly and cumbersome (space wise).

Virtual Computing to the rescue.  I really like using virtual machines. My latest Virtual Machine is running Windows 7. I’m using the Sun Virtual Box. I can use all my peripherals and it’s very stable. I can test my web apps on this machine with different broswers, settings and such. This prevent my development machine from getting ‘corrupted’, so it can do what it does best.

Virtual Box Settings

Virtual Box Settings

Windows 7 running on a Sun Virtual Box

Windows 7 running on a Sun Virtual Box


Pinging your way to productivity

July 10, 2009

Recently I had a bit of trouble connecting to a database from a web server. All parties involved had done their part as far as opening ports, database clients were in place (Oracle) and it seemed that we were good to go.

Using old and tried tools like IpConfig, Tracert, Ping and PathPing came to the rescue. While these are not programming tools per se, they are often used by System Admins and Network Engineers, it does not hurt to know their use.

So on this little discovery I found out that the IP address of the database we were trying to connect to was completely wrong. Once I corrected that, other issues had to be solved.

Now that we knew we had the righ IP Address, we were still not connecting. After delivering some information obtained via Tracert, we were able to determine what the problem was. The ports on the web server had been opened on the wrong subnet IP Address.

You may have your code ready to go but simple old tools like the ones above saved the day.

If you need  a refresher on what this tools do look up this link: Trace Route