in reply to Re^2: Slow response on DBI MySQL query
in thread Slow response on DBI MySQL query

sorry for my lack of knowledge.

No reason to be sorry about not knowing. Ignorance is not an excuse (to receive pardon) though it is an excellent reason to ask a question!

Change $lip to a question mark: WHERE ? BETWEEN ... Then, when execute()ing the query, pass the parameter. If in a loop to run multiple times, for example, the prepare would exist before the loop, the execute (and fetch) within it.

Replies are listed 'Best First'.
Re^4: Slow response on DBI MySQL query
by Fortificus (Novice) on Mar 24, 2015 at 14:49 UTC
    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]
[reply]
[d/l]

      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]

In Section Seekers of Perl Wisdom