|
| Iniciado por mat, 17,nov. 2005 15:16 - 3 respuestas |
| |
| | | |
|
| |
Publicado el 17,noviembre 2005 - 15:16 |
Hi,
Several queries working fine since WD7.5 would not work, or only sporadically, under WD9. It now appears that the problem is related to sub-queries, using IN (SELECT...). In one case, using totals in a subquery, the query had to be split into two. The main problem is that while some queries never work in WD9, they might work some of the time which makes testing extremely difficult.
The example below is one where the query would work in maybe 20% of the cases. I have many queries like these and there is just no telling wether they will work alright under all circumstances since they are put together based on user selection of search criteria. I'm quite simply afraid of deploying the application based on WD9 for the time being.
The subquery in WD8 allowed a response time of 1-2 seconds for finding the 38 records of the example, the WD9 query takes 6-7 seconds. This is better than not working at all but a further demonstration that HF can still not cope well with queries on multiple files.
The essence of this post is to be careful when moving with anything but the the most simple HF queries from WD8 to WD9.
Regards
Mat
WD8 query using sub-query IN clause, taking 1-2 seconds for returning 38 records, but only working sporadically in WD9
========================================================================
SELECT ...
FROM InvoiceDetail INNER JOIN Invoice ON InvoiceDetail.IDInvoice = Invoice.IDInvoice
WHERE Invoice.IsNew = 0
AND Invoice.Date >= '20051001'
AND Invoice.Date <= '20051031'
AND Invoice.RecType >= 20
AND Invoice.RecType <= 29
AND Invoice.IDInvoice IN (SELECT InvoiceDetail.IDInvoice FROM InvoiceDetail WHERE InvoiceDetail.IDCompany IN (SELECT AG.IDAddress FROM Address_AddressGroup AS AG WHERE AG.IDAddressGroup=1))
ORDER BY BusinessSector, ProductRange, ProductGroup, IdProduct, Date
Solution giving same result in WD9 and working all the time but taking 6-7 seconds for returning 38 records
==============================================================================
SELECT ...
FROM InvoiceDetail INNER JOIN Invoice ON (InvoiceDetail.IDInvoice = Invoice.IDInvoice),
InvoiceDetail INNER JOIN Address_AddressGroup ON (InvoiceDetail.IDCompany = Address_AddressGroup.IDAddress)
WHERE Invoice.IsNew = 0
AND Invoice.RecType >= 20
AND Invoice.RecType <= 29
AND Invoice.Date >= '20051001'
AND Invoice.Date <= '20051031'
AND Address_AddressGroup.IDAddressGroup=1
ORDER BY BusinessSector, ProductRange, ProductGroup, IdProduct, Date |
| |
| |
| | | |
|
| | |
| |
Publicado el 19,noviembre 2005 - 07:41 |
G'day Mat
You will be aware from previous posts that I never use SQL in Hyperfiles. It has never worked well and I am convinced it is an inherent design failure that was perpetuated in the V7 database engine rebuild. Your current difficulties only confirm this for me.
In your example I would use a view and filters or two filters to create a set of invoice detail records and I believe that with the appropriate indexes on the files that the response time would be acceptable.
//Could use a filter on the Invoice file instead of a view)
hcreateview(Invlist,Invoice,Index,Fieldlist,conditions)
hreadfirst(invlist)
while not hout(invlist)
hfilter(InvDetail,IDInvoice,Invoice.IDInvoice)
Hreadfirst(InvDetail,IDInvoice)
While not Hout(InvDetail)
If (invoice detail = addressgroup and IDcompany conditions)
Tableadd(Table,indetails)
end
Hreadnext(InvDetail,IDInvoice)
end
Hreadnext(Invlist)
end
Tablesort(Table,"BusinessSector,Productrange,IDProduct,Date"
I would be really interested in the timings if you could take the time to try using views or just all filters.
Regards
Al |
| |
| |
| | | |
|
| | |
| |
Publicado el 19,noviembre 2005 - 13:56 |
G'day Mat
You will be aware from previous posts that I never use SQL in Hyperfiles. It has never worked well and I am convinced it is an inherent design failure that was perpetuated in the V7 database engine rebuild. Your current difficulties only confirm this for me.
In your example I would use a view and filters or two filters to create a set of invoice detail records and I believe that with the appropriate indexes on the files that the response time would be acceptable.
//Could use a filter on the Invoice file instead of a view)
hcreateview(Invlist,Invoice,Index,Fieldlist,conditions)
hreadfirst(invlist)
while not hout(invlist)
hfilter(InvDetail,IDInvoice,Invoice.IDInvoice)
Hreadfirst(InvDetail,IDInvoice)
While not Hout(InvDetail)
If (invoice detail = addressgroup and IDcompany conditions)
Tableadd(Table,indetails)
end
Hreadnext(InvDetail,IDInvoice)
end
Hreadnext(Invlist)
end
Tablesort(Table,"BusinessSector,Productrange,IDProduct,Date"
I would be really interested in the timings if you could take the time to try using views or just all filters.
Regards
Al
Hi Al,
Thanks a lot for your help and example. The shortcomings of WD7 queries should not really have surprised me, given the meagre WD5.5 version. Unfortunately, I left myself be convinced by the WD7 presentation where queries had a prominent place. Because I was pleased with WD5.5 the "improvements" on queries made me chose WD instead of Delphi for the re-write of my most important DOS application.
However, I always found a syntax giving consistently correct results. The problems were in the response times for multi-file queries and concurrent file access/modification which slowed down query performance up to WD8. To obtain the first, I reduced most queries returning potentially more than just a handful of records to one, maximum two files. This also improved a little the 2nd problem.
I have been in direct contact several times with PC Soft about the matter, they never suggested that filters or views would improve performance. Actually, I believe that views are internally translated to queries. In the end, their syntax will be similar to my 2nd example (simple joins). In fact, under WD7.5, I made many trials with filters and views in an attempt to find quicker response times but never found any great differences. It's also fairly certain that queries on HF are nothing more than filters on the files which explains partially poor performance with concurrent file modification.
Indexes are another story, but I'm not short of them. Some index files are more than twice the data file size.
Unfortunately, your example is based only on two files while mine is based on three. The "address group" info is not available in the invoice but a relation file (IDcompany in the file InvoiceDetail pointing to IDAddress in the file Address_AddressGroup). Actually, there are potentially up to 5 files needed for selection and another two for descriptions. I realize that virtually anything can be done with views and filters but do you have examples where you link more than two files. If that worked better than a straight query, but is internally also based on queries, then the answer should be that I'd have to split sub-queries into single queries, e.g. put all the addresses belonging to a searched group into a "data source" and then use that data source in the final query, either joining or by sub-query, whichever is more efficient.
Actually, in WD7.5/8 I tried to chain together declared queries. In theory that should give the same result. In practice, it didn't and in addition slowed down incredibly opening such queries, as well as compiling the project.
Anyway, your input has made me think along other lines which might lead to a solution, for which I'm grateful. It's however disturbing how much time I have had to spend over the last three years trying to optimize performance of HF queries. Even more so since all of this worked alright under WD8.
Regards
Mat |
| |
| |
| | | |
|
| | |
| |
Publicado el 21,noviembre 2005 - 12:56 |
Hi Al,
I have been in direct contact several times with PC Soft about the matter, they never suggested that filters or views would improve performance. Actually, I believe that views are internally translated to queries. In the end, their syntax will be similar to my 2nd example (simple joins).
Al,
further tests confirmed the theory. I took rather long to realize how it all works. The result of a view is a data source. Thus your joining two views is nothing else than joining two queries on two data sources. Actually, I already applied that solution two weeks ago when moving a totalizing sub-query using and IN clause, plus HAVING, into a data source. I didn't see the implications for other queries at that time, though.
As I wanted to avoid simple join queries for performance reasons I searched further and finally tracked down the real problem to be with nested sub-queries having two or more IN clauses.
Based on the above theory of data sources, I put the second IN clause into an independent query, using a data source for the result, while compiling the WHERE clause of my main query (according to user input). The main query then has an IN clause on the data source: WHERE Invoice.IDCompany IN (SELECT IDAddress from myDataSource). I did the same with putting two IN clauses into an independent query with one sub-query, and that works, too.
Though this is complicating query design, at least the main query works again consistently and, even better, without losing performance which, alas for HF queries, is not the greatest to start with anyway.
Regards
Mat |
| |
| |
| | | |
|
| | | | |
| | |
|