Taking the time to write about something makes you learn more

You know how sometimes you learn something, then you start to see it everywhere you look?

That happens to me all the time, and I’ve even started to try to take advantage of it.

There’s a name the phenomenon of encountering something once, then starting to see it everywhere: it’s the Baaader-Meinhof Phenomenon, also known as the “frequency illusion.”  And it’s helped me start to write better training material.

I’ve found that I write the best material when I take a bit of time to step back and write training material, but then make sure I mix in some consulting work. Then I revisit the material and refine it to make it better.

The reason is the I personally seem to be pretty influenced by the frequency illusion. If I want to really get to understand a merge join, for instance, taking some time to research it, write some demos, and create some slides will bring it to the forefront of my brain. Then I don’t really have to think about it too much more if my brain is interested — every merge join I run into for the next few weeks will practically be surrounded by a glowing halo, inviting me to get to know it better.

The net result is really two things: I’ve started giving myself longer timelines for developing some content.  And I’ve also finally admitted that I really can never procrastinate to get something started.

 

Comments { 0 }

How I learned to write TSQL

I primarily work with SQL Server, but my favorite book on TSQL is the one that I learned with: The Transact SQL Cookbook.  It’s been many years since I picked it up, but I have a ton of happy memories of staring into this book while riding a train to and from work, getting my head around the examples and figuring out how they worked.

I haven’t picked up the more recent version, The SQL Cookbook, but I’m not surprised that it has pretty fantastic reviews.

Over time, the more I teach, the more I learn about the power of examples when it comes to learning. There’s something pretty magical for “recipe” style learning when it comes to picking up new concepts and new languages.

Comments { 0 }

SQL Server’s Filtered Indexes: The “Third” Index Dimension

One perk to filtered indexes that may not be obvious at first is that you get an extra “dimension”– almost an extra kind of key column. This can be useful for some queries that can be tricky to index.

Let’s we have a query with two inequalities in the predicate, like this:

SELECT COUNT(*)
FROM dbo.Table
WHERE ProductStatus > 5
and ProductsInStock > 1;
GO

We can create the following index, but it isn’t perfect:

CREATE INDEX ix_nofilterhere on dbo.Table (ProductStatus, ProductsInStock);
GO

Our data is first sorted by ProductStatus, so we can seek to the 6 and read the data thereafter, but we have a problem: the data at that point looks like this:

  • ProductStatus, ProductsInStock
  • 6, 0
  • 6, 0
  • 6, 0
  • 6, 0
  • 6, 0
  • 6, 1
  • 6, 1
  • 6, 6
  • 6, 6
  • 7, 0
  • 7, 0
  • 7, 0
  • 7, 0
  • 7, 0
  • 7, 0
  • 7, 1
  • 7, 3

We have to check every single row and see if the ProductsInStock is greater than 1. If we have a lot of products with nothing in stock, we might be reading a lot of rows uselessly.

If the index was filtered, like this, it would have a secret superpower:

CREATE INDEX ix_filtered on dbo.Table (ProductStatus) WHERE (ProductsInStock > 1);
GO

In this case, SQL Server can match the filter to the query with an exact match, so it probably doesn’t even have to think about the ProductsInStock predicate anymore: it knows everything in the filtered nonclustered index meets that criterion.

Now all that’s left is for it to solve for the predicate ProductStatus > 5, and that’s the leading key column in our filtered index! We can go straight to the right starting point and we don’t have to read any “wasted” rows.

Essentially, our filtered index predicate offloaded one of the predicates in our query and made solving the rest simpler.

Most of the time, non-filtered indexes work well enough. But if there are important queries with multiple inequalities, or important queries with top/order by and predicates on different columns, sometimes a filtered index can get pretty spectacular results. (Assuming you can get SQL Server to use it. It’s complicated.)

Remember: always test filtered indexes carefully before using them. (See “required set options for filtered indexes” here.)

Disclaimer: this post is full of pseudo-code which may not be syntactically perfect– just using snippets as an example to make notes on a point, etc.

