pcsoft.us.windevCopyright 2026, PC SOFT23 Nov 2015 12:14:00 Z18 Nov 2015 12:09:00 ZHello 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 <= {pFinperiod} This query is then processed in a loop to get some additional info and the data goes into a memory table. FOR EACH CRTransCAQry This query returns about 6000 records from the same file SELECT Crtrans.CTCLEUNIK AS CTCLEUNIK, Crtrans.ActualCost AS ActualCost, Crtrans.JobCost AS JobCost, FROM Crtrans WHERE AND Crtrans.FinPeriod <= {pFinPeriod} AND Crtrans.JOCLEUNIK = {pJOCleunik} The results of this second query are also processed in the same method but with an additional test to isolate the same 73 records produced in the first query FOR EACH CRTransCAQry IF Position(CRTransCAQry.JCSourceCleunik,[62283,62284,62285,62286,62287,62288,62289,62290,]) <> 0 The second process on the 6,000 records is twice as fast as the first process, so there is obviously something not right in the first query The query generator informs me that no further indexes are required to make it faster. Can anyone tell me if the first query can be made more efficient please Regards Al30WEBDEVen_UShttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please/read.awpmoderateur@pcsoft.fr (El moderador)webmaster@pcsoft.fr (El webmaster)guestpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55089/read.awp23 Nov 2015 12:14:00 ZAllard By 'Lost' I meant that as the query is part of the control in which is was written is is unavailable for use anywhere el…https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55089/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55089/read.awp[WD19] Help with a slow query pleaseguestpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55088/read.awp23 Nov 2015 10:40:00 ZArie Thankfully I have not had an issue when sending parameters as part of hExecuteQuery as normally WD pops up a window with t…https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55088/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55088/read.awp[WD19] Help with a slow query pleaseguestpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55086/read.awp23 Nov 2015 08:27:00 ZWell Derrek, I do use queries ( internal ) and just call them with mysource.parameter. Put it in the init code of say a table a…https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55086/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55086/read.awp[WD19] Help with a slow query pleaseguestpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55085/read.awp23 Nov 2015 08:22:00 ZDerek, there is some risk in using using hExecuteQuery and fill in the params directly. I've seen queries changed by WD with…https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55085/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55085/read.awp[WD19] Help with a slow query pleaseguestpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55082/read.awp22 Nov 2015 20:54:00 ZHello Derek Thanks for the additional info. I use HFreeQuery before running the query as a way of making the all parameters …https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55082/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55082/read.awp[WD19] Help with a slow query pleaseguestpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55075/read.awp21 Nov 2015 12:01:00 ZHi Al I also use the same queries multiple times within my applications. I do however have similar queries depending on the …https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55075/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55075/read.awp[WD19] Help with a slow query pleaseguestpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55074/read.awp21 Nov 2015 10:40:00 ZHello Allard Thanks for the followup info. I don't have any MDI windows but I do have some opensister() and openchild() wind…https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55074/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55074/read.awp[WD19] Help with a slow query pleaseguestpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55073/read.awp21 Nov 2015 09:40:00 ZHi All, First let me state that Iam not an expert. Far form it. I learned programming with Windev. and went from there. . So …https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55073/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55073/read.awp[WD19] Help with a slow query pleaseguestpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55072/read.awp21 Nov 2015 00:54:00 ZHello Allard Can you give some more information on the problems you see in using a query multiple times please. I tend to …https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55072/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55072/read.awp[WD19] Help with a slow query pleaseguestpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55051/read.awp19 Nov 2015 14:25:00 ZHi 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…https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55051/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55051/read.awp[WD19] Help with a slow query pleaseguestpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55049/read.awp19 Nov 2015 13:37:00 ZHello Allard Thanks for the confirmation on the method. I agree about the query writer, it is quite easy to use, I just have…https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55049/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55049/read.awp[WD19] Help with a slow query pleaseguestpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55048/read.awp19 Nov 2015 13:24:00 ZHi 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 …https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55048/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55048/read.awp[WD19] Help with a slow query pleaseguestpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55042/read.awp19 Nov 2015 07:19:00 ZHello Jose I don't understand the query either, but it is what the generator program produced. However your comment about the…https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55042/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55042/read.awp[WD19] Help with a slow query pleaseccordespcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55040/read.awp19 Nov 2015 01:56:24 ZAl a écrit : > Hello All > > I am trying to move from Classic to HFCS and from reading the forum I understand that Queries …https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55040/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55040/read.awp[WD19] Help with a slow query pleaseguestpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55038/read.awp18 Nov 2015 19:28:00 ZWell, 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 may…https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55038/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/55027-wd19-help-with-slow-query-please-55038/read.awp[WD19] Help with a slow query please