VFPOLEDB and Collatesequence  
Author Message
Hague2





PostPosted: Sat Feb 14 11:47:01 CST 2004 Top

Exchange Servers >> VFPOLEDB and Collatesequence

To get correct sort order I use "NORDAN" as collatesequence.

INDEX ON name TAG name COLLATE "nordan"

Now I have discovered that VFPOLEDB ignores the index when this
collatesequence is used.
Is this by design, am I doing something wrong here, or shall I report it as
a bug?

Rolf

Exchange Server38  
 
 
Anders





PostPosted: Sat Feb 14 11:47:01 CST 2004 Top

Exchange Servers >> VFPOLEDB and Collatesequence Rolf
From where are you using this VFPOLEDB?
I made a table including some names starting with Ö, Å and Ä, in that order.
I added an index
INDEX ON name tag name COLLATE 'nordan'
I created a CursorAdaptor class named nordanoledb, specifiying ADO as source
type and using this connectionstring
Provider=VFPOLEDB.1;Data Source=D:\VFP8TEST;Password="";Collating
Sequence=NORDAN;
and a query
SELECT * FROM Names ORDER BY name
I initialized this class and open a cursor

o=NewObject('nordanoledb', 'bib1.vcx')
o.CursorFill
Browse

It appeard sorted correctly
Name
Anna
Erik
Ärild
Åke
Örjan
Öyvind

-Anders




> To get correct sort order I use "NORDAN" as collatesequence.
>
> INDEX ON name TAG name COLLATE "nordan"
>
> Now I have discovered that VFPOLEDB ignores the index when this
> collatesequence is used.
> Is this by design, am I doing something wrong here, or shall I report it
as
> a bug?
>
> Rolf
>
>
>
>

 
 
Rolf





PostPosted: Sat Feb 14 17:56:29 CST 2004 Top

Exchange Servers >> VFPOLEDB and Collatesequence I use ADODB.RecordSet, but not cursoradaptor
What I have found out, is that the time to retrieve the records is very long
when I use collatesequence when the index is created.
I have done my testing with a table with 800000 records.

When I index like this:
INDEX ON name TAG name COLLATE "nordan"
Select * from <table> where name like 'Rolf%'
takes several minutes.
If I remove the index, the time used is the same.
That's why I think rushmore is disabled because of the collatesequence.

When I index like this:
INDEX ON name TAG name
Select * from <table> where name like 'Rolf%'
is quick as usual

Directly from VFP there is no difference.

I can post my code if you like.
The setup is n-tier, so it will be a lot of code. Maybe I can remove the
n-tier part for testing.

Rolf





> Rolf
> From where are you using this VFPOLEDB?
> I made a table including some names starting with Ö, Å and Ä, in that
order.
> I added an index
> INDEX ON name tag name COLLATE 'nordan'
> I created a CursorAdaptor class named nordanoledb, specifiying ADO as
source
> type and using this connectionstring
> Provider=VFPOLEDB.1;Data Source=D:\VFP8TEST;Password="";Collating
> Sequence=NORDAN;
> and a query
> SELECT * FROM Names ORDER BY name
> I initialized this class and open a cursor
>
> o=NewObject('nordanoledb', 'bib1.vcx')
> o.CursorFill
> Browse
>
> It appeard sorted correctly
> Name
> Anna
> Erik
> Ärild
> Åke
> Örjan
> Öyvind
>
> -Anders
>
>


> > To get correct sort order I use "NORDAN" as collatesequence.
> >
> > INDEX ON name TAG name COLLATE "nordan"
> >
> > Now I have discovered that VFPOLEDB ignores the index when this
> > collatesequence is used.
> > Is this by design, am I doing something wrong here, or shall I report it
> as
> > a bug?
> >
> > Rolf
> >
> >
> >
> >
>


 
 
Anders





PostPosted: Sun Feb 15 06:13:05 CST 2004 Top

Exchange Servers >> VFPOLEDB and Collatesequence Hi Rolf,

If you want Full Rushmore for a Nordan index, there are three conditions
you need to observe:
SET COLLATE TO 'Nordan'
SET ANSI OFF
SET DELETED OFF
SELECT * FROM Table WHERE name = 'Rolf'
'Partial' optimization, according to SYS(3054,1) will be just as fast most
likely and you will get Partial optimazation with
SET DELETED ON
or
SELECT * FROM Table WHERE name LIKE 'Rolf%'

With SET ANSI ON you will get full optimization but you can't search for
partial strings with =, only with LIKE.'string%'
SET DELETED ON will cause SYS(3054,1) to report Partial optimization, bu
that may most likely still be faster than dragging along an index on
DELETED().

-Anders



> I use ADODB.RecordSet, but not cursoradaptor
> What I have found out, is that the time to retrieve the records is very
long
> when I use collatesequence when the index is created.
> I have done my testing with a table with 800000 records.
>
> When I index like this:
> INDEX ON name TAG name COLLATE "nordan"
> Select * from <table> where name like 'Rolf%'
> takes several minutes.
> If I remove the index, the time used is the same.
> That's why I think rushmore is disabled because of the collatesequence.
>
> When I index like this:
> INDEX ON name TAG name
> Select * from <table> where name like 'Rolf%'
> is quick as usual
>
> Directly from VFP there is no difference.
>
> I can post my code if you like.
> The setup is n-tier, so it will be a lot of code. Maybe I can remove the
> n-tier part for testing.
>
> Rolf
>
>
>


> > Rolf
> > From where are you using this VFPOLEDB?
> > I made a table including some names starting with Ö, Å and Ä, in that
> order.
> > I added an index
> > INDEX ON name tag name COLLATE 'nordan'
> > I created a CursorAdaptor class named nordanoledb, specifiying ADO as
> source
> > type and using this connectionstring
> > Provider=VFPOLEDB.1;Data Source=D:\VFP8TEST;Password="";Collating
> > Sequence=NORDAN;
> > and a query
> > SELECT * FROM Names ORDER BY name
> > I initialized this class and open a cursor
> >
> > o=NewObject('nordanoledb', 'bib1.vcx')
> > o.CursorFill
> > Browse
> >
> > It appeard sorted correctly
> > Name
> > Anna
> > Erik
> > Ärild
> > Åke
> > Örjan
> > Öyvind
> >
> > -Anders
> >
> >


> > > To get correct sort order I use "NORDAN" as collatesequence.
> > >
> > > INDEX ON name TAG name COLLATE "nordan"
> > >
> > > Now I have discovered that VFPOLEDB ignores the index when this
> > > collatesequence is used.
> > > Is this by design, am I doing something wrong here, or shall I report
it
> > as
> > > a bug?
> > >
> > > Rolf
> > >
> > >
> > >
> > >
> >
>
>

 
 
Rolf





PostPosted: Sun Feb 15 10:10:10 CST 2004 Top

Exchange Servers >> VFPOLEDB and Collatesequence I set 'Collating Sequence=NORDAN' in the connection string
I guess this is the same as SET COLLATE TO 'Nordan' in VFP

How to 'SET ANSI OFF' and 'SET DELETED OFF' in the connection string I
don't know.

VFPOLEDB is very slow when the index is created like this:
INDEX ON name TAG name COLLATE "nordan"
The speed is the same if there is no index.

and very quick when I create the index like this:
INDEX ON name TAG name

I both cases I do
SELECT * FROM Table WHERE name LIKE 'Rolf%'

I can't see what I'm doing wrong here.
As far as I can see VFPOLEDB ignores the index if it is created with
'COLLATE <something>'
(Well, I have only tested with NORDAN)

Rolf





> Hi Rolf,
>
> If you want Full Rushmore for a Nordan index, there are three conditions
> you need to observe:
> SET COLLATE TO 'Nordan'
> SET ANSI OFF
> SET DELETED OFF
> SELECT * FROM Table WHERE name = 'Rolf'
> 'Partial' optimization, according to SYS(3054,1) will be just as fast most
> likely and you will get Partial optimazation with
> SET DELETED ON
> or
> SELECT * FROM Table WHERE name LIKE 'Rolf%'
>
> With SET ANSI ON you will get full optimization but you can't search for
> partial strings with =, only with LIKE.'string%'
> SET DELETED ON will cause SYS(3054,1) to report Partial optimization, bu
> that may most likely still be faster than dragging along an index on
> DELETED().
>
> -Anders
>


