October 28, 2015 at 4:30 am #31410
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'";
$sqlCmdgrp = $sqlConn.CreateCommand()
$sqlCmdgrp.CommandText = "SELECT GROUP_ID,obj_type FROM dbo.GROUPS where GROUP_LOGIN ='$user'";
sqlNonQuery "INSERT on database what i wanna)";
#write-host insert $user -BackgroundColor DarkCyan
October 28, 2015 at 6:29 am #31427
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 #31440
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 http://dontpad.com/scriptgetacl
October 28, 2015 at 9:27 am #31447
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 #31455
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 #31456
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 #31459
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 #31462
okay, so I do not see you insert code in. What does that look like?
You must be logged in to reply to this topic.