I'm currently doing a research using a TPC-H table and I'm trying to get the running time for 3 queries in NuoDB:
SELECT * FROM LINEITEM
SELECT * FROM LINEITEM WHERE L_PARTKEY BETWEEN 1 AND 80000
SELECT * FROM LINEITEM WHERE L_PARTKEY BETWEEN 1 AND 80000 OR L_PARTKEY
BETWEEN 100001 AND 200000
The thing is whenever I run a query, the results come but I guess the database does not store all of the data in memory, but rather keeps its connection open (so when I scroll down through the data results, it refreshes with more data). Thus, I cannot retrieve the query running time. I tried setting the MIN_QUERY_TIME to 1 so it would be caught in SYSTEM.QUERYSTATS but it does not appear there (although I find the query as open in SYSTEM.CONNECTIONS, which is why I think the database keeps an open connection rather than storing in-memory).
Is there any kind of solution to fetch all data(as in a select all) and get the query running time?
Is there another way to get this that I'm missing? When I do
SELECT COUNT(*) FROM LINEITEM
it does work and the query goes to SYSTEM.QUERYSTATS. I think it is because the database has to go through all rows in order to count and therefore the query finishes, whereas when I do a
SELECT *
it waits until I "ask" for more data.
I have been trying for days and I could not get into a solution. I even tried different 3rd party tools like DBVisualizer and SQL Workbench but they do not seem to give me the expected results.
I would be really glad if you could give me a hand or at least forward this e-mail to someone that might lead me to a possible solution.
Many thanks.
