The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
OLE Provider Concerns
Posted: 14 May 07 2:49 PM
|
fiogf49gjkf0d Hi I am about to build a SSIS application to import a very large amount of data from our old CRM to SLX. Is there any concerns i should have on performance of the SLX OLEDB Provider? |
|
|
|
Re: OLE Provider Concerns
Posted: 14 May 07 6:35 PM
|
fiogf49gjkf0d You are going to take a performance hit in performing the import, because you will need to use the provider to create new IDs for you - for every record in every table.
What this means is that you will not be able to perform traditional set-based INSERTs along the lines of
INSERT INTO [tablename(field1, field2, ...)]
SELECT field1, field2, ...
FROM ...
because every SLX record needs its own ID to be generated and this generation does not happen automatically via SQL Server IDENTITY fields.
So you will have to perform INSERTs one at a time via a loop - unfortunately losing a lot of SQL Server's query-optimisation power in the process. Your INSERT logic will need to use the generated IDs to maintain relationships between ACCOUNTS, CONTACTS etc - if you do not know SalesLogix, you should seek help here to understand how the tables are linked.
Bearing that in mind, performance via the provider is fine. Having said that, for big imports I would probably use it only to grab SLX IDs and use a SQL Native Client connection for everything else.
Remember that you can grab IDs in batches (eg 10,000 at a time) - this is much faster than doing it individually.
Good luck. |
|
|
|
Re: OLE Provider Concerns
Posted: 15 May 07 10:40 AM
|
fiogf49gjkf0d Yeah I have been looking at the IDs a lot. I think my SSIS Package will use a mid-tier type table to store say the accounts or contacts I am bringing over and toss a row count into a package variable then I can use the slx_DBIDs('ACCOUNT', N) to build N IDs for the table and just union or merge the column into the midtier. Then at the end I can just insert that mid level table to where it needs to go. |
|
|
|
Re: OLE Provider Concerns
Posted: 15 May 07 6:03 PM
|
fiogf49gjkf0d So you are thinking of creating an 'intermediate' ACCOUNT table that is empty apart from x thousand rows of IDs and then merging into that?
I guess you could do it by record number - maybe. Eg if you have another IDENTITY(1,1) field in the intermediate table and match that to your source data somehow...
update iAccount
from iAccount join iSource on iAccount.RecNo = iSource.RecNo
set ifield = iSource.field1, ...
Then at least you can harness some SQL Server processing power - and you'd only have to create the IDs once.
Anyway - sounds like you know what you're talking about - good luck. |
|
|
|
Re: OLE Provider Concerns
Posted: 21 Jun 07 3:35 PM
|
So I ran into a minor issue. Here's the jist: I have all my data ready to get SLX IDs for the account migration. I created an OLE DB Source in a data flow task and the connection is a SLX OLE connection to our dev SLX Server. The SQL Command for the OLE DB Source is slx_DBIDs('ACCOUNT', ?) and i am passing in my parameter for row count in my mid tier account table. When i first attempted this I thought no way it works but low and behold the OLE DB Source is seeing that I am getting a table back with the column named ID. I was like wow awesome easy. Then I realized it is seeing the Column as a char(13) instead of a char(12) for some reason. All my attempts to mess with the properties in the advanced editor are not doing what I thought they woul so I am still trying to get this last issue resolved.
Anyway if you have an idea let me know. I am also going to post this as its own topic. Thanks |
|
|
|