"Lost Connection during Query" oder "wie kann ich dieses View erstellen"

[Captnemo]

Hi,

nach dem ich das mit den Unions erfolgreich umgesetzt habe (mehrere-spalte-im-group-zusammenfassen) stehe ich mit genau diese Abfrage vor dem nächsten Problem.

Hier noch mal das Statement zur besseren Übersicht worum es geht:

Code:

Select Feld from (
      select s_vl_felgengroesse as Feld from Reifenlager t1 union 
      select s_vr_felgengroesse as Feld from Reifenlager t1 union 
      select s_hr_felgengroesse as Feld from Reifenlager t1 union 
      select s_hl_felgengroesse as Feld from Reifenlager t4) t21 
      order by feld

(Das man das vielleicht auch etwas anders schreiben kann, sollte jetzt erst mal keine Rolle spielen, außer es dient der Problemlösung).

Es wird also eine Liste aller vorhandenen Felgengrößen (diese werden per Hand eingetragen, es gibt also keine Referenz) aus allen 4 Rädern gebildet.
Die gleiche Abfrage wird auch noch mit anderen Felder aus der Tabelle gemacht, insgesamt 10 mal.
Das das ganze möglichst ohne große Wartezeiten ablaufen soll, passiert das in einem Thread, der also 10 mal ein solches Statement absetzt und das Result dann an's Hauptprogramm liefert. Und der Thread wird immer aufgerufen, wenn der User das Formular wieder neu öffnet.
Die Tabelle, aus der die Daten abgerufen wird, hat ca. 850 Datensätze.

So, das läuft im Grund auch exakt so, wie ich es mir vorgestellt habe.
Nur kam es seit dem Code vermehrt zu "Lost Connection during Query". Nach langer Suche bin ich auch darauf gekommen, dass es genau diese Abfragen sind, die den Verbindungsabbruch auslösen.
Diese ganzen Unions und dann auch mal gleich 10 mal hintereinander, von 8-10 Usern im Tagesbetrieb und dass alle paar Minuten ist dann wohl doch irgendwie zu viel (Das ist eine Annahme, keine Behauptung ;-) )

Meine erste Lösung wäre es jetzt ein View im MySQL zu erzeugen, welches mir quasi eine Tabelle bereitstellt, in deren Felder die Felgengröße schon kumuliert zur Verfügung steht. Das würde die Last, wenn es denn die Ursache ist, schon mal einiges verringern.

Aber MySQL läßt mich nicht.
Das Erzeugen eines Views mit:

Code:

Create View 'test' as
Select Feld from (
      select s_vl_felgengroesse as Feld from Reifenlager t1 union 
      select s_vr_felgengroesse as Feld from Reifenlager t1 union 
      select s_hr_felgengroesse as Feld from Reifenlager t1 union 
      select s_hl_felgengroesse as Feld from Reifenlager t4) t21 
      order by feld

wird mit den Meldung Error 1349: View's Select contains a subquery in the From clause SQL Statement.

Wie müßt das Statement lauten, damit es als View akzeptiert wird?

Oder fällt jemand eine andere Möglichkeit ein, das Problem zu umgehen?

Ich nutze in diesem Projekt Zeos 6.6.0-Stable unter D7. Es ist ein altes Projekt, dass ich erst mal nicht umstellen will.
Die Abfragen im Thread laufen über die gleich ZConnection. Ist das vielleicht das Problem? Oder sollte das nicht stören?

Da ich nicht wußte, welches ich jetzt als Titel nehmen soll, hab ich mal beides reingesetzt.

[EarlyBird]

mit zwei Views sollte es funktionieren:

Delphi-Quellcode:

Create View 'test1' as
      select s_vl_felgengroesse as Feld from Reifenlager t1 union 
      select s_vr_felgengroesse as Feld from Reifenlager t2 union 
      select s_hr_felgengroesse as Feld from Reifenlager t3 union 
      select s_hl_felgengroesse as Feld from Reifenlager t4 

