Migrate SQLPrompt Snippets to VSCode

 I love snippets; love em. And I have a whole bunch in RedGate SQL Prompt. Now I want to be able to use those in VSCode as well, but boy do I dread having to retype all of them. Solution? Python! First arg is the path where your SQLPrompt snippets are Second arg is the directory where you want it to spit out a "sql.json" file with all your snippets. """ A script to translate sqlprompt snippet files to vscode formatted snippets """ import os import json import glob import io import argparse class SQLPromptPlaceholder :     """Represents the values of a SQLPrompt placeholder"""     def __init__ ( self , name , default_value ):         self . name = name         self . default_value = default_value class SQLPromptSnippet :     """Represents the content of a SQLPrompt snippet"""     @ staticmethod     def from_file ( filename ):         """Generates an instance fr

Yin and Yang of Data Compression


I plan to write about data compression more in the future but for now I just wanted to share a quick diagram I drew up for a colleague.

Any time you're considering data compression on a table, two primary factors come into play. How much will it save you in disk space vs how much I/O will 
Y axis: Space Saved
X axis: I/O


On the Y axis, we have the space savings. You might have a table that's 10GB and but after compressing it, its 1GB. You might have another table that's 1TB and compressed it's 700GB. Clearly the first shrinks by a larger percent, but the second saves much more space.

On the X axis, we have table IO. Compressed data has to be decompressed when it's read or modified, and it's all done by the CPU. So the more I/O going on in a compressed table, the more pressure it will put on your CPU.

So how can we understand the diagram, strictly from the standpoint of these two opposing forces?

Tables in the green area you should almost always compress. These are large tables with big space savings and low I/O. The quintessential example here is an archive table; maybe something that's there for compliance, but is not really ever used otherwise. Shrink it and forget about it.

Next the tables in the red area are tables you should almost never compress. These are tables with very volatile data; something like an OLTP table with rows being continuously read and modified,  maybe from many connections. The high I/O workload will be taxing on your CPU. Your mileage may vary. The other tables in this section are tables which may not have much I/O, but really won't gain you anything by compressing. IMHO you shouldn't jump straight to compressing everything just because. Prematurely “optimizing” will get you into trouble.

And then the middle section, which, in reality, is where most of your real life examples will probably lie. For those, my dear reader, you'll need resources beyond what this humble article can supply. However even with a broader and deeper understanding of compression, these two major forces must always be in balance. Learn to master them, Jedi.

Comments

Popular posts from this blog

Migrate SQLPrompt Snippets to VSCode

Left-Padding Zeroes

Fixing Git "Unexpected Disconnect while reading sideband packet"