Author Posts

March 27, 2017 at 8:47 pm

I'm connecting to a database with multiple tables, one is 'groups', 'doctype' and 'catalogs'.

This is necessary as the tables reference each other using the GUIDs, Groups have access to catalogs which contain doctypes... etc

I've saved these to datatables using the below code:

 #build catalog reference
	$datagridview1.AutoSizeColumnsMode = 'DisplayedCells'
	$connectionstring = "Server = SQLSERVER1; Database = ContentCentral; Integrated Security = True"
	$connection = New-Object System.Data.SqlClient.SqlConnection
	$connection.ConnectionString = $connectionstring
	$connection.Open()
	
	$CatalogReferenceQuery = "SELECT ID,Name FROM [ContentCentral].[dbo].[Catalog]"
	
	$command = $connection.CreateCommand()
	$command.CommandText = $CatalogReferenceQuery
	
	$CatalogResults = ($command.ExecuteReader())
	$global:CatalogReference = New-Object System.Data.DataTable
	$CatalogReference.Load($CatalogResults)
	$connection.Close()
	
	
	#build Doctype reference
	$connectionstring = "Server = SQLSERVER1; Database = ContentCentral; Integrated Security = True"
	$connection = New-Object System.Data.SqlClient.SqlConnection
	$connection.ConnectionString = $connectionstring
	$connection.Open()
	
	$DocTypeReferenceQuery = "SELECT ID,CatalogId FROM [ContentCentral].[dbo].[DocType]"
	
	$command = $connection.CreateCommand()
	$command.CommandText = $DocTypeReferenceQuery
	
	$DocTypeResults = ($command.ExecuteReader())
	$global:DocTypeReference = New-Object System.Data.DataTable
	$DocTypeReference.Load($DocTypeResults)
	$connection.Close()
	
	#build Group reference
	$connectionstring = "Server = SQLSERVER1; Database = ContentCentral; Integrated Security = True"
	$connection = New-Object System.Data.SqlClient.SqlConnection
	$connection.ConnectionString = $connectionstring
	$connection.Open()
	
	$GroupReferenceQuery = "SELECT ID,Name FROM [ContentCentral].[dbo].[Group]"
	
	$command = $connection.CreateCommand()
	$command.CommandText = $GroupReferenceQuery
	
	$GroupResults = ($command.ExecuteReader())
	$global:GroupReference = New-Object System.Data.DataTable
	$GroupReference.Load($GroupResults)
	$connection.Close()

The datatable's look like...

$GroupReference
ID                                                          Name
--                                                          ----
20bc949a-0292-4339-8b21-8b42526d4508                        Account Analysis
b79ac147-3869-4d06-ae71-7336d8e693cc                        Administration
5ad9fbdd-49fb-4cee-8fe5-4c4b993ce2ca                        Administrators
30607749-bb26-406d-bc58-db86bfecece9                        Banc Services
590f7f60-971a-4fe2-9e2d-7c9043248b6d                        Cash Management
61740d3f-767b-444a-981c-359d068f1754                        Central Files
e52b5ee1-f2eb-4a32-8b71-12f59ed6eb94                        Central Files View Only
e0fb522c-4cd1-4bac-9685-3a0ad659c823                        Certificate of Deposits
13906f6b-0615-4882-8081-877f3f71b992                        Collateral
d9e994ff-0e42-4edf-b647-4f1d8b1a3d76                        Compliance
d9d1ab4f-b19d-476c-8d97-e1b7dd380ff0                        Control
3afda4d1-69c7-46d1-bf36-49da1574c36c                        DailyMaintenance Logs
9b065cab-43df-4970-b7b6-e65719085941                        EVPCOO

$CatalogReference

ID                                                          Name
--                                                          ----
05546661-9a9c-4915-8e38-10907b771c04                        Account Analysis
d901d0e1-3c5f-459c-8cbb-49b9cf32206f                        Administration
71ed5e7d-4435-4bc2-8b65-4b39a9bb2c2f                        Audit
1774b8c0-f759-49bd-8661-abe78e45625f                        Banc Services
4aa0e0df-13a8-4b3c-9882-bed3ed45f9f7                        Cash Management
55ddaecc-2aae-45a5-bdd3-9573e36e4536                        Central Files
9814b9be-9177-4f6a-9d01-ad255035dde8                        Certificate of Deposits
89a3c0a9-9935-4d67-bbc3-18baa4fa425a                        Collateral
42d3fc77-c906-4807-8a0a-dbfe287b3021                        COMPLIANCE
88b40d2f-e6c1-4dad-9cc3-a2a1ab012e18                        Control
9356279e-0ec5-44e8-b3ee-7e360643adee                        Daily Maintenance Logs
4cac0a34-a140-4fa8-8f10-94b61d22098b                        EVPCOO

$DocTypeReference

ID                                                          CatalogId
--                                                          ---------
03b15dde-1362-4252-b556-19cfc4fc3958                        335d98ff-ce07-495e-addd-03f2c06fca06
95ac091c-e976-49f5-b806-31b24bc770e7                        335d98ff-ce07-495e-addd-03f2c06fca06
6fc63062-04cd-43f6-8147-369af1fa19ea                        335d98ff-ce07-495e-addd-03f2c06fca06
53215a7f-276a-4f0d-9e85-6ddc81760221                        335d98ff-ce07-495e-addd-03f2c06fca06
6c138a80-e11c-4406-918e-b3995cea7e6f                        335d98ff-ce07-495e-addd-03f2c06fca06
2d12eef0-db56-4aec-9a2a-bc0338f84911                        335d98ff-ce07-495e-addd-03f2c06fca06
05bc9f06-73fe-4a61-a63b-be03db0a249d                        335d98ff-ce07-495e-addd-03f2c06fca06
6fa51b63-fe82-41dc-b667-f6b3f9a7d1d1                        335d98ff-ce07-495e-addd-03f2c06fca06
77afeeb9-632b-4d37-bd86-239e85538814                        05546661-9a9c-4915-8e38-10907b771c04
a51a36f0-f0e8-46b6-811a-4b5059b8dcc6                        05546661-9a9c-4915-8e38-10907b771c04
24c005de-d1dd-42b4-ad9f-7801a6447fa3                        05546661-9a9c-4915-8e38-10907b771c04
1b4625c1-dd90-4edf-b244-91bc70e6fe62                        05546661-9a9c-4915-8e38-10907b771c04
81fc91ed-e40a-4ce9-8984-97960afc3eee                        05546661-9a9c-4915-8e38-10907b771c04
a2f2d9df-c658-4dee-ba84-a237f2888985                        05546661-9a9c-4915-8e38-10907b771c04
54a6831f-bdeb-4c55-b03d-f81cd11a1957                        05546661-9a9c-4915-8e38-10907b771c04
20d8c79b-e4ae-41c6-ba0f-f8b2bab71d75                        05546661-9a9c-4915-8e38-10907b771c04
69b3e956-60a2-46d6-bd7c-040f1e42cf3a                        93b04769-192f-4368-bd8c-150e0b5c3acf
202ea888-a0b5-43b8-9e90-04215258fb41                        93b04769-192f-4368-bd8c-150e0b5c3acf
3e599b00-fc6f-43b4-82d7-0a34e9413c3b                        93b04769-192f-4368-bd8c-150e0b5c3acf

$groupreference

$groupreference contains 42 rows
$doctypereference contains 266 rows
$catalogreference contains 32 rows

Below is the code I'm using, it pulls into a datagridview in a format like...

Catalog|permission1|permission2|etc

It slows down dramatically on this line, taking about a second or two to produce each line. I'm no longer making database calls but referencing the 3 datatables I created earlier (which i thought would improve speed).

Long story short, is there a way to improve the speed of this process?

