in reply to Slow response on DBI MySQL query

"select Country from GeoIP where ('$lip' > Start_int) and ('$lip' < End_int) limit 1");

Aside from adding insecurity with dynamic SQL, there's little benefit from possible RDBMS caching. That is, preparing a query that uses palceholders allows the RDBMS to create a PLAN that (it thinks) is best for varying arguments. Also, rerunning the query does not require a new prepare.

And, large loads can be done with a bulk collect.

'$lip' > Start_int) and ('$lip' < End_int)

BETWEEN is inclusive. So a BETWEEN b and c means a >= b AND a <= c. So, there's a difference between (no pun intended) the two statements.

Replies are listed 'Best First'.
Re^2: Slow response on DBI MySQL query
by Fortificus (Novice) on Mar 24, 2015 at 14:06 UTC
    Great point in the between query! It should be a >= b AND a <= c and not a>b and a<c Sorry for the lack of knowledge, but when you say that it does not require a new prepare, how would that work? How would I need to change the query so that $lip is updated? Again, sorry for my lack of knowledge.
[reply]

      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.

[reply]
        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]

In Section Seekers of Perl Wisdom