Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
8
Erster Beitrag:
vor 3 Jahren, 1 Monat
Letzter Beitrag:
vor 3 Jahren, 1 Monat
Beteiligte Autoren:
Michael Drechsel, Piet van Zanten, DerekT, Allard, Stefan Bentvelsen

[WD17] I shot down the HFSQL Server with a Query

Startbeitrag von Michael Drechsel am 22.01.2015 21:42

Hi,

today I got a urgent mail from one of my customer: The HF SQL Server is not responding.

After some tests with a copy of the database at my developer laptop I saw the reason:

I have a query with a lot of parameters, 4 or 5 with "is in the list". My lists are GUIDs (32 chars+";").
If the user chooses 3 of theses parameters, the query returns an answer after 5 seconds, if he clicks also the 4 th parameter the HF SQL Server has 25 % utilisation and never returns an answer. The parameters are not the reason, 3 at the same time working, 4 not.

The only way to stop this is to restart the hf sql server.

Any ideas ?

Antworten:

Hi Michael,

not direct a solution, but WD20 should be optimized for the use of "is in the list" parameters.

von Stefan Bentvelsen - am 23.01.2015 07:47
Hih ow do you define your list.

I use list as well and have hd no problems so far

regards

Allard

von Allard - am 23.01.2015 08:22
Hi Allard,

My tables have always a key with a GUID. If the user want to get information about more then one item I add the GUIDs and put these string as a parameter in a query. Runs for years.

But I never have had so much GUID-Lists at the same time in the past.

von Michael Drechsel - am 23.01.2015 08:30
Michael

Had a similar problem, although the sever never refused to respond, when using multiple options submitted as a 'CONTAINS' statement.
Cannot remember but I think this was with HFSQL v18 and the max length of the strings I was using was 15.

What I did in the end was to write the query by hand and for the multiple strings I was sending as 'in the list' I ran a loop and inserted these in the query as individual 'OR value = target' conditions and called the query using HExecuteSQLQuery().
Not saying it completely solved the problem but a did make a great difference and got me of the hook.

Personally I have always thought that hand written queries operate more efficiently than those created in the Query editor but I may be fooling myself on this one.

Bit more work but may help in your situation.

von DerekT - am 23.01.2015 09:57
Hi Derek,

finally I solved it. My query joins 5 files and I moved some of the the parameters to other (=source) files.
Maybe I have overestimated the intelligence of the HF SQL query optimisation algorithm.

von Michael Drechsel - am 23.01.2015 10:15
Hi Michael,

You can create a query in the editor and copy and paste the SQL code to text to tweak it.
I use hExecuteSQLquery often if a query is only used once in my project.
If you like you can paste it back into the query and let WX reverse engineer it.
I often do that to optimize joins or remove redundant files that WX adds to the query.

Regards,
Piet

von Piet van Zanten - am 24.01.2015 10:39
Hi Piet,

I know. But my current problem has to do with the "intelligence" of the hf sql server.

Lets say you have two files:

Customer with GUID and Contract_GUID
Contract with GUID

and a query

select * from customer,contract where customer.contract_guid=contract.guid

The customer file has 1.000.000 records and the contract file has 1.000 records.

Till yesterday I thought thats no matter where I have to put the Parameter "contract_guid" (in the contract file or in the customer file). But thats not true. You have to set the parameter in the smaller file.
The speed ratio is 1:30 in my case (the query is much larger as in this simple example)

von Michael Drechsel - am 24.01.2015 15:17
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.