Help optimizing function

Welcome Forums General PowerShell Q&A Help optimizing function

This topic contains 7 replies, has 3 voices, and was last updated by

 
Participant
3 years, 6 months ago.

  • Author
    Posts
  • #31410

    Participant
    Topics: 7
    Replies: 10
    Points: 0
    Rank: Member

    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

    Participant
    Topics: 3
    Replies: 62
    Points: 0
    Rank: Member

    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

    Participant
    Topics: 7
    Replies: 10
    Points: 0
    Rank: Member

    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

    Participant
    Topics: 7
    Replies: 10
    Points: 0
    Rank: Member

    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

    Participant
    Topics: 3
    Replies: 62
    Points: 0
    Rank: Member

    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

    Participant
    Topics: 7
    Replies: 10
    Points: 0
    Rank: Member

    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

    Participant
    Topics: 1
    Replies: 110
    Points: 84
    Rank: Member

    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

    Participant
    Topics: 3
    Replies: 62
    Points: 0
    Rank: Member

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

The topic ‘Help optimizing function’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort