Frequent Table Operations - 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

SQL Server Development (2000)

Frequent Table Operations

Author

Topic

NickRice
Starting Member

13 Posts

Posted - 2003-09-04 : 06:26:33

Hi,

I would appreciate any recommendations on a situation when a table will be frequently truncated of all records and new rows will be added to it. This process will occur on the table once every hour. I was just wondering if there are any guidelines (like proper indexes, recomplication of the SP for regeneration of the execution plan, reindexing etc.). The table has a PK and varchar fields with lots of text in it. I'm running SQL Server 2000.

Thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-09-04 : 08:02:33

If you'll be importing millions of rows, you might see improved performance if you drop all the constraints and indexes before importing, then re-create them afterwards. Doing so will also let you take advantage of fast bulk loading through bcp or BULK INSERT (if you're importing from a text file) However, you should test this before committing to it as it may cause more hassle than it's worth.

NickRice
Starting Member

13 Posts

Posted - 2003-09-04 : 08:09:13

Around 15,000+ records get loaded on each run (records are inserted from another table as part of a transaction in a SP) and the table will be accessed by a web application (multi-user environment).

Any thoughts?

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-04 : 10:45:27

Yeah...don't worry about it...



USE Northwind
GO
DECLARE @x int,@start datetime, @end datetime 
 SELECT @x=0, @start = Getdate()

SET NOCOUNT ON

SELECT * INTO myTable99 FROM ( 
SELECT * FROM [order details] 
UNION ALL SELECT * FROM [order details]
UNION ALL SELECT * FROM [order details]
UNION ALL SELECT * FROM [order details]
UNION ALL SELECT * FROM [order details]
UNION ALL SELECT * FROM [order details]
UNION ALL SELECT * FROM [order details]
) AS xxx

TRUNCATE TABLE myTable99

  WHILE @x < 10
	BEGIN
		INSERT INTO myTable99 SELECT * FROM ( 
		SELECT * FROM [order details] 
		UNION ALL SELECT * FROM [order details]
		UNION ALL SELECT * FROM [order details]
		UNION ALL SELECT * FROM [order details]
		UNION ALL SELECT * FROM [order details]
		UNION ALL SELECT * FROM [order details]
		UNION ALL SELECT * FROM [order details]
		) AS xxx

		SELECT @x = @x + 1
		TRUNCATE TABLE myTable99
	END
SELECT 'Time Elapsed (ms): '+ Convert(varchar(25),DATEDIFF(ms,@start,getdate()))
GO

DROP TABLE myTable99
GO

15,000 records isn't alot...well depending on row size I guess..but still....

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources