Data from Datatables really slow

This topic contains 2 replies, has 2 voices, and was last updated by  Aaron 4 months, 3 weeks ago.

  • Author
    Posts
  • #67428

    Aaron
    Participant

    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
    	}
    
  • #67441

    Max Kozlov
    Participant

    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

  • #67461

    Aaron
    Participant

    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!

You must be logged in to reply to this topic.