RDBMS Get data from View in MSSQL

[emil_rowland]

Hi, I have some views in an external MSSQL database that I need to access from Adobe Campaign. I have used the RDBMS task in a workflow and for most views, this works great (fast speed around 17s) but for one view it takes hours for Campaign to get all data. If I run the View through Microsoft SQL Server Management tool it takes 10s to get all data and it returns around 5000 rows so it should go fast on Adobe Campaign. Does anyone else had this issue and know how to speed up the transfer?

//Emil

[Vipul_Raghav]

Hi Emil,

You should enable the SQL profiler to see what query does Campaign fire on your MSSQL database. Get the Execution plan of it to understand if the delay is because of query being created.

Additionally, since it is an external DB, you must be using FDA module.

When pulling data from FDA inside a workflow, Campaign has to create a temp table inside the FDA database and feed the relevant records.

When you view the VIEW On MSSQL you are simply firing a Select query but in case of workflow, a temp table is created, select on view is fired, data is fetched, inserted to temp table and then made available to you.

So there are a lot of moving parts you need to consider here.

regards,
Vipul

[emil_rowland]

Hi,

Thanks for your answers.

If I look in the workflows SQL logs I can see this two rows for the dbimport:

20/06/2018 08:09:43 (dbImport)

-- Log: Creating table 'wkf164472814_912_1'  DropTableIfExists 'wkf164472814_912_1'; CREATE TABLE wkf164472814_912_1(   sCustomerID nvarchar(255),    sFacility nvarchar(255),    sFacilityCountry nvarchar(255),    sOptionID nvarchar(255) );

20/06/2018 08:09:43 (dbImport)

[nms:extAccount:153446567]>[default] COPY INTO wkf164472814_912_1 (sFacilityBusinessGroup,sFacilityCountry,sCustomerID,sOptionID) SELECT   D0.Facility, D0.FacilityCountry, D0.CustomerID, D0.OptionID FROM [dbo].[VIEW_CART_ADDITIONS]D0

So does it create a temp table on the external database? Because it looks like it creates it in the local database, but I can't understand the COPY INTO query is it calling the SELECT query multiple times or is the SELECT query only executed once?

//Emil

[pablo_rosero1]

Hi Emil,

As I understand it, it's only executed once.

All the best,

Pablo