SQL connection takes a long time

[orjanb314]

I have made this script that connects to a mySQL database and selects the last row of the table supplied as input.

#!/usr/bin/perl

use DBI;
use Mysql;

$host = '10.47.115.91:3306';
$database = 'ebno_logging';
$user = 'Ebnolog';
$password = 'pw';
$querytable = $ARGV[0];
$querystring = "SELECT * FROM $querytable ORDER BY id DESC LIMIT 1";

$db = Mysql->connect($host, $database, $user, $password);

$query = $db->query($querystring);

@result = $query->fetchrow;

print $result[1];

The script executes pretty much instantly on my windows machine, but when I run it on my server the connection to the database takes almost 20 seconds. The server is running the linux distribution centOS. Anyone know what could cause the long delay when making a connection?

 

[rottmanja]

After $db = Mysql->connect($host, $database, $user, $password);

add
$db->selectDB($database);

Here is a sub routine that I wrote that might be of some help. Allows you to modularize your sql queries.

	sub dbQuery{
		# Define data array, based on array passed into this subroutine
		my @dataArr = @_;
		
		#define base connection properties
		my $dbHost = $dataArr[0];
		my $dbUser = $dataArr[1];
		my $dbPass = $dataArr[2];
		my $datasource = $dataArr[3];
		
		#Mysql Connection
		my $connect = Mysql->connect($dbHost, $datasource, $dbUser, $dbPass);
		
		#Select database
		$connect->selectDB($datasource);
		
		#Define sql query
		my $myquery = "$dataArr[4]";
		
		#Execute sql query 
		my $execute = $connect->query($myquery);
		
		# Define total fields	
		my $fieldNum = $execute->numfields();
		
		# Create result object
		my @result;
		
		# Iteration variable
		my $n = 0;
		
		# Loop over query and populate array
		while(my @qryResult = $execute->fetchrow) {
			for(my $i=0; $i< $fieldNum; $i++){
				$result[$n][$i] = $qryResult[$i];
			}
			$n++;
		}
		return @result;		
	}

 

[stevexff]

Are you sure it's the connection? You could put in some print statements just to check where the delay actually occurs.

Do you have a lot more rows on your table on the server than you do on your local machine?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[orjanb314]

I have tried to run the script with only the connection statement uncommented and it's the same then. Either way the tables in my database is only around 1000 lines so it shouldn't be an issue.

 

[travs69]

Turn on some tracing and see what's going on.

http://search.cpan.org/~timb/DBI-1.607/DBI.pm#TRACING

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;