Shop Talk: 2023-11-06

The Recording

The Panelists

  • Kevin Feasel
  • Mala Mahadevan
  • Mike Chrestensen

Notes: Questions and Topics

Nominations for Board Positions Open

There will be an election for two TriPASS board positions this year. Tracy Boggiano and Mike Chrestensen currently hold these positions and are re-running. If you are a TriPASS member in good standing (basically, you’ve signed up on Meetup and haven’t broken any of the bylaws), you can also throw your hat into the ring and run for office. If so, please let me know, either via e-mail (if you have it) or reaching out to me on Meetup.

Upcoming Events

The first topic of the night is that there are two major conferences coming up next week: PASS Summit and Live! 360. Tracy will be at the former and I’ll be at the latter, so if you’re going to either, come say hello.

Playing Connect 4 in T-SQL

Our next topic covered a great blog post by Tomaz Kastrun, who showed us how to play Connect 4 in T-SQL. Along the way, Anders pointed out an older Itzik Ben-Gan article about solving the 8 Queens problem in T-SQL. Unfortunately, the links in there don’t work anymore, though here’s another solution.

Using VS Code Extensions in Azure Data Studio

Somehow, we got sidelined on using Visual Studio Code extensions in Azure Data Studio. I used the example of the excellent Rainbow CSV extension for Visual Studio Code and showed how to get it working in Azure Data Studio. It failed the first time because my version of Azure Data Studio was a bit old, but after updating, the second time was the charm.

Oracle Migrations Are Scary

The next topic covered some pain Mike is going through right now as his organization does a big migration from Oracle to SQL Server. I don’t have a lot of great advice around this, sadly. Or maybe happily, as it means I haven’t had to do one of those.

Options for Improving View Performance

The final topic of the night also came from Mike and it has to do with optimizing some nasty-sounding views. We had to stick to high-level ideas because there was no example to troubleshoot live, but here’s some of the key guidance:

  • Views are just saved queries. Try running the query outside a view and see if there’s a drastic difference. There shouldn’t be, but if there is, it can be an indicator of some other problem we didn’t discuss.
  • Review the execution plans for common view queries. Optimize them like any other query plan: look for big hitters like large (and unnecessary) table scans, lazy spooling, or reading millions of rows to return back a few hundred.
  • Don’t nest views. The optimizer can give up if you nest views deeply enough and you can end up with many joins to the same tables, causing additional I/O that you could optimize out with a better query. Make the query optimizer’s job as simple as possible by keeping your queries simple.
  • We talked a little bit about the possibility of indexed views but passed on them because the situation was not conducive to using them.
  • Solomon brought up some good ideas as well: Make sure the data itself is in the correct data types and you don’t have implicit (or explicit) conversions on joins requiring scans.

Leave a Reply

Your email address will not be published. Required fields are marked *