SIFT Tables/Views

Hello,

I often need to create SQL queries from NAV db to calculate SUM of Amounts from some detailed tables as e.g. for “Detailed Cust. Ledg. Entry”, G/L, Sales Invoice Line, etc. I’m wondering if it could be an Ok practice to involve the SIFT tables (for old NAV) and SIFT views to make the queries more efficient instead of doing query with SUM from the detailed table records.Please, advise.

Thanks

I doubt you’ll have some performance improvement involving SIFT / VSIFT instead of subselects.

Besides, there lies rather heavy algorithm in NAV which exact level of SIFT entries to use in any given case. this will be difficult to clone in TSQL and by no means will make the script rather complicated.

I myself regularly write scripts of the same kind and I always have used subselects for sums or other types of FlowFields (and CASE for Option fields to get text, not integer value for such) with no performance problems.

The same subselects for Names instead of Codes from Master tables - be it Customers / Vendors, Dimensions, you name it. These can be fetched using JOINs, too, but I haven’t noticed performance difference between JOIN or subselect. What I have noticed is that FIRST run of such rather crazy query is much longer (say, fetching GL data month by month for whole FY as older versions Excel are limited to 64K of rows only and whole FY doesn’t fit in one sheet). The following queries are MUCH faster, as MSSQL creates/updates statistics and consecutive runs benefit of that.

Thank you for your response, Modris. I’m doing approximately the same as you’re mentioning. But, I’m wondering if we could also consider those SIFT tables/views.

What I meant could be in a simplify way as below:

It seems as the code from the SIFT view as below could be more efficient than to get it as SUM from the actual GL entries especially if there are many entries and it’s needed for many GL accounts.

SELECT [G_L Account No_] ,[Posting Date] ,[SUM$Amount]

FROM [Demo Database NAV (7-1)].[dbo].[CRONUS International Ltd_$G_L Entry$VSIFT$8]

where [G_L Account No_]=‘1110’ and [Posting Date]=‘2013-12-31 23:59:59.000’

Or we could also use JOIN with GL that would be more efficient anyway than to use SUM from GL.

The same from GL would be as below:

SELECT

[G_L Account No_],sum([Amount]) as Amount

FROM [Demo Database NAV (7-1)].[dbo].[CRONUS International Ltd_$G_L Entry]

where [G_L Account No_]=‘1110’ and [Posting Date]<=2013-12-31 23:59:59.000’

group by [G_L Account No_]

Which version of MSSQL you use?

If 2012 and up, then so-called window functions may be used:

Here’s a text version to copypaste, screenshot was just for proof it works [;)]

SELECT DISTINCT
GLE.[G_L Account No_],
GLE.[Posting Date],
FORMAT(GLE.[Posting Date],‘yyyy-MM’) AS YearMonth,
SUM(GLE.[Amount]) OVER ( PARTITION BY GLE.[G_L Account No_], GLE.[Posting Date] ) AS Net_Change_By_Day,
SUM(GLE.[Amount]) OVER ( PARTITION BY GLE.[G_L Account No_], FORMAT(GLE.[Posting Date],‘yyyy-MM’) ) AS Net_Change_By_YearMonth,
SUM(GLE.[Amount]) OVER ( PARTITION BY GLE.[G_L Account No_] ) AS Ending_Balance
FROM [dbo].[CRONUS Canada, Inc_$G_L Entry] AS GLE

WHERE GLE.[Posting Date] < ‘2014-01-01’

ORDER BY [G_L Account No_], GLE.[Posting Date]

a follow-up to my previous post - I looked at the execution plan, here’s what it advices:

/*
Missing Index Details from SQL_Window_Functions.sql - MO.Demo Database NAV NA (7-0) (Mo\Modric (66))
The Query Processor estimates that implementing the following index could improve the query cost by 23.6078%.
*/

/*
USE [Demo Database NAV NA (7-0)]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[CRONUS Canada, Inc_$G_L Entry] ([Posting Date])
INCLUDE ([G_L Account No_],[Amount])
GO
*/

there IS one not-good-practice issue in the query - DISTINCT clause is resource-heavy (13% in this case), but even with it and without that recommended the execution was instantaneous in DEMO DB

In real DB with 1’965’226 records in GL Entry table it took 1min 24sec, but it was not run on a server hardware, I did it at home (on FRI evening BTW!) on single i3 CPU desktop PC, well, disks are SSDs with 550+ Mbit/sec transfers. Anyway, 49% was again eaten by DISTINCT clause - now I understand why it is said to be used only if you can’t get along otherwise…

updated: Got rid of DISTINCT clause:

