Lots of inserts? Part I

  • Kenneth.Fisher

    SSCoach

    Points: 19542

    Comments posted to this topic are about the item Lots of inserts? Part I

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • This was removed by the editor as SPAM

  • Manie

    SSCarpal Tunnel

    Points: 4488

    Nice, thanks Kenneth. I was doubtful when I saw the begin tran and only saw commit. Am I wrong by saying that is the reason for the 99 open transactions? I have never seen anybody use it like that before.

    Manie Verster
    Developer
    Johannesburg
    South Africa

    I am happy because I choose to be happy.
    I just love my job!!!

  • Carlo Romagnano

    SSC-Insane

    Points: 21760

    Waiting for commiting all transactions, in reality, the table is empty for the other connections.

  • Carlo Romagnano

    SSC-Insane

    Points: 21760

    I prefer to use "SET IMPLICIT_TRANSACTIONS ON"

    Only one transaction on first I/O and one commit.

    SET IMPLICIT_TRANSACTIONS ON

    INSERT INTO tbl VALUES

    ( REPLICATE('a',5000) )

    GO 100

    COMMIT

    GO

    print @@trancount

  • Manie

    SSCarpal Tunnel

    Points: 4488

    I tested the COMMIT by adding COMMIT TRAN and still had 99 open transactions so I take it the GO 100 must be it and I also have never had any use for that. How would a person then make sure that each row/transaction has been committed? This is the reason why I like QotD so much because I learn things that I have never done or thought of doing before.:-D:-D:-D:-D:-D

    Manie Verster
    Developer
    Johannesburg
    South Africa

    I am happy because I choose to be happy.
    I just love my job!!!

  • shaneoneillis

    SSCommitted

    Points: 1635

    Nice little question on Batch Separators and Transactions.

    Cheers Kenneth!

  • tripleAxe

    SSCertifiable

    Points: 5522

    Great question! That's a neat little trick I need to remember. Never have seen that before and I'm sure it will come in useful.

  • Krishnraj

    SSCrazy

    Points: 2026

    Really nice question.

    And of-course nicely explain the answer.

  • edwardwill

    SSCarpal Tunnel

    Points: 4845

    I ran the script and then this

    SELECT COUNT(*) FROM tbl

    Guess what number it returned? (HINT - an integer between 99 and 101)

  • joeroshan

    SSChampion

    Points: 10377

    Thanks Kenneth

    I have used GO separator recursion logic for deleting batches/loading dummy data in development test environments in one time tasks. Good reminder to be careful with your transactions always!

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • John Mitchell-245523

    SSC Guru

    Points: 148265

    edwardwill (7/27/2016)


    I ran the script and then this

    SELECT COUNT(*) FROM tbl

    Guess what number it returned? (HINT - an integer between 99 and 101)

    Not sure whether you're saying you're surprised at that? If you run your query from a separate connection, something quite different will happen.

    John

  • edwardwill

    SSCarpal Tunnel

    Points: 4845

    John Mitchell-245523 (7/27/2016)


    edwardwill (7/27/2016)


    I ran the script and then this

    SELECT COUNT(*) FROM tbl

    Guess what number it returned? (HINT - an integer between 99 and 101)

    Not sure whether you're saying you're surprised at that? If you run your query from a separate connection, something quite different will happen.

    John

    Merely demonstrating on a public forum the depth of my ignorance! I will crawl away in shame now.

  • John Mitchell-245523

    SSC Guru

    Points: 148265

    edwardwill (7/27/2016)


    John Mitchell-245523 (7/27/2016)


    edwardwill (7/27/2016)


    I ran the script and then this

    SELECT COUNT(*) FROM tbl

    Guess what number it returned? (HINT - an integer between 99 and 101)

    Not sure whether you're saying you're surprised at that? If you run your query from a separate connection, something quite different will happen.

    John

    Merely demonstrating on a public forum the depth of my ignorance! I will crawl away in shame now.

    No shame. Better a silly question than a silly mistake!

    John

  • Manie

    SSCarpal Tunnel

    Points: 4488

    Edward,

    Confusius said: Ask a question and be a fool for a moment rather than not asking and remain a fool forever!

    Manie Verster
    Developer
    Johannesburg
    South Africa

    I am happy because I choose to be happy.
    I just love my job!!!

Viewing 15 posts - 1 through 15 (of 40 total)

You must be logged in to reply to this topic. Login to reply