Wednesday, September 10, 2014

Everything must "GO"

Use "GO" command daily uncounted number of times, but rarely with counter.

It is very useful feature when you want to run certain part of your code more than once.
Usually I fill out a table by some random values like this:

INSERT INTO tbl_Test_Load(ID, A)
SELECT NEWID(), REPLICATE('A',10);
GO 10

That code is supposed to fill out the table by 10 rows, because the statement before "GO" will be executed 10 times. Exactly as specified.

If I try to truncate table before its loading it will screw up everything:

TRUNCATE TABLE tbl_Test_Load;
INSERT INTO tbl_Test_Load(ID, A)
SELECT NEWID(), REPLICATE('A',10);
GO 10

That statement willbe also executed 10 times, but as a result we will have only one row int the test table because truncate statement will be interpreted as the part of batch and also will be executed 10 times.
In order to avoid that confusion we have to add extra "GO" between truncate and insert statements.

When I tried to insert more values than just 10-1000 records  I hit a performance issue.
Here is the test script:


USE tempdb
GO
SET NOCOUNT ON
GO
CREATE TABLE tbl_Test_Load(
       ID UNIQUEIDENTIFIER,
       A CHAR(10)
);
GO
PRINT '"GO" loop begins: ' + RIGHT(CONVERT(VARCHAR(30), GETDATE(), 121),12);
GO
INSERT INTO tbl_Test_Load(ID, A)
SELECT NEWID(), REPLICATE('A',10);
GO 100000
PRINT '"GO" loop Ends: ' + RIGHT(CONVERT(VARCHAR(30), GETDATE(), 121),12);

TRUNCATE TABLE tbl_Test_Load;

DECLARE @i INT = 100000;

PRINT '"While" loop begins: ' + RIGHT(CONVERT(VARCHAR(30), GETDATE(), 121),12);
WHILE @i > 0
BEGIN
  INSERT INTO tbl_Test_Load(ID, A)
  SELECT NEWID(), REPLICATE('A',10); 
  SET @i -= 1;
END
PRINT '"While" loop ends: ' + RIGHT(CONVERT(VARCHAR(30), GETDATE(), 121),12);
GO
DROP TABLE tbl_Test_Load;
GO



And here is the result of that script:
"GO" loop begins: 10:44:55.717
Beginning execution loop
Batch execution completed 100000 times.
"GO" loop Ends: 10:46:25.887
"While" loop begins: 10:46:25.887
"While" loop ends: 10:46:27.733


One and a half minute for "GO" loop and only two seconds for "WHILE" loop.
I was little bit surprised by that result.

That required an explanation. I looked in MSDN:

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO. 

GO is a utility command that requires no permissions. It can be executed by any user.

That explanation makes it clear. While "WHILE" loop is sent to a server and totally executed by the SQL Server engine "GO" loop is executed by a utility, in my case SSMS. Instead of one complex loop batch SSMS sent 10000 simple batches to SQL Server which is obviously slower, including handling the loop by SSMS.

Nice to know.



No comments:

Post a Comment