This month is a milestone for T-SQL Tuesday. It’s number 200, which doesn’t sound big, but this is a monthly party (started by Adam Machanic). We have 12 blog party events a year. 200 means this has been running for almost 17 years (16 years and 8 months).
I don’t care who you are, that’s impressive. Think about where you were, what you were doing, and what was happening in 2009.
I haven’t been running it that long, but I am glad I took it over from Adam and have kept it going. Thanks to everyone who writes and reads the posts and especially the hosts.
I tried to get Adam to host, but he declined. Fortunately Brent Ozar stepped up with a great initiation. My response below.
At First Glance
There are two things that immediately stand out to me when I see a query and create concern.
- cross joins
- functions in the where/on clause
While there are other things I might see, these two stand out and usually I can guess there will be issues.
For cross joins, I don’t see this as much when people use SQL Prompt or some other helper because they tend to use inner/left outer/right outer explicitly, or cross join. If you explicitly use a cross join, I might ask why, but these clauses require an ON clause, which means you’re deciding to join tables.
Where I see people using old style joins, like this:
select * from a, b where a.id > 23 and b.saledate > current_date or (a.id is null and b.saledate is null)
I get worried. This happens in Oracle, and PostgreSQ, and it’s easy to forget to join a and b, especially when there are multiple tables. Usually cross joins happen with legacy join conditions.
The other area is using functions in the WHERE clause. A common example is
select * from customer where upper(customername) = ‘Steve’
This function in the WHERE clause ruins the ability to see the data. The index is something like (‘Adam’, ‘bill’, ‘Steve’, ‘WILLIAM’). This can’t be used when the UPPER is applied. This often results in more reads, more scans than a system might otherwise take.
There are plenty of other issues that can indicate performance issues, but these two are the ones I’ve often run into and the ones that would have helped 2004 Steve write and review better code.
