Sisk has asked for the wisdom of the Perl Monks concerning the following question:

Greetings Monks.

I used to be pretty good with perl, but after a few years of not using it (I don't have time for many projects outside of work, where I'm usually expected to use other languages) I've gotten a bit rusty. I've been trying to revamp our online in/out board using a Perl/Tk script ran through perl2exe, but I've run into a problem. There's a memory leak in the script and I can't figure out what I did wrong or if there's a better way to do what I'm trying to do. Can someone help? Here is the script: 
#!/usr/bin/perl
#perl2exe_include Tk::Pane
use Win32::SqlServer;
use Tk;
use Tk::Scrollbar;
require Tk::Pane;

my $mw=new MainWindow;

# Main Window Elements
my $titleBanner=$mw->Label(-text=>"*Text changed to protect the innoce
+nt*",-font=>"Arial 12 bold")->pack();
my $testbutton=$mw->Button(-command=>\&destframe, -text=>"Refresh")->p
+ack();
my $statusFrame=$mw->Scrolled('Frame',-scrollbars=>"oe")->pack();
&destframe;
$mw->repeat(10000, \&destframe);

# Subroutine to get data from the DB
sub GetData{
    my $sqlsrv=Win32::SqlServer::sql_init('*Deleted*','*Params*','*For
+*','*Security*');

    my $batch=<<SQLEND;
    SELECT firstname, lastname, status
    FROM *Now I'm just being paranoid*
    WHERE status is NOT NULL
SQLEND

    my $result=$sqlsrv->sql($batch);


    return $result
}

sub destframe{
    # StatusFrame
    # Get the data first
    my $statusboard=&GetData;

    #create a loop to populate the status frame
    my $count=1;
    foreach my $row (@$statusboard) {
        my $status="$$row{status}";
        if ($status=="1") {
            $status="In";
        }elsif ($status=="0"){
            $status="Out";
        }else{
            $status="Not Set"
        }
        my $name=$statusFrame->Label(-text=>"$$row{firstname} $$row{la
+stname}")->grid(-row=>"$count", -column=>"1");
        my $status=$statusFrame->Label(-text=>"$status")->grid(-row=>"
+$count", -column=>"2");
        $count++;
    }
    
}
MainLoop;

[download]

Replies are listed 'Best First'.
Re: Memory Leak
by BrowserUk (Patriarch) on Jan 05, 2009 at 23:17 UTC

    Two possibilities:

    1. Call $sqlsrv->disconnect() method before your subroutine returns each time.

      From the docs:

      Disconnects from SQL Server and frees up any resources allocated for queries.

      Could be that the DESTROY() method is taking care of it for you, but it wouldn't hurt to try it.

    2. Instead of creating and destroying the connection for every query, create the connection outside the sub and re-use. 
      
my $sqlsrv=Win32::SqlServer::sql_init(
    '*Deleted*','*Params*','*For*','*Security*'
);

sub GetData{

    my $batch=<<SQLEND;
    SELECT firstname, lastname, status
    FROM *Now I'm just being paranoid*
    WHERE status is NOT NULL
SQLEND

    my $result=$sqlsrv->sql($batch);
    return $result
}

...
END{ 
    $sqlsrv->disconnect;
}

      [download]
    Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
    "Science is about questioning the status quo. Questioning authority".
    In the absence of evidence, opinion is indistinguishable from prejudice.
[reply]
[d/l]
[select]
Re: Memory Leak
by zentara (Cardinal) on Jan 05, 2009 at 22:51 UTC
    Hi, I don't have Win32 stuff to actually run this, but your code looks pretty good except for 1 thing. You seem to be calling Win32::SqlServer::sql_init every 10 seconds. I wonder if that leaking? Can you pull the sql_init out of that sub, and just do it once? Then just feed it the $batch in the GetData sub?
    I'm not really a human, but I play one on earth Remember How Lucky You Are
[reply]
Re: Memory Leak
by shmem (Chancellor) on Jan 05, 2009 at 22:53 UTC

    Just guessing... since I don't use Windows - maybe you should init 

        my $sqlsrv=Win32::SqlServer::sql_init('*Deleted*','*Params*','*For
+*','*Security*');

    [download]

    only once, and initialize $sqlsrv in the surrounding scope.

[reply]
[d/l]
[select]
Re: Memory Leak
by Anonymous Monk on Jan 06, 2009 at 17:41 UTC
    Moving the connection out of the subroutine helped (can't believe I missed that one). Thanks. It still leaks a little when the list refreshes, but its much less. I can set it to refresh far less often. Once every 5 minutes would be more than adequate, and that would be a little over 1mb of memory per hour at the rate it's leaking now. Given that this thing probably won't be running more than 10 hours that whould make it livable. I'd still like to know what's causing it though.
[reply]

      A half-arsed guess!

      In your loop, you are creating new labels ("name" & "status") each time around, and every time you re-populate. You do not seem to being doing anything to explicitely remove or destroy the previous set of labels. My guess is that these (the previous sets of labels), are hanging around and causing the memory growth.

      Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
      "Science is about questioning the status quo. Questioning authority".
      In the absence of evidence, opinion is indistinguishable from prejudice.
[reply]

Back to Seekers of Perl Wisdom