Lots of inserts? Part I

Question

Lots of inserts? Part I

Kenneth.Fisher

SSC-Insane

Points: 20406
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]

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

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

Carlo Romagnano SSC-Insane Points: 22792 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: 4628 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: 1753 More actions · Answer 5

Nice little question on Batch Separators and Transactions.

Cheers Kenneth!

tripleAxe SSCertifiable Points: 5707 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: 5966 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: 5966 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: 4628 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!!!

tom.w.brannon Hall of Fame Points: 3976 More actions · Answer 14

I expected the answer to be that there was an error because tbl did not exist. Since that wasn't an option, I did some experimenting around the open transactions and got a surprise.

Here is my full code:

create table dbo.tbl (x char(5000));

go

BEGIN TRAN

INSERT INTO dbo.tbl VALUES

( REPLICATE('a',5000) )

GO 100

COMMIT

GO 2

select count(*) from dbo.tbl;

go

rollback;

go

select count(*) From dbo.tbl;

go

drop table dbo.tbl;

Unsurprisingly, the first select returns 100. But the second returns 0! In fact any number below 100 on the GO after the commit results in an empty table. Somehow the COMMIT is being ignored. When I do use 100 then the rollback generates an error that there are no open transactions. Can anybody explain? I also tried just using GO 3 after the insert and the commit only had any effect when it was also 3. 2 left an empty table.