July 12, 2013 at 5:34 am
Can anybody explain .
why i am getting better Plan when add primary key to table.
for 2nd left key , 3rd left key and for non key column
table populated :
Set statistics io on
Create Table TestTable1 (ID INT , OID INT , PID INT , SPName Varchar(100) , name varchar(100) , CreatedON datetime , type char(1) , primary key (ID,SPName,CreatedON) )
GO
INsert into TestTable1
select top 100000 Row_number() Over(Order by S1.[Object_ID] ) ID ,
S1.Object_ID
,
S1.Parent_Object_ID
, S1.name + Convert( varchar,Row_number() Over(Order by S1.[Object_ID] ) ) SPName ,
S1.name,
S1.create_date - ( (Row_number() Over(Order by S1.[Object_ID] )) *.1) CreatedOn ,
S1.Type
from Sys.all_OBjects S1 , Sys.all_OBjects S2
GO
Select * into TestTable2 FRom TestTable1
GO
alter table TestTable2 alter column ID int not null
alter table TestTable2 alter column SPName varchar(100) not null
alter table TestTable2 alter column CreatedON datetime not null
GO
alter table TestTable2 add primary key (ID,SPName,CreatedON)
GO
-- 3nd left column
Select * FRom TestTable1 where CreatedON>='1-jan-2009' and CreatedON< '1-mar-2009' -- Query cost relative to batcj 53%
Select * FRom TestTable2 where CreatedON>='1-jan-2009' and CreatedON< '1-mar-2009'-- Query cost relative to batcj 47%
GO
-- 2nd left column
Select * FRom TestTable1 where Spname='sp_MSalreadyhavegeneration3982' -- Query cost relative to batcj 53%
Select * FRom TestTable2 where Spname='sp_MSalreadyhavegeneration3982' -- Query cost relative to batcj 47%
GO
-- NOn Key column
Select * FRom TestTable1 where name='sp_MSalreadyhavegeneration' -- Query cost relative to batcj 53%
Select * FRom TestTable2 where name='sp_MSalreadyhavegeneration' -- Query cost relative to batcj 47%
GO
-- 1st left column
Select * FRom TestTable1 where ID=3982 --Expected 50%
Select * FRom TestTable2 where ID=3982 --Expected 50%
GO
Neeraj Prasad Sharma
Sql Server Tutorials
July 12, 2013 at 5:43 am
Try rebuilding the clustered index on Table1 and updating statistics. You'll probably get 50/50 then. The index on Table1 is fragmented since you inserted the data after creating it.
John
July 12, 2013 at 5:49 am
July 12, 2013 at 6:14 am
It won't be fragmentation as the query optimiser does not take fragmentation into account when costing the queries. It might be stats, the insert will trigger an auto-update which is sampled on larger tables whereas the index creation does a stats update with full scan (though this isn't a large table).
Post the plans?
p.s. Plan costs != query performance. Don't make that mistake. If you want to compare performance, compare performance not estimated numbers.
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
July 15, 2013 at 12:31 am
GilaMonster (7/12/2013)
It won't be fragmentation as the query optimiser does not take fragmentation into account when costing the queries.
NOTED ..
It might be stats, the insert will trigger an auto-update which is sampled on larger tables whereas the index creation does a stats update with full scan (though this isn't a large table).
i updated stats on the table but got the same plan, script below
and query plan attached.
UPDATE STATISTICS DBO.TestTable1
WITH FULLSCAN
p.s. Plan costs != query performance. Don't make that mistake. If you want to compare performance, compare performance not estimated numbers.
please explain, This could be eye opener.
Neeraj Prasad Sharma
Sql Server Tutorials
July 15, 2013 at 1:17 am
Neeraj Prasad Sharma (7/15/2013)
p.s. Plan costs != query performance. Don't make that mistake. If you want to compare performance, compare performance not estimated numbers.
please explain, This could be eye opener.
Since I and several others have been saying it for a long time, there should be lots and lots of results from a google search.
p.s. The estimated row count on the clustered index seek is different for the two. Hence the difference in estimated cost and hence the difference in estimated cost %.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply