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!

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

SQL connection takes a long time

Status
Not open for further replies.

orjanb314

Programmer
Aug 7, 2008
3
NO
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?
 
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.

Code:
	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;		
	}
 
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::perlDesignPatterns)[/small]
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top