Transferring data between databases

I built a bit of code that transfers data from the Store/Products database to my site’s OptionalProducts database. Some other code downstream uses the updates in the OptionalProducts database. However, the transfers do not finish causing failure for that downstream code to use the updates. Obviously my code is super inefficient and I do not know how to make it work efficiently.

I tried running this as Async functions but still very slow. I tried putting the async code inside the onReady async function. The page did not open up until the database was updated, which took about 2 minutes on my computer. SOOooo SLOOOooow!

I scoured the forum for some ideas how to do this better. What I have coded so far is working but unacceptably slow . I am willing to completely gut what I have. Is there a better way to do this?

Here below is the function in question. It starts by getting a list of all items in the OptionalProducts database and uses that as the master index (it has 7 fields and 38 rows of data)

function getProductName () {
wixData . query ( “OptionalProducts” )
. find ()
. then ( ( OPresults ) => {
let len = OPresults.items.length
for(let i = 0 ; i<len ; i ++){

Within that index of 38 items, I query the Stores/Products database, including " collections " because the data I am transferring is the names of certain collections associated with specific product names found in both the Store/Products and the OptionalProducts databases.

wixData . query ( “Stores/Products” )
. include ( “collections” )
. eq ( “name” , OPresults.items[i ]. productName ) //find OP productName in “name” field of SP database
. find ()
. then ( ( SPresults ) => {

Each collection field can contain from 1 to 3 items, so I set up a “get” and “update” sequence after first finding which is “Instrument–”, “Gnss–”, or “Data Collector–”.

      **let**  len2  =  SPresults.items [ 0 ]. collections.length   /   

for(let j = 0 ; j<len2 ; j ++){
wixData . get ( “OptionalProducts” , OPresults.items[i ]. _id )
. then ( ( OPitems ) => {
if ( SPresults.items [ 0 ]. collections[j ]. name == “Instrument–” ) {
OPitems.unit = “instrument”
} else if ( SPresults.items [ 0 ]. collections[j ]. name == “Gnss–” ) {
OPitems.unit = “gnss”
} else if ( SPresults.items [ 0 ]. collections[j ]. name == “Data Collector–” ) {
OPitems.unit = “dataCollector”
}
wixData . update ( “OptionalProducts” , OPitems );
})
}
})
}
})
}

It is not surprising, that your code is very very slow.

You have loop in a loop, combined with query-function in a bad mix!

Take a look onto your own code…

function getProductName () {  
  wixData.query("OptionalProducts")
  .find()
  .then( (OPresults) => {let len = OPresults.items.length
    for(let i=0; i<len; i++){  
      wixData.query("Stores/Products")
      .include("collections")
      .eq("name", OPresults.items[i].productName)  
      .find()
      .then( (SPresults) => {let len2 = SPresults.items[0].collections.length 
         for(let j=0; j<len2; j++){ 
           wixData.get("OptionalProducts", OPresults.items[i]._id)
          .then( (OPitems) => {
             if (SPresults.items[0].collections[j].name == "Instrument--" ){
                 OPitems.unit = "instrument";
             } else if (SPresults.items[0].collections[j].name=="Gnss--" ){
                 OPitems.unit = "gnss";
             } else if (SPresults.items[0].collections[j].name=="Data Collector--") {
                 OPitems.unit = "dataCollector";
             } else {} 
             wixData.update("OptionalProducts", OPitems);
           })
         }        
      })
    }
  })
}

For in a for-loop!

Get rid of the loops.

Search for another structure of your code.

Try to work more with ASYNC-AWAIT instead of then()-Methods, could also help you!

Better, but still not good enough…

import wixData from 'wix-data';