WITH ctx AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY GLE.[G_L Account No_], GLE.[Posting Date] ORDER BY GLE.[G_L Account No_], GLE.[Posting Date] ) AS SimilarRowNo,
GLE.[G_L Account No_] AS GLAcc,
GLE.[Posting Date] AS Tx_Date,
FORMAT(GLE.[Posting Date],‘yyyy-MM’) AS Tx_YearMonth,
SUM(GLE.[Amount]) OVER ( PARTITION BY GLE.[G_L Account No_], GLE.[Posting Date] ) AS Net_Change_By_Day,
SUM(GLE.[Amount]) OVER ( PARTITION BY GLE.[G_L Account No_], FORMAT(GLE.[Posting Date],‘yyyy-MM’) ) AS Net_Change_By_YearMonth,
SUM(GLE.[Amount]) OVER ( PARTITION BY GLE.[G_L Account No_] ) AS Ending_Balance
FROM somecompany$G_L Entry] AS GLE
WHERE GLE.[Posting Date] < ‘2014-01-01’
)
SELECT
GLAcc,
FORMAT(Tx_Date,‘d’,‘en-CA’) AS Tx_Date,
Tx_YearMonth,
Net_Change_By_Day,
Net_Change_By_YearMonth,
Ending_Balance
FROM ctx
WHERE SimilarRowNo = 1

exec time on ~2M rows - 49 seconds

Thank you so much, Modris, again. The queries could be very useful. I also wanted to hear if those SIFT tables/views could be somehow useful as they’re already aggregating data.

Thanks,

Alex

SIFT are tables, VSIFT are views. And here we come to different versions of NAV.

SIFT Tables are updated by NAV logic @ the moment when a post is done, and those contain real summary data.

VIEWS in fact are stored queries, even indexed views introduced in latest versions of MSSQL DB. Those contain no real data, only the definition.When a view is used in any way, the script underneath is run - optimized, pre-compiled, but RUN. The WITH SCHEMABINDING clause allows indexing to speed up the query, but it is still run.

Here we come back to why views were introduced for - to hide the real DB structure from DB users, be it end-users or programmers. DBAs defined the DB structure & optimized it at their own wish, but VIEWS allowed to expose the data to whoeverelse using those data, maybe gathered together from numerous tables, but easy to use AND guaranteed to be the same if DBAs change the real underlying table structure if they found it not optimal.

Therefore, my crazy TSQL scripts hereinabowe in fact mimicrize VSIFT views, but do the same - query the data underneath.

I had not enough time to evaluate performance of different approaches, and maybe our DUG forum SQL guru Jorg Stryk will have many objections to what I said, but in real world those work - said if you have hardware serious enough.

here’s the proof…

CREATE VIEW [somecompany$G_L Entry$VSIFT$1]
WITH SCHEMABINDING AS SELECT
“G_L Account No_”,
“Posting Date”,
COUNT_BIG(*) “$Cnt”,
SUM(“Amount”) “SUM$Amount”,
SUM(“Debit Amount”) “SUM$Debit Amount”,
SUM(“Credit Amount”) “SUM$Credit Amount”,
SUM(“Additional-Currency Amount”) “SUM$Additional-Currency Amount”,
SUM(“Add_-Currency Debit Amount”) “SUM$Add_-Currency Debit Amount”,
SUM(“Add_-Currency Credit Amount”) “SUM$Add_-Currency Credit Amount”
FROM dbo.somecompanyR$G_L Entry"
GROUP BY “G_L Account No_”,“Posting Date”

“WITH SCHEMABINDING” is not related to indexing. What it does is link the view definition to the associated table(s). The tables cannot be modified in any way that would impact the view. To do such a change, you must first drop the view, make the table change, and then recreate the view.

The index (for an indexed view) is created much the same as a regular table. The advantage of using an Indexed View is that it improves “read” performance. As you now have a physical (clustered) index to work with. However this comes at a cost to “write” performance. As you must also now maintain that index for every related change in the associated table(s). The question becomes whether the benefit outweighs the cost. Which will vary depending how the tables are used.

When a view is used in any way, the script underneath is run - optimized, pre-compiled, but RUN. The WITH SCHEMABINDING clause allows indexing to speed up the query


imran

When a view is used in any way, the script underneath is run - optimized, pre-compiled, but RUN. The WITH SCHEMABINDING clause allows indexing to speed up the query


imran

Just to clarify the above:

While a view to which an index is added must use “SCHEMABINDING”. The “WITH SCHEMABINDING” in itself does not make a view an Indexed View. The “SCHEMABINDING” is only to insure the underlying tables are not modified in a manner that would invalidate the view or associated indexes.

OK, but you can not add (define) all & every index if there’s not SCHEMABININDIG with all it’s consequences

I see we have left Navision & gone to underlying DB instead :slight_smile:

As this is MSSQL, and, as nobody knows more about SQL-NAV intricacies (and SQL as such), as Jorg, I’ll take the courage to move this thread to another forum.

Jorg - help us to solve OP question ?

PS sorry for misspelling, i’ve latin kb only

edit:

or? consider an ideally flexible spherical cow in vacuum? maybe our discussion lies at that level?