Kind of a n00b question, I have simple a dataflow with left outer join between a table from 1 database, to a table within a different database with both databases residing on the same db server. Both tables have just over 100k rows.
A bottleneck occurs at the first query transform whilst performing the left outer join reading over 70 million records. It writes just over 4.5million records to the target table.
The entire process is probably handled exclusively by the jobserver. What can I do to use the db engine instead of the jobserver but without using the sql transform?
It’s been a while since I had to work with SQL Server. Have you tried bringing both tables into the same Datastore? If that doesn’t work have you tried creating two Datastores that both use the same user name? You need to do something that gives DS a clue that a single query will have access to both tables even though they are in different databases.
If the two tables are in different instances (different server names, etc) then you’ll have to do something within the database (a view) to make it look like the two tables are in the same database. That is enough to fool DS.
Correct, this is on SQL Server. Admitedly, I have not tried creating linked server or indeed placing the table (or creating a temp table with the same data) in the same datastore although, the db user is the same for the instance and there are 2 datastores created. The view is a good idea. I may try that!
An Update: Just tried the view. Would not have thought of that immediately. Reduced load time by 66.67%. Thank you for the idea, it is a quick and effective solution!
(BOB member since 2004-05-21)
(BOB member since 2007-09-12)