|
FOROS PROFESIONALES
WINDEV, WEBDEV y WINDEV Mobile |
| | | | | |
| Auto "Order by" added with query on MySql views |
| Iniciado por guest, 05,ago. 2016 20:54 - 3 respuestas |
| |
| | | |
|
| |
| Publicado el 05,agosto 2016 - 20:54 |
Hi. It's hard to explain this weird thing happening.. [WD20]
I created a View on MySql involving 3 tables by outer join.
When executing a query via query editor on this view instead of a common MySql table the HF engine (I use HExecuteQuery with HQueryDefault option) add an ORDER BY I never wrote in the editor followed by ALL the fields of the view.
The sad result is a query running 12-20 times slower in relation to its speed if run by hand via HExectuteSQLQuery with HQueryNoCorrection option.
I need to use the editor to take the advantage of filter parameters (enabled or disabled by Null query parameter). It's nonsense. It's a bug of the engine? |
| |
| |
| | | |
|
| | |
| |
| Publicado el 06,agosto 2016 - 05:59 |
if you are using query editor and HExecuteQuery with HQueryDefault option , don't assume wd will send query to the server with little change .
for mysql use neor profiler
http://www.profilesql.com/
specially when you call a query within a query .try and look at the profiler , you will shock |
| |
| |
| | | |
|
| | |
| |
| Publicado el 06,agosto 2016 - 13:52 |
Thanks.
The general issue is that HF engine creating SQL the actual queries running on mysql always append "ORDER BY" clause even if not needed.
This sometimes (on complex queries) tells MySql to use temporary tables and filesort.
Ex.
In a query I have a table Products in join with ProductLocalData... I never added ORDER BY Product.id in the editor but the profiler (or Putty) show me that the real query has that ORDER BY clause at end... |
| |
| |
| | | |
|
| | |
| |
| Publicado el 07,agosto 2016 - 02:56 |
I noticed that even in simple queries on one table it appends unuseful order by clause.
Is there a way to get rid of unwanted order by? |
| |
| |
| | | |
|
| | | | |
| | |
| | |
| |
|
|
|