Help optimizing function

This topic contains 7 replies, has 3 voices, and was last updated by Profile photo of Tore Groneng Tore Groneng 1 year, 5 months ago.

  • Author
    Posts
  • #31410
    Profile photo of Arestas
    Arestas
    Participant

    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

  • #31427
    Profile photo of Tore Groneng
    Tore Groneng
    Participant

    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.

  • #31440
    Profile photo of Arestas
    Arestas
    Participant

    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

  • #31447
    Profile photo of Arestas
    Arestas
    Participant

    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

  • #31455
    Profile photo of Tore Groneng
    Tore Groneng
    Participant

    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

  • #31456
    Profile photo of Arestas
    Arestas
    Participant

    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...

  • #31459
    Profile photo of Peter Jurgens
    Peter Jurgens
    Participant

    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.

  • #31462
    Profile photo of Tore Groneng
    Tore Groneng
    Participant

    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.