[WD12] HReadFirst after HExecuteSQLQuery

Hello to you all,

After I execute a query with HExecuteSQLQuery, and I know the result is just one (or 2) records. The first time I read something from the result (HReadFirst or HNbRec) it takes about 1 second to get a result. Can anybody tell me why this takes so long?

Hello,

It only takes that long when I use HFCS?!

Can't anybody tell me why the HReadFirst is time-consuming when I use HFCS? Is there something I can do? I thought there is no difference in the created data source by HExecuteSQLQuery() when using HF or HFCS?



Edited 1 time(s). Last edit at 06/15/2009 05:25PM by Louis Verbraak.

Hello,

Is the view (HCreateView instead of HExecuteSQLQuery) the way to go?

Hi Louis...

if I'm not mistaken, the query itself is executed only the first time
you read something... The fact that it take so long is probably due to
missing keys in the file to allow an optimized access, but without more
information on the query, it's hard to be sure


Best regards

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

More information on [www.fabriceharari.com]


Louis Verbraak wrote:
> Hello to you all,
> After I execute a query with HExecuteSQLQuery, and I know the result is just one (or 2) records. The first time I read something from the result (HReadFirst or HNbRec) it takes about 1 second to get a result. Can anybody tell me why this takes so long?
>
Message forwarded from pcsoft.us.windev

Hello Fabrice,

Here is the query I'm talking about:

Quote

lsSQL="SELECT SUM(TelOpenstaandBedrag) AS Bedrag FROM HF_ADM_Transacties WHERE SorteerveldFactuur='"+lsSorteerveldFactuur+"'"
lsSQL+=" AND Sorteerveld<>'"+lsSorteerveldFactuur+"'"
// Alleen de gevraagde periodes meetellen.
lsSQL+=" AND (BoekjaarGeboekt<"+gwiBoekjaar
lsSQL+=" OR (BoekjaarGeboekt="+gwiBoekjaar+" AND PeriodeGeboekt<="+gwiPeriodeTem+"))"
// Alleen zelfde debiteur.
lsSQL+=" AND "+lsSubAdm+"='"+lsDebCred+"'"
gppTraceTijd("Pos.11 - Voor SQLQuery")
// Should I use the connection?
IF HExecuteSQLQuery(lqBestand,gpsConnection,hQueryDefault,lsSQL) THEN
//IF HExecuteSQLQuery(lqBestand,hQueryDefault,lsSQL) THEN
____gppTraceTijd("Pos.12 - Voor HReadFirst")
____HReadFirst(lqBestand)
____gppTraceTijd("Pos.13 - Na HReadFirst")
____IF NOT HOut(lqBestand) THEN
________// Do something
____END
END

The time for HReadFirst is over 1 second. It doesn't make a difference if I use the connection-parameter or not. Do you have any idea? All suggestions are welcome of course.

Hi Louis...

Not knowing the content of your file (and the variable names do not help me a lot here :-) ), I would say that you need a composite key with the following variables in it, in that order:

SorteerveldFactuur + Sorteerveld + lsSubAdm + BoekjaarGeboekt + PeriodeGeboekt

If you don't have one, create it and test again, as this key should allow the engine to find the desired records extremely fast

best regards

Hello Fabrice,

Thanks for your suggestion. I'll try that.

When this is the problem I suppose I need to create a composite key for every combination a record must been found.

Is it neccesary that every part of a composite key is a key for itself?

Louis,
I noticed that the operators < and <= can slow down the query, when using hfcs. Maybe the hfcs-engine is not so good at this point.
At least in one case I rewrote the query using the IN operator. After that my query was instant instead of approx. 2 seconds.
(yes - I did have the proper keys and indexes)

With the IN operator however you need to know the IN-values, or you can get them via a subquery. Looking at your SQL I think you are able to determine the values before executing. Something like:

lsSQL+=" AND (BoekjaarGeboekt IN (2006,2007,2008)
lsSQL+=" OR (BoekjaarGeboekt=2009 AND PeriodeGeboekt IN (1,2,3,4,5))"

Not very generic, I agree, but it worked in my case.

Arie

Hi again...

not necessary at all... You need a composite key containing all the variables of the "=" conditions of your query then the ">=" ones, but each variable doesn't need to be a key.

If you use the query editor included in windev, it will automatically propose the appropriate composite key to add for you

best regards

Hello Fabrice,

Thanks again.

Hello Arie,

Thanks for your input. But creating the propriate composite key seems to work well in my case.

Hi again...

I must say that I personally have never seen a case where creating the appropriate composite key DIDN'T work... As long of course as an optimized file reading is POSSIBLE (contains in conditions is a case where it's not possible, by example)

best regards