function ExecNonQuery
{
param ($conStr, $cmdText)
# Determine if parameters were correctly populated.
if (!$conStr -or !$cmdText)
{
# One or more parameters didn't contain values.
write-Host "ExecNonQuery function called with no connection string and/or command text."
}
else
{
write-Host "Creating SQL Connection..."
# Instantiate new SqlConnection object.
$Connection = New-Object System.Data.SQLClient.SQLConnection
# Set the SqlConnection object's connection string to the passed value.
$Connection.ConnectionString = $conStr
# Perform database operations in try-catch-finally block since database operations often fail.
try
{
#write-Host "Opening SQL Connection..."
# Open the connection to the database.
$Connection.Open()
#write-Host "Creating SQL Command..."
# Instantiate a SqlCommand object.
$Command = New-Object System.Data.SQLClient.SQLCommand
# Set the SqlCommand's connection to the SqlConnection object above.
$Command.Connection = $Connection
# Set the SqlCommand's command text to the query value passed in.
$Command.CommandText = $cmdText
#write-Host "Executing SQL Command..."
# Note, to see a result back here, ExecuteNonQuery has been changed to ExecuteScalar
write-host $Command.ExecuteScalar()
}
catch [System.Data.SqlClient.SqlException]
{
# A SqlException occurred. According to documentation, this happens when a command is executed against a locked row.
#write-Host "One or more of the rows being affected were locked. Please check your query and data then try again."
}
catch
{
# An generic error occurred somewhere in the try area.
write-Host "An error occurred while attempting to open the database connection and execute a command."
}
finally {
# Determine if the connection was opened.
if ($Connection.State -eq "Open")
{
write-Host "Closing Connection..."
# Close the currently open connection.
$Connection.Close()
}
}
}
}
# Specify SQL Connection String.
$master = "Data Source=###;Initial Catalog=Master;Integrated Security=SSPI;"
$core = "Data Source=###;Initial Catalog=Core;Integrated Security=SSPI;"
# Specify SQL Connection String.
$runs = 10
$stopWatch = [System.Diagnostics.Stopwatch]::StartNew()
for ($i=0; $i -le $runs; $i++)
{
ExecNonQuery -conStr $core -cmdText "SELECT COUNT (*) FROM aspnet_Users"
ExecNonQuery -conStr $core -cmdText "SELECT COUNT (*) FROM aspnet_UsersInRoles"
ExecNonQuery -conStr $core -cmdText "exec sp_executesql N'SELECT [ItemId], [Order], [Version], [Language], [Name], [Value], [FieldId], [MasterID], [ParentID]
FROM (
SELECT [Id] as [ItemId], 0 as [Order], 0 as [Version], '''' as [Language], [Name], '''' as [Value], [TemplateID] as [FieldId], [MasterID], [ParentID]
FROM [Items]
UNION ALL
SELECT [ParentId] as [ItemId], 1 as [Order], 0 as [Version], '''' as [Language], NULL as [Name], '''', NULL, NULL, [Id]
FROM [Items]
UNION ALL
SELECT [ItemId], 2 as [Order], 0 AS [Version], '''' as [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
FROM [SharedFields]
UNION ALL
SELECT [ItemId], 2 as [Order], 0 AS [Version], [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
FROM [UnversionedFields]
UNION ALL
SELECT [ItemId], 2 as [Order], [Version], [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
FROM [VersionedFields]
) as temp WHERE [ItemId] IN (SELECT [ID] FROM [Items] WITH (nolock) WHERE [TemplateID]=@p0 OR [TemplateID]=@p1 OR [TemplateID]=@p2 OR [TemplateID]=@p3 OR [TemplateID]=@p4 OR [TemplateID]=@p5 OR [TemplateID]=@p6 OR [ID]=@p7 OR [ParentID]=@p8 OR [TemplateID]=@p9 OR [TemplateID]=@p10 OR [ParentID]=@p11)
ORDER BY [ItemId], [Order] ASC, [Language] DESC, [Version] DESC',N'@p0 uniqueidentifier,@p1 uniqueidentifier,@p2 uniqueidentifier,@p3 uniqueidentifier,@p4 uniqueidentifier,@p5 uniqueidentifier,@p6 uniqueidentifier,@p7 uniqueidentifier,@p8 uniqueidentifier,@p9 uniqueidentifier,@p10 uniqueidentifier,@p11 uniqueidentifier',@p0='AB86861A-6030-46C5-B394-E8F99E8B87DB',@p1='E269FBB5-3750-427A-9149-7AA950B49301',@p2='455A3E98-A627-4B40-8035-E683A0331AC7',@p3='239F9CF4-E5A0-44E0-B342-0F32CD4C6D8B',@p4='A87A00B1-E6DB-45AB-8B54-636FEC3B5523',@p5='F68F13A6-3395-426A-B9A1-FA2DC60D94EB',@p6='B6F7EEB4-E8D7-476F-8936-5ACE6A76F20B',@p7='11111111-1111-1111-1111-111111111111',@p8='11111111-1111-1111-1111-111111111111',@p9='EB06CEC0-5E2D-4DC4-875B-01ADCC577D13',@p10='B2A1FF6C-6B1F-41EF-B2E1-AA598CF7335F',@p11='C74AC643-53C8-4F1E-9508-840CDC72AACA'"
ExecNonQuery -conStr $master -cmdText "exec sp_executesql N'SELECT [ItemId], [Order], [Version], [Language], [Name], [Value], [FieldId], [MasterID], [ParentID]
FROM (
SELECT [Id] as [ItemId], 0 as [Order], 0 as [Version], '''' as [Language], [Name], '''' as [Value], [TemplateID] as [FieldId], [MasterID], [ParentID]
FROM [Items]
UNION ALL
SELECT [ParentId] as [ItemId], 1 as [Order], 0 as [Version], '''' as [Language], NULL as [Name], '''', NULL, NULL, [Id]
FROM [Items]
UNION ALL
SELECT [ItemId], 2 as [Order], 0 AS [Version], '''' as [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
FROM [SharedFields]
UNION ALL
SELECT [ItemId], 2 as [Order], 0 AS [Version], [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
FROM [UnversionedFields]
UNION ALL
SELECT [ItemId], 2 as [Order], [Version], [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
FROM [VersionedFields]
) as temp WHERE [ItemId] IN (SELECT [ID] FROM [Items] WITH (nolock) WHERE [ParentID] = @itemId)
ORDER BY [ItemId], [Order] ASC, [Language] DESC, [Version] DESC',N'@itemId uniqueidentifier',@itemId='734FB52F-33E3-4BCF-ADA9-27F1A4D8B4E1'"
ExecNonQuery -conStr $master -cmdText "exec sp_executesql N'SELECT [ItemId], [Order], [Version], [Language], [Name], [Value], [FieldId], [MasterID], [ParentID]
FROM (
SELECT [Id] as [ItemId], 0 as [Order], 0 as [Version], '''' as [Language], [Name], '''' as [Value], [TemplateID] as [FieldId], [MasterID], [ParentID]
FROM [Items]
UNION ALL
SELECT [ParentId] as [ItemId], 1 as [Order], 0 as [Version], '''' as [Language], NULL as [Name], '''', NULL, NULL, [Id]
FROM [Items]
UNION ALL
SELECT [ItemId], 2 as [Order], 0 AS [Version], '''' as [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
FROM [SharedFields]
UNION ALL
SELECT [ItemId], 2 as [Order], 0 AS [Version], [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
FROM [UnversionedFields]
UNION ALL
SELECT [ItemId], 2 as [Order], [Version], [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
FROM [VersionedFields]
) as temp WHERE [ItemId] IN (SELECT [ID] FROM [Items] WITH (nolock) WHERE [ParentID] = @itemId)
ORDER BY [ItemId], [Order] ASC, [Language] DESC, [Version] DESC',N'@itemId uniqueidentifier',@itemId='11A81FBC-77FB-40D9-A3B3-0078106B07FC'"
write-Host "Query Complete!"
}
$stopWatch.Stop()
Write-Host "total " $stopWatch.ElapsedMilliseconds "ms"
Write-Host "average " ($stopWatch.ElapsedMilliseconds/$runs) "ms"
