Tools and Examples¶

Core Stored Procedures (Recap)¶

spMFSetup_Reporting
spMFCreateTable / spMFCreateAllMFTables
spMFUpdateTable / spMFUpdateTableInBatches
spMFUpdateMFilesToMFSQL
spMFUpdateAllIncludedInAppTables
spMFClassTableStats
spMFGetMfilesLog (event log export)

Helper Functions¶

fnMFParseDelimitedString: Split multi‑lookup delimited list for joins.

Inspection Queries¶

Class Portfolio .. code:: sql

SELECT Name, IncludeInApp, LastObjectCount, LastUpdateOnUTC FROM dbo.MFClass WHERE IncludeInApp = 1 ORDER BY Name;

Recent Batch Outcomes .. code:: sql

SELECT TOP 50 ProcessBatch_ID, CreatedOnUTC, Status, LogText FROM dbo.MFProcessBatch ORDER BY ProcessBatch_ID DESC;

Batch Detail (Verbose) .. code:: sql

SELECT TOP 100 pbd.ProcessBatch_ID, pbd.ColumnName, pbd.ColumnValue FROM dbo.MFProcessBatchDetail pbd ORDER BY pbd.ProcessBatch_ID DESC;

High Churn Classes (Last Hour) .. code:: sql

SELECT mc.Name, COUNT(*) AS ChangeRows FROM dbo.MFProcessBatchDetail pbd INNER JOIN dbo.MFProcessBatch pb ON pb.ProcessBatch_ID = pbd.ProcessBatch_ID INNER JOIN dbo.MFClass mc ON pbd.Class_ID = mc.Class_ID WHERE pb.CreatedOnUTC > DATEADD(HOUR,-1, SYSUTCDATETIME()) GROUP BY mc.Name ORDER BY ChangeRows DESC;

Explode Multi‑Lookup Example¶

SELECT so.ObjID, f.ListItem AS CustomerID
FROM MFSalesOrder so
CROSS APPLY dbo.fnMFParseDelimitedString(so.Customer_ID, ',') f;

Analytic View Pattern¶

CREATE VIEW custom.vwInvoiceTotals AS
SELECT InvoiceDate, Customer, SUM(TotalAmount) AS Total
FROM MFInvoice
GROUP BY InvoiceDate, Customer;

Row Drift Audit (Random Sample)¶

SELECT TOP 20 * FROM MFInvoice ORDER BY NEWID();

Event Log Join Sketch¶

SELECT e.ID, e.TimeStamp, e.Type, t.NameOrTitle, t.ObjID
FROM MFilesEvents e
CROSS APPLY (SELECT e.Events.value('(/event/data/objectversion/objver/objid)[1]','int') AS ObjID,
                    e.Events.value('(/event/data/objectversion/title)[1]','nvarchar(255)') AS NameOrTitle) t
INNER JOIN MFInvoice i ON i.ObjID = t.ObjID;

Next¶

Performance tuning: Performance and Optimization