SQL indexed views are incompatible with Dynamics GP

Trying to use indexed views with Dynamics GP? – well I’ve been there and I have seen others attempt the same. The Dynamics GP database and application are not really compatible with SQL indexed views. To help out those searching around this subject I thought I should write up our experience.

What is an indexed view?

An indexed view is a SQL database view that has had an index applied to it. This sounds obvious, but the important thing to realise is that the index will be materialised as an index on disk. Think of it as another table created on disk representing the data held in the view, this is why it is quick to get the data if it is summary data. This index "table" is maintained whenever the data that it covers is altered. So it is easy to imagine that indexed views are great for creating summaries, filtered views of data that cross multiple tables, as they will keep track of changes automatically and update the index "table", thus keeping the data "in-sync" with the data in the various tables that it derives from. As the data that represents the SQL view has been pushed to disk and persisted there, querying that summary data is lightweight and quick, because the work has already been done to process the data when it was stored, pre-joined between tables and/or summarised, rather than the database engine having to do all that work on the fly for each query as the query is ran.

However, there is a downside. When (any) index is created, you pay a price for the benefit of fast data recall when reading, by suffering with slightly longer writes times and those longer writes lead to longer living locks on the data and then this can lead to blocking. This may cause issues with performance in some circumstances. The extra writing is because the data held in the index "table" that represents the view needs to be maintained (updated) whenever any of the data underpinning that view changes. Depending on the workload sizes of tables, IO performance etc, this may be significant work if large numbers of records are updated at once. Again as with any SQL index, it takes space on disk too. For large wide views over large tables this may be a consideration too bloating storage and the knock on consequences that brings.

One of the most frustrating matters when working with indexed views is that there are a whole heap of constraints and restrictions around what is permitted in the query that forms them. For example, when using GROUP BY, it must contain the function BIG_COUNT(*) as a column and various database settings restrictions can apply too (there are many, many more). This means when designing the simplest views SQL compile errors warning that it is not possible to do "this and that” frequently cause annoyance. It is very obvious why this is the case when you think about it. It is due to the fact the data is persisted to disk, so it needs to be unchanging to store it, thus any SQL function used by the view has to be deterministic -aha, no “getdate()” function! It is surprising how often this will catch people out. I could go on and on about the restrictions and requirements of indexed views, but just go try making one and you’ll discover the pain yourself, then go read up the documentation to realise how much there is to it!

Another example below are the SQL SET OPTIONS that are required by SQL indexed views…

IndexViewSettings

Using indexed view with Dynamics GP

Professionally we use indexed views a lot in our applications for the speed of access and real time integrity of summary data they offers, so sooner or later a GP admin or GP developer decides they would quite like to use an indexed view... -then it all ends in tears, let us see why…

WITH SCHEMABINDING

This is the first problem. To build an index view the view must use SCHEMABINDING. This locks the database schema and the view together. Unfortunately this may then cause issues with application updates. When Dynamics GP is updated for a service pack or upgrade, sometimes the tables or other database objects may need to be dropped and recreated. This happens during the upgrade process, however if the object that is to dropped is schema bound, then the upgrade script will not be allowed to do what it wants, causing the upgrade to fall over. Hence if indexed views have been bound to the GP tables this is a real risk when a site comes to do an upgrade. Obviously if the person performing the upgrade was aware, then they could drop the view and recreate it after the upgrade, but in real life the knowledge is lost as employees leave or contractors move on causing upgrade pain. 

The second problem is more terminal. Let's try it and see what happens if we create a view over the sales order header and lines to speed up the number of sales lines by country that and not voided in historical transactions. Create the view on a non-production database like this…

 

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
-- We must schema bind
IF OBJECT_ID ('SalesVoidedLinesSummary', 'view') IS NOT NULL
    DROP VIEW SalesVoidedLinesSummary ;
GO
CREATE VIEW SalesVoidedLinesSummary
    WITH SCHEMABINDING
    AS 
       SELECT  COUNT_BIG(*) Cnt,
               SOP30200.SOPTYPE,
               SOP30300.CCODE as Country
       FROM 
       dbo.SOP30200
       JOIN
       dbo.SOP30300 ON SOP30200.SOPTYPE=SOP30300.SOPTYPE AND SOP30200.SOPNUMBE=SOP30300.SOPNUMBE
       WHERE
       SOP30200.VOIDSTTS=0 --Not Voided
       AND SOP30200.SOPTYPE IN (1,2)
       GROUP BY  SOP30200.SOPTYPE, SOP30300.CCODE
