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)
{
if($groupid){
$groupid.Close();}
if($userid){
$userid.Close();}

$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();
$userid.Read();

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

if (!$groupid.HasRows)
{
$objtype ='4'
$aclIdfinal='5'
}
else
{
$objtype =$groupid.GetValue(1);
$aclIdfinal=$groupid.GetValue(0);
}

$groupid.Close();
}
else
{
$objtype =$userid.GetValue(1);
$aclIdfinal=$userid.GetValue(0);
}

$objtype
$userid.Close();
sqlNonQuery "INSERT on database what i wanna)";
#write-host insert $user -BackgroundColor DarkCyan

October 28, 2015 at 6:29 am

hi

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

Well,

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

Regards,

Arestas

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.

Regards,

Arestas

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.

Cheers
Tore

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

http://m.sqlmag.com/powershell/bulk-copy-data-sql-server-powershell

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?