CODEWITHSUNDEEP
sqlsql-servert-sqlsql-optimization
SystemCyprus
SystemCyprus

Reputation: 147

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

Upvotes: 1

Views: 413

Answers (1)

Dale K
Dale K

Reputation: 27201

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.

Upvotes: 9

Related Questions