Possible WTF? Joining on char fields in SQL Server
Another group at my company was complaining about slow queries when joining a few tables with a few hundred records each. I was poking around a bit and noticed that all of the columns they join on are either varchar or char fields, but nothing else leapt out at me as being awsomely bad. Is this a horrible practice that could be causing their slowness? I don't really work on the project, so it might well be something else that I missed. . .
It depends on the data and whether or not its indexed.
Joining any type is fine so long as that's what you want to do. Joining a mix of chars & varchars may have its issues though and should be examined for WTFery. Make sure PKs and FKs are indexed at very least, though with only a few hundred records even this won't have a huge benefit.
FWIW, I don't buy into all this 'only join on ints for performance'. Databases are designed to store & model data and by and large they do it well. So long as you get it basically right you'll be fine. The real performance issues come when you see some of the code featured on this site: Loading up all the data and scanning it at the client side instead of WHERE clauses, treating all data types as strings/XML and constantly converting betwen the two, searching "where [thing in a really huge database] LIKE '%bob%' .
Seems like joining a "few" tables with only several hundred records shouldn't be a bottleneck. Given the general nature of WTFery, my first question would be if you've verified that the queries are, in fact, slow. Or maybe there's some other nonsense that's going on with that other group. It seems almost certain that we're missing a key ingredient to this particular WTF.
Maybe the DB server is too busy with ITAPPMON requests to get enough time slices to work the data.
Joining any type is fine so long as that's what you want to do.Indeed, though I wouldn't want to join on a blob or clob.
I had a query that joined a 70 million row table and did a few filters on the resulting data. The problem: it did the filtering (for a value > 6) BEFORE the joins which were indexed and would result in a set much smaller than 70 million rows. Why the optimizer chose do to that, I'll never know, but that's one kind of thing that'll slow you down horribly.
SQL Server will give you an explain plan right? That'll give you a much better idea of where your bottlenecks are.