Create View 'test2' as     
Select Feld from test1 
      order by feld

[nahpets]

Nur erstmal so ein paar hingeworfenen "Gedankensplitter" ;-)

Selects, die per Union zusammengefügt werden, werden zur Dublettenbeseitigung von der Datenbank sortiert. Das Order by kann daher eigentlich entfallen, es wäre zumindest schon mal eine "Optimierungsmöglichkeit".

Wie oft werden die Felgengrößen verändert, ergänzt ...?

Eher sporadisch?

Dann erstelle Dir eine Tabelle in der Form:

Code:

create tbl_Felgengroessen as
    select s_vl_felgengroesse as Feld from Reifenlager t1 union
      select s_vr_felgengroesse as Feld from Reifenlager t1 union
      select s_hr_felgengroesse as Feld from Reifenlager t1 union
      select s_hl_felgengroesse as Feld from Reifenlager t1) t21

(Eventuell ist bei Deiner Datenbank die Syntax etwas anders, bitte ausprobieren)

Die Tabelle bindest Du dann überall dort ein, wo Du bisher Deine Union-Abfrage nutzt. Das dürfte den Aufwand, den die Datenbank betreiben muss, etwas reduzieren.

Du musst halt sicherstellen, dass diese Tabelle immer dann neu erstellt wird, wenn es bei den Felgengrößen eine Änderung gab.

Ob zur Laufzeit nun Dein Select ausgeführt wird oder die (momentan) nicht erstellbare View, dürfte egal sein. Bei einer Abfrage auf die View muss die Datenbank ja das dahinterliegende Select ausführen.

Eventuell lässt sich die View so erstellen:

Code:

create view v_Reifengroessen as
select s_vl_felgengroesse as Feld from Reifenlager 
union
select s_vr_felgengroesse as Feld from Reifenlager 
union
select s_hr_felgengroesse as Feld from Reifenlager 
union
select s_hl_felgengroesse as Feld from Reifenlager

Spitz formuliert ist die View ja nur eine "Schreibvereinfachung" für ein mehr oder weniger komplexes SQL-Statement.

Die Idee von EarlyBird mit zwei View ist ganz nett, aber ein

Code:

Select Feld from test1 order by feld

unterscheiden sich wohl weder im Aufwand noch im Ergebnis von

Code:

Select Feld from test2 order by feld

Oder: Die zweite View ist meiner Meinung nach entbehrlich.

Auch wenn die Datenmenge von 850 Sätzen jetzt nicht wirklich nach sehr viel aussieht, so dürfte die Häufigkeit der Abfragen die Datenbank doch schon etwas ans Schwitzen bringen.

In welcher Form benötigst Du die Ergebnisse dieser Abfragen im Programm?

Als Nachschlagtabellen, Inhalte von Listboxen ...?

Eventuell reicht es ja aus, wenn Du die Abfragen nur beim Programmstart lädst und dann innerhalb des Programmes in Listen bereithälst.

[Captnemo]

Zitat:

Zitat von nahpets (Beitrag 1335508)

Nur erstmal so ein paar hingeworfenen "Gedankensplitter" ;-)

Selects, die per Union zusammengefügt werden, werden zur Dublettenbeseitigung von der Datenbank sortiert. Das Order by kann daher eigentlich entfallen, es wäre zumindest schon mal eine "Optimierungsmöglichkeit".

Gut, das könnte sein. Aber das Order by wird dann ja auch nicht mehr großartig Last verursachen, da ja schon alles 'sortiert' ist.

Zitat:

Zitat von nahpets (Beitrag 1335508)

Wie oft werden die Felgengrößen verändert, ergänzt ...?


Eher sporadisch?

Das ist je nach Jahreszeit unterschiedlich.

Zitat:

Zitat von nahpets (Beitrag 1335508)

Dann erstelle Dir eine Tabelle in der Form:

Code:

