Skip to content

Instantly share code, notes, and snippets.

@meilechh
Last active August 13, 2019 14:05
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save meilechh/2174fcb03686ae8c26ad11521892f3cc to your computer and use it in GitHub Desktop.
Save meilechh/2174fcb03686ae8c26ad11521892f3cc to your computer and use it in GitHub Desktop.
const oracledb = require('oracledb');
async function runScript() {
const tableName = "TESTING_NODEJS";
let connection;
async function prepareData() {
const statement = `create table ${tableName} AS
WITH users AS (
SELECT 'Roland' AS first_name, 'Higgins' AS last_name, 29 AS age FROM dual
UNION
SELECT 'Chelsea', 'Watson', 20 FROM dual
UNION
SELECT 'Amber', 'Ryan', 27 FROM dual
UNION
SELECT 'Michael', 'Jones', 24 FROM dual
UNION
SELECT 'Tiana', 'Jones', 28 FROM dual
UNION
SELECT 'Violet', 'Sullivan', 20 FROM dual
UNION
SELECT 'Dexter', 'Wilson', 29 FROM dual
UNION
SELECT 'Sophia', 'Carter', 27 FROM dual
UNION
SELECT 'Sienna', 'Johnson', 29 FROM dual
UNION
SELECT 'Dexter', 'Carroll', 19 FROM dual
UNION
SELECT 'Sarah', 'Casey', 20 FROM dual
UNION
SELECT 'Sarah', 'Harris', 20 FROM dual
UNION
SELECT 'Amelia', 'Williams', 27 FROM dual
UNION
SELECT 'Michael', 'Johnston', 27 FROM dual
UNION
SELECT 'Vincent', 'Campbell', 22 FROM dual
UNION
SELECT 'David','Morrison', 23 FROM dual
UNION
SELECT 'Sydney', 'Barrett', 20 FROM dual
UNION
SELECT 'Lucy', 'Richards', 19 FROM dual
UNION
SELECT 'Maximilian', 'Bailey', 28 FROM dual
UNION
SELECT 'Elian', 'Parker', 24 FROM dual
UNION
SELECT 'Olivia', 'Richards', 23 FROM dual
UNION
SELECT 'Paul', 'Nelson', 21 FROM dual
UNION
SELECT 'Richard', 'Henderson', 23 FROM dual
UNION
SELECT 'Sabrina', 'Montgomery', 28 FROM dual
UNION
SELECT 'Jessica', 'Turner', 30 FROM dual
UNION
SELECT 'Paige', 'Rogers', 25 FROM dual
UNION
SELECT 'Walter', 'Bennett', 18 FROM dual
UNION
SELECT 'Abraham', 'Clark', 18 FROM dual
UNION
SELECT 'Edwin', 'Myers', 30 FROM dual
UNION
SELECT 'Maddie', 'Rogers', 28 FROM dual
UNION
SELECT 'Daniel', 'Thomas', 21 FROM dual
UNION
SELECT 'Chester', 'Brooks', 29 FROM dual
UNION
SELECT 'Dale', 'Williams', 18 FROM dual
UNION
SELECT 'Aiden', 'Alexander', 18 FROM dual
UNION
SELECT 'Eddy', 'Martin', 22 FROM dual
UNION
SELECT 'Dainton', 'Roberts', 28 FROM dual
UNION
SELECT 'Michael', 'Warren', 20 FROM dual
UNION
SELECT 'Rosie', 'Jones', 21 FROM dual
UNION
SELECT 'Chester', 'Johnson', 25 FROM dual
UNION
SELECT 'Paul', 'Henderson', 18 FROM dual
UNION
SELECT 'Frederick', 'Nelson', 23 FROM dual
UNION
SELECT 'Adele', 'Carroll', 27 FROM dual
UNION
SELECT 'Agata', 'Perkins', 21 FROM dual
UNION
SELECT 'Thomas', 'Harris', 20 FROM dual
UNION
SELECT 'Amber', 'Reed', 24 FROM dual
UNION
SELECT 'Harold', 'Edwards', 25 FROM dual
UNION
SELECT 'Leonardo', 'Henderson', 25 FROM dual
UNION
SELECT 'Lucas', 'Thomas', 26 FROM dual
UNION
SELECT 'Paige', 'Davis', 27 FROM dual
UNION
SELECT 'Lucia', 'Douglas', 23 FROM dual
UNION
SELECT 'Dale', 'Sullivan', 19 FROM dual
UNION
SELECT 'Agata', 'Andrews', 20 FROM dual
UNION
SELECT 'Lana', 'Cole', 26 FROM dual
UNION
SELECT 'Aiden', 'Grant', 25 FROM dual
UNION
SELECT 'Natalie', 'West', 30 FROM dual
UNION
SELECT 'Derek', 'Grant', 26 FROM dual
UNION
SELECT 'Arthur', 'Watson', 19 FROM dual
UNION
SELECT 'Lucy', 'Wilson', 18 FROM dual
UNION
SELECT 'Adelaide', 'Dixon', 26 FROM dual
UNION
SELECT 'Frederick', 'Baker', 19 FROM dual
UNION
SELECT 'Vanessa', 'Kelly', 29 FROM dual
UNION
SELECT 'Sarah', 'Rogers', 30 FROM dual
UNION
SELECT 'Adison', 'Gibson', 30 FROM dual
UNION
SELECT 'Lydia', 'Kelly', 29 FROM dual
UNION
SELECT 'Henry', 'Russell', 30 FROM dual
UNION
SELECT 'William', 'Bailey', 22 FROM dual
UNION
SELECT 'Cherry', 'Martin', 27 FROM dual
UNION
SELECT 'Rebecca', 'Hill', 20 FROM dual
UNION
SELECT 'Chelsea', 'Taylor', 25 FROM dual
UNION
SELECT 'Daryl', 'Richardson', 21 FROM dual
UNION
SELECT 'Bruce', 'Warren', 21 FROM dual
UNION
SELECT 'Edward', 'Chapman', 18 FROM dual
UNION
SELECT 'Sydney', 'Cooper', 18 FROM dual
UNION
SELECT 'Aldus', 'Thompson', 24 FROM dual
UNION
SELECT 'Amber', 'Chapman', 20 FROM dual
UNION
SELECT 'Fiona', 'Davis', 24 FROM dual
UNION
SELECT 'Isabella', 'Morgan', 19 FROM dual
UNION
SELECT 'Aida', 'Taylor', 28 FROM dual
UNION
SELECT 'Isabella', 'Casey', 20 FROM dual
UNION
SELECT 'Garry', 'Robinson', 28 FROM dual
UNION
SELECT 'James', 'Morgan', 20 FROM dual
UNION
SELECT 'Paul', 'Jones', 21 FROM dual
UNION
SELECT 'Lydia', 'Johnston', 30 FROM dual
UNION
SELECT 'Ryan', 'Cameron', 29 FROM dual
UNION
SELECT 'John', 'Anderson', 28 FROM dual
UNION
SELECT 'Savana', 'Wright', 30 FROM dual
UNION
SELECT 'Edward', 'Spencer', 24 FROM dual
UNION
SELECT 'Dexter', 'Ross', 30 FROM dual
UNION
SELECT 'Brooke', 'Johnson', 18 FROM dual
UNION
SELECT 'Rubie', 'Edwards', 19 FROM dual
UNION
SELECT 'Adison', 'Smith', 19 FROM dual
UNION
SELECT 'Cadie', 'Nelson', 29 FROM dual
UNION
SELECT 'Tyler', 'Parker', 19 FROM dual
UNION
SELECT 'Alina', 'Barnes', 18 FROM dual
UNION
SELECT 'Belinda', 'Harper', 28 FROM dual
UNION
SELECT 'Natalie', 'Foster', 18 FROM dual
UNION
SELECT 'Darcy', 'Douglas', 22 FROM dual
UNION
SELECT 'Frederick', 'West', 24 FROM dual
UNION
SELECT 'Joyce', 'Morgan', 20 FROM dual
UNION
SELECT 'Catherine', 'Farrell', 30 FROM dual
)
SELECT * from users`;
try {
try {
await connection.execute(`DROP table ${tableName}`);
}
catch (error) {
console.log(error);
}
//await connection.execute("ALTER SESSION SET tracefile_identifier='app.js-test-with-binds' SQL_TRACE=TRUE");
/*await connection.execute(`
BEGIN
DBMS_MONITOR.session_trace_enable(waits=>TRUE, binds=>TRUE);
END;`)*/
await connection.execute(statement);
console.log(`Finished creating ${tableName} table`);
} catch (err) {
console.error(err);
}
}
const dbConfig = {
user: process.env.DB_USER || process.argv[2],
password: process.env.DB_PASS || process.argv[3],
connectString: process.env.DB_CONNECTION || process.argv[4]
}
if(!connection) {
connection = await oracledb.getConnection(dbConfig);
}
try {
await prepareData();
const users = [
{
lastName: "Jones",
age: 21
}, {
lastName: "Johnson",
age: 27
}, {
lastName: "Casey",
age: 21
}, {
lastName: "Douglas",
age: 21
}, {
lastName: "Thompson",
age: 22
}, {
lastName: "Anderson",
age: 28
}, {
lastName: "Russell",
age: 28
}, {
lastName: "Ryan",
age: 27
}, {
lastName: "Sullivan",
age: 20
}, {
lastName: "Morgan",
age: 19
}, {
lastName: "Henderson",
age: 23
}, {
lastName: "Warren",
age: 29
}, {
lastName: "Grant",
age: 29
}, {
lastName: "Morgan",
age: 29
}, {
lastName: "Rogers",
age: 30
}, {
lastName: "Parker",
age: 24
}, {
lastName: "Chapman",
age: 20
}, {
lastName: "Nelson",
age: 29
}, {
lastName: "Henderson",
age: 18
}, {
lastName: "Davis",
age: 24
}
];
const query = `WITH users /* testing 115 */ AS (
${Array(30).join(0).split(0).map((item, i) =>
`${i == 0 ? `` : ` UNION `} SELECT :age${i} AS age, :last_name${i} AS last_name FROM dual`).join('')}
)
SELECT * from ${tableName} join users using (last_name, age)`;
async function runQuery(users) {
try {
const binds = [];
users.forEach(user => {
binds.push(user.age);
binds.push(user.lastName);
});
Array(30 - users.length).join(0).split(0).forEach((item, i) => {
binds.push(-9);
binds.push('YMMUD');
});
console.log("Running query");
const result = await connection.execute(query, binds, {outFormat: oracledb.OBJECT});
console.log(result);
} catch (error) {
console.error(error);
}
}
for (let j = 0; j < 100; j++) {
setTimeout(() => {
const array =[];
users.forEach(() => {
const randomNumber = Math.floor(Math.random() * 19) + 1;
array.push(() => runQuery(users.sort(() => randomNumber).slice(0, randomNumber)));
});
array.reduce((cur, next) => cur.then(next), Promise.resolve()).then(() => {
console.log("Finished all queries")
});
}, j* 10000);
}
} catch (err) {
console.error(err);
}
}
runScript();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment