Certain Formatted Text field (HTML) search conditions can be optimised in the Query builder. You can tell when this has happened because a lightning bolt will appear in the Query when you execute it.
Here is the message you get when you hover over the information icon having executed the Query. Note the lack of a lightning bolt in the green boxes.
Ideally SQL Server should have been installed with Full Text Indexing switched on, in which case all repositories created will have HTML columns optimised by default. You can follow these steps to retrospectively add it.
Checking if your server has full text indexing enabled
Right click on your server in SQL Server Management Studio and select Facets.
Below you can see the option ‘IsFullTextInstalled’. If this is True then you can skip to the last stage.
Installing full text indexing on your server
Step 1: Open the Installation Center for the version of SQL Server you have installed.
Select “Add features to an existing installation” (the top option).
Locate your installation media.
Select Add Features to an existing install.
Continue until the Feature Selection and select “Full-Text and Semantic Extractions for Search”. Then ‘Next’ until installation is complete.
Adding full text indexing to your Repository
Once you have installed full text indexing on your server you need to enable it on your MooD database. (This is the part which is usually done by Repository Manager when you create a Repository)
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AND CAST(LEFT(CAST( SERVERPROPERTY('productversion') AS CHAR(50) ), PATINDEX('%.%', CAST( SERVERPROPERTY('productversion') AS CHAR(50) )) - 1) AS INT) >= 10)
CREATE FULLTEXT CATALOG HTMLFTCatalog
declare @sql nvarchar(4000)
set @sql = 'CREATE FULLTEXT INDEX ON MooDElementHTML ( HTMLValue TYPE COLUMN FileExtension LANGUAGE 2057 ) KEY INDEX ElementHTMLPrimaryKey ON HTMLFTCatalog WITH STOPLIST = OFF'
exec sp_executesql @sql
Checking your Queries are now optimised
This is the result – the Query is now optimised and will execute significantly quicker than before.
Please sign in to leave a comment.