create tbl_Felgengroessen as
    select s_vl_felgengroesse as Feld from Reifenlager t1 union
      select s_vr_felgengroesse as Feld from Reifenlager t1 union
      select s_hr_felgengroesse as Feld from Reifenlager t1 union
      select s_hl_felgengroesse as Feld from Reifenlager t1) t21

(Eventuell ist bei Deiner Datenbank die Syntax etwas anders, bitte ausprobieren)

Die Tabelle bindest Du dann überall dort ein, wo Du bisher Deine Union-Abfrage nutzt. Das dürfte den Aufwand, den die Datenbank betreiben muss, etwas reduzieren.

Das könnte ich natürlich auch machen. Aber diese Tabelle müsste ja auch immer wieder auf den aktuellen Stand gebracht werden. Das wollte ich ja im Grunde mit der View erreichen.

Zitat:

Zitat von nahpets (Beitrag 1335508)

Du musst halt sicherstellen, dass diese Tabelle immer dann neu erstellt wird, wenn es bei den Felgengrößen eine Änderung gab.

Ob zur Laufzeit nun Dein Select ausgeführt wird oder die (momentan) nicht erstellbare View, dürfte egal sein. Bei einer Abfrage auf die View muss die Datenbank ja das dahinterliegende Select ausführen.

Das ist vielleicht mein Denkfehler. Ich habe Views noch nie genutzt. Ich hatte gedacht das ein View vom SQL-Server immer dann aktualisiert wird, wenn sich an den Referenztabellen Daten verändern. Quasi auf Verdacht, so dass die Daten im View bei Bedarf bereit fix und fertig zur Verfügung stehen, und nicht erst erzeugt werden müssen, wenn die Abfrage kommt.

Zitat:

Zitat von nahpets (Beitrag 1335508)

Eventuell lässt sich die View so erstellen:

Code:

create view v_Reifengroessen as
select s_vl_felgengroesse as Feld from Reifenlager 
union
select s_vr_felgengroesse as Feld from Reifenlager 
union
select s_hr_felgengroesse as Feld from Reifenlager 
union
select s_hl_felgengroesse as Feld from Reifenlager

Spitz formuliert ist die View ja nur eine "Schreibvereinfachung" für ein mehr oder weniger komplexes SQL-Statement.

Das wäre natürlich dann kein wirklicher Vorteil. Ist zwar schön, dass man sich dann etwas Schreibarbeit spart, aber wo ist der effektive Vorteil?

Zitat:

Zitat von nahpets (Beitrag 1335508)

Die Idee von EarlyBird mit zwei View ist ganz nett, aber ein

Code:

Select Feld from test1 order by feld

unterscheiden sich wohl weder im Aufwand noch im Ergebnis von

Code:

Select Feld from test2 order by feld

Oder: Die zweite View ist meiner Meinung nach entbehrlich.

Das werde ich mal ausprobieren.

Zitat:

Zitat von nahpets (Beitrag 1335508)

Auch wenn die Datenmenge von 850 Sätzen jetzt nicht wirklich nach sehr viel aussieht, so dürfte die Häufigkeit der Abfragen die Datenbank doch schon etwas ans Schwitzen bringen.

Deswegen bekomme ich wahrscheinlich auch das "Lost Connection during Query". Obwohl die Abfrage trotzdem gefühlt nur 1 oder 2 Sekunden dauert. Es wundert mich doch schon.

Zitat:

Zitat von nahpets (Beitrag 1335508)

In welcher Form benötigst Du die Ergebnisse dieser Abfragen im Programm?

Als Nachschlagtabellen, Inhalte von Listboxen ...?

Eventuell reicht es ja aus, wenn Du die Abfragen nur beim Programmstart lädst und dann innerhalb des Programmes in Listen bereithälst.

Genau. Ich brauch sie für eine Combobox, für die Auswahl durch den User.
Ich könnte sie natürlich bei Programmstart laden. Da die Anwendung aber den ganzen Tag am Stück läuft, würden sich die Einträge nicht mehr aktualisieren. Und bei 10 Benutzern, würde das gerade in den Jahreszeiten wo sich genau diese Daten stark verändern dazu führen, dass der eigentliche Nutzen nicht mehr so unbedingt gegeben ist. Wenn ein neues Element hinzugekommen ist, soll diese am gleichen aber auch an anderen Arbeitsplätzen gleich zur Verfügung stehen. Schon allein um unterschiedliche Schreibweisen möglichst zu vermeiden.

