| Author |
Topic |
|
funketekun
Constraint Violating Yak Guru
491 Posts |
 Posted - 2007-09-27 : 00:06:59
|
| which one is better?we are currently using DTS to extract data from 50 tables. Some with condtions, some without condtions from AS400. I'm planning to use linked servers instead. Any comments?LS = faster and simple?=============================http://www.sqlserverstudy.com |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-09-27 : 03:53:00
|
| If you are going to query these tables frequently, it is better to extract data and keep them locally rather than querying linked servers.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2007-09-27 : 09:03:26
|
| Yeah, we do that with DTS. Extract all the records from 50 tables and put it into a DB on SS.I want to use Linked Servers to do this intead of DTS. Whgat do you think?=============================http://www.sqlserverstudy.com |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-09-27 : 11:09:48
|
| Depends on the amount of data, what the dts package is actually doing and where it is putting the data.If it's doing a fast insert than that will be faster than using the linked server (tr log writes).It takes a lot of effort to load a dts package though - especially if it's saved in msdb - so if there is a small amount of data to be transferred it's faster to use the linked server.I often do everything via linked server then move the big tables to dts packages (which would be auto generated rather than created manually.Similar thing applies to SSIS but theer is more overhead in loading the packages but you can often do more in them and raw files can also help.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2007-10-29 : 15:03:16
|
| I used the same DTS package code on the SP. Why is it taking forever to run?I have 2 columns year, moth . Trying to get data less greater than 4 months ago. My code is like this.and YR = YEAR(DATEDIFF(DAY, 0, GETDATE()))and month > DATEADD(MONTH, -4, DATEDIFF(DAYhttp://www.sqlserverstudy.com |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-10-29 : 15:11:56
|
| DTS is almost always going to be your fastest option. why do u think there is an advantage to directly querying via linked server?anyway, how often are the dts pkgs running?what is network speed?what driver are u using to connect to AS400?what is total size of data moved?are u explicitly using transactions?As Harsh said, faster to keep locally. Especially on disparate OS, db platform |
|
|
|
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2007-10-29 : 15:28:38
|
| russell, DTS is almost always going to be your fastest option. why do u think there is an advantage to directly querying via linked server?I'm trying to build a Datawarehouse. Currently we are using DTS to extract data every night. I test a Linked server to extract 2 million rows, it was way faster than DTS copy objects for 25 minutes. That's why I think LS are faster.how often are the dts pkgs running?dailywhat is network speed?no sure. I think 100mbpswhat driver are u using to connect to AS400? iseries odbc driver (Does it make any difference?)what is total size of data moved?It varies (50 tables)are u explicitly using transactions?I'm using explicit transactions. http://www.sqlserverstudy.com |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-10-29 : 22:02:40
|
| driver matters but u are using right one -- unless there is an iseries oledb driver available (which is what i used and was notably faster, and less problems than odbc).which were u saying was taking forever in your above post? for me, dts is significantly faster.i cant think of too many reasons to use explicit transactions in a one-way pump though. do u need them? that will slow things down quite a bit. if u dont need them, try doing away with the transactions.also, on sql server, what recovery model is your target db in? if simple, dts has to be faster... |
|
|
|
gongxia649
So Suave
344 Posts |
Posted - 2007-10-30 : 00:01:00
|
| Russel,Some dts converted to linked server takes forever. But Link Served is faster for other tables. I dont know why is like this.It's in sinple recovery model as we dont need to restore using the logI use explicit transaction because I want to put the rowcount into a history table. If it fails I won't insert on the history table, if it succeeds it will insert the rowcount. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-10-30 : 00:19:50
|
| i dont know why either without seeing it up close :)but in my case had best success with dts. reason i mention recovery mode is that in simple, dts pump is not a fully logged operation, which should speed things up.one way to drastically slow down a dts package is to run it from a machine other than the target of the data pump, reason being that dts is a client application, so all data would go from source to machine running the dts/job to the target. if u are doing this, that would probably explain why some are faster using linked server.in general, i would answer your original question with "dts" but if u are having better performance with direct queries against linked server, then that is probably way to go. |
|
|
|
|
dts vs linked server