Whats quicker; SQL or SSIS?

Hi all,

I'm a bit short of time for a project so can't really do both and compare (but will at some point), so therefore;

What is quicker; an SQL statement or using the objects in SSIS?
i.e. If I can do a lookup by joined table or sub-query, is that quicker than the lookup object in SSIS?
Same goes for data conversions/cast statement, derived columns, the union object etc.  

I'd assume they're slower which, if they are slower, then leads me to ask, are they only there for people that don't know SQL?

Regards,
Kinton
LVL 2
kintonAsked:
Who is Participating?
 
dportasConnect With a Mentor Commented:
I think this is a false dilemma. T-SQL and SSIS achieve very different things and there isn't really much overlap.

If you want to integrate and transform data from heterogeneous data sources then SSIS offers vastly more than T-SQL does. I know you can achieve some things using linked servers but for most purposes you are limited to SQL-DBMS and a few other providers. Performing lookups and joins across different systems using linked servers isn't remotely feasible in many cases. Querying file data sources using T-SQL is only possible using a few very simple file formats.

On the other hand if your data is already on a single SQL Server and you want it to stay there then T-SQL should give you everything you need. In the majority of cases the SQL-DBMS (T-SQL or SQL-CLR) will be the most efficient engine for processing the data because other methods require the data to be fetched and then resent over some external protocol (OLEDB or ODBC).

There are potential exceptions around very large aggregations and lookups that SSIS can achieve in memory but might otherwise hit TempDB using purely SQL. These are exceptions though. In most cases I would assume T-SQL to be most efficient unless you identify a specific performance bottleneck.






0
 
bradleys40Commented:
I dont quite understand you thinking
ssis is a replacement for DTS
0
 
kintonAuthor Commented:
All of the data is on the one box so that answers my question perfectly thanks dportas.

Regards,
Kinton
0
All Courses

From novice to tech pro — start learning today.