Why did index improve performance so much

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Why did index improve performance so much

Author

Topic

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-05 : 15:21:32

I have a table


 declare @um table (Line_pk int,coverage varchar(50),limit varchar(50))

that I was trying to insert 2 records in


insert into @um
  select 
       li.pk
      ,[coverage] = replace(replace(lim.[type],'LineDefault',''),'Split','')
      ,[Limit] = coalesce(lim.ivalue,lim.svalue)
 
from
   policy pol
inner join
  line li
on
  pol.pk = li.policy_pk
inner join linestate ls
  on
   li.pk = ls.line_pk
and pol.rootdctkey = @rootdctkey
 
inner join exposure expos
  on 
       ls.pk     = expos.linestate_pk
and expos.[type] = 'UMType'
inner join mapUMLimits mapUM
  on
    expos.svalue           = mapUM.UMType
and pol.PrimaryRatingState = mapUM.PrimaryRatingState
inner join limit lim
  on
    ls.pk      = lim.linestate_pk
and lim.[Type] = mapUM.LineDefault

the query ran in less than one second without the insert into statement. I put an index on limit(linestate_pk,[type]) and now the insert takes less than a second. Why would an index speed up the insert so much, when the select statement by itself was running fast?

Jim

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-05-05 : 15:24:59

This might have to do with cached data rather than an index. To compare apples to apples, you should run the following two commands in between each test:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-05 : 15:29:46

Thanks Tara,

I'll remember to do that before testing again. By the way, the original insert into (without the index) took 17 minutes! I figured if it took less than s second to return the records, it would tkae less than a second to insert them. Does SQL reserve space for the table variable based on the estimated rows returned by the execution plan?

Jim

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-05-05 : 17:11:15

I'm not sure.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-05-09 : 23:23:24

Remember indexes are also needed for faster DML operations.Only thing is you need to set fill-factor low if it is regularly changed.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-05-10 : 15:12:04

quote:
Originally posted by sodeep

Remember indexes are also needed for faster DML operations.Only thing is you need to set fill-factor low if it is regularly changed.

We played around with lowering the fill factor and only saw excellent performance when it was set to 100%. Performance drastically changed for the worse when we lowered the value. BOL indicates that you should rarely change this, I believe starting with 2005.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-11 : 00:33:49

I think it does not.

A table variable always holds one record only according to query engine.
That's because no statistics are built on table variables.

The fillfactor is a useful tool if you plan properly. You have to consider recordsize and number of records in a page before changing the value.
If there are a LOT of inserts in a table, lowering the fillfactor can have a great performance impact, because the page splits will happen later rather than sooner.

E 12°55'05.63"
N 56°04'39.26"

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-11 : 19:32:54

But this was to a table variable, does fill factor even apply? Also, the total records inserted was only 2. Do the fill factor and indexes affect the table variable?

Jim

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-05-11 : 21:58:01

Well... the question is how fast you are able to retrieve from the select..portion and ofcourse index should be there to speed up. There is no reason to have fillfactor and clustered PK for Table variable just for 2 records.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-12 : 07:21:50

The select part of the query, when run by itself, took less than one second. When I added the insert into part the query took 17 minutes the first time, and the second time I killed it after 3 minutes. There are no indexes or pk on the table variable

Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-12 : 09:05:48

Are you low on memory or space on tempdb?

E 12°55'05.63"
N 56°04'39.26"

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-12 : 09:21:40

database_name database_size unallocated space
tempdb 9.50 MB 6.71 MB

EXEC sp_spaceused @updateusage = N'TRUE'

reserved data index_size unused
1320 KB 592 KB 648 KB 80 KB

Is what things look like now

Jim

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-05-12 : 10:02:45

Does the poor performance re-occur every time you run it under the condition you describe? ie: with the INSERT INTO @um and without the index.

Be One with the Optimizer
TG

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-12 : 11:36:47

Yes, if I take the index off, the performance goes back to over 15 minutes.

Jim

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-05-12 : 15:20:08

I have no idea. What I would do is start collecting some facts like:

Does the fact that it is a table variable make a difference? ie: switch to a physical table and see if there is any difference.

Is the execution time difference due to the plan changing? - look at actual exec plan for:
-with index and with insert
-without index and with insert
-without index and without insert

Seems like it has to be due to the plan changing. but I guess the first thing is to discover what is causing the difference. Once you know what is causing it (plan, blocking, whatever) then you can figure out why.

Be One with the Optimizer
TG

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-13 : 09:16:14

quote:
Does the fact that it is a table variable make a difference? ie: switch to a physical table and see if there is any difference.

Is the execution time difference due to the plan changing? - look at actual exec plan for:
-with index and with insert
-without index and with insert
-without index and without insert

There is a difference when switching to a physical table -- much faster. For some reason, the insert into the table variable shows an index scan on the line table (Cost 2%) and a merge join, the physical table is an index seek and nested loop. When the index is created the execution plans are the same. (Index seeks and nested loops/hash matches.

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources