Notes

A lovely isometric exploration game about a town filled with classic Japanese ghosts and a girl looking for her missing sister. It’s not all that scary; mostly it keeps you on edge. Not all that hard either, though you do have to pay attention: there are lots of ghosts, they all behave differently, and you can’t afford to wander around daydreaming. It’s not all that fast, though you’ll need quick reflexes here and there, especially near the end.

I came away feeling really good about it. Maybe they didn’t have millions to spend, but you can tell they tried to make the best game they could.

I’ve already written a couple of times about the quirks of working with huge tables on the client (one, two), but late last week my colleagues suggested another interesting way to optimize relatively large data collections on the client. The short version: the standard recommends working with large collections only on the server. However, if you iterate through the entire collection on the client at the start, for example in the form’s opening handler, it gets cached. After that, you can loop through it, search for rows, and do other work without extra server calls. Overall, you spend less time shuffling data around.

I’ve just run some simple tests, and yes, it seems to work. The method feels a bit like a workaround, and I haven’t tested it under serious load yet, but it’s probably worth keeping in mind.

Game journalists and YouTubers are competing to tear Close to the Sun apart in their reviews, but I unexpectedly liked it. It’s certainly no masterpiece, yet it captures the atmosphere of an enormous Flying Dutchman brilliantly. Parts of it bring BioShock to mind, but I’m not about to tear a story apart just because it reminds me of something else.

There’s also a fantastic single playing over the end credits, which literally sold me the game a couple of days ago. You can listen to it without touching the game itself.

PageIOLatch waits, which I wrote about today, are easy to encounter in any system, even small ones. PageLatch waits are the opposite: they’re hard to notice until you have several thousand users.

They occur when a page is already in RAM and several users try to modify it. Until one user finishes, the others must wait. The wait is usually tiny, but with many users competing for the page, it becomes noticeable.

Two things can cause this.

An Index Hotspot

This happens when we try to write heavily to the last page of an index, primarily one with a monotonically increasing key. Examples include indexes on reference-type fields: since late 8.2 versions, the platform has generated sequential GUIDs, reducing disk fragmentation and making the index key monotonically increasing.

What is an index with a monotonically increasing key? In 1C, register indexes on the period are one example. They don’t grow perfectly monotonically, but the important point is that we generally write data at the end. Document number and catalog code indexes also belong here: a new number is assigned and written to the end of the index.

Fixing a hotspot means changing the design: you need writes to land in different parts of the index, not all at the end.

tempdb System Pages

Creating or dropping a table updates a number of internal pages: IAM (Index Allocation Map), PFS (Page Free Space), GAM (Global Allocation Map), SGAM (Shared Global Allocation Map), and others.

Why does this matter for tempdb? The 1C platform regularly creates an enormous number of tables there. Those internal pages get updated so often that you start seeing PageLatch waits.

MS SQL Server can optimize system page updates for tempdb so they happen less often, but sometimes even that isn’t enough, especially when tables are created with indexes, a very common case in 1C applications.

There are a few ways to tackle it. First, for DBMS versions before 2016, disable mixed extents using trace flag 1118. That removes the need for two internal page types, GAM and SGAM, so you no longer have to wait for their updates. An extent is eight data pages, or 64 kilobytes. If it contains pages from one table, it is uniform; if from several, mixed.

Second, split tempdb into multiple files. Since 2016, the DBMS splits it into eight files by default. Each file has its own internal pages, so spreading the work across files cuts the waits.

Third, reduce the number of temporary tables to lower the load on tempdb. Rewrite the most frequent queries so they run acceptably fast without temporary tables.

When a data page is read from disk into the buffer cache, it is latched for a very short time: it cannot be written, and in some cases cannot be read either. Waiting for that action to finish is a PageIOLatch wait.

Maybe you don’t have enough RAM. Or maybe some lousy queries are churning through the buffer cache, reading millions of rows to return three. The DBMS repeatedly fails to find the required pages in cache and has to read them from disk again and again.

