will it call subquery for every row of table with distinct in subqyery.
-
hi,
1) select * from abc where abcid =(select top 1 abcid from xyz where xyzid =@xyz)
2) Select * from df where dfid in (select distinct dfid from uz)
3) select * from df1 where exists (select top 1 dfid from uz1 where uz1.dfid=df1.dfid)
Q1) some body told me that first query will execute (select top 1 abcid from xyz where xyzid =@xyz) for each row of abc table, is it correct?
Q2) will second query execute (selec distinct dfid from uz) for each row of df. that is will it apply distinct for every row of df table?
Q3) will the third will optimise if i use top 1 in exists () block?
yours sincerley
-
1. No, it's not a correlated subquery, so it only needs to run once.
2. Same as 1.
3. This is a correlated subquery. I'm not sure what your question is, though. Have you tried it with and without the TOP 1 and compared the execution plans (and the results, of course)?
John
-
rajemessage 14195 (11/17/2016)
hi,1) select * from abc where abcid =(select top 1 abcid from xyz where xyzid =@xyz)
2) Select * from df where dfid in (select distinct dfid from uz)
3) select * from df1 where exists (select top 1 dfid from uz1 where uz1.dfid=df1.dfid)
Q1) some body told me that first query will execute (select top 1 abcid from xyz where xyzid =@xyz) for each row of abc table, is it correct?
Q2) will second query execute (selec distinct dfid from uz) for each row of df. that is will it apply distinct for every row of df table?
Q3) will the third will optimise if i use top 1 in exists () block?
yours sincerley
As for sample 3 (the TOP 1 combined with EXISTS): the SQL optimzer will build the same execution plan with or without the TOP 1. With the EXISTS statement the optizer only has to know if (at least) one row exists for the particular [dfid], so it will implicitly use some kind of optimization to search for at least one row whether you specify the TOP 1 or not.
** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! ** -
John Mitchell-245523 (11/17/2016)
1. No, it's not a correlated subquery, so it only needs to run once.2. Same as 1.
3. This is a correlated subquery. I'm not sure what your question is, though. Have you tried it with and without the TOP 1 and compared the execution plans (and the results, of course)?
John
Actually it's not clear whether the first two are correlated subqueries or not. We don't know whether abcid in the subquery is a field in xyz table or a reference to abc.abcid in the outer query. The same is true of query 2. Also, the subquery in 1 uses a TOP operator without specifying an order.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
1) select * from abc where abc.abcid =(select top 1 xyz.abcid from xyz where xyzid =@xyz order by xyz.abcid )2) Select * from df where df.dfid in (select distinct uz.dfid from uz )
yours sincerley
-
rajemessage 14195 (11/22/2016)
1) select * from abc where abc.abcid =(select top 1 xyz.abcid from xyz where xyzid =@xyz order by xyz.abcid )2) Select * from df where df.dfid in (select distinct uz.dfid from uz )
yours sincerley
Not sure what this is adding? It's a direct copy of your initial code, but in IFCode brackets.
Also, sorry, it's really bugging me as you did it in both posts. It's spelt Sincerely.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
this one has alias, so that u can find which col belongs to which table.
1) select * from abc where abc.abcid =(select top 1 xyz.abcid from xyz where xyzid =@xyz order by xyz.abcid )
2) Select * from df where df.dfid in (select distinct uz.dfid from uz )
Q1) some body told me that first query will execute (select top 1 abcid from xyz where xyzid =@xyz) for each row of abc table, is it correct?
Q2) will second query execute (selec distinct uz.dfid from uz) for each row of df. that is will it apply distinct for every row of df table?
Q3) in second query if uz table has two million records , and query is executing , at the same time if some one inserts a new record at the end of the table uz , will that records be considered by the the query second query.
@4) should we put distinct in second query (select distinct uz.dfid from uz) which i have used in "in" clause.
yousr sincerly
-
rajemessage 14195 (11/22/2016)
this one has alias, so that u can find which col belongs to which table.1) select * from abc where abc.abcid =(select top 1 xyz.abcid from xyz where xyzid =@xyz order by xyz.abcid )
2) Select * from df where df.dfid in (select distinct uz.dfid from uz )
Q1) some body told me that first query will execute (select top 1 abcid from xyz where xyzid =@xyz) for each row of abc table, is it correct?
No. And, although this has been answered, let me re-iterate: What you are describing is a correlated subquery which is described in books online (emph. mine):
Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query.
In Query #1 the inner query (in parenthesis) does not reference the outer query and therefore will be evaluated first and only once.
Q2) will second query execute (selec distinct uz.dfid from uz) for each row of df. that is will it apply distinct for every row of df table?
Q4) should we put distinct in second query (select distinct uz.dfid from uz) which i have used in "in" clause.
The DISTINCT clause does not effect the final output so the optimizer will ignore it.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
well my third question is not answered and one more question is
would u like to use 1) or 2)?
1) Select
*
FROM abc where abc.abcid =(select top 1 xyz.abcid from xyz where xyzid =@xyz order by xyz.abcid )
2)
Declare @abc int
select @abc =(select top 1 xyz.abcid from xyz where xyzid =@xyz)
select * from abc where abc.abcid=@abc
please consider following example
I have made a test on the AdventureWorks2012 with your above Q1 two formats and the following is the execution plan. According to this image you could know that it would be better to use the second query. You could also make a test on your machine to find out which query would be better.
yours sincerley
-
rajemessage 14195 (11/22/2016)Q3) in second query if uz table has two million records , and query is executing , at the same time if some one inserts a new record at the end of the table uz , will that records be considered by the the query second query.
By default, no the newly inserted record will not be included in the results of an already running query. Research transactions, locks and isolation.
I can't select #1 or #2 because I don't know what you're trying to do. Don't get too hung up on the execution plans. Things may look simpler, but separating tasks takes some power away from the optimizer and you may incur more overhead.
Your first statement feels unreliable. Sometimes it can help to write your query as a sentence.
Get me all the Sales Order Details for the first ID (alphabetically) for Products with a name beginning with 'LL Road Pedal'.
This implies:
A. Multiple products with LL Road Pedal exist, or possibly that individual products may have multiple product IDs.
B. The IDs have some priority or significance beyond just being a unique identifier
C. There is no expectation that this same query will return Sales Order Details for the same Product in the future.
This feels more consistent to me, but may not necessarily be what you need.
Select * FROM abc where abc.abcid in (select xyz.abcid from xyz where xyzid =@xyz)Wes
(A solid design is always preferable to a creative workaround) -
To answer your third question that would depend on the isolation level of the transaction. You can override the databases default isolation level with something like
set transaction isolation level read uncommittedbefore your select statement and that can cause what you mention to happen. The default isolation level is read committed.
Have a look here where you will see that Serializable isolation level that will guarantee consistency in all situations, but increases the likely hood of locks to resources.
Do a search on SQL Server isolation levels to learn more about this.
----------------------------------------------------
-
this is what i ment,
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/16/1345.aspx
-
that one might be taking range lock pls see this one,
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/16/1345.aspx
-
this one might be taking range lock, pls see follwing.
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/16/1345.aspx
( i have tried to add reply but it site was not adding it so you may see this post more than once)
your sincerely
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply