I use fairly vanilla SQL most of the time so I was shocked to read this post on the PostgreSQL list when I ran into some slowness with a NOT IN query. While my experience is with Postgres, apparently this works with most databases.

If you have a NOT IN with a subquery that returns lots of rows, an EXISTS or NOT EXISTS will usually be faster. And not just a little bit. Like by a factor of 200:1 in my NOT EXISTS case! I am doing batch modification to a new schema and used this query to get a count of the remaining records while I was running my conversion scripts:

SELECT count(*) as total
FROM tblLookupAuthenticateOld
WHERE uidMember NOT IN (SELECT uidMember FROM tblLookupAuthenticate)

The EXPLAIN looks like:

"Aggregate  (cost=9033491.00..9033491.01 rows=1 width=0)"
"  ->  Seq Scan on tbllookupauthenticateold  (cost=1772.98..9033453.73 rows=14910 width=0)"
"        Filter: (NOT (subplan))"
"        SubPlan"
"          ->  Materialize  (cost=1772.98..2304.14 rows=29816 width=36)"
"                ->  Seq Scan on tbllookupauthenticate  (cost=0.00..1510.16 rows=29816 width=36)"

Look at that cost: 9033491.00!! This took about 100 seconds to run on my laptop. It’s because the query is effectively doing a sequential scan for every record in the first table. Ugh. Then I swapped it to NOT EXISTS:

SELECT count(*) as total
FROM tblLookupAuthenticateOld
WHERE NOT EXISTS (select * FROM tblLookupAuthenticate WHERE uidMember = tblLookupAuthenticateOld.uidMember)

The EXPLAIN looks like:

"Aggregate  (cost=247542.82..247542.83 rows=1 width=0)"
"  ->  Seq Scan on tbllookupauthenticateold  (cost=0.00..247505.54 rows=14910 width=0)"
"        Filter: (NOT (subplan))"
"        SubPlan"
"          ->  Index Scan using tbllookupauthenticate_pkey1 on tbllookupauthenticate  (cost=0.00..8.28 rows=1 width=298)"
"                Index Cond: (uidmember = $0)"

This query ran in about 500ms. That’s a “decent” improvement of 200:1. I’m planning to run a search in Eclipse tonight for “NOT IN” to see if there are any places where NOT IN once made sense but not [NOT] EXISTS is more appropriate as the subquery returns more and more rows.

I know I have my patterns - I get my computer set up and for the next 2 years I leave it nearly as-is. Then I get a new machine (Lenovo X61) and it’s time to see what’s new out there.

I did that search four weeks ago when installing Thunderbird and came across some really great plug-ins. With a month of daily use under my belt, I want to recommend the top three for your consideration:

1. Nostalgy - Despite the goofy name, this plugin is genius for people who love keyboard shortcuts. If you’ve ever used Eclipse’s Ctrl-Shift-R shortcut that lets you just start typing any filename in your workspace with an as-you-type filtered list of available files to select from, you will fall in love with this. With any message(s) selected, Nostalgy lets you hit “S” and then start typing a folder name and select from a list of matching email folders. Hit enter and all of the selected messages will be moved to that folder. Need to open a folder quickly? Hit “G”, start typing the name of a folder and hit enter as soon as it’s the first match in the dynamic list. It’s so simple, yet so sweet. For organized folder-users, this is a dream plugin.
2. Recipients Overview - One downside to the X61 is it has a low resolution of 1024×768 when mobile. While I spend most of my time docked, screen real estate becomes a real issue on the road. Thunderbird has a nice feature that lets you condense the header information on a message to take up less space but it doesn’t list all of the people on the TO and CC list. I consider this important information for deciding how to handle and/or reply to an email. Recipients Overview simply enables listing all of the recipients in the condensed view saving you space when you need it without cutting out important details.
3. Slideshow - This one is a little simple but so many times that’s all it takes to make your life easier. If you receive a message with images attached, Slideshow will give you a one-click option to open them in a slideshow format for viewing. This is usually much easier than trying to view them in the confine of the message window but doesn’t require an external image editor. Sweet and simple!

Try them and let me know if you find them as helpful as I do!

Mozilla just released Sunbird and Lightning 0.8, the calendar project of the Mozilla Group. I’ve been using the combination of Thunderbird, Lightning/Sunbird and a GTD tool called ThinkingRock for over a year now. The problem is that my calendar files have grown and the calendar would take sometimes as long as 5-10 seconds to switch from month to month. A real usability drag.

But I just installed Lightning 0.8 into Thunderbird 2.0 and fired it up and not only is the new task pane and conversion between mail, tasks and events really neat, the thing FLIES. I can switch between months now in less than a second and the visual design of the application continues to be polished. The speed has been my only complaint about what is otherwise a great piece of software. Kudos to the team for making big strides over the past year.

If you’re looking for a well-integrated GTD solution, my combination of Thunderbird (mail), Lightning/Sunbird (calendar) and ThinkingRock (GTD tasks/projects) has worked really well. ThinkingRock exports an iCal file of your appointments and tasks which I load into Thunderbird as a remote calendar. Then BirdieSync pulls the appointments and tasks from Thunderbird into my Windows Mobile 5 phone so I have full access to everything at all times. This is, unfortunately, read-only access from the mobile but there is a Pocket PC extension for ThinkingRock that I have not yet tried.

I couldn’t find this easily with TEH GOOGLE so I present to you here, a complete list of the built-in properties that you can access using Ant. If you don’t know what Ant is and you’re a developer, then you probably don’t use deployment scripts and should be whipped. Check out Jim Priest’s Ant Wiki for a boatload of information and helpful links.

You need do nothing else other than wrap these values in with a ${} inside of a build file to use their values. They are a combination of built-in Ant properties and all of System.getProperties():

basedir		the absolute path of the project's basedir (as set with the basedir attribute of <project>).
ant.file	the absolute path of the buildfile.
ant.version	the version of Ant
ant.project.name	the name of the project that is currently executing; it is set in the name attribute of <project>.
ant.java.version	the JVM version Ant detected; currently it can hold the values "1.2", "1.3", "1.4" and "1.5".
ant.home	home directory of Ant
java.version	JRE version
java.vendor	JRE vendor
java.vendor.url	Java vendor URL
java.home	Java installation directory
java.vm.specification.version	JVM specification version
java.vm.specification.vendor 	JVM  specification vendor
java.vm.specification.name 	JVM specification name
java.vm.version	JVM implementation version
java.vm.vendor	JVM implementation vendor
java.vm.name	JVM implementation name
java.specification.version	JRE specification version
java.specification.vendor	JRE specification vendor
java.specification.name		JRE specification name
java.class.version	Java class format version number
java.class.path	Java class path
java.ext.dirs	Path of extension directory or directories
os.name		Operating system name
os.arch		Operating system architecture
os.version	Operating system version
file.separator	File separator ("/" on UNIX)
path.separator	Path separator (":" on UNIX)
line.separator	Line separator ("\n" on UNIX)
user.name	User's account name
user.home	User's home directory
user.dir	User's current working directory

If you want to test these, just use echo:

<echo>OS: ${os.arch}</echo>
<echo>VM: ${java.vm.name}</echo>
<echo>Username: ${user.name}</echo>

Finally, you can also get at your environment variables for your system by using the following syntax:

<property environment="env"/>

<echo>Hostname: ${env.COMPUTERNAME}</echo>
<echo>Path: ${env.Path}</echo>

Note that the environment variables are case-sensitive even if your OS is not (e.g., Windows). So for Windows, the variable is env.Path but on Unix (I believe) it would be env.PATH. env.PATH at any rate doesn’t work on Windows so watch your case!