CommandTimeOut can only be the default..

[leedavi]

In a large DB a search on "ORDERS" can take longer that (30seconds). Which gives a timeout error.

There needs to be a way to set the commandtimeout.

The problem only appears in large databases when doing a search on the order. We could remove the complexity of the order search to solve this.

[leedavi]

Current Search

/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP (1000) [ItemId]
,[PortalId]
,[ModuleId]
,[TypeCode]
,[XMLData]
,[GUIDKey]
,[ModifiedDate]
,[TextData]
,[XrefItemId]
,[ParentItemId]
,[Lang]
,[UserId]
,[LegacyItemId]
FROM [dbo].[NBrightBuy]
where portalid = 0
and (
(([xmldata].value('(genxml/billaddress/genxml/textbox/firstname)[1]', 'nvarchar(max)') like '%wolf%' collate sql_latin1_general_cp1_ci_ai ))
or (([xmldata].value('(genxml/billaddress/genxml/textbox/lastname)[1]', 'nvarchar(max)') like '%wolf%' collate sql_latin1_general_cp1_ci_ai ))
or (([xmldata].value('(genxml/billaddress/genxml/textbox/unit)[1]', 'nvarchar(max)') like '%wolf%' collate sql_latin1_general_cp1_ci_ai ))
or (([xmldata].value('(genxml/billaddress/genxml/textbox/street)[1]', 'nvarchar(max)') like '%wolf%' collate sql_latin1_general_cp1_ci_ai ))
or (([xmldata].value('(genxml/billaddress/genxml/textbox/postalcode)[1]', 'nvarchar(max)') like '%wolf%' collate sql_latin1_general_cp1_ci_ai ))
or (([xmldata].value('(genxml/billaddress/genxml/textbox/email)[1]', 'nvarchar(max)') like '%wolf%' collate sql_latin1_general_cp1_ci_ai ))
or (([xmldata].value('(genxml/shipaddress/genxml/textbox/firstname)[1]', 'nvarchar(max)') like '%wolf%' collate sql_latin1_general_cp1_ci_ai ))
or (([xmldata].value('(genxml/shipaddress/genxml/textbox/lastname)[1]', 'nvarchar(max)') like '%wolf%' collate sql_latin1_general_cp1_ci_ai ))
or (([xmldata].value('(genxml/shipaddress/genxml/textbox/unit)[1]', 'nvarchar(max)') like '%wolf%' collate sql_latin1_general_cp1_ci_ai ))
or (([xmldata].value('(genxml/shipaddress/genxml/textbox/street)[1]', 'nvarchar(max)') like '%wolf%' collate sql_latin1_general_cp1_ci_ai ))
or (([xmldata].value('(genxml/shipaddress/genxml/textbox/postalcode)[1]', 'nvarchar(max)') like '%wolf%' collate sql_latin1_general_cp1_ci_ai ))
or (([xmldata].value('(genxml/shipaddress/genxml/textbox/email)[1]', 'nvarchar(max)') like '%wolf%' collate sql_latin1_general_cp1_ci_ai ))
or (([xmldata].value('(genxml/productrefs)[1]', 'nvarchar(max)') like '%wolf%' collate sql_latin1_general_cp1_ci_ai ))
or (([xmldata].value('(genxml/ordernumber)[1]', 'nvarchar(max)') like '%wolf%' collate sql_latin1_general_cp1_ci_ai ))
)

on over 8000 orders, 1min 4 sec.

Reduced search...

/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP (1000) [ItemId]
,[PortalId]
,[ModuleId]
,[TypeCode]
,[XMLData]
,[GUIDKey]
,[ModifiedDate]
,[TextData]
,[XrefItemId]
,[ParentItemId]
,[Lang]
,[UserId]
,[LegacyItemId]
FROM [dbo].[NBrightBuy]
where portalid = 0
and (
(([xmldata].value('(genxml/billaddress/genxml/textbox/firstname)[1]', 'nvarchar(max)') like '%wolf%' collate sql_latin1_general_cp1_ci_ai ))
or (([xmldata].value('(genxml/billaddress/genxml/textbox/lastname)[1]', 'nvarchar(max)') like '%wolf%' collate sql_latin1_general_cp1_ci_ai ))
or (([xmldata].value('(genxml/shipaddress/genxml/textbox/postalcode)[1]', 'nvarchar(max)') like '%wolf%' collate sql_latin1_general_cp1_ci_ai ))
or (([xmldata].value('(genxml/shipaddress/genxml/textbox/email)[1]', 'nvarchar(max)') like '%wolf%' collate sql_latin1_general_cp1_ci_ai ))
or (([xmldata].value('(genxml/ordernumber)[1]', 'nvarchar(max)') like '%wolf%' collate sql_latin1_general_cp1_ci_ai ))
)

on over 8000 orders, 22sec. Which would be OK.

[DNNMonster]

I agree it will solve slow order searches for most systems and you could also reduce or eliminate the wildcard usage. In the largest systems I think one still has to consider adopting the same approach you've implemented for products in an attempt to ensure all data points that are a part of a where clause, join or sort come from an indexed column rather than xml data values. I do not believe there is a way around this but I am open to correction.

[leedavi]

After investigation using exists will be faster...

SELECT TOP (1000) [ItemId]
,[PortalId]
,[ModuleId]
,[TypeCode]
,[XMLData]
,[GUIDKey]
,[ModifiedDate]
,[TextData]
,[XrefItemId]
,[ParentItemId]
,[Lang]
,[UserId]
,[LegacyItemId]
FROM [dbo].[NBrightBuy]
where portalid = 0
and typecode = 'order'
and
(
[xmldata].exist('//genxml/billaddress/genxml/textbox/*[fn:contains(text()[1], "wolf")]') = 1
or [xmldata].value('(genxml/ordernumber)[1]', 'nvarchar(max)') like '%wolf%' collate sql_latin1_general_cp1_ci_ai
)

In fact I am actually surprised how quick it is. I think this is the way to go for all unindexed value searches in future.