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.