Lots of inserts? Part I

Question

Lots of inserts? Part I

Kenneth.Fisher

SSC-Insane

Points: 20396
More actions
July 27, 2016 at 12:08 am

#315597

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]
July 27, 2016 at 12:14 am

This was removed by the editor as SPAM

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

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

Manie SSCarpal Tunnel Points: 4568 More actions · Answer 1

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: 22669 More actions · Answer 2

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

Carlo Romagnano SSC-Insane Points: 22669 More actions · Answer 3

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: 4568 More actions · Answer 4

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: 1750 More actions · Answer 5

Nice little question on Batch Separators and Transactions.

Cheers Kenneth!

tripleAxe SSCertifiable Points: 5706 More actions · Answer 6

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: 2027 More actions · Answer 7

Really nice question.

And of-course nicely explain the answer.

edwardwill SSCertifiable Points: 5851 More actions · Answer 8

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 More actions · Answer 9

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: 148809 More actions · Answer 10

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 SSCertifiable Points: 5851 More actions · Answer 11

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: 148809 More actions · Answer 12

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: 4568 More actions · Answer 13

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!!!