Außerdem ändert das ja nichts an dem Problem. Dann würde im schlimmsten Fall die Fehlermeldung gleich zu Beginn kommen.
Und gerade weil diese Abfrage ja ein wenig länger braucht, als ein einfaches Select habe ich das in ein Thread gepackt.
Wenn im dem nur eine diese Abfragen laufen würde, wäre es vielleicht auch noch kein Problem, aber ich muss das für 10 verschiedenen Felder machen. Und zwischen den Abfragen innerhalb des Thread noch Sleeps einzufügen würde dessen Laufzeit auch wieder unnötig verlängern.

Wenn alle Stricke reißen, dann müsste ich halt die per union verknüpften Abfragen im Thread einzeln machen und dort zusammensetzen.

Mich wundert aber trotzdem, dass der Fehler "Lost Connection during Query" auftritt. Er kommt definitiv nur wenn ich die Query mit Union ausführen lasse. Lasse ich die Weg, habe ich kein Problem mehr. Nach allem was ich gelesen habe, kommt diese Fehlermeldung aber nicht vom Zeos selbst, sondern von der libmySQL50.dll und wird von Zeos nur weitergereicht. Von daher kann ich daran programmmäßig auch nicht wirklich viel ändern. Ein Timeout einstellen kann ich meines Wissens nicht. Außer vielleicht in der my.cnf.

[nahpets]

Bin mir nicht sicher, aber meine Vermutung ist, dass einfach die Menge der (eventuell sogar gleichzeitig) auf die Datenbank losgelassenen Abfragen, einfach die (Verbindungs-?)Kapazität zwischen Client und Datenbank übersteigt.

Schau bitte mal, ob es da technisch oder konfigurationsbedingt, irgendwelche Einschränkungen gibt.

Nehmen wir mal die Felgengröße als konkretes Problem:

Es können, für die vier Felgengrößen, zu beliebigen Zeiten, beliebig viele Änderungen eingepflegt werden.

Des weiteren gehen wir davon aus, dass Du Dich für die oben skizzierte "Feld"-Tabelle für die Felgengrößen entschieden hast.

Um diese Feldtabelle nun mit wenig Aufwand zu pflegen, könnte eventuell ein Datenbanktrigger für ein Insert in die Tabelle Reifenlager reichen.
Dieser Trigger wird immer dann von der Datenbank automatisch ausgeführt, wenn ein neuer Datensatz in die Tabelle Reifenlager eingefügt wird.
Innerhalb dieses Triggers müsste dann geprüft werden, ob es die Felgengrößen schon in der Feldtabelle gibt, wenn nicht, wird ein neuer Datensatz eingefügt.

Wenn aus dem Reifenlager ein Datensatz entfernt wird, so muss ein Trigger für das Ereignis Delete ausgeführt werden.
Dieser Trigger muss dann aus der Feldtabelle alle die Felgengrößen entfernen, die es nun in der Tabelle Reifenlager nicht mehr gibt.

Für Änderungen im Reifenlager muss es einen Trigger für das Ereignis Update geben. Hier ist dann zu prüfen, ob es alle Felgengrößen dieses Datensatzes schon in der Feldtabelle gibt, wenn nein, müssen sie hinzugefügt werden. Die Felgengrößen, die es nicht mehr gibt, müssen entfernt werden.

Ob das für die Datenbank dann weniger Arbeit ist und insgesamt schneller wird, als Deine bsher angedachte Lösung, vermag ich nicht zu sagen. Hier müsstest Du mal probieren, ob Du das mit My-SQL (da fehlt mir leider jegliche Erfahrung) realisieren kannst. Bei Oracle wüßte ich, wie ich vorgehen könnte, weiß aber nicht, ob und wie das auf andere Datenbanken übertragbar ist.

