Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
15
Erster Beitrag:
vor 6 Jahren, 7 Monaten
Letzter Beitrag:
vor 6 Jahren, 7 Monaten
Beteiligte Autoren:
Al, Piet van Zanten, Arie, Fabrice Harari

[WD16] Switch to from HF Classic to HFCS - Have to recode everything ?

Startbeitrag von Al am 27.12.2011 05:51

Hello All
We have always used HF Classic and I intended to have a serious look at switching over the HFCS over the Christmas break.
The first five minutes under test has been a complete disaster as it looks like I will have to re-code our entire app because our methodology works so slowly under HFCS.
This is code that was first cut years ago in WD5.5 with only small tweaks since then

This code takes 1 second to run under HFClassic and 11.5 seconds with HFCS !

I appreciate that HFCS works better with queries but I am staggered that there is so much difference in execution times.
The time delay is in the multiple HReadSeekFirst() commands used to extract descriptions from a central lookup file


//CRView is about 3500 records
HReadFirst(CRView)
WHILE NOT HOut(CRView)
IF ChkUserAccess("CRED",CRView.crCleunik)
HReadSeekFirst(Lookups,LUCLEUNIK,CRView.CreditorGroup)
LCreditortype = LU.Description
HReadSeekFirst(Lookups,LUCLEUNIK,CRView.EXCleunik)
LExpenseType = LU.Description
HReadSeekFirst(Lookups,LUCLEUNIK,CRView.TypeOfBusiness)
LBusType = LU.Description
HReadSeekFirst(Glacc,GLCLEUNIK,CRView.DRGlaccount) THEN
LPurchAcc = Glacc.GLAccountNo
TableAdd("TABLE",CRView.CRCleunik+TAB+CRView.creditorcode+TAB+CRView.creditorname+TAB+...
LBusType+TAB+CRView.ACTIVESTATUS+TAB+LCreditortype+TAB+CRView.IncludeInDailySheets+TAB+...
LExpenseType+TAB+LPurchAcc)

END // ChkUserAccess()
HReadNext(CRView)
END//while
HDeleteView(CRView)




As I see it the options are to denormalize the data and include the descriptions into the data or to build the data into a query and deposit it straight into the table. With my limited experience with queries I would not know where to start to gather the data required for this simple table. Either way, to switch to HFCS will be a long road and given the size of this app probably not worth the effort.

Regards
Al

Antworten:

Hi Al,
from 1 to 11,5 seconds seems very rare to me. Are your HF and HFCS both on the same machine? I mean, are you comparing situations which are comparable. No additional network traffic for instance, while the HFCS is on another machine?

Looking at your code I see several ways to speed things up, which will count for HF as well!
CRView seems to be a view, loaded before hand.
You could do the same with the LOOKUPS-data. Just create a query for the LOOKUPS file and use it in HReadSeekFirst(). (a query in fact is a view...)

A better way though is to add "lookups" and "glacc" to the view, by using a simple LEFT JOIN. And get every data you need in one query.
Can you tell us how CRView looks like?

Second: what does ChkUserAccess() do? Is it using data from other tables? Then you get 3500 (or more) addionional data handling lines. It's much more efficient to load this data once, again by using a query or view.

(afaik "views" are the old way and "queries" the new way - I guess queries are a bit more efficient).





von Arie - am 27.12.2011 10:36
Hello Arie

Thanks for your interest.

I am doing this in Windev test GO mode. The HFCS and HF are the same set of data on a local SSD. I hope you are right and I have not setup the HFCS correctly.

To make the test I set the project files to the folder where the HF classic files are. The HFCS is setup in the same directory.
After that I comment out the HDescribeConnection() code in the project init to turn off access via the HFCS.

Views are very quick if you don't use many fields and the view load is instant.

This is the full code

TableDeleteAll("TABLE")
LFldList is string = "CRCLEUNIK,CreditorCode,CreditorName,TypeOfBusiness,ActiveStatus,CreditorGroup,IncludeInDailySheets,EXCleunik,DRGLAccount"
SWITCH LdisplayFILTER
CASE "BT_ALL"
HCreateView(CRView, Creditor, LFldList, "CreditorCode", "", hViewDefault)
CASE "BT_ACTIVE"
HCreateView(CRView, Creditor, LFldList, "CreditorCode", "ActiveStatus="+True+"", hViewDefault)
CASE "BT_INACTIVE"
HCreateView(CRView, Creditor, LFldList, "CreditorCode", "ActiveStatus="+False+"", hViewDefault)
CASE "BT_HOLD"
HCreateView(CRView, Creditor, LFldList, "CreditorCode", "HoldPayment="+True+"", hViewDefault)
END


