Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
19
Erster Beitrag:
vor 1 Jahr
Letzter Beitrag:
vor 1 Jahr
Beteiligte Autoren:
Noel Tanti, Harry W, DerekT, GuenterP, Fabrice Harari, ICI, Paulo Oliveira, DarrenF, steve erts, harry w, Abdelillah ELFASSI

Best Practice - hreadseek or sql query?

Startbeitrag von Noel Tanti am 30.10.2016 18:15

Hi everybody,

I am always wondering what is the best practice in getting one record and update few records: hreadseek(file,etc) or do an sql query?

Thanks
Noel

Antworten:

Hi Noel.
Hreadseekxxxx() functions are always faster then sql query to find one record at the time, if you have an index on that field.

von ICI - am 02.11.2016 08:08
Hi Noel

Best practice, as stated by PCSoft in the help:
- read one record: hreadseek
- read multiple records: query
- update record (add/edit/delete): hadd, hmodify, hdelete

There are some specific cases where it's not true, but in general, I agree

Best regards

von Fabrice Harari - am 02.11.2016 09:02
Hi Fabrice and ICI

does the same apply when the server is a remote one and working through a VPN?

Thanks
Noel

von Noel Tanti - am 02.11.2016 11:38
Hi Noel.
This is just my opinion.
VPN can be slow connection for all cases .

MyConnection..compresssion = True
can make it little faster .
It is your decision based on your case, tests, experience

1. WHEN EXCEPTION IN
2. HTransactionStart ()
can be very usable

See help: HTransactionStart

Regards !

von ICI - am 02.11.2016 17:47
Hreadseek gets the entire record.
With SQL you can retrieve only the fields you need.

von harry w - am 03.11.2016 08:16
Hi,

According to me SQL queries are more universal than Hxxxxx. and this is true in case of migration to another system doing tests inside a DBMS etc...

Regards
A. ELFASSI

von Abdelillah ELFASSI - am 03.11.2016 09:24
Hi, there is no real difference in time between reading the whole record and reading a few field. The actuator of the hard disk has to move and read the record anyway. If there is no field to be read from the .mmo file there shouldn't be any difference in time.


Quote
harry w
Hreadseek gets the entire record.
With SQL you can retrieve only the fields you need.


von GuenterP - am 03.11.2016 10:08
OK, maybe no difference on the database server but there is less IO on the network.

von Harry W - am 03.11.2016 10:38
This most compelling reason to use sql is the compatibility with different file systems.
Clients might 'grow out' of HFSQL as when a new it guy comes along and wants to change to something he is comfortable with.


Regards
Noel

von Noel Tanti - am 03.11.2016 10:54
I'd recommend using hfilteridentical if you want to read exact record or set of records. hreadseek looks for first match OR HIGHER and can get you trouble IMO.

btw, I use h commands (and SQL) with SQL server and mysql db's with no problem.

I never use hfsql.

von steve erts - am 03.11.2016 22:31
Use hreadseekfirst for an exact match read.

I agree with Fabrice; in broad terms, use h... functions for singular read/write/delete and queries for multiples; like populating table controls, or generating a list in general.

von DarrenF - am 03.11.2016 23:28
Lets say i use MSSQL as database in combination with Windev NativeAccess and i want to read 1 record.

What to use ? HReadSeek or SQL query ? (using HExecuteSQLQuery..WhitOutCorrection)

I think SQL query.... because there is no translation to a sql statement....

von Harry W - am 04.11.2016 09:12
Hi, it depends whether you're using a Native Access or OLE DB or ODBC for access of the db ... Native Access uses H-commands directly - no translation afaik.

von GuenterP - am 04.11.2016 09:27
Quote
GuenterP
Hi, it depends whether you're using a Native Access or OLE DB or ODBC for access of the db ... Native Access uses H-commands directly - no translation afaik.


No translation ???
When you use Hreadseek in your Windev code to read a record in MSSQL database then there must be a translation to 'sql language' or do i mis something ????

von Harry W - am 04.11.2016 10:38
With native access if you use H*** functions the native access convert the H** function to SQL.

Some of the convertions are not the best ones, with MSSQL you can use the profiler to see what convertion is made.

You you need to use HADD/HMODIFY/HDELETE (for instance to use triggers in code) you must use HREAD** if not just use queries,

In some situations you have to use queries, if you use complex queries with sub-queries or you want ot use specific option of the DB engine you must use hQueryWithoutCorrection

When you use queries with hQueryDefault the native access will try to convert your SQL to convert the data type of the DB to the WX data type.
One sample is if you use MSSQL 2000 the columns in the DB are always datetime (if you create the db tables with HCREATION) and in WX you can have date or time fields.

von Paulo Oliveira - am 04.11.2016 11:57
No one really seems to know exactly what 'H' commands do - be nice if PCS gave us some info.

Personally I have always believed that conversion to SQL always happens regardless of the DB - HFSQL or any other you may use.

HReadSeek() has to have a 'WHERE ValueSort >= Param'
HReadSeekFirst() has to have a 'WHERE ValueSort = Param'

I cannot see why or how the 'H' commands would do something differently to the Query generator or indeed a 'RollYourOwn' query.
A well formed query is after all as about optimized as it can get if you have the correct indexes on the table.

Of course like Harry W I could be missing the 'bleeding obvious'

von DerekT - am 04.11.2016 12:00
Hi Derek,

Maybe, the bleeding obvious thing is that the hxxx functions existed WAY before they added SQL support.
So, my understanding is that the code behind the hxxx function is the low level code to access HF (classic or C/S), the kernel, if you want...
Then the SQL layer on top adds a high level logic allowing for complex queries. But once the query engine reach the point of retrieving data, it will use the same low level/kernel functions (ie the hxxx functions, as they are coded in their dll).

This would explain the recommendations I listed (coming from the help, I must say again):

when acceding one record (read or write), it's faster to call the low level function, as SQL would just add a layer. When acceding multiple records at once, it's faster to use the SQL layer, being coded in C ad ran directly on the DB, than loop in wlanguage on the client side to call the low level functions (hxxx).

Well, that's my understanding only...

Of course, when running against another DB than HF, than the problem is completely inverted, as the hxxx functions have to be translated into a sql query that the foreign DB can understand. In that case, there is no advantage in using hreadseek (except maybe for code simplicity sake).

Best regards

von Fabrice Harari - am 04.11.2016 12:55
Fabrice

Good explanation.
I had not even considered the pre SQL scenario.

Bit of a late starter here as WD was already on v5.5 when I first got it and I did not really start using it until v7.5

von DerekT - am 04.11.2016 17:02
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.