February 5, 2010 at 9:10 am
Quite interesting Paul! I have seen numerous real-world systems where that was a killer. Things that make you go "HMMMMM"!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 5, 2010 at 9:14 am
TheSQLGuru (2/5/2010)
Quite interesting Paul! I have seen numerous real-world systems where that was a killer. Things that make you go "HMMMMM"!!
Well yes, absolutely. I'm not arguing the general point about statistics - you know I agree with you.
I just had to respond to the specific example you raised.
(And yes it is an interesting result - if I blogged, I might have blogged about it, but I don't LOL)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 5, 2010 at 9:16 am
AHA! Do this:
dbcc freeproccache
go
DECLARE @OneRow
TABLE (
-- row_id INTEGER IDENTITY PRIMARY KEY,
value INTEGER NOT NULL
);
--INSERT @OneRow VALUES (123456);
INSERT @OneRow VALUES (95); --can even put in 77 here and get zero rows, still get hash
SELECT *
FROM @OneRow R
JOIN dbo.Data D
ON D.value = R.value;
Now you SHOULD get the nested loop but do not. Clearly the stats on the perm table lead the optimizer to the probability that your request is gonna hit the biggie so it does a hash even when it should NOT. So it simply kills you in a different manner. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 5, 2010 at 9:17 am
DOH! More goof - you need an index on the value joined or it can never do anything but a table scan/hash. Back in a moment with better analysis...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 5, 2010 at 9:19 am
Clearly a bug in the optimizer - take it up with the SQL Team :laugh: :laugh: :laugh: :laugh: :laugh:
No, seriously, I got my caveat post in first, so AHA! back 😀
(note I haven't actually run your code yet, so I might be back in a minute*)
* unlikely as it is now 5:19AM
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 5, 2010 at 9:20 am
TheSQLGuru (2/5/2010)
DOH! More goof - you need an index on the value joined or it can never do anything but a table scan/hash. Back in a moment with better analysis...
Bugger. I knew I should have read it first.
P.S. You needn't think you can get away with adding indexes!!! Not the same problem as originally stated!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 5, 2010 at 9:33 am
Try this:
use tempdb
go
CREATE TABLE dbo.Data
(
row_id INTEGER IDENTITY PRIMARY KEY,
value INTEGER NOT NULL,
value2 varchar(10)
);
INSERT dbo.Data WITH (TABLOCK)
(value, value2)
SELECT TOP (750000)
value = 123456,
'asdf'
FROM master.sys.columns C1,
master.sys.columns C2,
master.sys.columns C3;
INSERT dbo.Data
(value, value2)
VALUES (95, 'zzz');
create index idx on data (value)
set statistics io on --show actual query plan too
dbcc freeproccache
go
DECLARE @OneRow
TABLE (
-- row_id INTEGER IDENTITY PRIMARY KEY,
value INTEGER NOT NULL
);
INSERT @OneRow VALUES (123456); --this one does get hash
--INSERT @OneRow VALUES (95); --so does this one (bad)
--insert @onerow values (77) --and this one (bad)
--always hashes
--cpu 187, duration 701, reads 2560
SELECT d.value, d.value2
FROM @OneRow R
JOIN dbo.Data D
ON D.value = R.value;
--run this one for the 95 or 77 value only
--cpu 0, duration 226, reads 13
SELECT d.value, d.value2 --on my server this even gets parallelized due to massive estimated row count (75000)!
FROM @OneRow R --but still MUCH fewer reads
INNER LOOP JOIN dbo.Data D
ON D.value = R.value
--DROP TABLE dbo.Data;
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply