Using complicated server side queries on HFSQL C/S

Startbeitrag von Marijan Tomasic am 08.03.2016 10:26

How to solve the problem when I have a query that runs on a large set of data, using many other serverside queries, tables and functions, and returns a (relatively) small set of data?

On MSSQL server there is a simple solution, I create the complicated view on the server, Importing it in Analysis and use as a normal table.

The question is how is the best and most practical way to solve such a problem on HFSQL C/S? What are my options for best performance?

I would like to have such a query in Analysis and use it in the same way as a table (but read-only).

Can serverside query in some way be used as a classic table of analysis? (on HFSQLCS database)

I hope I have clearly enough explained what bothers me.



Are not ALL queries server side queries? Or are you talking about stored procedures?

Best regards

von Fabrice Harari - am 08.03.2016 11:56
This is not about stored procedures, and concerns only HFSQLCS.
As I understand it, the query defined in the application can not refer to other queries, or source queries must been executed beforehand. (Help topic: "Queries based on queries")

If I define a complicated query within the application, then I generate a lot of unnecessary traffic between the server and application, especially if the query is based on many other queryes. And I do not want that, I want only the final resultset to be returned, like it is when using table defined in analysis.

On the other hand, if the query is defined only on the server it is not visible in code?!?

I do not understand the difference between a query defined in the application and query defined in the analysis, physically located on the server. (HFSQLCS)
What is purpose of having query defined in analysis? Is it only for using it in stored procedures?

It would be good to have the ability to use Query defined in Analysis as one created in application. As we can do with MSSQL or MySQL imported views.

I hope I have better explained the problem. English is not my native language ....

von Marijan Tomasic - am 08.03.2016 13:39
You might want to look at HFSQLCS views or materialized views which are new in V20. They also use server side queries to build a view and can be used to minimize client/server traffic when using queries based on queries. But be warned, they come with their own quirks. For example a view that has a calculated field, the field will not have correct representation in the analysis making the view un-usable, so no calculated fields in a view.

Another solution is to create a new file to hold the view, then a stored query and stored procedure to refresh data in the table. You would use HExecute to refresh the table when needed..


von Davie - am 08.03.2016 18:04
Zur Information: hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.