check if I am correct

[Glowworm27]

Hello all,

we have a SQL database that is populated with data from an Oracle database for website stuff. I did not write the queries that get the data, a former developer wrote them, and we have been having a little troulbe with the transacation logs and some other things. Which is how I got involved.

The SQL jobs run and refresh the data every 30 minutes.

The jobs consist of the following query (greatly reduced for the web, but in a sense this is what it does.)

Begin transacation
Truncate Table mytable 

Insert into mytable(
Select * from oracledatabase where addtime > sysdate -90
)
if @@trancount >0 
Commit transaction

I was thinking that this is a very huge waste of resources and bandwidth and I should optimize the jobs and queries to reduce bandwidth, time and resources.

an optimized query would do something like this

begin transaction
delete mytable where addtime < dateadd(day, -90, now())

insert into mytable(
Select * from oracledatabase where addtime > (Select Max(addtime) from mytable)
)
if @@trancount > 0
Commit transacation

obviously i would fix up the syntax so it would be correct but I think you get the idea.

Instead of truncating the table each time the job runs, and inserting 90 days worth of data. (which can be approx 30,000 rows)

Delete the data older than 90 days from mytable
and get the data from the oracle database newer than the data in mytable

as an FYI there are 7 jobs just like this that run to fill several tables, and they run every 30 minutes.

George Oakes
CEO & President
COPS Software, Inc.

http://www.cops2005.com

Programmer & Developer
.Net, WSS 3.0, SQL DBA
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[markros]

Well, you will have performance problems if you change from Truncate to delete as truncate is minimally logged operation which doesn't use lots of resources and delete is logged and slow operation.

On the other hand, if you run this job every 30 minutes, then you're right, you will be deleting only a few records and inserting a few records every time, so it does make sense to change it this way.

PluralSight Learning Library

 

[Glowworm27]

Yeah I knew that truncate was minimally logged, and deletes were logged with greater detail.

the database is set to Simple recover mode how much does that affect the deletes vs the truncates?

Thanks
G

George Oakes
CEO & President
COPS Software, Inc.

http://www.cops2005.com

Programmer & Developer
.Net, WSS 3.0, SQL DBA
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[markros]

If you have a development database you can try with I suggest to switch to your approach and monitor the performance for a day. It does sound that you should get a performance increase with this idea.

The difference in logging of Delete vs. Truncate in Simple recovery mode should not be that big.

PluralSight Learning Library