GO

 

Then create the indexed view by adding the index. - REMEMBER I SAID NO PRODUCTION DATABASES WITH THIS LITTLE EXPERIMENT!


--We materialise the view by creating an index on it
CREATE UNIQUE CLUSTERED INDEX IX_SOP30200SOP30300VoidSummary
    ON SalesVoidedLinesSummary (SOPTYPE, Country);
GO

 

We can now query the view data like so..

SELECT  * FROM SalesVoidedLinesSummary where SOPTYPE=1 AND Country='PT'

and directly before we created the view with this…

SELECT  COUNT_BIG(*) Cnt,
               SOP30200.SOPTYPE,
               SOP30300.CCODE as Country
       FROM 
       dbo.SOP30200
       JOIN
       dbo.SOP30300 ON SOP30200.SOPTYPE=SOP30300.SOPTYPE AND SOP30200.SOPNUMBE=SOP30300.SOPNUMBE
       WHERE
       SOP30200.VOIDSTTS=0 --Voided
       AND SOP30200.SOPTYPE = 1
       AND SOP30300.CCODE='PT'
       GROUP BY  SOP30200.SOPTYPE, SOP30300.CCODE

 

...and we find that from not having the view to having the view we have dramatically reduced the query time as shown below. I’m not sure about the integrity of those figures but this isn’t a discussion around query optimisations, just accept that indexed views solve performance problems.

    Total execution time without view:  17390   
     Total execution time with view:        505   

 

In production

So developer or IT pro after testing that out on the test company database says, “great, now lets create it on the production database as that worked like a charm”, and then goes to login to GP and create a sales order having created the view…

...to then get the dismay of the error that follows...

“An edit operation on table ‘SOP_Master_Number_SETP’ failed. A record was already locked'.”

IndexViewGPError1

and

INSERT failed because the following SET options have incorrect settings ‘QUOTED_IDENTIFIER, CONCAT_NULL_YEALS_NULL,ANSI_WARNINGS’. Verify that SET options are correct for use with indexed views and”…

GPIndexViewError2

… then starts the support calls flowing in from the users! 

So why we can’t have nice things?

Go back up in this post, as you saw in the graphic, that there are certain SQL SET options required to make indexed views work. Sadly these are not compatible with the SET options required to make Dynamics GP work shown below! -thus the application breaks, simple as that! If those phones are still ringing from your users right now, then just simply drop the view and your users will be happy again.

We did play around a bit but never found a solution to this issue, but if you have a work around do let me know.

References

Mariano Gomez has a nice post about why this is required by Dexterity to make GP run correctly in this post:

Microsoft SQL Server DSN Configuration

Microsoft - Creating Indexed Views

Gavin wrote this which inspired me to document the issue before others fall into it...

Brief overview and comparison of how summary values are stored and calculated in Dynamics GP, Dynamics NAV and Dynamics 365 Business Central

 

What do you call that window GP?

Steve Endow mentioned on twitter the other day about the naming of objects in GP when programming. The inconstancies are astounding, I have to agree, you can spend half an hour trying to determine if the object named something almost like what you want is actually the object you want or not, then two mins to actually write the mod! The fact you end up using a tool to work out what the object name of the window is in the code, well that just tells the story.

api naming dynamics gp steve endow

What Steve might not realise is that it is even worse if you speak English rather than American. See that window he’s looking at in the tweet, look at it on my screen…

puchaseinv

So we have Enquiry rather than Inquiry, no big deal? Well mostly no we learn the translations, but it confusing to the new developers who have to get used to this translations issue. Sometimes the translated terms are not as obvious as this. However even this would be an issue if you were looking at the object explorer ordered alphabetically or doing a search for the object.

There  is fun though with Debtors and Customers. I still don’t really know why we can’t have customers too, for our users it would make more sense than the accounting term debtors!

Steve kindly furnished me with this screen shot of an American GP customer window.

GP2015CustMaint

Compare that to what we see…with a English Debtor Window. These are the same windows, replacing customer everywhere, now imagine searching for this in visual studio, or a field called debtor id…

dmuk

In visual studio when developing addins we have RMCustomerMaintenance, good job I didn’t go searching for debtor when looking for the object, eh?

customerobject

So I conclude it is all fun and games developing visual studio addins for GP!

 

Thanks for Steve inspiring this post with his tweet and helping with the screen shot.

GPUG Summit 2016 Tampa USA - Part 5

Sadly Last Summit Day started with a last walk into the conference centre along the river walk with its art works, miniature trams and other artefacts along the way.

miss

Today Bob starts the day off announcing next year’s summit details, Nashville October 2017.

Summit 2017

He also lets us know what the other events look like. Note how reIMAGINE has been made earlier to deal with the issue of too many days out the office next to each other. We also see AMPLIFY back again. Some were wondering if this would be come an biannual or on demand event, but it looks like its annual for now.

calendar

 

The GPUG all star awards were awarded and I got to have a close look at Amber’s award, she was so happy!

Allstars[3]

Amber

Steve Endow and I had an advanced .NET developer session in the afternoon that was fun. I brought some IoT kit through airport security, for the session to demo and also talked about harnessing GP protocol handers for your own applications. Steve showed some really cool demos using reflection to attach buttons dynamically and without programming to GP windows among other things. I was expecting eight people on a Friday afternoon session and for something as specialist as our session, so when I counted thirty people while Steve was talking I was quite amazed – thank you, every one of you for turning up.

iot2

iot1

With that it was time for most people to go home. My flight was not until Saturday afternoon, so was potentially on a lonely last evening.
What better way to end the week, Belinda Allen asked me to join herself, Jen Kuntz, Aaron Back, John Lowther and Mark Polino for dinner. I was feeling a bit star struck in such company!

THANK YOU EVERYONE for the experience and friendship.

 

Saturday Morning

I explored Tampa central library, that has linking walkways between buildings, some curious books and a train in the kids book section.

last1last2

I then checked out the Art Museum – and it was family day so I got in free! – bonus!

We are spoilt with the big arts museums we have at home, but it was nice to potter around. While I was sitting deciding what to do next Jen tweeted that she was heading to the airport via a cheap shuttle. I contacted her to find the details and arranged to meet up for the shuttle.

last3last4

I grabbed a lovely lunch at the Duckweed Urban Grocery store/cafe, a great place to check out if you are in Tampa.

last5

I then got the hotel shuttle to meet Jen and started the long trek back to the United Kingdom. Arriving home on Sunday afternoon, having bypassed a nights sleep.

jen and Tim

GPUG Summit 2016 Tampa USA - Part 1
GPUG Summit 2016 Tampa USA - Part 2
GPUG Summit 2016 Tampa USA - Part 3
GPUG Summit 2016 Tampa USA - Part 4

 

GPUG Summit 2016 Tampa USA - Part 4

I got invited to be interviewed by the Enterprise Software Podcast. As a listener for some time now, I couldn’t turn the offer down…

podcast

We talked about the GPUG Summit and that more UK engagement is required, I did my best to promote GPUG in the interview. 

While at summit, there were a couple of particular presenters that I wanted to see presenting sessions. I got to catch a session by Belinda Allen, I loved her casual confident presenting style.

belinda

I also caught session by Leslie Vail where she did some crazy things with Dynamics GP word Templates, you could tell lots of hours had gone into the preparation on that presentation and I did learn a few tricks that I will apply back at base.

wordtemplate

There were other sessions and events going on that I caught too..

vote

more

After a long day I ended up walking up though town past the Tampa Theatre to the far end of the river walk, to the water park, noticing cycle tyre pumps and more art works on the way. It was fun to see that the freight train also goes right through the city streets along the track I walk over each day. This explains the loud horn that goes off each night as it hoots its way through the city traffic!

 

picture house

train

water park 2pumpwaterPArk

park

 

GPUG Summit 2016 Tampa USA - Part 1
GPUG Summit 2016 Tampa USA - Part 2
GPUG Summit 2016 Tampa USA - Part 3
GPUG Summit 2016 Tampa USA - Part 5