0
\$\begingroup\$
  while (true) {
    const result = await sql.query(`
        SELECT TOP (1) *
        FROM  ${originalTable} 
        WHERE IdentifiedState = 0 AND Email <> '' AND AnalyticsAppID = ${AnalyticsAppID}
        ORDER BY InsertDate 
    `);

    if (result.setOfRecords.length == 0) {
        break
    }

    for (let i = 0; i < result.setOfRecords.length; i++) {
        const item = result.setOfRecords[i]
        let UserProperty = {}
        let DBEmail = item['Email'];

        UserFields.forEach(i => {
            let j = i;
            if (i == 'FirstName') { j = 'firstName'; }
            if (i == 'LastName') { j = 'lastName'; }
            if (i == 'Email') {
                item[i] = item[i].split(";")[0];
                item[i] = item[i].split(",")[0];
                item[i] = item[i].replace("$", "@");
                item[i] = item[i].toLowerCase()
                j = 'email';
            }
            UserProperty[j] = item[i];
        });

        if (validateEmail(UserProperty['email']) && UserProperty['email'].length > 10) {
            try {
              await analytics.identify({
                userId: UserProperty['email'],
                traits: UserProperty
              });
              console.log('Successful')
            }
            catch (error) {
              console.log('Error: ', error);
            }
            finally {
              console.log(originalTable, DBEmail, UserProperty['email']);
            }
        }

        await sql.query(`
          UPDATE ${originalTable} 
          SET   IdentifiedState = 1 
          WHERE AnalyticsAppID =  ${AnalyticsAppID} AND Email = '${DBEmail}' `
        );
    }
  }

I have this ETL script, but it ends up in a Timeout exception. I am wondering if there's a need to make the script stronger. Because it loops endlessly and I am wondering what's the best practices with these types of scripts.

\$\endgroup\$
4
  • 1
    \$\begingroup\$ Welcome to Code Review! When you say that "it ends up in a Timeout exception", what is "it"? Is the problem in one of the SQL queries? \$\endgroup\$ May 3, 2022 at 19:16
  • \$\begingroup\$ there's no error within the code, but doing a while loop like this sometimes end up in a timeout exception, probably because the server is getting a lot of calls. \$\endgroup\$
    – Sayaman
    May 4, 2022 at 0:22
  • 2
    \$\begingroup\$ So, you have a performance problem with the database? Show us your table and index definitions, and give us an approximate size of the table. \$\endgroup\$ May 4, 2022 at 0:25
  • \$\begingroup\$ What SQL library is this? Please show complete, runnable code. Thanks. \$\endgroup\$
    – ggorlen
    May 5, 2022 at 4:28

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.