RDBMS Get data from View in MSSQL

Avatar

Avatar
Springboard
Level 1
emil_rowland
Level 1

Like

1 like

Total Posts

22 posts

Correct reply

0 solutions
Top badges earned
Springboard
Establish
Validate 1
Give Back
Ignite 1
View profile

Avatar
Springboard
Level 1
emil_rowland
Level 1

Like

1 like

Total Posts

22 posts

Correct reply

0 solutions
Top badges earned
Springboard
Establish
Validate 1
Give Back
Ignite 1
View profile
emil_rowland
Level 1

19-06-2018

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

Replies

Avatar

Avatar
Validate 1
Employee
Vapsy
Employee

Likes

370 likes

Total Posts

726 posts

Correct reply

342 solutions
Top badges earned
Validate 1
Boost 50
Boost 5
Boost 3
Boost 250
View profile

Avatar
Validate 1
Employee
Vapsy
Employee

Likes

370 likes

Total Posts

726 posts

Correct reply

342 solutions
Top badges earned
Validate 1
Boost 50
Boost 5
Boost 3
Boost 250
View profile
Vapsy
Employee

19-06-2018

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

Avatar

Avatar
Springboard
Level 1
emil_rowland
Level 1

Like

1 like

Total Posts

22 posts

Correct reply

0 solutions
Top badges earned
Springboard
Establish
Validate 1
Give Back
Ignite 1
View profile

Avatar
Springboard
Level 1
emil_rowland
Level 1

Like

1 like

Total Posts

22 posts

Correct reply

0 solutions
Top badges earned
Springboard
Establish
Validate 1
Give Back
Ignite 1
View profile
emil_rowland
Level 1

19-06-2018

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

Avatar

Avatar
Validate 250
Level 10
pablo_rosero1
Level 10

Likes

81 likes

Total Posts

348 posts

Correct reply

77 solutions
Top badges earned
Validate 250
Validate 100
Validate 50
Validate 25
Validate 10
View profile

Avatar
Validate 250
Level 10
pablo_rosero1
Level 10

Likes

81 likes

Total Posts

348 posts

Correct reply

77 solutions
Top badges earned
Validate 250
Validate 100
Validate 50
Validate 25
Validate 10
View profile
pablo_rosero1
Level 10

27-11-2018

Hi Emil,

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

All the best,

Pablo