How to optimize following type of query?

Question

1.00/5 (1 vote)

See more:

There is a pre-written query in one of stored procedure in my project. It is taking too much time to get executed which leads to slowness in the application.

SQL

UPDATE table_name
SET
	column_name =(SELECT DISTINCT
			COUNT(column1)
		FROM 
			table1 
				LEFT JOIN table2 on some_condition
				INNER JOIN table3 on some_condition
				INNER JOIN table4 on some condition
		WHERE 

			( column1 in ('A', 'B','C') or column1 is NULL )

			AND table1.columnname  < getDate()
			AND  anothercolumnname = 0

			AND	anotherCName1 <> 'blah blah'
			AND	anotherCName2 not in ('blah blah') 

			AND table2.anotherCName3 = 'blah')

Please help me in optimizing this query.

What I have tried:

Any help would be appreciated.

Posted 8-Jun-20 22:07pm

Member 14857708

Updated 9-Jun-20 8:51am

Patrice T

v3

Add a Solution

Comments

CHill60 9-Jun-20 4:15am

Start here..
Top 10 steps to optimize data access in SQL Server: Part I (use indexing)[^]
SQL Server Performance Tips and Guidelines[^]
SQL Query Optimization FAQ Part 1 (With video explanation)[^]
Improving the performance of queries using SQL Server: Part 1[^]

Richard MacCutchan 9-Jun-20 4:29am

Looks like a Solution to me.

Member 14857708 9-Jun-20 4:30am

Can you please optimize the above query so that I understand it better. I am a newbie and don't know much of SQL.

Richard MacCutchan 9-Jun-20 4:35am

Then go and spend some time learning SQL.

Member 14857708 9-Jun-20 4:42am

Oh! I'm so sorry.. I didn't check the full post.. I just saw the links.

2 solutions

Solution 2

SQL

LEFT JOIN table2 on some_condition

The conditions are what make a query fast or slow. So exact condition matters, tables structures and indexes matters too.

SQL

AND table1.columnname  < getDate()

getDate() is a volatile function, it means that it prevent any optimization. if query is run around midnight, date can change during query.
By using a volatile function, runtime of query depend on number of records in table.
By using an intermediate variable, runtime depend on number of records that match the condition (if indexed).
Read this: GETDATE (Transact-SQL) - SQL Server | Microsoft Docs[^]

Posted 8-Jun-20 22:38pm

Patrice T

Updated 9-Jun-20 22:08pm

v2

Comments

Jörgen Andersson 10-Jun-20 3:57am

GetDate() is not a volatile function, it is a non-deterministic runtime constant.

Patrice T 10-Jun-20 4:07am

English is not my primary language, I may misuse the word "volatile", but the problem I describe remain the same.
Read this: GETDATE (Transact-SQL) - SQL Server | Microsoft Docs[^]

Jörgen Andersson 10-Jun-20 4:41am

No, the value of GetDate() Cannot change during the query. It's set as a constant at the start of the query.
This can be easily tested using : SELECT somecolumn,GetDate() As MyDateTime INTO MyTestTable FROM MyVeryLargeTable, the value of MyDateTime will be a constant even if the query runs for minutes.

A transaction with several statements is a completely different question though. They will have different values of GetDate() taken from the start of every separate statement.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2020-06-08T22:35:00

Start here..
Top 10 steps to optimize data access in SQL Server: Part I (use indexing)[^]
SQL Server Performance Tips and Guidelines[^]
SQL Query Optimization FAQ Part 1 (With video explanation)[^]
Improving the performance of queries using SQL Server: Part 1[^]

A couple of other points - without table schema and sample data it's a little difficult to give you much detail - we don't know what Indexing solutions you've applied for example.

"on some_condition" is not helpful - it may be the way you have defined your joins that is the problem

table1.columnname < getDate()Try using

SQL

declare @ddate Date = getDate()
. . .
table1.columnname < @ddate

Results may not be noticeable depending on data volumes - again an unknown to us

Try getting rid of DISTINCT - the count of records is already distinct

EDIT - Another useful article on how to find out where the time is going .. How to Analyze SQL Server Performance[^]