|
| [WD17] I shot down the HFSQL Server with a Query |
| Iniciado por guest, 22,ene. 2015 22:42 - 7 respuestas |
| |
| | | |
|
| |
| Publicado el 22,enero 2015 - 22: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 ? |
| |
| |
| | | |
|
| | |
| |
| Publicado el 23,enero 2015 - 08:47 |
Hi Michael,
not direct a solution, but WD20 should be optimized for the use of "is in the list" parameters. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 23,enero 2015 - 09:22 |
Hih ow do you define your list.
I use list as well and have hd no problems so far
regards
Allard |
| |
| |
| | | |
|
| | |
| |
| Publicado el 23,enero 2015 - 09:30 |
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. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 23,enero 2015 - 10:57 |
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. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 23,enero 2015 - 11:15 |
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. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 24,enero 2015 - 11:39 |
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 |
| |
| |
| | | |
|
| | |
| |
| Publicado el 24,enero 2015 - 16:17 |
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) |
| |
| |
| | | |
|
| | | | |
| | |
|