PC SOFT

FOROS PROFESIONALES
WINDEV, WEBDEV y WINDEV Mobile

|
|
|
FR
Inicio → WINDEV 2024 → Filter tables on two string values
Filter tables on two string values
Iniciado por antonino.neri, 26,may. 2015 12:05 - 8 respuestas
#1
Publicado el 26,mayo 2015 - 12:05
Hi guys,

I have a table named CITY with these fields:

ID is a numeric field (unique key)
Region is a string field (duplicated key)
Country is a string field (duplicated key)
City is a string field (duplicated key)

Now I want to filter this table to find all the cities that are in a specific Region (example01) and a specific Country (example02):

I use this filter:

HFilter(CITY,Region,"Region="+example01+" AND Country="+example02)
HReadFirst(CITY,Region)
IF HFound(CITY) THEN
// Do something
ELSE
// Do something else
END
HDeactivateFilter(CITY)


It seems this filter does not work because it never finds the searched records even if they do exist in the table.
What am I doing wrong?

Thank you all for your care.
#2
Publicado el 26,mayo 2015 - 14:57
Hi Antonino,

without knowing the content of your variables, it's hard to say, but it
seems to me that you are missing the quotes around your values

IN any case, to do what you want, I would create a composite key
country+region, then do an hfilter with this syntax:
hfilter(CITY,Key_CountryRegion,[example02, example01],[example02,example01])

Result would be mush faster

Best regards

--
Fabrice Harari
International WinDev, WebDev and WinDev mobile Consulting

Already there: WXShowroom.com, WXReplication (open source)
Coming soon: WXEDM (open source)
More information on http://www.fabriceharari.com


On 5/26/2015 4:05 AM, Antonino wrote:
Hi guys,

I have a table named CITY with these fields:

ID is a numeric field (unique key)
Region is a string field (duplicated key)
Country is a string field (duplicated key)
City is a string field (duplicated key)

Now I want to filter this table to find all the cities that are in a
specific Region (example01) and a specific Country (example02):

I use this filter:

HFilter(CITY,Region,"Region="+example01+" AND Country="+example02)
HreadFirst(CITY,Region)
IF HFound(CITY) THEN
// Do something
ELSE
// Do something else
END
HDeactivateFilter(CITY)


It seems this filter does not work because it never finds the searched
records even if they do exist in the table.
What am I doing wrong?

Thank you all for your care.
#3
Publicado el 26,mayo 2015 - 17:45
Hi Fabrice,

thanks a lot for your help. You are right, I was missing the single quote around my values:

HFilter(CITY,Region,"Region='"+example01+"' AND Country='"+example02+"'")
HReadFirst(CITY,Region)
IF HFound(CITY) THEN
// Do something
ELSE
// Do something else
END
HDeactivateFilter(CITY)


I did not create a composite key because I have to process a few records (less than 100) so this does not require a long time but, to be honest, I was not aware that a composite key makes me save time. Thanks for this tip too.

Kindest Regards
#4
Publicado el 26,mayo 2015 - 19:19
Hi Antonino,

gald I could help... as for the composite key, it's simple, really

with your syntax and keys available only on each field, the hf engine
has no other choice than to use ONE of the two keys, find all the record
matching, then discard all the ones who do not match the second part of
the condition...

With the correct composite key and condition, ONLY the resulting records
are read... This is of course also true for data access done with queries

Best regards

--
Fabrice Harari
International WinDev, WebDev and WinDev mobile Consulting

Already there: WXShowroom.com, WXReplication (open source)
Coming soon: WXEDM (open source)
More information on http://www.fabriceharari.com


On 5/26/2015 9:45 AM, Antonino wrote:
Hi Fabrice,

thanks a lot for your help. You are right, I was missing the single
quote around my values:

HFilter(CITY,Region,"Region='"+example01+"' AND Country='"+example02+"'")
HreadFirst(CITY,Region)
IF HFound(CITY) THEN
// Do something
ELSE
// Do something else
END
HDeactivateFilter(CITY)


I did not create a composite key because I have to process a few records
(less than 100) so this does not require a long time but, to be honest,
I was not aware that a composite key makes me save time. Thanks for this
tip too.

Kindest Regards
#5
Publicado el 27,mayo 2015 - 18:02
Hi Fabrice,

thanks again for your explanations :)
Just one more question: according your experience, which command is faster between HFilter and HExecuteQuery?
In other words, I would like to understand if it's better to use filters when I am dealing with bulky tables or run queries.

