Author Posts

October 28, 2015 at 4:30 am

Hello all,

Can anyone help me optimizing this part of my script? Its fast but it could be a lot faster **i think**.

function ProcessPath ($folder, $filePath)
$folderOwner = 55
##write-host Processing $filePath -BackgroundColor Cyan

LogInfo "Processing $filePath "

$acl = (get-acl filesystem::"$filePath" -ErrorAction SilentlyContinue).Access |Select-Object -ExpandProperty IdentityReference -Unique

foreach ($user in $acl)

$aclid = 0
$Domain,$user = $user -split "\\",2

$sqlCmd = $sqlConn.CreateCommand()
$sqlCmd.CommandText = "SELECT USER_ID,Obj_type FROM dbo.USERS where USER_LOGIN ='$user'";
$userid= $sqlCmd.ExecuteReader();

if (!$userid.HasRows)
$sqlCmdgrp = $sqlConn.CreateCommand()
$sqlCmdgrp.CommandText = "SELECT GROUP_ID,obj_type FROM dbo.GROUPS where GROUP_LOGIN ='$user'";
$groupid= $sqlCmdgrp.ExecuteReader();

if (!$groupid.HasRows)
$objtype ='4'
$objtype =$groupid.GetValue(1);

$objtype =$userid.GetValue(1);

sqlNonQuery "INSERT on database what i wanna)";
#write-host insert $user -BackgroundColor DarkCyan

October 28, 2015 at 6:29 am


Which part of the script is slow/needs optimizing? I only see 2 time consuming actions here. Get-Acl and a foreach loop running a query against a SQL-server. Please also consider using the pre-tag for your script or paste a Gist link. Formatting and such could also be better as it would be easier to read quickly.

October 28, 2015 at 7:21 am


The best improvement from my point of view, would be to insert 1000 records each time instead of just one.
But unfortunately i don't know how to do it and the information i found online wasn't clear enough for me.

can check for it at



October 28, 2015 at 9:27 am

After some time running script performance starts getting very slow.
At the beginning i can get 1.5k inserts on DB per minute. After 50k inserts it gets to 500k inserts per minute.
Could really use some help on this.



October 28, 2015 at 1:56 pm

The penalty on inserts will most likely be on the SQL-server side. There is probably a marginal performance gain opportunity on the client side (your powershell script). Maybe a SQL/DBA could give you some advice on how to configure your database. I imagine the reduction in speed is due to index refreshing on the SQL server as you insert data. Possibly you could "turn-off" indexing while you insert data and rebuild it your self when you are done. A DBA would be able to give good advice here.


October 28, 2015 at 2:27 pm

Problem is that i don't have any index on the table i am inserting. So thats not it whats slowing script...
In the database side i can confirm its optimized. Also have some other applications running in the same database...

October 28, 2015 at 4:48 pm

I found this a while ago when looking into bulk inserting data into sql

In the end I did not put it into practise however it should be useful to you.

October 29, 2015 at 1:18 am

okay, so I do not see you insert code in. What does that look like?