Coming soon: Your brand new Help Center & Community! Get a sneak-peek here

How to improve SuperOffice 7.5 WinClient + DB Performance?

We have been using SuperOffice for 10 years and currently have around 250 users.

The database is now 17GB in physical size, below you can see the Disk Usage by Top Tables report

The DB server is a virtual machine with dual Intel 2.5 GHz Xeon E5-2640 CPUs and 16GB RAM, in a corporate IT environment. The DB connects via 10GB LAN to the network. Latency from client to DB Server is <1ms.

I rebuild all fragmented indexes with a stored procedure every night.

I also rebuild the freetextindex every night

I have noticed that the larger the DB, the longer WinClient takes to load. 

In todays' environment, the performance is only just OK, and many times under high DB CPU load, the users complain of slow performance.

What tips can people give to improve database and WinClient performance?

Would it be worth throwing another 2 x CPUs in the DB Server? (I might be able to convince our IT to do this).

Here's the top records from our Disk Usage by Top Tables report, sorted by Data desc.

Table Name # Records Reserved (KB) Data (KB) Indexes (KB) Unused (KB)
crm7.FREETEXTINDEX 28'614'169 2'413'864 1'067'936 1'345'800 128
crm7.SALEHIST 4'044'622 2'410'720 1'108'000 1'301'856 864
crm7.APPOINTMENT 876'158 712'392 172'688 521'424 18'280
crm7.CONTACT 779'629 557'560 211'912 337'272 8'376
crm7.UDCONTACTSMALL 780'806 516'008 333'160 178'488 4'360
crm7.UDPERSONSMALL 929'941 513'144 323'568 170'584 18'992
crm7.PERSON 919'870 501'024 208'704 281'552 10'768
crm7.TEXT 1'091'695 430'248 410'896 18'896 456
crm7.ADDRESS 1'612'773 347'448 241'032 102'368 4'048
crm7.UDTEMPSMALL 728'285 273'232 272'728 464 40
crm7.PHONE 1'731'442 272'608 196'440 73'200 2'968
crm7.FREETEXTWORDS 4'026'684 266'512 138'752 127'728 32
crm7.VISIBLEFOR 995'800 251'048 140'208 101'184 9'656
crm7.URL 468'322 190'784 89'072 88'624 13'088
crm7.EMAIL 535'405 190'256 86'048 91'800 12'408
crm7.FOREIGNKEY 581'625 183'936 73'976 97'784 12'176
crm7.SELECTIONMEMBER 1'289'132 176'720 45'192 124'120 7'408
crm7.TRAVELTRANSACTIONLOG 2'142'553 154'056 78'728 74'680 648
crm7.DOCUMENT 347'416 149'056 80'472 59'744 8'840
crm7.UDDOCSMALL 233'227 135'640 81'344 39'576 14'720
crm7.QUOTELINE 77'514 121'728 115'656 4'320 1'752
crm7.UDAPPNTSMALL 158'921 102'776 66'368 27'096 9'312
crm7.UDCONTACTLARGE 302'896 85'688 71'488 12'328 1'872
crm7.SALE 117'752 78'288 33'160 40'240 4'888
crm7.UDSALESMALL 120'999 75'760 47'176 20'864 7'720
crm7.RELATIONS 263'449 74'352 39'064 32'656 2'632

RE: How to improve SuperOffice 7.5 WinClient + DB Performance?

Hi Jochen,

That's an impressive database! Do you know what version of SQL is used, and that SQL is configured to use all availeble memory? 

The used Xeon CPU's have each 6 cores and this should be sufficient but it depends how the server is used. Is this SQL server dedicated for SuperOffice, or are there more databases running on this server?

Are the 2 Xeon processors assigned to this VM or are these the processors from the host where the VM environent is running?

 

Af: Alexander Hesselberth 27. mar 2017

RE: How to improve SuperOffice 7.5 WinClient + DB Performance?

> Do you know what version of SQL is used, and that SQL is configured to use all availeble memory? 

