Manual improvement for MSSQL

For MSSQL a view can be altered in order to improve the performance and to avoid deadlocks. Starting from v 6.6 the improvement is automatically included.

ALTER VIEW VTISCATALOG (MSSQL)

ALTER view [dbo].[VTISCATALOG] as
select
    O.name AS TABLE_NAME,
    CASE WHEN O.Type='U' THEN UPPER(S.name) ELSE S.Name END AS COLUMN_NAME,
    S.column_id AS COLUMN_ID,
    CASE WHEN T.system_type_id IN (35,99) /*text,ntext*/ THEN CAST(-1 AS int) ELSE CAST(S.max_length AS int) END AS DATA_LENGTH,
    CAST(S.precision as int) AS DATA_PRECISION,
    CAST(S.scale as int) AS DATA_SCALE,
    case S.is_nullable
    when 0 then 'N'
    when 1 then 'Y'
    end AS NULLABLE,
    T.name AS DATA_TYPE,
    ep.value as CAPTION,
    -1 as XROWCOUNT, -- *** cast(isnull(P.ROWS,-1) as int) AS XROWCOUNT,
    CAST(S.is_identity as int) AS is_identity,
    case when UPPER(S.name) ='RVER__' then 1 else 0 end as rowversion
from sys.columns S 
    inner join sys.types T   on (S.user_type_id = T.user_type_id)
    inner join sys.objects O  on (S.object_id=O.object_id)
    left join (select major_id, minor_id, [value] FROM sys.extended_properties  WHERE name = 'Caption') ep
        ON (ep.major_id = S.object_id and ep.minor_id = S.column_id)
    -- *** left join sys.partitions P  ON (S.object_id = P.object_id)
-- *** WHERE (P.index_id=0 or P.index_id=1 or P.index_id is null)

Change log