HReadFirst(CRView)
WHILE NOT HOut(CRView)
IF ChkUserAccess("CRED",CRView.crCleunik) THEN
IF HReadSeekFirst(Lookups,LUCLEUNIK,CRView.CreditorGroup)
LCreditortype = LU.Description
ELSE
LCreditortype = ""
END
IF HReadSeekFirst(ExpType,EXCLEUNIK,CRView.EXCleunik)
LExpenseType = EX.BudgetCode
ELSE
LExpenseType = ""
END
IF HReadSeekFirst(Lookups,LUCLEUNIK,CRView.TypeOfBusiness)
LBusType = LU.Description
ELSE
LBusType = ""
END
IF HReadSeekFirst(Glacc,GLCLEUNIK,CRView.DRGlaccount) THEN
LPurchAcc = Glacc.GLAccountNo
ELSE
LPurchAcc = ""
END
TableAdd("TABLE",CRView.CRCleunik+TAB+CRView.creditorcode+TAB+CRView.creditorname+TAB+...
LBusType+TAB+CRView.ACTIVESTATUS+TAB+LCreditortype+TAB+CRView.IncludeInDailySheets+TAB+...
LExpenseType+TAB+LPurchAcc)

END
HReadNext(CRView)
END//while
HDeleteView(CRView)



The times I mentioned are purely for the loop through the view and do not include the creation of the view.

The lookup file is a central repository of descriptions so the field Creditor.TypeOfBusiness
is a numeric value representing the primary key value Lookups.LUCleunik. So to get the Creditor Business Type, Expense Typeand Creditor Group I have to do three seeks into the lookups file. The creditor DRGlAccount is a primary key values that also needs a seek into a foreign data file to extract the GL Account number.

The ChkUserAccess() is a separate process that checks if the user has access to the specific creditor record or not.

The profiler identifies the large number of HReadSeekFirst() as the culprit with them taking up 10.5 seconds of the total time to run through the view.


Regards
Al


von Al - am 27.12.2011 11:09
Al, it seems you do the connections the right way. And switch between hf and hfcs nicely.

You can put those HreadSeekFirst together with CRView in one query. And let HFCS do the search&collect job. This will certainly speed it up.
But you are right: this will require you to recode things.

I'm sure you don't need to rewrite the entire app. Maybe on some places like the one you mention here. I'm afraid it's a matter of trial&error.

With the query designer you can create a query holding data from "creditor" and "lookup" and "exttype" and "glacc". If not every creditor has a lookup (or exttype of glascc) you need to define the join as OUTER join (double click the join and choose (include a row per "creditor" without "lookup")

Your query would look something like this:
SELECT creditor.crleunik, creditor.creditorcode, L1.description AS description1, L2.description AS description2
FROM creditor LEFT OUTER JOIN lookup L1 ON creditor.CreditorGroup = lookup.lucleunik
LEFT OUTER JOIN lookup L2 ON creditor.TypeOfBusiness= lookup.lucleunik
WHERE ActiveStatus = {ParamStatus}
AND HoldPayment = {ParamHoldPayment}

When executing the query just apply the params you need, as you do now. If a {param} is not supplied , hf-engine will remove the whole line.


von Arie - am 27.12.2011 11:31
Hi Al,

When I switched to HFCS there was only a small loss of performance.
The speed of your server and network is the bottleneck.
What are their specs?
The strain on a single machine is much higher since it will be serving both the local program and the hfcs server. I guess if you test with a dedicated HFCS server on a different machine the results will be better.
Indeed, it's obvious that 5 x 3500 requests to the server wil be much slower than a single query, but in your case the difference is greater than what I experienced using a dedicated server.
In my case, I started with the old code, but in over a year I gradually rewrote all code to use queries. Finally, performance is much better than the old classic way.
However, this involves a great deal of recoding and debugging.

Regards,
Piet






von Piet van Zanten - am 27.12.2011 12:20
Hello Arie & Piet

In this app there is not a single query, it is all views and hreadseeks so it will be a complete re-write. I wil however try it on a dedicated server to see how much difference that makes.
It has only become a problem at one site that has grown to about 45 users ( most are only 5-20 users) and we are maxing out their server because HyperFIle Classic is single threaded. It didn't seem to be such a problem with older servers with fewer cores but higher clock speeds.

For the moment we will probably stay with HF Classic and use a BI interface for the reports which will reduce the load.

I have experimented with a query based on Arie's code and it is much faster but the memory table hangs for about 15 seconds after it has loaded. This doesn't happen when I load it from HyperFileClassic which is quite odd as the table load code is the same.


Regards
Al

von Al - am 27.12.2011 12:33
Hello Piet

I tried it all again using a spare server (2 Xeons 4 cores 4GB Ram SCSI drives, Windows 2003 server OS) and the results were the slightly slower than on the local machine. I suspected as much because the local box is pretty quick and I had the data on an SSD.

So the long term solution is a combination of denormalizing and re-coding using queries.


Is there any type of SQL code generator that I can use to help me write the queries. The inbuilt WIndev one is pretty limited. I am having a look at SQL Prompt from Redgate, its a plugin for MS SQL Server Management Studio, but are there any others ?


Regards
Al

von Al - am 28.12.2011 02:45
Hi Al,

If we take your posted code as an example, a quick method might be to create two very simple queries for the Lookups and Gacc files that select the whole file and sort on the key you use in Hreadseekfirst. Search and replace all references to Lookups and Gacc in your code by qry_Lookups and qry_Gacc.
The Hreadseekfirsts will be much faster.

Regards,
Piet

von Piet van Zanten - am 28.12.2011 09:57
Al,
I'm just wondering: is hfcs still slower in this situation the 2nd or even 3rd time you run it (without closing your app)?
hfcs needs time to learn the application and cache data.

von Arie - am 28.12.2011 10:44
Hello Arie

I tried it on the Windows Server with 2gb of RAM cache and also on an XP64 bit machine running as a aserver and gave it 4gb of Ram. There was no difference in performance no matter how many times I ran it. All our larger sites run everything on the server either through terminal services or citrix so in a sense our existing HF Classic is running in a "Client/Server" mode because the clients are logging into the server and running the app there with the data.
Its just this lack of multi threading in the HF Classic engine that is the problem because some of our reports can max out the server. Perhaps a bigger server... :-)

Regards
Al

von Al - am 28.12.2011 10:54
Hello Piet

I will try your suggestion and let you know. The interesting thing from this is just how well HF Classic performs. It is gathering 3500 records, performing 4 x 3500 Hreadseekfirst(), running an access checking proc 3500 times and loading the data into a memory table all in just over one second.

Regards
Al


von Al - am 28.12.2011 11:23
Hello Piet

Take a bow !

Using queries for the lookups solved the problem entirely. Using the HFCS engine it now takes a fraction longer than the HFClassic. The queries only have two fields, primary key and description so they are almost instant to build and as you predicted the HReadSeekFirst() functions are now very very quick.

Thanks for your great solution.

Regards
Al

von Al - am 28.12.2011 12:02
Hi Al,

I never had any complaints about hf classic, even in networking environment it performed really well. The reason why it's so fast must be because the files are completely cached locally. If opportunistic locking was switched off on the server, performance dropped dramatically.

Regards,
Piet



von Piet van Zanten - am 28.12.2011 12:08
Hi Al,

Glad to be of help.
Don't forget to use hCancelDeclaration after you are done with the queries, to free resources. Queries are global to your project and will stay resident until you free them.

Regards,
Piet

von Piet van Zanten - am 28.12.2011 12:19
Hi everybody

Your comparison has a basic flaw. I understand that you are doing datat access with ONE user/program at a time. In such a situation, HF classic is optmizing access by taking ownership of the file and working in memory. As soon as you add a second user (just opening the file should be enough), you'll see the data access speed in classic go down drastically.

At this point, you can start comapring by adding users on both side. The same code will very quickly become faster for HFCS as the number of simultaneous users increases.

If your programs are written for mono-user situation then HFclassic is a very good choice. For all the other, without changing your code, you should see a much faster (and more stable) data access speed with HFCS.

Best regards



von Fabrice Harari - am 28.12.2011 20:53
Zur Information:
MySnip.de 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.