We use SQL Server Management Studio v11.0.3000.0, running on Windows Server 2012 R2 Standard 64-bit (OS 6.3.9000, No service pack)

The SuperOffice database Compatibility Level is set to SQL Server 2012 (110)

The database appears to use all available RAM, I just checked and sqlservr.exe is currently consuming 12.5 GB of RAM, with various OS processes using the rest

Free RAM is currently 126 MB, with Standby RAM at 2.2 GB.

> The used Xeon CPU's have each 6 cores and this should be sufficient but it depends how the server is used. Is this SQL server dedicated for SuperOffice, or are there more databases running on this server?

The SQL Server is dedicated soley to SuperOffice.

I have some other replicated (small) DBs on the same machine but they are only used for sync routines in the middle of the night, and noone can log on to them. So all they do when they are idle is consume disc space.

> Are the 2 Xeon processors assigned to this VM or are these the processors from the host where the VM environent is running?

This I do not know - the server is part of a VM farm operated by our IT. I am unsure how CPU resources are allocated to the different VMs running in the farm.

Note that as a preventative maintenance measure I do the following

  • Check and defrag the disks manually, weekly (even though this is set to automatic once a week, I like to keep an eye on it)
  • Shrink all log files of all replicated DBs daily to conserve discspace (I drop and create these DBs nightly). I've had logs grow to many GB before I implemented this rule. Shrink is build into the SQL sync routines.
  • Delete all log files (from various routines) that are >100 days old to conserve disk space. This is a batch file triggered by the Task Scheduler
  • Rebuild all SuperOffice fragmented indexes nightly but only if fragmentation > 30% and page count > 20

Anything else I can do to tune things a bit?

I keep a sharp eye on the sync jobs and have defered some that were running multiple times daily to a once-a-day schedule (in the night) so as to reduce server load during productive hours

Unfortunately one of my external DBs I copy is only available at 0700, and thus it eats into normal productive hours, with the SQL Server quite busy until 0800

Af: Jochen Siegenthaler 27. mar 2017

RE: How to improve SuperOffice 7.5 WinClient + DB Performance?

Hello Jochen,

The database and the log files are they stored on the same server?
It might be an option to store these on different servers.

Kind regards,

Af: Henk Bergevoet 27. mar 2017

RE: How to improve SuperOffice 7.5 WinClient + DB Performance?

Hi Jochen,

the size of the database shouldn't have that much to say when it comes to the startuptime of the client. I'm assuming the startuptime is the main problem you want to fix?

You should check crm7.userpreference for 'counter'-rows: https://community.superoffice.com/en/technical/forum/rooms/topic/superoffice-product-group/crm-windows-application/slow-startup-caused-by-counter-rows-in-crm7userpreference/

Also check crm7.extapp table for any applications referencing old servers.

And turn off sounds in the win-client.

And check the files in %socache% to make sure that none of the files generated there are abnormally big.

Af: Frode Lillerud 27. mar 2017

RE: How to improve SuperOffice 7.5 WinClient + DB Performance?

Yes, DB file and logfile are on the same server

I cannot see how changing the location of the log file will have an impact on database performance

Sure, it impacts security (if a drive failure occurs, but I cover that with daily DB backups stored on a different server) and it impacts diskspace (I have 85GB free space for my 17GB database, so not an issue)

But moving the log file location won't impact my goal of improving DB performance

 

Af: Jochen Siegenthaler 27. mar 2017

RE: How to improve SuperOffice 7.5 WinClient + DB Performance?

Hi Frode, what do you mean by %socache%?

I have no environment variable with that name... (we're on v7.5.SR1)

Af: Jochen Siegenthaler 27. mar 2017

RE: How to improve SuperOffice 7.5 WinClient + DB Performance?

> You should check crm7.userpreference for 'counter'-rows: https://community.superoffice.com/en/technical/forum/rooms/topic/superoffice-product­-group/crm-windows-application/slow-startup-caused-by-counter-rows-in-crm7userpreference/

SELECT * FROM crm7.userpreference WHERE prefkey like 'Counter-%'

Result: 0 rows (which is good) so that is no issue

> Also check crm7.extapp table for any applications referencing old servers.

SELECT * FROM crm7.extapp WHERE deleted=0

55 rows, I'll check them one by one but most are websites

> And turn off sounds in the win-client.

Where? The only sound setting I can find is Preferences > Diary > Silent after: 1

> And check the files in %socache% to make sure that none of the files generated there are abnormally big.

Where do I find %socache% ??

Ah, do you mean C:\Users\username\AppData\Local\SuperOffice\SOCache

Largest file is doctmpl.bin, 2.5 MB. I have 9 files >1MB

"abnormal" is always relative, is 2.5MB abnormal for you? :)

Af: Jochen Siegenthaler 27. mar 2017

RE: How to improve SuperOffice 7.5 WinClient + DB Performance?

I can't help with this in 7.5 without being able to run with a profiler in your environment.
In 8.0, however, we can turn on a Timer system with a souser.ini switch:

[Startup]
Timers=1

Timer recording is then turned on in strategic places.
This allows us to get some idea of where the bottlenecks are during the startup seuqence - without needing to setup a profiler. The timer recording system itself has negligable impact. Profiling SoCrm.exe is never a trivial undertaking.

A new feature in 8 is the ability to easily Cripple any panels other then Contact and Diary. This feature can be extended with another souser.ini switch:

[FeatureToggle]
ChooseFeatures=1

This will make it easy to also Cripple External Apps, Web panels and Custom archives. The result is that only a minimal system will be initialized.

/conrad

 

Example snippet of a Timer log, this can then be generated from a menu item in your Personal image menu of the Top panel

.
Here my Contact panel system to ca 3.5 sec to load.

 

14:18:42:825 -- -- -- -- -- 3.463140        Contact System
14:18:42:826 -- -- -- -- -- -- 3.354928        SContactTriViewPanel::SContactTriViewPanel
14:18:42:826 -- -- -- -- -- -- -- 3.354825        SContactTriViewPanel::InitializePanels
14:18:42:828 -- -- -- -- -- -- -- -- 0.508817        SContactCardsTabView::SContactCardsTabView
14:18:42:828 -- -- -- -- -- -- -- -- -- 0.190422        SContactCardMainView
14:18:42:954 -- -- -- -- -- -- -- -- -- -- 0.004824        class TUDefControlSpecialList<7>
14:18:43:19  -- -- -- -- -- -- -- -- -- 0.003769        class TUDefControlNormalList<7>
14:18:43:22  -- -- -- -- -- -- -- -- -- 0.130928        SContactCardUdefView
14:18:43:156 -- -- -- -- -- -- -- -- -- 0.028825        SContactCardInterestView
14:18:43:273 -- -- -- -- -- -- -- -- -- 0.025548        SContactCardErpView
14:18:43:298 -- -- -- -- -- -- -- -- -- 0.027054        SContactCardPostItView
14:18:43:347 -- -- -- -- -- -- -- -- 0.002956        class NSArchiveControl::Impl
14:18:43:348 -- -- -- -- -- -- -- -- -- 0.002090        class SCurrentArchiveRow
14:18:43:761 -- -- -- -- -- -- -- -- 0.365018        NSNamedCriteriaControl::NSNamedCriteriaControl
14:18:43:766 -- -- -- -- -- -- -- -- -- 0.358780        NSNamedCriteriaControl::RepopulateSelector
14:18:43:772 -- -- -- -- -- -- -- -- -- -- 0.011301        SNamedSearchCriteriaHelper::SNamedSearchCriteriaHelper

Af: Conrad Weyns 27. mar 2017

RE: How to improve SuperOffice 7.5 WinClient + DB Performance?

Hi Conrad, 

I tried the timer in 8.0 SR4, but didn't get any more info in the logfile. I added 

[Startup]
Timers=1

to SuperOffice.ini, and restarted SuperOffice.

