Thursday, April 9, 2009

Already had to upgrade JustTheWeather

Like any noob developer, my program didn't work for everyone. I believe I've fixed the major problem: uses GPS location instead of last known network location. I also added an end user license agreement, to protect myself - nothing more (in this crazy world).

Please let me know if you encounter any issues by e-mailing me or posting comments to this blog.

Wednesday, April 8, 2009

T-SQL simple query optimization - search arguments and predicates

Once you start really trying to understand the "how's" in T-SQL, you can implement much more optimized queries. Take the following simple lesson with search predicates. 

These two queries technically will return the same results, looking for all records where the date variable is equal to the datecolumn, or the date column is null and the date variable is null.

SELECT [somecolumnname]
FROM [schema].[sometablename]
WHERE COALESCE([datecolumn],'20100101') = COALESCE(@date, '20100101')

SELECT [somecolumnname]
FROM [schema].[sometablename]
WHERE [datecolumn] = @date OR ([datecolumn] IS NULL AND @date IS NULL)

But performance wise, the first query is manipulating the table column which means the predicate isn't a search argument and SQL Server cannot efficiently use an index on the datecolumn, if one exists.

Simple rule, always attempt to find a way around using a function on your table columns in the WHERE clause. If you have to, use a CTE, temp table or table variable if you need to limit the results to search by beforehand.

Another gotcha is that even if you don't have a function on your search clause, using some evaluations aren't a search argument. Like this one:

SELECT [columnname]
FROM [schema].[tablename]
WHERE [columnname] LIKE '%8'

Not only is this not a search argument and cannot utilize indexes, but horrible performance as each row value needs to be evaluated individually to see if it ends with the number 8. 

Although WHERE [columnname] LIKE '8%' is a search argument.

Good to have a thorough grasp of T-SQL. Here's a site with good overview of this and other search argument topics

Happy searching!

JustTheWeather app for Android / Google Phone

I'm playing around with the new Android platform and have produced my first application. It's called JustTheWeather and takes your GPS location and queries the Google weather api for current and forecast conditions, displaying the results with text and images.

I wanted to make a simple app with just the weather so my 10 year old step daughter could look on my phone for her daily question of "What's the weather going to be like today?".
It has been fun and I've learned quite a lot, mostly that I have a lot to learn!
Probably going to publish it to the android market soon, for free of course, it's so simple that I can't imagine charging for it.
This was just a stepping stone (or diving board depending upon how you look at it) into building the application I really want on my Google phone. Shhh.... more to come.