<SPAN lang=EN-GB style="mso-ansi-language: EN-GB">Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

<SPAN lang=EN-GB style="mso-ansi-language: EN-GB"><o:p> </o:p>

<SPAN lang=EN-GB style="mso-ansi-language: EN-GB">We have major performance issues while searching products in our shop.<o:p></o:p>

<SPAN lang=EN-GB style="mso-ansi-language: EN-GB">In an earlier BVC4 and BVC5 versions we used full text search.<o:p></o:p>

<SPAN lang=EN-GB style="mso-ansi-language: EN-GB">This gave us an enormous search boost.<o:p></o:p>

<SPAN lang=EN-GB style="mso-ansi-language: EN-GB"><o:p> </o:p>

<SPAN lang=EN-GB style="mso-ansi-language: EN-GB">We want to use the full text search on the new BVC 5.3.2 version.<o:p></o:p>

<SPAN lang=EN-GB style="mso-ansi-language: EN-GB"><o:p> </o:p>

<SPAN lang=EN-GB style="mso-ansi-language: EN-GB">We saw a new stored procedure was used in the latest version (bvc_Product_StoreSearch_s)

This SP is totally different form earlier versions. This version is the slowest version we ever seen in BVC. A search is slow as 5 – 8 seconds to return records. We have over 15.000 items in the shop. We have a dedicated SQL 2005 server.<o:p></o:p>

<SPAN lang=EN-GB style="mso-ansi-language: EN-GB"><o:p>
Browsing the shop by category is just fine. Nice performance. (http://www.licht-geluid.nl/shop2/)
</o:p>

<SPAN lang=EN-GB style="mso-ansi-language: EN-GB">Can someone give us some hints how to (re)build the bvc_Product_StoreSearch_s SP to search against the full text products database?

Ebijs.<o:p></o:p>

Depending on the size of your catalog the searches can be slow. SQL server is doing a textual search through every record in your database. Fields such as description can greatly increase the search time.

Yes, in the future we hope to implement support for full text indexing, and moving to a separate "store search" stored proc was the first step. This way the store search can be changed independently of other parts of the store.

If you are getting very slow results with only 298 products then it sounds to me like you might be on an overloaded shared server. The Long Description (if they are really long) is what will kill performance, but there is no reason with only 298 products why you would be having slow results. We have had tens of thousands of products with snappy results.

How many items are you returning per page?

I'm also using BVC 5.3.2 for an e-commerce site that has about 9,700 rows in the bvc_Product table and about 17,000 rows in the bvc_ProductChoiceCombinations table.

When I run a simple keyword search (as shown in the code below) directly in Microsoft SQL Server 2005 Management Studio against SQL Server 2005 Developer Edition on my personal PC it takes 4 seconds to complete and returns about 15 rows matching.

This is on Windows XP Pro SP3 image running in a Microsoft VirtualPC image of 2GB RAM on a host that has 4GB RAM running Vista Business 64bit SP1 on an Intel Core 2 Duo 6300 CPU @ 1.86 GHz.

I then run this same query on the same database running on the customer's hosted server which has only 1GB RAM on Windows Server 2003 R2 SP2 on an Intel Xeon E5450 @ 3.00 GHz. The database is SQL Server 2005 Enterprise Edition. This machine is being hosted on a VMWare ESX server so I don't know how much of the CPU this particular virtual machine is getting from the host provider.

When I run the same query below in that environment, it takes 6 seconds to complete. The entire page load time from the submit button being clicked to the page completely rendered takes about 10 seconds.

I ran the query through SQL Profiler and found that is loops about 12,000 times calling bvc_ProductAvailableAndActive stored procedure. Does this mean the bvc_Product_StoreSearch_s stored procedure that is initially called ends up calling the bvc_ProductAvailableAndActive stored procedure for each row in the product table joined with the combinations table? Or something similar?

Seems to me something is inefficient with this procedure.

-- Start of SQL code to execute stored procedure

DECLARE @return_value int

EXEC @return_value = [dbo].[bvc_Product_StoreSearch_s]
@Keyword1 = N'brown',
@SearchSKU = 1,
@SearchProductName = 1,
@SearchMetaDescription = 1,
@SearchMetaKeywords = 1,
@SearchShortDescription = 1,
@SearchLongDescription = 1,
@SearchKeywords = 1,
@StartRowIndex = 0,
@MaximumRows = 15

SELECT 'Return Value' = @return_value

GO

-- End of SQL code

Please respond with any help you can provide and if you want my SQL Profiler *.trc output file, I can provide it.

Thanks.

Andre

amarion,

The looping through product available and active was fixed in version 5.4. Searches will be much faster if you upgrade.