async function getProductName () {  
    let OptionalProducts = await wixData.query("OptionalProducts").find()
    let len = OptionalProducts.items.length; console.log("Res-Length: ", len);
  
    let PRODUCTS = await wixData.query("Stores/Products")
    .include("collections")
    .eq("name", OptionalProducts.items[i].productName)  
    .find()

    let len2 = PRODUCTS.items[0].collections.length; console.log("len2: ", len2); 
    console.log("PRODUCTS: ", PRODUCTS);  

    //looping two ARRAYS against each other....
    for(let i=0; i<len; i++){  
        for(let j=0; j<len2; j++){ 
            wixData.get("OptionalProducts", OptionalProducts.items[i]._id)
            .then((OPitems)=>{
                if (PRODUCTS.items[0].collections[j].name == "Instrument--" ){
                    OPitems.unit = "instrument";
                } 
                else if (PRODUCTS.items[0].collections[j].name=="Gnss--" ){
                    OPitems.unit = "gnss";
                }
                else if (PRODUCTS.items[0].collections[j].name=="Data Collector--") {
                    OPitems.unit = "dataCollector";
                }
                else {} 
                wixData.update("OptionalProducts", OPitems);
           })
        }        
    }
}

Are the red marked code-parts essential?
Try to improve it more!

Some totaly another way of thinking (example)…
Some example-exerpt of my own codings…(you have an already prepared shop) ?

import wixData from 'wix-data';

// Get all PRODUCTS-DATA----------------------------------------------------------------
export async function get_MainData(DATABASE,limitValue,FIELD1,VALUE1) {
    return wixData.query(DATABASE) 
    .limit(limitValue)
    .eq(FIELD1, VALUE1)
    .find()
    .then(async(res)=>{console.log("RES---: ", res);
        let items = await res.items; console.log("Items---: ", items);
        let productIDs = await res.items[0].products; console.log("Product-IDs--: ", productIDs);
        let products = await get_SubMenuProducts(productIDs); console.log("Products--: ", products);
        return (products);

        function get_SubMenuProducts(productIDs){
            return wixData.query("Stores/Products")
            .limit(limitValue)
            .hasSome("_id", productIDs)
            .find()
            .then(async(res)=>{//console.log("RES-PRODUCTS: ", res);
                if (res.items.length > 0) {//console.log("Data found!");
                    let items = await res.items;                     
                    for (let i = 0; i < items.length; i++) {items[i]._id = i.toString();}
                    return (items);
                }
                else {console.log("No data found!"); return ([]);}
            }).catch((err)=>{console.log(err); return [];});
        }        
    }).catch((err)=>{console.log(err); return [];});   
}
// Get all PRODUCTS-DATA-----------------------------------------------------------------------

Paste this on your BACKEND and put the following on your FRONTEND, to call the backend-function…

Replace placeholders with your own data…

import wixData from 'wix-data';
import {get_MainData} from 'backend/xxxxxxxxxxxxxx.jsw'


let productsDATA = await get_MainData("Stores/Products", limitValue, FIELD1, VALUE1);
console.log("Products: ", productsDATA)
1 Like

Thanks for your response. For the past several weeks I have been laboring over this question of how to copy selected data from one database to another. My approach of looping inside of loops makes perfect sense to me, and I have known from the start it would be very inefficient. My hashing algorithm is not very efficient, and I can see whatever hashing Wix uses to find the data I call for in databases is likewise not very fast.

I have experimented with several other approaches, including “Await” to run synchronously, and including database avoidance, but so far nothing seems to work better. I continue to look for other ways to do what I need.

I appreciate your comments and your response as yet another approach I could try. First I need to figure out what you are suggesting…unfortunately my own lack of JavaScript programming experience and skill keeps me bogged down by having to take time to teach myself new ways I see in code such as yours. One issue it all the dang shortcuts most experienced coders take in their examples. I wish there was a standard that all had to adhere to, but there is not. Coding in JavaScript in general, and in the Wix version specifically, is a bit like the “Wild West” where anything goes. I probably need an expended course in JavaScript concepts to get this.

Yes, for the method I was using the red marked code is key. It IS the update I am making to the database so I need it for my coding approach to work at all.

Going crazy! I need another beer!

:grin: Going crazy! I need another beer! :grin:

I am creating my own Mega-Query, just right now, because exact of all these mind blowing situation, when working with Wix-Data, just like you describe it here.

