Diese Seite mit anderen teilen ...

Informationen zum Thema:
WinDev Forum
Beiträge im Thema:
Erster Beitrag:
vor 1 Jahr, 9 Monaten
Letzter Beitrag:
vor 1 Jahr, 9 Monaten
Beteiligte Autoren:
Stefano Giavardi, StefanoG, ccc2

Auto "Order by" added with query on MySql views

Startbeitrag von Stefano Giavardi am 05.08.2016 18: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?


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

specially when you call a query within a query .try and look at the profiler , you will shock

von ccc2 - am 06.08.2016 03:59
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.

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...

von Stefano Giavardi - am 06.08.2016 11:52
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?

von StefanoG - am 07.08.2016 00:56
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.