Im flooding my SQL Server with INSERT / UPDATE requests. How do I optimize?

I have an application that calculates a bunch of numbers and then inserts them into a table (or updates a record in the table if it exists). In my test environment it is issuing 100 insert or update requests to the server per second and it could run for several hours.
After a the first several hundred requests, the SQL server is bogging down (processor at 90-100%) and the application slows down while it waits on SQL to update the database.
What would be the best way to optimize this app? Would it help to loop through all my insert/update requests and then send them as one big batch of statements to the server (per 1000 requests or something)? Is there a better way of doing this?
Thanks!

Here are two approachs we did:

1. Queue up all insert/update, and run batch job as one transaction in one connection, next release of our Lattice.DataMapper will support run batch job in a seperate thread at certain time you define, see midnight tonight.
2. You can send a xml doc with all your insert/update data over to SQL server and call stored procedures using SQL server xml support. So only one stored procedure call you are done.

iif stored procedure

Hi all,

I have a bunch of queries initially written for MS Access that contain iif
function.
Since I'm new in SQL Server and I don't like the idea of rewriting all
queries using CASE statement, I hope someone has written iif T-SQL stored
procedure.
If someone have T-SQL code of such procedure I would appreciate you post
this code here.

Thanks in advance!"Anabella" <x@.x.com> wrote in message news:cu86s9$pj0$1@.ls219.htnet.hr...
> Hi all,
> I have a bunch of queries initially written for MS Access that contain iif
> function.
> Since I'm new in SQL Server and I don't like the idea of rewriting all
> queries using CASE statement, I hope someone has written iif T-SQL stored
> procedure.
> If someone have T-SQL code of such procedure I would appreciate you post
> this code here.
> Thanks in advance!

Since IIF doesn't exist in TSQL, I don't think you have any other option.
The Upsizing Wizard might help, but the most reliable thing to do would be
to rewrite them.

Simon|||"Anabella" <x@.x.com> wrote in message news:cu86s9$pj0$1@.ls219.htnet.hr...
> Hi all,
> I have a bunch of queries initially written for MS Access that contain iif
> function.
> Since I'm new in SQL Server and I don't like the idea of rewriting all
> queries using CASE statement, I hope someone has written iif T-SQL stored
> procedure.
> If someone have T-SQL code of such procedure I would appreciate you post
> this code here.
> Thanks in advance!

I think maybe you could write a udf which took parameters.
I think handling the various comparisons might be hard work.
Off the top of my head, I think you'd have to change the syntax of your
query a bit so you handed the comparison as a parameter.
And... erm... changing your query is what you wanted to avoid.

I've got some other bad news for you as well.

MS Access sql is slightly different from sql server sql .
I would suggest you need to go through all your queries and check em out.
Changing iif to case statements is likely just part of the task you face.

--
Regards,
Andy O'Neill|||Andy O'Neill (aon14nocannedmeat@.lycos.co.uk) writes:
> I think maybe you could write a udf which took parameters.
> I think handling the various comparisons might be hard work.
> Off the top of my head, I think you'd have to change the syntax of your
> query a bit so you handed the comparison as a parameter.
> And... erm... changing your query is what you wanted to avoid.

Since boolean is not a datatype in SQL Server, you cannot write a
UDF which permits you to say:

SECECT dho.iif(@.x = @.y, 1, 2)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95F7EFAAF416CYazorman@.127.0.0.1...
> Andy O'Neill (aon14nocannedmeat@.lycos.co.uk) writes:
>> I think maybe you could write a udf which took parameters.
>> I think handling the various comparisons might be hard work.
>> Off the top of my head, I think you'd have to change the syntax of your
>> query a bit so you handed the comparison as a parameter.
>> And... erm... changing your query is what you wanted to avoid.
> Since boolean is not a datatype in SQL Server, you cannot write a
> UDF which permits you to say:
> SECECT dho.iif(@.x = @.y, 1, 2)

I had in mind handing over the comparison operation as a string.
So check if the comparison to be made is varchar and '=' or maybe '>' or
'<'.
If that makes sense.

That's the part I'm actually most confident I could handle.
I didn't even begin to think about the various datatypes one could have for
other parameters...

--
Regards,
Andy O'Neill