Welcome Guest, Not a member yet? Register   Sign In
Query Binding problem
#1

[eluser]majidmx[/eluser]
Hi Everyone,

Today I faced a very strange issue. I have a nested SQL command with only one param being Binded to it. Something like this :

Code:
$db_sql    =    'SELECT * , SUM(tbl2_saleNum) AS TotalSale , '.
                        '(SELECT MAX(tbl1_end_date) FROM table1 WHERE tbl1_tbl4_id = tbl3.tbl4id) AS LAST_tbl1_DATE ,'.
                        'cmp_advertiser_payout, '.
                        'TRUNCATE(SUM(cmp_advertiser_payout * tbl2_saleNum),2) AS TOTAL_AMOUNT '.
                    'FROM table2 '.
                    'LEFT JOIN table3 ON tbl2_tbl3_id = cmp_id '.
                    'LEFT JOIN table4 on tbl3.tbl4id = table4.tbl4id '.
                    'WHERE DATE(tbl2_date_time) > (IFNULL((SELECT MAX(tbl1_end_date) FROM table1 WHERE tbl1_tbl4_id = tbl3.tbl4id),0)) '.
                    'AND DATE(tbl2_date_time) < CURDATE() '.
                    'AND tbl3.tbl4id =  ? '.
                    'GROUP BY  cmp_id '.
                    'HAVING TotalSale > 0 '.
                    'ORDER BY cmp_id';
$db_val    =    array($networkid);            
$db_res    =    $this->dbr->query($db_sql , $db_val);

And it used to work very well and all of a suddent it has stopped working on the live server, without showing any error on the screen or PHP log file or CI's log file.
But when I replace the Binding with the real value and just give up using the binding it works pretty well :

Code:
$db_sql    =    'SELECT * , SUM(tbl2_saleNum) AS TotalSale , '.
                        '(SELECT MAX(tbl1_end_date) FROM table1 WHERE tbl1_tbl4_id = tbl3.tbl4id) AS LAST_tbl1_DATE ,'.
                        'cmp_advertiser_payout, '.
                        'TRUNCATE(SUM(cmp_advertiser_payout * tbl2_saleNum),2) AS TOTAL_AMOUNT '.
                    'FROM table2 '.
                    'LEFT JOIN table3 ON tbl2_tbl3_id = cmp_id '.
                    'LEFT JOIN table4 on tbl3.tbl4id = table4.tbl4id '.
                    'WHERE DATE(tbl2_date_time) > (IFNULL((SELECT MAX(tbl1_end_date) FROM table1 WHERE tbl1_tbl4_id = tbl3.tbl4id),0)) '.
                    'AND DATE(tbl2_date_time) < CURDATE() '.
                    "AND tbl3.tbl4id =  $networkid ".
                    'GROUP BY  cmp_id '.
                    'HAVING TotalSale > 0 '.
                    'ORDER BY cmp_id';        
$db_res    =    $this->dbr->query($db_sql);

Do you have any idea what could be the reason, as it really scares me for other sql Bidnings that we have in our system .

Thanks,
MajiD
#2

[eluser]majidmx[/eluser]
Interesting enough, it's working on localhost. But the execution time you can really feel it, it's like 20 seconds for binded query and 1 second for the direct one.
#3

[eluser]coolgeek[/eluser]
Have you enabled profiling and looked at the query that actually gets executed?

Code:
$this->output->enable_profiler(true);

Are you sure that the expected data in table3 and table4 (for the joins) on the live server does in fact exist?
#4

[eluser]majidmx[/eluser]
Hey,

Thanks for the reply.
I tried the profiler; with query binding the execution time is 10 seconds and without the query bidning the execution time is 0.3.

I'm pretty sure the data exists because the only change I make to the code is as follow.

Code:
'AND tbl3.tbl4id =  ? '.

...

$db_val    =    array($networkid);            
$db_res    =    $this->dbr->query($db_sql , $db_val);

to
Code:
"AND tbl3.tbl4id =  $networkid ".

...

$db_res    =    $this->dbr->query($db_sql);

I'm pretty sure it has something to do with this nested query, because I Have more weighted queries but they're simpler and they're easily executed.
What do you think ?
#5

[eluser]danmontgomery[/eluser]
It would be helpful if you'd paste the query being run in both cases here. It might help you spot the error, or make it possible for one of us to do so.
#6

[eluser]coolgeek[/eluser]
The MySQL optimizer seems to be choosing different optimization plans depending on whether you use query binding or not.

The reason I asked you to run the profiler isn't as much because of the timer, but because it prints out the exact query that gets executed. I assume that you have observed and ensured that there is nothing bizarre going on with the bound variable.

It may be possible that an implicit type conversion is being performed on tbl3.tbl4id in the 'AND tbl3.tbl4id = ? ' bind clause. I used to run into that on Oracle. I don't know whether that is an issue on MySQL.

From a MySQL prompt you can use the EXPLAIN command to see how the optimizer is going to execute that query.

mysql> explain 'paste your query here';

without the quotes. In your case, you would want to explain each of the two queries to find the divergence.
#7

[eluser]majidmx[/eluser]
Hi,

Thanks for your replies.
The problem with the server not having the Errors logged, is that the webserver's timeout is much less than the PHP timeout and that's shy we don't receive any error messages.

I have multiple DB connection and profiler doesn't work with that, but anyway, I changed the queries to work with default DB and the only difference in the generated SQL queries is :

Code:
campaign.networkid =  '41'
when it binding the variable and
Code:
campaign.networkid =  41
in regular case.
Which I have attached the results of the EXPLAIN for both cases. An it seems that there is a type conversion for the case of
Code:
campaign.networkid =  '41'

What do you guys think ?

Thanks

P.S : Not sure if the screenshots have been attached or not, here are the links :

case '41'
case 41
#8

[eluser]coolgeek[/eluser]
I can't see enough of a difference between the two plans to point toward a source of the problem.

The only other suggestion I have is to try the MySQL query profiler if you are able to do so.

http://dev.mysql.com/tech-resources/arti...filer.html




Theme © iAndrew 2016 - Forum software by © MyBB