To solve all these PROBLEMS in FUTURE in a much more efficient way → in near FUTURE i will use my own generated → MEGA-QUERY-SYSTEM, which will make it possible to solve your problem in → seconds!

I will remember your POST and paste your situation into the MEGA-QUERY-SYSTEM aswell.

Perhaps i will need a little bit of your help, especialy → if possible, i would take a look onto your SETUP, to understand your issued situation more → to be able to investigate it even much better.

We are always working with the same STANDARD and BORING (not very efficient) QUERIES → presented by the Velo-API.


Without question!
They are good and easy to understand (more or less) query-examples, shown in the Velo-API-Docs, but they are very inefficient when it comes to more complex data processing. So you have to build chained multiple single-queries, to get your result.

And so the idea was born to create a highly efficient MEGA-QUERY from several individual data queries, which can do absolutely everything, whereby it is completely irrelevant whether you have DIRECT filter functions in your query (yes, that’s right → multiple filter functions ) want to incorporate, or want to adjust your limitation directly, or even want to operate a paginator based on the resulting data of your query.

You can do it within seconds without having to deal with the entire topic of DATA-QUERIES in detail. Just type in some values and absolutely everything works → magicaly and full automaticaly.

Another example would be:
You want to fill a dropdown with values ​​from a database.
This requires a few separate individual steps to get the desired result.

But it is also much easier and with only 1 or 2 more input values ​​in your query, which unlock another function (fully automatic!) And your MEGA-QUERY spits out your desired RESULT without creating several individual queries and nest one behind the other.

Can you follow me ?

You can easily copy data from one Collection to another using this simple backend function.

const wixData = require('wix-data')

export const copyDatasets = async (datasetA, datasetB) => {
  const query = await wixData.query(datasetA).find()
  const itemsA = query.items
  const insertB = await wixData.bulkInsert(datasetB, itemsA)
  console.log(insertB)
}

Just provide the two collections as parameters and you will copy one dataset to another.

And if you need to copy specific items, you just add a .eq() method to the query.

1 Like

@Bruno Prado
Nice one Bruno!

But also here, we always generating new individual queries for individual situations, again and again and again.

Also will try to include your example Bruno!

And here an example how our daily generated queries looks like…

Queriying and queriying and queriying :grin: and lets explode the size of your code.
I don’t like this anymore →

------------> TIME FOR ------------->

1 Like

@russian-dima It is a little hard for me to follow your concept. But it sounds like you also have found database queries inefficient and are developing a way to make them more efficient (what you are calling Mega Query), is that correct?

Personally I believe Wix needs more efficient hashing methods to find intersections quicker. Is that what you are working on?

You wrote you might like to see my “Setup”. Not sure what you mean. Do you mean you want to see my code?

@jimyaccino
…developing a way to make them more efficient (what you are calling Mega Query), is that correct?

EXACTLY!

With my → MEGA-QUERY you simply can add or remove filters, without redesign your whole query (again & again & again).
With my MEGA-QUERY you will have → JUST → ONE ← QUERY in your whole project (backend-Version).
With my MEGA-QUERY, you will be able to query everything and get every result you want, no matter if you activate → (limitation/skiping/filters and some more stuff).

Also you will be able to activate built-in-functions, whcih will allow you to get for example prepared DATA for → REPEATERS/DROPDOWNS/MEDIA-GALLARIES & much more.

With my MEGA-QUERY, you will be able to reduce your CODE-SIZE to about 25-50%.

And some more anvanced stuff :grin::grinning:, which i don’t wanna talk about here :wink:

So yes, the MEGA-QUERY itself is 10x-20x bigger & more complex than a normal one ---------------> BUT ----> YOU can use it for everything !!!:sunglasses::heart_eyes::hugs:

Never having headaches again about → how to generate my query.

  1. Will i do it as a returning function?
  2. Will i need to use skip + limit, because i have a bigger DB ?
  3. Will i be able to connect it for example to a PAGINATOR or REPEATER ?
  4. And so on…

Personally I believe Wix needs more efficient hashing methods to find intersections quicker. Is that what you are working on?

CORRECT!

→ Multi-Universal-Mega-Query ←

