Speed up insert and delete Queries

[abkad]

Hi all ,
I am loading data files intp Sql server 2005. I must delete then insert a large number of rows into tables.
The Queries for examples are

Delete from customer where name='John' and country='US'
.
.
Insert into customer values ('John','Bray','US')
Insert into customer values ('Jim','Adams','US')
Insert into customer values ('Chris','Peters','CA')

for the Insert queries I tried to insert to a temp table and then insert to customer but the performance was the same.

Insert into customer (name, country)
select 'John','Bray','US'
union all 
select 'Jim','Adams','US'
union all 
select 'Chris','Peters','CA'

Any Idea or suggestion on improving the performance of my queries and speeding up my queries

Cheers!!

 

[Mich]

Hmmm, some questions. How are these queries executed? Is it a bulk/batch job or are these queries run throughout the day? Do you have an indexing strategy on this table(s)?

-If it ain't broke, break it and make it better.

 

[abkad]

Well I can say it is a throughout day exceution since files are frequently pushed to be processed to delete old data and insert new. the index strategy is well done on the table. But since I have too many deletes and inserts that causing the problem so I am looking into a startegy that can speed up the large number of queries been exceuted.

What do you mean by break and of what?

Thanks!!!

 

[Mich]

Since you're running a lot of inserts and deletes you need maintenance jobs to update your statistics and reorganize/rebuild your indexes. You mention that you're loading data files, what is this process?

As far as query optimization, the simpler the better.

-If it ain't broke, break it and make it better.

 

[SQLSister]

If using a set-based approach didn't speed things up from the use of repeated values clauses, I wonder if there is a trigger that is causing the slowdown.

Also, you may have too many indexes, this slows down inserts, updates and deletes. Don't just willy-nilly delte indexes though, you can have a huge impact on select statements.

If you are truly inserting a lot of records, consider using SSIS, it is a very fast way to insert records.

"NOTHING is more important in a database than integrity." ESquared

 

[Olaf Doschke]

I wonder what you're doing here with deletes and inserts and why there are files frequently "pushed".

You know they recently discovered a third main sql statement called UPDATE?

Bye, Olaf.