Performance of Insert Into - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Performance of Insert Into

Author

Topic

bgutschow
Starting Member

7 Posts

Posted - 2008-07-31 : 10:45:07

A relative newbie here. I have the following statemtent:

insert into cur_results(attr_value,description,sessionid,selected, attr_code,pgroup,show)
select cur_attributevalues.attr_value,
cur_attributevalues.description,
'8AED5BFD-A8BA-4D80-AB83-6F36D4CE4CF3',
cur_attributevalues.default_val,
cur_attributes.attr_code,
cur_attributevalues.pgroup,
1
from cur_attributes
join cur_attributevalues on cur_attributevalues.attr_code = cur_attributes.attr_code
join cur_productgroup on cur_productgroup.prod_line = cur_attributes.prod_line and
cur_productgroup.pgroup = cur_attributevalues.pgroup
where cur_attributes.prod_line = 'DP4'

The Product Line DP4 selects approximately 13000 records. If the destination table contains existing records, the 13000 new records get loaded in 3 - 4 seconds. If the destination table is empty, the 13000 records can take 3 - 4 minutes to load.

Any thoughts would be greatly appreciated!
Bill Gutschow
Curbell Inc.

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2008-07-31 : 11:10:33

where are your indexes placed and what kind of indexes are they? i have a hunch this has something to do with allocating space in new index pages versus filling up spaces in existing pages.

Regards,
Sean

you are the sum of your record collection

bgutschow
Starting Member

7 Posts

Posted - 2008-07-31 : 13:17:29

Sean,
I got this out of Enterprise Manager. I hope this helps.

Bill
CREATE UNIQUE CLUSTERED
INDEX [PK_Cur_Results] ON [dbo].[Cur_Results] ([Attr_Value], [SessionID], [PGroup])
WITH
FILLFACTOR = 90
,DROP_EXISTING
ON [PRIMARY]

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2008-07-31 : 13:38:30

that looks like it might be a pretty big natural key you are using. if one record has a key that is bigger than the 10% of a page you are leaving empty, this could be causing page splits.

Is this the only method that data gets inserted into this table? If so, you might consider dropping the primary key with an alter table, doing your insert and recreating the primary key after wards.

If there is other activity hitting this table, you may want to use an explicit transaction to lock the table during the process.

Regards,
Sean

you are the sum of your record collection

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources