nested loops join

Hi,
I have a select statement that gets data from only one table.
When I write OPTION(LOOP JOIN) after this query and run it, the
execution time is 2-3 times faster than without OPTION(LOOP JOIN).
If I use OPTION(FAST 1) the execution time is as fast as with OPTION(LOOP
JOIN)
Does anyone know why its faster with nested loops join even though I don't
join any tables?
Thanks!
//MalinDid you look at the actual execution plan to see what it is doing in both
cases?
Andrew J. Kelly SQL MVP
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:uwxC2gaRFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a select statement that gets data from only one table.
> When I write OPTION(LOOP JOIN) after this query and run it, the
> execution time is 2-3 times faster than without OPTION(LOOP JOIN).
> If I use OPTION(FAST 1) the execution time is as fast as with OPTION(LOOP
> JOIN)
> Does anyone know why its faster with nested loops join even though I don't
> join any tables?
> Thanks!
> //Malin
>|||The graphical execution plans are identical. (select <-- Clustered index
s)
If I have set showplan_text on there is a difference.
select col1, col2, col3
from table1
where col1=1234
option(loop join)
|--Clustered Index S(OBJECT:([DB].[dbo].[table1].[PK_tIndex]),
SEEK:([table1].[col1]=Convert([@.1])) ORDERED FORWARD)
select col1, col2, col3
from table1
where col1=1234
|--Clustered Index S(OBJECT:([DB].[dbo].[table1].[PK_tIndex]),
SEEK:([table1].[col1]=1234) ORDERED FORWARD)
Does "Convert([@.1])" have something to do with the execution time of the
query?
Thanks for helping.
// Malin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eSqTusaRFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Did you look at the actual execution plan to see what it is doing in both
> cases?
> --
> Andrew J. Kelly SQL MVP
>
> "Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
> news:uwxC2gaRFHA.1500@.TK2MSFTNGP09.phx.gbl...
>|||for instance, ...perhaps you have a couple of search arguments ANDed and SQL
Server can join these
by two indexes (aka index intersection) and this is the join which is influe
nced by your hint.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eSqTusaRFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Did you look at the actual execution plan to see what it is doing in both
cases?
> --
> Andrew J. Kelly SQL MVP
>
> "Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
> news:uwxC2gaRFHA.1500@.TK2MSFTNGP09.phx.gbl...
>|||Hej :-),
I only have one argument in the where statement.
My query looks like "select col1, col2, col3 from table1 where col1=1234"
(as you probably already have seen in my previous message)
That's why I wonder where the "join" is?
// Malin
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23LGPO7aRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> for instance, ...perhaps you have a couple of search arguments ANDed and
> SQL Server can join these by two indexes (aka index intersection) and this
> is the join which is influenced by your hint.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eSqTusaRFHA.2604@.TK2MSFTNGP10.phx.gbl...
>|||Hej. :-)
Strange... Did you look at the execution plan?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:%23pJpeDbRFHA.3444@.tk2msftngp13.phx.gbl...
> Hej :-),
> I only have one argument in the where statement.
> My query looks like "select col1, col2, col3 from table1 where col1=1234"
(as you probably
> already have seen in my previous message)
> That's why I wonder where the "join" is?
> // Malin
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23LGPO7aRFHA.3928@.TK2MSFTNGP09.phx.gbl...
>|||The graphical execution plans are identical. (select <-- Clustered index
s)
If I have set showplan_text on there is a difference.
select col1, col2, col3
from table1
where col1=1234
option(loop join)
|--Clustered Index S(OBJECT:([DB].[dbo].[table1].[PK_tIndex]),
SEEK:([table1].[col1]=Convert([@.1])) ORDERED FORWARD)
select col1, col2, col3
from table1
where col1=1234
|--Clustered Index S(OBJECT:([DB].[dbo].[table1].[PK_tIndex]),
SEEK:([table1].[col1]=1234) ORDERED FORWARD)
Does "Convert([@.1])" have something to do with the execution time of the
query?
Anything more I can do to find out what this can depend on?
// Malin
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eVHTpHbRFHA.3076@.tk2msftngp13.phx.gbl...
> Hej. :-)
> Strange... Did you look at the execution plan?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
> news:%23pJpeDbRFHA.3444@.tk2msftngp13.phx.gbl...
>|||In this example you have the value for Col1 as an integer. In the real
table is the datatype for Col1 an Integer?
Andrew J. Kelly SQL MVP
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:e0XLO5aRFHA.1172@.TK2MSFTNGP12.phx.gbl...
> The graphical execution plans are identical. (select <-- Clustered index
> s)
> If I have set showplan_text on there is a difference.
> select col1, col2, col3
> from table1
> where col1=1234
> option(loop join)
> |--Clustered Index S(OBJECT:([DB].[dbo].[table1].[PK_tIndex]),
> SEEK:([table1].[col1]=Convert([@.1])) ORDERED FORWARD)
>
> select col1, col2, col3
> from table1
> where col1=1234
> |--Clustered Index S(OBJECT:([DB].[dbo].[table1].[PK_tIndex]),
> SEEK:([table1].[col1]=1234) ORDERED FORWARD)
> Does "Convert([@.1])" have something to do with the execution time of the
> query?
> Thanks for helping.
> // Malin
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eSqTusaRFHA.2604@.TK2MSFTNGP10.phx.gbl...
>|||yes "col1" is an integer in the real table, the query looks exactly as I
have written except the names :-)
//Malin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LZ9yTbRFHA.904@.tk2msftngp13.phx.gbl...
> In this example you have the value for Col1 as an integer. In the real
> table is the datatype for Col1 an Integer?
> --
> Andrew J. Kelly SQL MVP
>
> "Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
> news:e0XLO5aRFHA.1172@.TK2MSFTNGP12.phx.gbl...
>|||I will post to the internal group and see if anyone has seen this before.
Andrew J. Kelly SQL MVP
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:uvQUHabRFHA.3076@.TK2MSFTNGP14.phx.gbl...
> yes "col1" is an integer in the real table, the query looks exactly as I
> have written except the names :-)
> //Malin
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23LZ9yTbRFHA.904@.tk2msftngp13.phx.gbl...
>