March 21, 2012 at 10:24 am
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
March 21, 2012 at 10:28 am
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
March 21, 2012 at 10:34 am
I'll second that but add:
It also depends a lot on:
Data types
indexes
disc config
disc speeds
and probably more...
Mike
March 21, 2012 at 10:41 am
Thanks guys...
Thanks,
Charmer
March 21, 2012 at 10:42 am
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
March 21, 2012 at 11:48 am
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
March 21, 2012 at 12:01 pm
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
March 21, 2012 at 12:08 pm
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.
March 21, 2012 at 8:32 pm
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
March 22, 2012 at 1:07 am
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
March 22, 2012 at 3:15 am
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
March 22, 2012 at 9:51 am
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
March 22, 2012 at 9:56 am
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
March 22, 2012 at 10:04 am
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.
March 22, 2012 at 12:00 pm
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