|
Subject:
|
SQL suggestion
|
|
Posted By:
|
pankaj_daga
|
Post Date:
|
11/22/2003 10:00:50 AM
|
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.
Pankaj
|
|
Reply By:
|
Jeff Mason
|
Reply Date:
|
11/25/2003 11:15:02 AM
|
quote:
Originally posted by pankaj_daga
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.
SQL has no facility to handle arrays. The various languages which you use to interface to the RDMS (e.g T/SQL for SQL Server) generally are at best one pass compilers and efficient string manipulation is not one of their strengths, so you are right to be wary of passing in large strings and then parsing them apart.
You might want to look into the various client-based batch update mechanisms that various data providers, er, provide - e.g. ADO updateable recordsets or .NET Datasets.
If you are going to be inserting 40,000 entries at a pop, you should prepare yourself for the fact that inserting them all at once is going to take some time and you may want to think about setting user expectations appropriately or look into more exotic ways of "hiding" the updates from the user (i.e. doing the insert on a background thread, etc).
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
|
|
Reply By:
|
sal
|
Reply Date:
|
11/25/2003 2:11:10 PM
|
What will happen if you suddenly lose connection to the database whdn you are no record 20,000 of 40,000. Maybe a temp table would be best(I do not like temp tables). At least something that you can put in a transaction.
Users do not like to wait more than 5 seconds and someone will eventually kill the program just because they will think that "it crashed".
Sal
|
|
Reply By:
|
pankaj_daga
|
Reply Date:
|
11/27/2003 7:19:56 AM
|
Hi,
I put all the data in a few temp tables (I have a temp table for each corresponding real table). This way I can insert the data without a transacion in the temp table and the update the real tables in one transaction.
However, after doing some profiling I found out that what was taking all the time was copying the data from the temp tables to the real tables! Many a case it would even time out!
Pankaj
|
