Welcome to T-SQL Tuesday! This month’s host is Rob Volk (blog|twitter) and the topic is Data Analogies.
Analogies
Come in! Come in, my boy! Now, your father sent you to me to explain what I used to do. Well, I was a DBA, my boy! What do you mean “what does that do”? I took care of the databases! I made sure that they were looked after, that they had vim and vigour, and that they didn’t go down.
What? No, I don’t mean “down-down”, it’s not a direction! I mean that they didn’t go offline. No, not “off-off”, well not quite… It was my duty to ensure that they were available. Got it? No?
Database Administration
Well, let’s take that whiskey cabinet over there Jas…Jaeysin. Let us say that the whiskey cabinet is our database, and it is our job to see that it is never empty. We do that by lots of ways; by checking on it and making sure that nothing is alarming, like empty bottles. We check that there is space if someone wants to add more whiskey and that anyone who wants something from it, can get it. Like me, hehe.
What? You don’t understand how that’s like being a DBA? Well think about it my boy, I would check on the databases, make sure nothing was alarming, and that the data was always available for whoever wanted it.
Security
What’s that? You want some? Ho ho, my boy, you are one for jests. I tell you what, try opening the cupboard door on the left. Yes, the one where you can see all the sticks and cherries through the glass. Not a problem for you, was it? Put back the cherry, please. And wipe your hands…NOT ON THE GLASS! Nevermind, my boy, nevermind, I shouldn’t have put the soda water in a dangerous place like that…inside a cupboard…away from the ledge. Try and open the right cupboard door now. Yes, the one with the fancy bottles and the shiny lights. Yes, I’m aware it’s locked. Now see, you cannot open that door but I can because I have permission to open that door. That was another part of my job, making sure that people had the right permission to get what they wanted, and that people without permission could not.
What’s that? Who decides on the permissions? Well, back then, it was a business decision. Where those higher up in standing would pick, and I would have to follow their instructions. Now, I’m in charge.
What do you mean that’s not what your father says? Right, well, I’ll be having a few words with him, and we’ll see more about this “under his partner’s thumb” business. No, I can’t open it either. Because I don’t have the key. Yeah well, I may be db_owner, but not sysadmin… Nevermind.
Performance
What else did I do? Well, I made sure those who wanted data from the database knew where they could get the data and could get the data promptly. Well, do you see the whiskey cabinet? Yes, the one on the right. Yes, the one you’re not allowed open. Yes, you’re allowed to look…oi! Quit your cheek, or I’ll add that to the list of things I’ll have to talk to your father about. Now, if someone were to go to that cabinet wanting a nice Scotch, they only have to reach into the middle shelf, and they would have a whole choice of Scotch to choose from. I know that because I know that my middle shelf is only for Scotch. Same with the databases; I helped people to create tables to hold their similar data the same way I choose that shelf to have my Scotch.
And see the way that the bottles get fancier as you scan your eyes from left to right. I ordered that shelf, so the most expensive bottles were all the way over there on the right. And, the least expensive bottles are all over to the left. Same with the databases; I would create indexes so that people could go to what they wanted, whether that be the “expensive bottles” or not. No more looking through the entire table for what they want, they knew exactly where to go and knew when they could stop looking as well.
What? Indexes, my boy, an index. No, it hasn’t nothing to do with your finger! Wait! You may be on to something there. Tell me, have you ever run your index finger down a phone book? A phone book. You’ve never heard of it? Hold on…this thing? Ever seen this? Big yellow book, lots of information? Yes, I know we have Google, nevermind.
Redundancy
Was that it? No lad, that was not it. The world of data is vast and open. So much more than what an analogy using a whiskey cabinet can provide. But I will leave you with one more analogy; Redundancy. It’s always essential to have a redundant copy of your data, a recovery plan if a disaster were ever to strike. Open that desk drawer for me, my boy. Yes, it’s another bottle of whiskey. A redundant backup, if you would. Now I believe I see your father pull into the drive, so it is time for you to leave. I shall go see if my backup can still be restored.
At this stage of my career with Transact-SQL, I would like to think that I have the language mostly mapped. If there is something that seems new or unusual, then it often comes down to the developers marking the code to make replacing it more effortless.
In case you don’t grasp what I’m on about, the below piece of code shows it pretty well.
SELECT N'##datetime##' AS search_and_replace;
<clickbait>I had never seen this before</clickbait>
Can you imagine my shock when I came across a piece of code that not only was not for finding and replacing but even though I did not think it would compile, it did!
If you can imagine my shock, then you’re going to need to increase it more when I tell you that there are a whole family of the same functions! Here is the code that threw me for a loop the first time I saw it.
SELECT {d '1970-01-01'};
Researching
There are more than just {d '<datetime>'} as well!
SELECT {d '2020-01-01'} AS sample_date,
{t '12:31:56'} AS sample_time,
{ts '2020-01-01 12:31:56'} AS sample_datetime;
It’s not just date and time functions but string function as well.
SELECT LEN(N'This string is 33 characters long') AS string_length,
{fn BIT_LENGTH(N'This string is 33 characters long')} AS string_bit_length,
{fn OCTET_LENGTH(N'This string is 33 characters long')} AS string_nvarchar_octet_length,
{fn OCTET_LENGTH('This string is 33 characters long')} AS string_varchar_octet_length,
DATALENGTH(N'This string is 33 characters long') AS string_nvarchar_datalength,
DATALENGTH('This string is 33 characters long') AS string_varchar_datalength;
I was asked today if I could figure out how many ways I could arrange the following equation to get the number 18?
// Replacing the "?"s with either "+", "-", "*", or "/" how many ways can you get 18?
2 ? 2 ? 2 ? 2 ? 2
Now I’m sure with enough time and effort that I probably could. But if you ask me this before I’ve had any coffee, the best you’re going to get is a dirty look.
Again, that’s the best that you’re going to get…
So I got lazy.
I know PowerShell.
I know that I can create the formula in PowerShell.
I know that I can invoke the formula in PowerShell to get the result.
So with an icy glare at the offending message and a sip at my scalding coffee, I create the script.
The Script
I’m not saying that this script is any good. Like I said, I wrote the script before the caffeine had hidden the tiredness from my brain.
It works, and that was the main thing for me at the time.
$operators = '+', '-', '*', '/'
$Permutations = foreach ($op1 in $operators) {
foreach ($op2 in $operators) {
foreach ($op3 in $operators) {
foreach ($op4 in $operators) {
$MatsIzHard = [scriptblock]::Create("2 $op1 2 $op2 2 $op3 2 $op4 2")
[PSCustomObject]@{
Formula = $MatsIzHard.ToString()
Result = $MatsIzHard.InvokeReturnAsIs()
}
}
}
}
}
Like I said, seems to work…
Now that I have the results in the $Permutations variable, I can look for any results where the Result property is 18.
$Permutations.Where({ $_.Result -eq 18 })
Yes, I can answer your question.
Yes, I can get 18 from that expression 2 ways…
On to my Actual Job
Overall that took around 3 minutes to complete. Which was the perfect time required for the caffeine to kick in and for me to be ready to start my day.
Now, that PowerShell was not pretty, dynamic, or efficient. It was probably only fast because the number of iterations was so low.
But I’m happy since even though I can’t do maths before coffee, I can at least write PowerShell.
Now, we can see all the users; the user itself, the system users, and the other user I created on the database.
Original Article
The Backstory
Work is in the process of automating tasks. Part of this automation includes verifying the automation that we’ve done.
Where am I going with this?
Well, when we’ve automated the creation of database users we also want to verify that we’ve created the users that we say we’ve created.
My fellow co-workers have, thankfully, seen the dbatools light and we use the command Get-DbaDbUser to get the users in a database and compare the list against the users we were supposed to create.
If there are any users that should have been created but don’t show up, well then we have a problem.
The Principle of Least Privilege
Works fine for me […] but it looks like […] can’t run it with her “public” access to the db server.
I’m not going to sugarcoat things – the person that sent me the request has more access than they rightly need. The “public” access worker did not need any of that access so I wasn’t going to just give her the same level.
Plus, we’re supposed to be a workforce that has embraced the DevOps spirit and DevOps is nothing if it doesn’t include Security in it.
So, if I could find a way to give the user enough permission to run the command and not a lot more, then the happier I would be.
But, I was surprised how difficult it was to find out what permissions were needed to run Get-DbaDbUser. Even more surprised when I failed and realised I’d have to find out myself.
If anyone else can Google/Bing it and get the answer, please let me know 😐
The Test
Let’s create a new user with no permissions in SQL Server.
USE [master];
GO
CREATE LOGIN LimitedPermissions WITH PASSWORD = N'MorePermissionsMoreProblems!';
GO
Now let’s test it out. I have a database in my instance called __DBA. Can we access the users in that database?
It doesn’t work. What’s even more surprising is that it silently doesn’t work. No warnings, no permissions errors, or nothing. And I included the -EnableException switch!
The Investigation
It’s good to know that you can check out the contents of the dbatools (and other) commands from PowerShell. No, I’m not talking about opening the .ps1 files. I’m talking about using the Function:\ psdrive.
See those $server.databases and $db.users? For me, that means that it’s using SMO (Server Management Objects). If there was any hope of me google/binging permissions before this, well it’s gone now.
The Will is going
To cut a rather long story short, eventually I came to the idea of thinking that maybe it only needs to connect to the database. So let’s try that.
USE __DBA;
GO
CREATE USER LimitedPermissions FROM LOGIN LimitedPermissions;
GO
And now let’s try our Get-DbaDbUser command again.
I will confess to only starting this post late. So my tips and tricks will not be well thought out or planned. They will involve PowerShell though, something that I think about daily.
What we know
I consider it to be common knowledge that you can open up PowerShell from the explorer.
By default, my PowerShell opens up to “C:\Users\Shane”.
But by typing “PowerShell” into the location bar of an explorer, you can open a PowerShell session.
The PowerShell session will open to the location the explorer was open.
Et Viola
Reverse it
Did you know that you can drag and drop onto a PowerShell console?
Let’s create an empty text file.
New-Item -Name TestEmptyFile.txt -ItemType File
And we can see that it shows up in the open explorer location.
If we were to drag and drop the file into our PowerShell console window, it will return the full path to that file
Learn from History
If you spend a lot of time in a PowerShell console, it’s not rash to presume that you’re going to be running some of the same commands over and over again.
That’s where PowerShell’s history comes into play.
By using the command Get-History or even its alias h , you can see the commands that you’ve run before:
#Hashtag
Claudio Silva ( blog | twitter ) mentions in his T-SQL Tuesday post about using PSReadline’s HistorySearchBackward and HistorySearchForward.
I’ve fallen into the habit of using #.
Get-History returns an Id that we can use with our #. On our PowerShell console, if we want to run the 2nd command in our history, we only need to type #2 and then press Tab.
If we don’t know the Id but know a word, phrase, or substring of the command we can use #<word | phrase | substring of the command> to look through our history for the command.
So to find the command Get-History that we ran, we can use #Hist and then press Tab.
If it’s still not the right command, we can keep pressing Tab until we find the previous command that we’re looking for.
..but Sweet
I’m pretty sure I haven’t blown your socks off in amazement with these tips and tricks. But they work, they’re semi-useful, and they should be helpful.
Them: … and one of the steps in the job creates files…
Me: Okay…
Them: … and we need you to check if it creates the files, otherwise we don’t need to do any of the rest of the steps.
Me: Why don’t use just error out if that step fails?
Them: Cause there are other errors in that step but they don’t mean it failed
Me: … say what?
Pretty close representation of the conversation
Investigation
I’m going to ignore the whole “there are other errors” for the moment and actually attempt this task. First, let’s try to see if there is a way to get the last time a job step ran.
I already have a test SQL Agent job called “TestEmail” so let’s use that for our test.
Glancing at the output, it appears that we’re looking for the LastRunDate property. In this screenshot, it shows 1/1/0001 12:00:00 AM which means it’s never run before.
Creating Files
We need a way to create files, and not create files, on demand.
Vaguely in the back of my head (and I apologise for not remembering whom), I remembered someone using the presence of a temp table to fire or not fire a trigger. We’re going to use that premise here.
In a SSMS window, we have this code:
USE __DBA;
GO
/* Create the files */
DROP TABLE IF EXISTS dbo.DoNotCreateFiles;
/* Do not create the files */
CREATE TABLE dbo.DoNotCreateFiles (DoNotCreateFilesID int NOT NULL);
If we want to create files from the PowerShell script, we need to drop the table. If we don’t want to create files from the PowerShell script, we need to ensure the table exists.
Next, we create this PowerShell file which I’ve called “CreatePowerShellFiles.ps1“.
$Query = @'
IF EXISTS (SELECT 1/0 FROM [sys].[tables] WHERE [name] = N'DoNotCreateFiles')
BEGIN
SELECT CreateFiles = 0;
END; ELSE
BEGIN
SELECT CreateFiles = 1;
END;
'@
[bool]$CreateFiles = (Invoke-DbaQuery -SqlInstance localhost -Database __DBA -Query $Query).CreateFiles
if ($CreateFiles) {
[PSCustomObject]@{
Name = 'CreatedFile'
DateCreated = Get-Date
Nonce = New-Guid
} | Export-Csv -NoTypeInformation -Path "C:\Users\shane.oneill\Desktop\TestPowerShellCreatedCode_$(Get-Date -Format FileDateTime).csv"
}
Adding this file as a step in our job, it checks for the existence of our table – if the table exists it does nothing otherwise it creates a sample csv file.
Now for the main course
We’re going to add another step now. This one will check for files created after the previous step has run.
First, we’ll create a PowerShell file (“CheckPowerShellFiles.ps1“).
param (
[Parameter(Mandatory)]
$JobName,
[Parameter(Mandatory)]
$StepName,
[Parameter(Mandatory)]
[ValidateScript({ Test-Path -Path $PSItem })]
$FileDirectory
)
$Jobs = Get-DbaAgentJob -SqlInstance localhost -Job $JobName
$LastStep = $Jobs.JobSteps |
Where-Object Name -eq $StepName
$FilesExist = Get-ChildItem -Path $FileDirectory |
Where-Object LastWriteTime -ge $LastStep.LastRunDate
if (-not $FilesExist) {
$ErrorMessage = 'Files were not created after {0}' -f $LastStep.LastRunDate
throw $ErrorMessage
}
And add it to the job, passing in the parameters that we want:
Test Run
We’ve got two states that we want to test
The files get created.
Job should succeed.
The files don’t get created.
Job should fail.
Let’s run the first test:
Make sure the table is dropped so we create the files:
USE __DBA;
GO
/* Create the files */
DROP TABLE IF EXISTS dbo.DoNotCreateFiles;
Now run the job:
Success!
Now to check that the job will fail if no files get created:
Make sure the table exists so no files get created:
/* Do not create the files */
CREATE TABLE dbo.DoNotCreateFiles (DoNotCreateFilesID int NOT NULL);
Now run the job:
Congrats, you have successfully failed
Taking a look at the job history, we can see our error message:
Finally
Well, we’ve proved that this method works!
I can pass on “CheckPowerShellFiles.ps1” to the people who requested the check telling them that they only need to add in the right values for the parameters…
Along with a polite note along the lines of “you should really fix your errors”.
I admire the array of learning materials that they have for different topics. I find it reassuring seeing the names of Pluralsight authors and recognising them independently as knowledge leaders in their field.
I even have a personal subscription since I was devastated when I moved jobs and was removed from the old jobs plan!
So, even though it’s been out for a while now, when I saw that they now have an extension for VS Code it was enough to make me install it on my machine.
I’m currently going through some C# Fundamentals by Scott Allen ( twitter | blog ) on Pluralsight with an aim to work my way into Entity Framework.
With the Pluralsight extension on VS Code, I can open up some sample code file and Pluralsight will recommend some clips based on the file
Clicking on one of those clips will open a new tab in VS Code like below:
Clicking the “Watch Clip” button will bring up a dialog from VS Code asking if you meant to go to an external website.
Opening or copying & going to the link will open up the Pluralsight video segment from the recommendations.
Insert Dad joke response here
Give it a go and see how it works for you. I use Azure Data Studio for my SQL code but I’m tempted to open up the .sql files in VS Code and see what Pluralsight recommends for them.
What do I think it will say? Who wrote this? Urgh! Kill it, kill it with fire!
At one stage or another, I’m sure that we’ve all been asked to performance tune database code. Sometimes this tuning can involve changing the code. How do we know that the changes that we are introducing isn’t going to introduce bugs? You got it – Unit tests!
Set up
Let’s set up a contrived example – Contrived of course coming from the words “con” as in fake and “Tried V” as in I tried 5 times before I just gave upand used what I had.
USE [tempdb];
GO
/*
Let's build this thang...
*/
CREATE TABLE [dbo].[Objects]
(
[id] int NOT NULL CONSTRAINT [PK dbo.Objects(id)] PRIMARY KEY,
[level] int NOT NULL,
[object_type] int NOT NULL,
[tag] nvarchar(256) NOT NULL,
[description] nvarchar(max) NOT NULL,
[parent_object_id] int NULL
);
GO
INSERT INTO [dbo].[Objects]
(
[id],
[level],
[object_type],
[tag],
[description],
[parent_object_id]
)
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS [id],
NTILE(10000) OVER (ORDER BY [S01].[alias]) AS [level],
[M01].[severity] AS [object_type],
[S01].[alias] AS [tag],
[M01].[text] AS [description],
NULLIF(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1, 0) AS [parent_object_id]
FROM [sys].[messages] AS [M01]
INNER JOIN [sys].[syslanguages] AS [S01] ON [M01].[language_id] = [S01].[lcid]
WHERE [S01].[alias] = N'English';
GO
SELECT *
FROM [dbo].[Objects];
GO
This will insert 11,466 records into the dbo.Objects table. The table has a column called parent_object_id that points to its … parent object id, wow you have been paying attention! Luckily, or unluckily, tuning the schema of the table is out of scope for this post
Next we have the piece of code that we’ve been asked to performance tune. It’s …uhm … well it works.
/*
RBAR SQL
*/
CREATE FUNCTION [dbo].[GetChildObjects]
(
@ObjectId int
)
RETURNS @ChildObjects TABLE
(
[object_id] int NOT NULL,
[level] int NOT NULL,
[object_type] int NOT NULL,
[tag] nvarchar(256) NOT NULL,
[description] nvarchar(max) NOT NULL
)
AS
BEGIN
--
-- Insert first object.
--
INSERT INTO @ChildObjects
SELECT
id,
level,
object_type,
tag,
description
FROM dbo.Objects
WHERE id = @ObjectId;
--
-- Stack
--
DECLARE @Stack TABLE
(
ObjectId int NOT NULL,
lvl int NOT NULL
);
--
-- Initialise level for stack
--
DECLARE @lvl int = 1,
@CurrentObjectId INT = NULL;
--
-- Insert current object
--
INSERT INTO @Stack
(
ObjectId,
lvl
)
VALUES
(@ObjectId, @lvl);
--
-- Loop through stack and get child objects
--
WHILE (@lvl > 0)
BEGIN
IF EXISTS (SELECT * FROM @Stack WHERE lvl = @lvl)
BEGIN
--
-- Get object
--
SELECT @CurrentObjectId = ObjectId
FROM @Stack
WHERE lvl = @lvl;
--
-- Insert child objects
--
INSERT INTO @ChildObjects
SELECT
id,
level,
object_type,
tag,
description
FROM dbo.Objects
WHERE parent_object_id = @CurrentObjectId;
--
-- Delete object from stack
--
DELETE FROM @Stack
WHERE lvl = @lvl
AND ObjectId = @CurrentObjectId;
--
-- Insert the childnodes of the current node into the stack
--
INSERT INTO @Stack
(
ObjectId,
lvl
)
SELECT
id,
@lvl + 1
FROM dbo.Objects
WHERE parent_object_id = @CurrentObjectId;
IF (@@ROWCOUNT > 0) -- If the previous statement added one or more nodes, go down for its first child.
SET @lvl = @lvl + 1; -- If no nodes are added, check its sibling nodes
END;
ELSE
SET @lvl = @lvl - 1; -- Back to the level immediately above
END;
RETURN;
END;
GO
Timing is important
Let’s get some times for this function. There’s nothing fancy here, we’re just going to call it for different values, returning more and more results and see how long it takes to finish.
/*
Simple tezt...
*/
SELECT * FROM [dbo].[GetChildObjects](11466) OPTION (RECOMPILE); -- 0 seconds.
SELECT * FROM [dbo].[GetChildObjects](11000) OPTION (RECOMPILE); -- 3 seconds.
SELECT * FROM [dbo].[GetChildObjects](10000) OPTION (RECOMPILE); -- 10 seconds.
SELECT * FROM [dbo].[GetChildObjects](9000) OPTION (RECOMPILE); -- 17 seconds.
SELECT * FROM [dbo].[GetChildObjects](8000) OPTION (RECOMPILE); -- 23 seconds.
SELECT * FROM [dbo].[GetChildObjects](7000) OPTION (RECOMPILE); -- 29 seconds.
SELECT * FROM [dbo].[GetChildObjects](6000) OPTION (RECOMPILE); -- 36 seconds.
SELECT * FROM [dbo].[GetChildObjects](5000) OPTION (RECOMPILE); -- 41 seconds.
SELECT * FROM [dbo].[GetChildObjects](4000) OPTION (RECOMPILE); -- 48 seconds.
SELECT * FROM [dbo].[GetChildObjects](3000) OPTION (RECOMPILE); -- 54 seconds.
SELECT * FROM [dbo].[GetChildObjects](2000) OPTION (RECOMPILE); -- 61 seconds.
SELECT * FROM [dbo].[GetChildObjects](1000) OPTION (RECOMPILE); -- 68 seconds.
SELECT * FROM [dbo].[GetChildObjects](10) OPTION (RECOMPILE); -- 76 seconds.
Taking from 0 seconds for 1 row to 76 seconds for 11,456 rows, we now our timings.
As you can see there has been some performance tuning efforts by the code creators already. So impressed was I by the use of OPTION (RECOMPILE) that I asked why they asked me to take a look.
The execution plan seems fine; doesn’t look like there’s anything we can do with it…
Well, let’s take a look at the execution plan, shall we?
Ah!
No surprises
Listen, we both know that this needs to be rewritten. Here is the crux of the post – how do we know that our re-write isn’t going to introduce unexpected side-effects? Little happy “unspecified-features“? You got it – Unit tests!
Creating Unit Tests
To keep this brief, I’ve installed tSQLt on my tempdb database. The documentation for installing it is very easy to follow on the tSQLt website so head there for instructions. We’re going to create some tests for this function.
/*
tSQLt time!
*/
EXECUTE [tSQLt].[NewTestClass] @ClassName = N'GetChildObjects';
GO
CREATE PROCEDURE [GetChildObjects].[test returns nothing for nothing]
AS
BEGIN
-- Arrange (new "fake" table).
EXECUTE [tSQLt].[FakeTable] @TableName = N'[dbo].[Objects]';
-- Act
SELECT
*
INTO #Actual
FROM [dbo].[GetChildObjects](1);
-- Assert (empty is empty).
EXECUTE [tSQLt].[AssertEmptyTable] @TableName = N'#Actual'
END;
GO
CREATE PROCEDURE [GetChildObjects].[test returns 1 row if no child objects]
AS
BEGIN
-- Arrange (new "fake" table).
EXECUTE [tSQLt].[FakeTable] @TableName = N'[dbo].[Objects]';
INSERT INTO [dbo].[Objects]
(
[id],
[level],
[object_type],
[tag],
[description],
[parent_object_id]
)
VALUES
(
1,
1,
1,
N'Don''t care',
N'Absolutely don''t care',
NULL
);
-- Act
SELECT
*
INTO #Actual
FROM [dbo].[GetChildObjects](1);
SELECT
1 AS [object_id],
1 AS [level],
1 AS [object_type],
N'Don''t care' AS [tag],
N'Absolutely don''t care' AS [description]
INTO #Expected
EXECUTE [tSQLt].[AssertEqualsTable] @Expected = N'#Expected', @Actual = N'#Actual';
END;
GO
CREATE PROCEDURE [GetChildObjects].[test returns multiple rows...say 4]
AS
BEGIN
-- Arrange (new "fake" table).
EXECUTE [tSQLt].[FakeTable] @TableName = N'[dbo].[Objects]';
INSERT INTO [dbo].[Objects]
(
[id],
[level],
[object_type],
[tag],
[description],
[parent_object_id]
)
SELECT
[V].[number],
[V].[number],
[V].[number],
N'Don''t care '+ CONVERT(varchar(2), [V].[number]),
N'Absolutely don''t care '+ CONVERT(varchar(2), [V].[number]),
NULLIF([V].[number] -1, 0)
FROM [master].[dbo].[spt_values] AS [V]
WHERE [V].[type] = 'P'
AND [V].[number] > 0
AND [V].[number] <= 10
ORDER BY [V].[number]
-- Act
SELECT
*
INTO #Actual
FROM [dbo].[GetChildObjects](7);
SELECT
[id] AS [object_id],
[level],
[object_type],
[tag],
[description]
INTO #Expected
FROM [dbo].[Objects]
WHERE [id] >= 7
ORDER BY [id];
EXECUTE [tSQLt].[AssertEqualsTable] @Expected = N'#Expected', @Actual = N'#Actual';
END;
GO
Now we have tests to see what happens when the table is empty, when only 1 row should be returned, and when multiple rows are supposed to be returned. Hey! 4 rows still counts as multiple!
Running these tests couldn’t be simpler:
EXECUTE [tSQLt].[Run] @TestName = N'GetChildObjects';
GO
Re-Write time
The best piece of advice I heard for performance tuning, especially with a hectic schedule, is to
Have a “good enough” goal and have a “time limit”
If the code has to run under a minute and you get it to 51 seconds after 30 minutes, great! Stop – I’m sure you’ve a to-do list that is only getting bigger.
/*
All your bases are belonging to SET!!!
*/
DROP FUNCTION [dbo].[GetChildObjects]; --Don't worry, it's in source control.
GO
CREATE FUNCTION [dbo].[GetChildObjects]
(
@ObjectId int
)
RETURNS TABLE
AS
RETURN
(
WITH [Stack] AS
(
SELECT
[O].[id] AS [object_id],
[O].[parent_object_id]
FROM [dbo].[Objects] AS [O]
WHERE [O].[id] = @ObjectId
UNION ALL
SELECT
[Obj].[id],
[Obj].[parent_object_id]
FROM [Stack]
INNER JOIN [dbo].[Objects] AS [Obj] ON [Stack].[object_id] = [Obj].[parent_object_id]
)
SELECT
[Det].[id] AS [object_id],
[Det].[level],
[Det].[object_type],
[Det].[tag],
[Det].[description]
FROM [Stack] AS [S]
INNER JOIN [dbo].[Objects] AS [Det] ON [S].[object_id] = [Det].[id]
);
GO
Wait! How do you… oh yeah. Unit Tests.
EXECUTE [tSQLt].[Run] @TestName = N'GetChildObjects';
GO
Taking from 0 seconds for 1 row to 31 seconds for 11,456 rows, we now our new timings. And while I’m not a fan of the MAXRECURSION 0 I don’t have a major problem with adding it to an existing RECOMPILE statement.
A great side effect is that we have more information in the execution plan!
A wild index suggestion appeared
There we have it!
Thanks to our unit tests we have a more performant code to send back with confidence that it works the same as the old code…just faster.
Now if you’ll excuse me, my 30 minutes are up and my to-do list is growing.
Yes I am aware that these are normally posted directly after the podcast
I had the absolute pleasure of talking with Kevin Hill ( Blog | Twitter | Podcast ) on the first episode of his new podcast “Data Bits”.
You can check out Kevin’s blog post announcing it here and the actual podcast here.
It was an energising experience reliving past times, talking data tales, and cringing over shared scars received from working with data.
Next, Kevin had Andy Levy ( Blog | Twitter ) as his second guest on the show. It was uplifting hearing them talk shop and realising that maybe I should get more “aggressively friendly”.
I’m currently catching up with the next few episodes including Ginger Grant ( Blog | Twitter ), Jules Behrens ( Twitter ), and Louis Davidson ( Blog | Twitter ).
Thankfully, I’ve been able to scrounge some more time to listen to podcasts in this lock-down. There’s always an upside 😁!
I’ve been trying to improve my knowledge of Python for the past while. I’ve fallen into the same trap that I fell into when I started my DBA learnings though.
When I began my DBA learning, I was a Blog Post Warrior.
I read every blog post and DBA StackExchange post I could find. I had RSS feeds and alerts all set up and firing. I still have these RSS feeds and alerts though. There was nothing wrong with them, the problem was with the way I was using them. That was the extent of my learning. It was all theory and no practice!
Me: Oh, that’s how you do a page restore. Good to know. My Brain: Do you want to try that? Me: No, I read how to do it so I know it now.
Me and Me
I hope I don’t have to point out how sub-optimal this is. I changed how I learned about DBA topics. I built a lab and starting doing some practical work alongside the theory.
Yes, it may mean a slow down in learning throughput but it also means that I only need to learn a topic once. By embracing the practical side, I’ve moulded the information into my brain. I’ve also learned more if I hit errors that aren’t in the blog posts and have to fix them.
Same with Python
I’ve set up RSS feeds. I’ve signed up to newsletters. I’m checking out questions online. But I was not practising.
That has to change now.
I have a dependency on PowerShell. I like it. I find it easier to think that way. So that is where I have to change. Anytime I find myself reaching for PowerShell and I have time in my deadline, I’ll try to do Python as well. That’s where this blog post comes into play.
The Reason
We’re adding information to our internal wiki. We use TFS (Team Foundation Server) for this and TFS allows a variation of markdown. If you want to create a table in this markdown, then you follow the syntax
• :--- means left-align the value • ---: means right-align the value
The current work involves taking the contents of a file and outputting the content with | (pipes) between them, at the start of the line, and at the end of the line.
This is quite a manual process and one that I had done more than twice.
A perfect sign to start automating!
PowerShell
PowerShell came without any real effort or thought.
This seems pretty intuitive to me. Get the content and on each line split out the first part before the tab as the variable login_date and the second part as login_count. After that, it’s a case of adding those variables into a string wrapped with pipes.
Copy and Paste
Python
Python was a bit more difficult to put together and, in the end, I’m not sure I’ve done more than a literal translation of PowerShell to Python. But, hey, it works!
with open(r'C:\Users\shane.oneill\Documents\Versions.txt', 'r') as f:
for line in f:
lg_mth, db_cnt = line.split()
print(f"| {lg_mth} | {db_cnt} |")
Getting my Pyth-ON!
Now I can easily copy and paste this into TFS.
Hey, maybe even create a script as I’ll more than likely need to update this regularly going forward. The most important aspect I take from this is the fact that I can. I have the ability and the practical experience to try this and other examples like this again in the future.
Sin é
That’s it. It wasn’t so much an attempt at a revolutionary post as it was an attempt to get practical experience with Python. Also a chance to get stuck in to writing blog posts again.
Hopefully there’s more to come. Python, PowerShell, and all.