SQL suggestion requested

[xargon]

Hi everyone,

I am developing an application that needs to do multiple writes many tables in the database. I will explain the structure of the tables and what I am doing:

Right now, I make an entry for every new data. There could easily be more than 40,000 entries for each application run. I would have liked to store the entries in memory (in an array) and would like flush them out in one stored procedure. The only glitch is that I do not know if stored procedures can handle C++ arrays.

I saw many articles on the web about using comma seperated strings etc and passing it to the stored procedure. However, I am always wary of strings, especially hige concatenations. I was wondering if someone has any experience regarding this and what would be the best course of action. The performance of the application right now is almost unacceptable and I would like to optimize it before it grows out of bounds.

Thanks a lot.

Xargon

[raghupathys]

you can try this approach,

1. Open a db connection.
2. Use a recordset object, and retrieve the top 1 row from the table that you want to add to.
3. Disconnect the recrodset.
4. Now keep adding rows to the recordset using the addrow method.
5. Once you are thru, connect the recordset back to the db.
6. Call the updatebatch method of the recordset object.

all the rows that you had added will be inserted into the table in one shot.

hope this helps.

[xargon]

Hi there,

Thanks for the recordset suggestion. However, is there any way to do this through CRecordset class from MFC. The AddNew function in CRecordset does not seem to support bulk row insertions. If you know of something, please let me know.

Thanks,
Xargon

[antares686]

Originally posted by xargon
Hi everyone,

I am developing an application that needs to do multiple writes many tables in the database. I will explain the structure of the tables and what I am doing:

Right now, I make an entry for every new data. There could easily be more than 40,000 entries for each application run. I would have liked to store the entries in memory (in an array) and would like flush them out in one stored procedure. The only glitch is that I do not know if stored procedures can handle C++ arrays.

I saw many articles on the web about using comma seperated strings etc and passing it to the stored procedure. However, I am always wary of strings, especially hige concatenations. I was wondering if someone has any experience regarding this and what would be the best course of action. The performance of the application right now is almost unacceptable and I would like to optimize it before it grows out of bounds.

Thanks a lot.

Xargon

You can only

use the concatinated method as long as the submitted data length will not exceed 8000 bytes (char/varchar type limit).

If this is for inserts only then use ADO to create your connections, recordsets and commands then you can configure so you can run the SP over and over and limit the chatter to some extenmt.

If you can give a bit more detail on the logic and what you are doing as far as flow I can provide a small example of code that might help.

[xargon]

Hi,

Thank you so much for the reply. Here is a bit more detail on what I am trying to do:

Basically, there are these few tables. I will only list 3 tables to show what I am really trying to do.


Table Experiment_T:
--------------------------
-- ExperimentID: Primary Key, Identity
-- Person ordered
-- Person responsible
-- Date

Now each experiment has many tests

Table Tests_T:
--------------------------
-- TestID: Primary Key, Identity
-- ExperimentID: The parent experiment for this test
-- Analysis Type (irrelevant)
-- Comments

Now, each test can have various states that we want

Table TestStates_T
-------------------------
-- StateID: Primary Key, Identity
-- TestID: The parent test
-- StateName
-- StateValue

Now, each experiment can have at least 5000 tests, each with another 5-10 states. Since there is concurrent database access, what I am doing is putting the data in temporary tables and then copying them to the main table in one transaction after everything is copied over.

However, since the ExperimentID and TestID are referenced in more than one table, I have to make a loop which basically does the following

-- insert the experiment in the main experiment table and gets the identity of the inserted value (ExperimentID)

-- Now, loop through each test in the temporary table and get the temporary testID.
-- Insert into the Tests_T table and get the identity value (new TestID).
-- Find all data in temporary TestStates_T table that has the temporary testID and copy it in the Tests_T table and use the new TestID for the TestID column.

This referencing is what is another problem and I do not know if recordset can fix this.
I am very shaky with database stuff, so I am sure that there must be a better way to do this. If some of you more experienced folks can show me some way, I would be really grateful.

Thanks,

Sincerely,

Xargon

[xargon]

Hi all,

Another thing that I noticed was a degradation of SQL server performance on Insert(s). When I start my program, I can update 120-130 records a minute (there is a lot of processing within my app). However, by the second minute it starts slowing down and it becomes about half of it by the third minute. There are no memory leaks that I am aware of. The task manager does not report any rise in memory. So, I am not sure what causes it.

Pankaj