Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 22nd, 2003, 11:00 AM
Authorized User
 
Join Date: Sep 2003
Location: , , .
Posts: 83
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL suggestion

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 With Quote
  #2 (permalink)  
Old November 25th, 2003, 12:15 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
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 With Quote
  #3 (permalink)  
Old November 25th, 2003, 03:11 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 With Quote
  #4 (permalink)  
Old November 27th, 2003, 08:19 AM
Authorized User
 
Join Date: Sep 2003
Location: , , .
Posts: 83
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Two question for suggestion? ssomchai BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 0 April 30th, 2008 09:31 PM
Need your suggestion... popp SQL Language 1 July 2nd, 2007 01:44 AM
Suggestion for your Website RobMeade BOOK: Beginning VB.NET 2nd Edition/Beginning VB.NET 2003 2 July 21st, 2004 01:23 PM
Suggestion Snib Forum and Wrox.com Feedback 2 April 10th, 2004 04:27 PM
Suggestion Ben Horne Flash (all versions) 4 April 7th, 2004 09:18 AM



All times are GMT -4. The time now is 06:28 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.