Best-practice for SQL queries on Dev/Test and Prod environments

0

Hi Roman,

in which use cases are you using the views?

I know that they exist, but because of the mentioned reasons I have ignored them.
If you are using them internally, from within WEBCON BPS, I would use BPS internal views instead. This has a few additional benefits. If this doesn't work for some reason I use plain SQL.

If you are using from another application / BI, I would create own views which use the GUID for form types/ steps/workflows etc.

Best regards,
Daniel

Hi Daniel

Well, the main reason why I am using SQL views in some places is because I can't get the results I need with a BPS internal views. With the standard filters I can't filter for what I need (because you cannot run functions in standard filters - if you can so, I haven't found any information about it).
AND, there are actions for which it is not possible to use BPS internal views at all, but an SQL query *is* expected.

Example are the two actions from 'Database retention', where there is no possibility to use a BPS internal views.

Using the SQL views was originally recommended to me by an (other) MVP... 😅

Best regards,
Roman

Hi Roman,

as the BPS internal view would only have a benefit if it's used for fields/attributes. The date time values are in the correct format for the user and the like.


Back to your situation. There are two reasons when I would use, my own, views.
1. I need to use a function which is not supported, when the sql query is written in the Designer studio. For example if I need to use ROW_Number() which requires an order by.
2. As far as I remember, the SQL server can improve the performance for executed views.

If neither of the options above are required, I would either use MSSQL database data source or just write the query where it is used. In one attachment you see the "conversion" of a generated process view to a MSSQL database data source.

It just occurred to me, that you may talk about different views. I hope I didn't make a wrong assumption. :)


It's also worth to note, that the data source has a standard and an advanced filter option, see the other screenshot. I've only used the advanced a handful of times.
As you will notice in the "Show" view of the expression editor, not even the data sources use the process views.

Slightly off topic:
I once created a process to transfer these scripts using a dictionary, but I haven't tested them in a long time.
https://github.com/Daniel-Krueger/webcon_artifactDeployment/tree/main/2021.1.3.205


Best regards,
Daniel

Hi Daniel

Regarding the views, I really did mean the SQL views on the SQL server.
(
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
)

As long as I can run queries using BPS interal views or using data sources (with filters), I can use them. But in some cases this is just not possible.

I will adapt my approach based on your advice when it comes to queries.

Thanks for your support.