Help optimizing function

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


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.


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



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.



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.


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…

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.

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