Zitat:

Gut, das könnte sein. Aber das Order by wird dann ja auch nicht mehr großartig Last verursachen, da ja schon alles 'sortiert' ist.

Naja, zumindest von Oracle kenne ich das so:

Zuerst wird das innere SQL ausgeführt, also die Unions un das Ergebnis wird sortiert. Es muss also temporär vorgehalten werden.

Anschließend wird das äußere SQL über die temporär vorgehaltenen Ergebnismenge ausgeführt und sortiert.

Ob und wieviel Last bei 'ner kleinen DAtenmenge dabei entsteht, mag ich nicht zu beurteilen.
Fakt ist, es ist überflüssig und sollte damit entfallen.
Auch viele, kleine, überflüssige, nur wenige resourcenverbrauchende, Abfragen erzeugen zusätzliche Laufzeit.
Und wenn mir ein System zu langsam ist, dann spare ich grundsätzlich alles ein, was Laufzeit kostet, auch wenn es (dem ersten Augenschein nach) nur auf die einzelne Abfrage sehr wenig ist.

Zitat:

Das wäre natürlich dann kein wirklicher Vorteil. Ist zwar schön, dass man sich dann etwas Schreibarbeit spart, aber wo ist der effektive Vorteil?

Naja, stell' Dir mal ein sehr komplexes System vor, in dem so ein Statement nicht mal eben in vier Zeilen "abzufackeln" ist, sondern eher mal ein paar hundert Zeilen lang wird.
Und nun wird dieses Statement nicht nur an einer Stelle benötigt, sondern an vielen, z. B. in einer Desktop-App, einem Webinterface, für den Reportgenerator ...

Nun muss man dieses Teil schon an drei Stellen pflegen. Mit 'ner View aber nur noch einmal.

Mal flappsig und überspitzt formuliert:
Alles was ich in Delphi mehr als einmal benötige wird zu 'ner Funktion oder Prozedure.

Jedes Select-Statement, das ich mehr als einmal benötige, wird zu 'ner View.

Ansonsten zur Fehlermeldung:

Schau bitte mal hier: https://www.benefind.de/web.php?q=%2...+mysql&org=307
Es scheint dort mehrere Lösungsansätze zu geben.

[p80286]

Eigentlich unglaublich, daß es diesen Fehler überhaupt gibt, aber sei's drum. Mit EarlyBirds Ansatz läzt sich der Fehler vermeiden (http://forums.mysql.com/read.php?61,...7705#msg-87705).
In einem View ist ein Sort meist überflüssig, da ein View oft nur Rohdaten für einen select zur Verfügung stellt. Der kann dann sortieren wie's gebraucht wird. Der Vorteil eines Views sind u.a. die entfallenden Prüfungen (Syntax). So ist ein select der Views nutzt im allgemeinen immer etwas schneller als ein größeres Select. So sollte es zumindest sein.
Und natürlich die Vorteile, die Stephan genannt hat, einmal testen, immer nutzen. Das als Schreibvereinfachung zu sehen ist schon stark untertrieben.

Gruß
K-H

[Medium]

Zitat:

Zitat von Captnemo (Beitrag 1335518)

Das ist vielleicht mein Denkfehler. Ich habe Views noch nie genutzt. Ich hatte gedacht das ein View vom SQL-Server immer dann aktualisiert wird, wenn sich an den Referenztabellen Daten verändern. Quasi auf Verdacht, so dass die Daten im View bei Bedarf bereit fix und fertig zur Verfügung stehen, und nicht erst erzeugt werden müssen, wenn die Abfrage kommt.

Das trägt jetzt evtl. nicht wirklich zur Problemlösung bei, aber zur Natur von Views: Ein View ist vom Konzept her datenlos, und eigentlich, naiv gesehen, nicht viel mehr als ein Select, dass die eigentlich dahinter liegende normalisierte Tabellenstruktur zur einfacheren Benutzung in ein semantisch sinnvolle Ansicht überführt. Es ist fast wie ein prepared Statement - und da ist mein Latein dann auch am Ende - ich weiss nicht genau was, ausser der Persistenz über eine Session hinaus, ein View anders macht als ein prepared Statement. Aber zumindest hält ein View keine eigene Tabelle, die irgendwie aktualisiert werden müsste. Die Selects werden immer live ausgeführt. (Oder halt aus dem Cache, wie jedes andere Select auch.)

[hoika]

Hallo,
es gibt Views, die tatsächlich Daten enthalten (materialized views),
siehe hier https://docs.oracle.com/cd/B10501_01...7/repmview.htm

dass ist hier aber nicht verwendbar (wegen Oracle, und Nutzung für Replikation).

Ein anderer Nutzungsaspekt bei Views ist die Rechteverwaltung.
Ich kann einem Nutzer das (Select-)Recht zur Ausführung eines Views geben,
ihm aber aber sämtliche Rechte der zugrundliegenden Tabellen wegnehmen.


Heiko

[Captnemo]

Also, ich habe mir jetzt mal die Views erstellt. Ohne 'order by xxx' sind die Inhalte im MySQL dann unsortiert.

Aber das kann man erst mal vernachlässigen. Schlimmer ist, dass der Fehler "Lost connection during Query" damit trotzdem auftritt. Gefühlt nicht mehr so oft, aber er tritt auf.

Ich kannte das natürlich über eine Trigger lösen, der bei einem Insert oder Update eine Referenztabelle pflegt. Damit muss ich mich erst mal befassen.
Oder aber, ich mache ein einfaches Select auf meine 4 Felder, und lasse die im Thread zusammenführen und sortieren. Ist sicherlich nicht schneller.

Ein anderer Gedanke, der mir grade kam. Das das ein sehr altes Projekt ist, nutze ich dort immer noch die libmysql50.dll, könnte das evtl. das Problem auslösen.
Und was mir auch noch auffällt, dass scheinbar tritt der Fehler nur auf, wenn ich eine SQL-Abfrage in einem Thread laufen lasse. Was ich vorher in dem Projekt nie gemacht habe.
Das lässt mich annehmen, dass es evtl. nicht zwangsläufig am SQL-Server liegen muss.

[Medium]

Das kann dann ein Problem werden, wenn du z.B. im Thread Komponenten nutzt die du auf dein Formular gelegt hast. Ich hatte bei MySQL Zugriffen auch schon Probleme damit, weiss nur nicht mehr ob das bei Zeos oder UniDAC war. Ich erstelle seither immer alle Ressourcen dafür im Thread selbst, und habe keine Probleme mehr gehabt.
Wichtig dabei ist: Im Konstruktor des Threads erstellte Dinge sind nicht im Threadkontext!! Daher quasi so:

Delphi-Quellcode:

type
  TMyThread = class(TThread)
  private
    FServerName: String;
    FPort: Integer;
    FUsername: String;
    FPassword: String;
    FCatalog: String;
  protected
    procedure Execute; override;
  public
    constructor Create(aConnection: TZConnection (*zur einfachen Übernahme der Verbindungsinfos von einer Connection auf dem Formular*));
  end;

implementation

constructor TMyThread.Create(aConnection: TZConnection);
begin
  inherited Create(false);
  FServerName := aConnection.Servername;
  FPort := aConnection.Port;
  ...
end;

procedure TMyThread.Execute;
var
  connection: TZConnection;
  query: TZQuery;
begin
  connection := TZConnection.Create(nil);
  connection.ServerName := FServerName;
  ...
  query := TZQuery.Create(nil);
  query.Connection := connection;
  try
    repeat
      ...
    until Terminated;
  finally
    connection.Free;
    query.Free;
  end;
end;

Oder die Connection und Query als Feld des Threads, falls man mal ein paar mehr Thread-Methoden aufrufen will und diese nicht immer als Parameter mitgeben will.