Hi all,
Am in the process of adapting a sample db to a multi purpose rental database.
All coming together nicely, however I am experiencing extremely slow execution time of a couple of
queries used as source for listboxes in frmbookings.
The first one is run via macro to populate a listbox with available rental days for a specific item from the current date forward
which works sort of ok after 2-3 seconds.
The macro tests.from is run on lost focus of the client listbox
Can live with this
The macro tests.todayte which is executed on modified of the Desde(from) listbox works as expected
but takes 25 secs plus to execute (with very little testdata)
The macro is...
Code: Select all
SUB todayte(oEvent AS OBJECT)
DIM oDatField AS OBJECT
DIM oForm AS OBJECT
DIM oFeld AS OBJECT
DIM inID AS INTEGER
DIM stSql(0) AS STRING
DIM stTeilsql AS STRING
DIM stDatum AS STRING
oDatField = oEvent.Source.Model
stDatum = oDatField.CurrentValue.Year & "-" & Right("0" & oDatField.CurrentValue.Month , 2) & "-" & Right("0" & oDatField.CurrentValue.Day , 2) 'cdate in SQL-Schreibweise
oForm = oDatField.Parent
oFeld = oForm.getByName("to")
inID = oForm.getByName("item").CurrentValue
stTeilsql = "( SELECT ""a"".""cdate"", ( SELECT 'belegt' FROM ""tblRes"" WHERE ""itid_fk"" = '"+inID+"'"
stTeilsql = stTeilsql + " AND ""a"".""cdate"" BETWEEN ""from"" AND ""to"" ) AS ""item"" FROM ""tblCalendar"" AS ""a"""
stTeilsql = stTeilsql + " WHERE ""a"".""cdate"" >= '"+stDatum+"')"
stSql(0) = "SELECT ""cdate"" FROM "+stTeilsql +" WHERE ""cdate"" < COALESCE((SELECT MIN(""cdate"") FROM "+stTeilsql+" WHERE ""item"" = 'belegt'),(SELECT MAX (""cdate"") FROM ""tblCalendar""))"
oFeld.ListSource = stSql
oFeld.refresh
END SUB
https://c.gmx.com/@740972710229711543/v ... 8-UsBHRd_Q
Appreciate any guidance to rewrite the statements leading to a more acceptable runtime, thank you