Re^4: Slow response on DBI MySQL query

That makes sense! Since this is a sub-routine and it gets called multiple times from the main body (please see example below), would it make sense for me to do the prepare on the main body and just execute in the sub-routine? Did I understand your advice correctly?

if (($message[6] eq "ASA-6-302015") || ($message[6] eq "ASA-6-302013")
+) {
        $Config_found = 1;
        $Accepted++;
        $Action="Allow";
        # $Scountry = &Obtain_GeoIP ($message[14]);
        my $sqlinsert = $dbh->prepare("insert into `$Table_Name` ( Mon
+th, Day, Time, Host, ASA_ID, Protocol, Source_Segment, Source_IP, Sou
+rce_Port, Source_NAT_IP, Source_NAT_PORT, Source_DNS, Source_GeoIP, S
+ource_Domain, Destination_Segment, Destination_IP, Destination_Port, 
+Destination_NAT_IP, Destination_NAT_Port, Destination_DNS, Destinatio
+n_GeoIP, Destination_Domain, Action, Severity, Notes, Full_message ) 
+values ( '$message[0]', '$message[1]', '$time', '$message[5]', '$mess
+age[6]', '$message[9]', '$message[13]', '$message[14]','$message[15]'
+,'$message[16]','$message[17]', '$Sdns', '$Scountry', '$Sdomain', '$m
+essage[19]', '$message[20]', '$message[21]', '$message[22]', '$messag
+e[23]', '$Ddns', '$Dcountry', '$Ddomain', '$Action', '$severity', '$n
+otes', '$original_line'  )");
        $sqlinsert->execute();
        next;
        }
[download]

Comment on Re^4: Slow response on DBI MySQL query Download Code

Replies are listed 'Best First'.
Re^5: Slow response on DBI MySQL query by chacham (Prior) on Mar 24, 2015 at 15:28 UTC
Yes. Also, change the INSERT to use parameters. It is insecure using variables to create a dynamic SQL statement. Using a variable for the table name is particularly bad, and cannot be rewritten via a prepare. For separate table, use separate queries. (Well, except when an INSERT ALL can be used.) If possible, the INSERT and SELECT can be made into one statement: INSERT INTO ... SELECT ... WHERE This setup works excellently for multiple parameters, allowing a complex action to be executed as one. SQL works with sets of data, and that is best, where possible.	[reply]

Replies are listed 'Best First'.

Re^5: Slow response on DBI MySQL query
by chacham (Prior) on Mar 24, 2015 at 15:28 UTC

Yes. Also, change the INSERT to use parameters. It is insecure using variables to create a dynamic SQL statement. Using a variable for the table name is particularly bad, and cannot be rewritten via a prepare. For separate table, use separate queries. (Well, except when an INSERT ALL can be used.)

If possible, the INSERT and SELECT can be made into one statement: INSERT INTO ... SELECT ... WHERE This setup works excellently for multiple parameters, allowing a complex action to be executed as one. SQL works with sets of data, and that is best, where possible.

[reply]