performance of select

Here is DDL in my way. Other information is masked.
Table a
sri int, PK, Clustered
num varchar(7) Nonclustered
.
.
.
p uniqueidentifier Nonclustered
Table s
num varchar(7), PK,Clustered
ssi tinyint --can have either 1 or 2
IF EXISTS ( SELECT sri FROM a WITH (NOLOCK)
INNER JOIN s WITH (NOLOCK)
ON a.num = s.num
WHERE a.p IS NULL AND s.ssi = 1 )
PRINT 'YES'
--uses index s on p, runs in a fraction of second
IF EXISTS ( SELECT sri FROM a WITH (NOLOCK)
INNER JOIN s WITH (NOLOCK)
ON a.num = s.num
WHERE a.p IS NULL AND s.ssi = 2 )
PRINT 'YES'
--uses index scan on num, takes around 8-25 seconds. Have any thoughts why
it takes so long, how to make this use an index s on p.SELECT ssi, COUNT(*) FROM s
GROUP BY ssi
How many rows have an ssi value of 2, compared to an ssi value of 1? If
there are a lot more rows that have ssi = 2 than 1, then the optimizer will
choose an index scan, because it would be the better method to find matching
rows through the select statement.
"S" wrote:

> Here is DDL in my way. Other information is masked.
> Table a
> sri int, PK, Clustered
> num varchar(7) Nonclustered
> .
> .
> .
> p uniqueidentifier Nonclustered
> Table s
> num varchar(7), PK,Clustered
> ssi tinyint --can have either 1 or 2
>
> IF EXISTS ( SELECT sri FROM a WITH (NOLOCK)
> INNER JOIN s WITH (NOLOCK)
> ON a.num = s.num
> WHERE a.p IS NULL AND s.ssi = 1 )
> PRINT 'YES'
> --uses index s on p, runs in a fraction of second
>
> IF EXISTS ( SELECT sri FROM a WITH (NOLOCK)
> INNER JOIN s WITH (NOLOCK)
> ON a.num = s.num
> WHERE a.p IS NULL AND s.ssi = 2 )
> PRINT 'YES'
> --uses index scan on num, takes around 8-25 seconds. Have any thoughts why
> it takes so long, how to make this use an index s on p.|||Ratio is few hundreds to millions
2 - few hundreds
1 - millions. Is that what causing the daly to find the records.
"Mark Williams" wrote:
> SELECT ssi, COUNT(*) FROM s
> GROUP BY ssi
> How many rows have an ssi value of 2, compared to an ssi value of 1? If
> there are a lot more rows that have ssi = 2 than 1, then the optimizer wil
l
> choose an index scan, because it would be the better method to find matchi
ng
> rows through the select statement.
>
> --
> "S" wrote:
>|||Could be...
You can update the statistics of table "a", preferably WITH FULL_SCAN,
and see if that makes any difference.
Gert-Jan
S wrote:
> Ratio is few hundreds to millions
> 2 - few hundreds
> 1 - millions. Is that what causing the daly to find the records.
> "Mark Williams" wrote:
>|||Try modifying your queries:
IF EXISTS ( SELECT 1 FROM a WITH (NOLOCK)
INNER JOIN s WITH (NOLOCK)
ON a.num = s.num
WHERE a.p IS NULL AND s.ssi = 1 )
PRINT 'YES'
uses index s on p, runs in a fraction of second
IF EXISTS ( SELECT 1 FROM a WITH (NOLOCK)
INNER JOIN s WITH (NOLOCK)
ON a.num = s.num
WHERE a.p IS NULL AND s.ssi = 2 )
PRINT 'YES'
Since your are using the query in the context of EXISTS, you can use SELECT
1 instead of SELECT sri (credit to Jim Underwood, as seen in a posting
earlier today). Specifying sri in the select list may be affecting which
indexes are chosen.
Other than that, run the statements below and post the text execution plans
here.
SET SHOWNPLAN_TEXT ON
GO
SELECT sri FROM a WITH (NOLOCK)
INNER JOIN s WITH (NOLOCK)
ON a.num = s.num
WHERE a.p IS NULL AND s.ssi = 1
SELECT sri FROM a WITH (NOLOCK)
INNER JOIN s WITH (NOLOCK)
ON a.num = s.num
WHERE a.p IS NULL AND s.ssi = 2 )
-
"S" wrote:

