Posted on 2004-11-23
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.
Question by:k_murli_krishna
    LVL 1

    Accepted Solution

    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.

    LVL 14

    Expert Comment

    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
    LVL 10

    Expert Comment

    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.
    LVL 24

    Expert Comment

    by:Joe Woodhouse
    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.
    LVL 6

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    PRTG Network Monitor lets you monitor your bandwidth usage, so you know who is using up your bandwidth, and what they're using it for.
    This paper addresses the security of Sennheiser DECT Contact Center and Office (CC&O) headsets. It describes the DECT security chain comprised of “Pairing”, “Per Call Authentication” and “Encryption”, which are all part of the standard DECT protocol.
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now