Best way to insert data to MSDE db

Hello.
I'm creating a stand-alone windows application using the MSDE server.
Performance issues in this application are big-deal to my client.
I need to insert data to the database in rate of – 2MB / sec.
Each insert action should insert ~ 10-40 MB to the database.
My questions are:
1. What is the fastest way to INSERT data to the database? Using insert from
c# code, scripts, stored procedure, other?
2. What is the fastest way to retrieve information from the database?
Thanks.
hi,
Eli wrote:
> Hello.
> I'm creating a stand-alone windows application using the MSDE server.
> Performance issues in this application are big-deal to my client.
> I need to insert data to the database in rate of - 2MB / sec.
> Each insert action should insert ~ 10-40 MB to the database.
> My questions are:
> 1. What is the fastest way to INSERT data to the database? Using
> insert from c# code, scripts, stored procedure, other?
nope... the fastest insert method is BULK INSERT (via BULK INSERT statement
or BCP.exe) that can even advantage from combined CPUs... and using BULK
LOGGED recovery model will help ...

> 2. What is the fastest way to retrieve information from the database?
data retrival is demanded to SELECT statements, and you have non other
ways.. you can optimize your index design to make the application works
better, you can even over-index your structure, but this will slow down
insertions..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks for the quick response.
Can you direct me to a link of how to implement the command by code ?
I'm working with C#, and need to send the data from the code.
Do you know of a way to insert the data has an object?
I mean, the rows I want to insert are allready in a class/struct format, and
sending them as-is to the database, insetd of parsing them to insert command,
will be, I think,a fast way. What do you think?
"Andrea Montanari" wrote:

> hi,
> Eli wrote:
> nope... the fastest insert method is BULK INSERT (via BULK INSERT statement
> or BCP.exe) that can even advantage from combined CPUs... and using BULK
> LOGGED recovery model will help ...
>
> data retrival is demanded to SELECT statements, and you have non other
> ways.. you can optimize your index design to make the application works
> better, you can even over-index your structure, but this will slow down
> insertions..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi,
Eli wrote:
> Thanks for the quick response.
> Can you direct me to a link of how to implement the command by code ?
> I'm working with C#, and need to send the data from the code.
> Do you know of a way to insert the data has an object?
> I mean, the rows I want to insert are allready in a class/struct
> format, and sending them as-is to the database, insetd of parsing
> them to insert command, will be, I think,a fast way. What do you
> think?
>
unfortunately BULK operations only supports flat data format and not
structured formatted data... you can use CSV, native SQL Server exported
data via BCP and so on, but not data from xml files and the like...
BULK operations accept a (txt) file to be imported, as described in
http://msdn.microsoft.com/library/de...ba-bz_4fec.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply