Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Dear Pierre,
this problem is known by MS, and is documented at MSDN. Search for Collate keyword. Mainly SQL select with joins can be slower with indexes using in joins other than machine. (And, there are many bug with select if your sequnces arenot machine)
I also use Hungarian collating sequnce only on name fields. All other indexes are machine. I had many trouble changing index collate sequnces.
I uploaded into File Section an app that can change the index collate sequence.
Donnot forget, that reindex will use the actual collate sequnce!
So the right order of commands:
1. remove indexes
2. change to collate sequence you want (general, french, hungarian, anything)
3. rebuild only the affected indexes
4. change to other collate sequence you want (machine)
5. rebuild the affected indexes
The index itself hold the info about its collate sequence.
Of course, I asked myself, what happens, if a machine doesnot support the collate sequnce that a special index tag has. As I experinced it doesnot cause any problem when you insert new elements or change other. (i.e. the order reamins right)
bb
>I have an VFP5.0a application produced with Visual FoxExpress 5.0 in which I have a database containing some 20 tables & views.
>The application also has Stonefield Database Toolkit embedded for database maintenance & repair.
>Until last application's update, everything was fine & dandy. All original tables had compound indexes collated "machine". Then I decided to change them to "general" because I need to sort some accented characters (french).
>This is when that the performance dropped BIG TIME from what as instant to WELL over 10 seconds for a "one word" search in a table containing 32K records.
>I reversed all the chnages (i.e: indexes are now back to "machine") using the Stonefield Toolbox and I still have the same performance issue.
>Reversing the indexes back to machine hasn't improved anything. BTW this is the ONLY change that was done in the application before and after.
>Anyone has a suggestion as to what I need to do or look for to get back to my original performance ?
>TIA
>
>Pierre Richard
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement