April 7, 2008 at 11:44 am
Does having a GO statement between individual DML statements in a SQL script reduce potential for resource contention/locking?
Or is it just a network utilization issue?
Which is better?
UPDATE table1 SET col1 = 1;
UPDATE table1 SET col1 = 2;
...
UPDATE table1 SET col1 = n;
GO
OR
UPDATE table1 SET col1 = 1;
GO
UPDATE table1 SET col1 = 2;
GO
...
UPDATE table1 SET col1 = n;
GO
Thanks for any advice! 🙂
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 7, 2008 at 12:30 pm
This is a batch separator. In some places, it will impact what your scripts will do.
The semi-colon you have included is also a batch separator but is different in the impact on the script.
You should read the books online section on these two to see what they will do.
April 10, 2008 at 12:57 pm
It should reduce lock times. Since each batch is run as a set, breaking up the batches with "go" should let each one complete without holding more resources. I'm not certain on that though, because the following works (I didn't expect it to):
create table #Test (
ID int identity primary key,
Val varchar(10))
go
begin transaction
insert into #test (val)
select 'Test1'
go
insert into #test(val)
select 'Test2'
rollback
go
select *
from #test
When I ran that, the transaction rolled back and I ended up with 0 rows in the table. I actually expected an error message that the begin transaction was missing a rollback/commit and another error that the rollback was missing a begin.
On the other hand, this:
create table #Test (
ID int identity primary key,
Val varchar(10))
go
insert into #test (val, notacolumn)
select 'Test1', 1
go
insert into #test(val)
select 'Test2'
go
select *
from #test
Gave me the error message I expected (column "notacolumn" does not exist), but the final select showed that "Test2" was successfully inserted into the table. I expected that behavior, and it worked as expected.
So, I can't say for sure that it reduces resource use, or reduces locks. I'll have to test more for that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 12:34 am
GSquared (4/10/2008)
It should reduce lock times. Since each batch is run as a set, breaking up the batches with "go" should let each one complete without holding more resources.
In default isolation level, locks taken by inserts/updates/deletes are held until the end of the transaction, not the end of the batch. Locks taken by selects are held until the end of the statement.
When I ran that, the transaction rolled back and I ended up with 0 rows in the table. I actually expected an error message that the begin transaction was missing a rollback/commit and another error that the rollback was missing a begin
There's no syntactical requirement that they both be present in a piece of code. Syntactically, thre's nothing wrong with beginning a transaction in one stored procedure and committing it in another.
If you try to rollback/commit on a connection where there's no open transaction, then you will get an error:
Msg 3903, Level 16, State 1, Line 1
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
That's just saying that you're trying to rollback a transaction that doesn't exist
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2008 at 7:03 am
Thanks folks for your replies and feedback.
Can someone confirm whether or not adding GO statements between individual DML statements in a script helps in reducing utilization of network resources?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 11, 2008 at 7:11 am
Network resources? No effect at all.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 7:17 am
I would think sending a smaller batch to the server at one time (say a one-line DML statement) vs. a larger batch (1,000s of lines) would have an effect on the network...
Back to my original example:
UPDATE table1 SET col1 = 1;
UPDATE table1 SET col1 = 2;
...
UPDATE table1 SET col1 = n;
GO
vs.
UPDATE table1 SET col1 = 1;
GO
UPDATE table1 SET col1 = 2;
GO
...
UPDATE table1 SET col1 = n;
GO
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 11, 2008 at 7:17 am
Gail, I checked that concept out as follows (beginning and ending transactions in separate procs):
create table TranTest (
ID int identity primary key,
Val char(5))
go
create proc TranTest2
as
insert into dbo.trantest (val)
select 'test2'
commit
go
create proc TranTest1
as
begin transaction
insert into dbo.trantest (val)
select 'test1'
exec dbo.trantest2
go
dbo.trantest1
go
select *
from dbo.trantest
And got the following result:
(1 row(s) affected)
(1 row(s) affected)
Msg 266, Level 16, State 2, Procedure TranTest2, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
(2 row(s) affected)
Did I misunderstand what you wrote?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 7:28 am
Michael Earl (4/7/2008)
This is a batch separator. In some places, it will impact what your scripts will do.The semi-colon you have included is also a batch separator but is different in the impact on the script.
Semi-colons (";") are not batch separators. They are 90% non-functional statement terminators introduced as a placebo for C, C++, C#, java, etc. programmers who couldn't believe that the SQL could figure out where the statement ended without them. (except in the rare case when new commands do not make it into the SQL Keyword list like the SEND & RECEIVE mistake, in which case they actually serve as statement introducers)
In any event, they have nothing to do with Batch separation.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 11, 2008 at 7:37 am
rbarryyoung (4/11/2008)
Michael Earl (4/7/2008)
This is a batch separator. In some places, it will impact what your scripts will do.The semi-colon you have included is also a batch separator but is different in the impact on the script.
Semi-colons (";") are not batch separators. They are 90% non-functional statement terminators introduced as a placebo for C, C++, C#, java, etc. programmers who couldn't believe that the SQL could figure out where the statement ended without them. (except in the rare case when new commands do not make it into the SQL Keyword list like the SEND & RECEIVE mistake, in which case they actually serve as statement introducers)
In any event, they have nothing to do with Batch separation.
perhaps, but they are going to be REQUIRED as of SS2011. Yes - those useless placebos will be EVERYWHERE.... Apparently someone's not confident that the parser will be smart enough by then to be able to tell where discrete SQL operations start....
Now THAT inspires some confidence....:P
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 11, 2008 at 7:49 am
Matt Miller (4/11/2008)
rbarryyoung (4/11/2008)
Michael Earl (4/7/2008)
This is a batch separator. In some places, it will impact what your scripts will do.The semi-colon you have included is also a batch separator but is different in the impact on the script.
Semi-colons (";") are not batch separators. They are 90% non-functional statement terminators introduced as a placebo for C, C++, C#, java, etc. programmers who couldn't believe that the SQL could figure out where the statement ended without them. (except in the rare case when new commands do not make it into the SQL Keyword list like the SEND & RECEIVE mistake, in which case they actually serve as statement introducers)
In any event, they have nothing to do with Batch separation.
perhaps, but they are going to be REQUIRED as of SS2011. Yes - those useless placebos will be EVERYWHERE.... Apparently someone's not confident that the parser will be smart enough by then to be able to tell where discrete SQL operations start....
Now THAT inspires some confidence....:P
Per MSDN (http://msdn2.microsoft.com/en-us/library/ms143729(SQL.100).aspx), this will be in "a future version of SQL Server". Doesn't specify which one. Specifically says, "The following SQL Server Database Engine features are supported in the next version of SQL Server, but will be removed in a later version. The specific version of SQL Server has not been determined. ... Not ending Transact-SQL statements with a semicolon."
Or do you have data that contradicts that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 7:53 am
GSquared (4/11/2008)
Matt Miller (4/11/2008)
rbarryyoung (4/11/2008)
Michael Earl (4/7/2008)
This is a batch separator. In some places, it will impact what your scripts will do.The semi-colon you have included is also a batch separator but is different in the impact on the script.
Semi-colons (";") are not batch separators. They are 90% non-functional statement terminators introduced as a placebo for C, C++, C#, java, etc. programmers who couldn't believe that the SQL could figure out where the statement ended without them. (except in the rare case when new commands do not make it into the SQL Keyword list like the SEND & RECEIVE mistake, in which case they actually serve as statement introducers)
In any event, they have nothing to do with Batch separation.
perhaps, but they are going to be REQUIRED as of SS2011. Yes - those useless placebos will be EVERYWHERE.... Apparently someone's not confident that the parser will be smart enough by then to be able to tell where discrete SQL operations start....
Now THAT inspires some confidence....:P
Per MSDN (http://msdn2.microsoft.com/en-us/library/ms143729(SQL.100).aspx), this will be in "a future version of SQL Server". Doesn't specify which one. Specifically says, "The following SQL Server Database Engine features are supported in the next version of SQL Server, but will be removed in a later version. The specific version of SQL Server has not been determined. ... Not ending Transact-SQL statements with a semicolon."
Or do you have data that contradicts that?
No - that's about it. Just wanted to point out they're going to become increasingly important, eventually to be required.... The SS2011 was a bit tongue in cheek (but that didn't translate real well...)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 11, 2008 at 7:57 am
Interesting. Ok, will take that back. There's nothing wrong with having begin tran and commit in different batches though. This does work without error
BEGIN TRAN
GO
WAITFOR delay '00:00:10'
GO
COMMIT TRAN
Looks like SQL throws an error if a procedure exits with a different trancount than it started. Doesn't stop execution however.
create table TranTest (
ID int identity primary key,
Val char(5))
GO
create proc TranTest2
AS
SELECT @@TRANCOUNT AS InTranTest2
insert into dbo.trantest (val)
select 'test2'
COMMIT transaction
go
create proc TranTest1
as
begin transaction
insert into dbo.trantest (val)
select 'test1'
SELECT @@TRANCOUNT AS BeforeTranTest2
exec dbo.trantest2
SELECT @@TRANCOUNT AS AfterTranTest2 -- this does run
GO
EXEC dbo.trantest1
SELECT @@TRANCOUNT AS AfterTranTest1
GO
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2008 at 8:11 am
Yeah. I had found the thing with begin and commit/rollback in different procs, and had assumed that batches would work the same way. Found out I was wrong (which is why I said that in my first post in this thread).
I knew, for example, that variables declared before a "go" couldn't be used after it. Temp tables, of course, can be used so long as it's the same connection. I thought transactions would be on a per-batch basis, but was definitely wrong about that. (That's why I like this site. I keep finding stuff I'm wrong about, which helps me learn, and stuff I'm right about, which helps reinforce good habits.)
Seems like transactions are per connection and per proc/script, while variables are per batch, and temp tables are per connection. I wonder what else has sensitivity that way? Or in some other way I haven't tested yet?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 12:12 pm
Matt Miller (4/11/2008)
perhaps, but they are going to be REQUIRED as of SS2011. Yes - those useless placebos will be EVERYWHERE.... Apparently someone's not confident that the parser will be smart enough by then to be able to tell where discrete SQL operations start....Now THAT inspires some confidence....:P
Depressing, but I have no doubt that you are right. Something that I observed almost 20 years ago:
Q: Why do all commercial languages look more like "C" every year?
A: Because all commercial language compilers are written by C/C++ developers who believe that all languages would be better if only they could be more like C/C++, in stark contrast to the view of almost all of the professional users of those languages.
Still waiting for semi-colons in VB...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply