June 23, 2011 at 7:08 am
Is it possible to issue multiple DML statements via one EXEC command?
I have a linked server (Microsoft OLE DB for ODBC Provider) that I can only access using the exec() command (otherwise the query hangs and no records are ever returned). When I need to insert 10 rows into a table in the remote database, I'm having to use 10 exec() commands, which takes more time than it should. I don't think the inserts themselves have a lot of overhead. I think it's that the connection is continuously opened and closed.
For example, here's what my query window would look like:
EXEC('INSERT INTO Table (Name) VALUES ('Mike')') AT RemoteServer
EXEC('INSERT INTO Table (Name) VALUES ('Bob')') AT RemoteServer
EXEC('INSERT INTO Table (Name) VALUES ('Patty')') AT RemoteServer
EXEC('INSERT INTO Table (Name) VALUES ('Nicole')') AT RemoteServer
.
.
.
I've written a while loop to dynamically build these statements and run them, but again, it takes a long time. Oddly enough, if I create an insert/update query in access and run it, it goes fairly quickly, but I thought since SQL Server was essentially using the same ODBC driver that I should get comparable speeds. Is there anything I can do with the EXEC() command, or is there another query method that allows DML statements and might be quicker?
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
June 23, 2011 at 7:16 am
Why don't you create a Stored Procedure and put all your logic that you are executing with EXEC(), and finally call that SP.
June 23, 2011 at 7:21 am
What do you mean? My concern is the opening and closing of so many connections and how it's causing query performance degradation and unnecessary load on the server. Would simply putting the logic in a stored procedure address that?
Mike Scalise, PMP
https://www.michaelscalise.com
June 23, 2011 at 7:38 am
yes, separate the statements with semi-colon:
EXEC('INSERT INTO Table (Name) VALUES ('Mike'); INSERT INTO Table (Name) VALUES ('Bob')') AT RemoteServer
The probability of survival is inversely proportional to the angle of arrival.
June 23, 2011 at 7:42 am
Since it sounds like you have a lot of data to insert you might find it faster to do you insert as a single command like this.
EXEC('INSERT INTO Table (Name)
select ''Mike'' union all
select ''Bob'' union all
select ''Patty'' union all
select ''Nicole''')
or use the 2008 syntax:
EXEC('INSERT INTO Table (Name)
values (''Mike''),
(''Bob''),
(''Patty''),
(''Nicole'')')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 23, 2011 at 8:40 am
Thanks for the info. Here are the results of my test. Method 2 looks most promising, but I'm not sure why it only inserted the one row.
--Method 1 (10 rows inserted in 1 min, 32 secs)
EXEC('INSERT INTO VISITOR_POSITIONS (ID, VIPO_DESC) VALUES (1001, ''Test1'')') AT RemoteServer
EXEC('INSERT INTO VISITOR_POSITIONS (ID, VIPO_DESC) VALUES (1002, ''Test2'')') AT RemoteServer
EXEC('INSERT INTO VISITOR_POSITIONS (ID, VIPO_DESC) VALUES (1003, ''Test3'')') AT RemoteServer
EXEC('INSERT INTO VISITOR_POSITIONS (ID, VIPO_DESC) VALUES (1004, ''Test4'')') AT RemoteServer
EXEC('INSERT INTO VISITOR_POSITIONS (ID, VIPO_DESC) VALUES (1005, ''Test5'')') AT RemoteServer
EXEC('INSERT INTO VISITOR_POSITIONS (ID, VIPO_DESC) VALUES (1006, ''Test6'')') AT RemoteServer
EXEC('INSERT INTO VISITOR_POSITIONS (ID, VIPO_DESC) VALUES (1007, ''Test7'')') AT RemoteServer
EXEC('INSERT INTO VISITOR_POSITIONS (ID, VIPO_DESC) VALUES (1008, ''Test8'')') AT RemoteServer
EXEC('INSERT INTO VISITOR_POSITIONS (ID, VIPO_DESC) VALUES (1009, ''Test9'')') AT RemoteServer
EXEC('INSERT INTO VISITOR_POSITIONS (ID, VIPO_DESC) VALUES (1010, ''Test10'')') AT RemoteServer
--Method 2 (1 row inserted (1001, 'Test1') in 50 secs)
EXEC('INSERT INTO VISITOR_POSITIONS (ID, VIPO_DESC) VALUES (1001, ''Test1'');
INSERT INTO VISITOR_POSITIONS (ID, VIPO_DESC) VALUES (1002, ''Test2'');
INSERT INTO VISITOR_POSITIONS (ID, VIPO_DESC) VALUES (1003, ''Test3'');
INSERT INTO VISITOR_POSITIONS (ID, VIPO_DESC) VALUES (1004, ''Test4'');
INSERT INTO VISITOR_POSITIONS (ID, VIPO_DESC) VALUES (1005, ''Test5'');
INSERT INTO VISITOR_POSITIONS (ID, VIPO_DESC) VALUES (1006, ''Test6'');
INSERT INTO VISITOR_POSITIONS (ID, VIPO_DESC) VALUES (1007, ''Test7'');
INSERT INTO VISITOR_POSITIONS (ID, VIPO_DESC) VALUES (1008, ''Test8'');
INSERT INTO VISITOR_POSITIONS (ID, VIPO_DESC) VALUES (1009, ''Test9'');
INSERT INTO VISITOR_POSITIONS (ID, VIPO_DESC) VALUES (1010, ''Test10'')') AT RemoteServer
--Method 3 (syntax error, 'Could not execute statement on remote server RemoteServer')
EXEC('INSERT INTO VISITOR_POSITIONS (ID, VIPO_DESC)
SELECT 1001, ''Test1'' UNION ALL
SELECT 1002, ''Test2'' UNION ALL
SELECT 1003, ''Test3'' UNION ALL
SELECT 1004, ''Test4'' UNION ALL
SELECT 1005, ''Test5'' UNION ALL
SELECT 1006, ''Test6'' UNION ALL
SELECT 1007, ''Test7'' UNION ALL
SELECT 1008, ''Test8'' UNION ALL
SELECT 1009, ''Test9'' UNION ALL
SELECT 1010, ''Test10''') AT RemoteServer
--Method 4 (syntax error, 'Could not execute statement on remote server RemoteServer')
EXEC('INSERT INTO VISITOR_POSITIONS (ID, VIPO_DESC) VALUES (1001, ''Test1''),
(1002, ''Test2''),
(1003, ''Test3''),
(1004, ''Test4''),
(1005, ''Test5''),
(1006, ''Test6''),
(1007, ''Test7''),
(1008, ''Test8''),
(1009, ''Test9''),
(1010, ''Test10'')') AT RemoteServer
Mike Scalise, PMP
https://www.michaelscalise.com
June 23, 2011 at 8:47 am
Not sure why method 3 did not work. Looks correct to me. Method 4 will only work if the remote server is sql 2008. From the two you have working looks like method 1 is the fastest (but 32 seconds for 10 inserts is pretty darn slow). To see if either 3 or 4 will work you could just run the contents of your exec directly on that server and see if there is some syntax issue that is hidden because of the multiple levels of ''''.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 23, 2011 at 8:48 am
You can't use CR/LF in the command line... just semi colons.
It will make a very long string, or you can build the string in a series statements like:
set @sql = @sql +';insert table values(x,y,z)'
set @sql = @sql +';insert table values(x,y,z)'
exec(@sql)
The newlines (CR/LF) in your TSQL is why method 2 or 3 is not working.
The probability of survival is inversely proportional to the angle of arrival.
June 23, 2011 at 8:53 am
sturner (6/23/2011)
You can't use CR/LF in the command line... just semi colons.
BAH! Thanks was wondering why that was an issue.
Try like this:
--method 3
EXEC('INSERT INTO VISITOR_POSITIONS (ID, VIPO_DESC) SELECT 1001, ''Test1'' UNION ALL SELECT 1002, ''Test2'' UNION ALL SELECT 1003, ''Test3'' UNION ALL SELECT 1004, ''Test4'' UNION ALL SELECT 1005, ''Test5'' UNION ALL SELECT 1006, ''Test6'' UNION ALL SELECT 1007, ''Test7'' UNION ALL SELECT 1008, ''Test8'' UNION ALL SELECT 1009, ''Test9'' UNION ALL SELECT 1010, ''Test10''') AT RemoteServer
--method 4
EXEC('INSERT INTO VISITOR_POSITIONS (ID, VIPO_DESC) VALUES (1001, ''Test1''),(1002, ''Test2''),(1003, ''Test3''),(1004, ''Test4''),(1005, ''Test5''),(1006, ''Test6''),(1007, ''Test7''),(1008, ''Test8''),(1009, ''Test9''),(1010, ''Test10'')') AT RemoteServer
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 23, 2011 at 8:55 am
Sean Lange (6/23/2011)
Not sure why method 3 did not work. Looks correct to me. Method 4 will only work if the remote server is sql 2008. From the two you have working looks like method 1 is the fastest (but 32 seconds for 10 inserts is pretty darn slow). To see if either 3 or 4 will work you could just run the contents of your exec directly on that server and see if there is some syntax issue that is hidden because of the multiple levels of ''''.
Sean, method 1 was 1 minute, 32 secs. The remote server is IBM Unidata. Files in that database have been "SQLized" and are available as tables through the ODBC driver. Therefore, I didn't really expect any SQL Server specific commands to work. However, I expected there to be a way to run multiple valid/standard SQL statements with one connection.
Mike Scalise, PMP
https://www.michaelscalise.com
June 23, 2011 at 9:01 am
mikes84 (6/23/2011)
Sean, method 1 was 1 minute, 32 secs. The remote server is IBM Unidata. Files in that database have been "SQLized" and are available as tables through the ODBC driver. Therefore, I didn't really expect any SQL Server specific commands to work. However, I expected there to be a way to run multiple valid/standard SQL statements with one connection.
Roger that. The non-SQLServer IBM DB server may not execute a bunch of semi-colon separated commands, you'll just have to try it. In any case I'm not familiar with that animal.
The probability of survival is inversely proportional to the angle of arrival.
June 23, 2011 at 9:02 am
sturner (6/23/2011)
You can't use CR/LF in the command line... just semi colons.It will make a very long string, or you can build the string in a series statements like:
set @sql = @sql +';insert table values(x,y,z)'
set @sql = @sql +';insert table values(x,y,z)'
exec(@sql)
The newlines (CR/LF) in your TSQL is why method 2 or 3 is not working.
The new method 2 (without any CR/LF, only semicolons) resulted in:
(1 row inserted (1001, 'Test1') in 2 secs)
So, even though only 1 row was inserted, removing the spaces shaved 48 seconds off of the time. Is there any other way to delimit SQL statements?
Mike Scalise, PMP
https://www.michaelscalise.com
June 23, 2011 at 9:03 am
Also, methods 3 and 4 returned the same error as before.
Mike Scalise, PMP
https://www.michaelscalise.com
June 23, 2011 at 9:07 am
EEEWWWWW!!!!! I missed the 1 minute part and only 32 seconds. As i was writing this you posted that neither 3 or 4 worked on a single line. Not sure what else you can do to speed it up. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 23, 2011 at 9:09 am
How many actual rows do you need to insert?
If you are talking about hundreds... or more than perhaps there is a bulk insert equivalent for that server that will be faster than a bunch of successive EXEC() commands. I would read the manual on that IBM database and see if there is another command separator that it will accept. (I find it hard to believe it would not have such functionality).
Failing that, I'd do the inserts to a local table then try to insert the whole table via the remote server connection, if that is even possible.
You are right to want to avoid the use of multiple separate batches to the remote machine if you are concerned with efficiency (or transactional consistancy).
The probability of survival is inversely proportional to the angle of arrival.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply