Earlier this week I was working through an issue that I’ve seen one too many times lately and thought Id write about it. For various reasons *cough* Legacy *cough* I often seem to run across columns in tables that are defined with what would appear to be the wrong data type or worse, multiple data elements stuffed into one column!
In this case the table held various account codes in a char(06) column. These codes were things like ‘001’, ‘002’,’A’, ‘B’ etc
Each account code was for use in a different system. so, on system X it was an integer and system Y the codes were character based, etc
This data was used to join many different tables. Initially these joins were small and straightforward with predicates restricting the code table results to only include the proper data for the system being used.
pseudo code looks roughly like this
SET DATADESC = CODE.MORE_DATADESC
ON DATA.CODE_KEY = CODE.CODE_KEY
ON META_KEY = CODE.CODE_KEY
AND DATESTAMP > = ‘9999-12-31′
WHERE DATA IS NULL
AND CODE.DATATYPE = ‘ACCT’
This is the execution plan from a typical update of 150Million rows
Notice the Metadata table and Codes table are joined before the data table so the resulting codes data is only going to include the 16 rows of smallint data which passes the implicit convert to smallint
As the rowcounts increased and joins changed the implicit data type conversions continued to work until one day they didn’t. Instead they started returning this error message
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value ‘B ‘ to data type smallint”
After some digging it turns out the optimizer had started using a different plan where it joined the tables together in a different order.
Heres that execution plan of the same update
Notice the Metadata table join is reordered and happens after the codes table is joined to the data table. When the data table is joined to the code table the error above occurs as character data in codes cant be converted to smallint during the join
To overcome this problem there are a couple of choices (admittedly there are more than a couple so this list isnt inclusive)
Best –> Change the source table to account for having different source systems account code types in different columns with the correct datatypes. Benefits are straightforward, the BIG drawback is the extra work for system redesign
Better –> Change the queries to use an explicit conversion on the joined columns changing the datatype on the data table from smallint to char(06). The benefit is the small amount of change needed, the drawback is the extra bytes involved per row joined
Good OK –> Add a query hint to force the join order. The benefit is the small amount of change needed, the drawback is in my mind we’re now forcing the optimizer to take a different path than it would if left to its own. I try to avoid hints unless there arent other options and in this case there are.
Other things that could be considered as solutions include temp tables, CTE’s, etc. all of which were more change then we wanted to pursue testing.
At the begining of this year, I gave the reigns of the annual summit program committee to Adam Jorgensen (Blog|Twitter) and I havent looked back… Much! Of course after being involved with a system for so many years its hard to completely let go (ok its not really hard to forget the work!) but, aside from the occassional questions I havent really looked a lot at whats happening in the Program Committee this year. Id like to congratulate those volunteers lucky enough to be chosen to help select abstracts for the summit and if I knew your names, Id call you out but it seems the public listing of volunteers has been removed. You’ve got a daunting task to get through the 800 or so abstracts that im sure will be submitted but, without you, the summit would surely be a lot more bland!! For those of us would be speakers in the SQL Server space, I also noticed something that unless youve been living under a rock youve probably already seen as well. Abstract submissions need to be completed by Sunday May 13 AKA 5 days from NOW!. Since no time is specified I’ll make a wild guess (yes its truly a guess) and say they close at Midnight Pacific time. but why deopend on a guess, make sure to get those abstracts submitted sooner rather than later. It appears as though the wait to get the results of the selection will be over before it really starts as they are due to be released on Jun 8
I was asked by someone to write a mission statement for chapters. I don’t know if this qualifies in the truest definition of a mission statement or not but, I partially stole this from a friend and I hope he doesn’t mind: 2012 is going to be the year of the chapter.
Its been a busy quarter since I took over responsibility for chapters and there have many changes, hopefully this post can summarize the highpoints.
During the past quarter we’ve seen exceptional growth of chapters, including seeing 12 new chapters formed across the globe!
A BI Chapter in Wisconsin, USA led by Farouq Abukhamireh
Algeria led by Badrou Zeggar
Bangkok, Thailand led by Fukiat Julnual
Ekatrinburg, Russia led by Evgeny Fedyakov
Kharkiv Ukraine led by Denis Reznik
Kyiv Ukraine led by by Konstantin Kosinsky
Las Cruces, New Mexico USA led by Colleen Barnitz and Russ Burns
Leicester, UK led by Gavin Campbell
Maidenhead, UK led by Richard Douglas
Stavropol, Russia led by Maksim Lemeshko
Santa Catarina, Brazil led by Rodrigo Dornel
Last month I put forward some recommendations to the board of directors for increased spending for the Fiscal year 2012 (ends 6/30/2012) two of those proposals were accepted and funded.
Yesterday I had the opportunity to lead 2 separate meetings with our Regional Mentors in order to bring them up to speed on what those proposals are funding.
The first thing this is going to allow us to do is send a “Chapter Kit” or mailing to every PASS Chapter around the globe! I’m not going to spoil the surprise about what’s going to be inside but, we hope that Chapter leaders will find the contents valuable and can put them to good use.
The second program we’re going to implement is to fund some regional mentor travel to PASS chapter meetings and SQLSaturdays. The basics are that we’ve given each regional mentor a funding allocation and asked them to attempt to visit as many chapter meetings in their region as they can, with the primary focus being on chapters especially those that need help with speakers, or are otherwise struggling. We are attempting to fill a gap in speakers at local UG’s and allow the Regional Mentors to get out in the community and evangelize for PASS.
Both of these programs are new, and both are things that we’ve talked about trying for a good while. I’m expecting to see both of these have a positive impact on the community and I’m expecting to get lots of feedback during the process and learn some lessons while we see exactly how this is going to work and what impact its going to have.
Since I started with chapters I’ve been pushing forward with a plan to revamp the existing tools our Chapter leaders use to manage chapters and I’m happy to say I’m starting to see some real movement towards helping with our needs in this area! I expect the first round of changes to start being rolled out within the next 45 days. As this project continues ill continue to write about the new and exciting changes we’re making.
Do you know someone in the PASS Community who is a hidden hero? Perhaps a chapter leader who is doing terrific work to help the SQL community. Maybe its a member who is putting in 110% to organize a SQL Rally. A virtual chapter leader who is organizing great sessions or perhaps its a speaker you’ve seen go above and beyond and speak at multiple chapters/virtual chapter events this month. There are so many people who contribute and make the SQL community so vibrant that finding those who stand out is tougher than ever!
These people are the lifeblood of the SQL Server community, they freely give of their time to make PASS the premiere SQL Server association and its about time we start recognizing them!
Since Ive been volunteering with PASS one thing that has always struck me is how rare it is that PASS actually recognizes outstanding volunteers. When I first joined, we only had the PASSion award which is awarded yearly at the Summit to the single volunteer who had the “most” outstanding contributions. In more recent years we’ve done a better job of recognizing additional people who are outstanding volunteers through the outstanding volunteers presentation at the Summit however, these volunteers all come from the passion award nominations and those nominations are only open just prior to the summit.
I’ve always thought that we should recognize volunteers across the organization in a more consistent way, and not revolve that recognition around the Summit. Luckily, the opportunity to do something about this lack of recognition knocked earlier this year and we’ve made progress. In last weeks connector there was an announcement about nominating volunteers to be recognized as outstanding volunteers of the month. The initial intent is that we’ll accept email nominations during every month for any volunteer who has made outstanding contributions to the community. Those submissions will be reviewed on the last day of the month and recognition will follow the next month. Initially, we are planning to recognize these outstanding volunteers with a nice certificate and highlight them in the connector newsletter. Its not much compared to what these people do day in and day out but with success over time, hopefully we can expand this a bit more. For a start though, I think this is a good first step down the right road.
We intend to have the first recognition cycle start in March so that means that submissions are due by 2/29 (tomorrow!) Please, take a moment and send us an email with as many details as possible about the contributions of an outstanding volunteer who has made an impact in the SQL community. As always with these types of things, the more details the better!
This is a blog post that should have been published about 3 weeks ago. Better late than never I suppose….
With all endings there is a beginning. I’ve written previously about deciding to take on a different role within the PASS organization in 2012. I’m excited to say that I convinced the leaders within the BOD to let me have a chance at leading the Global Chapters portfolio. What does this mean to the two of you reading this? Likely not much but, to myself and a few hundred chapter leaders and regional mentors I’m hoping it means the beginning of a great run of imrovements to the Chapters program within PASS. For the past weeks I’ve been working very closely with our wonderful Community Evangelist Karla Landrum (Blog|Twitter) and her counterparts within PASHQ to learn everything that I possibly can about what our chapters are doing, and what they need to grow and thrive. What I’ve learned is somewhat what I already knew and can be summed up in 4 words!
We have unmet needs!!
After staffing the Community Evangelist role last year it appears as though some of the pain points with running a chapter have been alleviated however, it seems as though there are still plenty of places that could use some improvement. One of the first things I have attempted to do is to get approval to spend PASS IT time on improving the suite of tools we provide to make managing a chapter easier. While I have a list of things I’d like to see these tools become based on feedback I’ve personally received from my friends in the community, I would like to encourage all PASS RM’s & CL’s to get in contact with me though any means available. I’d like for every PASS Chapter Leader/Regional Mentor to list the top 2-3 things they’d like the chapter tools to do that would make running their chapter easier and send those to me. I obviously cant say we’ll address everything but, this year I intend to see to it that we address as many as possible.
Connect Share Learn
While this is the simplified version of the overall PASS mission statement, it is applicable here as well. I want to encourage you to reach out to me and connect, in order to share your successes and pain points in leading a PASS chapter so that hopefully we can each learn from it. My contact info is at the top right of this page, connect with me on linked in and drop me an email to bend my ear if you’d like to talk about anything remotely related to SQL Server, PASS Chapters or the SQL Server Community at large. Ill be “the guy” trying to keep the ship on course for at least the next calendar year and I plan to make the best of it.