sqlsql-servert-sqlsql-optimization

Is there a way to optimize this SQL query?

I have this query I have to automate with AWS Lambda but first I want to optimize it.

It seems legit to me but I have this feeling I can do something to improve it.

SELECT q_name, count(*)
FROM myTable
WHERE status = 2
AND DATEDIFF(mi, create_stamp, getdate()) > 1
GROUP BY q_name
Solution

  • The only improvement I can see is not to apply a function to your column, because that makes the query unsargable (unable to use indexes). Instead leave the column as it is and calculate the correct cutoff.

    SELECT q_name, count(*)
FROM myTable
WHERE [status] = 2
--AND DATEDIFF(mi, create_stamp, getdate()) > 1
-- Adjust the logic to meet your requirements, because this is slightly different to what you had
AND create_stamp < DATEADD(minute, -1, getdate())
GROUP BY q_name;

    Note, while dateadd does accept abbreviations for the unit to add, its much clearer to type it in full.