[Solved] Help with SQL statement optimisation

[gkick]

WIN10, LO7.2.5.2 (x64) HSQL 2.5.1

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

The link to the mockup db
https://c.gmx.com/@740972710229711543/v ... 8-UsBHRd_Q


Appreciate any guidance to rewrite the statements leading to a more acceptable runtime, thank you

[eremmel]

Thanks for explaining your query and taking the effort to clarify the functional requirement.
This is what I understand:

Code: Select all

-- select any cdate from tblCalendar and add to each cdate 'belegt' when some condition exists
Q1: ( SELECT "a"."cdate", 
	( SELECT 'belegt' FROM "tblRes" 
		WHERE "itid_fk" = :inID
 			AND "a"."cdate" BETWEEN "from" AND "to" ) AS "item" 
 FROM "tblCalendar" AS "a"
 WHERE "a"."cdate" >= :stDatum)

-- If any 'belegt' in Q1 take the smallest cdate, else take max cdate of tblCalendar
Q2: SELECT "cdate" 
	FROM Q1 
WHERE "cdate" < COALESCE((SELECT MIN("cdate") FROM Q1 WHERE "item" = 'belegt'),(SELECT MAX ("cdate") FROM "tblCalendar"))

This is indeed an expensive operation, because the Q1 is used and (re)calculated at two places. It might help to look into WITH ... SELECT. But this might also do what you want:

Code: Select all

SELECT X.cdate FROM (
-- find the smallest cdate from calander when we have a hit on tblRes else this will be empty
SELECT MIN(a.cdate) as cdate
FROM tblCalendar a 
	INNER JOIN tblRes b ON a.cdate BETWEEN b.from AND b.to 
WHERE b.itid_fk = :inID
UNION
-- take the highest cdate from ....
SELECT MAX(cdate) FROM tblCalendar
) X
-- Take the first value. 
ORDER BY X.cdate
LIMIT 1

Edit: Addition: Create indexes on your tables: One index on cdate in table tblCalendar and the index { itid_fk , from, to } on table tblRes. Query will run in spit second

[gkick]

@eremmel

Thank you so much for that, will put into action tonight. Silly me forgetting all about the indexing. Thought about a view instead but couldn't figure it out. Must admit SQL is not my forte. Normally I find my way around but tend to get stuck with the complexity of using aliases for tables and subqueries.

Any tutorials on these aspects you could recommend? Thanks again for your help, been banging my head with this for some time.

[eremmel]

@gkick, I've age of learning from books and lots performance engineering experience. Even with databases like HSQLDB you can get execution plans so you can analyze it and see for any expensive things.
But i've no special tutorials at hand, just go to one of those online 'universities. I learned one important rule: when you can express a question in a correct sentence (that sentence might be complex) you can write a SQL statement for that.
Note that I wrote the SQL statement just as a solution direction. You might need to change the quoting etc here and there. But when you can write macro's that will be no issue. Success.

[gkick]

@eremmel
Thank you again and wow just by applying suggested indexes the original code s runtime
from 20 secs+ has been reducd to 5 secs. had a try with both alternative codesamples but ended
up with a expected case error possibly due to not double quotting or maybe HSQL nit supporting
Q1:
Will work out the finite details...
And yes some 30 years ago aspent a fortune on 3kgs books about dbase3 for dos, clipper,vba,dao,ado..

[eremmel]

You are should only experiment with the last SQL statement just above the EDIT section in my post of 1/13/2022. Remove any lines that start with dash dash '--' These are just comments. You can put all the remaining text on one single line in you code. You should fix the the quoting of tables/fields etc.
You should be able to get results in a split second.

[gkick]

@eremmel

Thanks for the hint on the one line statement, am almost there. Here is the revised code which seems to work well
that is no more errors with the SQL. Just need to fix the invalid value error in the declarations, grr

Code: Select all

SUB todate(oEvent AS OBJECT)
	DIM oForm AS OBJECT
	DIM oFeld AS OBJECT
	DIM inID AS INTEGER
	oFeldStart = oEvent.Source.Model
	oForm = oFeldStart.Parent
	oFeld = oForm.getByName("to")	
	inID = oFeldStart.CurrentValue	
	DIM stSql AS STRING
	stSql = "(SELECT ""X"".""cdate"" FROM (SELECT MIN(""a"".""cdate"") as ""cdate"" FROM ""tblCalendar""  ""a"" INNER JOIN ""tblRes"" ""b"" ON ""a"".""cdate"" BETWEEN ""b"".""from"" AND ""b"".""to""  WHERE ""b"".""itid_fk"" = "":inID"" UNION SELECT MAX(""cdate"") FROM ""tblCalendar"" ""X""  ORDER BY ""X"".""cdate"" LIMIT 1)	"
	oFeld.ListSource = stSql
	oFeld.refresh
END SUB

Thanks so much

[eremmel]

It is a long time ago that I did some scripting, but I used in the query :inID, the colon means bind-value (value to replace), so I think that you should concatenate the value inID in the stSQL as you did in your original code. Also have a look your original post you handle your variables in a different way.