:stuck_out_tongue_winking_eye::smiling_imp::stuck_out_tongue_winking_eye::smiling_imp::stuck_out_tongue_winking_eye::smiling_imp::stuck_out_tongue_winking_eye::smiling_imp::stuck_out_tongue_winking_eye::smiling_imp::stuck_out_tongue_winking_eye::smiling_imp::stuck_out_tongue_winking_eye::smiling_imp::stuck_out_tongue_winking_eye::smiling_imp::stuck_out_tongue_winking_eye::smiling_imp::stuck_out_tongue_winking_eye::smiling_imp::stuck_out_tongue_winking_eye::smiling_imp::stuck_out_tongue_winking_eye:

First → Prototype already working!:grin:

You wrote you might like to see my “Setup”. Not sure what you mean. Do you mean you want to see my code?

If you could leave an e-mail in your PROFILE, maybe i would contact you, if i won’t be able to understand your issue to 100%, but since Bruno also throw some good stuff, perhaps all will work without to disturb you.

Just need some time.

@Bruno - → Are you interested on a colorobative project?
If so → contact me, you will find my contact in my profile! :wink:

@Bruno Prado
You got an e-mail :wink:

2 Likes

@jimyaccino
Did you already found your solution?

@russian-dima Not really. I tried a few different approaches. All seem to take about 45 seconds on my PC. Since I have to copy specific information from the collections multi-reference items, the solution is much more complicated that meets the eye, and I believe the multi-reference collection items lookups and parsing is what makes this inefficient.

I would like to process these database updates every time a user opens the page. That guarantees any changes made to the database will be picked up on the page data. However, due to the running times of 45 seconds, I am concerned the user will abandon my page long before the data is updated. I therefore decided to remove the suto updating from my page, and move it to another dummy page, which I will run every time the store products database is manually. My only concern is that if we manually update the store database and forget to run this bit of code, visitors will not get the best information.

I have spent week or so this already and I have convinced myself there is no way to make this transfer of data significantly more efficient…except of course that your mega method might do it. I suspect your method still needs to get all the data ahead of time into a JSON object of something like that so it can be used later.

Here is what I have now (excluding a different database “images” update which is efficient). I left my console.logs (I use them profusely during development) and my //notes to self in the code shown. If you see anything that can significantly improve a 45 second delay, I most certainly will try your suggestion. I am hoping for a 30 seconds or so improvement,

Once I am done and satisfied, I expect to move all database work to the backend.

Thanks,

import wixData from ‘wix-data’ ;

$w ( ‘#information’ ). text = “Updating databases now … please wait”
//console.log (“calling opener”)
opener ()

async function opener () {
await transferUnitData () //updates OptionalProducts database with “unit” field data
console . log ( “done transferring data” )
$w ( ‘#information’ ). text = “Completed. You may exit this page”
}

async function transferUnitData () {
console . log ( “in transferUnitData” )
let SPresults = await wixData . query ( “Stores/Products” )
. include ( “collections” )
. contains ( “name” , “–” )
. find ()
console . log ( "list of products = " , SPresults.items.length )
for(let i = 0 ; i < SPresults . items.length ; i ++){
console . log ( "search for product = " , SPresults.items[i ]. name )
let OPresults = await wixData . query ( “OptionalProducts” )
. eq ( “productName” , SPresults.items[i ]. name )
. find ()
console . log ( "…records to update = " , OPresults.items.length )
for(let j = 0 ; j < OPresults . items.length ; j ++){
let OPitems = await wixData . get ( “OptionalProducts” , OPresults . items[j ]. _id )
let uni = SPresults . items[i ]. collections [ 0 ]. name [ 0 ]. toLowerCase () + SPresults . items[i ]. collections [ 0 ]. name . slice ( 1 , - 2 )
uni = uni . split ( " " ). join ( “” )
OPitems . unit = uni ;
await wixData . update ( “OptionalProducts” , OPitems );
}
console . log ( “…completed this update” )
}
}

@jimyaccino
Will surely take a look onto it the next days.
Stay tuned…