Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
15
Erster Beitrag:
vor 2 Jahren, 7 Monaten
Letzter Beitrag:
vor 2 Jahren, 7 Monaten
Beteiligte Autoren:
Al, Allard, DerekT, Arie, Jose Antonio Garrido

[WD19] Help with a slow query please

Startbeitrag von Al am 18.11.2015 11:09

Hello All

I am trying to move from Classic to HFCS and from reading the forum I understand that Queries are the way to go instead of procedural code for extracting data, but having no experience with queries, I have to rely in the query generator and I fear that it is not that efficient.

This query return 73 records out of a file of about 450,000 records
SELECT
Crtrans.CTCLEUNIK AS CTCLEUNIK,
Crtrans.ActualCost AS ActualCost,
Crtrans.JobCost AS JobCost,
FROM
Crtrans
WHERE
CRTrans.JCSourceCleunik IN
(
SELECT
CostCat.CACLEUNIK AS CACLEUNIK,
CostCat.BUCLEUNIK AS BUCLEUNIK,
CostCat.CodeOrder AS CodeOrder
FROM
CostCat
WHERE
CostCat.BUCLEUNIK = {pBUCleunik}
)
AND CRTrans.FinPeriod

Antworten:

Well, I never use IN but JOIN.
The SUB SELECT is executed for each row of the main SELECT, so it's executed 6000 times, and maybe always return the same values. At least that the behavior when using JOIN.

Also I didn't understand how you use IN SELECT with more than one ITEM (actually three items):
WHERE CRTrans.JCSourceCleunik IN SELECT CostCat.CACLEUNIK, CostCat.BUCLEUNIK,CostCat.CodeOrder.

Regards,
José Antonio.

von Jose Antonio Garrido - am 18.11.2015 18:28
Hello Jose

I don't understand the query either, but it is what the generator program produced. However your comment about the number of times the second select ran prompted me to create a string for the eight numbers to be matched and use them as a parameter and so the query now has a last line of:
AND Crtrans.JCSourceCleunik IN ({pCAList})

This is much faster thanks.

Regards
Al

von Al - am 19.11.2015 06:19
Hi Al

Yes that is the way to go. Parameter is in the list. I use it all the time.
The querie builder works great for me . I use parameters almoast everywhere. works great!!

regards
Allard

von Allard - am 19.11.2015 12:24
Hello Allard

Thanks for the confirmation on the method.
I agree about the query writer, it is quite easy to use, I just have to get smarter about queries.

Regards
Al

von Al - am 19.11.2015 12:37
Hi Al,

I use queries a lot and they work great. However I had some problems with using queries. That was when I used the same querie several times in diffentent places. This is a no go thing to do. especially when your app is a multi user app!!.

When using one querie for one proces it works great. To be sure I use internal queries as mutch as possible if queries are used to display stuf etc. Then Iam sure it isnot used somewhere else.

For calculations agrigations etc in code I use the seperate queries ( with hexecute) but never use one querie in two different proceses. Because it can give you unexpected results

Regards
Allard

von Allard - am 19.11.2015 13:25
Hello Allard

Can you give some more information on the problems you see in using a query multiple times please.

I tend to create a query with multiple parameters and to then use that query many times and only use the parameters relevant for that use of the data. I have no handwritten queries, they are all created with the query generator and are always run as
HFreeQuery()
Query.FieldName = pParameterValue
HExecuteQuery()

.e.g a query on a file of job cost budgets has a 2 parameters pJobKey and pCostCodeKey
When I need all the cost codes for a job I use CostCodeQry.PJobKey = Jo.JobKey and leave the pCodeCodeKey blank. Another time I will use the pCostCodeKey parameter to just get a single record.

I use this query in very many places in my project and the query could be in use by multiple users with different parameters in different parts of the application simultaneously.

I am assuming from your warning that Windev is not capable of handling the fact that a query with the same name is being created many times by many different processes.

If this is the case I would need to change my methods and I would then have hundreds of queries, many of them identical, which seems quite inefficient and hard to maintain.

Regards
Al

von Al - am 20.11.2015 23:54
Hi All,

First let me state that Iam not an expert. Far form it. I learned programming with Windev. and went from there. . So What I state I have taken as advice from other developers.

1 The freeing of the querie is good practice It should free the querie so it can be used somewhere else.

If you do queries and on the results of the querie use the hfunctions then the process tens to get longer. And if in some other process one uses the same querie then wrong things can happen.

Especially if you have a multi window mdi like app and fill a table with a querie and run and other window filling stuff with the same querie and return to the first window one can get wrong results.
Believe me I have had that problem.

As I see it in your case you do free the querie is not doing a lot so you might get away with it

I took the advice from someone I met at a WDG- meeting (Windev Developers Group )and later on from someone who has just retired as a programmer. And I have had no problems ever since.

Maybe other developers can respond. For my explanation might not be extensive enough. MY excuses for that As Stated Iam not an expert. I would not want to be responsible for you doing unnecessary work.
Maybe Arie of Fabrice can give a better explination.

von Allard - am 21.11.2015 08:40
Hello Allard

Thanks for the followup info.
I don't have any MDI windows but I do have some opensister() and openchild() windows and I will keep an eye on how I use queries in those circumstances.

Regards
Al

von Al - am 21.11.2015 09:40
Hi Al

