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).
Custom Apps, Inc.