In investigating some performance issues in my project, I found that my named queries, which I just started using/converting to, were running very slowly. If I have a client window open for my project, the difference between runQuery and runNamedQuery is very large. If I do not have a client window open for my project, the difference is still significant, but much less noticeable. Please see my test below:
# start test code
totalNamedQueryMs = 0
totalQueryMs = 0
for testNumber in range(10):
print('test%d' % testNumber)
start = system.date.now()
for i in range(100):
test = system.db.runNamedQuery('select1', {})
msElapsed = system.date.millisBetween(start, system.date.now())
print(' runNamedQuery took %d ms' % msElapsed)
totalNamedQueryMs += msElapsed
start = system.date.now()
for i in range(100):
test = system.db.runQuery('''select 1''')
msElapsed = system.date.millisBetween(start, system.date.now())
print(' runQuery took %d ms' % msElapsed)
totalQueryMs += msElapsed
print('avg seconds to run 100 "select 1" using runNamedQuery: %2f' % (totalNamedQueryMs / 10000.0))
print('avg seconds to run 100 "select 1" using runQuery: %2f' % (totalQueryMs / 10000.0))
Results while client window is open (and running normal queries, currently ~30/second)
test0
runNamedQuery took 28771 ms
runQuery took 103 ms
test1
runNamedQuery took 28051 ms
runQuery took 102 ms
test2
runNamedQuery took 29323 ms
runQuery took 120 ms
test3
runNamedQuery took 29915 ms
runQuery took 95 ms
test4
runNamedQuery took 31524 ms
runQuery took 109 ms
test5
runNamedQuery took 27637 ms
runQuery took 105 ms
test6
runNamedQuery took 27647 ms
runQuery took 89 ms
test7
runNamedQuery took 28750 ms
runQuery took 103 ms
test8
runNamedQuery took 27985 ms
runQuery took 92 ms
test9
runNamedQuery took 29025 ms
runQuery took 93 ms
avg seconds to run 100 "select 1" using runNamedQuery: 28.862800
avg seconds to run 100 "select 1" using runQuery: 0.101100
Results while client window is not open:
test0
runNamedQuery took 550 ms
runQuery took 74 ms
test1
runNamedQuery took 561 ms
runQuery took 71 ms
test2
runNamedQuery took 565 ms
runQuery took 73 ms
test3
runNamedQuery took 531 ms
runQuery took 74 ms
test4
runNamedQuery took 547 ms
runQuery took 70 ms
test5
runNamedQuery took 535 ms
runQuery took 72 ms
test6
runNamedQuery took 556 ms
runQuery took 73 ms
test7
runNamedQuery took 535 ms
runQuery took 75 ms
test8
runNamedQuery took 536 ms
runQuery took 73 ms
test9
runNamedQuery took 542 ms
runQuery took 70 ms
avg seconds to run 100 "select 1" using runNamedQuery: 0.545800
avg seconds to run 100 "select 1" using runQuery: 0.072500