I also use the same queries multiple times within my applications.
I do however have similar queries depending on the size (number of fields) to be returned.
Not sure if this is really necessary but it feels wrong to return a data set with 20 fields per record when I only need 5.

This is part of the reason I never use embedded queries as they are effectively lost from the application and you cannot give them a meaningful name.

Multi user has no impact as each will be operating in their own instance of the application.

If you have multi window, multi thread or MDI then set 'Independent HFSQL context' on the window.
This will isolate the result set returned by the query to the window.

If you need to run the same query twice in the same window or process then store each result in a variable or array.
Be sure to use hFreeQuery(myQuery) after each call as this will clear the result from memory.

In your example hFreeQuery(myQuery) before the query call has no effect unless of course the query has already been run in which case you should ideally have made this call once the result set had served its purpose.

Another option for calling your query with parameters would be
hExecuteQuery(myQuery,hQueryDefault,Jo.JobKey,pCostCodeKey)
if one or more parameters are not required then subtitute the variable for Null as
hExecuteQuery(myQuery,hQueryDefault,Jo.JobKey,NULL)
A matter of preference of course but I prefer this = less code, easier to read and WD is kind enough to display the parameter names when entering the code.

von DerekT - am 21.11.2015 11:01
Hello Derek

Thanks for the additional info.
I use HFreeQuery before running the query as a way of making the all parameters null and then I only have to set values for the parameters I will be using but I will add it in afterwards now.

Regards
Al

von Al - am 22.11.2015 19:54
Derek,

there is some risk in using using hExecuteQuery and fill in the params directly.
I've seen queries changed by WD without notifying the developer, after which the order of the params had changed as well.
Then you get unexepcted results during runtime, because the entered values don't match the parameters anymore. Caused me several hours once :sneg:

But I agree with you on the other options :spos:
You can even define classes to use an independant hyperfile context

// Declare the class
IndependentHFSQLClass is Class, independent HFSQL Context

END


Not sure what impact it has on performance, because on every new instance of the class, the HF-context is copied.

von Arie - am 23.11.2015 07:22
Well Derrek,
I do use queries ( internal ) and just call them with mysource.parameter. Put it in the init code of say a table and run it with ( tainit )or one of the other arguments, so they are not “Lost” form the app.
I have heard developers that even sent a terminal key as a parameter to be sure the querie belongs to a specific terminal. So I am not sure about separated sessions stuff ( I havenot done so yet)
Maybe this is a issue others can shine some lite on?

I know for a fact that is you use databinding on say a large treeview it definitely goes wrong. It messes up other parts of the app in a rigorous way. I had a mager bug and solved it by filling the tree by programming. So depending on windev in managing stuff is not always a good Idea. ( if your app is not a little app )
Just my thoughts. ( Better be save then sorry )

Regards
Allard

von Allard - am 23.11.2015 07:27
Arie
Thankfully I have not had an issue when sending parameters as part of hExecuteQuery as normally WD pops up a window with the calls to the query listed.
Never say never though so I guess I will need to be extra diligent when making query changes.

I had not realised that the HFSQL Context could be set in a class - interesting.
That said I have to admit I do not see why I would need that as an instance of a class is, well a separate instance.
Need to do a bit more investigation.

At the moment I am playing (on & off as time allows) with the new MVP RAD.
Some interesting (and frustrating) stuff in there around the 'Presenter' object.

von DerekT - am 23.11.2015 09:40
Allard
By 'Lost' I meant that as the query is part of the control in which is was written is is unavailable for use anywhere else in the application.

In a multi user situation each using is running their own instance of the application.
How a query can request a result set and receive one that has been requested by different instance (user) of the app is, quite frankly, beyond my understanding.

It is always difficult commenting on issues without seeing the code used by I have not had any problems when using independent HFSQL Context.
This creates a copy of the window opened and isolates it, and its data, from the same window being opened at the same time.

An alternative approach, my preferred method, is to use arrays as the bound data source for tables and combos.
UML is the easiest way as it will create a class for each datafile alternatively you can use structures.
From this you can declare as many arrays as are needed....
arr_MyArray1 is an array of MyFile (or MyStructure)
arr_MyArray2 is an array of MyFile (or MyStructure) and so on

Bind your table to an array (By Variable)
Following execution of your query use FileToArray(MyQuery,MyArray1) followed by TableDisplay(MyTable,taInit) or ListDisplay(MyCombo(taInit) as appropriate.
Each Table/Combo will now contain a unique dataset returned by the query.

An added benefit of using this method is that all data contained in a row is available.
Declare a variable as rec_MyRecord is MyFile (or MyStructure).
On row selection (Table or Combo) use rec_MyRecord = MyTable, rec_MyRecord now contains all data contained in the row of the array regardless of whether they are displayed in the control and can be retrieved using the rec_MyRecord.AValue syntax.
Very useful if more than one value is needed from a combo or, if controls on the window are bound to rec_MyRecord, then SourceToScreen is your friend.
rec_MyRecord can also be sent as a parameter to other windows, reports, methods thus saving the need to re-fetch the record set.

Of course we all use WD differently, this is just some of my preferences, and what suits you may well be OK.
Problem I find is that nothing is ever deprecated so it is very easy to continue using what we know as opposed to what may be best.

von DerekT - am 23.11.2015 11:14
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.