Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2015
    Posts
    3

    Unanswered: Sequences, Locks, and Clusters

    Hello.
    I am currently working with a system that operates on multiple clusters and when creating new records, it uses Sequences to generate the PKs. These are for generally smaller numbers of records at a time, however I am interested in expanding to handle larger batches of records that in addition to the smaller instances, but I would like to cut down on the number of calls required if possible.
    I will say that I am aware that I can just do a batch insert and include the NEXTVAL call for the PK in that statement, however I am caching the PK for the duration of processing for other reasons so this will not suffice.

    I currently have two thoughts on how to approach this:
    1: Alter the sequence in a transaction, incrementing by the number of new rows, store the CURVAL in a temp variable, commit the transaction, and return the temp variable. I can assume that the range of CURVAL - # -> CURVAL are my reserved PKs. I have no idea how to implement this, but would have to assume a stored procedure would be involved. And that I would have one created per PK/ table. Concern is with how the transaction affects the sequence and how

    2: Create a recursive CTE, using 0 as my base step and selecting those below the PKs needed. (Or -1 to prevent more than I need I think). I attempted the below:
    Code:
    WITH CTE AS (
    	SELECT NEXT VALUE FOR SEQ_#TABLE_NAME# AS PK, 0 AS STEP
    	UNION
    	SELECT NEXT VALUE FOR SEQ_#TABLE_NAME# AS PK, STEP+1 AS STEP
    	FROM CTE
    	WHERE STEP < #NUM_ROWS#
    )
    SELECT T.PK FROM CTE AS T
    My issue with this is the question of recursive CTE performance vs calling for individual values for the same amount. Not concerned with latency between app and db. At the very least I would not be interfering with other connections.

    3: I have my two ideas, but I lack experience here. Are there any other suggestions in addition to critique?

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    do you need to know what the sequence number was for other processing? Such as inserting a foreign key into a subordinate table(s)? If not, I would suggest just using an identity column. No need to call for the sequence, just insert your next record and let the DB handle the numbering.
    Dave

  3. #3
    Join Date
    Jan 2015
    Posts
    3

    Re:

    Yeah, that is their main use so I still need to retain it. Otherwise, I would have just used an identity column and had done with it.

  4. #4
    Join Date
    May 2014
    Posts
    24
    Have you tried other options other than using a foreign key for numbering?

  5. #5
    Join Date
    Jan 2015
    Posts
    3
    There is another key being used on all the tables that consists of a concatenated VARCHAR of unique identifiers from the parent table down to the table in question, however they are not used for determining the foreign key. I can't imagine using those in terms of a join on LIKE. Also, storing the key internally allows for additional error handling. I have tried playing with the system and it isn't easy or flexible to attempt a batch insert with a join on that key. This is from a system long before me.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •