1

I Have this project here playing with Philly crime data. I'm building this query to select incidents by type and police district. It takes 21 seconds in PHP and Zend, but under 3 seconds on the command line.

I debug with Zend_Db_Profiler with debug output like so:

numQueries: 2
query: connect
elapsed seconds: 0.0019519329071045
query: SELECT "incident".* FROM "incident" WHERE (dc_dist = '15') AND (lower(text_general_code) = 'homicide') GROUP BY "location_block"
elapsed seconds: 21.583115816116

If I copy and paste that very same select statement into the sqlite3 command line client, it takes maybe 3 seconds.

Am I missing something here? Why is Zend taking 21 seconds?

Update:

running just a script using PDO still results in a 21 second query:

// Create (connect to) SQLite database in file
    $file_db = new PDO('sqlite:../db/crime.db');
    // Set errormode to exceptions
    $file_db->setAttribute(PDO::ATTR_ERRMODE, 
                            PDO::ERRMODE_EXCEPTION);

    $start_time = microtime(true);
    // Select all data from memory db messages table 
    $result = $file_db->query("SELECT 'incident'.* FROM 'incident' WHERE (dc_dist = '15') AND (lower(text_general_code) = 'homicide') GROUP BY 'location_block'");
    $end_time = microtime(true);

    $elapsed_time = $end_time - $start_time;

    print "query took " . $elapsed_time . " seconds\n";

output:

query took 21.751929998398 seconds

2nd Update

It looks like it's the group by addition to the sql statement that's holding it all up. If I remove it, and run the pdo script or the zend framework application, it reaches the same speeds as directly from the command line:

With 'GROUP BY' clause, 23 seconds:

numQueries: 2
query: connect
elapsed seconds: 0.0007779598236084
query: SELECT "incident".* FROM "incident" WHERE (dc_dist = '15') AND (lower(text_general_code) = 'homicide') GROUP BY "location_block"
elapsed seconds: 23.092380046844

Without 'GROUP BY' clause, under a second:

numQueries: 2 query: connect
elapsed seconds: 0.0011060237884521
query: SELECT "incident".* FROM "incident" WHERE (dc_dist = '15') AND (lower(text_general_code) = 'homicide')
elapsed seconds: 0.080777883529663

PrivateBrussels
  • 78
  • 5
  • Does it *always* take about 21 seconds? Is the CLI *always* faster? – Charles Dec 30 '12 at 18:19
  • Always around 21 seconds. Sometimes 24. Every run. Every command line run is around 2 or 3 seconds. In the php code, I've tried running the query a second time to see if it was possible that the first run is doing a database schema thing but that the second run takes the same amount of time. – PrivateBrussels Dec 30 '12 at 19:39
  • Try creating a small script that uses PDO's [SQLite support](http://php.net/ref.pdo-sqlite) to execute the query, without the wrapper that Zend DB provides. Does it take the same amount of time? – Charles Dec 30 '12 at 20:22
  • nope still taking 21 seconds. I'm editing my question to show that. So I guess Zend isn't the culprit. – PrivateBrussels Dec 31 '12 at 00:07
  • Do you see the same delay when running the test script through a web server vs at a command prompt? – Charles Dec 31 '12 at 01:05
  • it looks like it's the group by clause. I've updated my original question. – PrivateBrussels Jan 01 '13 at 16:26

0 Answers0