> > I use ADODB.RecordSet, but not cursoradaptor
> > What I have found out, is that the time to retrieve the records is very
> long
> > when I use collatesequence when the index is created.
> > I have done my testing with a table with 800000 records.
> >
> > When I index like this:
> > INDEX ON name TAG name COLLATE "nordan"
> > Select * from <table> where name like 'Rolf%'
> > takes several minutes.
> > If I remove the index, the time used is the same.
> > That's why I think rushmore is disabled because of the collatesequence.
> >
> > When I index like this:
> > INDEX ON name TAG name
> > Select * from <table> where name like 'Rolf%'
> > is quick as usual
> >
> > Directly from VFP there is no difference.
> >
> > I can post my code if you like.
> > The setup is n-tier, so it will be a lot of code. Maybe I can remove the
> > n-tier part for testing.
> >
> > Rolf
> >
> >
> >


> > > Rolf
> > > From where are you using this VFPOLEDB?
> > > I made a table including some names starting with Ö, Å and Ä, in that
> > order.
> > > I added an index
> > > INDEX ON name tag name COLLATE 'nordan'
> > > I created a CursorAdaptor class named nordanoledb, specifiying ADO as
> > source
> > > type and using this connectionstring
> > > Provider=VFPOLEDB.1;Data Source=D:\VFP8TEST;Password="";Collating
> > > Sequence=NORDAN;
> > > and a query
> > > SELECT * FROM Names ORDER BY name
> > > I initialized this class and open a cursor
> > >
> > > o=NewObject('nordanoledb', 'bib1.vcx')
> > > o.CursorFill
> > > Browse
> > >
> > > It appeard sorted correctly
> > > Name
> > > Anna
> > > Erik
> > > Ärild
> > > Åke
> > > Örjan
> > > Öyvind
> > >
> > > -Anders
> > >
> > >


> > > > To get correct sort order I use "NORDAN" as collatesequence.
> > > >
> > > > INDEX ON name TAG name COLLATE "nordan"
> > > >
> > > > Now I have discovered that VFPOLEDB ignores the index when this
> > > > collatesequence is used.
> > > > Is this by design, am I doing something wrong here, or shall I
report
> it
> > > as
> > > > a bug?
> > > >
> > > > Rolf
> > > >
> > > >
> > > >
> > > >
> > >
> >
> >
>


 
 
Anders





PostPosted: Sun Feb 15 14:23:07 CST 2004 Top

Exchange Servers >> VFPOLEDB and Collatesequence Hi Rolf
> I set 'Collating Sequence=NORDAN' in the connection string
> I guess this is the same as SET COLLATE TO 'Nordan' in VFP
Don't guess. Test! <g>.
SET CLASSLIB TO Bib1.vcx
oCA = CreateObject('nordanoledb' )
* my connection string has Collate = 'Nordan'
oCA.DataSourceType='ADO'
oCA.SelectCmd ='SELECT * FROM Jobs!Bookings WHERE idno='SMN/EL/703'
* That's over 1 million records with a nordan index on idno
oCA.DataSource.ActiveConnection.Execute([SET COLLATE TO 'Nordan'])
oCA.CursorFill && 139 records in 0.12 seconds
USE
oCA.DataSource.ActiveConnection.Execute([SET COLLATE TO 'Machine'])
oCA.CursorFill && 139 records in 6.12 seconds

It seems that SET COLLATE TO 'Nordan' makes the query run 60 times faster.
You would see the same effect in native VFP data access.
If you're in VFP you can use Machine indexes in the querying but put a
Nordan index on the query result.
-Anders



> I set 'Collating Sequence=NORDAN' in the connection string
> I guess this is the same as SET COLLATE TO 'Nordan' in VFP
>
> How to 'SET ANSI OFF' and 'SET DELETED OFF' in the connection string I
> don't know.
>
> VFPOLEDB is very slow when the index is created like this:
> INDEX ON name TAG name COLLATE "nordan"
> The speed is the same if there is no index.
>
> and very quick when I create the index like this:
> INDEX ON name TAG name
>
> I both cases I do
> SELECT * FROM Table WHERE name LIKE 'Rolf%'
>
> I can't see what I'm doing wrong here.
> As far as I can see VFPOLEDB ignores the index if it is created with
> 'COLLATE <something>'
> (Well, I have only tested with NORDAN)
>
> Rolf
>
>
>