> Ratio is few hundreds to millions
> 2 - few hundreds
> 1 - millions. Is that what causing the daly to find the records.
>|||Table s has 8 million records but only a few hundred have ssi=2, remaining
records
have ssi= 1
|--Compute Scalar(DEFINE:([Expr1003]=If [Expr1004] then 1 else 0))
|--Nested Loops(Left Semi Join, DEFINE:([Expr1004] = [PROBE VALUE]))
|--Constant Scan
|--Filter(WHERE:([a].[p]=NULL))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([DB_NAME].[dbo].[a] ))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([s].[num]))
|--Clustered Index
Scan(OBJECT:([DB_NAME].[dbo].[s].[PK_s]), WHERE:([s].[ssi]=2))
|--Index
S(OBJECT:([DB_NAME].[dbo].[a].[IX_a_num] ), SEEK:([a].[num]=[s].[num])
ORDERED FORWARD)
for ssi= 2
|--Compute Scalar(DEFINE:([Expr1003]=If [Expr1004] then 1 else 0))
|--Nested Loops(Left Semi Join, DEFINE:([Expr1004] = [PROBE VALUE]))
|--Constant Scan
|--Nested Loops(Inner Join, OUTER REFERENCES:([a].[num]) WITH
PREFETCH)
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([DB_NAME].[dbo].[a] ))
| |--Index S(OBJECT:([DB_NAME].[dbo].[a].[IX_a_p),
SEEK:([a].[p]=NULL) ORDERED FORWARD)
|--Clustered Index
S(OBJECT:([DB_NAME].[dbo].[s].[PK_s]), SEEK:([s].[num]=[a].[num]),
WHERE:([s].[ssi]=1) ORDERED FORWARD)
"Mark Williams" wrote:

> Try modifying your queries:
> IF EXISTS ( SELECT 1 FROM a WITH (NOLOCK)
> INNER JOIN s WITH (NOLOCK)
> ON a.num = s.num
> WHERE a.p IS NULL AND s.ssi = 1 )
> PRINT 'YES'
> uses index s on p, runs in a fraction of second
>
> IF EXISTS ( SELECT 1 FROM a WITH (NOLOCK)
> INNER JOIN s WITH (NOLOCK)
> ON a.num = s.num
> WHERE a.p IS NULL AND s.ssi = 2 )
> PRINT 'YES'
> Since your are using the query in the context of EXISTS, you can use SELEC
T
> 1 instead of SELECT sri (credit to Jim Underwood, as seen in a posting
> earlier today). Specifying sri in the select list may be affecting which
> indexes are chosen.
> Other than that, run the statements below and post the text execution plan
s
> here.
> SET SHOWNPLAN_TEXT ON
> GO
> SELECT sri FROM a WITH (NOLOCK)
> INNER JOIN s WITH (NOLOCK)
> ON a.num = s.num
> WHERE a.p IS NULL AND s.ssi = 1
> SELECT sri FROM a WITH (NOLOCK)
> INNER JOIN s WITH (NOLOCK)
> ON a.num = s.num
> WHERE a.p IS NULL AND s.ssi = 2 )
>
> -
>
> "S" wrote:
>
>|||Which order are the plans in? Is the ssi = 1 query first?
"S" wrote:
> Table s has 8 million records but only a few hundred have ssi=2, remaining
> records
> have ssi= 1
> |--Compute Scalar(DEFINE:([Expr1003]=If [Expr1004] then 1 else 0))
> |--Nested Loops(Left Semi Join, DEFINE:([Expr1004] = [PROBE VALUE])
)
> |--Constant Scan
> |--Filter(WHERE:([a].[p]=NULL))
> |--Bookmark Lookup(BOOKMARK:([Bmk1000]),
> OBJECT:([DB_NAME].[dbo].[a] ))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([s].[num]))
> |--Clustered Index
> Scan(OBJECT:([DB_NAME].[dbo].[s].[PK_s]), WHERE:([s].[ssi]=2))
> |--Index
> S(OBJECT:([DB_NAME].[dbo].[a].[IX_a_num] ), SEEK:([a].[num]=[s].[num])
> ORDERED FORWARD)
>
> for ssi= 2
> |--Compute Scalar(DEFINE:([Expr1003]=If [Expr1004] then 1 else 0))
> |--Nested Loops(Left Semi Join, DEFINE:([Expr1004] = [PROBE VALUE])
)
> |--Constant Scan
> |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[num]) WITH
> PREFETCH)
> |--Bookmark Lookup(BOOKMARK:([Bmk1000]),
> OBJECT:([DB_NAME].[dbo].[a] ))
> | |--Index S(OBJECT:([DB_NAME].[dbo].[a].[IX_a_p),
> SEEK:([a].[p]=NULL) ORDERED FORWARD)
> |--Clustered Index
> S(OBJECT:([DB_NAME].[dbo].[s].[PK_s]), SEEK:([s].[num]=[a].[num]),
> WHERE:([s].[ssi]=1) ORDERED FORWARD)
>
> "Mark Williams" wrote:
>|||Yes.
"Mark Williams" wrote:
> Which order are the plans in? Is the ssi = 1 query first?
> --
> "S" wrote:
>|||I am sorry. I think I gave the incorrect order of execution plans.
ssi =1 uses index s on both s and a
ssi=2 uses index s on a and index scan on s
"S" wrote:
> Yes.
> "Mark Williams" wrote:
>|||I tried replicating your data set on a much smaller scale. I created and
populated table s with about 100,000 rows with the same distribution of ssi
=
1 and ssi = 2 rows. Created and populated table a with an IDENTITY column fo
r
the PK, foreign key relationship to s on the num column, and nonclustered
indexex on p and num.
Ran both the queries and they had identical execution plans. Don't know why
you are getting different ones.
"S" wrote:
> I am sorry. I think I gave the incorrect order of execution plans.
> ssi =1 uses index s on both s and a
> ssi=2 uses index s on a and index scan on s
> "S" wrote:
>