SELECT clause

Question

SELECT clause

gstarsaini

Ten Centuries

Points: 1010
More actions
August 7, 2014 at 2:46 am

#291694

Do multiple SELECT queries on the same table but different condition in WHERE clause block or affect each other in any way?

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

They do not block each other, but they compete for the same resources of course (mainly IO I guess).

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

gstarsaini Ten Centuries Points: 1010 More actions · Answer 2

Thanks for the reply.

All I want to know is whether it will affect the query execution time?

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

gstarsaini (8/7/2014)
Thanks for the reply.
All I want to know is whether it will affect the query execution time?

It might. Depends on the queries and how often they are executed.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ScottPletcher SSC Guru Points: 101123 More actions · Answer 4

gstarsaini (8/7/2014)
Do multiple SELECT queries on the same table but different condition in WHERE clause block or affect each other in any way?

They definitely could. The WHERE conditions determine which row(s) match. But the lock(s) are on the underlying row(s)|page(s) themselves. Therefore, blocking could occur no matter the WHERE conditions are. Indeed, a SELECT without a WHERE clause could block other queries at certain times as well.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Readers don't block readers. Readers can block writers and the other way around. Even on that case, it would depend on the locks acquired based on the queries and the indexes available in the table.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

gstarsaini Ten Centuries Points: 1010 More actions · Answer 6

Thanks for the reply.

This means there is no effect on the queries?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

gstarsaini (8/7/2014)
Thanks for the reply.
This means there is no effect on the queries?

Each reply posted to your question implies "maybe", "probably" or "definitely". The average of these isn't "definitely not"!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

gstarsaini Ten Centuries Points: 1010 More actions · Answer 8

Thanks again.

Avoid sarcasm. Don't insist on the last word.

ScottPletcher SSC Guru Points: 101123 More actions · Answer 9

ChrisM@Work (8/7/2014)
gstarsaini (8/7/2014)
Thanks for the reply.
This means there is no effect on the queries?
Each reply posted to your question implies "maybe", "probably" or "definitely". The average of these isn't "definitely not"!

No, definitely could. Hint(s) in a SELECT can cause them to take higher-level locks. The SELECT could be part of an UPDATE statement.

Yes, pure SELECT-only should never cause blocking with another pure SELECT-only.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

ScottPletcher (8/7/2014)
ChrisM@Work (8/7/2014)
gstarsaini (8/7/2014)
Thanks for the reply.
This means there is no effect on the queries?
Each reply posted to your question implies "maybe", "probably" or "definitely". The average of these isn't "definitely not"!
No, definitely could. Hint(s) in a SELECT can cause them to take higher-level locks. The SELECT could be part of an UPDATE statement.
Yes, pure SELECT-only should never cause blocking with another pure SELECT-only.

But will most likely affect performance (even if only slightly), which is part of OP's question.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ScottPletcher SSC Guru Points: 101123 More actions · Answer 11

ChrisM@Work (8/7/2014)
ScottPletcher (8/7/2014)
ChrisM@Work (8/7/2014)
gstarsaini (8/7/2014)
Thanks for the reply.
This means there is no effect on the queries?
Each reply posted to your question implies "maybe", "probably" or "definitely". The average of these isn't "definitely not"!
No, definitely could. Hint(s) in a SELECT can cause them to take higher-level locks. The SELECT could be part of an UPDATE statement.
Yes, pure SELECT-only should never cause blocking with another pure SELECT-only.
But will most likely affect performance (even if only slightly), which is part of OP's question.

Quite true as well.

Which is why I don't quite understand the "definitely not!" 🙂

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

ScottPletcher (8/7/2014)
Which is why I don't quite understand the "definitely not!" 🙂

You may have missed the word before that phrase.

"The average of those isn't 'definitely not'"

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101123 More actions · Answer 13

GilaMonster (8/7/2014)
ScottPletcher (8/7/2014)
Which is why I don't quite understand the "definitely not!" 🙂
You may have missed the word before that phrase.
"The average of those isn't 'definitely not'"

Yep, sure did :blush:

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

gstarsaini Ten Centuries Points: 1010 More actions · Answer 14

gstarsaini

Ten Centuries

Points: 1010

August 8, 2014 at 12:46 am

#1736300

Thanks you.This will help.