search

sagiegurari / simple-oracledb

Extend capabilities of oracledb with simplified API for quicker development.
Apache License 2.0
36 stars 4 forks source link

increase select query performance #10

Closed behroozshafiabadi closed 8 years ago

behroozshafiabadi commented 8 years ago

i have lot of rows in table(~1000). when i use "connection.query" its take about 13 sec to back results. inestead when i set streamResults to true,the time dose not changed a lot. what should i do to increase performance ?

sagiegurari commented 8 years ago

13 sec is a lot. I wonder how much such a query takes if you run it via sqlplus, to see the overhead. does it have LOBs?

behroozshafiabadi commented 8 years ago

wow... its take same time in sqlplus no it dosen't contain any CLOB or BLOB columns in general when i must use streamResult instead of query result set ?

sagiegurari commented 8 years ago

Ok, so first of all if it takes 13 seconds in sqlplus, the issue is the amount of data or the performance of the select there. can't help with that and it is not an issue with the connection.query function.

As for the stream, the stream allows you instead of getting an array of row objects, to stream the data so you listen to the data via 'data' events. That is good when you query huge amount of data, and you don't want to hold it in memory, just want to proxy it to another stream. For example you query it and stream it to a file or http response. So instead of taking up huge amount of nodejs memory, you just hold 1 row in the memory and pass it to the next output stream and that's it. See example: https://github.com/sagiegurari/simple-oracledb#usage-query

behroozshafiabadi commented 8 years ago

yes of course its lag is about bad structure query....but Just for my assurance is that any way to increase this type of query( huge data results ) performance ??

sagiegurari commented 8 years ago

on the client maybe add/remove stuff on the 'where' but really can't say. maybe on the oracle db server side (hints/network/index/....)