[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

INSERTS IN STORED PROCEDURE

Posted on 2004-11-23
5
Medium Priority
?
3,306 Views
Last Modified: 2008-01-09
1) We have a application where from front end, 30 values are input then together with current date & timestamp, 30 rows should be inserted in a table.

2) This can be 30, 60, 200 etc. i.e. any number of inputs & hence any number of records to be inserted in table.

3) char & varchar datatypes is max 255 characters. text is big & okay for us but cannot be passed as a stored procedure parameter.

4) We are trying to send a concatenated string including values of all these 30 etc. inputs from frontend. This we will tokenize in stored proc & carry out the inserts. Is this possible?

4) Are there any array type datatypes in Sybase.  Is there any other way of doing this in a stored procedure.
0
Comment
Question by:k_murli_krishna
5 Comments
 
LVL 1

Accepted Solution

by:
gagaliya earned 300 total points
ID: 12657777
If i understand what you are saying correctly, you just want to do multiple inserts from user-defined values. Why are you trying to force a single stored proc to do this. You should use a loop within your application language to do the inserts one at a time. This is the most clean implementation

Using string concatenation to pass the values into a stored proc as a single input, then parse it out within the sp, and loop through them for insertion is a complete nightmare. Not to mention there is a size limitation on input parameters for the sp.  This should be done on the application level, not database.

The only array datatype structure i am aware of is cursor which is not applicable here. The only way i know that allows you to do mass inserts in sybase is:  insert into TableOne select Cols.. from TableTwo.  This is very useful but only if the data already exist in db table somewhere. If it is passed to you from frontend gui/forms, there is no way around it. Just loop through multiple insert statements.


0
 
LVL 14

Expert Comment

by:Jan Franek
ID: 12657839
Ad 3) - in newer versions of ASE (I think 12.5 or later) char & varchar datatypes can have up to 16384 characters

Ad 4) - yes it is possible - using charindex and substring functions it can be done quite easily, but I'm not sure how effective is string handling in T-SQL (compared to C)

Ad 5) - no, there's no array datatype in Sybase ASE
0
 
LVL 10

Expert Comment

by:bret
ID: 12657862
On 12.5.x, the maximum size of a char/varchar is increased ~16k, so you could pass quite a few values (but not an unlimited number) all concatenated together in one string.

But I have to agree, I don't think handling the values that way is a good idea, it seems much cleaner to just have the application do one insert for each value submitted.
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 12660201
You've already been given good advice, and I agree with what the others have said. There's little business or technical reason to want to handle multiple inserts like this in a single procedure call.

You could place all the rows to be inserted in a file, or something that looks like a file (a UNIX named pipe, for example), and call bcp to do a bulk insert to the table in a single operation. This is faster than row by row inserts - "slow" bcp will be the same speed as INSERT ... SELECT, and "fast" bcp is roughly equivalent to SELECT INTO in performance. But you'll lose time in exporting the rows from your app to a file, and cleaning up the file afterwards. This has to be done outside of ASE, which means you lose ASE transaction management and error handling.

It *is* possible to to a "bulk array insert" directly from a C array structure in your app into a Sybase table, using the Bulk Library functions in Open Client. This is basically equivalent to doing a bcp from a file into a table. This is complicated and unless you're talking hundreds of thousands of rows that has to be loaded as quickly as possible (this approach *will* outperform row by row inserts in the same way that fast bcp does), it probably isn't worth the coding and testing effort.

Nah, stick with row by row inserts from your app. If a single-threaded approach isn't fast enough, look into multiple connections each doing a subset of the inserts. This will introduce locking and concurrency problems which you'd need to fix with partitioning the table and/or row-level locking - both of which introduce other issues.
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 12664860
k_murli_krishna
the trick here is not to have one proc call that does 30 inserts (because as every has already said: that is just too hard and silly), but to actually batch 30 EXEC procedure statements into one string and the pass that string to sybase as if it was a single statement.

this is much more efficient than 30 seperate calls on sybase (especially over a WAN)

NOTE: you may have to increase your buffer sizes for larger strings to be passed.

if this is not a matter of improving performance and it is just about batching commands that belong together, then you should be looking at "transaction control" techniques.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Moore’s Law has proven itself time and time again since it was first introduced. So what’s next? Will Moore’s law continue to remain relevant, or will new technology take over and bring us the next big advancement in computing?
Last month Marc Laliberte, WatchGuard’s Senior Threat Analyst, contributed reviewed the three major email authentication anti-phishing technology standards: SPF, DKIM, and DMARC. Learn more in part 2 of the series originally posted in Cyber Defense …
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses
Course of the Month18 days, 6 hours left to enroll

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question