akaRickShaw

Hi, just like the subject, what is the best way Currently, I have a stored proc. to do the inserting of records that is invoked by my C# app. So, every 5 secs, a code is called to insert the 4000 records. I also have a some sort of a header record for the group of 4000 records. When I look at the record created date of the header record, they roughly 14 secs. apart.

Thanks,

Rick..


Re: .NET Framework Data Access and Storage What is the best way to insert 4000 records to database every 5 secs. in C#?

guy kolbis

Hi,

Your options are:

1) Stored proc

2) Inline sql statements

3) Batch update (using the SqlDataAdapter)

4) SSIS/DTS

You need to considure these options according to your design.

For performance issues I would go with 3.

If you will give us more details about the insert operation I could maybe give a better answer.

For example Are you using ADO.net 2.0 Do you need to get back the identities after the insert opration

Hope that helps.

guy kolbis






Re: .NET Framework Data Access and Storage What is the best way to insert 4000 records to database every 5 secs. in C#?

boban.s

Can you explain the reason for inserting 4000 records every 5s. That is to much data. Is this occurs all the time or just in periods. To speed up the inserts, you need to remove indexes of table, also trigers are out of order, not to mention foreign keys. Also autogrowt must be bigger than default 10%.
And the way of inserting the data is important. But this is to much data, so you will probably need to implement some queue funtionality on the client side.




Re: .NET Framework Data Access and Storage What is the best way to insert 4000 records to database every 5 secs. in C#?

RickShaw888

Hi, thanks for helping. Here is what the app. is doing.

There is a list which contains 4000 items. At the press of the timer, the app. will loop through the list of 4000 items every 5 secs. Each pass, a batch header records gets created (through stored procedure) and bring back to the app. the new identity value of the batch. This new batch identity value then gets used in creating the 4000 records. In inserting the 4000 records, the loop has a logic to determine what table to insert a particular record to. There are currently 4 possible tables a record can go to. All the table/database inserts are stored procedure call.

I have a method called Record() that has the code to do the above inserting of records. Now, everytime this method called (timer in the gui), it creates the connection string and create all the SqlCommands for the 4 stored procedures with their parameters. Actually, 5 stored procedures including the one that inserts the batch header. After I finished creating the stored procedures stuff (parameters, etc), I open the connection and this connection doesn't close until all 4000 records are written out in the database. I have also put in a transaction just to be sure that the batch gets inserted in the database at one piece.

I am using .Net 2.0 and the database is MS SQL 2005 Server Express Edition.

Please let me know if you need more info or clarification.

Thanks again.

Rick..




Re: .NET Framework Data Access and Storage What is the best way to insert 4000 records to database every 5 secs. in C#?

RickShaw888

Hi, this application, actually a test application is for engineering use. 4000 (or possibly more) every 5s is not a common thing but this could happen. It could occur for an entire day but again, not all the time.

Thanks,

Rick..




Re: .NET Framework Data Access and Storage What is the best way to insert 4000 records to database every 5 secs. in C#?

boban.s

I still don't have the picture of the whole process. What is the purpose of the Client App. Is it posible that all calculation in the loop can be executed on the Server. Or the same question, what is the purpose of the Server. Is all of 4000 records are changing or you just want to insert all of them. If they are not changing, you can think of not inserting all of them. You can make insertion like a continues job that will try to insert all the records founded in records array. Records will be added in this array and the job for inserting will insert them in the DB when he catch up. So when you have to insert records more frequently then the array will grow in size, but when have a pause in generating records, the job will process the already inserted array records and decrese the size of array and if the break is big, maybe insert all tempting records from array and wait for new ones.




Re: .NET Framework Data Access and Storage What is the best way to insert 4000 records to database every 5 secs. in C#?

guy kolbis

Hi,

So I would still use the 3rd option in the following way:

1) call the sp to insert the header and get back the identity.

2) create 4 SqlDataAdapter, one for each insert query.

3) create 4 DataTables

4) Processing the 4000 records and inserting them to the right DataTable

5) Execute the update command in each dataadapter with the DataTable as input

Note that for the adatpers to work, each row state should be added. if you will need help with that let me know.

guy kolbis






Re: .NET Framework Data Access and Storage What is the best way to insert 4000 records to database every 5 secs. in C#?

akaRickShaw

Hmmm, actually, posting of 4000 records every 5 sec. now works. That is after I added one index in one of the reference table.

Thanks,