> > Hi Rolf,
> >
> > If you want Full Rushmore for a Nordan index, there are three
conditions
> > you need to observe:
> > SET COLLATE TO 'Nordan'
> > SET ANSI OFF
> > SET DELETED OFF
> > SELECT * FROM Table WHERE name = 'Rolf'
> > 'Partial' optimization, according to SYS(3054,1) will be just as fast
most
> > likely and you will get Partial optimazation with
> > SET DELETED ON
> > or
> > SELECT * FROM Table WHERE name LIKE 'Rolf%'
> >
> > With SET ANSI ON you will get full optimization but you can't search for
> > partial strings with =, only with LIKE.'string%'
> > SET DELETED ON will cause SYS(3054,1) to report Partial optimization, bu
> > that may most likely still be faster than dragging along an index on
> > DELETED().
> >
> > -Anders
> >


> > > I use ADODB.RecordSet, but not cursoradaptor
> > > What I have found out, is that the time to retrieve the records is
very
> > long
> > > when I use collatesequence when the index is created.
> > > I have done my testing with a table with 800000 records.
> > >
> > > When I index like this:
> > > INDEX ON name TAG name COLLATE "nordan"
> > > Select * from <table> where name like 'Rolf%'
> > > takes several minutes.
> > > If I remove the index, the time used is the same.
> > > That's why I think rushmore is disabled because of the
collatesequence.
> > >
> > > When I index like this:
> > > INDEX ON name TAG name
> > > Select * from <table> where name like 'Rolf%'
> > > is quick as usual
> > >
> > > Directly from VFP there is no difference.
> > >
> > > I can post my code if you like.
> > > The setup is n-tier, so it will be a lot of code. Maybe I can remove
the
> > > n-tier part for testing.
> > >
> > > Rolf
> > >
> > >
> > >


> > > > Rolf
> > > > From where are you using this VFPOLEDB?
> > > > I made a table including some names starting with Ö, Å and Ä, in
that
> > > order.
> > > > I added an index
> > > > INDEX ON name tag name COLLATE 'nordan'
> > > > I created a CursorAdaptor class named nordanoledb, specifiying ADO
as
> > > source
> > > > type and using this connectionstring
> > > > Provider=VFPOLEDB.1;Data Source=D:\VFP8TEST;Password="";Collating
> > > > Sequence=NORDAN;
> > > > and a query
> > > > SELECT * FROM Names ORDER BY name
> > > > I initialized this class and open a cursor
> > > >
> > > > o=NewObject('nordanoledb', 'bib1.vcx')
> > > > o.CursorFill
> > > > Browse
> > > >
> > > > It appeard sorted correctly
> > > > Name
> > > > Anna
> > > > Erik
> > > > Ärild
> > > > Åke
> > > > Örjan
> > > > Öyvind
> > > >
> > > > -Anders
> > > >
> > > >


> > > > > To get correct sort order I use "NORDAN" as collatesequence.
> > > > >
> > > > > INDEX ON name TAG name COLLATE "nordan"
> > > > >
> > > > > Now I have discovered that VFPOLEDB ignores the index when this
> > > > > collatesequence is used.
> > > > > Is this by design, am I doing something wrong here, or shall I
> report
> > it
> > > > as
> > > > > a bug?
> > > > >
> > > > > Rolf
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > >
> > >
> >
>
>

 
 
Rolf





PostPosted: Sun Feb 15 17:19:19 CST 2004 Top

Exchange Servers >> VFPOLEDB and Collatesequence Execute( 'SET COLLATE TO "NORDAN"' ) is the solution.

I was not aware of this being possible.
In my stupidity I thought that 'Collating Sequence=NORDAN' in the
connection string had the same effect.
It seems that 'Collating Sequence=NORDAN' in the connection string har no
effect on the speed. I will test this further.

Now I have learned to execute 'SET COLLATE ...' via VFPOLEDB.
Is there a way for VFPOLEDB to find out what collating sequence was used for
creating an index?
The VFP buys should make this automatic. Then we would have top speed all
the time.

Thank you for your time.
Rolf





