Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
8
Erster Beitrag:
vor 4 Jahren, 9 Monaten
Letzter Beitrag:
vor 4 Jahren, 9 Monaten
Beteiligte Autoren:
J. Flietstra, Ivan V., CCC2, GuenterP, Frans, DerekT

ORDER BY without database name in MYSQL

Startbeitrag von J. Flietstra am 27.10.2013 10:18

Hi ,

We found out that when you use the query builder in windev and make a select query Windev does not add the database name in the ORDER BY section

SELECT database.field1 AS field1,
database.field2 AS field2,
database.field3 AS field3,
database.field4 AS field4,
database.field5 AS field5,
FROM
database
WHERE
database.field5 = 'TEST'
ORDER BY
Field5

THERE IS A INDEX ON Field5!!!

When you execute this qyery in Windev with hExecuteQuery and the database has 1.6000.000 record, its takes a long time to execute this query because MYSQL is sorting the database on the fly with all the fields in the SELECT statement.
database.field1+database.field2+database.field3+database.field4+database.field5

When we change manualle the query and put the databasename in the ORDER BY statement

ORDER BY
database.Field5

It take 28 seconds to execute the query with hexecuteQuery...

I see that in all the querys build with windev, the database is NOT added in the ORDER BY statement..

So When you use WIndev and MYSQL your programma can get a performance boost when you add the name of the database in the ORDER BY section.

It should be nicer that PCSoft change this asap... They put the database name always except in the ORDER BY ...

Antworten:

Thanks for sharing

von Frans - am 27.10.2013 10:53
Hi,

thanks a lot!
That's interesting!

von GuenterP - am 27.10.2013 14:14
That's both interesting and strange.

I have never added the table name to an ORDER BY statement.
Just did a quick 'google' on ORDER BY - dozens of examples but none with the table name - not even the MySQL manual.

This would seem to indicate that the WD processing is incorrect.

Have you sent this to PCS ?

von DerekT - am 27.10.2013 17:13
We have send it to pcsoft...
We can check if this also happens if we execute the statement in the Workbensch from MySQL

I think it is a interpertation from Windev,
Maybe execute withoutcorrectiton will solve this?

von J. Flietstra - am 27.10.2013 17:27
why database.field1 ? shouldn't it be tablename.field1 . it kind confusing if u using the word 'database' cause in mysql it doesn't support direct reference field of a table from another database like MS-SQL do.

databasename.dbo.tablename.field1

if you really need use table from another database , you must first declare federate table

in query , I always specify tablename.field1 . this is because in complex query with tables that have same field name will cause error.

von CCC2 - am 30.10.2013 04:11
I have alot of this examples in my codes, i tried changing in one query and u know what, it was much faster executed.

This is some serious stuff that needs tobe handled by PCSoft.

von Ivan V. - am 30.10.2013 08:09
thats right...
it comes from clarion

Database should be tablename. :)

von J. Flietstra - am 30.10.2013 08:15
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.