indexing/updating rebuilt table

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

SQL Server Development (2000)

indexing/updating rebuilt table

Author

Topic

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2008-01-03 : 14:23:26

I have a table with around 150,000 rows that gets reloaded every night (DELETE followed by INSERT). The table has around 60 columns, with three indexes, one on SSN (PK), one on FNAME/LNAME, and one on EMPLOYEENUMBER. The performance of the table has been reasonable but I want to make sure it's as good as possible. By default in SQL Server 2000, should the statistics on the table be getting updated automatically after the new data inserted? If not, should I do UPDATE STATISTICS? Also, when I do DBCC SHOW_STATISTICS on the primary key, the average length is 11 and the density is 6.7... how do I know if these are good values?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-01-03 : 14:29:34

Auto update/create should be enabled plus a job that runs update statistics. We can't determine when SQL Server will decide to do the auto update/create, so that's why a manual job is needed too.

I'd highly recommend running update statistics on this table directly after the import.

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

X002548
Not Just a Number

15586 Posts

Posted - 2008-01-03 : 14:36:24

first I would truncate the table

And I question when you say INSERT...you don't mean T-SQL INSERT do you

and 150,000 isn't a lot of data

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2008-01-03 : 14:45:11

I was wondering if somebody would mention doing TRUNCATE instead of DELETE. The only reason I'm doing DELETE is that I thought using TRUNCATE means no logging occurs and the transaction can't be rolled back correctly. Am I wrong?

Why wouldn't I mean T-SQL INSERT? Are you saying I should be using BCP or something?

X002548
Not Just a Number

15586 Posts

Posted - 2008-01-03 : 14:51:46

yes, truncate is a minimally logged operation

Is the table having transactions applied to it? Doesn't sound like since you're blowing it away every night. So if it's not be touched expect for read only operations, and the source of the data is from someplace else, why not do TRUNCATE

So what is the source of the data, that you have to do an insert?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2008-01-03 : 14:57:48

quote:
Originally posted by influent
I thought using TRUNCATE means no logging occurs and the transaction can't be rolled back correctly. Am I wrong?

Depends on how you are thinging about it

Are you thinking you can recover the rows after the transaction completes? No, only if you play the logs.

Here, run this


CREATE TABLE myTable99(Col1 char(1))
GO
INSERT INTO myTable99(Col1)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C'
GO

SELECT * FROM myTable99
BEGIN TRAN
TRUNCATE TABLE myTable99
SELECT * FROM myTable99
ROLLBACK TRAN
SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2008-01-03 : 15:02:26

The source is a table from a different database on the same server, but it's not a straight copy, the data gets filtered and manipulated somewhat. It's a long story, but suffice it to say that it has to be that way. And I need to keep it in a transaction so the table isn't empty if an error occurs.

A few insertions are made into the table each day.

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2008-01-03 : 15:04:06

Hmm, thanks Brett, I guess there's no reason to use DELETE instead of TRUNCATE in this case.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-01-03 : 15:07:33

Not sure if it's clear or not, but in Brett's sample code he shows that you can rollback a TRUNCATE as long as you are inside a transaction.

So I'd suggest using TRUNCATE rather than DELETE for performance reasons. Just make sure you test a rollback situation.

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

X002548
Not Just a Number

15586 Posts

Posted - 2008-01-03 : 15:11:59

my 2 cents

1. Create a view out of all your rules and make it in the format of the destination table
2. bcp out the data in native format using xp_cmdshell
3. inside a transaction, truncate the table
4. Confirm the truncate emptied the table
4. using xp_cmdshell, bcp the view output to the table
5. confirm the bcp in worked
6. Commit the work

Done

In your spare time

It should be a lot faster, if that's a concern (which it always is)

So the table is read only right

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2008-01-03 : 15:13:59

quote:
Originally posted by influent
A few insertions are made into the table each day.

Wait, which table

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources