I was recently in Houston TX teaching for two weeks and had the excellent opportunity to eat at a TEXAS ROADHOUSE while I was there. Yes it is a chain and sort of more know for stakes rather then ribs, but these were darn good ribs. I'm adding them to my list of restaurants worth going to if I find one in a town I'm in. And heck with free peanuts that you toss the shells on the floor, yeehaw! Count me in! :-)

Yes, this is the performance problem.

Recently in a class of mine we were all reviewing a trace file (10046) of a transaction that ran on the client’s system. There was one SQL statement that consumed about 70% of the total time for the run. The statement was rather simple one table and one predicate. The predicate was a concatenation of several columns (each had an index) with an equality operator to a concatenation of a couple of binds.

The predicate looked something like this (it was longer and slightly more complicated, then this but this captures the flavor):

where COL1 || '-' || COL2 = :B1 || '-' || :B2

Pretty clearly the Optimizer can’t use the indexes on the columns because they are concatenated together in the predicate. The STAT lines weren’t in the trace data so we couldn’t see for sure but it was likely doing a full table scan which was driving about 60,000 LIOs for the statement. After some discussion several options were bantered about on how to fix the SQL, with a new index and rewriting the predicate being the top two.

As we were finishing up the discussion, the person who brought in the trace said “But this statement isn’t the performance problem.”

To which I replied “Oh yes it is.”

This is something that I’ve experienced before. Folks will think another SQL statement is the problem before they start and when it isn’t they still focus on the statement they think is the problem. We all do it. We have a preconceived idea of what the problem is and when faced with something else, we have a hard time getting over our preconceived idea.

This is the root of why having some one with little knowledge of the system can seem to have such great insight. Really it’s just that they have no preconceived idea of what is wrong. They can then follow the clues without prejudice and see what everyone else may have missed.

Cardinal Thomas Wosley (1471-1530) was an advisor to King Henry the VIII. He gave the King the advice of "Be very, very careful what you put into that head, because you will never, ever get it out." Several 100 years later this advice still rings true.

Temperatures

Living in the Great State of Michigan in the USA gives me the opportunity to experience weather in all its ways. This is my thermometer on my back porch.

Ribs In New Jersey

I'm here in New Jersey the Garden state of the USA. Went out for some good ribs at Houston's (181 Riverside Sq Mall Hackensack, NJ). Quite good. Very nice atmosphere and friendly staff. Although I do prefer a dry rub on the ribs the sauce they used was excellent. I also really liked the shoe string fries, they were nice and crunchy. The coleslaw was different from my norm and quite tasty as well. Great meal!

Were you there?

Click here for a video of the 2009 Hotsos Symposium.

Will you be there next year?

BBQ in Phoenix AZ

Ahhh... a great lunch at the BBQ Company here in Phoenix AZ. I got a 1/3 rack of ribs for lunch with some sweet beans and a corn medley. Excellent sauce, and the corn medley was particularly good. The only bummer about this place is that it's only open for lunch. This is now added to the list of places to get to while in Phoenix.



OK, now back to some SQL optimization... How many LIOs is that thing doing?!?!

Breakfast at Matt's

One of my other favorite foods is Breakfast Food and in particular Waffles. Matt's Big Breakfast has the best Waffles I've ever had. And the other food there is excellent as well. I try to get to Matt's at least once each time I'm in the Phoenix/Tempe area in Arizona. This morning was the day for this week and my waffle was excellent as always!!



They open at 06:30 and the best thing to do is to be there at opening if you want a seat. There aren't many and they go fast!