 | |
Re: DTS lookups and connections
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: DTS lookups and connections -
08-11-2004
, 03:43 PM
|
i've been usein DTS for transfecopying data from location to another lately i use AX script withinn lookups to replicate a database to another one and while watching the DTS working in the SQl Profiler I noticed that for each lookup in a row a connection is i\opend to get the lookup then closed once for each row of data ! this costs my application a lot of time my package has 3 connections a source , destnation and one for lookup |
#2
| |||
| |||
|
|
That's the way lookups work. Row * Row Can you not rewrite this using TSQL and a join statement? -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. www.konesans.com - Consultancy from the people who know "Eisa" <Eisa (AT) discussions (DOT) microsoft.com> wrote in message news:F88930EC-CA0E-4047-B5D6-4C96736704D3 (AT) microsoft (DOT) com... i've been usein DTS for transfecopying data from location to another lately i use AX script withinn lookups to replicate a database to another one and while watching the DTS working in the SQl Profiler I noticed that for each lookup in a row a connection is i\opend to get the lookup then closed once for each row of data ! this costs my application a lot of time my package has 3 connections a source , destnation and one for lookup |
#3
| |||
| |||
|
|
I ALREADY KNOW THAT WHAT I'M ASKIN ABOUT IS WHY A CONNECTION IS OPEND AND CLOSED FOR EVERY LOOKUP THER PROFILER DISPLAYES THAT AUDIT LOGIN EXEC ..... AUDIT LOGOUT AUDIT LOGIN EXEC ..... AUDIT LOGOUT I HAVE MORE THAN ONE LOOKUP FOR EACH ROW IS IT GOOD OR BAD FOR PERFORMANCE TO OPEN AND CLOSE THET CONNECTION AND HOW 2 PREVENT THANX "Allan Mitchell" wrote: That's the way lookups work. Row * Row Can you not rewrite this using TSQL and a join statement? -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. www.konesans.com - Consultancy from the people who know "Eisa" <Eisa (AT) discussions (DOT) microsoft.com> wrote in message news:F88930EC-CA0E-4047-B5D6-4C96736704D3 (AT) microsoft (DOT) com... i've been usein DTS for transfecopying data from location to another lately i use AX script withinn lookups to replicate a database to another one and while watching the DTS working in the SQl Profiler I noticed that for each lookup in a row a connection is i\opend to get the lookup then closed once for each row of data ! this costs my application a lot of time my package has 3 connections a source , destnation and one for lookup |
#4
| |||
| |||
|
|
Mr Allan Mitchell first thanx for replying my english is not so good as it is not my native toungue writing in CAPs was a hardware error  about my db i have so many tables that have forign keys from many other tables and the database in the destination location has ID's that I have 2 look up 1 per row sometimes 3 or 4 it may be a bad design for transforming with DTS and using look ups but it's been normalized well another time thanx for Ur time "Allan Mitchell" wrote: From the tone of the mail it looks as though you are deliberately trying to not get help. Having >1 lookup per row is disastrous for performance. You cannot stop the connection opening and closing. -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. www.konesans.com - Consultancy from the people who know "Eisa" <Eisa (AT) discussions (DOT) microsoft.com> wrote in message news:131BFBCA-A7BB-4A39-BF72-A67A306DF6C4 (AT) microsoft (DOT) com... I ALREADY KNOW THAT WHAT I'M ASKIN ABOUT IS WHY A CONNECTION IS OPEND AND CLOSED FOR EVERY LOOKUP THER PROFILER DISPLAYES THAT AUDIT LOGIN EXEC ..... AUDIT LOGOUT AUDIT LOGIN EXEC ..... AUDIT LOGOUT I HAVE MORE THAN ONE LOOKUP FOR EACH ROW IS IT GOOD OR BAD FOR PERFORMANCE TO OPEN AND CLOSE THET CONNECTION AND HOW 2 PREVENT THANX "Allan Mitchell" wrote: That's the way lookups work. Row * Row Can you not rewrite this using TSQL and a join statement? -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. www.konesans.com - Consultancy from the people who know "Eisa" <Eisa (AT) discussions (DOT) microsoft.com> wrote in message news:F88930EC-CA0E-4047-B5D6-4C96736704D3 (AT) microsoft (DOT) com... i've been usein DTS for transfecopying data from location to another lately i use AX script withinn lookups to replicate a database to another one and while watching the DTS working in the SQl Profiler I noticed that for each lookup in a row a connection is i\opend to get the lookup then closed once for each row of data ! this costs my application a lot of time my package has 3 connections a source , destnation and one for lookup |
#5
| |||
| |||
|
|
Are you then taking over the whole DB. If you are and they are the same vendor then look at BACKUP/RESTORE. If not then you will have to import in the order the tables are related through DRI. Some suggest turning off the constrints , pmping in the data and then adding back the constraints. Personally I do not like this way as data integrity could be shot. -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. www.konesans.com - Consultancy from the people who know "Eisa" <Eisa (AT) discussions (DOT) microsoft.com> wrote in message news:FABB12DE-E6A0-4250-B801-656BD57D5485 (AT) microsoft (DOT) com... Mr Allan Mitchell first thanx for replying my english is not so good as it is not my native toungue writing in CAPs was a hardware error about my db i have so many tables that have forign keys from many other tables and the database in the destination location has ID's that I have 2 look up 1 per row sometimes 3 or 4 it may be a bad design for transforming with DTS and using look ups but it's been normalized well another time thanx for Ur time "Allan Mitchell" wrote: From the tone of the mail it looks as though you are deliberately trying to not get help. Having >1 lookup per row is disastrous for performance. You cannot stop the connection opening and closing. -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. www.konesans.com - Consultancy from the people who know "Eisa" <Eisa (AT) discussions (DOT) microsoft.com> wrote in message news:131BFBCA-A7BB-4A39-BF72-A67A306DF6C4 (AT) microsoft (DOT) com... I ALREADY KNOW THAT WHAT I'M ASKIN ABOUT IS WHY A CONNECTION IS OPEND AND CLOSED FOR EVERY LOOKUP THER PROFILER DISPLAYES THAT AUDIT LOGIN EXEC ..... AUDIT LOGOUT AUDIT LOGIN EXEC ..... AUDIT LOGOUT I HAVE MORE THAN ONE LOOKUP FOR EACH ROW IS IT GOOD OR BAD FOR PERFORMANCE TO OPEN AND CLOSE THET CONNECTION AND HOW 2 PREVENT THANX "Allan Mitchell" wrote: That's the way lookups work. Row * Row Can you not rewrite this using TSQL and a join statement? -- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. www.konesans.com - Consultancy from the people who know "Eisa" <Eisa (AT) discussions (DOT) microsoft.com> wrote in message news:F88930EC-CA0E-4047-B5D6-4C96736704D3 (AT) microsoft (DOT) com... i've been usein DTS for transfecopying data from location to another lately i use AX script withinn lookups to replicate a database to another one and while watching the DTS working in the SQl Profiler I noticed that for each lookup in a row a connection is i\opend to get the lookup then closed once for each row of data ! this costs my application a lot of time my package has 3 connections a source , destnation and one for lookup |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.