"$(($catalogreference | ? { $_.id -eq (($DocTypeReference | ? { $_.id -eq $doctypeid }).catalogid.guid) }).name)"
$connectionstring = "Server = SQLSERVER; Database = ContentCentral; Integrated Security = True"
	$connection = New-Object System.Data.SqlClient.SqlConnection
	$connection.ConnectionString = $connectionstring
	$connection.Open()
	
	$PermissionQuery = "SELECT doctypeid FROM [ContentCentral].[dbo].[DocTypePermission_S] where Userid LIKE '$($userHash.get_item($selected))' and type = 'user'"
	
	$command = $connection.CreateCommand()
	$command.CommandText = $PermissionQuery
	
	$PermissionResult = ($command.ExecuteReader())
	$Permissiontable = New-Object System.Data.DataTable
	$Permissiontable.Load($PermissionResult)
	$connection.Close()
	
	$DocTypeIDs = ($permissiontable | foreach-object { $_.doctypeid.guid }) | sort-object -unique
	
	$catalognamehash = @{ }
	$names = @()
	
	if ($doctypeids)
	{
	
		foreach ($DocTypeID in $DoctypeIDs)
		{
			$connectionstring = "Server = vmibsql1; Database = ContentCentral; Integrated Security = True"
			$connection = New-Object System.Data.SqlClient.SqlConnection
			$connection.ConnectionString = $connectionstring
			$connection.Open()
			
			$rightsQuery = "SELECT AllowDocView,AllowDocSearch,AllowDocBrowse,AllowDocAdd,AllowDocEdit,AllowDocMetaEdit,AllowDocDelete,AllowApprovalProcessAssign,AllowWorkQueueAssign,AllowRetentionOverride,AllowDocTypeAdmin,AllowApprovalProcessAdmin,AllowWorkQueueAdmin,AllowDocShare,AllowDocViewInApprovalQueue,AllowAnnotationWrite,AllowAnnotationPrint,AllowDocDownload FROM [ContentCentral].[dbo].[DocTypePermission_S] where doctypeid = '$doctypeid' and Userid LIKE '$($userHash.get_item($selected))' and type = 'user'"
			
			$command = $connection.CreateCommand()
			$command.CommandText = $rightsQuery
			
			$RightsResult = ($command.ExecuteReader())
			$rightstable = New-Object System.Data.DataTable
			$rightstable.Load($RightsResult)
			$connection.Close()

			##really slows down here...##
			"$(($catalogreference | ? { $_.id -eq (($DocTypeReference | ? { $_.id -eq $doctypeid }).catalogid.guid) }).name)"
			$names += "$(($catalogreference | ? { $_.id -eq (($DocTypeReference | ? { $_.id -eq $doctypeid }).catalogid.guid) }).name)"
		}
		#only need the unique rows
		$names = $names | Sort-Object -Unique
	}

March 28, 2017 at 6:51 am

1. you do not need to open/close connections so many times
2. get rid of "" around your code $name += "..."
3. your slowdown cause can be $names +=
arrays is static objects and every += to array fully recreate array

 C:\> $array = @()
 C:\> $hash = @{}
 C:\> [System.Collections.ArrayList]$arraylist = @()
 C:\> $collection = {}.Invoke()
 C:\>  measure-command { 1..10000 | %{ $array += $_ } }

Seconds           : 3
Milliseconds      : 56
Ticks             : 30565578

 C:\>  measure-command { 1..10000 | %{ $hash[$_] = $_ } }

Seconds           : 0
Milliseconds      : 115
Ticks             : 1152038

 C:\>  measure-command { 1..10000 | %{ [void]$arraylist.add($_) } }

Seconds           : 0
Milliseconds      : 106
Ticks             : 1067340

 C:\>  measure-command { 1..10000 | %{ [void]$collection.add($_) } }

Seconds           : 0
Milliseconds      : 107
Ticks             : 1076668

4. let sql do this for you, it's designed for it 🙂
use SELECT ... JOIN .... query

March 28, 2017 at 3:49 pm

Max,

Learning more about SQL and learning Select...Join was the true key here!

I wiped almost all my powershell code away for SQL queries that took the script from 30 seconds per click to instantly seeing results...Thanks you again!