EnableDebug is set to 1, and LogPath has been set to a valid path. A file is created, but none of the extra timing info is written to it. This is supposed to work in 8.0 SR4, right? 

Af: Frode Lillerud 27. mar 2017

RE: How to improve SuperOffice 7.5 WinClient + DB Performance?

I just measured the WinClient 7.5 startup time on our 17GB database:

30 seconds

Deleting the SOCache (C:\Users\username\AppData\Local\SuperOffice\SOCache) results in slightly longer:  33 seconds

The next startup (after the cache was freshly created on the last run): 27 seconds

Any tips on speeding up the startup time?

Love to get it down to <10 seconds.... but how?

Af: Jochen Siegenthaler 27. mar 2017

RE: How to improve SuperOffice 7.5 WinClient + DB Performance?

Hi Jochen

Getting back to your original question, I suggest you take this into consideration:

In a virtualized environment, the compute capacity limit is based on the number of logical processors - not cores - because the processor architecture is not visible to the guest applications.

For example, a server with four sockets populated with quad-core processors and the ability to enable two hyperthreads per core contains 32 logical processors with hyperthreading enabled, but only 16 logical processors with hyperthreading disabled.

These logical processors can be mapped to virtual machines on the server with the virtual machines’ compute load on that logical processor mapped into a thread of execution on the physical processor in the host server.

You may want to disable hyperthreading when the performance per virtual processor is important.

You can enable or disable hyperthreading using a BIOS setting for the processor during the BIOS setup, but it is typically a server scoped operation that will impact all workloads running on the server.

This may suggest separating workloads that will run in virtualized environments from those that would benefit from the hyperthreading performance boost in a physical operating system environment.

For the reason above I suggest you take a look into the Hyperthreading settings in the BIOS of the host, and change settings accordingly. Other than than I believe adding another two cores will be benificial.

Af: Søren Rasmussen 28. mar 2017

RE: How to improve SuperOffice 7.5 WinClient + DB Performance?

Frode:

I do not write to the log file, merely register tiny info structs for later logging (Limited to 1000 entries).
Goto the Associate Image button menu in the top panel.
You should see 3 new items:

Timers
Dump Timers
Delete timers.

Dump timers will crate a lg file and open it.

Let me know if you don't see these menu items..

/conrad

Af: Conrad Weyns 28. mar 2017

RE: How to improve SuperOffice 7.5 WinClient + DB Performance?

Found it!

Note, it also includes the time you spend typing in the username / password. So keep that in mind when troubleshooting.

Af: Frode Lillerud 28. mar 2017

RE: How to improve SuperOffice 7.5 WinClient + DB Performance?

Cool feature Conrad :-) Is there also an overview of the screens we can disbale with the Toggle function?

@Jochen,

regarding the performance, when SuperOffice is started, it fetches a lot of data (preferences) With the timer function and the profiler running in the back you should get more information about the delay but it is also important to look into the VM configuration like Soren said.

@Soren,

Thanks for your explanation.

Af: Alexander Hesselberth 29. mar 2017

RE: How to improve SuperOffice 7.5 WinClient + DB Performance?

8.1 has a nice GUI for this:

 

 

 

Re: Startup performance.
The timer system was designed for a very specific reason.
Since it has a negligable footprint and is only used to record the time it takes to create "modules" - i.o.w. never in code that runs gazillion times - I left it in. I reckon it will come in good use some day :-)

As far as the server environment is concerned, Absolutely! It is the most important aspect.
We just recently experienced this with our Win test base. The VM it was running on did not go around any more. After that was fixed, the speed increases we experienced was baffling. I was expecting 4 to 5 times but 30 to 40 was closer.

That said, Jochen is reporting 30 secs and wants < 10 secs!  I may have forgotten how "fast" 7.5 was but i my univerese, 30 secs is actually quite good!

If however, once SoCrm is running, it feels sluggish in use, then I'd be worried.

/conrad

Af: Conrad Weyns 29. mar 2017