Thanks for your time and care.


Regards,
Antonino
#6
Publicado el 27,mayo 2015 - 23:29
Hi Antonino

unfortunately, the answer to your question is much more complex than it
seems...

In theory, a query should always be faster, as the amount of data is the
same, but there is less back and forth...

However, a query will return ALL the results at once, while
hfilter+hreadfirst+hreadnext will return only the number of records that
YOU want initially

So a table linked directly to a file (ie using hreadfirst/next in the
background) will have an initial display faster than if it is linked to
a query returning a big number of records (the whole file; by example)...

For the same reason the file linked table will read ONLY the records
that are displayed, so if the user goes only 2 pages down, you'll be
reading way less data from the file...

Another potential problem with the rule about queries being faster: the
whole dataset is read on the server at once... If you have a hundred
users doing that aat the same time, your HFCS server (or any server)
becomes the overloaded...

If instead you were using hreadfirst/next, each one would be separated
from the next one by a small delay (process on the client side) and that
would in return gives the HFCS server a chance to process the hreadnext
of several others clients... IN that case, if you read the same number
of records in final, the overall time would be LONGER with the
hreadfirst/next, but the PERCEIVED time for each user would be shorter
as data would START to arrive and be displayed faster for EVERY CLIENT
(instead of having some clients waiting with nothing coming back for a
while).

For me, one thing to consider early when designing an application is
load balancing :
- a query will use a lot of server resources, few client resources
- a hreadfirst/next will user more client and network resource, but
server resources needed will be lower and less punctual.

Another one is PERCEIVED speed vs technical one, as explained with the
tables example above.

There are other cases that are very interesting to study, but the end
result is that in my experience, the ALMIGHTY query is not always the
solution, and that load balancing should be something to consider as
soon as you start coding.

And of course, answers and choices will be different for a one user
application VS a 100 users on HF C/S VS a web application VS a
replicated application...

Best regards

--
Fabrice Harari
International WinDev, WebDev and WinDev mobile Consulting

Already there: WXShowroom.com, WXReplication (open source)
Coming soon: WXEDM (open source)
More information on http://www.fabriceharari.com


On 5/27/2015 10:02 AM, Antonino wrote:
Hi Fabrice,

thanks again for your explanations :)
Just one more question: according your experience, which command is
faster between HFilter and HExecuteQuery?
In other words, I would like to understand if it's better to use filters
when I am dealing with bulky tables or run queries.

Thanks for your time and care.


Regards,
Antonino
#7
Publicado el 28,mayo 2015 - 09:37
Hi Fabrice,

thanks again for the detailed explanation.
I do understand that a unique answer does not exist and that every case must be analyzed separately, but let's suppose that both the query and the filter must return a single record (as it is in my case). Also consider that there is just one connection to the database, in fact we are talking about an application that runs in background and parses some huge (700 MB) csv files. So no concurrent access to the HFSQL files. Would you still use the query?

Thanks :)
#8
Publicado el 28,mayo 2015 - 11:09
Hi Antonino

no... I would not in that case... There is a help page somewhere where
pcsoft is detailing what to use when (general principle)... And I agree
on these general principles:

- if you need to read ONE or only very few records, use hreadseek (for
one), hfilter+hreadfirst/next (for a few)... In that case, the overhead
of analysing the query and running it is higher than the back and forth

- if you need to read MANY records, and specially if you need only PARTS
of the records, use a query

- for writing data, use hadd, hmodify, hdelete

Best regards

--
Fabrice Harari
International WinDev, WebDev and WinDev mobile Consulting

Already there: WXShowroom.com, WXReplication (open source)
Coming soon: WXEDM (open source)
More information on http://www.fabriceharari.com


On 5/28/2015 1:37 AM, Antonino wrote:
Hi Fabrice,

thanks again for the detailed explanation.
I do understand that a unique answer does not exist and that every case
must be analyzed separately, but let's suppose that both the query and
the filter must return a single record (as it is in my case). Also
consider that there is just one connection to the database, in fact we
are talking about an application that runs in background and parses some
huge (700 MB) csv files. So no concurrent access to the HFSQL files.
Would you still use the query?

Thanks :)
#9
Publicado el 28,mayo 2015 - 12:44
Hi Fabrice,

thanks a lot for your time and help.
Now everything is clear to me :)


Kindest Regards
→ Volver a WINDEV 2024