Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Repeated var declarations

Status
Not open for further replies.
audiopro

audiopro

Programmer
Apr 1, 2004
3,165
GB
I have an app which calls the same sub routine a number of times in a single pass. The sub routine is accessing and processing data from a MySql database. I have some local vars in the sub routine, declared with my, and find that sometimes the process slows down to a crawl. Should I be declaring these local vars as globals or are the sub routine calls totally independant of each other?

Keith
 
Sort by date Sort by votes
They are independent. However, what you might consider doing is caching your prepared statements in order to speed things up. Take the following example from last week modified a little.

Code: 
[olive][b]foreach[/b][/olive] [url=http://perldoc.perl.org/functions/my.html][black][b]my[/b][/black][/url] [blue]$name[/blue] [red]([/red][red]qw([/red][purple]bob mary george[/purple][red])[/red][red])[/red] [red]{[/red]
	[black][b]my[/b][/black] [blue]$id[/blue] = [maroon]finduser[/maroon][red]([/red][blue]$name[/blue][red])[/red][red];[/red]
	[url=http://perldoc.perl.org/functions/print.html][black][b]print[/b][/black][/url] [red]"[/red][purple][blue]$name[/blue] -> [blue]$id[/blue][purple][b]\n[/b][/purple][/purple][red]"[/red][red];[/red]
[red]}[/red]

[url=http://perldoc.perl.org/functions/sub.html][black][b]sub[/b][/black][/url] [maroon]finduser[/maroon] [red]{[/red]
	[black][b]my[/b][/black] [blue]$name[/blue] = [url=http://perldoc.perl.org/functions/shift.html][black][b]shift[/b][/black][/url][red];[/red]
	
	[black][b]my[/b][/black] [blue]$id[/blue][red];[/red]
	[black][b]my[/b][/black] [blue]$sth[/blue] = [blue]$dbh[/blue]->[maroon]prepare[/maroon][red]([/red][red]q{[/red][purple]SELECT ID FROM accountuser WHERE username=?[/purple][red]}[/red][red])[/red][red];[/red]
	[blue]$sth[/blue]->[maroon]execute[/maroon][red]([/red][blue]$name[/blue][red])[/red] or [url=http://perldoc.perl.org/functions/die.html][black][b]die[/b][/black][/url] [blue]$dbh[/blue]->[maroon]errstr[/maroon][red];[/red]
	[blue]$sth[/blue]->[maroon]bind_columns[/maroon][red]([/red]\[blue]$id[/blue][red])[/red][red];[/red]
	[blue]$sth[/blue]->[maroon]fetch[/maroon][red];[/red]
	[blue]$sth[/blue]->[maroon]finish[/maroon][red]([/red][red])[/red][red];[/red]
	
	[url=http://perldoc.perl.org/functions/return.html][black][b]return[/b][/black][/url] [blue]$id[/blue][red];[/red]
[red]}[/red]

Now most of the computation in the above example actually occurs with simply preparing the statement handle. To speed things up, we can cache the prepared statement so that it is only prepared once in a closure.

Code: 
[olive][b]foreach[/b][/olive] [url=http://perldoc.perl.org/functions/my.html][black][b]my[/b][/black][/url] [blue]$name[/blue] [red]([/red][red]qw([/red][purple]bob mary george[/purple][red])[/red][red])[/red] [red]{[/red]
	[black][b]my[/b][/black] [blue]$id[/blue] = [maroon]finduser[/maroon][red]([/red][blue]$name[/blue][red])[/red][red];[/red]
	[url=http://perldoc.perl.org/functions/print.html][black][b]print[/b][/black][/url] [red]"[/red][purple][blue]$name[/blue] -> [blue]$id[/blue][purple][b]\n[/b][/purple][/purple][red]"[/red][red];[/red]
[red]}[/red]

[red]{[/red]
	[black][b]my[/b][/black] [blue]$sth[/blue][red];[/red]
	[url=http://perldoc.perl.org/functions/sub.html][black][b]sub[/b][/black][/url] [maroon]finduser[/maroon] [red]{[/red]
		[black][b]my[/b][/black] [blue]$name[/blue] = [url=http://perldoc.perl.org/functions/shift.html][black][b]shift[/b][/black][/url][red];[/red]

		[black][b]my[/b][/black] [blue]$id[/blue][red];[/red]
		[blue]$sth[/blue] ||= [blue]$dbh[/blue]->[maroon]prepare[/maroon][red]([/red][red]q{[/red][purple]SELECT ID FROM accountuser WHERE username=?[/purple][red]}[/red][red])[/red][red];[/red]
		[blue]$sth[/blue]->[maroon]execute[/maroon][red]([/red][blue]$name[/blue][red])[/red] or [url=http://perldoc.perl.org/functions/die.html][black][b]die[/b][/black][/url] [blue]$dbh[/blue]->[maroon]errstr[/maroon][red];[/red]
		[blue]$sth[/blue]->[maroon]bind_columns[/maroon][red]([/red]\[blue]$id[/blue][red])[/red][red];[/red]
		[blue]$sth[/blue]->[maroon]fetch[/maroon][red];[/red]

		[url=http://perldoc.perl.org/functions/return.html][black][b]return[/b][/black][/url] [blue]$id[/blue][red];[/red]
	[red]}[/red]
[red]}[/red]

That statement handle will be happy go lucky as long as the originating $dbh is alive. So that should work under pretty much all environments.

Another place for increasing efficiency would be to have an INDEX at username in the above example. But that's delving into less perlish pursuits, so I'll see if this alone fits your need.

- Miller
 
Upvote 0 Downvote
The delay doesn't appear to be the database processing as the MySql calls are to small look up tables and each single call, when tested on it's own, is instant.

With local vars in the sub routine I call this sub a number of times, the script slows down to a crawl.
With global vars, all of the calls are carried out instantly.

Keith
 
Upvote 0 Downvote
Let's see some code!!! [bigglasses]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[noevil]
Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;
 
Upvote 0 Downvote
a script slowing down can be the result of copious amounts of warnings being generated. Check the error log and see if there are warnings being generated and post your code.

------------------------------------------
- Kevin, perl coder unexceptional! [wiggle]
 
Upvote 0 Downvote
This is the sub routine being called, it is called from several places within the main script. There are no errors reported in the log.
Code: 
sub showsect{
	my $TERM;
	my $Collection;
#offerprice isused to control display of additional pictures in offers and artists network
	my $offerprice=0;
	print "\n<div class='sectsel'>\n";

	$DIMS=150;
	if($_[0] == 1){
		$pic_sql="SELECT TITLE, ARTIST, STYLE, MEDIUM, FORMAT, CATEGORY, WIDTH, HEIGHT, SPRICE, FILENAME, NUM FROM PICTURES WHERE CUSNUM='YES' AND ARTALLOW<>'NO' AND $WhichSection = '$WhichItem' ORDER BY RAND()";
	}elsif($_[0] == 2){
		$pic_sql="SELECT TITLE, ARTIST, STYLE, MEDIUM, FORMAT, CATEGORY, WIDTH, HEIGHT, SPRICE, FILENAME, NUM FROM PICTURES WHERE CUSNUM='YES' AND ARTALLOW<>'NO' AND CATEGORY<>'SOLD' ORDER BY NUM DESC LIMIT 12";
		$WhichItem="New Items";
	}elsif($_[0] == 3){
		$pic_sql="SELECT TITLE, ARTIST, STYLE, MEDIUM, FORMAT, CATEGORY, WIDTH, HEIGHT, SPRICE, FILENAME, NUM FROM PICTURES WHERE CUSNUM='YES' AND ARTALLOW<>'NO' AND MEDIUM LIKE '%SCULPTURE%' ORDER BY RAND()";
		$WhichItem="Sculptures";
	}elsif($_[0] == 4){
		$TERM=$query->param("serchterm") || "";
		open (SLOG, ">>serchlog.txt") || &errormess;
		print SLOG "$TIMELINE";
		print SLOG " - ";
		print SLOG "$TERM";
		print SLOG "\n";
		close (SLOG);
		$pic_sql="SELECT TITLE, ARTIST, STYLE, MEDIUM, FORMAT, CATEGORY, WIDTH, HEIGHT, SPRICE, FILENAME, NUM FROM PICTURES WHERE CUSNUM='YES' AND ARTALLOW<>'NO' AND (MEDIUM LIKE '%$TERM%' OR TITLE LIKE '%$TERM%' OR STYLE LIKE '%$TERM%'OR ARTIST LIKE '%$TERM%') ORDER BY RAND()";

	}elsif($_[0] == 5){
		# Special Offer List

		$pic_sql="SELECT TITLE, ARTIST, STYLE, MEDIUM, FORMAT, CATEGORY, WIDTH, HEIGHT, SPRICE, FILENAME, NUM, SPRICE FROM PICTURES WHERE CUSNUM='YES' AND ARTALLOW<>'NO' AND CATEGORY='DISCOUNT' ORDER BY RAND()";
		$WhichItem="Special Offer";
		# set offerprice to display discount graphic
		$offerprice=1;

	}elsif($_[0] == 6){
		# Artist's network main screen launch

		$pic_sql="SELECT TITLE, ARTIST, STYLE, MEDIUM, FORMAT, CATEGORY, WIDTH, HEIGHT, SPRICE, FILENAME, NUM, SPRICE FROM PICTURES WHERE CUSNUM='YES' AND ARTALLOW<>'NO' AND CATEGORY='ARTNET' ORDER BY RAND()";
		$WhichItem="Artist's Network";
		# set offerprice to suppress bottom images and artist details
		$offerprice=2;
	}
	$pic_sth=$dbh->prepare($pic_sql);
	$pic_sth->execute();
	$pic_rows=$pic_sth->rows;
	my $ArtistStory='';
	
	if($pic_rows > 0){
		if($_[0] == 4){
			print "<h1>Site Search for the term '$TERM' ($pic_rows)</h1>";
		}else{

			$Collection=&make_proper($WhichItem);
			my $bits;
			my $ArtistLink="artstory/".$Collection;
			$ArtistLink=~ s/ //g;
			$ArtistLink=lc($ArtistLink);
			$ArtistLink.=".kjw";
			if(-e $ArtistLink){
				open (LOG, "<".$ArtistLink) || print "cannot open file";
					@allmessages=<LOG>;
					close LOG;
					foreach $bits (@allmessages){
						$ArtistStory.=$bits;
					}
			}

			print "<div class='secthead'>The $Collection Collection ($pic_rows)</div>";
			if($offerprice == 1){
				print "<img src='images2/offerpic.jpg'><br>";
			}

		}
		if($pic_rows == 1){
			print "<p><div class='small'>Click the Picture to Enlarge</div></p>";
		}else{
			print "<p><div class='small'>Click a Picture to Enlarge</div></p>";
		}
		my $printit;
		my @PrintPics;
		my $Piccer;
		my $Caption;
		my $AltCont;
		my $BlankDiv="\n<div class='sectselitem'> \n</div>\n";
		my $numnum;
		my $countr;
		my @pic_results;
		while(@pic_results=$pic_sth->fetchrow_array()){
			++$countr;
			$pic_results[9].=".jpg";
			$pic_results[9]=lc $pic_results[9];
			$pic_results[0]=&make_proper($pic_results[0]);
			$pic_results[1]=&make_proper($pic_results[1]);
			if(uc $WhichSection ne "ARTIST"){
				$Caption="<br>$pic_results[0]<br>$pic_results[1]";
				$AltCont="<br>$pic_results[0] by $pic_results[1] - Click for large picture";
			}else{
				$Caption="<br>$pic_results[0]";
			}
			if($offerprice == 1){
				if($pic_results[11] > 10){
					$pic_results[11]=sprintf("%.2f",$pic_results[11]);
					$Caption.="<br><strong>£$pic_results[11] before discount.</strong>";
				}else{
					$Caption.="<br><strong>Price on Application</strong>";
				}
			}
			$ARTIST=uc($pic_results[1]);
			if($LOCATION==0){
				$Piccer="\n<div class='sectselitem'><a href=\"artdec.cgi?lastsec=$LastSect&amp;critter=$WhichItem&amp;stile=$WhichSection&amp;lastitm=$LastItem&amp;lastinp=$INP&amp;biscuits=$BASKONT&amp;fishnet=$EXERT&amp;call=showbigpic&amp;num=$pic_results[10]&amp;artist=$ARTIST#pictop\"><img border='0' src='$pic_results[9]' width='$DIMS' height='$DIMS' alt=''>";
				if($pic_results[5] eq 'Yes'){
					$Piccer.= "\n<div class='frontextsold'>SOLD\n</div>\n";
				}
				$Piccer.= "$Caption</a><br>\n</div>\n\n";
			}else{
				$Piccer="\n<div class='sectselitem'><a title='Click to show larger image.' href=\"artdec.cgi?lastsec=$LastSect&amp;critter=$WhichItem&amp;stile=$WhichSection&amp;lastitm=$LastItem&amp;lastinp=$INP&amp;biscuits=$BASKONT&amp;fishnet=$EXERT&amp;call=showbigpic&amp;num=$pic_results[10]&amp;artist=$ARTIST&amp;offert=$offerprice#pictop\"><img border='0' src=\"fumnail2.php?imnam=$pic_results[9]&amp;maxwid=$DIMS&amp;maxhi=$DIMS&amp;blow=17\" alt='Click to show larger image.'>";
				if($pic_results[5] eq 'SOLD'){
					$Piccer.= "\n<div class='frontextsold'>SOLD\n</div>\n";
				}
				$Piccer.= "$Caption</a><br>\n</div>\n\n";
			}
			if(($countr < 2) and ($pic_rows%3 == 1)){
				push(@PrintPics,$BlankDiv);
				push(@PrintPics,$Piccer);
				push(@PrintPics,$BlankDiv);
	
			}elsif(($countr < 2) and ($pic_rows%3 == 2)){
				push(@PrintPics,$Piccer);
				push(@PrintPics,$BlankDiv);
			}else{
				push(@PrintPics,$Piccer);
			}
		}

		for($countr=1; $countr<=@PrintPics; ++$countr){
			print "$PrintPics[-$countr]";
		}

		my $art_sql;
		$Collection=uc($Collection);
		$art_sql="SELECT ADDINFO, BIOPIC FROM ARTISTS WHERE NAME='$Collection' AND EXHIB='YES'";
		if($_[0] < 5){
			&GetBioInfo($art_sql,$ARTIST);
		}
		if($EXERT eq 'lookaritetit'){
			print "<p><a href='artdec.cgi?biscuits=&amp;call=exar&amp;pag=exa'>Return to Exhibited Artists</a></p>";
		}
	}else{
		print "<h1>Sorry - No matches were found for the term '$TERM'.</h1>";
		print "<hr><h2>N E E D&nbsp;&nbsp;&nbsp;H E L P ?</h2><a href=\"javascript:contWindow('artnet.cgi?biscuits=BASKIT&amp;call=contact&amp;witch=7&amp;disp=5&amp;dispat=1&amp;mekfile=n&amp;disptype=ED')\"><br>Please Contact Us</a><br><hr>";
	}
	if($_[0] == 4){
		print "<h1>Search Again?</h1>";
		print "\n<div class='serch'>";
		print "\n<div class='logz'><form method='POST' action='artdec.cgi'>Search The Site";
		print "<input type='text' name='serchterm' size='14'>";
		print "<input type='submit' value='Find Me' name='call'></form>";
		print "</div></div>";
	}
	print "<p><a href='artdec.cgi?biscuits=&amp;fishnet=$EXERT&amp;call=hindhex&amp;pag=ind&amp;biscuits=$BASKONT'><img border='0' src='buttons/homepage.jpg'></a>";
	print "</p>";


	if($BASKONT){
		print "<p><a href='artbuy.cgi?fishnet=$EXERT&amp;call=newrtbuy&amp;biscuits=$BASKONT'>Proceed to Checkout</a></p>";
	}
	print "<!-- ENDIV sectsel --></div><!-- ENDIV pagcont --></div><div class='pagcont'><!-- DIV pagcont -->";

}

Keith
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Dino_1000
  • Locked
  • Question
Use the perl debugger to dump all the data in a string variable
Replies
0
Views
646
Dino_1000
Dino_1000
dan
  • Locked
  • Question
add a variable to subject in formmail.pl
Replies
6
Views
793
feherke
feherke
whn
  • Locked
  • Question
How to assign bit shift to a variable? 1
Replies
2
Views
337
whn
whn
brittany1
  • Locked
  • Question
Comparing 3 vars if defined.
Replies
5
Views
430
MillerH
MillerH
markmulligansr
  • Locked
  • Question
How to parse data from a record with fixed data up to a point and then variable length information. 1
Replies
2
Views
255
markmulligansr
markmulligansr

Part and Inventory Search

Sponsor

Back
Top