Patch	Tag	Database schema version	Release	Description
TIS6_4_0-35810	v6.4.0.30		2021-12-16	#8427 Fixed Cronjob with Plano operators #8358 Fixed Installing Dependecies #8401 Make sure to expand user folder when other users filtered out #8403 Fixed login with empty user pool #8201 Fixed Cronjob timing inaccuracy #8299 Fixed Userpool control
TIS6_4_0-35761	v6.4.0.29		2021-11-05	#8267 Improved TISBoard introduced TISBoardWestTreeFixedDefault setting #8310 Fixed OpenId refresh token, TIS token stays valid during refreshing OpenId token
TIS6_4_0-35749	v6.4.0.28		2021-10-25	#7329 Fixed Adjust Portlet properties-Op for shared dashboard pages #8265 Fixed User pool in cluster, remote instances not aware about warm-up clone #7963 Improved Logging for investigating performance issues with adapting portlet properties #7275 Fixed Copy datanodes/operations of non-existent operators #8212 Fixed database schema clean-up of dynamic tables (ORA) #7390 Improved Auto-Patch functionality for schema updates by application #8180 Fixed Operation update keep operations' disabled state #7998 Improved cancellation for python processes
TIS6_4_0-35721	v6.4.0.27		2021-09-13
TIS6_4_0-35707	v6.4.0.26		2021-08-20	... #7998 Improved cancellation of for each-recalc #8006 Update SignalR version 2.4.2 #8002 Improved Adapt portlet property (version increments) performance and avoid deadlocks #8001 Improved Schema update via software #7993 Fixed delete user pool clone deadlocks (MSSQL) #7977 Improved TISInstaller extend tisconfig for SqlDmlTimeout* settings, ignore empty settings #7879 Improved performance with warm-up: Implement bulk delete for anonymous users (MSSQL) #3725 Fixed copy project: avoid deadlocks (MSSQL)
TIS6_4_0-35684	v6.4.0.25		2021-07-30	#7916 Fixed portlet presentation in error case (portlets disappeared)
TIS6_4_0-35682	v6.4.0.24		2021-07-27	#7909 Fixed Identifier-Op with new version and Merge-Op loosing column references #7911 Improved performance with warm-up: Stop TaskRunner gracefully #7869 Improved performance with warm-up: fixed admin portlet for user pool (MSSQL) #7773 More logging for OpenId authentication
TIS6_4_0-35664	v6.4.0.23		2021-07-16	#7564 Fixed UserManagement with OpenId user case-sensitivity
TIS6_4_0-35662	v6.4.0.22		2021-07-15	~~#7901 Improved Generate Shifts 2.0-Op for additional parameters~~ #7163 Fixed Generate Shifts-Op parameter MaximumNumberOfPersonsOnCallShifts
TIS6_4_0-35652	v6.4.0.21	dbupdate-2021-07-13-0000.sql	2021-07-15	Please note that the database schema needs to be updated #7864 Fixed CultureInfo table, added languages without region (2) #7461 Fixed Subsession with OpenId #7751 Improved performance with warm-up: consider also existing pool users #7911 Improved performance with warm-up: keep sessions of queued/running tasks alive #7744 Improved performance with warm-up: local session url passed to Python-Op #7885 Improved performance with warm-up: avoid warning logging
TIS6_4_0-35640	v6.4.0.20	dbupdate-2021-06-25-0000.sql	2021-07-05	Please note that the database schema needs to be updated #5055 Improved logging SignalR notification when page deploy removed #7875 Fixed TaskRunner: Bump on task completed #7881 Fixed Python-Op: pymeta don't wait for timeout in case of script error #7847 Fixed Python-Op: pymeta use adapted timeout for pipe connect and subsequent waits #7879 Improved performance with warm-up: Implement bulk delete for anonymous users #7751 Improved performance with warm-up user pool #7848 Fixed Export with huge TIS tables, use Zip64 option if necessary #7869 Fixed admin portlet for anonymous users with warm-up and cluster #7864 Fixed CultureInfo table, added languages without region #7812 Fixed TISInstaller prepare.ps1 use relative path for psql #7883 Fixed deployment web.config template, introduced additional config TiSvcHostUrl
TIS6_4_0-35612	v6.4.0.19		2021-06-21	#7883 Known bug in web.config template #7751 Improved userpool warm-up #7840 Fixed TimeInterval-Op sorting by identifier #7739 Avoid deadlocks on MSSQL during page deployment #7691 Improved OpenId access denied #7812 Fixed prepare.ps1 for path containing spaces #7635 Fixed tisconfig template for OpenId #7701 Fixed Pivot-Op with apostrophe in resulting column names #7170 Fixed Python-Op timeout, don't dispose cancel token after start
TIS6_4_0-35559	v6.4.0.18		2021-06-08	#7883 Known bug in web.config template #7777 Fixed TISBoard EdGrid and SimpleGrid filter row without DashpageHeader #7744 Fixed Python-Op Use local TIService URL #7762 Fixed Export resulting in corrupted files which cannot be imported again #7733 Workaround AppSetting for disable portlet info in editor #7756 Fixed Multiplexer-Op added Datanoderefs for non-active Datanodes #7749 Improved EditTable-Op upgrade #7747 Improved PersistenceRead-Op upgrade #7748 Improved ReadEditableGrid-Op upgrade #7639 Improved FileGuard AllowedAppId setting added #7639 Fixed TIService crashing if cron job solution fails #7691 Improved Autorization error message with OpenId #6904 Fixed Editor data node tab with dirty event #7501 Fixed Editor prevent data node result table update #7460 Revert SignalR DisconnectTimeout to 30s
TIS6_4_0-35532	v6.4.0.17		2021-05-18	#6904 Fixed race condition with parallel retrieving result tables #7501 Fixed TIS Editor data node result table update #7460 Update SignalR to version 2.4.2 #7692 Improved User Management omit ClientId dialog if default is specified in configuration #7695 Fixed Statistics-Op with null values #7673 Fixed potential NRE with XIH.SetParam #7674 Improved OpenId role mapping, allow empty InitRoles #7669 Mitigate potential deadlocks with setting portlet properties during logon events
TIS6_4_0-35513	v6.4.0.16		2021-05-07	#7646 New versions of keep column selection in op settings if input table changes, improved error message Select columns-Op, Delete columns-Op, Delete duplicate data records-Op #7578 Fixed Select data source-Op recalc behaviour #7673 Fixed String parameters trimming to max length (Oracle) #7636 Improved TISVersion information #7652 Improved Project import more logging #7650 Fixed ESS multiple tabs of TIS Board #7660 Fixed copy operator without recalculation then op settings lost #7647 Improved exception handling in project clone #7568 Improved patch.ps1 #7170 Fixed python-op timeout #7637 Fixed HTML portlet token url encoding #7387 Fixed TIS Board Delete deployed pages #7633 Fixed Refresh token #7632 Fixed result table filtering, sorting #7460 Fixed missing globalConfig entries for SignalR
TIS6_4_0-35452	v6.4.0.14		2021-04-19	#7560 Fixed EditTable-Op DataReader error (MSSQL) #7629 Fixed TISInstaller list of imported certificates #7500 Fixed AdminPortlet Logged on user for cluster deployment #7531 Improved TISBoard remove final "Finished" and proceed immediately #7564 Fixed authentication with OpenId match case of username #7547 Fixed XIH.AccountId with OpenId #7578 Fixed Multiplexer-Op recalculate dirty data nodes #7542 Fixed SendEmail-Op error message #7559 Improved TISEditor add safety question for op upgrade action #7552 Fixed TISInstaller for Oracle TISDB update #7549 Fixed TISEditor role checking for user management
TIS6_4_0-35411	v6.4.0.13		2021-03-26	Release #7507 Fixed TISEditor save related operator information #7521 Improved TISEditor highlight sorted columns in result table #7460 Fixed TISEditor lost SignalR connection due to Chrome update #7510 Fixed CVE-2021-26701 System.Text.Encodings.Web Vulnerability #7461 Fixed subsession handling with OpenId #7323 Fixed Merge-Op key column order #7494 Fixed EditTable-Op result table #7491 Fixed logon with OpenId with no roles mapped #6725 Fixed Portlet properties when updating page portlet #7445 Fixed Stimulsoft-Op with empty data nodes #7273 Fixed Portlet error message if source data node has been deleted #7184 Improved Stimulsoft-Op provide upgrade method #7432 Fixed TIS table write-op with date/time columns
TIS6_4_0-35369	v6.4.0.11	dbupdate-2021-03-03-0000.sql	2021-03-04	Release Candidate