response time on complex queries.

[leuname93]

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]

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]

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]

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