jeudi 4 décembre 2014

T-SQL View -- How to 'pre-fetch' schema using scalar function, then populate using table query


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