> Hi Rolf
> > I set 'Collating Sequence=NORDAN' in the connection string
> > I guess this is the same as SET COLLATE TO 'Nordan' in VFP
> Don't guess. Test! <g>.
> SET CLASSLIB TO Bib1.vcx
> oCA = CreateObject('nordanoledb' )
> * my connection string has Collate = 'Nordan'
> oCA.DataSourceType='ADO'
> oCA.SelectCmd ='SELECT * FROM Jobs!Bookings WHERE idno='SMN/EL/703'
> * That's over 1 million records with a nordan index on idno
> oCA.DataSource.ActiveConnection.Execute([SET COLLATE TO 'Nordan'])
> oCA.CursorFill && 139 records in 0.12 seconds
> USE
> oCA.DataSource.ActiveConnection.Execute([SET COLLATE TO 'Machine'])
> oCA.CursorFill && 139 records in 6.12 seconds
>
> It seems that SET COLLATE TO 'Nordan' makes the query run 60 times faster.
> You would see the same effect in native VFP data access.
> If you're in VFP you can use Machine indexes in the querying but put a
> Nordan index on the query result.
> -Anders
>


> > I set 'Collating Sequence=NORDAN' in the connection string
> > I guess this is the same as SET COLLATE TO 'Nordan' in VFP
> >
> > How to 'SET ANSI OFF' and 'SET DELETED OFF' in the connection string I
> > don't know.
> >
> > VFPOLEDB is very slow when the index is created like this:
> > INDEX ON name TAG name COLLATE "nordan"
> > The speed is the same if there is no index.
> >
> > and very quick when I create the index like this:
> > INDEX ON name TAG name
> >
> > I both cases I do
> > SELECT * FROM Table WHERE name LIKE 'Rolf%'
> >
> > I can't see what I'm doing wrong here.
> > As far as I can see VFPOLEDB ignores the index if it is created with
> > 'COLLATE <something>'
> > (Well, I have only tested with NORDAN)
> >
> > Rolf
> >
> >
> >


> > > Hi Rolf,
> > >
> > > If you want Full Rushmore for a Nordan index, there are three
> conditions
> > > you need to observe:
> > > SET COLLATE TO 'Nordan'
> > > SET ANSI OFF
> > > SET DELETED OFF
> > > SELECT * FROM Table WHERE name = 'Rolf'
> > > 'Partial' optimization, according to SYS(3054,1) will be just as fast
> most
> > > likely and you will get Partial optimazation with
> > > SET DELETED ON
> > > or
> > > SELECT * FROM Table WHERE name LIKE 'Rolf%'
> > >
> > > With SET ANSI ON you will get full optimization but you can't search
for
> > > partial strings with =, only with LIKE.'string%'
> > > SET DELETED ON will cause SYS(3054,1) to report Partial optimization,
bu
> > > that may most likely still be faster than dragging along an index on
> > > DELETED().
> > >
> > > -Anders
> > >


> > > > I use ADODB.RecordSet, but not cursoradaptor
> > > > What I have found out, is that the time to retrieve the records is
> very
> > > long
> > > > when I use collatesequence when the index is created.
> > > > I have done my testing with a table with 800000 records.
> > > >
> > > > When I index like this:
> > > > INDEX ON name TAG name COLLATE "nordan"
> > > > Select * from <table> where name like 'Rolf%'
> > > > takes several minutes.
> > > > If I remove the index, the time used is the same.
> > > > That's why I think rushmore is disabled because of the
> collatesequence.
> > > >
> > > > When I index like this:
> > > > INDEX ON name TAG name
> > > > Select * from <table> where name like 'Rolf%'
> > > > is quick as usual
> > > >
> > > > Directly from VFP there is no difference.
> > > >
> > > > I can post my code if you like.
> > > > The setup is n-tier, so it will be a lot of code. Maybe I can remove
> the
> > > > n-tier part for testing.
> > > >
> > > > Rolf
> > > >
> > > >
> > > >


> > > > > Rolf
> > > > > From where are you using this VFPOLEDB?
> > > > > I made a table including some names starting with Ö, Å and Ä, in
> that
> > > > order.
> > > > > I added an index
> > > > > INDEX ON name tag name COLLATE 'nordan'
> > > > > I created a CursorAdaptor class named nordanoledb, specifiying ADO
> as
> > > > source
> > > > > type and using this connectionstring
> > > > > Provider=VFPOLEDB.1;Data Source=D:\VFP8TEST;Password="";Collating
> > > > > Sequence=NORDAN;
> > > > > and a query
> > > > > SELECT * FROM Names ORDER BY name
> > > > > I initialized this class and open a cursor
> > > > >
> > > > > o=NewObject('nordanoledb', 'bib1.vcx')
> > > > > o.CursorFill
> > > > > Browse
> > > > >
> > > > > It appeard sorted correctly
> > > > > Name
> > > > > Anna
> > > > > Erik
> > > > > Ärild
> > > > > Åke
> > > > > Örjan
> > > > > Öyvind
> > > > >
> > > > > -Anders
> > > > >
> > > > >


> > > > > > To get correct sort order I use "NORDAN" as collatesequence.
> > > > > >
> > > > > > INDEX ON name TAG name COLLATE "nordan"
> > > > > >
> > > > > > Now I have discovered that VFPOLEDB ignores the index when this
> > > > > > collatesequence is used.
> > > > > > Is this by design, am I doing something wrong here, or shall I
> > report
> > > it
> > > > > as
> > > > > > a bug?
> > > > > >
> > > > > > Rolf
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > >
> > > >
> > >
> >
> >
>


 
 
Anders





PostPosted: Mon Feb 16 07:41:45 CST 2004 Top

Exchange Servers >> VFPOLEDB and Collatesequence Hi Rolf
One can set the ActiveConnection of ADOX.Catalog , ADOX.Tables and
Adox.Index to a ADODB.Connection
The Tables.Item(n).Indexes object lets you enumerate the names of the
indexes for the table but not much more., Yes, check if its Unique or
Primary Key index. ADO isd not the way here as far as I can see.
-Anders
MS VFP MVP




> Execute( 'SET COLLATE TO "NORDAN"' ) is the solution.
>
> I was not aware of this being possible.
> In my stupidity I thought that 'Collating Sequence=NORDAN' in the
> connection string had the same effect.
> It seems that 'Collating Sequence=NORDAN' in the connection string har no
> effect on the speed. I will test this further.
>
> Now I have learned to execute 'SET COLLATE ...' via VFPOLEDB.
> Is there a way for VFPOLEDB to find out what collating sequence was used
for
> creating an index?
> The VFP buys should make this automatic. Then we would have top speed all
> the time.
>
> Thank you for your time.
> Rolf
>
>
>


> > Hi Rolf
> > > I set 'Collating Sequence=NORDAN' in the connection string
> > > I guess this is the same as SET COLLATE TO 'Nordan' in VFP
> > Don't guess. Test! <g>.
> > SET CLASSLIB TO Bib1.vcx
> > oCA = CreateObject('nordanoledb' )
> > * my connection string has Collate = 'Nordan'
> > oCA.DataSourceType='ADO'
> > oCA.SelectCmd ='SELECT * FROM Jobs!Bookings WHERE idno='SMN/EL/703'
> > * That's over 1 million records with a nordan index on idno
> > oCA.DataSource.ActiveConnection.Execute([SET COLLATE TO 'Nordan'])
> > oCA.CursorFill && 139 records in 0.12 seconds
> > USE
> > oCA.DataSource.ActiveConnection.Execute([SET COLLATE TO 'Machine'])
> > oCA.CursorFill && 139 records in 6.12 seconds
> >
> > It seems that SET COLLATE TO 'Nordan' makes the query run 60 times
faster.
> > You would see the same effect in native VFP data access.
> > If you're in VFP you can use Machine indexes in the querying but put a
> > Nordan index on the query result.
> > -Anders
> >


> > > I set 'Collating Sequence=NORDAN' in the connection string
> > > I guess this is the same as SET COLLATE TO 'Nordan' in VFP
> > >
> > > How to 'SET ANSI OFF' and 'SET DELETED OFF' in the connection string
I
> > > don't know.
> > >
> > > VFPOLEDB is very slow when the index is created like this:
> > > INDEX ON name TAG name COLLATE "nordan"
> > > The speed is the same if there is no index.
> > >
> > > and very quick when I create the index like this:
> > > INDEX ON name TAG name
> > >
> > > I both cases I do
> > > SELECT * FROM Table WHERE name LIKE 'Rolf%'
> > >
> > > I can't see what I'm doing wrong here.
> > > As far as I can see VFPOLEDB ignores the index if it is created with
> > > 'COLLATE <something>'
> > > (Well, I have only tested with NORDAN)
> > >
> > > Rolf
> > >
> > >
> > >


> > > > Hi Rolf,
> > > >
> > > > If you want Full Rushmore for a Nordan index, there are three
> > conditions
> > > > you need to observe:
> > > > SET COLLATE TO 'Nordan'
> > > > SET ANSI OFF
> > > > SET DELETED OFF
> > > > SELECT * FROM Table WHERE name = 'Rolf'
> > > > 'Partial' optimization, according to SYS(3054,1) will be just as
fast
> > most
> > > > likely and you will get Partial optimazation with
> > > > SET DELETED ON
> > > > or
> > > > SELECT * FROM Table WHERE name LIKE 'Rolf%'
> > > >
> > > > With SET ANSI ON you will get full optimization but you can't search
> for
> > > > partial strings with =, only with LIKE.'string%'
> > > > SET DELETED ON will cause SYS(3054,1) to report Partial
optimization,
> bu
> > > > that may most likely still be faster than dragging along an index on
> > > > DELETED().
> > > >
> > > > -Anders
> > > >


> > > > > I use ADODB.RecordSet, but not cursoradaptor
> > > > > What I have found out, is that the time to retrieve the records is
> > very
> > > > long
> > > > > when I use collatesequence when the index is created.
> > > > > I have done my testing with a table with 800000 records.
> > > > >
> > > > > When I index like this:
> > > > > INDEX ON name TAG name COLLATE "nordan"
> > > > > Select * from <table> where name like 'Rolf%'
> > > > > takes several minutes.
> > > > > If I remove the index, the time used is the same.
> > > > > That's why I think rushmore is disabled because of the
> > collatesequence.
> > > > >
> > > > > When I index like this:
> > > > > INDEX ON name TAG name
> > > > > Select * from <table> where name like 'Rolf%'
> > > > > is quick as usual
> > > > >
> > > > > Directly from VFP there is no difference.
> > > > >
> > > > > I can post my code if you like.
> > > > > The setup is n-tier, so it will be a lot of code. Maybe I can
remove
> > the
> > > > > n-tier part for testing.
> > > > >
> > > > > Rolf
> > > > >
> > > > >
> > > > >


> > > > > > Rolf
> > > > > > From where are you using this VFPOLEDB?
> > > > > > I made a table including some names starting with Ö, Å and Ä, in
> > that
> > > > > order.
> > > > > > I added an index
> > > > > > INDEX ON name tag name COLLATE 'nordan'
> > > > > > I created a CursorAdaptor class named nordanoledb, specifiying
ADO
> > as
> > > > > source
> > > > > > type and using this connectionstring
> > > > > > Provider=VFPOLEDB.1;Data
Source=D:\VFP8TEST;Password="";Collating
> > > > > > Sequence=NORDAN;
> > > > > > and a query
> > > > > > SELECT * FROM Names ORDER BY name
> > > > > > I initialized this class and open a cursor
> > > > > >
> > > > > > o=NewObject('nordanoledb', 'bib1.vcx')
> > > > > > o.CursorFill
> > > > > > Browse
> > > > > >
> > > > > > It appeard sorted correctly
> > > > > > Name
> > > > > > Anna
> > > > > > Erik
> > > > > > Ärild
> > > > > > Åke
> > > > > > Örjan
> > > > > > Öyvind
> > > > > >
> > > > > > -Anders
> > > > > >
> > > > > >


> > > > > > > To get correct sort order I use "NORDAN" as collatesequence.
> > > > > > >
> > > > > > > INDEX ON name TAG name COLLATE "nordan"
> > > > > > >
> > > > > > > Now I have discovered that VFPOLEDB ignores the index when
this
> > > > > > > collatesequence is used.
> > > > > > > Is this by design, am I doing something wrong here, or shall I
> > > report
> > > > it
> > > > > > as
> > > > > > > a bug?
> > > > > > >
> > > > > > > Rolf
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > >
> > >
> >
>
>