Comments { 0 }

Annual Life Optimization Checklist

This jigsaw puzzle is your life

This jigsaw puzzle is your life

I took a little time off from work recently. I headed out to the beach with my dog and rented a house.

My main purpose from the vacation was to spend some quality time relaxing. And I did that! I invited some wonderful people to visit me for part of the trip. We talked and ate and spent a lot of time enjoying the sand and water and sky.

My secondary mission was to spend some time optimizing my personal life. I’m pretty good at keeping on top of things, but recently I realized that there was more than one area where I knew I could do better. There were other areas that I just hadn’t devoted any time to (like end of life planning). I tracked everything I worked on in a checklist.

Optimizing my life took more time than I expected. It was also incredibly rewarding and gave me a big sense of accomplishment. Was I relaxed more afterward because of the fun times, or because of the peace of mind of having gotten things in order? It’s hard to say, but it was a great combination.

My Life Optimization Checklist

Here’s the checklist of the 15 things I plan to revisit in detail at least once a year:

  1. Credit: Pull your free annual credit report from all three major credit reporting companies. Information how to get your free reports is here on the FTC site. Follow up on any items you don’t understand. Consider asking for credit increases on the cards you use and removing lesser used cards with fees if you have a large number of accounts and you don’t log into them all regularly.
  2. Mortality: Update your will, power of attorney, and related documents. If you have a living trust established, identify any items you wish to transfer to control of the trust and make a plan for carrying out the transfer. If you don’t have these documents, check out NOLO’s option
  3. Beneficiaries: Update your benefiaries online in life insurance or investment accounts.
  4. Investments: Review the performance of your stock and retirement investment accounts. Potentially adjust and research new funds.
  5. Retirement: Review your retirement accounts and the amount you put in. Set aside money or adjust your regular donation amount. Research any new accounts that you have been thinking about opening.
  6. Debt: Review your annual income and reassess your plans to pay any outstanding loans/mortgages/other debt for the next year. (Old college loans still hanging out? Maybe you can pay more down and get rid of them sooner than you thought. But make sure you balance this with your retirement plans, too.)
  7. Giving: Review your charitable donations for year to date. Set a target charitable donation account and make a plan to set aside the money you would like to donate.
  8. Pets: Is your pet registered and current on shots and tags? If they are microchipped, do you have the chip number handy? (If they aren’t chipped, why not get it done?)
  9. Health: What was the date of your last doctor appointment? Set up an annual checkup if needed.
  10. Teeth: What was the date of your last dental cleaning? Set up a checkup if needed.
  11. Data: Review the files on your personal computer. Make sure you are backing up files as needed and have secured and encrypted the data if needed.
  12. Websites: Review registration, website hosting, and automated backup status of any websites you host.
  13. Cars: Any outstanding maintenance you’ve been putting off? Schedule it! If you’re planning to make an auto purchase or sale in the next year, estimate out what it’ll cost and make a plan to set aside the money. Review the rates on your auto insurance and make sure you’re satisfied with what you’re getting.
  14. Volunteering: Is there something you’ve been meaning to do for an organization that’s important to you? Figure out if you can do it during the break, or establish a schedule to make it happen in the next year.
  15. Other Adult Responsibilities: Is there anything else that you know you should take care of that you’ve been putting off? (Renter’s insurance? Legal documents? Anything?) Confront it, and make a plan to get it researched, planned, and taken care of.

There are things missing from this list: I don’t have dependents. (I don’t have any kids or older family members to care for.) I suspect all you parents out there are much more organized than I am and have to keep on top of this stuff more often! Or maybe it’s even harder for you to keep up.

I don’t think I’ll use the list as an excuse to put things off. If anything, having a list helps me feel like I have a finite number of items to cover, and more like I can break them up into achievable goals and take care of them. But I do really like having set aside some days to just take care of optimizing my grown up life– and I plan to do it again next year.

Comments { 0 }