Is there a way to optimize this SQL query?-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

If possible, I would suggest you to use a stored procedure. Using a stored procedure is more performant as stored procedures are stored as a machine code in the server's cache and not recompiled unless the code is modified.

Here is the documentation

CREATE PROCEDURE ABC  
AS  
    SELECT q_name, COUNT(*)
    FROM myTable
    WHERE [status] = 2
      AND create_stamp < DATEDIFF(mi, create_stamp, getdate()) > 1
    GROUP BY q_name