I have an application that uses T-SQL Views. A number of these Views are quite complex, joining data from many tables. To avoid column-name collisions, the Views often employ Table-Scalar Functions to return table data under a modified/prefixed schema.
For example, I have two tables (parent ACTION
table and child ACTIONSUB
table), both with many columns, and both with identical columns names --
ACTION
RowGUID UID Ref Name LastUpdate etc...
ACTIONSUB
RowGUID UID fkUID Ref Name LastUpdate etc...
As part of our build process, we regenerate our library of table-access functions, one function per table. These functions can accept a delimited list of UID
values, or will return all rows if passed NULL
. As stated above, these functions' main purpose is to return all column data from the table with a modified/pre-fixed schema, so that we can JOIN
them together into a single View. So, using our table-access functions, our tables above would return --
fn_TAF_ACTION(null)
AC_RowGUID AC_UID AC_Ref AC_Name etc...
fn_TAB_ACTIONSUB(null)
ACSUB_RowGUID ACSUB_UID ACSUB_fkUID ACSUB_Ref ACSUB_Name etc...
This approach works, until we have very large tables and/or join data from many tables (functions); then our View performance can seriously degrade.
Our views are simple query statements, usually with calculated columns added to the select list. A simple example --
CREATE VIEW vw_ActionSub2Action_01 AS
SELECT acsub.*, ac.*, IsNull(ac.AC_Date1, ac.AC_Date2) AS [AC_ComboDate], ac.AC_Ref +'.'+ acsub.ACSUB_Ref AS [ComboRef], etc...
FROM fn_TAF_ACTIONSUB(null) acsub
LEFT JOIN fn_TAF_ACTION(null) ac ON acsub.ACSUB_fkUID = ac.AC_UID
I'm wondering if these Views could be re-written using CTEs, to somehow 'pre-fetch' the modified/pre-fixed schemas using the functions, and then query the tables with SELECT INTO
to populate data into the View.
Is it possible?
Something like --
-- first, setup View schema using TAF functions to return empty data set
SELECT acsub.*, ac.*, '' AS [AC_ComboDate], '' AS [ComboRef], etc...
FROM fn_TAF_ACTIONSUB(0) acsub
LEFT JOIN fn_TAF_ACTION(0) ac ON acsub.ACSUB_fkUID = ac.AC_UID
-- then, populate the View by querying the tables
SELECT acsub.*, ac.*, IsNull(ac.Date1, ac.Date2) AS [AC_ComboDate], ac.Ref +'.'+ acsub.Ref AS [ComboRef], etc...
FROM ACTIONSUB acsub
LEFT JOIN ACTION ac ON acsub.fkUID = ac.UID
Is there a way to combine the above process into a single View definition?
PREFACE
I realize our above use of Views may be inherently non-performant; however, our system uses these Views, and performance is reasonable in most cases, so pushing a 're-design' of our processing logic is not feasible.
Aucun commentaire:
Enregistrer un commentaire