search

sqlanywhere / node-sqlanywhere

SAP SQL Anywhere Database Client for Node
Apache License 2.0
39 stars 37 forks source link

response time on complex queries. #37

Open leuname93 opened 4 years ago

leuname93 commented 4 years ago

What is the best way to make complex queries? in other words, when the query includes several joins, the library does not respond or send errors, it only stays processing. I made a query that takes 30 min to response.

DanCummins-SAP commented 4 years ago

Hi leuname93 ,

I am not sure if you are saying you expect the query to take a long time, or you hoped there would be a way to make it go faster.

In answer to te first question, "how do I construct complex queries that take a long time" you have taken the right approach - join a bunch of tables with no matching key. A colleague suggested to use system tables like SYSTABLE, SYSCOLUMN, ...

If you were asking how to make it go faster, then you need to have indexes on the columns you are matching.

Are you trying to do load testing of your application? You could create multiple node clients running "SELECT ... FROM Customer, Products, INSERT INTO Products", etc. to test performance.

Are you trying to figure out why a particular query is slow? There are tools included with SQL Anywhere that should be able to help. You can gather the detailed graphical plan (with statistics) for the query of interest. Usually look for table scans, where indexes may have been missed/skipped.

Hope this helps.

Kind regards, Dan

leuname93 commented 4 years ago

What I'm trying to say is that if I make a query directly in sqlanywhere it doesn't take long to response. but if I make the same query from node it takes too long to response to around 30 min. Why is this happening?

my query includes multiple columns in select, multiple IF ELSE statements, and multiple LEFT OUTER JOIN

DanCummins-SAP commented 4 years ago

Hi leuname93,

When it is fast, what tool do you use? dbisql? Is it running on the same client machine that is slow for node? IIRC, dbisql fetches 500 rows at a time.

For node, there is an API in the connection object: conn.setRowSetSize() - Have you used that?

You can get support for the node driver here: https://sqlanywhere-forum.sap.com/

Dan