Full text search capability in SQL Server?

  • 17 March 2008
  • 5 replies
  • 10 views
P
Badge +13

This question pertains to K2 2003 and also the BlackPearl product.

On Workspace (or search by API), search for folio or process data is done by wildcard, but for a large historical data this could be slow.   Would SQL Server's built in full text index search be beneficial in this case or because of permissions involved it wouldn't be easily integrated?

 For someone who has View rights to all processes with 1 million rows (which in term has probably a few million rows the way the data is stored in each field), that is a lot of records to scan through for %textstring% type of SQL operation.


 

5 replies

P
Badge +13

I did further experiment on this, it seems that the Workspace search had Full-Text index designed in mind since if you apply Full-Text index to _ProcInstXML Value column, you can easily use "value1 and value2" or "value1 and not value2" and query against 1.4 million rows is instantaneous over the traditional '%filter%'.


N
Badge +5

Hi Peter,


I'm having the same issue. (querying lots of records in _ProcInstXML.Value)


Can you give a bit more details on how to set up Ful-Text index search on ProcInstXML ?


Thanks,


Nicolas

P
Badge +13

I never deployed this to production, only did the feasibility study in QA.    It'd involve creating a new field in the table to have the uniqueness.


[fulltext_id] [bigint] IDENTITY(1,1) NOT NULL,


 CONSTRAINT [PK__ProcInstXml] PRIMARY KEY CLUSTERED
(
    [fulltext_id] ASC


After right click on the table in SQL Server Management Studio and use menu item Full Text-Index, Define full text index.   Once it's set up you can use full text index's query function to query the table.    You can search in Google on how to create full text index as it is a generic SQL function (not specific to K2).   All I did is adding an unique field as required by full text index engine.

N
Badge +5

Thanks !


I wonder what the best (fastest) way is to setup a workflow that can contain multiple Product selection. (eg ProductID, Amount)


I keep a SQL table with those Products. (containing additional information like a description). A webform has a combobox showing those products. Users can select multiple products. When the workflow is started, those products must be linked to the workflow process.


If I want to build a webform that queries the K2 XML field containing those Products. (eg I search for ProductID 3), what would be the cleanest way to do this ? (using or not using K2 XML Fields) Or are K2 XML fields meant for keeping workflow routing information, etc


Regards,


Nicolas

P
Badge +13

In your external custom table you can save the ProcInstID with the list of products.


You webform can query that external table to populate the information, which gives you the ability to index on your ProductID.


Workflow would then just handle the routing/approval or other system level activities.


It's not correct to search via K2 XML field for your scenario.

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings