What is more fast: ssis or database engine?
Question
Answers
-
I think database engine is faster than ssis.
http://www.sqlservercentral.com/blogs/jamesserra/2011/08/29/when-to-use-t_2D00_sql-or-ssis-for-etl/
Hi neonash,
I unmarked your response as answer, since this is incorrect answer to your original question. I do understand what you mean now, but what you wrote is not correct. SSIS can not be faster than database engine for the insert statement itself, since SSIS use the database engine. But for preparing the data SSIS might do much better (same if you know how to work with SQLCLR). Please read all the responses that you got :-) you cannot compare SSIS (which is external tool to the SQL Server) to database engine (which is part of the SQL Server itself).
I think that now it is clear that you intended to ask something like: What is more fast: using SSIS or using Transact-SQL query (that we wrote)? Please remember that we have another common solution and it is using SQLCLR.
* SSIS work with T-SQL queries as well, if you use it to insert/update data on the SQL Server. Therefore comparing SSIS to T-SQL is not a good question in my opinion, as well, but this is legitimate question like asking if SQLCLR is faster than T-SQL :-)
The answer to this question is that it depend on your personal abilities to develop the best execution plan (i am not talking about the SQL Server Query Execution Plan, but your planing of how to execute our queries), and your abilities to write the best query/solution (for example using SQLCLR can help you prepare the data for the insert, fast as the SSIS do probably), and more parameters. SSIS write T-SQL queries behind the screen, but can also make other actions that in T-SQL might be slow, in faster way (using other languages), eventually the SQL Server will execute those queries, as well. Therefore, theoretical you always have the option to write the same queries (or better) using T-SQL / SQLCLR and execute them in the same option (one query for all the action, or several that executes parallel, using only T-SQL or combine with CLR, etc') by yourself.
* SSIS is a great tool that give you fast and simple solution, without the need of writing the query yourself.
Ronen Ariely
[Personal Site] [Blog] [Facebook]
- Edited by pituachMVP Saturday, February 14, 2015 11:08 AM
- Proposed as answer by Satish Kartan, MCITP Saturday, February 14, 2015 2:58 PM
- Marked as answer by Lydia ZhangMicrosoft contingent staff Tuesday, February 17, 2015 9:39 AM
All replies
-
I think database engine is faster than ssis.
http://www.sqlservercentral.com/blogs/jamesserra/2011/08/29/when-to-use-t_2D00_sql-or-ssis-for-etl/
Hi neonash,
I unmarked your response as answer, since this is incorrect answer to your original question. I do understand what you mean now, but what you wrote is not correct. SSIS can not be faster than database engine for the insert statement itself, since SSIS use the database engine. But for preparing the data SSIS might do much better (same if you know how to work with SQLCLR). Please read all the responses that you got :-) you cannot compare SSIS (which is external tool to the SQL Server) to database engine (which is part of the SQL Server itself).
I think that now it is clear that you intended to ask something like: What is more fast: using SSIS or using Transact-SQL query (that we wrote)? Please remember that we have another common solution and it is using SQLCLR.
* SSIS work with T-SQL queries as well, if you use it to insert/update data on the SQL Server. Therefore comparing SSIS to T-SQL is not a good question in my opinion, as well, but this is legitimate question like asking if SQLCLR is faster than T-SQL :-)
The answer to this question is that it depend on your personal abilities to develop the best execution plan (i am not talking about the SQL Server Query Execution Plan, but your planing of how to execute our queries), and your abilities to write the best query/solution (for example using SQLCLR can help you prepare the data for the insert, fast as the SSIS do probably), and more parameters. SSIS write T-SQL queries behind the screen, but can also make other actions that in T-SQL might be slow, in faster way (using other languages), eventually the SQL Server will execute those queries, as well. Therefore, theoretical you always have the option to write the same queries (or better) using T-SQL / SQLCLR and execute them in the same option (one query for all the action, or several that executes parallel, using only T-SQL or combine with CLR, etc') by yourself.
* SSIS is a great tool that give you fast and simple solution, without the need of writing the query yourself.
Ronen Ariely
[Personal Site] [Blog] [Facebook]
- Edited by pituachMVP Saturday, February 14, 2015 11:08 AM
- Proposed as answer by Satish Kartan, MCITP Saturday, February 14, 2015 2:58 PM
- Marked as answer by Lydia ZhangMicrosoft contingent staff Tuesday, February 17, 2015 9:39 AM
-
Hi
If compare database engine and ssis in for example migrate from sqlserver to sqlserver:
engine db is faster than ssis, because ssis use t-sql.
More details..
http://www.sqlservercentral.com/blogs/jamesserra/2011/08/29/when-to-use-t_2D00_sql-or-ssis-for-etl/
Did you read anything that we wrote to you?!?
You continue to compare "cars" to "drivers", "TV" and "electricity"! The drivers use the car, the TV use the electricity, the SSIS use the database engine... how can you compare them?!? Please read what people wrote to you. We are trying to help you understand :-)
* I recommend you to understand what is SSIS and what is database engine before you start to "compare" them. The link that you bring AGAIN have not even mention database engine! In any case your conclusion is not correct even if you meant to compare "using ONLY PURE T-SQL" to "using the SSIS" (which use T-SQL and more), since this is depending and in some cases SSIS will be faster and some cases using your own (at least my own) query will be faster!
Please read this: SQL Server Database Engine
The Database Engine is the core service for storing, processing, and securing data.
Ronen Ariely
[Personal Site] [Blog] [Facebook]
- Edited by pituachMVP Sunday, February 15, 2015 12:59 AM