So if you’re seeing a lot of PageIOLatch waits, faster disks, more RAM, and better queries will probably help.

First, to avoid confusion: the platform’s object locks have nothing to do with managed locks, let alone DBMS locks. Second, there are two types: pessimistic and optimistic object locks.

Both are well documented on ITS; here’s the short version.

Pessimistic Locks

A form extension applies one when the user starts editing an object, for example by changing a field. If someone else tries to edit the same object in a form, that form tries to get a pessimistic lock too. It can’t, so the user gets “Failed to lock record.”

The platform is being pessimistic here: the first user has started editing, so they’ll probably save. Better not let the second user change it in the meantime.

You can also apply a pessimistic lock using the object’s Lock() method and release it with Unlock(). Other options are the global context’s LockDataForEdit() and a managed form’s LockFormDataForEdit().

Optimistic Locks

First, some theory: the platform stores versions for reference-type objects, such as catalogs and documents. Basically, a version tells you when the object last changed. When a form extension or code reads an object, its version is read with it.

When writing the object, the platform compares the version obtained when reading it with the version currently stored in the database. If they differ, it reports: “The operation cannot be performed because the versions do not match or the database record is missing.”

That’s an optimistic lock. It’s called that because the platform leaves the check until the last possible moment, when someone actually tries to save.

You cannot apply an optimistic object lock through code: the version is stored in the object table’s _Version field, populated by the DBMS. The platform provides no way to change it directly, though writing the object will change its version.

A very interesting post about the trouble the “Clear All Rights” command can cause in a role. It does what it says, sure: the rights are gone. But the role object can end up eating several times more memory than it needs, out of nowhere.

I read it, thought about it, and went to check our configuration. And what do you know? I found a couple of dozen roles stuffed to the brim with “unchecked boxes.” The rights export weighed in at around three hundred megabytes. After a cleanup, it was almost three times smaller.

It’s curious that this had gone unnoticed for so long. Most likely, it’s down to the object count: our configuration is relatively small. Those roles just weren’t eating enough resources for us to notice.

This is a tricky game to recommend, but it really is art, in the most literal sense. I can’t remember the last time a game had me so spellbound I couldn’t tear myself away.

Maybe it was the art, maybe the music, or maybe how honestly the creators captured the feeling they were trying to get across.

A couple of weeks ago, we added an “Inactive” parameter to FirstBIT ERP’s product catalog. The task was simple: if a user no longer needs a product, they check the box and it disappears from everywhere, including selection forms and warehouse balance selection forms.

Sounds straightforward, right? The code was simple too: we went through every object where users select products and added an extra choice parameter.

But users started complaining that the setting didn’t work. We investigated and realized we’d forgotten input history. Turns out it just ignores choice parameters. Users selected a product in an invoice, marked it inactive, returned to the invoice, and… Saw the supposedly disabled product in the history again.

We looked for a way out. The problem is that input history lives in system storage, and there’s no way to change it from code. You can wipe it altogether, but it only actually clears when the client restarts, and even then it’s hit and miss. Disable history completely? That feels like curing a cold by cutting off the patient’s head.

At some point, we learned that input history is stored not just per field, but also per combination of choice parameters. Each combination of parameters and values has its own history. So, if we add another choice parameter to every product selection field, changing its value will “clear” the history. In reality, it creates a new one, but what difference does that make to the user?

So that’s what we did. It lives in shared storage and is passed to forms when they open through a common module that adds the choice parameter programmatically. When a user sets or clears Inactive for any product, the parameter changes, and already open forms receive it through notifications.

It all started so innocently, didn’t it?

It works pretty well, but the downsides are obvious. First, system storage gradually grows as new combinations of real choice parameters and our artificial one appear. Second, saving products is now a potential bottleneck: two users can’t save products with changed Inactive flags at the same time, because writing our hidden choice parameter’s new value to shared storage causes a lock.

The first problem can be solved by clearing storage on some trigger; the second, by storing our choice parameter per user, for example in an information register. Still, we’re really hoping 1C gives us access to input history before we have to pile more workarounds on top of the ones we’ve already built :-)

Why would you need to know when MS SQL Server started? Say we’re looking into DBMS waits and want to know how long the sys.dm_os_wait_stats DMV has been collecting data. It keeps its data in RAM from the moment the DBMS starts, just like the other DMVs.

You can get the startup time from sys.dm_os_sys_info:

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

There’s another way, using tempdb. It’s created when the server starts, so its creation time gives us the same answer. You can get it from sys.databases:

SELECT create_date FROM sys.databases WHERE name = 'tempdb'

One catch: the server’s startup time doesn’t necessarily tell you when DMV data collection began. Someone might have cleared the stats by hand, for example like this:

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)

Whenever a SQL query could run but is waiting for another resource, information about the reason for the wait is recorded. You can access it through sys.dm_os_wait_stats.

Here are a couple of ready-made scripts to make sense of it:

• A script from Ian Stirk’s article “Uncover Hidden Data to Optimize Application Performance,” published in MSDN Magazine back in 2008. It lists wait types ordered by time, from the most prominent to the least.
• A script from Paul Randal’s article about analyzing MS SQL Server waits; there’s also a Russian translation on Habr. It filters out waits that always occur and can usually be ignored. It also adds a link to a detailed explanation of each wait, such as this description of CXPACKET.

I wasn’t expecting much, but this game surprised me. It looks good, the attention to detail shows, and overall it’s rather nice. Toward the end, it’s even touching.

Play it properly: turn off the lights, put on headphones, and, of course, switch off your inner nitpicker.

Infostart published an interesting article about the DBMS error “cannot insert duplicate key.” One possible cause is using an object’s unique identifier to match it during exchanges with other infobases, without a dedicated register mapping local objects to identifiers in the external database.

I haven’t yet encountered anything as wild as unique identifiers colliding across different infobases for the same table. But I abandoned the “single identifier” approach for good after building an exchange between FirstBIT ERP and GROTEM / Agent.

The first version worked like this: data from FirstBIT ERP was first exported to an intermediary 1C infobase written by GROTEM’s developers, and then to the mobile application server’s main database.

Matching was very simple: object identifiers. The same document in both databases had the same identifier, for example. But some mappings were more complicated: a FirstBIT ERP counterparty became three objects on GROTEM’s side: a counterparty, a retail outlet, and a settlement document.

Yes, a settlement document. All three objects had the same identifier, that of the FirstBIT ERP counterparty. It wasn’t particularly elegant, but the platform handles this arrangement perfectly well, and we decided there shouldn’t be any problems.

The exchange refused to work, though.

A quick look showed that the data made it to the intermediate infobase just fine and passed every integrity and completeness check. Only the last step was failing: sending it to the mobile application server’s database.

Eventually, it turned out that:

1. This database has a table storing all the objects we care about: documents, catalog items, and so forth.
2. That table has a unique index on the object GUID.

Well, mystery solved. Of course, the very first exported counterparty created three entities with the same GUID. They lived happily in their separate tables on the 1C side, but couldn’t be written into one shared table in the application database.

A good video about loneliness, its causes, and its consequences. Spoiler: it’s serious, right down to the biological level. The narration is in English, but Russian subtitles are available if needed.

(A tweet that sums it up nicely.)

(Also, apparently I’ve played enough StarCraft II that I can only hear the title in Zeratul’s voice, with the music from Alone.)

After upgrading the platform to 8.13.12.1790, we encountered an intermittent bug when switching a control’s current page on the server. It works on the server itself, but as soon as control returns to the client, the current page reverts to the previous one. The current-page-change event also fires, as though the user had changed it manually.

Unpleasant, but nothing terrible: we worked around the bug, documented it, and reported it to 1C. They’re still thinking about it. But weirdly, I’m a little disappointed. When you dig into these things, there’s always a bit of mystery. It feels like you’re about to uncover something interesting, some new quirk of the platform, maybe.

