which is faster? SSIS or SQL?

  • Hi friends,

    i try to run some T-SQL statements in ssms like insert and update left outer join with 5 tables...due to large records it takes too much time..

    so if SSIS does the same task with less time , i would go for it...

    Please i need your suggestions...

    Thanks,
    Charmer

  • My experience has shown that TSQL is faster for that. It largely depends on how you write your queries.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I'll second that but add:

    It also depends a lot on:

    Data types

    indexes

    disc config

    disc speeds

    and probably more...

    Mike

  • Thanks guys...

    Thanks,
    Charmer

  • Charmer (3/21/2012)


    Hi friends,

    i try to run some T-SQL statements in ssms like insert and update left outer join with 5 tables...due to large records it takes too much time..

    so if SSIS does the same task with less time , i would go for it...

    Please i need your suggestions...

    Why would you think SSIS would be faster with simple SQL statements like insert and updates? It still has to execute those commands on the database engine.

    Jared
    CE - Microsoft

  • SSIS can be faster at certain actions inside SQL Server. For example, I have a process that copies data from one server to another on a scheduled basis. Replication won't do what I need, so I tested T-SQL and SSIS. SSIS is MUCH faster, because of avoiding the DTC processes between servers. But that's an unusual case, quite rare.

    Most of the time, T-SQL will be faster than SSIS on operations inside the SQL Server space. Where it really has it's utility is moving data between engines (text to database, or database to Excel, or XML to Oracle, and so on). And there, it's not the speed that makes it better, it's the more robust error-handling and split data flows, and all that kind of thing.

    - 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

  • GSquared (3/21/2012)


    SSIS can be faster at certain actions inside SQL Server. For example, I have a process that copies data from one server to another on a scheduled basis. Replication won't do what I need, so I tested T-SQL and SSIS. SSIS is MUCH faster, because of avoiding the DTC processes between servers. But that's an unusual case, quite rare.

    Most of the time, T-SQL will be faster than SSIS on operations inside the SQL Server space. Where it really has it's utility is moving data between engines (text to database, or database to Excel, or XML to Oracle, and so on). And there, it's not the speed that makes it better, it's the more robust error-handling and split data flows, and all that kind of thing.

    I agree, but if these are simple SQL tasks as the OP indicates (just insert, update, and join with no imports or conversions) I pose the question to the OP again... Why would you think SSIS to be faster? I think you can answer your own question if you just take a step back and answer my question 🙂

    Jared
    CE - Microsoft

  • Charmer (3/21/2012)


    Hi friends,

    i try to run some T-SQL statements in ssms like insert and update left outer join with 5 tables...due to large records it takes too much time..

    so if SSIS does the same task with less time , i would go for it...

    Please i need your suggestions...

    i think the real question here is what are you trying to accomplish? Are you moving data from one server to another, between databases on the same server?

    The question is rather broad and without further input and clarification we really can't give you a really clear answer.

  • I agree with Lyn on this one.

    Need more information as to what you are trying to achieve. Define a long time too please... it's all rather broad.

    SSIS is a toolset mainly designed around external data sets (like CSVs for example) that need to be loaded into SQL.

    T-SQL is the native language for Microsoft SQL Server.

    Some things to think about that affect inserts/updates...

    a) Transaction logging mode..........

    b) Single user mode.......

    Cheers

    Michael

  • Charmer (3/21/2012)


    Hi friends,

    i try to run some T-SQL statements in ssms like insert and update left outer join with 5 tables...due to large records it takes too much time..

    so if SSIS does the same task with less time , i would go for it...

    Please i need your suggestions...

    SSIS will take longer if you just put those SQL statements in an Execute SQL task because SSIS just sends those statements to the database engine (so same processing time) + SSIS has additional overhead.

    Inserts can be faster as SSIS uses bulk insert when the fast load option is set on the OLE DB Destination, but there are methods in SQL Server as well to do bulk inserts.

    Updates are never faster in SSIS. There's no way to do them fast natively in SSIS as the OLE DB Command is too painfully slow. So you end up writing the data to a staging table and do a set based update with TSQL. Which is just the same is doing the update in the database anyway.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Hi To all,

    What i am exactly doing is just inserting and updating records left join with 4 to 6 tables.....

    i even created indexes for the columns which ever i am handling to increase the speed.....

    i deal with 6 to 7 million records...fortunately, it takes time to execute on my local machine around 2 to 3 hours...

    the same T-SQL statements takes more than 7 hours on the client server and even it is taking hours and hours to run the scripts for just 430,000 records....

    I have no idea about why the client server is very slow.....

    This is one of the sample script that i use....I don't know how to make more efficient script instead of this.....

    Give me your ideas please...

    with t as

    (

    SELECT --COUNT(*)

    j.NameIDRef

    --, 0 AS SEQNUM

    , ROW_NUMBER() over (partition by Juris.PnxCodeValue, j.NameIDRef order by galcd+galct desc) -1 seqnum

    , ADR.GASTR# AS PREMISE

    , A.PnxCodeValue AS PREFIX

    , ADR.GASNAM AS STREETNAME

    , B.PnxCodeValue as STREETTYPE

    , ADR.GASQLF AS APTNO

    , CASE

    WHEN AJADR# = 0 then 'No Address, ID = 0'

    when AJADR# > 0 and isnull((Select distinct cast(GAADR# as varchar) from PSGADR where GAADR# = j.AJADR#), 'nm')='nm' then 'Master Address ID: ' + CAST(AJADR# AS varchar) + ' does not exist'

    ELSE adr.location

    END AS LOCATION

    , J.AJCITY AS CITY

    , C.PnxCodeValue AS STATE

    , J.AJZIP AS ZIP

    , ADR.GALCU AS MODIFIEDPFIDREF

    , adr.modifieddttm AS MODIFIEDDTTM

    , Juris.PnxCodeValue as JurisID

    From GloucMidPolice.dbo.SrcNameDtl ND

    join SrcCodeMap Juris

    on nd.JurisID = Juris.PnxCodeValue and Juris.CodeID = 100

    JOIN PSAJCK AS J

    on j.NameIDRef = nd.NameIDRef and AJORI# = Juris.SrcCodeValue

    left outer join PSGADR AS ADR

    ON J.AJADR# = ADR.GAADR#

    Left outer Join SrcCodeMap A on A.SrcCodeValueRef = adr.GASDIR and A.CodeID = 2071 -- Prefix

    Left outer Join SrcCodeMap B on B.SrcCodeValueRef = adr.GASSUF and B.CodeID = 6001 -- StreetType

    Left outer Join SrcCodeMap C on C.SrcCodeValueRef = j.AJSTAT and C.CodeID = 2009 -- state

    )

    insert into GloucMidPolice.dbo.srcnameaddr

    (

    nameidref

    , seqno

    , Premise

    , PREFIX

    , streetname

    , streettype

    , aptno

    , location

    , city

    , state

    , zip

    , modifiedpfidref

    , modifieddttm

    , JurisID

    )

    SELECT

    t.nameidref

    , seqnum

    , ltrim(rtrim(PREMISE))

    , ltrim(rtrim(PREFIX))

    , STREETNAME

    , ltrim(rtrim(stREETTYPE))

    , APTNO

    , t.LOCATION

    , CITY

    , left(ltrim(rtrim(STATE)),2)

    , ltrim(rtrim(ZIP))

    , t.MODIFIEDPFIDREF

    , t.MODIFIEDDTTM

    , t.JurisID

    FROM t

    JOIN GloucMidPolice.dbo.SrcNameDtl snd

    on t.nameidref = snd.NameIDRef AND t.JurisID = snd.JurisID

    where t.seqnum = 0

    Thanks,
    Charmer

  • SQLKnowItAll (3/21/2012)


    Charmer (3/21/2012)


    Hi friends,

    i try to run some T-SQL statements in ssms like insert and update left outer join with 5 tables...due to large records it takes too much time..

    so if SSIS does the same task with less time , i would go for it...

    Please i need your suggestions...

    Why would you think SSIS would be faster with simple SQL statements like insert and updates? It still has to execute those commands on the database engine.

    Hi, Please look at my last post...that is what i really need to do...

    Thanks,
    Charmer

  • Without DDL, (including all indexes) and some actual execution plans we are all flying blind, it is almopst impossible o make any suggestions.

    Can you try running the select separately from the insert, to at least pin down which is costing you the most.

    Then post the execution plan, and DDL

    Thanks

    Mike

  • CASE

    WHEN AJADR# = 0 then 'No Address, ID = 0'

    when AJADR# > 0 and isnull((Select distinct cast(GAADR# as varchar) from PSGADR where GAADR# = j.AJADR#), 'nm')='nm' then 'Master Address ID: ' + CAST(AJADR# AS varchar) + ' does not exist'

    ELSE adr.location

    END AS LOCATION

    I'd focus on this within your select list. A correlated sub-query in the select can be horrible for performance and scale very poorly with an increased number of records.

    You can easily re-write something like this as a sub query and join it to the resultset.

  • Charmer (3/22/2012)


    SQLKnowItAll (3/21/2012)


    Charmer (3/21/2012)


    Hi friends,

    i try to run some T-SQL statements in ssms like insert and update left outer join with 5 tables...due to large records it takes too much time..

    so if SSIS does the same task with less time , i would go for it...

    Please i need your suggestions...

    Why would you think SSIS would be faster with simple SQL statements like insert and updates? It still has to execute those commands on the database engine.

    Hi, Please look at my last post...that is what i really need to do...

    One thing I noticed in the code, the haphazard use of table aliases on the columns in the select statement. Makes it hard to know which table a column is from and could cause a problem if a column with the same name is added to another table used in the query.

    Really is a good idea to use the table alias on all columns, not just those that are ambiguous at the time you write the query. In fact, in production code I even use table aliases in single table queries. You never know when you may have to add a second table to the query to meet a change in requirements.

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply