2

I have a quite heavy SQL search query that takes a few minutes to complete, called from a PHP script that's called by an ajax request.

Problem is, users often click the search button many times from impatience, and each time it creates a new ajax request, and a new PHP script execution. But the old ones continue executing, even though the connection has been dropped. This causes the SQL server load to constantly be at 100% CPU usage.

So I tested my theory that script execution continues even after the browser tab is closed. I used 2 types of queries, an ad hoc query and a stored procedure execution, both methods do the same thing, inserting the numbers 1-9 into a table, with a 2 second delay between each number.

Ad hoc query:

for($i = 1; $i < 10; $i++){
    $sql = "INSERT INTO t (i) VALUES(?)";
    $res = pdoQuery($sql, array($i));
    if($res === false){
        echo $pdo->getErrorMessage();
        http_response_code(500);
        exit();
    }
    sleep(2);
}

SP call:

$sql = "EXEC sp_Slow";
$res = pdoQuery($sql);
if($res === false){
    echo $pdo->getErrorMessage();
    http_response_code(500);
    exit();
}

How I tested: using buttons that trigger ajax calls to each script, I tested them, by clicking the button and immediately closing the tab. And then monitoring the data in the table. And just as I suspected, new data gets inserted every 2 seconds. (This also happens if I directly open the script in the browser and closing the tab, instead of requesting it through ajax)

I need a way to completely kill both PHP and SQL execution whenever the user disconnects, transactions are not important because it's just a select operation.

Improve this question
7
  • 1
    Perhaps either disable the button after the first click and show one of those spinning twidder images to let them know something is happening or take a page from the playbook of any travel site and redirect them to another page that says "performing search" while they are waiting. Commented May 30, 2017 at 17:19
  • I'm interested in seeing if this is possible. But an alternative is to use a session variable to prevent a user from searching multiple times. Commented May 30, 2017 at 17:20
  • That only covers a particular scenario (multiple clicks of the same query), because the user sometimes legitimately needs to revise their search query, and waiting > 1 minute for the first query to finish is just poor UX Commented May 30, 2017 at 17:21
  • @iceman2992 If that's the case, you could look into only allowing one query to be run per session. If you grab the SPID of the query being executed and save that to a session variable, you can then do a check for a value in that session variable and kill the query related to that SPID. Commented May 30, 2017 at 17:24
  • 1
    @iceman2992 See the answer to this MSDN question. Basically just query select @@spid at the start of the script to get the SPID. Save that to your session variable. Then execute kill [spid] to cancel that query. I don't know which library you're using to make your queries, or if your library supports kill (my TSQL work is done outside of PHP, so I'm not familiar with its libraries), but this is what I would look into. Commented May 30, 2017 at 17:29

2 Answers 2

Reset to default
0

You can change this behaviour using php.ini directive or at runtime with ignore_user_abort() function.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Can a PHP script executed via an AJAX call determine if a user has aborted the execution of the script? I believe that the OP's main concern is halting the execution of his query as well.
Just tested it, doesn't work, I added ignore_user_abort(false); at the top of the script, and the same behavior was observed
@iceman2992 Look here: stackoverflow.com/questions/7582485/…
OP appears to be using sql server, not mysql.
@JonathanKuhn the logic is the same though. You basically get the process ID from the server, and then kill that process. SQL Server works the same way as MySQL in that regard. edit: My bad; I should have thought to look further than the first answer.
|
0

Here's what I did, from the comment by @Robbie Toyota, thanks!

if(!empty($_SESSION['SearchSPID'])){
    $sql = "KILL " . $_SESSION['SearchSPID'];
    $res = pdoQuery($sql);
    if($res === false){
        exit('Query error: failed to kill existing spid:' . $_SESSION['SearchSPID']);
    }       
    $_SESSION['SearchSPID'] = null;
}

$sql = "SELECT @@spid AS SPID";
$res = pdoQuery($sql);
$spid = $res->row_assoc()['SPID'];

$_SESSION['SearchSPID'] = $spid;

// do long query here

$_SESSION['SearchSPID'] = null;

Of course using this method you have to be careful about session file locking, which if happens will make this whole thing pointless, because then the requests will be sequential and not parallel

Improve this answer

2 Comments

Thanks for posting your solution for posterity. Does it work? I'm just curious to know if PDO supports kill for sql server. Also, as @N.B. mentioned, you should review your search query to speed it up. If you're using indexes properly, and you're not returning/processing massive amounts of data, a query shouldn't be taking several minutes.
Yes it does work, and yes, I am trying to optimize the query as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.