Using XML Index on NBRIGHTBUY Table

[ufoloko]

I was wondering if is possible to use XML Index into XMLData Column for the NBrightBuy Table. Im asking this because I have around 20000 items and site is getting slow.

Im looking that database dont have stored procedures querying into that XMLData field, i think site retrieve all xml data and module process inside that field, so I dont know if XMLIndex can really improve performance.

Anyone have any other option to improve performance ?

[DNNMonster]

The NBrightBuyIdx table is using a trigger approach that can be used as an example of how one might go about indexing the xml data points. Ultimately you'll have to promote any xml data point that you're querying against to a first class indexed db column if you're finding that the query is the bottleneck.

[ufoloko]

Thanks, I think Im having the bottleneck because im using direct query in database for import or update products, this because i have around 20000 products, and I have a process to sync from a local system database, updating stock, prices or description in some cases.

Im using specifically NBrightBuy_Update stored procedure.

Maybe you can help me on how to achieve this in a better way

[DNNMonster]

I'm not sure that I have a better way... A file import approach would be an alternate method but not necessarily better. If the site is slowing down just during your update process then I would break things up into reasonable chunks that don't result in crushing the data base for an extended period of time. It may just be that trying to do 20,000 product updates is too demanding for the server resources to handle gracefully in one go. There are other super smart humanoids here that may also have some ideas.

[leedavi]

The XML format is slow if you are trying to order by a value in there. Otherwise it's actually seems faster to get the data out (no proof of that). It's like a NoSQL DB in a relational DB. I've done testing on a database with 200,000 records and the access times were near the same with a database on 10,000 record.

If you are processing/importing each product and you have 20,000 that could be slow. Because on each import the IDX records are created for each product. The reason we have the IDX record, is for working speed, not saving 20,000 products.

Have a look at this: https://github.com/openstore-ecommerce/Developer-Documents/blob/master/SQL-Architecture.pdf

One method you could try is to build the XML for your records (including the IDX) in an import program, then update the database directly, that "might" be quicker. XML can be slow when being manipulated in SQL, it's not a problem when update a few records, but 20,000 is a lot of XML manipulations. You may also find the Memory of the server a bottle neck. DNN, SQL and OpenStore tend to be heavy on memory.

[leedavi]

Question: why are you doing this?

Could the work you need to complete be ran on the scheduler? I've used that in the past to update a lot of products and it seemed to work OK. But you obviously need to pick a time when the website is not being used as much.

[ufoloko]

Hi @leedavi thanks for your response and thanks for your time.

I need to upload a database from a local system to web.

So, I need to make the initial load for all products (~20000), so, i uploaded all products from CSV to a TEMP table with all needed fields. Then i created a procedure with a cursor to move from that table to NBrightBuy table, basically using procedure NBrightBuy_Update. This process is slow so i run with schedule taking 500 records each time, every hour. so i need about 40 hours or about 2 days to complete the process.

Then to keep in sync with local system, im taking every day just modified records (stock or prices), uploading to my TEMP Table, and then im running a similar procedure for update, with same Stored procedure NBrightBuy_Update. (this process is a little bit slow because i need to check first if the product exist to get the current ID, and same for categories).

What you think about the process ?

[leedavi]

Another approach could be to temporary disable the trigger (You'll need nobody else updating) Then do the update to both the IDX and the normal data,

That should be quicker, your not rebuilding the IDX, just updating.

[ufoloko]

About this approach, If I disable the trigger, how i can update the indexes later, with a cursor record by record ?

[leedavi]

A cursor would work. !n the case of an update use SQL, If you need a new index you'll need to create it, which will not save time.

But, it sounds like you need to sync the products.... therefore the changed products will normally be there?

From what you describe I think what you're doing sounds like a sensible way of doing this sync.

My first thought is that, can you implement a real time update. Can your local DB trigger a post to the OpenStore website? If it can, you can have an API plugin to receive the data and update. I suppose it depends on how your local DB gets update, if it's updated in a batch system this will give no time advantage.

[leedavi]

Usually if you're updating a few records at one time, there should be no problem. The system is designed to do that.

Check your sever CPU and Memory are not overloaded, I usually find adding more memory speeds things up.

[we have a rule of: minimum 4GB for a small e-commerce (1000 products), the maximum depends on usage of the website.]

Also, look at putting a limit on the memory used by SQL, so it doesn't take everything. There is a delay in SQL releasing memory when it's needed, so adding a limit sometimes helps with speed. Although depending on workload, this might not help.

[ufoloko]

Thanks for the information, i will try to use all your recommendations