|
| [WD20] Is running a query on a query good policy ? |
| Iniciado por guest, 29,dic. 2015 00:02 - 14 respuestas |
| |
| | | |
|
| |
| Publicado el 29,diciembre 2015 - 00:02 |
Hello All
The query editor runs out of puff after about 8 joins and refuses to add any more, so I have used the first query as the base data for a second query so I can add more joins.
It all seems to work ok, but I am wondering if there are any pitfalls in using queries in this manner that I am not aware of ?
Regards
Al |
| |
| |
| | | |
|
| | |
| |
| Publicado el 29,diciembre 2015 - 09:19 |
Hi Al,
I used that method before in one of my program , just make sure to check if there are any records in the first query before running second query.
the bad part for this method is the join is done in local , if you processing huge data , you may experience slow .
other methods you can try is
- using hexecutesqlQuery(0
- if you are using external database (sql server, mysql, postgresql, etc... ) you can create view as the based. this way the joins will be done in server side .
hope will help
CCC2 |
| |
| |
| | | |
|
| | |
| |
| Publicado el 29,diciembre 2015 - 11:04 |
Hi All,
[WD18] I use frequently a query on a query as it can help finetuning performance when you know how to query with the less possible results in your first query in stead of in your second query. I have the impression you put less stress on the server than using a view (but maybe you want to sell a bigger server).
Of course it is always a matter of checking this is a better solution than any other, but when the question is 'does it work well', yes, the principle works very well. Thusfar I have not found any pitfalls, as long as you remember to free both queries with 'HFreeQuery'. I got strange behaviour, having freed the first one and not the second one.
Hope it helps,
Rudolf |
| |
| |
| | | |
|
| | |
| |
| Publicado el 30,diciembre 2015 - 11:35 |
Hello CCC2
For the moment I am happy using the SQL wizard but as I get bettter at queries I will try writing my own and run them with HexecuteSQL Query as you suggest.
Regards
Al |
| |
| |
| | | |
|
| | |
| |
| Publicado el 30,diciembre 2015 - 11:53 |
Hi Al,
You can use the editor to create your basic query and then copy the code to use it by hExecuteSQLquery.
Then you can add some more joins and other stuff.
Regards,
Piet |
| |
| |
| | | |
|
| | |
| |
| Publicado el 30,diciembre 2015 - 12:19 |
Hello Piet
Thanks for the info, much better than starting from scratch.
Regards
Al |
| |
| |
| | | |
|
| | |
| |
| Publicado el 30,diciembre 2015 - 12:25 |
Hi Al,
you can also modify the code of the SQL in the query editor (press F2).
Then you lose the visual presentation, but maybe the 8+ joins are allowed?
I do this quite often, because the visual query editor has it's limitations on other points as well.
HExecuteQuery() can still be used as before, so no need for code changes whereever you use the query. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 30,diciembre 2015 - 13:17 |
Hello Arie
After the 8 + joins the query wizard returns with a message that it can't work out the connections and then it gets really annoyed and deletes all the joined files leaving only the original file !
Regards
Al |
| |
| |
| | | |
|
| | |
| |
| Publicado el 30,diciembre 2015 - 17:26 |
Al
I have not got a query that requires 8+ joins so I may, not for the first time I might add, be barking up the wrong tree.
Try editing the query text directly using file suffixes instead of names.
If you roll your own or edit the text WD does accept this.
Maybe, just maybe, it will not get so confused.
SELECT a.idProperty AS idProperty, a.PK_Property AS PK_Property, a.FK_Landlord AS FK_Landlord, a.ReferenceCode AS ReferenceCode, b.Name AS Name, c.FormalName FROM Property AS a, Landlord AS b, Landlord AS c WHERE a.idProperty = {pnIDProperty} AND a.FK_Landlord = b.PK_Landlord AND a.FK_Landlord = c.PK_Landlord ORDER BY a.PK_Property ASC
Not the greatest example I grant you but does give an idea of what I am talking about.
NOTE: You can link to the same file twice using this approach - see Landlord above
If you do need to roll your own and use HExecuteSQLQuery it is not that different
Just declare
MyQry is string [ ] Paste the above, or better still your, query between the [] brackets, change the {Parameter} for %1 and use Stringbuild to substitute a value.
If all else fails then try building it line by line to discover where 'enough is enough' so to speak where multiple joins are required. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 30,diciembre 2015 - 17:59 |
Hi A1,
I highly not recommend to do 2 query unless you have no choice.
i just run a test and found this method is not recommend on big database.
this is how i test.
DB : mysql
I create 3 tables, continent (7 records), region (23 records) , country (245 records)
I create 2 query
----- QRY1 ---------
SELECT
Continent.Id AS Continent_Id,
Continent.Continent_Name AS Continent_Name,
Region.Id AS Region_Id,
Region.Region_Name AS Region_Name,
Region.Continent_Id AS Region_Continent_Id
FROM
Continent,
Region
WHERE
Continent.Id = Region.Continent_Id
----- QRY2 ---------
SELECT
QRY1.Continent_Id AS QRY1_Continent_Id,
QRY1.Continent_Name AS QRY1_Continent_Name,
QRY1.Region_Id AS QRY1_Region_Id,
QRY1.Region_Name AS QRY1_Region_Name,
Country.Id AS Country_Id,
Country.Region_Id AS Country_Region_Id,
Country.Country_Name AS Country_Name
FROM
Country,
QRY1
WHERE
QRY1.Region_Id = Country.Region_Id
-------- code ---------------------------------------------------------
HCancelDeclaration(QRY1)
HCancelDeclaration(QRY2)
HExecuteQuery(QRY1)
Info( HNbRec(QRY1))
HExecuteQuery(QRY2)
Info( HNbRec(QRY2))
the result is correct but when i look into the profiler . it is totally different story
------from profiler when execute QRY1 ---------
SELECT `Continent`.`Id` AS `Continent_Id` , `Continent`.`Continent_Name` AS `Continent_Name` , `Region`.`Id` AS `Region_Id` , `Region`.`Region_Name` AS `Region_Name` , `Region`.`Continent_Id` AS `Region_Continent_Id`
FROM `Continent` , `Region`
WHERE ( `Continent`.`Id` = `Region`.`Continent_Id` ) ORDER BY `Continent`.`Id`
-----from profiler when execute QRY2 -------------------------
SELECT `Continent`.`Id` AS `Continent_Id` , `Continent`.`Continent_Name` AS `Continent_Name` , `Region`.`Id` AS `Region_Id` , `Region`.`Region_Name` AS `Region_Name` , `Region`.`Continent_Id` AS `Region_Continent_Id`
FROM `Continent` , `Region`
WHERE ( `Continent`.`Id` = `Region`.`Continent_Id` ) ORDER BY `Continent`.`Id`
-----------------------------------------------------------------------------------------------------
SELECT COUNT(*) AS WDACCESNATIF_NBENR FROM `Country`
------------------------------------------------------------------------------------------------------
SELECT * FROM `Country` WHERE `Region_Id`=1 ORDER BY `Region_Id`,`Id` LIMIT 100
-----------------------------------------------------------------------------------------------------
SELECT COUNT(*) AS WDACCESNATIF_NBENR FROM `Country`
------------------------------------------------------------------------------------------------------
SELECT * FROM `Country` WHERE `Region_Id`=2 ORDER BY `Region_Id`,`Id` LIMIT 100
=========================================================
repeat until Region_Id = 23
========================================================
i highly recommend you use hExecuteSQLquery() |
| |
| |
| | | |
|
| | |
| |
| Publicado el 30,diciembre 2015 - 22:38 |
Hello All
Thanks for all the replies - it looks like I should use HExecuteSQLQuery. The problem is that I really should go away and learn the basic theory behind queries, but as usual I am constrained by the fact that there are only a measly 24 hours in every day so if I can get some more help here I would appreciate it.
These are the two queries from the query wizard - I have removed the field list to make it a bit shorter.
This is the first query to extract data from file CRTrans and link in data from other files where the value in a CRTrans field is a foreign key to a primary key in the other file.
CRTrans is a file of Creditor Invoices and there are links in each record to the creditor, a job, GL accounts etc so to present this information in a table, I need to link to a large number of other files.
Previously, I was doing it all procedurally by filtering CRTrans to a Job or Creditor, setting up a loop, reading the first record and then doing a series of HSeekFirst() on the other files and then moving to the next record in CRTrans and all of this in HF Classic. The new work is using HFCS so that is why I am trying out the queries.
SELECT List of fields FROM ( ( ( ( ( Creditor LEFT OUTER JOIN ( OrdLines RIGHT OUTER JOIN Crtrans ON OrdLines.OTCLEUNIK = Crtrans.OTCLEUNIK ) ON Creditor.CRCLEUNIK = Crtrans.CRCLEUNIK ) LEFT OUTER JOIN Glacc ON Crtrans.DrGlAccount = Glacc.GLCLEUNIK ) LEFT OUTER JOIN Glacc GLCRAccAlias ON Crtrans.CrGlAccount = GLCRAccAlias.GLCLEUNIK ) LEFT OUTER JOIN CostCat ON Crtrans.JCSourceCleunik = CostCat.CACLEUNIK ) LEFT OUTER JOIN Orders ON OrdLines.ODCLEUNIK = Orders.ODCLEUNIK ) LEFT OUTER JOIN Budgets ON CostCat.BUCLEUNIK = Budgets.BUCLEUNIK WHERE ( Crtrans.JOCLEUNIK = {pJOCleunik} AND Crtrans.FullyPaid = {pFullyPaid} AND Crtrans.CRCLEUNIK = {pCRCleunik} AND Crtrans.FinPeriod BETWEEN {pStartFinPeriod} AND {pEndFinPeriod} AND Crtrans.OTCLEUNIK = {pOTCleunik} AND Crtrans.TransactionDate BETWEEN {pStartTransDate} AND {pEndTransDate} AND Crtrans.ClaimFinPeriod BETWEEN {pStartClmFinPeriod} AND {pStartClmFinPeriod} ) ORDER BY {psBudget} ASC, {psCreditor} ASC, {psInvoiceNo} ASC This returns the correct number of records from the file The second query uses the data from the first to continue the process of getting mnore linked data SELECT List of fields from query 1 FROM ( ( Emp RIGHT OUTER JOIN ( Stock RIGHT OUTER JOIN ( FAssets RIGHT OUTER JOIN ( Lookups RIGHT OUTER JOIN ( ProjMemo INNER JOIN CRTransJOQry ON ProjMemo.PMCLEUNIK = CRTransJOQry.CTCLEUNIK ) ON Lookups.LUCLEUNIK = CRTransJOQry.UOM ) ON FAssets.FACLEUNIK = CRTransJOQry.FACLEUNIK ) ON Stock.STCLEUNIK = CRTransJOQry.STCLEUNIK ) ON Emp.EMCLEUNIK = CRTransJOQry.EMCLEUNIK ) LEFT OUTER JOIN PayGroup ON Emp.PGCleunik = PayGroup.PGCleunik ) LEFT OUTER JOIN Lookups PGLookupsAlias ON PayGroup.PayGroupPosition = PGLookupsAlias.LUCLEUNIK WHERE ( ProjMemo.SourceFile = 'CT' )
One thing I don't understand (among the many) is that the first query primarily uses left joins and the second uses mainly right joins. In the first query, why would the join to collect the orderline record, which is then used to collect the order number from the order file, be mixed in with the join to get the Creditor code ?
In principle, if I want to combine the two queries into one, is it just a matter of inserting the "From" clauses from the second query after the "From" clauses of the first query ? In the second query, I only need records from ProjMemo where the Sourcefile = "CT" and the SourceCleunik = CRTransJOQry.CTCleunik and I can't figure out where that goes if everything is combined into one query
I think I will probably end up just using the first query to do the bulk of the work and pick up the rest of the data procedurally.
Regards
Al |
| |
| |
| | | |
|
| | |
| |
| Publicado el 31,diciembre 2015 - 11:13 |
Al,
I don't know when of why de WD query editor 'creates' left joins at day 1 and right joins on other moment. That's done behind the scenes.
In your example you try to get records from Crtrans.
There has to be a corresponding Projmemo record otherwise the Ctrans record will not get into your result set.
But that does not count for all other tables. If there for example no Emp record (which means most of the time that Ctrans.emp is empty, because otherwise you would have non-existing EMP-id in CTrans which would make no sense....) then you will still get a record in the resultset. But the EMP fields will be empty/null
LEFT or RIGHT joins are in fact doing the same thing, The result depends on the order of the listed tables.
You can turn all RIGHT joins into LEFT joins AND switch the first and second tablename. It gives you the same result.
If you do so you have LEFT joins only, which make things better readable.
From that point it's much easier to mix both queries into one.
btw: the OUTER word can be omitted if you like. That's the default for a LEFT/RIGHT JOIN, instead of a INNER JOIN.
And it also doesn;t matter what the seqeunce in the ON= part is. table1.key=table2.key is the same as table2.key=table1.key
Also without the parentheses is reads better.
Try something like this
SELECT List of fields FROM Crtrans LEFT INNER JOIN ProjMemo ON ProjMemo.PMCLEUNIK = CRTrans.CTCLEUNIK LEFT JOIN Creditor ON Creditor.CRCLEUNIK = Crtrans.CRCLEUNIK LEFT JOIN OrdLines ON OrdLines.OTCLEUNIK = Crtrans.OTCLEUNIK LEFT JOIN Glacc ON Crtrans.DrGlAccount = Glacc.GLCLEUNIK LEFT JOIN Glacc GLCRAccAlias ON Crtrans.CrGlAccount = GLCRAccAlias.GLCLEUNIK LEFT JOIN CostCat ON Crtrans.JCSourceCleunik = CostCat.CACLEUNIK LEFT JOIN Orders ON OrdLines.ODCLEUNIK = Orders.ODCLEUNIK LEFT JOIN Budgets ON CostCat.BUCLEUNIK = Budgets.BUCLEUNIK LEFT JOIN Lookups ON Lookups.LUCLEUNIK = CRTrans.UOM LEFT JOIN FAssets ON FAssets.FACLEUNIK = CRTrans.FACLEUNIK LEFT JOIN Stock ON Stock.STCLEUNIK = CRTrans.STCLEUNIK LEFT JOIN Emp ON Emp.EMCLEUNIK = CRTrans.EMCLEUNIK LEFT JOIN PayGroup ON Emp.PGCleunik = PayGroup.PGCleunik LEFT JOIN Lookup AS PGLookupsAlias ON PayGroup.PayGroupPosition = PGLookupsAlias.LUCLEUNIK WHERE Crtrans.JOCLEUNIK = {pJOCleunik} AND Crtrans.FullyPaid = {pFullyPaid} AND Crtrans.CRCLEUNIK = {pCRCleunik} AND Crtrans.FinPeriod BETWEEN {pStartFinPeriod} AND {pEndFinPeriod} AND Crtrans.OTCLEUNIK = {pOTCleunik} AND Crtrans.TransactionDate BETWEEN {pStartTransDate} AND {pEndTransDate} AND Crtrans.ClaimFinPeriod BETWEEN {pStartClmFinPeriod} AND {pStartClmFinPeriod} AND ProjMemo.SourceFile = 'CT' ORDER BY {psBudget} ASC, {psCreditor} ASC, {psInvoiceNo} ASC |
| |
| |
| | | |
|
| | |
| |
| Publicado el 31,diciembre 2015 - 22:57 |
Hello Arie
Thanks for the code simplification, I will give it a go.
Regards
Al |
| |
| |
| | | |
|
| | |
| |
| Publicado el 01,enero 2016 - 12:25 |
Great explanation by Arie
IMHO the query generator went south when it was changed to accommodate the current SQL format (v18 iirc)
Now adds parenthesis all over the place, frequently does not run for complex queries and as someone on here once remarked turns the graphic display into something resembling the London Underground map.
As a result I tend to 'roll my own' which actually takes no longer than with the generator and has the added benefit of a. working and b. looking better.
This was provided by the generator (using an alias on the second link to LU_SysLookUps)...
SELECT Property.idProperty AS idProperty, Property.PK_Property AS PK_Property, Property.FK_Landlord AS FK_Landlord, LU_SysLookUps.Description AS _Accommodation, b.Description AS _PropType FROM ( LU_SysLookUps , LU_SysLookUps b ) RIGHT OUTER JOIN Property ON LU_SysLookUps.idLU_SysLookUps = Property.idLU_Accommodation AND b.idLU_SysLookUps = Property.idLU_PropertyType WHERE ( Property.idProperty = 1 ) When tested in WDSQL it will not run - complains of Unexpected word: ,
If I remove the set of parenthesis in FROM it does run BUT because of the AND on the second join the result is a Cartesian join.
If I changed the code to
SELECT Property.idProperty AS idProperty, Property.PK_Property AS PK_Property, Property.FK_Landlord AS FK_Landlord, a.Description AS _Accommodation, b.Description AS _PropType FROM LU_SysLookUps a, LU_SysLookUps b RIGHT OUTER JOIN Property ON a.idLU_SysLookUps = Property.idLU_Accommodation RIGHT OUTER JOIN Property ON b.idLU_SysLookUps = Property.idLU_PropertyType WHERE Property.idProperty = 1 I get the expected result returned.
It may seem a pain to write your own and use HExecuteSQLQuery() but once you get into it (with the occasional visit to http://www.w3schools.com/sql/) it is not that bad or time consuming.
Just my 2 cents worth |
| |
| |
| | | |
|
| | |
| |
| Publicado el 02,enero 2016 - 00:54 |
Hello Derek
It is getting clearer now.
Thanks you for the sql teaching site link, it is a very good reference site for me.
Regards
Al |
| |
| |
| | | |
|
| | | | |
| | |
|