Hallo Leute,
(endlich ist das Forum wieder online.)
Meine Frage:
Gibt es eine Möglichkeit, die Sortierung in Access auf 'natürlich' einzustellen?
Der Explorer sortiert z. B. so:
datei 1
datei 2
...
datei 9
datei 10
datei 11
...
Access hingegen sortiert binär, also
datei 1
datei 10
datei 11
datei 2
...
datei 9
Ich habe keine Einstellung in Access 2016 gefunden, wo man das umstellen könnte. Bleibt als einzige Möglichkeit nur, führende Nullen einzufügen, also
datei 01
datei 02
...
datei 09
datei 10
datei 11
oder gibt es eine andere Lösung?
Edit 1. Okt. 2016: habe den Thread-Titel auf "Natürliche Sortierung" geändert
Hallo,
Du kannst über eine Abfrage die Zahl in ein extra Feld extrahieren (als Zahl!) und dann nach der Zahl sortieren.
Wie genau ist denn der Dateistring aufgebaut ?
Gibt es immer ein Leerzeichen vor der Zahl ?
Was steht denn genau in den Feldern, ein Dateiname ?
In Access kann man nur pfuschen - ihm fehlen eigene Datentypen und Arrays nach denen sortiert werden kann. Außerdem können keine Indexe auf Ausdrücken beruhen. D. h. die nachfolgende Funktion wird mit zunehmender Datenmenge und zunehmender Verschachtelung Abfragen langsam machen.
Man lege einen VBA-Verweis auf die Microsoft VBScript Regular Expressions 5.5 Bibliothek an und stelle folgende Funktion in einem allgemeinen Modul zur Verfügung.Public Function GetSortPart$(ByVal Value, Optional ByVal PartNum& = 1)
Static RegExp As New VBScript_RegExp_55.RegExp
Dim matchPart$
If Nz(Value) = vbNullString Or PartNum < 1 Or PartNum > 3 Then
GetSortPart = "0"
Else
With RegExp
'// 1) Nicht-Ziffernfolge feststellen 0..n (soviel wie möglich)
'// 2) Ziffernfolge feststellen 0..n (soviel wie möglich)
'// 3) der Rest
.Pattern = "(\D*)(\d+)(.*)"
'// Treffer auslesen
matchPart = .Execute(Value)(0).submatches(PartNum - 1)
Select Case PartNum
Case 1, 2
If Len(matchPart) Then
'// Zeichen- oder Ziffernfolge zurückgeben
GetSortPart = matchPart
Else
'// reicht zum Sortieren und kann in eine Zahl umgewandelt werden
GetSortPart = "0"
End If
Case 3
'// wir wollen den Rest hinter der Ziffernfolge
GetSortPart = matchPart
End Select
End With
End If
End Function
Angewendet auf eine Tabelle data|----+---------------|
| id | filename |
|----+---------------|
| 1 | datei 1 |
| 2 | datei 2 |
| 3 | datei 3 |
| 4 | datei 11 |
| 5 | datei 10 |
| 6 | datei 22 |
| 7 | datei 13 |
| 8 | datei 14 |
| 9 | datei15 |
| 10 | datei7 |
| 11 | datei 19 |
| 12 | datei 1116 |
| 13 | datei 5 |
| 14 | datei 3.2 |
| 15 | datei 3.0.1 |
| 16 | datei3.0.0 |
| 17 | datei3.0.2 |
| 18 | datei 3.1.1 |
| 19 | 4 datei 28 |
| 20 | 3datei5.1 |
|----+---------------|sortiert diese Abfrage dannSELECT d.id,
d.filename
FROM data AS d
ORDER BY Trim(GetSortPart([filename])),
CLng(GetSortPart([filename], 2)),
CLng(GetSortPart(GetSortPart([filename], 3), 2)),
CLng(GetSortPart(GetSortPart(GetSortPart([filename], 3), 3), 2));die Daten zu diesem Ergebnis:|----+---------------|
| id | filename |
|----+---------------|
| 20 | 3datei5.1 |
| 19 | 4 datei 28 |
| 1 | datei 1 |
| 2 | datei 2 |
| 16 | datei3.0.0 |
| 3 | datei 3 |
| 15 | datei 3.0.1 |
| 17 | datei3.0.2 |
| 18 | datei 3.1.1 |
| 14 | datei 3.2 |
| 13 | datei 5 |
| 10 | datei7 |
| 5 | datei 10 |
| 4 | datei 11 |
| 7 | datei 13 |
| 8 | datei 14 |
| 9 | datei15 |
| 11 | datei 19 |
| 6 | datei 22 |
| 12 | datei 1116 |
|----+---------------|
PS: in der Praxis wird man vermutlich kaum so verrückte Leerzeichen wie im Beispiel haben.
Hallo,
ist das nicht mit Kanonen auf Spatzen geschossen ?
Man weis ja auch nicht ob das wirklich um Dateinamen geht. Es war ja möglicherweise nur ein Beispiel.
Es wäre ja vielleicht auch sinnvoll, das in der Tabelle gleich auf 2 Felder aufzuteilen.
Und wenn wie dargestellt, die Zahl hinten steht mit einem Leerzeichen davor, kann man die Zahl mit Mid(x,y) abschneiden und als Zahl sortieren.
Daher wäre erst mal abzuwarten, bis man die angefragten Zusammenhänge/Hintergründe kennt.
Nun, die Fragestellung ist ja nach natürlicher Sortierung ausgerichtet - egal wie und wo Ziffern angeordnet sind. Und da ist Access einfach nicht mehr zeitgemäß mit seinem uralten SQL-Dialekt. Wie eine moderne Datenbank eine natürliche Sortierung implementieren kann, ist in diesem Beispiel (http://sqlfiddle.com/#!15/adb3c/3) zu sehen.
Zitat von: Lachtaube am August 13, 2016, 08:59:48Und da ist Access einfach nicht mehr zeitgemäß mit seinem uralten SQL-Dialekt. Wie eine moderne Datenbank eine natürliche Sortierung implementieren kann, ist in diesem Beispiel (http://sqlfiddle.com/#!15/adb3c/3) zu sehen.
Das SQL Fiddle zeigt eine benutzerdefinierte Funktion. Der Implementierungsansatz ist genau der gleiche wie in deinem (übrigens sehr guten) Beispiel oben.
Die einzigen, mittelbaren Unterschiede sind, dass du mit einem Server-DBMS (SQL Fiddle war für PostgreSQL) die Funktion bereits serverseitig implementieren kannst, und, unter gewissen Umständen, auch die Ergebnisse indizieren kannst.
Access kann aufgrund seiner Architektur als
Desktop-Datenbank beides nicht. Das hat aber nichts mit dem SQL-Dialekt und seiner "Modernität" zu tun.
Ob Desktop- oder Server-Datenbanksystem dürfte für die Implementierung von Funktionalitäten eher eine untergeordnete Rolle spielen. SQLite3 ist auch eine reine Desktop-Datenbank und weist eine Schnittstelle für Collating Sequences (http://www.sqlite.org/c3ref/create_collation.html) auf (klar, auch hier muss man die Sortierung selbst implementieren).
Zitat von: Lachtaube am August 13, 2016, 11:16:38
Ob Desktop- oder Server-Datenbanksystem dürfte für die Implementierung von Funktionalitäten eher eine untergeordnete Rolle spielen.
Nein, keineswegs. Eine Desktop-Datenbank führt, per Definition, die DB-Engine auf dem Client (Desktop) aus. Eine serverseitige Implementierung jedweder Funktionalität ist damit ausgeschlossen.
Eine Indizierung von funktionsbasierten, virtuellen Spalten ist auch bei den DBMS, die das grundsätzlich können, nur mit sehr strikten Einschränkungen möglich. - Mit PostgreSQL kenne ich mich kaum aus, würde aber aus wager Erinnerung dort dazu das Stichwort
Purity Level anführen.
In Access/VBA ist eine Überprüfung von benutzerdefinierten VBA-Funktionen auf die dafür erforderlichen Merkmale (z.B. Determinismus) aufgrund des durch externe Bibliotheken praktisch unbegrenzten Funktionsumfangs schlichtweg unmöglich.
Zitat von: Lachtaube am August 13, 2016, 11:16:38
SQLite3 ist auch eine reine Desktop-Datenbank und weist eine Schnittstelle für Collating Sequences (http://www.sqlite.org/c3ref/create_collation.html) auf (klar, auch hier muss man die Sortierung selbst implementieren).
Das ist eine schöne Möglichkeit spezifisch Sortierreihenfolgen zu implementieren. Läuft aber, soweit ich das ohne SQLLite-Kenntnisse beurteilen kann, vom Endergebnis aus das gleiche Hinaus, wie dein Access/VBA-Beispiel.
Natürlich wäre es wünschenswert, wenn der Funktionsumfang von Access/ACE/Jet größer wäre. Die konkreten Punkte, die du hier beanstandest, haben jedoch nichts damit zu tun, dass Access "nicht modern" ist, sondern beruhen auf grundsätzlichen Architekturentscheidungen in der Implementierung der DB-Engine. Andere System mit einer vergleichbaren Architektur haben (bzw. hätten, ich weiß nicht ob es sie wirklich gibt) ähnliche Einschränkungen.
Hier wäre eine Umsetzung für eine SQLite3-Datenbank. Der Code verwendet Olaf Schmidts hervorragendes vbRichClient-Framework (http://vbrichclient.com) mit einem VBA-Verweis auf vbRichClient5.
Zunächst wird eine Klasse für die Implementierung unserer Custom Collation benötigt.'// KlassenModul: AdditionalCollations
Option Explicit
Implements ICollation
Private NC As cStringCompare, LCID_DE&
Private Sub Class_Initialize()
'// Wrapper um die API-Funktion CompareString
Set NC = New_c.StringCompare
LCID_DE = NC.MakeLCID(German_Germany)
End Sub
Private Function ICollation_CallbackCollate(ByVal ZeroBasedNameIndex As Long, _
S1 As String, S2 As String) As Long
Const cmpDigitalsAsNumbers = 8
Select Case ZeroBasedNameIndex
Case 0
'// Sortierung wie im "Explorer"
ICollation_CallbackCollate = NC.CompareString(S1, S2, _
cmpIgnoreCase Or _
cmpDigitalsAsNumbers, LCID_DE)
End Select
End Function
Private Property Get ICollation_DefinedNames() As String
'// Name der Sortierung festlegen
ICollation_DefinedNames = "DE_NC"
End PropertyDer Test erfolgt in einem allgemeinen Modul.Option Compare Database
Option Explicit
Public Sub TestNaturalCollation()
Dim cnn As cConnection, va, v
'// Beispieldaten
va = Array(Array("street", "9, James street"), _
Array("street", "10, James street"), _
Array("street", "10 ter, James street"), _
Array("street", "10 bis"), _
Array("street", "James street"), _
Array("street", "5, Rudolph street"), _
Array("version", "v1.10.1"), Array("version", "v1.10.1.10"), _
Array("version", "v1.10.1.9"), Array("version", "v1.10.1.9.7"), _
Array("version", "v1.10.1.9.14"), Array("version", "v1.9", "v1.10"))
'// Memory-Datenbank erstellen
Set cnn = New_c.Connection(OpenMode:=DBCreateInMemory)
'// unsere Sortierung hinzufügen
cnn.AddUserDefinedCollation New AdditionalCollations
'// Tabelle erzeugen
cnn.Execute "CREATE TABLE t (grp text not null, txt text not null);)"
'// für Memory-Datenbank nicht relevant
'cnn.BeginTrans
'// Anfügeabfrage anlegen
With cnn.CreateCommand("INSERT INTO t (grp, txt) VALUES (?, ?);")
'// Beispieldaten auslesen
For Each v In va
'// Parameter setzen
.SetText 1, CStr(v(0)): .SetText 2, CStr(v(1))
'// Daten in Tabelle einfügen
.Execute
Next
End With
'cnn.CommitTrans
'// ADODB-Recordset erzeugen
With cnn.OpenRecordset("SELECT * FROM t ORDER BY 1, 2 COLLATE DE_NC;") _
.GetADORsFromContent
'// sortierte Ausgabe in Direktbereich schreiben
Debug.Print .GetString(, , vbTab, vbCrLf)
End With
End SubUnd hier dann das Ergebnis:street 5, Rudolph street
street 9, James street
street 10 bis
street 10 ter, James street
street 10, James street
street James street
version v1.9
version v1.10.1
version v1.10.1.9
version v1.10.1.9.7
version v1.10.1.9.14
version v1.10.1.10
Hallo!
Sorry, dass ich erst jetzt antworte, war leider krank.
In der Tat ist mein Beispiel wohl zu stark vereinfacht, es sollte nur dazu dienen, die "natürliche" Explorer-Sortierung darzustellen. Das Wort "datei" ist keineswegs konstant, die Zahlen existieren nicht immer, es gibt also auch Daten ohne Zahlen.
Die Idee, eine API-Funktion zu nutzen, gefällt mir sehr gut, denn immerhin schafft es der Explorer ja auch.
Ich tendiere dazu, ein zusätzliches Feld "Sortierung" in meine Tabelle einzufügen und die Originaldaten in passende Sortierdaten zu wandeln. Das wäre vielleicht aus Performancegründen sinnvoll.
Erstmal vielen Dank für die Beispiele.
Zitat von: crystal am August 16, 2016, 11:44:46Die Idee, eine API-Funktion zu nutzen, gefällt mir sehr gut, denn immerhin schafft es der Explorer ja auch.
Die API-Funktion, die der Windows Explorer dafür verwendet ist
StrCmpLogicalW (https://msdn.microsoft.com/en-us/library/bb759947.aspx). - Allerdings habe ich im Moment keine Idee, wie man diese sinnvoll im Kontext einer Access-Abfrage einsetzen könnte.
Hallo PhilS,
ZitatAllerdings habe ich im Moment keine Idee, wie man diese sinnvoll im Kontext einer Access-Abfrage einsetzen könnte.
So geht' mir auch. Die einzige Möglichkeit besteht wohl darin, einen Sotierwert zusätzlich in der Tabelle zu führen.
Ich fand dafür LCMapStringEx, https://msdn.microsoft.com/en-us/library/windows/desktop/dd318702(v=vs.85).aspx
Diese Funktion konvertiert einen String entsprechend der Spracheinstellung und kennt u.a. auch das Flag SORT_DIGITSASNUMBERS beim Flag LCMAP_SORTKEY.
Aber wie kann ich LCMapStringEx in VBA benutzen? Vielleicht kannst du ein Beispiel bauen, denn wie man einen Wrapper definiert, ist zu viel für mich. Es müsste eine Funktion sein, der man den Originalstring übergibt und die dann den konvertierten String zurückgibt. Für meine Zwecke könnten Flags und andere Parameter hart gesetzt sein.
Die Desktop-Datenbank Access hat keine öffentliche Schnittstelle, um CompareString, CompareStringEx, LCMapStringEx, lstrcmp, lstrcmp direkt nutzen zu können. Was damit geht, ist, ein Datenfeld (Array) in VBA zu sortieren.
Wie groß ist denn der Datenbestand der Tabelle, und ist Access als Backend in Stein gemeißelt?
Hallo Lachtaube,
Ja, ich weiß, daß Access keine "offizielle" Schnittstelle dafür hat.
Aber ich denke doch, dass es prinzipiell möglich wäre, die genannte API-Funktion aufzurufen und zu nutzen, um einmalig alle Daten meines Datenfeldes in ein zusätzliches Sortierfeld in der Tabelle zu kopieren.
Ich will also nicht ein
"ORDER BY sortnatural(tabellenfeld)",
sondern ein
"ORDER BY sortfeld"
erreichen, wobei "tabellenfeld" den Originalstring und "sortfeld" einen sortierbar konvertierten String enthält.
Ein solches Vorgehen wird übrigens auch im MSDN beim Thema "Sorting" aus Performance-Gründen empfohlen, was ja auch offensichtlich und naheliegend ist.
Um dieses "sortfeld" zu erzeugen, dachte ich mir, es wäre vielleicht sinnvoll, LCMapStringEx zu benutzen, statt eine eigene Funktion zu bauen.
LCMapStringEx übrigens sortiert nicht, sondern "übersetzt" nur einen String in einen sortierbaren Wert, der dann mit binärem, byte- oder sogar bitweisem Vergleich sortiert werden kann.
Ähnliches könnte ich für die (zu stark abstrahierten) Daten aus meinem initialen Beispiel erreichen, indem ich eine Funktion baue, die die zu sortierenden Werte schlicht mit ausreichen vielen führenden Nullen ergänzt. Aber LCMapStringEx ist ja wohl auch in der Lage, eine entspr. Übersetzung für landesspezifische Zeichen (z.B. Umlaute) zu machen. Warum also das Rad neu erfinden, statt diese API-Funktion einfach zu benutzen?
Ich bin nur leider nicht in der Lage, einen VBA-Wrapper für LCMapStringEx zu bauen.
Meine Tabelle besteht momentan aus knapp 10.000 Datensätzen, Tendenz steigend...
Hallo
hier ein Ansatz, ist aber so nicht voll funktionsfähig, evtl noch ein wenig mit den flag spielen:
Private Declare Function LCMapStringEx Lib "KERNEL32.DLL" _
( _
ByVal lpLocaleName As Long, _
ByVal dwMapFlags As Long, _
ByVal lpSrcStr As Long, _
ByVal cchSrc As Long, _
ByVal lpDestStr As Long, _
ByVal cchDest As Long, _
ByVal lpVersionInformation As Long, _
ByVal lpReserved As Long, _
ByVal lParam As Long _
) As Long
Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoW" _
( _
ByVal Locale As Long, _
ByVal LCType As Long, _
ByVal lpLCData As Long, _
ByVal cchData As Long _
) As Long
Private Declare Function GetUserDefaultLCID Lib "kernel32" () As Long
Private Function GetLocale(lLocaleEnum&, Optional ByVal lLCID As Long) As String
Dim lRet As Long
Dim sret As String
If lLCID = 0 Then lLCID = GetUserDefaultLCID()
lRet = GetLocaleInfo(lLCID, lLocaleEnum&, StrPtr(sret), 0)
sret = String(lRet, 0)
lRet = GetLocaleInfo(lLCID, lLocaleEnum&, StrPtr(sret), lRet)
If lRet Then GetLocale = Left(sret, lRet - 1)
End Function
Public Function GetSortKeyString(ByVal stxt As String, Optional ByVal LCID As Long = 1031) As String
Dim sLocale$
Dim sret$
Dim lRet&
Const lFlag& = &H400 Or &H8
sLocale = GetLocale(92, LCID)
lRet = LCMapStringEx(StrPtr(sLocale), lFlag, StrPtr(stxt), Len(stxt), StrPtr(sret$), 0, 0, 0, 0)
If lRet Then
sret$ = String$(lRet, 0)
lRet = LCMapStringEx(StrPtr(sLocale), lFlag, StrPtr(stxt), Len(stxt), StrPtr(sret$), lRet, 0, 0, 0)
GetSortKeyString = Left(sret, lRet)
End If
End Function
die Funktion "GetSortKeyString" dann mal in deiner Abfrage aufrufen
PS: das flag müsste wohl lauten : &H400 Or &H8 Or &H800
Hi daolix,
von wegen "von nix nen plan", wie du in deinem Profil schreibst...
Das ist genau, was ich mir vorgestellt habe. Allerdings werde ich deine Funktion "GetSortKeyString" nicht in einer Abfrage aufrufen, sondern um das zusätzliche "sortfeld" in meiner Tabelle zu erzeugen.
Ich werde berichten, ob und wie es geklappt hat.
Danke für deine Mühe, diesen Wrapper zu schreiben und dabei sogar noch GetLocaleInfo etc. zu berücksichtigen!
Scheint mir insgesamt eine excellente Lösung zu sein!
Gruß,
crystal
Wie gesagt mit den Flags musst du noch ein wenig spielen, denn immo wird nicht unterschieden zw z.b. "D 9" und "D9", ausser du definierst eine sekundäre Sortierung ala "..ORDER BY sortfeld, Tabellenfeld
Zitat von: daolix am August 17, 2016, 14:47:27die Funktion "GetSortKeyString" dann mal in deiner Abfrage aufrufen
PS: das flag müsste wohl lauten : &H400 Or &H8 Or &H800
Mit den Konstanten ist es weniger kryptisch:
Private Const LCMAP_SORTKEY = &H400 ' WC sort key (normalize)
Private Const LCMAP_BYTEREV = &H800 ' byte reversal
Private Const SORT_DIGITSASNUMBERS As Long = &H8 ' use digits as numbers sort methodIch habe auch ein wenig mit der LCMapStringEx-Function gespielt. - Grundsätzlich genau der richtige Ansatz. Ich sehe nur ein grundlegendes Problem in Verbindung mit Access als Backend.
LCMapStringEx generiert als Sortkey ein Byte-Array. Access kann nicht über Binärfelder sortieren. Wenn man das Byte-Array jetzt als String behandelt, verwendet Access für die Sortierung die jeweils für die DB eingestellt Sortierreihenfolge. Dabei kann es aber IMO passieren, dass die Sortierung am Ende nicht der binären Sortierung der Bytes entspricht und damit nicht mehr korrekt ist.
Übersehe ich etwas?
ZitatÜbersehe ich etwas?
Nö, du hast recht, wie ich gerade übelst feststellen durfte.
ZitatMit den Konstanten ist es weniger kryptisch:
Dat ist wohl war, is halt die faulheit die siegt beim Übernehmen der Werte aus nen anderen dialekt.
Ich glaube dafür ist mir eine Lösung eingefallen:
Private Function GetByteString(buffer() As Byte, ByVal buffLen As Long) As String
Dim retVal As String
Dim i As Long
For i = 0 To buffLen - 1
retVal = retVal & Format(buffer(i), "000") & ","
Next i
GetByteString = retVal
End Function
Mit der Function fülle ich die Byte-werte immer auf drei Stellen mit Nullen auf und hänge sie als String hintereinander. - Das sollte funktionieren.
Das Endergebnis entspricht allerdings noch nicht so ganz meinen Erwartungen. (Siehe Screenshot).
Was wiederum geht, ist, ein Binärfeld anzulegen, dessen Kapazität allerding maximal 510 Bytes umfasst. Das Ergebnis aus LCMapStringEx lässt sich dann innerhalb der Größenbeschrnkung ablegen, und das Binärfeld kann zum Sortieren verwendet werden.Option Explicit
'int LCMapStringEx(
' _In_opt_ LPCWSTR lpLocaleName,
' _In_ DWORD dwMapFlags,
' _In_ LPCWSTR lpSrcStr,
' _In_ int cchSrc,
' _Out_opt_ LPWSTR lpDestStr,
' _In_ int cchDest,
' _In_opt_ LPNLSVERSIONINFO lpVersionInformation,
' _In_opt_ LPVOID lpReserved,
' _In_opt_ LPARAM sortHandle
');
Private Const LOCALE_NAME_USER_DEFAULT$ = vbNullString
Private Const LCMAP_SORTKEY& = &H400 ' // WC sort key (normalize)
Private Const SORT_DIGITSASNUMBERS& = &H8 ' // use digits as numbers sort method
#If VBA7 Then
Private Declare PtrSafe Function LCMapStringEx Lib "kernel32" ( _
ByVal lpLocaleName As LongPtr _
, ByVal dwMapFlags As Long _
, ByVal lpSrcStr As LongPtr _
, ByVal cchSrc As Long _
, ByVal lpDestStr As LongPtr _
, ByVal cchDest As Long _
, Optional ByVal lpVersionInformation As LongPtr _
, Optional ByVal lpReserved As LongPtr _
, Optional ByVal lParam As Long _
) As Long
#Else
Private Declare Function LCMapStringEx Lib "kernel32" ( _
ByVal lpLocaleName As Long _
, ByVal dwMapFlags As Long _
, ByVal lpSrcStr As Long _
, ByVal cchSrc As Long _
, ByVal lpDestStr As Long _
, ByVal cchDest As Long _
, Optional ByVal lpVersionInformation As Long _
, Optional ByVal lpReserved As Long _
, Optional ByVal lParam As Long _
) As Long
#End If
Public Function GetMappedStringArray(ByVal Source$) As Byte()
Dim r&, dest() As Byte
r = LCMapStringEx(StrPtr(LOCALE_NAME_USER_DEFAULT), _
LCMAP_SORTKEY Or SORT_DIGITSASNUMBERS, _
StrPtr(Source), Len(Source), 0, 0)
If r Then
ReDim dest(r - 1)
r = LCMapStringEx(StrPtr(LOCALE_NAME_USER_DEFAULT), _
LCMAP_SORTKEY Or SORT_DIGITSASNUMBERS, _
StrPtr(Source), Len(Source), _
VarPtr(dest(0)), _
UBound(dest) + 1)
GetMappedStringArray = dest
End If
End Function
Zitat von: Lachtaube am August 17, 2016, 18:20:12Was wiederum geht, ist, ein Binärfeld anzulegen, dessen Kapazität allerding maximal 510 Bytes umfasst.
Natürlich, per DDL, du hast recht!
Leider kann man die Funktion mit Rückgabewert
Byte() nicht direkt in eine Abfrage einbauen. Also muss man erst über ein Recordset in die Tabelle den binären SortKey schreiben und kann dann danach sortieren. - Was mir nicht ganz so gut gefällt, weil es Update-Anomalien mit sich bringen könnte.
Auch wenn das Byte-Array sicherlich der eigentlich performantere und bessere Datentyp wäre, würde ich trotzdem meinen Lösungsvorschlag mit der Umwandlung des Byte-Arrays in einen String vorziehen. Das kann man direkt in einer Abfrage verwenden, ohne den SortKey speichern zu müssen.
Noch eine Anmerkung zu meinem Screenshot mit der inkorrekten Sortierung: Dies wurde offenbar durch den Flag
LCMAP_BYTEREV verursacht, den ich eingebaut hatte, um seine Auswirkung zu testen. Außerdem hatte ich mit
LCMAP_BYTEREV gelegentliche Access-Abstürze, wenn ich die Funktion in der Recordset-Schleife für die ganze Tabelle aufgerufen habe. Ohne den Flag ist das Ergebnis so wie ich es erwartet hätte.
ZitatDies wurde offenbar durch den Flag LCMAP_BYTEREV verursacht, den ich eingebaut hatte
hmm, also bei mir nicht. Da hat deine Idee sowohl mit als auch ohne diesem Flag funktioniert.
Das Flag LCMAP_BYTEREV käme dann sinnvoll zum Einsatz, wenn der SrcStr im Big Endian Format vorläge.
Tja, auf Anomalien muss man natürlich immer achten. Im Zweifelsfall muss man halt noch einmal vorher eine Aktualisierungsabfrage laufen lassen. Der Vorteil des Binärfelds besteht darin, dass es indiziert werden kann.
Ich habe noch zwei weitere Versionen der Funktion erstellt. Eine, die auch in Abfragen auf das Binärfeld angewendet werden kann (z. Bsp. zum Aktualisieren) und eine, die zur Sortierung in Abfragen geeignet ist (Verzicht auf Index-Nutzung). Diese wird deutlich mehr Kapazität als die Version der aneinandergeketteten "000",-Bytefolgen aufweisen und auch schneller sein, weil hier die Zeichen-Verkettung entfällt. Hier noch einmal der komplette Code.Option Explicit
'int LCMapStringEx(
' _In_opt_ LPCWSTR lpLocaleName,
' _In_ DWORD dwMapFlags,
' _In_ LPCWSTR lpSrcStr,
' _In_ int cchSrc,
' _Out_opt_ LPWSTR lpDestStr,
' _In_ int cchDest,
' _In_opt_ LPNLSVERSIONINFO lpVersionInformation,
' _In_opt_ LPVOID lpReserved,
' _In_opt_ LPARAM sortHandle
');
Private Const LOCALE_NAME_USER_DEFAULT$ = vbNullString
Private Const LCMAP_SORTKEY& = &H400 ' // WC sort key (normalize)
Private Const SORT_DIGITSASNUMBERS& = &H8 ' // use digits as numbers sort method
#If VBA7 Then
Private Declare PtrSafe Function LCMapStringEx Lib "kernel32" ( _
ByVal lpLocaleName As LongPtr _
, ByVal dwMapFlags As Long _
, ByVal lpSrcStr As LongPtr _
, ByVal cchSrc As Long _
, ByVal lpDestStr As LongPtr _
, ByVal cchDest As Long _
, Optional ByVal lpVersionInformation As LongPtr _
, Optional ByVal lpReserved As LongPtr _
, Optional ByVal lParam As Long _
) As Long
#Else
Private Declare Function LCMapStringEx Lib "kernel32" ( _
ByVal lpLocaleName As Long _
, ByVal dwMapFlags As Long _
, ByVal lpSrcStr As Long _
, ByVal cchSrc As Long _
, ByVal lpDestStr As Long _
, ByVal cchDest As Long _
, Optional ByVal lpVersionInformation As Long _
, Optional ByVal lpReserved As Long _
, Optional ByVal lParam As Long _
) As Long
#End If
'// Nur zur Verwendung in Recordsets geeignet
Public Function GetMappedStringAsByteArray(ByVal Source$) As Byte()
Dim r&, dest() As Byte
r = LCMapStringEx(StrPtr(LOCALE_NAME_USER_DEFAULT), _
LCMAP_SORTKEY Or SORT_DIGITSASNUMBERS, _
StrPtr(Source), Len(Source), 0, 0)
If r Then
ReDim dest(r - 1)
r = LCMapStringEx(StrPtr(LOCALE_NAME_USER_DEFAULT), _
LCMAP_SORTKEY Or SORT_DIGITSASNUMBERS, _
StrPtr(Source), Len(Source), _
VarPtr(dest(0)), _
UBound(dest) + 1)
'// mehr als 510 Bytes passen nicht in ein Binärfeld
If r > 510 Then ReDim Preserve dest(509)
GetMappedStringAsByteArray = dest
End If
End Function
'// Zur Verwendung in Abfragen und Recordsets - nicht zur Sortierung in Abfragen geeignet
Public Function GetMappedStringArrayAsAnsiString$(ByVal Source$)
Dim r&, dest() As Byte
r = LCMapStringEx(StrPtr(LOCALE_NAME_USER_DEFAULT), _
LCMAP_SORTKEY Or SORT_DIGITSASNUMBERS, _
StrPtr(Source), Len(Source), 0, 0)
If r Then
ReDim dest(r - 1)
r = LCMapStringEx(StrPtr(LOCALE_NAME_USER_DEFAULT), _
LCMAP_SORTKEY Or SORT_DIGITSASNUMBERS, _
StrPtr(Source), Len(Source), _
VarPtr(dest(0)), _
UBound(dest) + 1)
'// mehr als 510 Bytes passen nicht in ein Binärfeld
If r > 510 Then ReDim Preserve dest(509)
GetMappedStringArrayAsAnsiString = dest
End If
End Function
'// Zur Verwendung als Sortierschlüssel in Abfragen und Recordsets geeignet
'// ob Access dabei mehr als 255 Zeichen berücksichtigt, müsste man prüfen
'// - ich vermute eher nicht
Public Function GetMappedStringArrayAsUTF16$(ByVal Source$)
Dim r&, dest() As Byte
r = LCMapStringEx(StrPtr(LOCALE_NAME_USER_DEFAULT), _
LCMAP_SORTKEY Or SORT_DIGITSASNUMBERS, _
StrPtr(Source), Len(Source), 0, 0)
If r Then
ReDim dest(r - 1)
r = LCMapStringEx(StrPtr(LOCALE_NAME_USER_DEFAULT), _
LCMAP_SORTKEY Or SORT_DIGITSASNUMBERS, _
StrPtr(Source), Len(Source), _
VarPtr(dest(0)), _
UBound(dest) + 1)
GetMappedStringArrayAsAnsiString = StrConv(dest, vbUnicode)
End If
End FunctionPS: Wer nicht weiß, wie ein Binärfeld erstellt werden kann, kann temporär die Systemtabellen der Datenbank einblenden und sich z. Bsp. aus der MSysObjects-Tabelle das Owner-Feld in seine Tabelle kopieren und umbenennen.
Hi Leute,
wirklich Klasse, was ihr hier für Antworten geliefert habt!!!
LCMapStringEx liefert ja scheinbar ein Byte-Array, bei dem je 2 Bytes einem Unicode-Zeichen entsprechen, oder? Wenn das so ist, könnte man diese je 2 Bytes nicht auch in ASCII-Äquivalente zurückwandeln, um "sortfeld" lesbar zu bekommen? Denn das Byte-Array ist natürlich nicht besonders "schön". (Übrigens könnte man auf das Komma verzichten, da man den zusammengesetzten String eh nicht lesen kann.)
Da es in meiner Anwendung "nur" um den Fall geht, in einem String "irgendwo" eingebettete Zahlen (und Umlaute) natürlich zu sortieren, tendiere ich inzwischen doch wieder zu einer eigenen Funktion, die an passenden Stellen führende Nullen (bzw. 2-Zeichen-Äquivalente) einfügt, so schön LCMapStringEx auch ist. Nach grober Sichtung meiner knapp 10.000 Datensätze habe ich gesehen, dass numerische Bestandteile maximal 3 Vorkommastellen haben, so dass ich mit einer Auffüllung auf 4 führende Nullen wohl einigermaßen auf der sicheren Seite wäre. Notfalls müsste ich eine solche Wandlungs-Funktion auf mehr Stellen ändern und dann einmalig alle Datensätze bearbeiten.
Aber vielleicht habt ihr ja noch bessere Ideen.
Danke jedenfalls für diese lehrreichen Beispiele zur Nutzung von LCMapStringEx.
crystal
PS: Laut MSDN-Doku ist die Nutzung des Flags LCMAP_SORTKEY nicht mit anderen Main-Flags kombinierbar, vielleicht kam es deshalb zu Abstürzen.
Zitat von: crystal am August 17, 2016, 21:29:27LCMapStringEx liefert ja scheinbar ein Byte-Array, bei dem je 2 Bytes einem Unicode-Zeichen entsprechen, oder? Wenn das so ist, könnte man diese je 2 Bytes nicht auch in ASCII-Äquivalente zurückwandeln, um "sortfeld" lesbar zu bekommen?
Die Funktion
GetMappedStringArrayAsUTF16$ macht das ja - nur wird daraus dann noch kein lesbarer Text. Für bestimmte Unicode-Zeichen werden auch schon einmal 10 Bytes verbraten. Ich denke, dass außer einem eindeutigen Sortierschlüssel, den Bytefolgen nicht viek Sinn hinterlegt ist. Ansonsen würde ich auch eine Umkehr-API Funktion erwarten.
Ein nächster Schritt sollte ein Benchmarking umfassen, um die verschiedenen Versionen auf Praxistauglichkeit testen zu können.
Zitat von: Lachtaube am August 17, 2016, 20:59:18...und eine, die zur Sortierung in Abfragen geeignet ist (Verzicht auf Index-Nutzung). Diese wird deutlich mehr Kapazität als die Version der aneinandergeketteten "000",-Bytefolgen aufweisen und auch schneller sein, weil hier die Zeichen-Verkettung entfällt.
Du möchtest deine Funktion
GetMappedStringArrayAsUTF16 für die Sortierung in Abfragen verwenden?
Du hast vermutlich überlesen, was ich dazu in #17 geschrieben hatte:
Zitat von: PhilS am August 17, 2016, 15:42:31Wenn man das Byte-Array jetzt als String behandelt, verwendet Access für die Sortierung die jeweils für die DB eingestellt Sortierreihenfolge. Dabei kann es aber IMO passieren, dass die Sortierung am Ende nicht der binären Sortierung der Bytes entspricht und damit nicht mehr korrekt ist.
Das "IMO" oben streiche ich. - Ich hab ein konkretes Beispiel.
Gibt mal in einer Tabelle mit Text-Spalte die folgenden Zeichen ein (je eines pro Datensatz):
! . ? a ü x zDann verwende diese Spalte als Input für
GetMappedStringArrayAsUTF16 und sortiere über den Rückgabewert der Funktion.
Mir ist schon klar, dass meine numerischen Byte-Werte-Folgen von der Performance suboptimal sind, aber eine Alternative sehe ich noch nicht.
Ja, das stimmt.Als Alternative könnte man eine Hex-Zeichenkette erstellen.'// Zur Verwendung als Sortierschlüssel geeignet
'// Ausgabe als Hex-String
Public Function GetMappedStringArrayAsHex(ByVal Source)
Dim i&, r&, dest() As Byte
If Len(Nz(Source)) = 0 Then Exit Function
'// erforderliche Länge ermitteln
r = LCMapStringEx(StrPtr(LOCALE_NAME_USER_DEFAULT), _
LCMAP_SORTKEY Or SORT_DIGITSASNUMBERS, _
StrPtr(Source), Len(Source), 0, 0)
If r > 0 Then
'// Ziel-Array dimensionieren
ReDim dest(r - 1)
'// Ziel-Array füllen
r = LCMapStringEx(StrPtr(LOCALE_NAME_USER_DEFAULT), _
LCMAP_SORTKEY Or SORT_DIGITSASNUMBERS, _
StrPtr(Source), Len(Source), _
VarPtr(dest(0)), r)
'// Ergebnispuffer mit Leerzeichen initialisieren
GetMappedStringArrayAsHex = Space$(2 * (r - 1))
'// 0-Byte am Ende ignorieren
For i = 0 To UBound(dest) - 1
Mid$(GetMappedStringArrayAsHex, 2 * i + 1, 2) _
= Right$("00" & Hex$(dest(i)), 2)
Next
Else
GetMappedStringArrayAsHex = CVErr(vbObjectError + &H2001)
End If
End FunctionBei einem Benchmarking mit einer lokalen Tabelle über 50000 Datensätze und Textlängen zwischen 30 und 50 Zeichen braucht diese Funktion knappe 10 Sekunden zur Sortierung - hingegen erledigt ein indiziertes Binärfeld in ca. 0,1 Sekunden diese Aufgabe, und ein Update aller Sortierwerte benötigt ca. 4,8 Sekunden.
Hallo nochmal, Lachtaube.
Ist ja schon eine Weile her, aber dein Code ist eben "zeitlos" toll. Es gibt nur einen kleinen Fehler, den ich hier korrigiert habe (siehe Kommentar "xxxxx" ganz am Ende).
Ich denke, dieser Thread sollte in ein neues Unterforum aufgenommen werden: "Excellente Lösungen".
Option Explicit
'int LCMapStringEx(
' _In_opt_ LPCWSTR lpLocaleName,
' _In_ DWORD dwMapFlags,
' _In_ LPCWSTR lpSrcStr,
' _In_ int cchSrc,
' _Out_opt_ LPWSTR lpDestStr,
' _In_ int cchDest,
' _In_opt_ LPNLSVERSIONINFO lpVersionInformation,
' _In_opt_ LPVOID lpReserved,
' _In_opt_ LPARAM sortHandle
');
Private Const LOCALE_NAME_USER_DEFAULT$ = vbNullString
Private Const LCMAP_SORTKEY& = &H400 ' // WC sort key (normalize)
Private Const SORT_DIGITSASNUMBERS& = &H8 ' // use digits as numbers sort method
#If VBA7 Then
Private Declare PtrSafe Function LCMapStringEx Lib "kernel32" ( _
ByVal lpLocaleName As LongPtr _
, ByVal dwMapFlags As Long _
, ByVal lpSrcStr As LongPtr _
, ByVal cchSrc As Long _
, ByVal lpDestStr As LongPtr _
, ByVal cchDest As Long _
, Optional ByVal lpVersionInformation As LongPtr _
, Optional ByVal lpReserved As LongPtr _
, Optional ByVal lParam As Long _
) As Long
#Else
Private Declare Function LCMapStringEx Lib "kernel32" ( _
ByVal lpLocaleName As Long _
, ByVal dwMapFlags As Long _
, ByVal lpSrcStr As Long _
, ByVal cchSrc As Long _
, ByVal lpDestStr As Long _
, ByVal cchDest As Long _
, Optional ByVal lpVersionInformation As Long _
, Optional ByVal lpReserved As Long _
, Optional ByVal lParam As Long _
) As Long
#End If
'// Nur zur Verwendung in Recordsets geeignet
Public Function GetMappedStringAsByteArray(ByVal Source$) As Byte()
Dim r&, dest() As Byte
r = LCMapStringEx(StrPtr(LOCALE_NAME_USER_DEFAULT), _
LCMAP_SORTKEY Or SORT_DIGITSASNUMBERS, _
StrPtr(Source), Len(Source), 0, 0)
If r Then
ReDim dest(r - 1)
r = LCMapStringEx(StrPtr(LOCALE_NAME_USER_DEFAULT), _
LCMAP_SORTKEY Or SORT_DIGITSASNUMBERS, _
StrPtr(Source), Len(Source), _
VarPtr(dest(0)), _
UBound(dest) + 1)
'// mehr als 510 Bytes passen nicht in ein Binärfeld
If r > 510 Then ReDim Preserve dest(509)
GetMappedStringAsByteArray = dest
End If
End Function
'// Zur Verwendung in Abfragen und Recordsets - nicht zur Sortierung in Abfragen geeignet
Public Function GetMappedStringArrayAsAnsiString$(ByVal Source$)
Dim r&, dest() As Byte
r = LCMapStringEx(StrPtr(LOCALE_NAME_USER_DEFAULT), _
LCMAP_SORTKEY Or SORT_DIGITSASNUMBERS, _
StrPtr(Source), Len(Source), 0, 0)
If r Then
ReDim dest(r - 1)
r = LCMapStringEx(StrPtr(LOCALE_NAME_USER_DEFAULT), _
LCMAP_SORTKEY Or SORT_DIGITSASNUMBERS, _
StrPtr(Source), Len(Source), _
VarPtr(dest(0)), _
UBound(dest) + 1)
'// mehr als 510 Bytes passen nicht in ein Binärfeld
If r > 510 Then ReDim Preserve dest(509)
GetMappedStringArrayAsAnsiString = dest
End If
End Function
'// Zur Verwendung als Sortierschlüssel in Abfragen und Recordsets geeignet
'// ob Access dabei mehr als 255 Zeichen berücksichtigt, müsste man prüfen
'// - ich vermute eher nicht
Public Function GetMappedStringArrayAsUTF16$(ByVal Source$)
Dim r&, dest() As Byte
r = LCMapStringEx(StrPtr(LOCALE_NAME_USER_DEFAULT), _
LCMAP_SORTKEY Or SORT_DIGITSASNUMBERS, _
StrPtr(Source), Len(Source), 0, 0)
If r Then
ReDim dest(r - 1)
r = LCMapStringEx(StrPtr(LOCALE_NAME_USER_DEFAULT), _
LCMAP_SORTKEY Or SORT_DIGITSASNUMBERS, _
StrPtr(Source), Len(Source), _
VarPtr(dest(0)), _
UBound(dest) + 1)
'xxxxx
' GetMappedStringArrayAsAnsiString = StrConv(dest, vbUnicode)
'xxxxx
GetMappedStringArrayAsUTF16 = StrConv(dest, vbUnicode)
End If
End Function
Ich habe den Code in einem größeren Access-Projekt eingesetzt und das Ergebnis überzeugt total!!!
Vielen Dank also für die grandiose Lösung! :)
;D
Ich habe die hier erarbeitete Lösung inzwischen auch im Artikel Natürliche Zahlensortierung in Access mit VBA (http://codekabinett.com/rdumps.php?Lang=1&targetDoc=logische-zahlensortierung-access-vba) auf meiner Webseite zusammengefasst.
Hallo,
clever gelöst! Ihr habt auch an alles gedacht. Auf das Gebietsschema wäre ich nicht gekommen.
Gruß Andreas
Hallo daolix, MzKlMa, Lachtaube und PhilS,
Natürlich gebührt der "Ruhm für diese tolle Lösung" euch allen.
Speziellen Dank an PhilS für den Artikel auf deiner Website: er ist auch didaktisch hervorragend!
Solche Threads und Lösungen sind wirklich fantastisch für ein aktives Access- und VBA-Forum. Ich bin mir sicher, dass die Lösung bald weiter bekannt wird und weltweite Beachtung findet.
Bravo!
Hallo,
soeben habe ich den Thread-Titel auf "Natürliche Sortierung" geändert, damit die tolle Lösung vielleicht noch einfacher gefunden werden kann.
Bitte, liebe Mods, erstellt ein neues Unterfoum "Excellente Lösungen" oder ähnlich, um solche Threads zusammenfassen zu können.
Hallo,
danke für den Vorschlag, wir werden darüber nachdenken.
Andreas
Noch eine kleine Verbesserung der Performance (Speichern des Sortwerts in der Tabelle selbst).
Die Idee ist lediglich, alle Hex-Strings der Zahlen 0 bis 255 >>>einmal<<< zu erzeugen und dann nur noch zu referenzieren.
Die alte Version braucht für 10.000 Datensätze ca. 6 Sekunden, die neue unter 1 Sekunde - ohne Garantie. (Änderungen siehe Kommentar "'crystal".)
Option Compare Database
Option Explicit
Private Const LOCALE_NAME_USER_DEFAULT$ = vbNullString
Private Const LCMAP_SORTKEY& = &H400 ' // WC sort key (normalize)
Private Const SORT_DIGITSASNUMBERS& = &H8 ' // use digits as numbers sort method
#If VBA7 Then
Private Declare PtrSafe Function LCMapStringEx Lib "kernel32" ( _
ByVal lpLocaleName As LongPtr _
, ByVal dwMapFlags As Long _
, ByVal lpSrcStr As LongPtr _
, ByVal cchSrc As Long _
, ByVal lpDestStr As LongPtr _
, ByVal cchDest As Long _
, Optional ByVal lpVersionInformation As LongPtr _
, Optional ByVal lpReserved As LongPtr _
, Optional ByVal lParam As Long _
) As Long
#Else
Private Declare Function LCMapStringEx Lib "kernel32" ( _
ByVal lpLocaleName As Long _
, ByVal dwMapFlags As Long _
, ByVal lpSrcStr As Long _
, ByVal cchSrc As Long _
, ByVal lpDestStr As Long _
, ByVal cchDest As Long _
, Optional ByVal lpVersionInformation As Long _
, Optional ByVal lpReserved As Long _
, Optional ByVal lParam As Long _
) As Long
#End If
Public strH As String 'crystal
Public sHex 'crystal
Public blnInitialized As Boolean 'crystal
'
'// Zur Verwendung als Sortierschlüssel geeignet
'// Ausgabe als Hex-String
Public Function GetMappedStringArrayAsHex(ByVal Source)
Dim i&, r&, dest() As Byte
If Len(Nz(Source)) = 0 Then Exit Function
'// erforderliche Länge ermitteln
r = LCMapStringEx(StrPtr(LOCALE_NAME_USER_DEFAULT), _
LCMAP_SORTKEY Or SORT_DIGITSASNUMBERS, _
StrPtr(Source), Len(Source), 0, 0)
If r > 0 Then
'// Ziel-Array dimensionieren
ReDim dest(r - 1)
'// Ziel-Array füllen
r = LCMapStringEx(StrPtr(LOCALE_NAME_USER_DEFAULT), _
LCMAP_SORTKEY Or SORT_DIGITSASNUMBERS, _
StrPtr(Source), Len(Source), _
VarPtr(dest(0)), r)
'// Ergebnispuffer mit Leerzeichen initialisieren
GetMappedStringArrayAsHex = Space$(2 * (r - 1))
'// 0-Byte am Ende ignorieren
For i = 0 To UBound(dest) - 1
Mid$(GetMappedStringArrayAsHex, 2 * i + 1, 2) _
= GetHex(dest(i)) 'crystal
' = Right$("00" & Hex$(dest(i)), 2) 'crystal
Next
Else
GetMappedStringArrayAsHex = CVErr(vbObjectError + &H2001)
End If
End Function
Public Function GetHex(ByVal intValue) As String 'crystal
If Not blnInitialized Then
strH = "00,01,02,03,04,05,06,07,08,09,0A,0B,0C,0D,0E,0F," & _
"10,11,12,13,14,15,16,17,18,19,1A,1B,1C,1D,1E,1F," & _
"20,21,22,23,24,25,26,27,28,29,2A,2B,2C,2D,2E,2F," & _
"30,31,32,33,34,35,36,37,38,39,3A,3B,3C,3D,3E,3F," & _
"40,41,42,43,44,45,46,47,48,49,4A,4B,4C,4D,4E,4F," & _
"50,51,52,53,54,55,56,57,58,59,5A,5B,5C,5D,5E,5F," & _
"60,61,62,63,64,65,66,67,68,69,6A,6B,6C,6D,6E,6F," & _
"70,71,72,73,74,75,76,77,78,79,7A,7B,7C,7D,7E,7F," & _
"80,81,82,83,84,85,86,87,88,89,8A,8B,8C,8D,8E,8F," & _
"90,91,92,93,94,95,96,97,98,99,9A,9B,9C,9D,9E,9F," & _
"A0,A1,A2,A3,A4,A5,A6,A7,A8,A9,AA,AB,AC,AD,AE,AF," & _
"B0,B1,B2,B3,B4,B5,B6,B7,B8,B9,BA,BB,BC,BD,BE,BF," & _
"C0,C1,C2,C3,C4,C5,C6,C7,C8,C9,CA,CB,CC,CD,CE,CF," & _
"D0,D1,D2,D3,D4,D5,D6,D7,D8,D9,DA,DB,DC,DD,DE,DF," & _
"E0,E1,E2,E3,E4,E5,E6,E7,E8,E9,EA,EB,EC,ED,EE,EF," & _
"F0,F1,F2,F3,F4,F5,F6,F7,F8,F9,FA,FB,FC,FD,FE,FF,"
sHex = Split(strH, ",")
blnInitialized = True
End If
GetHex = sHex(intValue)
End Function
Das ganze mit dieser Abfrage:
UPDATE Produkte SET Produkte.SortName = GetMappedStringArrayAsHex(Produkte.Name);
Vielleicht könnte man die Performance noch weiter verbessern, wenn man das Ziel-Array "dest" nicht bei jedem Aufruf neu bildet, sondern nur einmal public definiert und z. B. auf eine Länge von 1024 Zeichen initialisiert. Dann könnte man den zweifachen Aufruf von LCMapStringEx auf einen reduzieren und müßte nur noch auf r<1024 prüfen.
Nur Ideen....sicher verbesserungsfähig....
Gruß,
crystal
Hallo
ZitatDann könnte man den zweifachen Aufruf von LCMapStringEx auf einen reduzieren
Könnte man machen, aber ich glaube nicht das das zu einer signifikanten Verbesserung führt. Der Performancefresser ist hier die Hexbildung.
Und hier könnte man ggf statt mit der Mid-Funktion [Mid$(GetMappedStringArrayAsHex ...] mit einem Pointer und CopyMemory arbeiten. Aber sehr viel mehr wird man mit VB(A) nicht mehr rauskizueln können.
Zitat von: crystal am Oktober 01, 2016, 20:24:47
Noch eine kleine Verbesserung der Performance (Speichern des Sortwerts in der Tabelle selbst).
Die Idee ist lediglich, alle Hex-Strings der Zahlen 0 bis 255 >>>einmal<<< zu erzeugen und dann nur noch zu referenzieren.
Wenn du den SortKey in einer Tabelle speichern willst, solltest du direkt das Byte-Array als Binär-Datentyp (http://codekabinett.com/rdumps.php?Lang=1&targetDoc=binaerdaten-access-datenbank-sortieren-indizieren) speichern. Dieser hat im Vergleich eine wesentlich größere Speicherdichte, da es nur jeweils ein Byte für jedes Byte im Byte-Array benötigt. Der Hex-String hat seine Daseinsberechtigung nur als Rückgabewert einer Funktion, die ad-hoc in einer Abfrage aufgerufen wird, weil hier kein anderer, adäquater Datentyp möglich ist.
Wenn du das Byte-Array in einen Hex-String konvertierst und dann erst speicherst, verlierst du doppelt Speicherplatz. Erstmal, weil bei der Umwandlung in den Hex-String ein Byte zu zwei Zeichen wird und dann nochmal weil beim Speichern pro Zeichen zwei Bytes (Unicode) in der Tabelle belegt werden.
- Binär in Tabelle (max. 510 Bytes) = Max 510 Bytes in Byte-Array (Speicherbedarf 1:1)
- Text in Tabelle (max. 255 Zeichen) = Max 127 Bytes im Byte-Array (Speicherbedarf 4:1)
Weiterhin ist der Binär-Datentyp in der Tabelle sicherlich auch deshalb noch schneller, weil er auch Binär sortiert werden kann und keine spezielle Sortierreihenfolge dafür angewendet werden muss.
Die Idee einfach ein vordefiniertes Byte-Array der maximal verwertbaren Länge zu verwenden und ohne den ersten Aufruf von LCStringMapEx an die Funktion zu übergeben halte ich für eine Idee, die durchaus die Performance noch etwas verbessern kann.
Abhängig von den oben genannten Limits für die weitere Verwendung des ByteArray kann man einfach die maximale Länge an Bytes übergeben und muss den Rest ohnehin ignorieren. (Ich habe jetzt allerdings nicht geprüft, ob LCStringMapEx die ersten Bytes überhaupt in den Buffer schreibt, wenn vorher klar ist, dass die Gesamtlänge nicht ausreicht.
Evtl. macht es dann mehr Sinn, einfach einen noch größeren Buffer an die Funktion zu übergeben und dann den (zu großen) Rest des Arrays einfach zu verwerfen, weil man ihn ja eh nicht für die Sortierung benutzen kann. – Dies verursacht dann natürlich potenzielle Ungenauigkeiten bei der Sortierung von langen Texten.
Hallo PhilS,
danke für deine Hinweise und die ausführlichen Begründungen!
Wieder ein schönes Beispiel für exakte, verständliche und vollständige Hilfe ohne erhobenen Zeigefinger (und ohne Ironie oder Geringschätzung des Fragenden).
So müssen Beiträge in einem Forum geschrieben werden: sachlich, fachlich fundiert und konstruktiv.
Bravo! 5 von 5 Sternen für deinen Beitrag und dessen didaktische Qualität!
Gruß, crystal
Hallo nochmal PhilS,
ich habe jetzt versucht, mit meiner Aktualisierungs-Abfrage ein biäres Feld zu erzeugen.
UPDATE Produkte SET Produkte.SortName = GetMappedStringArrayAsByteArray(Produkte.Name);
Das funktioniert aber leider nicht.
Erst wenn ich im Code
Public Function GetMappedStringAsByteArray(ByVal Source$) As Byte()
auf
Public Function GetMappedStringAsByteArray(ByVal Source$)
ändere, klappt es, sonst kommt nur der Fehler
Unbekannte Funktion "GetMappedStringAsByteArray".
Komisch, aber wahr...
Zitat von: crystal am Oktober 06, 2016, 17:37:01ich habe jetzt versucht, mit meiner Aktualisierungs-Abfrage ein biäres Feld zu erzeugen.
UPDATE Produkte SET Produkte.SortName = GetMappedStringArrayAsByteArray(Produkte.Name);
Das funktioniert aber leider nicht.
Erst wenn ich im Code
Public Function GetMappedStringAsByteArray(ByVal Source$) As Byte()
auf
Public Function GetMappedStringAsByteArray(ByVal Source$)
ändere, klappt es, sonst kommt nur der Fehler
Unbekannte Funktion "GetMappedStringAsByteArray".
Komisch, aber wahr...
Diesen Post hatte ich mir vor Jahren mal auf Wiedervorlage gelegt, und zufällig jetzt gerade wieder im Blick.
Mit dem Abstand ist das Problem hier relativ klar.
Funktionen können nur direkt in Abfragen verwendet werden, wenn sie primitive Datentypen (z.B. String, Zahl, Datum) zurückgeben, aber nicht mit komplexen Typen, wie einem Array.
Ein Byte-Array ist aber ja fast ein "binary". - Daher vermute ich, wenn man den Rückgabetyp der Funktion nicht explizit definiert, wird das Byte-Array implizit in einen Binary-Typ in der DB konvertiert.