Then you finally look at the problem from the right angle, and it’s just an ordinary bug. Maybe striped or spotted, but a perfectly ordinary bug, sitting there twitching its antennae. And you think: damn, is that all I caught? :-)

While writing about searching for rows on the client, I remembered another detail about data on managed forms, also involving transfers between client and server.

Say we need to work with fairly large value tables, thousands of rows each. These might hold data that generally doesn’t need to be shown to the user: pricing parameters, sales and purchase information, or something else. Creating these tables as form attributes seems logical, if only because the designer is convenient.

But things get awkward when the platform sends one of those tables to the server. Instead of a nice compact ValueTable, it sends a much bulkier FormDataCollection. The difference is enormous: on one of our projects, this data exchange accounted for around 40% of a key operation’s total runtime.

The fix is pretty obvious: don’t use form attributes for the tables. Instead, create value tables in code, for example in the OnCreateAtServer handler, put them in temporary storage, and keep only their storage addresses in form attributes. When you need to work with a table, only the address string is sent to the server, so you spend next to nothing on serialization and data transfer. On the server, retrieve the table, read its data, change it if necessary, and save it back to temporary storage at the same address.

When people talk about how something develops, a product, a language, a programming language, whatever, they sometimes call it “evolution.” The idea is that bad solutions gradually die out, while good ones stick around and make the thing better. Fair enough, but that’s not the part I’m thinking of right now. The catch is that descendants inherit more than the useful stuff. They also get every badly put-together, often pointless mechanism that didn’t quite manage to kill their ancestors.

As a result, we hold our breath while swallowing, get confused between dissatisfied and unsatisfactory, and program in the wonderful language JavaScript, which, handy and popular as it is, has picked up every flaw a scripting language could possibly have, and then some.

We’re still lucky, though: consider giraffes! Their laryngeal nerve first travels two meters down the neck and then back up to the laryngeal muscles, instead of going the few centimeters straight from the brain.

Knowing all this makes it a little easier to look at what my code sometimes turns into after another round of development. So what if this hamster has grown a dragon’s tail? Evolutionarily speaking, that’s a pretty useful tail. Who am I to argue with Darwin?

I learned quite a while ago that FindRows() on form data collections can quietly generate server calls, when I ran into a form that took forever to load. The method was being called right in the opening handler, inside a loop, no less. You shouldn’t do that, of course, as the help gently suggests: calling the method executes a server call.

Today, though, while writing code for a similar task, I noticed there were no actual server calls. Surprised, I checked ITS. Here’s the explanation: when you call FindRows() on the client, the search does happen there, but the platform doesn’t keep all the data of large collections on the client. It transfers data from the server, and requesting that data is the server call mentioned in the help.

The platform developers are vague about the collection size below which the client doesn’t need the server; ITS says it’s about twenty rows. So I’d still do the same thing: make an explicit server call and don’t worry about the platform adding its own. Still, it’s always nice to understand a little better what’s going on under the hood :-)

One thing that makes learning English much easier is practice that practically falls into your lap. Sometimes it’s more useful than examples from something like the Cambridge Dictionary, simply because it sticks better.

Yesterday in class, for example, we were working on hypothetical situations and getting our heads around wish and if only. Today, the first thing in my feed was this tweet. Hard to make it any clearer :-)

Or take the difference between try and trying, which we recently reviewed. I memorized it long ago thanks to Felicity’s strained “I do my best” in Borderlands: The Pre-Sequel. That scene sticks with you, and so does the sense that she just can’t do what’s being asked of her.

With all the fuss over that Reebok ad, I came across a great video from their competitors on YouTube. I’m barely following the whole thing, but even I caught some of the fallout. It’s already a couple of years old, but it’s about exactly the same thing, and damn! An ad made by someone with their head screwed on straight. I don’t know how else to put it.

I showed it to the little one: she watched wide-eyed. I’ll show it again next time she’s loafing around instead of training :-)