Greetings,

I'm interesting in hearing about certain techniques that might enable me to
execute nonqueries in bulk much faster than one-by-one. Currently, we have
areas on the system that'll execute anywhere from 25 to 1000's in bulk but
it does so by sending the command each time.

One technique that I'm working on, in our data access layer (ADO.NET
wrapper, similar to but very different from the Data Access Block), a
BatchProcessor object, that queues up stored procedure commands (at the
moment just SP's), and converts them into a single string of "EXEC
usp_SPName @param1=...; EXEC usp_SPName @param1=...; etc" and executes that
single string. It actually does increase performance (not significantly on
fewer commands) but I was wondering if there are better ways (that don't
include making changes to the database or stored procedures).


Thanks,
Shawn

I expect you're spinning your wheels. Check out the SqlBulkCopy or SSIS
interfaces that can do this FAR more efficiently.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"Shawn B." <(E-Mail Removed)> wrote in message
news:e$(E-Mail Removed)...
> Greetings,
>
> I'm interesting in hearing about certain techniques that might enable me
> to execute nonqueries in bulk much faster than one-by-one. Currently, we
> have areas on the system that'll execute anywhere from 25 to 1000's in
> bulk but it does so by sending the command each time.
>
> One technique that I'm working on, in our data access layer (ADO.NET
> wrapper, similar to but very different from the Data Access Block), a
> BatchProcessor object, that queues up stored procedure commands (at the
> moment just SP's), and converts them into a single string of "EXEC
> usp_SPName @param1=...; EXEC usp_SPName @param1=...; etc" and executes
> that single string. It actually does increase performance (not
> significantly on fewer commands) but I was wondering if there are better
> ways (that don't include making changes to the database or stored
> procedures).
>
>
> Thanks,
> Shawn
>