Optimization question

[k108]

Hi,

We have a database that is used for email generation. Once a day, several hundred thousand emails are generated in one of the tables. Consequently, this particular table grows very large.

Then once the emails are sent out, the table shrinks in size (I'm not sure how this happens, but...) Anyways, it has to do with the data being deleted from the table.

So, we've got a situation where a table grows very large, then is reduced in size, on a daily basis.

Does anyone have any tips for the optimization for this process???

Thanks

 

[mrdenny]

As data is deleted from a table that table will shrink in size. You are removing data from the table, that's what's going to happen.

To optomize this process do not have the database auto-shrink. Make sure that the database growth rates are set at a good rate so that it doesn't slow down the database because the growth rates are to big, but so small that it have to grow to many times during the operation.

Make sure you have as few indexes as possible as this will slow down the insert and deletes.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[mrdenny]

Didn't mean to treat you like a plithering idiot (which I can tell you aren't from our other chats online) it's been a long day already.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[k108]

No, no offense taken. I am new at all this. I am a certified Oracle DBA (ok, so it was a few years ago!) so I know I'm not "dumb", ha, but just ignorant about SQL Server.

I'm just grateful for the help!

Yeah, I'm pretty sure we aren't set to autoshrink, but I'll check - that's a good point.

Thanks

 

[mrdenny]

Unfornitually in SQL Server we can't manage the table sizes like you can in Oracle. It's something that is lacking on the SQL side from my point of view. (I think I mentioned this at some point.)

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief