RE: Index & Seek Question

[Paul H. Tarver]

Let me explain in more detail what I'm doing. I have a Windows webserver I'm 
working with and I've built a program to import IIS log data from about 50 
websites for a specific range of dates and then doing some analysis SQL queries 
on the log detail specifically focusing on activity which takes place across 
the multiple sites/logs on the server. Ultimately, I'm searching for bad actors 
attempting to do bad things on this server. I have a look up table with URL 
requests we consider to be bad and I'm doing a look up on the Client IP 
addresses to determine whether in the world the activity is coming from. To 
that end, I look up each unique IP address in the IP2LOCATION-LITE-DB3 list and 
then update the temporary log table table with the source locations. Once we 
identify a bad actor, I have a blocking system I use to completely block 
blacklisted IP's from even getting a response from the server, so we don't 
waste a lot of time generating 403 or 404 errors. 

But it just dawned on me while writing this that one way to squeeze a little 
more speed out of the process, is to just update a separate table of the unique 
IP addresses and then join the results in my analysis queries rather than 
physically updating the working log tables.

This is really an exercise in optimizing my programming as much as possible, 
although we are already seeing some significant results from our efforts so 
far. I cut my server memory usages in half the first day I started this 
strategy and shaved 35% off the number of active processes running at any one 
time. It's been like getting a new server. But more importantly, I'm having 
loads of fun testing what I can do with this little program and how fast I can 
make it go. :)

Thanks!

Paul H. Tarver
Tarver Program Consultants, Inc.


-Original Message-
From: ProfoxTech [mailto:profoxtech-boun...@leafe.com] On Behalf Of Ted Roche
Sent: Saturday, December 16, 2023 3:42 PM
To: profoxt...@leafe.com
Subject: Re: Index & Seek Question

IPv4 addresses are 32-bit numbers expressed for human convenience as 4
numbers between 0 and 255 separated by dots, but they can really be
handled more easily as a single 32-bit integer.

So, have you added a column to your table that stores the integer
value of the IP address?

ALTER TABLE YourTable ADD COLUMN dIPADDR INT(16)

UPDATE YourTable SET dIPADDR = ipsegment[1]*256^3 +ipsegment[2]*256*2
+ ipsegment[3]*256 + ipsegment[4]

Then add an index on that column.

Convert your search value using the same algorithm.

An answer on a 50,000 row table should be returned in less than one second.


On Fri, Dec 15, 2023 at 6:08 PM Paul H. Tarver  wrote:
>
> Been quiet in here for a while, so I thought I'd toss out something I'm
> tinkering with and ask for your advice. Besides, I want to keep my name on
> the monthly top 10! J
>
>
>
> I have a large table populated with the IP2LOCATION-LITE-DB3 list. I'm
> running a process to convert an IPV4 address into a decimal number and then
> locate the first record in my table where the value I'm looking up is
> between the From_IP numeric value and the To_IP numeric value in the table
>
>
>
> Here's the code snippet I have working now:
>
> --
>
> LPARAMETERS toParams
>
> LOCAL lcBase2IP, lnElements, lnIpNumber
>
>
>
> IF USED("ip_locs")
>
>
>
>   lcCurrSel = ALIAS()
>
>
>
>   lnElements = ALINES(ipsegment, toParams.ocClientIp, 1, ".")
>
>
>
>   IF lnElements = 4
>
>
>
> lcBase2IP =
> PADL(THISFORM.utilities.DEC2BASX(VAL(ALLTRIM(ipsegment[1])), 2), 8, "0") + ;
>
>
> PADL(THISFORM.utilities.DEC2BASX(VAL(ALLTRIM(ipsegment[2])), 2), 8, "0") + ;
>
>
> PADL(THISFORM.utilities.DEC2BASX(VAL(ALLTRIM(ipsegment[3])), 2), 8, "0") + ;
>
>
> PADL(THISFORM.utilities.DEC2BASX(VAL(ALLTRIM(ipsegment[4])), 2), 8, "0")
>
>
>
> lnIpNumber = THISFORM.utilities.bin2dec(lcBase2IP)
>
>
>
> SELECT ip_locs
>
> LOCATE FOR lnIpNumber => ip_locs.ip_from AND lnIpNumber <=
> ip_locs.ip_to
>
>
>
> IF FOUND('ip_locs')
>
>
>
>   toParams.ocCountry_Code = ip_locs.Country_Code
>
>   toParams.ocCountry_Name = ip_locs.Country_Name
>
>   toParams.ocRegion_Name = ip_locs.Region_Name
>
>   toParams.ocCity_Name = ip_locs.City_Name
>
>
>
> ENDIF
>
>
>
>   ENDIF
>
>
>
>   SELECT (lcCurrSel)
>
>
>
> ENDIF
>
>
>
> RETURN
>
> ---
>
&

Re: Index & Seek Question

[Ted Roche]

IPv4 addresses are 32-bit numbers expressed for human convenience as 4
numbers between 0 and 255 separated by dots, but they can really be
handled more easily as a single 32-bit integer.

So, have you added a column to your table that stores the integer
value of the IP address?

ALTER TABLE YourTable ADD COLUMN dIPADDR INT(16)

UPDATE YourTable SET dIPADDR = ipsegment[1]*256^3 +ipsegment[2]*256*2
+ ipsegment[3]*256 + ipsegment[4]

Then add an index on that column.

Convert your search value using the same algorithm.

An answer on a 50,000 row table should be returned in less than one second.


On Fri, Dec 15, 2023 at 6:08 PM Paul H. Tarver  wrote:
>
> Been quiet in here for a while, so I thought I'd toss out something I'm
> tinkering with and ask for your advice. Besides, I want to keep my name on
> the monthly top 10! J
>
>
>
> I have a large table populated with the IP2LOCATION-LITE-DB3 list. I'm
> running a process to convert an IPV4 address into a decimal number and then
> locate the first record in my table where the value I'm looking up is
> between the From_IP numeric value and the To_IP numeric value in the table
>
>
>
> Here's the code snippet I have working now:
>
> --
>
> LPARAMETERS toParams
>
> LOCAL lcBase2IP, lnElements, lnIpNumber
>
>
>
> IF USED("ip_locs")
>
>
>
>   lcCurrSel = ALIAS()
>
>
>
>   lnElements = ALINES(ipsegment, toParams.ocClientIp, 1, ".")
>
>
>
>   IF lnElements = 4
>
>
>
> lcBase2IP =
> PADL(THISFORM.utilities.DEC2BASX(VAL(ALLTRIM(ipsegment[1])), 2), 8, "0") + ;
>
>
> PADL(THISFORM.utilities.DEC2BASX(VAL(ALLTRIM(ipsegment[2])), 2), 8, "0") + ;
>
>
> PADL(THISFORM.utilities.DEC2BASX(VAL(ALLTRIM(ipsegment[3])), 2), 8, "0") + ;
>
>
> PADL(THISFORM.utilities.DEC2BASX(VAL(ALLTRIM(ipsegment[4])), 2), 8, "0")
>
>
>
> lnIpNumber = THISFORM.utilities.bin2dec(lcBase2IP)
>
>
>
> SELECT ip_locs
>
> LOCATE FOR lnIpNumber => ip_locs.ip_from AND lnIpNumber <=
> ip_locs.ip_to
>
>
>
> IF FOUND('ip_locs')
>
>
>
>   toParams.ocCountry_Code = ip_locs.Country_Code
>
>   toParams.ocCountry_Name = ip_locs.Country_Name
>
>   toParams.ocRegion_Name = ip_locs.Region_Name
>
>   toParams.ocCity_Name = ip_locs.City_Name
>
>
>
> ENDIF
>
>
>
>   ENDIF
>
>
>
>   SELECT (lcCurrSel)
>
>
>
> ENDIF
>
>
>
> RETURN
>
> ---
>
>
>
> This works fine and it really isn't that slow. It takes about 30 - 60
> seconds to do the location look up on a list of 50,000+ ip address records.
> I'm pretty sure I'm getting Rushmore optimization on the LOCATE FOR line,
> but I've been wondering if there is a way to use SEEK with an index to find
> a value between the numeric IP_FROM field and the numeric IP_TO field. But I
> cannot figure out how I can create an index that would give me Rushmore
> optimization and let me use the SEEK command for a value between those to
> numerics.
>
>
>
> FYI, I tried doing a simple SQL query to a temporary cursor, but
> surprisingly, the LOCATE FOR command is actually faster. Hey, I know I'm
> being greedy, but I'm wondering if it is possible to squeeze any more speed
> out of this process. I may be using the best method, but I figured if y'all
> had a minute you might give me a little feedback.
>
>
>
> Thanks in advance!
>
>
>
> Paul H. Tarver
> Tarver Program Consultants, Inc.
>
>
>
>
>
>
>
> --- StripMime Report -- processed MIME parts ---
> multipart/alternative
>   text/plain (text body -- kept)
>   text/html
> ---
>
[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: https://leafe.com/archives
This message: 
https://leafe.com/archives/byMID/CACW6n4vBBNaoWjARfLMGBGoVe96FEixm_UE_X-roNmu4s=m...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index & Seek Question

[Richard Kaye]

The BETWEEN() function is Rushmore optimizable. SYS(3054) will give you 
information on how optimized your query/index is.

--

rk



From: ProfoxTech  On Behalf Of Paul H. Tarver
Sent: Friday, December 15, 2023 6:08 PM
To: profoxt...@leafe.com
Subject: Index & Seek Question

Been quiet in here for a while, so I thought I'd toss out something I'm
tinkering with and ask for your advice. Besides, I want to keep my name on
the monthly top 10! J



I have a large table populated with the IP2LOCATION-LITE-DB3 list. I'm
running a process to convert an IPV4 address into a decimal number and then
locate the first record in my table where the value I'm looking up is
between the From_IP numeric value and the To_IP numeric value in the table



Here's the code snippet I have working now:

--

LPARAMETERS toParams

LOCAL lcBase2IP, lnElements, lnIpNumber



IF USED("ip_locs")



lcCurrSel = ALIAS()



lnElements = ALINES(ipsegment, toParams.ocClientIp, 1, ".")



IF lnElements = 4



lcBase2IP =
PADL(THISFORM.utilities.DEC2BASX(VAL(ALLTRIM(ipsegment[1])), 2), 8, "0") + ;


PADL(THISFORM.utilities.DEC2BASX(VAL(ALLTRIM(ipsegment[2])), 2), 8, "0") + ;


PADL(THISFORM.utilities.DEC2BASX(VAL(ALLTRIM(ipsegment[3])), 2), 8, "0") + ;


PADL(THISFORM.utilities.DEC2BASX(VAL(ALLTRIM(ipsegment[4])), 2), 8, "0")



lnIpNumber = THISFORM.utilities.bin2dec(lcBase2IP)



SELECT ip_locs

LOCATE FOR lnIpNumber => ip_locs.ip_from AND lnIpNumber <=
ip_locs.ip_to



IF FOUND('ip_locs')



toParams.ocCountry_Code = ip_locs.Country_Code

toParams.ocCountry_Name = ip_locs.Country_Name

toParams.ocRegion_Name = ip_locs.Region_Name

toParams.ocCity_Name = ip_locs.City_Name



ENDIF



ENDIF



SELECT (lcCurrSel)



ENDIF



RETURN

---



This works fine and it really isn't that slow. It takes about 30 - 60
seconds to do the location look up on a list of 50,000+ ip address records.
I'm pretty sure I'm getting Rushmore optimization on the LOCATE FOR line,
but I've been wondering if there is a way to use SEEK with an index to find
a value between the numeric IP_FROM field and the numeric IP_TO field. But I
cannot figure out how I can create an index that would give me Rushmore
optimization and let me use the SEEK command for a value between those to
numerics. 



FYI, I tried doing a simple SQL query to a temporary cursor, but
surprisingly, the LOCATE FOR command is actually faster. Hey, I know I'm
being greedy, but I'm wondering if it is possible to squeeze any more speed
out of this process. I may be using the best method, but I figured if y'all
had a minute you might give me a little feedback. 



Thanks in advance!



Paul H. Tarver 
Tarver Program Consultants, Inc. 







--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---

[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: https://leafe.com/archives
This message: 
https://leafe.com/archives/byMID/mn2pr10mb3407c3d8387737728836c4c7d2...@mn2pr10mb3407.namprd10.prod.outlook.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index & Seek Question

[Charlie]

If the field is indexed you can use SET SOFTSEEK. I think you'd then 
have to do another check to ensure the value being pointed to is also 
less than the upper bound.


HTH,
-Charlie


On 12/15/2023 5:08 PM, Paul H. Tarver wrote:

Been quiet in here for a while, so I thought I'd toss out something I'm
tinkering with and ask for your advice. Besides, I want to keep my name on
the monthly top 10! J

  


I have a large table populated with the IP2LOCATION-LITE-DB3 list. I'm
running a process to convert an IPV4 address into a decimal number and then
locate the first record in my table where the value I'm looking up is
between the From_IP numeric value and the To_IP numeric value in the table

  


Here's the code snippet I have working now:

--

LPARAMETERS toParams

LOCAL lcBase2IP, lnElements, lnIpNumber

  


IF USED("ip_locs")

  


   lcCurrSel = ALIAS()

  


   lnElements = ALINES(ipsegment, toParams.ocClientIp, 1, ".")

  


   IF lnElements = 4

  


 lcBase2IP =
PADL(THISFORM.utilities.DEC2BASX(VAL(ALLTRIM(ipsegment[1])), 2), 8, "0") + ;

  
PADL(THISFORM.utilities.DEC2BASX(VAL(ALLTRIM(ipsegment[2])), 2), 8, "0") + ;


  
PADL(THISFORM.utilities.DEC2BASX(VAL(ALLTRIM(ipsegment[3])), 2), 8, "0") + ;


  
PADL(THISFORM.utilities.DEC2BASX(VAL(ALLTRIM(ipsegment[4])), 2), 8, "0")


  


 lnIpNumber = THISFORM.utilities.bin2dec(lcBase2IP)

  


 SELECT ip_locs

 LOCATE FOR lnIpNumber => ip_locs.ip_from AND lnIpNumber <=
ip_locs.ip_to

  


 IF FOUND('ip_locs')

 


   toParams.ocCountry_Code = ip_locs.Country_Code

   toParams.ocCountry_Name = ip_locs.Country_Name

   toParams.ocRegion_Name = ip_locs.Region_Name

   toParams.ocCity_Name = ip_locs.City_Name

  


 ENDIF

  


   ENDIF

  


   SELECT (lcCurrSel)

  


ENDIF

  


RETURN

---

  


This works fine and it really isn't that slow. It takes about 30 - 60
seconds to do the location look up on a list of 50,000+ ip address records.
I'm pretty sure I'm getting Rushmore optimization on the LOCATE FOR line,
but I've been wondering if there is a way to use SEEK with an index to find
a value between the numeric IP_FROM field and the numeric IP_TO field. But I
cannot figure out how I can create an index that would give me Rushmore
optimization and let me use the SEEK command for a value between those to
numerics.

  


FYI, I tried doing a simple SQL query to a temporary cursor, but
surprisingly, the LOCATE FOR command is actually faster. Hey, I know I'm
being greedy, but I'm wondering if it is possible to squeeze any more speed
out of this process. I may be using the best method, but I figured if y'all
had a minute you might give me a little feedback.

  


Thanks in advance!

  


Paul H. Tarver
Tarver Program Consultants, Inc.



  




--- StripMime Report -- processed MIME parts ---
multipart/alternative
   text/plain (text body -- kept)
   text/html
---


[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: https://leafe.com/archives
This message: 
https://leafe.com/archives/byMID/df8ee1f4-19a6-463c-919f-dd96a9ddf...@gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index on collector tag collector unique

[Michael Madigan]

Thanks Ted,

I just realized that If I set deleted off, I get the correct results, so the 
first record with collector 31 must be deleted.   
I will just use the SQL statement you suggested to make sure it's always 
correct.  
Thank you.  



  From: Ted Roche 
 To: "profox@leafe.com"  
 Sent: Monday, November 28, 2016 3:46 PM
 Subject: Re: Index on collector tag collector unique
  
Pretty weird. That's a bug. Or perhaps a corruption in the existing indexes.

SELECT DISTINCT collector in MyTable ORDER BY collector INTO CURSOR YourCursor

can give you the same thing, real quick if you already have an index
tag on collector, and even if you don't.



On Mon, Nov 28, 2016 at 3:30 PM, Michael Madigan  wrote:
> Strange behavior I've never seen before
> I have a 3 character field called collector.    I have many records with 
> collector= '31' .  When I index using the command "Index on collector tag 
> collector unique"  I get every one except collector 31.  I get collector 3, I 
> get collector 30, but I don't get collector 31.  There are over 2000 records 
> with collector = '31'
>
> I realize you have to reindex every time to make sure you have every 
> collector accounted for, so it's not that
>
> This is really odd.
>
> --- StripMime Report -- processed MIME parts ---
> multipart/alternative
>  text/plain (text body -- kept)
>  text/html
> ---
>
[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/1894721230.1808438.1480368663...@mail.yahoo.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index on collector tag collector unique

[Ted Roche]

Pretty weird. That's a bug. Or perhaps a corruption in the existing indexes.

SELECT DISTINCT collector in MyTable ORDER BY collector INTO CURSOR YourCursor

can give you the same thing, real quick if you already have an index
tag on collector, and even if you don't.



On Mon, Nov 28, 2016 at 3:30 PM, Michael Madigan  wrote:
> Strange behavior I've never seen before
> I have a 3 character field called collector.I have many records with 
> collector= '31' .   When I index using the command "Index on collector tag 
> collector unique"   I get every one except collector 31.   I get collector 3, 
> I get collector 30, but I don't get collector 31.   There are over 2000 
> records with collector = '31'
>
> I realize you have to reindex every time to make sure you have every 
> collector accounted for, so it's not that
>
> This is really odd.
>
> --- StripMime Report -- processed MIME parts ---
> multipart/alternative
>   text/plain (text body -- kept)
>   text/html
> ---
>
[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/CACW6n4tjJ2scWrT9cavOh9a7Ngsqs=lk_ymv98cnkafjter...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index Corruption / SMB

[Chris Davis]

We have now applied the Enterprise Rollup and enabled SMB2 again, so I will see 
how it goes.

Thanks

Chris.

-Original Message-
From: ProfoxTech [mailto:profoxtech-boun...@leafe.com] On Behalf Of Alan Bourke
Sent: 11 March 2015 09:51
To: profoxt...@leafe.com
Subject: Re: Index Corruption / SMB

I have never needed to go near any disk write caching settings since Windows 
95/98 days. YMMV.

There is also file lock caching to do with SMB. 

--
  Alan Bourke
  alanpbourke (at) fastmail (dot) fm

On Wed, 11 Mar 2015, at 09:11 AM, Allen wrote:
> Does this mean that write ahead cache on the disk drive can be safely 
> left on? And is this win 7 and 8 or just servers?
> Al
> 
> -Original Message-
> From: Dave Crozier
> Sent: Wednesday, March 11, 2015 9:20 AM
> To: profoxt...@leafe.com
> Subject: RE: Index Corruption / SMB
> 
> +1 for Alan's comment. SMB3 seems to have cured all the cache problems
> inherent with SMB1/SMB2
> 
> Dave
> 
> -Original Message-
> From: ProFox [mailto:profox-boun...@leafe.com] On Behalf Of Alan 
> Bourke
> Sent: 10 March 2015 14:52
> To: profox@leafe.com; profoxt...@leafe.com
> Subject: Re: Index Corruption / SMB
> 
> 
> On Tue, 10 Mar 2015, at 02:32 PM, Man-wai Chang wrote:
> > In addition to un-necessary caching, you also need to disable 
> > opportunistic locking (oplocks) in Samba.
> 
> 
> No, you don't. Leave the caching alone. it's there for a reason. If 
> you're having to mess with caching on a regular basis, you need to be 
> looking at your program code.
> 
> You *can't* turn off OpLocks under SMB2 and SMB3. You have to knock 
> everything back to SMB1 and then turn off OpLocks which to me is both 
> unnecessary and a bad idea.
> 
> 
[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/50b267080b15c148a824de4785a7451e83a3e...@dc4hs14exmbx06.os.local
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index Corruption / SMB

[Richard Kaye]

That's an interesting endorsement. I've been burned so many times by index or 
table corruption due to SMB issues that it's been really hard to trust MS when 
they say "No, really. We've fixed it this time."

--

rk
-Original Message-
From: ProfoxTech [mailto:profoxtech-boun...@leafe.com] On Behalf Of Dave Crozier
Sent: Wednesday, March 11, 2015 4:21 AM
To: profoxt...@leafe.com
Subject: RE: Index Corruption / SMB

+1 for Alan's comment. SMB3 seems to have cured all the cache problems 
+inherent with SMB1/SMB2

Dave

-Original Message-
From: ProFox [mailto:profox-boun...@leafe.com] On Behalf Of Alan Bourke
Sent: 10 March 2015 14:52
To: profox@leafe.com; profoxt...@leafe.com
Subject: Re: Index Corruption / SMB


On Tue, 10 Mar 2015, at 02:32 PM, Man-wai Chang wrote:
> In addition to un-necessary caching, you also need to disable 
> opportunistic locking (oplocks) in Samba.


No, you don't. Leave the caching alone. it's there for a reason. If you're 
having to mess with caching on a regular basis, you need to be looking at your 
program code.

You *can't* turn off OpLocks under SMB2 and SMB3. You have to knock everything 
back to SMB1 and then turn off OpLocks which to me is both unnecessary and a 
bad idea.


[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/cy1pr10mb0218865e7f7716efa013d069d2...@cy1pr10mb0218.namprd10.prod.outlook.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index Corruption / SMB

[Alan Bourke]

I have never needed to go near any disk write caching settings since
Windows 95/98 days. YMMV.

There is also file lock caching to do with SMB. 

-- 
  Alan Bourke
  alanpbourke (at) fastmail (dot) fm

On Wed, 11 Mar 2015, at 09:11 AM, Allen wrote:
> Does this mean that write ahead cache on the disk drive can be safely
> left 
> on? And is this win 7 and 8 or just servers?
> Al
> 
> -Original Message- 
> From: Dave Crozier
> Sent: Wednesday, March 11, 2015 9:20 AM
> To: profoxt...@leafe.com
> Subject: RE: Index Corruption / SMB
> 
> +1 for Alan's comment. SMB3 seems to have cured all the cache problems 
> inherent with SMB1/SMB2
> 
> Dave
> 
> -Original Message-
> From: ProFox [mailto:profox-boun...@leafe.com] On Behalf Of Alan Bourke
> Sent: 10 March 2015 14:52
> To: profox@leafe.com; profoxt...@leafe.com
> Subject: Re: Index Corruption / SMB
> 
> 
> On Tue, 10 Mar 2015, at 02:32 PM, Man-wai Chang wrote:
> > In addition to un-necessary caching, you also need to disable
> > opportunistic locking (oplocks) in Samba.
> 
> 
> No, you don't. Leave the caching alone. it's there for a reason. If
> you're 
> having to mess with caching on a regular basis, you need to be looking at 
> your program code.
> 
> You *can't* turn off OpLocks under SMB2 and SMB3. You have to knock 
> everything back to SMB1 and then turn off OpLocks which to me is both 
> unnecessary and a bad idea.
> 
> 
[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/1426067444.1472998.238796701.20b35...@webmail.messagingengine.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index Corruption / SMB

[Man-wai Chang]

On Tue, Mar 10, 2015 at 10:51 PM, Alan Bourke  wrote:
> No, you don't. Leave the caching alone. it's there for a reason. If
> you're having to mess with caching on a regular basis, you need to be
> looking at your program code.

All kinds of write caching is evil, in my conservative opinions :)

> You *can't* turn off OpLocks under SMB2 and SMB3. You have to knock
> everything back to SMB1 and then turn off OpLocks which to me is both
> unnecessary and a bad idea.

I didn't know that. I remember smb.conf had options for oplocks...
https://www.samba.org/samba/docs/man/Samba-HOWTO-Collection/locking.html

-- 
 .~. Might, Courage, Vision. SINCERITY!
/ v \ 64-bit Ubuntu 9.10 (Linux kernel 2.6.39.3)
/( _ )\ http://sites.google.com/site/changmw
^ ^ May the Force and farces be with you!

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/CAGv=MJCt=8_67G3Ltn24LX=ZXbZanp0rzY2Dh1iXuwXJf1Xo=g...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index Corruption / SMB

[Allen]

Does this mean that write ahead cache on the disk drive can be safely left 
on? And is this win 7 and 8 or just servers?

Al

-Original Message- 
From: Dave Crozier

Sent: Wednesday, March 11, 2015 9:20 AM
To: profoxt...@leafe.com
Subject: RE: Index Corruption / SMB

+1 for Alan's comment. SMB3 seems to have cured all the cache problems 
inherent with SMB1/SMB2


Dave

-Original Message-
From: ProFox [mailto:profox-boun...@leafe.com] On Behalf Of Alan Bourke
Sent: 10 March 2015 14:52
To: profox@leafe.com; profoxt...@leafe.com
Subject: Re: Index Corruption / SMB


On Tue, 10 Mar 2015, at 02:32 PM, Man-wai Chang wrote:

In addition to un-necessary caching, you also need to disable
opportunistic locking (oplocks) in Samba.



No, you don't. Leave the caching alone. it's there for a reason. If you're 
having to mess with caching on a regular basis, you need to be looking at 
your program code.


You *can't* turn off OpLocks under SMB2 and SMB3. You have to knock 
everything back to SMB1 and then turn off OpLocks which to me is both 
unnecessary and a bad idea.



[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/E394794D2C564B08B55AF3412BA5610E@GSL.local
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index Corruption / SMB

[Dave Crozier]

+1 for Alan's comment. SMB3 seems to have cured all the cache problems inherent 
with SMB1/SMB2

Dave

-Original Message-
From: ProFox [mailto:profox-boun...@leafe.com] On Behalf Of Alan Bourke
Sent: 10 March 2015 14:52
To: profox@leafe.com; profoxt...@leafe.com
Subject: Re: Index Corruption / SMB


On Tue, 10 Mar 2015, at 02:32 PM, Man-wai Chang wrote:
> In addition to un-necessary caching, you also need to disable 
> opportunistic locking (oplocks) in Samba.


No, you don't. Leave the caching alone. it's there for a reason. If you're 
having to mess with caching on a regular basis, you need to be looking at your 
program code.

You *can't* turn off OpLocks under SMB2 and SMB3. You have to knock everything 
back to SMB1 and then turn off OpLocks which to me is both unnecessary and a 
bad idea.


[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/18725b8cd2d5d247873a2baf401d4ab2468c9...@ex2010-a-fpl.fpl.LOCAL
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index Corruption / SMB

[Alan Bourke]

On Tue, 10 Mar 2015, at 02:32 PM, Man-wai Chang wrote:
> In addition to un-necessary caching, you also need to disable
> opportunistic locking (oplocks) in Samba.


No, you don't. Leave the caching alone. it's there for a reason. If
you're having to mess with caching on a regular basis, you need to be
looking at your program code.

You *can't* turn off OpLocks under SMB2 and SMB3. You have to knock
everything back to SMB1 and then turn off OpLocks which to me is both
unnecessary and a bad idea.


___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/1425999101.164.238402333.4f406...@webmail.messagingengine.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index Corruption / SMB

[Man-wai Chang]

In addition to un-necessary caching, you also need to disable
opportunistic locking (oplocks) in Samba.

On Mon, Mar 9, 2015 at 5:07 PM, Chris Davis  wrote:
> We have been for a while disabling SMB2 and in general this seems to remove 
> any issues.
> Although we have one site which despite this still seems to have regular 
> index corruption.

-- 
 .~. Might, Courage, Vision. SINCERITY!
/ v \ 64-bit Ubuntu 9.10 (Linux kernel 2.6.39.3)
/( _ )\ http://sites.google.com/site/changmw
^ ^ May the Force and farces be with you!

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/CAGv=MJAZiP7xZFrTKH=2xvjndugvjpvu_24evfyfnrhabsn...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index Corruption / SMB

[Ted Roche]

On Mon, Mar 9, 2015 at 5:07 AM, Chris Davis  wrote:
>
> Although we have one site which despite this still seems to have regular 
> index corruption.
>

The shaky implementation of SMB2 was just the latest thing to cause
index corruption, which we've had in Fox since we put it on networks.
Check the logs on servers and workstations to see if there are errors
being logged at the lower network layers; a bad NIC or cable or
electrical interference can wreak havoc with Fox files.


-- 
Ted Roche
Ted Roche & Associates, LLC
http://www.tedroche.com

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/cacw6n4uovwj2on225r449jzp-dec3nflvjsgluqat7bnaag...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index Corruption / SMB

[Chris Davis]

Super thanks for this, we will try enabling it and applying the hotfix.

-Original Message-
From: ProfoxTech [mailto:profoxtech-boun...@leafe.com] On Behalf Of Alan Bourke
Sent: 09 March 2015 10:49
To: profoxt...@leafe.com
Subject: Re: Index Corruption / SMB

Also the below just applies to Server 2008. With Server 2012 thus far I have 
not had to do anything.

--
  Alan Bourke
  alanpbourke (at) fastmail (dot) fm

On Mon, 9 Mar 2015, at 10:41 AM, Alan Bourke wrote:
> The situation in my opinion is:
> 
> Leave SMB and caching settings alone. 
> 
> There were issues with Server 2008 at one point which have long since 
> been fixed, however you should install the Enterprise Hotfix Rollup on 
> Server 2008 and Windows 7 to avail of further fixes to SMB which you 
> will not have gotten via the Windows Update route.
> 
> We do not see index corruption issues or similar with the above in 
> place.
> 
> 
> 
> -- 
>   Alan Bourke
>   alanpbourke (at) fastmail (dot) fm
> 
> On Mon, 9 Mar 2015, at 09:07 AM, Chris Davis wrote:
> > Morning All
> > 
> > Could I raise this old chestnut please.
> > 
> > Just looking really for what's considered the correct configuration 
> > for Windows Server 2008/2012.
> > 
> > We have been for a while disabling SMB2 and in general this seems to 
> > remove any issues.
> > 
> > Although we have one site which despite this still seems to have 
> > regular index corruption.
> > 
> > Any thoughts?
> > 
> > Thanks
> > 
> > Chris.
> > 
> > 
> > __ This email has been scanned by the Symantec Email Security.cloud 
> > service.
> > For more information please visit http://www.symanteccloud.com 
> > 
> > __
> > 
> > --- StripMime Report -- processed MIME parts --- 
> > multipart/alternative
> >   text/plain (text body -- kept)
> >   text/html
> > ---
> > 
[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/50b267080b15c148a824de4785a7451e83a3c...@dc4hs14exmbx06.os.local
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index Corruption / SMB

[Alan Bourke]

Also the below just applies to Server 2008. With Server 2012 thus far I
have not had to do anything.

-- 
  Alan Bourke
  alanpbourke (at) fastmail (dot) fm

On Mon, 9 Mar 2015, at 10:41 AM, Alan Bourke wrote:
> The situation in my opinion is:
> 
> Leave SMB and caching settings alone. 
> 
> There were issues with Server 2008 at one point which have long since
> been fixed, however you should install the Enterprise Hotfix Rollup on
> Server 2008 and Windows 7 to avail of further fixes to SMB which you
> will not have gotten via the Windows Update route.
> 
> We do not see index corruption issues or similar with the above in
> place.
> 
> 
> 
> -- 
>   Alan Bourke
>   alanpbourke (at) fastmail (dot) fm
> 
> On Mon, 9 Mar 2015, at 09:07 AM, Chris Davis wrote:
> > Morning All
> > 
> > Could I raise this old chestnut please.
> > 
> > Just looking really for what's considered the correct configuration for
> > Windows Server 2008/2012.
> > 
> > We have been for a while disabling SMB2 and in general this seems to
> > remove any issues.
> > 
> > Although we have one site which despite this still seems to have regular
> > index corruption.
> > 
> > Any thoughts?
> > 
> > Thanks
> > 
> > Chris.
> > 
> > __
> > This email has been scanned by the Symantec Email Security.cloud service.
> > For more information please visit http://www.symanteccloud.com
> > __
> > 
> > --- StripMime Report -- processed MIME parts ---
> > multipart/alternative
> >   text/plain (text body -- kept)
> >   text/html
> > ---
> > 
[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/1425898140.192145.237775577.013e9...@webmail.messagingengine.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index Corruption / SMB

[Alan Bourke]

The situation in my opinion is:

Leave SMB and caching settings alone. 

There were issues with Server 2008 at one point which have long since
been fixed, however you should install the Enterprise Hotfix Rollup on
Server 2008 and Windows 7 to avail of further fixes to SMB which you
will not have gotten via the Windows Update route.

We do not see index corruption issues or similar with the above in
place.



-- 
  Alan Bourke
  alanpbourke (at) fastmail (dot) fm

On Mon, 9 Mar 2015, at 09:07 AM, Chris Davis wrote:
> Morning All
> 
> Could I raise this old chestnut please.
> 
> Just looking really for what's considered the correct configuration for
> Windows Server 2008/2012.
> 
> We have been for a while disabling SMB2 and in general this seems to
> remove any issues.
> 
> Although we have one site which despite this still seems to have regular
> index corruption.
> 
> Any thoughts?
> 
> Thanks
> 
> Chris.
> 
> __
> This email has been scanned by the Symantec Email Security.cloud service.
> For more information please visit http://www.symanteccloud.com
> __
> 
> --- StripMime Report -- processed MIME parts ---
> multipart/alternative
>   text/plain (text body -- kept)
>   text/html
> ---
> 
[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/1425897660.188496.237771221.69492...@webmail.messagingengine.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index Corruption / SMB

[Man-wai Chang]

I think you need to disable all sort of writing-caching as well as
opportunistic caching

I suspect you don't have to disable SMB2, which is just a protocol.

On Mon, Mar 9, 2015 at 5:07 PM, Chris Davis  wrote:
> We have been for a while disabling SMB2 and in general this seems to remove 
> any issues.
> Although we have one site which despite this still seems to have regular 
> index corruption.


-- 
 .~. Might, Courage, Vision. SINCERITY!
/ v \ 64-bit Ubuntu 9.10 (Linux kernel 2.6.39.3)
/( _ )\ http://sites.google.com/site/changmw
^ ^ May the Force and farces be with you!

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/CAGv=mjcwgf5oronpggyttnrvpf0nwvclnp8cnh_g6jmotnm...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index

[mbsoftwaresolutions]

On 2014-07-17 13:58, Stephen Russell wrote:

Says nothing about using plural indexes at the same time.


Using SYS(3054,x) would help you see the Rushmore optimizations, akin to 
EXPLAIN in MySQL.




___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/16a0eb017e2548fd2bec05d587a6c...@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index

[mbsoftwaresolutions]

On 2014-07-17 11:40, Stephen Russell wrote:

I don't think that VFP can optimize multiple indexes either.



No...that was one of the bright points about VFP...it *could* do that.

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/2467cd1bedd74aa11f78e3d042dee...@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index

[Ted Roche]

On Thu, Jul 17, 2014 at 12:43 PM, Richard Kaye  wrote:

> Rushmore can use multiple indexes. Always has.
>

I'm fairly sure the Rushmore technology, once-and-perhaps-still trademarked
and patent-pending, was built with the ability to take multiple index
bitmaps built from the original B+ trees and merge those values to optimize
the records that were ultimately read into the result set. In the end, I
think it was this technology (and the couple of really, really smart guys
who wrote the code) that resulted in Microsoft purchasing Fox Software and
in the coming years introduce similar technologies into the Access engine,
ODBC and SQL Server.



> Learning how to make indexes that are used in a join and participate in
> the where clause is the tough nut to crack.
>
> --
> Stephen Russell
>

Hence MS's motivation at hiring the coders and taking out a competitor with
the "merger"


-- 
Ted Roche
Ted Roche & Associates, LLC
http://www.tedroche.com


--- StripMime Report -- processed MIME parts ---
multipart/alternative
  text/plain (text body -- kept)
  text/html
---

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/cacw6n4vlswoalku7ynifxhptyr2hbq8bwv0sytwrekyzda+...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index

[Stephen Russell]

On Thu, Jul 17, 2014 at 11:43 AM, Richard Kaye  wrote:

> Rushmore can use multiple indexes. Always has.
>
> >From the help file:
> "Visual FoxPro SQL commands use Rushmore as a basic tool in multi-table
> query optimization, using existing indexes and even creating new ad-hoc
> indexes to speed queries."
> -



Says nothing about using plural indexes at the same time.

I read Multi Table where an index on a join condition will be desired first
whenever a multi table query is defined.

The statement after that "creating ad-hoc indexes to speed queries" sounds
false.  I don't ever remember anyone talking about dynamic index creation
EVER with respect to Rushmore the early days.

If it generated on the fly you would never need to have them and THAT would
have been repeated over and over again.  :)
Thus it sounds like marketing hype.



-- 
Stephen Russell
Sr. Analyst
Ring Container Technology
Oakland TN

901.246-0159 cell


--- StripMime Report -- processed MIME parts ---
multipart/alternative
  text/plain (text body -- kept)
  text/html
---

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/CAJidMYJY82Xw+Qf=zuggcxqqxi3dd8cb+8kbqxzoubyx8g0...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index

[Richard Kaye]

Rushmore can use multiple indexes. Always has.

>From the help file:
"Visual FoxPro SQL commands use Rushmore as a basic tool in multi-table query 
optimization, using existing indexes and even creating new ad-hoc indexes to 
speed queries."

--

rk
-Original Message-
From: ProfoxTech [mailto:profoxtech-boun...@leafe.com] On Behalf Of Stephen 
Russell
Sent: Thursday, July 17, 2014 11:41 AM
To: profoxt...@leafe.com
Subject: Re: Index

On Thu, Jul 17, 2014 at 8:56 AM, <

I don't think that VFP can optimize multiple indexes either.

Learning how to make indexes that are used in a join and participate in the 
where clause is the tough nut to crack.

--
Stephen Russell
Sr. Analyst
Ring Container Technology
Oakland TN

901.246-0159 cell


___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/DF1EEF11E586A64FB54A97F22A8BD04423A2B81760@ACKBWDDQH1.artfact.local
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index

[Stephen Russell]

On Thu, Jul 17, 2014 at 8:56 AM, <
mbsoftwaresoluti...@mbsoftwaresolutions.com> wrote:

> I guess my view has been changed because I use MySQL (MariaDB) on the
> backend now (for years) and I believe I learned that it won't optimize with
> multiple indexes...it only chooses one.  So if I had 3 separate indexes,
> it'd only use one of them, whereas if I had all 3 fields in the index, my
> EXPLAIN showed that I'd get a better result (if I were searching for all 3
> fields).  You've used MySQL and even been certified as I recall in it, Ted.
>  Am I wrong?
>  -


I don't think that VFP can optimize multiple indexes either.

Learning how to make indexes that are used in a join and participate in the
where clause is the tough nut to crack.


-- 
Stephen Russell
Sr. Analyst
Ring Container Technology
Oakland TN

901.246-0159 cell


--- StripMime Report -- processed MIME parts ---
multipart/alternative
  text/plain (text body -- kept)
  text/html
---

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/cajidmykc1fefewoi0uwpubjse81ehrrkzdfrxownvfaw7yd...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index

[mbsoftwaresolutions]

I guess my view has been changed because I use MySQL (MariaDB) on the 
backend now (for years) and I believe I learned that it won't optimize 
with multiple indexes...it only chooses one.  So if I had 3 separate 
indexes, it'd only use one of them, whereas if I had all 3 fields in the 
index, my EXPLAIN showed that I'd get a better result (if I were 
searching for all 3 fields).  You've used MySQL and even been certified 
as I recall in it, Ted.  Am I wrong?


Thanks,
--Michael


On 2014-07-17 07:31, Ted Roche wrote:

The compound index will only work on the one situation where you are
processing field1+field2+field3 ("ORDER BY field1+field2+field3") while
using individual indexes lets Rushmore use the for more operations 
(like
WHERE field1=this and Field2=that). Rushmore is primarily focused on 
the

WHERE clause, since you lose speed reading records you don't have to.

In nearly all cases, individual, atomic indexes, one field each, are 
better

than multiple.

However, in optimizing a single statement, the key is to ensure the 
LEFT
SIDE of the WHERE expressions are EXACTLY the same as the expression 
used

to create the index, such as:

WHERE STR(iClientID,8) = "12345678"

will only work if you have an index created with that exact same 
expression,


INDEX on STR(iClientID,8) TAG AReallyDumbIndex



On Wed, Jul 16, 2014 at 6:05 PM, Jeff Johnson  wrote:


Michael:  I was going to say that but I didn't have the guts.  ;^)


On 7/16/2014 2:41 PM, mbsoftwaresoluti...@mbsoftwaresolutions.com 
wrote:



I'm gonna make a wager and say "NO", and I know that's a large risky
wager seeing how it's Ted's comment (and we all know he's an ace!).

Why not the compound index as you (Jeff) described?  I would have 
said

the same thing.

--Mike



On 2014-07-16 17:23, Jeff Johnson wrote:


Ted:  So these indexes are created so that when you run that select
statement it will put them in order?

Interesting.

On 7/16/2014 1:56 PM, Ted Roche wrote:


Sytze:

I've been surprised to find everyone works with a "large amount of 
data"
but for some of us, that's thirty thousand records and for others 
it is

thirty million.


INDEX ON pcode TAG pcode
INDEX ON pcode2 TAG pcode2
INDEX ON pcode3 TAG pcode3
INDEX ON DELETED() TAG DELETED



On Wed, Jul 16, 2014 at 4:40 PM, Sytze de Boer 


wrote:

 After yesterday, with heart in mouth, I ask the following


I have a table with large amount of data
It contains Master contract, sub contract, sub-sub contract
pcode N(5)
pcode2 N(7)
pcode3 N(9)

Example
3770
3770, 377001
3770, 377001, 37700101
3770, 377001, 37700102
3770, 377002
3770, 377002, 37700201
3770, 377002, 37700202

When I do a select statement, I seem to get them in correct order 
with

select * from contract order by pcode,pcode2,pcode3 etc

But if I want to create an index, I lose my head.

Any pointers?

--
Kind regards,
Sytze de Boer
Kiss Software


--- StripMime Report -- processed MIME parts ---
multipart/alternative
   text/plain (text body -- kept)
   text/html
---


[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/79a897cb1ecd40c35bb608d8d7879...@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index

[Ted Roche]

The compound index will only work on the one situation where you are
processing field1+field2+field3 ("ORDER BY field1+field2+field3") while
using individual indexes lets Rushmore use the for more operations (like
WHERE field1=this and Field2=that). Rushmore is primarily focused on the
WHERE clause, since you lose speed reading records you don't have to.

In nearly all cases, individual, atomic indexes, one field each, are better
than multiple.

However, in optimizing a single statement, the key is to ensure the LEFT
SIDE of the WHERE expressions are EXACTLY the same as the expression used
to create the index, such as:

WHERE STR(iClientID,8) = "12345678"

will only work if you have an index created with that exact same expression,

INDEX on STR(iClientID,8) TAG AReallyDumbIndex



On Wed, Jul 16, 2014 at 6:05 PM, Jeff Johnson  wrote:

> Michael:  I was going to say that but I didn't have the guts.  ;^)
>
>
> On 7/16/2014 2:41 PM, mbsoftwaresoluti...@mbsoftwaresolutions.com wrote:
>
>> I'm gonna make a wager and say "NO", and I know that's a large risky
>> wager seeing how it's Ted's comment (and we all know he's an ace!).
>>
>> Why not the compound index as you (Jeff) described?  I would have said
>> the same thing.
>>
>> --Mike
>>
>>
>>
>> On 2014-07-16 17:23, Jeff Johnson wrote:
>>
>>> Ted:  So these indexes are created so that when you run that select
>>> statement it will put them in order?
>>>
>>> Interesting.
>>>
>>> On 7/16/2014 1:56 PM, Ted Roche wrote:
>>>
 Sytze:

 I've been surprised to find everyone works with a "large amount of data"
 but for some of us, that's thirty thousand records and for others it is
 thirty million.


 INDEX ON pcode TAG pcode
 INDEX ON pcode2 TAG pcode2
 INDEX ON pcode3 TAG pcode3
 INDEX ON DELETED() TAG DELETED



 On Wed, Jul 16, 2014 at 4:40 PM, Sytze de Boer 
 wrote:

  After yesterday, with heart in mouth, I ask the following
>
> I have a table with large amount of data
> It contains Master contract, sub contract, sub-sub contract
> pcode N(5)
> pcode2 N(7)
> pcode3 N(9)
>
> Example
> 3770
> 3770, 377001
> 3770, 377001, 37700101
> 3770, 377001, 37700102
> 3770, 377002
> 3770, 377002, 37700201
> 3770, 377002, 37700202
>
> When I do a select statement, I seem to get them in correct order with
> select * from contract order by pcode,pcode2,pcode3 etc
>
> But if I want to create an index, I lose my head.
>
> Any pointers?
>
> --
> Kind regards,
> Sytze de Boer
> Kiss Software
>
>
> --- StripMime Report -- processed MIME parts ---
> multipart/alternative
>text/plain (text body -- kept)
>text/html
> ---
>
[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/CACW6n4tFdkede=k-6ncalzbdmx9fdhpw8ey2dtedllxomr0...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index

[Ted Roche]

On Wed, Jul 16, 2014 at 5:01 PM, Fernando D. Bozzo 
wrote:

> The index for DELETED() is only justifiable if deleted records are more
> than 20% of the total records of the DBF.
>

I hadn't heard that figure quoted before.

This was discussed years ago in the magazines, including a great series by
Mac Rubel and a wonderful article by our host Ed Leafe. In concept, the
idea of having deleted tags was to avoid forcing the query engine to have
to read the entire physical table in order  to deterine which records
applied to the result set. On very large sets, avoiding a lot of
unnecessary reads would result in significant speedup. However, it depends
on so many factors: network latency, bandwidth and saturation, disk(s)
speed and seek time, amount of fragmentation, how much of the table is
already cached, size of the table, block size and so forth.

The most important point to remember is this: you must test your technique
on your data, on your network, under the real workload that the application
will experience. Otherwise, we are all just speculating.


> Not always the "full optimize" is the best option, and sometimes it takes
> more time than without this index.
>

True!


-- 
Ted Roche
Ted Roche & Associates, LLC
http://www.tedroche.com


--- StripMime Report -- processed MIME parts ---
multipart/alternative
  text/plain (text body -- kept)
  text/html
---

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/cacw6n4tf7eku97kx+pshnagphua1trxoy+9ustq0bfnha4v...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index

[Laurie Alvey]

IMHO, since I can't imagine that you'll be doing arithmetic on these
columns, even though they contain only digits, they should really be of
type C. While VFP lets us index on an expression, if you ever move to SQL
Server for example, you'll be in trouble.

Laurie


On 16 July 2014 23:07, Eurico Chagas Filho  wrote:

> Use BINTOC().
>
> E.
>
>
> On Wednesday, July 16, 2014 6:40 PM, "
> mbsoftwaresoluti...@mbsoftwaresolutions.com" <
> mbsoftwaresoluti...@mbsoftwaresolutions.com> wrote:
>
>
> >
> >
> >I'm gonna make a wager and say "NO", and I know that's a large risky
> >wager seeing how it's Ted's comment (and we all know he's an ace!).
> >
> >Why not the compound index as you (Jeff) described?  I would have said
> >the same thing.
> >
> >--Mike
> >
> >
> >On 2014-07-16 17:23, Jeff Johnson wrote:
> >> Ted:  So these indexes are created so that when you run that select
> >> statement it will put them in order?
> >>
> >> Interesting.
> >>
> >> On 7/16/2014 1:56 PM, Ted Roche wrote:
> >>> Sytze:
> >>>
> >>> I've been surprised to find everyone works with a "large amount of
> >>> data"
> >>> but for some of us, that's thirty thousand records and for others it
> >>> is
> >>> thirty million.
> >>>
> >>>
> >>> INDEX ON pcode TAG pcode
> >>> INDEX ON pcode2 TAG pcode2
> >>> INDEX ON pcode3 TAG pcode3
> >>> INDEX ON DELETED() TAG DELETED
> >>>
> >>>
> >>>
> >>> On Wed, Jul 16, 2014 at 4:40 PM, Sytze de Boer 
> >>> wrote:
> >>>
>  After yesterday, with heart in mouth, I ask the following
> 
>  I have a table with large amount of data
>  It contains Master contract, sub contract, sub-sub contract
>  pcode N(5)
>  pcode2 N(7)
>  pcode3 N(9)
> 
>  Example
>  3770
>  3770, 377001
>  3770, 377001, 37700101
>  3770, 377001, 37700102
>  3770, 377002
>  3770, 377002, 37700201
>  3770, 377002, 37700202
> 
>  When I do a select statement, I seem to get them in correct order
>  with
>  select * from contract order by pcode,pcode2,pcode3 etc
> 
>  But if I want to create an index, I lose my head.
> 
>  Any pointers?
> 
>  --
>  Kind regards,
>  Sytze de Boer
>  Kiss Software
> 
> 
>  --- StripMime Report -- processed MIME parts ---
>  multipart/alternative
> text/plain (text body -- kept)
[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/camvtr9ezfuml32ejznmtdbdohpihcpevtyscr3nqhespf+n...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index

[Eurico Chagas Filho]

Use BINTOC().

E.


On Wednesday, July 16, 2014 6:40 PM, 
"mbsoftwaresoluti...@mbsoftwaresolutions.com" 
 wrote:
 

>
>
>I'm gonna make a wager and say "NO", and I know that's a large risky 
>wager seeing how it's Ted's comment (and we all know he's an ace!).
>
>Why not the compound index as you (Jeff) described?  I would have said 
>the same thing.
>
>--Mike
>
>
>On 2014-07-16 17:23, Jeff Johnson wrote:
>> Ted:  So these indexes are created so that when you run that select
>> statement it will put them in order?
>> 
>> Interesting.
>> 
>> On 7/16/2014 1:56 PM, Ted Roche wrote:
>>> Sytze:
>>> 
>>> I've been surprised to find everyone works with a "large amount of 
>>> data"
>>> but for some of us, that's thirty thousand records and for others it 
>>> is
>>> thirty million.
>>> 
>>> 
>>> INDEX ON pcode TAG pcode
>>> INDEX ON pcode2 TAG pcode2
>>> INDEX ON pcode3 TAG pcode3
>>> INDEX ON DELETED() TAG DELETED
>>> 
>>> 
>>> 
>>> On Wed, Jul 16, 2014 at 4:40 PM, Sytze de Boer  
>>> wrote:
>>> 
 After yesterday, with heart in mouth, I ask the following
 
 I have a table with large amount of data
 It contains Master contract, sub contract, sub-sub contract
 pcode N(5)
 pcode2 N(7)
 pcode3 N(9)
 
 Example
 3770
 3770, 377001
 3770, 377001, 37700101
 3770, 377001, 37700102
 3770, 377002
 3770, 377002, 37700201
 3770, 377002, 37700202
 
 When I do a select statement, I seem to get them in correct order 
 with
 select * from contract order by pcode,pcode2,pcode3 etc
 
 But if I want to create an index, I lose my head.
 
 Any pointers?
 
 --
 Kind regards,
 Sytze de Boer
 Kiss Software
 
 
 --- StripMime Report -- processed MIME parts ---
 multipart/alternative
    text/plain (text body -- kept)
    text/[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/1405548426.71223.yahoomail...@web163401.mail.gq1.yahoo.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index

[Jeff Johnson]

Michael:  I was going to say that but I didn't have the guts.  ;^)


On 7/16/2014 2:41 PM, mbsoftwaresoluti...@mbsoftwaresolutions.com wrote:
I'm gonna make a wager and say "NO", and I know that's a large risky 
wager seeing how it's Ted's comment (and we all know he's an ace!).


Why not the compound index as you (Jeff) described?  I would have said 
the same thing.


--Mike


On 2014-07-16 17:23, Jeff Johnson wrote:

Ted:  So these indexes are created so that when you run that select
statement it will put them in order?

Interesting.

On 7/16/2014 1:56 PM, Ted Roche wrote:

Sytze:

I've been surprised to find everyone works with a "large amount of 
data"

but for some of us, that's thirty thousand records and for others it is
thirty million.


INDEX ON pcode TAG pcode
INDEX ON pcode2 TAG pcode2
INDEX ON pcode3 TAG pcode3
INDEX ON DELETED() TAG DELETED



On Wed, Jul 16, 2014 at 4:40 PM, Sytze de Boer 
 wrote:



After yesterday, with heart in mouth, I ask the following

I have a table with large amount of data
It contains Master contract, sub contract, sub-sub contract
pcode N(5)
pcode2 N(7)
pcode3 N(9)

Example
3770
3770, 377001
3770, 377001, 37700101
3770, 377001, 37700102
3770, 377002
3770, 377002, 37700201
3770, 377002, 37700202

When I do a select statement, I seem to get them in correct order with
select * from contract order by pcode,pcode2,pcode3 etc

But if I want to create an index, I lose my head.

Any pointers?

--
Kind regards,
Sytze de Boer
Kiss Software


--- StripMime Report -- processed MIME parts ---
multipart/alternative
   text/plain (text body -- kept)
   text/html
---


[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/53c6f715.7060...@san-dc.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index

[mbsoftwaresolutions]

I'm gonna make a wager and say "NO", and I know that's a large risky 
wager seeing how it's Ted's comment (and we all know he's an ace!).


Why not the compound index as you (Jeff) described?  I would have said 
the same thing.


--Mike


On 2014-07-16 17:23, Jeff Johnson wrote:

Ted:  So these indexes are created so that when you run that select
statement it will put them in order?

Interesting.

On 7/16/2014 1:56 PM, Ted Roche wrote:

Sytze:

I've been surprised to find everyone works with a "large amount of 
data"
but for some of us, that's thirty thousand records and for others it 
is

thirty million.


INDEX ON pcode TAG pcode
INDEX ON pcode2 TAG pcode2
INDEX ON pcode3 TAG pcode3
INDEX ON DELETED() TAG DELETED



On Wed, Jul 16, 2014 at 4:40 PM, Sytze de Boer  
wrote:



After yesterday, with heart in mouth, I ask the following

I have a table with large amount of data
It contains Master contract, sub contract, sub-sub contract
pcode N(5)
pcode2 N(7)
pcode3 N(9)

Example
3770
3770, 377001
3770, 377001, 37700101
3770, 377001, 37700102
3770, 377002
3770, 377002, 37700201
3770, 377002, 37700202

When I do a select statement, I seem to get them in correct order 
with

select * from contract order by pcode,pcode2,pcode3 etc

But if I want to create an index, I lose my head.

Any pointers?

--
Kind regards,
Sytze de Boer
Kiss Software


--- StripMime Report -- processed MIME parts ---
multipart/alternative
   text/plain (text body -- kept)
   text/html
---


[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/a70a5ffc3abfbee907501fa2dedba...@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index

[Stephen Russell]

On Wed, Jul 16, 2014 at 3:40 PM, Sytze de Boer  wrote:

> After yesterday, with heart in mouth, I ask the following
>
> I have a table with large amount of data
> It contains Master contract, sub contract, sub-sub contract
> pcode N(5)
> pcode2 N(7)
> pcode3 N(9)
>
> Example
> 3770
> 3770, 377001
> 3770, 377001, 37700101
> 3770, 377001, 37700102
> 3770, 377002
> 3770, 377002, 37700201
> 3770, 377002, 37700202
> -


This is just a flat file.  Why would you not normalize the data.

Table(s) Contract, SubContract, SubSubContract.

Then an index would be "fun".

In this instance you just need to combine all three columns into a single
index.  In reality you may only need to index pcode3 because it is all
relative data of the former two columns within.

What happens when you get subcontract 100?




-- 
Stephen Russell
Sr. Analyst
Ring Container Technology
Oakland TN

901.246-0159 cell


--- StripMime Report -- processed MIME parts ---
multipart/alternative
  text/plain (text body -- kept)
  text/html
---

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/cajidmyjjuggudrl25-zx_pt+b+w-x8_nmgd5k_oxmuo6zgk...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index

[Jeff Johnson]

Ted:  So these indexes are created so that when you run that select 
statement it will put them in order?


Interesting.

On 7/16/2014 1:56 PM, Ted Roche wrote:

Sytze:

I've been surprised to find everyone works with a "large amount of data"
but for some of us, that's thirty thousand records and for others it is
thirty million.


INDEX ON pcode TAG pcode
INDEX ON pcode2 TAG pcode2
INDEX ON pcode3 TAG pcode3
INDEX ON DELETED() TAG DELETED



On Wed, Jul 16, 2014 at 4:40 PM, Sytze de Boer  wrote:


After yesterday, with heart in mouth, I ask the following

I have a table with large amount of data
It contains Master contract, sub contract, sub-sub contract
pcode N(5)
pcode2 N(7)
pcode3 N(9)

Example
3770
3770, 377001
3770, 377001, 37700101
3770, 377001, 37700102
3770, 377002
3770, 377002, 37700201
3770, 377002, 37700202

When I do a select statement, I seem to get them in correct order with
select * from contract order by pcode,pcode2,pcode3 etc

But if I want to create an index, I lose my head.

Any pointers?

--
Kind regards,
Sytze de Boer
Kiss Software


--- StripMime Report -- processed MIME parts ---
multipart/alternative
   text/plain (text body -- kept)
   text/html
---


[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/53c6ed66.8040...@san-dc.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index

[Fernando D. Bozzo]

The index for DELETED() is only justifiable if deleted records are more
than 20% of the total records of the DBF.

Not always the "full optimize" is the best option, and sometimes it takes
more time than without this index.


Best regards.-



2014-07-16 22:56 GMT+02:00 Ted Roche :

> Sytze:
>
> I've been surprised to find everyone works with a "large amount of data"
> but for some of us, that's thirty thousand records and for others it is
> thirty million.
>
>
> INDEX ON pcode TAG pcode
> INDEX ON pcode2 TAG pcode2
> INDEX ON pcode3 TAG pcode3
> INDEX ON DELETED() TAG DELETED
>
>


--- StripMime Report -- processed MIME parts ---
multipart/alternative
  text/plain (text body -- kept)
  text/html
---

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/CAGQ_JukkVCS-knD+hD35XMo0GvMP2sB28UQv8aEA_hL-dE=k...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index

[Ted Roche]

Sytze:

I've been surprised to find everyone works with a "large amount of data"
but for some of us, that's thirty thousand records and for others it is
thirty million.


INDEX ON pcode TAG pcode
INDEX ON pcode2 TAG pcode2
INDEX ON pcode3 TAG pcode3
INDEX ON DELETED() TAG DELETED



On Wed, Jul 16, 2014 at 4:40 PM, Sytze de Boer  wrote:

> After yesterday, with heart in mouth, I ask the following
>
> I have a table with large amount of data
> It contains Master contract, sub contract, sub-sub contract
> pcode N(5)
> pcode2 N(7)
> pcode3 N(9)
>
> Example
> 3770
> 3770, 377001
> 3770, 377001, 37700101
> 3770, 377001, 37700102
> 3770, 377002
> 3770, 377002, 37700201
> 3770, 377002, 37700202
>
> When I do a select statement, I seem to get them in correct order with
> select * from contract order by pcode,pcode2,pcode3 etc
>
> But if I want to create an index, I lose my head.
>
> Any pointers?
>
> --
> Kind regards,
> Sytze de Boer
> Kiss Software
>
>
> --- StripMime Report -- processed MIME parts ---
> multipart/alternative
>   text/plain (text body -- kept)
>   text/html
> ---
>
[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/CACW6n4stwMw=bVUV7fTD=kF4LpMPZ5b2pxXAtun8VyyS=5b...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index

[Jeff Johnson]

On 7/16/2014 1:40 PM, Sytze de Boer wrote:

After yesterday, with heart in mouth, I ask the following

I have a table with large amount of data
It contains Master contract, sub contract, sub-sub contract
pcode N(5)
pcode2 N(7)
pcode3 N(9)

Example
3770
3770, 377001
3770, 377001, 37700101
3770, 377001, 37700102
3770, 377002
3770, 377002, 37700201
3770, 377002, 37700202

When I do a select statement, I seem to get them in correct order with
select * from contract order by pcode,pcode2,pcode3 etc

But if I want to create an index, I lose my head.

Any pointers?



how about index on str(pcode, 5, 0) + str(pcode2, 7, 0) + str(pcode3, 9, 
0) [to myindex.idx or tag mytag]


HTH

--
Jeff

Jeff Johnson
j...@san-dc.com
SanDC, Inc.
623-582-0323
Fax 623-869-0675

http://www.san-dc.com
 



___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/53c6e4f5.3090...@san-dc.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index On failing silently

[Ted Roche]

On Fri, Aug 30, 2013 at 10:00 AM, Paul Newton wrote:

> Thanks for your suggestions Ted.
>
> We have a readwrite cursor (unbuffered) and we are trying to create the
> index from scratch, i.e. at the point that the code runs there is no CDX.
>  This code is running in a DLL
>

Well, sure, that's probably why it's not working.

What's the current directory set to? DLLs often start by default in some
strange place like C:\Windows\System32. Of course, your app probably
doesn't have write-rights there.



-- 
Ted Roche
Ted Roche & Associates, LLC
http://www.tedroche.com


--- StripMime Report -- processed MIME parts ---
multipart/alternative
  text/plain (text body -- kept)
  text/html
---

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/CACW6n4vkFN7TV3MwiST3drkLLwGDFgrER50=vrsndykb+gs...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index On failing silently

[Paul Newton]

This code is actually in a loop which creates index tags for several 
tables/cursors.  It is working for most of the tables but is failing on just 
one or two ...  they are all in the same location 

-Original Message-
From: ProfoxTech [mailto:profoxtech-boun...@leafe.com] On Behalf Of Ted Roche
Sent: 30 August 2013 15:05
To: profoxt...@leafe.com
Subject: Re: Index On failing silently

On Fri, Aug 30, 2013 at 10:00 AM, Paul Newton wrote:

> Thanks for your suggestions Ted.
>
> We have a readwrite cursor (unbuffered) and we are trying to create 
> the index from scratch, i.e. at the point that the code runs there is no CDX.
>  This code is running in a DLL
>

Well, sure, that's probably why it's not working.

What's the current directory set to? DLLs often start by default in some 
strange place like C:\Windows\System32. Of course, your app probably doesn't 
have write-rights there.



--
Ted Roche
Ted Roche & Associates, LLC
http://www.tedroche.com


--- StripMime Report -- processed MIME parts ---
multipart/alternative
  text/plain (text body -- kept)
  text/html
---

[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/9a8106afb0ef9b44b69045c4448935a2014ff...@ukfawexmbx2.infor.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index On failing silently

[Paul Newton]

Thanks for your suggestions Ted.

We have a readwrite cursor (unbuffered) and we are trying to create the index 
from scratch, i.e. at the point that the code runs there is no CDX.  This code 
is running in a DLL

Paul

-Original Message-
From: ProfoxTech [mailto:profoxtech-boun...@leafe.com] On Behalf Of Ted Roche
Sent: 30 August 2013 14:53
To: profoxt...@leafe.com
Subject: Re: Index On failing silently

On Fri, Aug 30, 2013 at 7:14 AM, Paul Newton wrote:

> I have a situation where the following command fails to create the 
> specified index tag (and no error is reported)
>
> Index On wu_name Tag wctrl1
>
> I am completely stumped so if anybody has any suggestions I would be 
> most grateful
>

Insufficient description for a lot of good guesses. Is this from the command 
line or within an application? Get everyone else out of the system, turn it off 
and back on again, restore from backup if possible.

It's likely a corrupted index or table header. First try the index: Make sure 
you have a backup, of course, and take a fresh one for forensics later. Make 
sure that you know what all of the tags in the CDX are (the INDEX ON argument 
as well as any FOR or unusual clauses like UNIQUE). Also, any declared 
relations as they can disappear when the tags are removed.
Completely remove the CDX and build it fresh.

--
Ted Roche
Ted Roche & Associates, LLC
http://www.tedroche.com


--- StripMime Report -- processed MIME parts ---
multipart/alternative
  text/plain (text body -- kept)
  text/html
---

[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/9a8106afb0ef9b44b69045c4448935a2014ff...@ukfawexmbx2.infor.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index On failing silently

[Paul Newton]

There is an error handler in place but it is not being called

-Original Message-
From: ProfoxTech [mailto:profoxtech-boun...@leafe.com] On Behalf Of Jean 
Laeremans
Sent: 30 August 2013 14:45
To: profoxt...@leafe.com
Subject: Re: Index On failing silently

No active 'on error' routine ?

A+
jml


--- StripMime Report -- processed MIME parts --- multipart/alternative
  text/plain (text body -- kept)
  text/html
---

[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/9a8106afb0ef9b44b69045c4448935a2014ff...@ukfawexmbx2.infor.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index On failing silently

[Ted Roche]

On Fri, Aug 30, 2013 at 7:14 AM, Paul Newton wrote:

> I have a situation where the following command fails to create the
> specified index tag (and no error is reported)
>
> Index On wu_name Tag wctrl1
>
> I am completely stumped so if anybody has any suggestions I would be most
> grateful
>

Insufficient description for a lot of good guesses. Is this from the
command line or within an application? Get everyone else out of the system,
turn it off and back on again, restore from backup if possible.

It's likely a corrupted index or table header. First try the index: Make
sure you have a backup, of course, and take a fresh one for forensics
later. Make sure that you know what all of the tags in the CDX are (the
INDEX ON argument as well as any FOR or unusual clauses like UNIQUE). Also,
any declared relations as they can disappear when the tags are removed.
Completely remove the CDX and build it fresh.

-- 
Ted Roche
Ted Roche & Associates, LLC
http://www.tedroche.com


--- StripMime Report -- processed MIME parts ---
multipart/alternative
  text/plain (text body -- kept)
  text/html
---

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/CACW6n4v+DCP=7ruy7j3eswwxxc2hw7amcrueff0wc-sggqf...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index On failing silently

[Jean Laeremans]

No active 'on error' routine ?

A+
jml


--- StripMime Report -- processed MIME parts ---
multipart/alternative
  text/plain (text body -- kept)
  text/html
---

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/capqlobxqpouwzpd_aptw-eqckpkspglevcbsbd0p-hcgqak...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index On failing silently

[Paul Newton]

Thanks for your reply Tracy.
Yes
Yes
No
No
Paul

-Original Message-
From: ProfoxTech [mailto:profoxtech-boun...@leafe.com] On Behalf Of Tracy 
Pearson
Sent: 30 August 2013 14:27
To: profoxt...@leafe.com
Subject: RE: Index On failing silently

Paul Newton wrote on 2013-08-30: 
>  I have a situation where the following command fails to create the
specified index tag (and no error is reported)
>  
>  Index On wu_name Tag wctrl1
>  
>  I am completely stumped so if anybody has any suggestions I would be 
> most
grateful
>  
>  Paul Newton
>  

Paul,

Is the correct cursor/table active? 
If it is a SELECT INTO CURSOR do you have NOFILTER or READWRITE clause on the 
select?
Do you have on demand backup?
Do you have anti-virus without exclusion to the DBF/CDX/FPT extensions?


Tracy Pearson
PowerChurch Software


[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/9a8106afb0ef9b44b69045c4448935a2014fe...@ukfawexmbx2.infor.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index On failing silently

[Tracy Pearson]

Paul Newton wrote on 2013-08-30: 
>  I have a situation where the following command fails to create the
specified index tag (and no error is reported)
>  
>  Index On wu_name Tag wctrl1
>  
>  I am completely stumped so if anybody has any suggestions I would be most
grateful
>  
>  Paul Newton
>  

Paul,

Is the correct cursor/table active? 
If it is a SELECT INTO CURSOR do you have NOFILTER or READWRITE clause on
the select?
Do you have on demand backup?
Do you have anti-virus without exclusion to the DBF/CDX/FPT extensions?


Tracy Pearson
PowerChurch Software


___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/000601cea584$8b302f20$a1908d60$@powerchurch.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index On failing silently

[Peter Cushing]

Paul Newton wrote:

I have a situation where the following command fails to create the specified 
index tag (and no error is reported)

Index On wu_name Tag wctrl1

I am completely stumped so if anybody has any suggestions I would be most 
grateful


  
Is the file opened exclusively?  It may ignore the command it it cannot 
lock the file to execute it.


Peter





Rajan Imports has changed - we are now Whispering Smith Ltd.  For more 
information see our website at www.whisperingsmith.com


Please update your address book with my new email address: 
pcush...@whisperingsmith.com

.

This communication is intended for the person or organisation to whom it is addressed.  The contents are confidential and may be protected in law.  Unauthorised use, copying or 
disclosure of any of it may be unlawful.  If you have received this message in error, please notify us immediately by telephone or email.


www.whisperingsmith.com

Whispering Smith Ltd
Head Office:61 Great Ducie Street, Manchester M3 1RR. Tel:0161 831 3700 
Fax:0161 831 3715
London Office:17-19 Foley Street, London  W1W 6DW Tel:0207 299 7960


___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/5220814d.20...@whisperingsmith.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index does not match database

[Jeff Johnson]

Sytze:  I agree with everything you've said, but once it does not match 
it continues not to match until you reindex or re-create like you 
mentioned before.  This one did not match and then seconds later, 
matched.  I have only seen this when copying files but there is no copy 
function going on.


Jeff

---

Jeff Johnson
j...@san-dc.com
(623) 582-0323

www.san-dc.com
www.arelationshipmanager.com

On 02/14/2013 12:23 PM, Sytze de Boer wrote:

Delete the CDX and issue the "index on" command

I have seen this on a number of occasions where I have overwritten the
DBF's with another set of DBF's without also over-writing the CDX's
The issue of "Index does not match database" may not be immediately
apparent to the system.
My system traps this error number (114) and automatically deletes the CDX
and does the "index on" routine





On Fri, Feb 15, 2013 at 8:03 AM, Jeff Johnson  wrote:


Yep


Jeff

---

Jeff Johnson
j...@san-dc.com
(623) 582-0323

www.san-dc.com
www.arelationshipmanager.com

On 02/14/2013 11:43 AM, MB Software Solutions, LLC wrote:


On 2/14/2013 1:23 PM, Jeff Johnson wrote:


My curdir() is the folder the table is in.

Jeff



You've got a CDX and not the older IDX, right?




__**_
Post Messages to: ProFox@leafe.com
Subscription Maintenance: 
http://mail.leafe.com/mailman/**listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/**
listinfo/profoxtech 
Searchable Archive: 
http://leafe.com/archives/**search/profox
This message: http://leafe.com/archives/**byMID/profox/511D351C.9080403@**
san-dc.com

** All postings, unless explicitly stated otherwise, are the opinions of
the author, and do not constitute legal or medical advice. This statement
is added to the messages for those lawyers who are too stupid to see the
obvious.

Report [OT] Abuse: http://leafe.com/reportAbuse/**
511d351c.9080...@san-dc.com







___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/511d3b18.8020...@san-dc.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index does not match database

[Sytze de Boer]

Delete the CDX and issue the "index on" command

I have seen this on a number of occasions where I have overwritten the
DBF's with another set of DBF's without also over-writing the CDX's
The issue of "Index does not match database" may not be immediately
apparent to the system.
My system traps this error number (114) and automatically deletes the CDX
and does the "index on" routine





On Fri, Feb 15, 2013 at 8:03 AM, Jeff Johnson  wrote:

> Yep
>
>
> Jeff
>
> ---
>
> Jeff Johnson
> j...@san-dc.com
> (623) 582-0323
>
> www.san-dc.com
> www.arelationshipmanager.com
>
> On 02/14/2013 11:43 AM, MB Software Solutions, LLC wrote:
>
>> On 2/14/2013 1:23 PM, Jeff Johnson wrote:
>>
>>> My curdir() is the folder the table is in.
>>>
>>> Jeff
>>>
>>
>>
>> You've got a CDX and not the older IDX, right?
>>
>>
>>
>
> __**_
> Post Messages to: ProFox@leafe.com
> Subscription Maintenance: 
> http://mail.leafe.com/mailman/**listinfo/profox
> OT-free version of this list: http://mail.leafe.com/mailman/**
> listinfo/profoxtech 
> Searchable Archive: 
> http://leafe.com/archives/**search/profox
> This message: http://leafe.com/archives/**byMID/profox/511D351C.9080403@**
> san-dc.com
>
> ** All postings, unless explicitly stated otherwise, are the opinions of
> the author, and do not constitute legal or medical advice. This statement
> is added to the messages for those lawyers who are too stupid to see the
> obvious.
>
> Report [OT] Abuse: http://leafe.com/reportAbuse/**
> 511d351c.9080...@san-dc.com
>



-- 
Kind regards
Sytze de Boer
Kiss Software


--- StripMime Report -- processed MIME parts ---
multipart/alternative
  text/plain (text body -- kept)
  text/html
---

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/CAG1nNy9Ab1tm-LV=vyz7fbdmzjczpgsc3hox1v5mx+jwic6...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index does not match database

[Jeff Johnson]

Yep

Jeff

---

Jeff Johnson
j...@san-dc.com
(623) 582-0323

www.san-dc.com
www.arelationshipmanager.com

On 02/14/2013 11:43 AM, MB Software Solutions, LLC wrote:

On 2/14/2013 1:23 PM, Jeff Johnson wrote:

My curdir() is the folder the table is in.

Jeff



You've got a CDX and not the older IDX, right?





___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/511d351c.9080...@san-dc.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index does not match database

[MB Software Solutions, LLC]

On 2/14/2013 1:23 PM, Jeff Johnson wrote:

My curdir() is the folder the table is in.

Jeff



You've got a CDX and not the older IDX, right?


--
Mike Babcock, MCP
MB Software Solutions, LLC
President, Chief Software Architect
http://mbsoftwaresolutions.com
http://fabmate.com
http://twitter.com/mbabcock16

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/511d3049.6000...@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index does not match database

[Jeff Johnson]

My curdir() is the folder the table is in.

Jeff

---

Jeff Johnson
j...@san-dc.com
(623) 582-0323

www.san-dc.com
www.arelationshipmanager.com

On 02/14/2013 11:17 AM, Richard Kaye wrote:

Pathing? Possibly picking up a similarly named index from the wrong place the 
first time?

--
rk

-Original Message-
From: ProfoxTech [mailto:profoxtech-boun...@leafe.com] On Behalf Of Jeff Johnson
Sent: Thursday, February 14, 2013 1:14 PM
To: profoxt...@leafe.com
Subject: Index does not match database

I am working on a Web Connect application that has an unusual problem.
When browsing a file I received an "Index does not match database" which kicks 
me out of the browse.  When I reissue the browse it is fine.  The unusual problem could 
definitely be related to a mismatched index.

I have never seen it not match and then immediately match right after that.

Anybody know what could cause that?

--
Jeff



[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/511d2b90.6010...@san-dc.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index does not match database

[Richard Kaye]

Pathing? Possibly picking up a similarly named index from the wrong place the 
first time?

--
rk

-Original Message-
From: ProfoxTech [mailto:profoxtech-boun...@leafe.com] On Behalf Of Jeff Johnson
Sent: Thursday, February 14, 2013 1:14 PM
To: profoxt...@leafe.com
Subject: Index does not match database

I am working on a Web Connect application that has an unusual problem.  
When browsing a file I received an "Index does not match database" which kicks 
me out of the browse.  When I reissue the browse it is fine.  The unusual 
problem could definitely be related to a mismatched index.

I have never seen it not match and then immediately match right after that.

Anybody know what could cause that?

--
Jeff


___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/DF1EEF11E586A64FB54A97F22A8BD0442181EE0C6B@ACKBWDDQH1.artfact.local
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index and DBF corruption in SMB2 environments.

[Tracy Pearson]

Alan Bourke wrote on 2012-11-08: 
>  I turned up this Microsoft Article a while back:
>  
>  "Data corruption when one user on a computer that is running Windows 7
>  or Windows Server 2008 R2 updates a shared file that is open on multiple
>  computers by using SMB Version 2
>  Article ID: 2618096"
>  
>  It gives details on obtaining supported hotfixes for Windows 7, Vista
>  and Windows Server 2008 to address this. These hotfixes are not included
>  in any current service packs, nor are they installed via Windows Update,
>  as far as I know. I would imagine the next Service Packs for the OS's in
>  question will include the fix.
>  
>  It *appears* to have largely sorted a persistent index corruption issue
>  on one site for us.
>  
>  Article 2028965 may also be of interest.
> 
>

Alan,

Thank you for sharing. I'll keep this in mind for our customers.

Tracy Pearson
PowerChurch Software


___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/000701cdbdd1$c4b2d040$4e1870c0$@powerchurch.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index and DBF corruption in SMB2 environments.

[MB Software Solutions, LLC]

On 11/8/2012 9:29 AM, Alan Bourke wrote:

I turned up this Microsoft Article a while back:

"Data corruption when one user on a computer that is running Windows 7
or Windows Server 2008 R2 updates a shared file that is open on multiple
computers by using SMB Version 2
Article ID: 2618096"

It gives details on obtaining supported hotfixes for Windows 7, Vista
and Windows Server 2008 to address this. These hotfixes are not included
in any current service packs, nor are they installed via Windows Update,
as far as I know. I would imagine the next Service Packs for the OS's in
question will include the fix.

It *appears* to have largely sorted a persistent index corruption issue
on one site for us.

Article 2028965 may also be of interest.






So glad I don't use DBFs in most of my deployed apps any more.  One 
still does and I'd love to retool/rewrite it to use MySQL instead.  Oh 
to find the time.


--
Mike Babcock, MCP
MB Software Solutions, LLC
President, Chief Software Architect
http://mbsoftwaresolutions.com
http://fabmate.com
http://twitter.com/mbabcock16

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: 
http://leafe.com/archives/byMID/profox/509be1af.7060...@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: index column Unique or empty

[Stephen Russell]

On Tue, Nov 11, 2008 at 7:40 AM, Ajoy Khaund <[EMAIL PROTECTED]> wrote:
> Dave Thanks and also to Maurice.
>
> This field is not a very important field. In a employee table permanent
> workers have a Provident Fund Number which the temporary guys don't have.
> Now I have to have check when saving the record to confirm the number does
> not exist.
>
> Where would you put a check normally.
--

I would validate that # before I did the row insert.  Is there a high
velocity on inserts to this table?  If so create a trigger for insert
to gen it at insert.
-- 
Stephen Russell
Sr. Production Systems Programmer
Mimeo.com
Memphis TN

901.246-0159


___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: index column Unique or empty

[Ajoy Khaund]

Dave Thanks and also to Maurice.

This field is not a very important field. In a employee table permanent 
workers have a Provident Fund Number which the temporary guys don't have. 
Now I have to have check when saving the record to confirm the number does 
not exist.

Where would you put a check normally.


Ajoy Khaund
Neamati Road
Near Bhogdoi Bridge
Jorhat 785001-21
Assam, India

Tel: 91-376-2351288
Cell: 91-94350-92287
Mail: [EMAIL PROTECTED]
Mail: [EMAIL PROTECTED]

"Walking on water and developing software from a specification are easy if
both are frozen."
- Edward  V. Berard, "Life-Cycle Approaches"

--
From: "Dave Crozier" <[EMAIL PROTECTED]>
Sent: Tuesday, November 11, 2008 1:27 PM
To: <[EMAIL PROTECTED]>
Subject: RE: index column Unique or empty

> Ajoy,
> Your requirement breaks all the standard Database rules. Either the index
> should be unique or not. The constraint of being unique is there for a
> reason - else why have it.
>
> You are better off leaving the unique field as it is and always filling it
> in, then adding a sub field, for example lInclude (Boolean).
>
> Field Name Type Length
> Unique_Key C 5
> lInclude Logical 1
>
> Next create a new composite index based upon the two fields using long 
> hand
> or a UDF which will convert the Logical field to a character expression:
>
> Index on iif(lInclude, "1","0")+Unique_Key to tag Live
> Note that personal preference may dictate that you swap the order of the
> index so that we postfix the unique key as to prefixing it.
>
> Then all you have to do is always prefix/postfix your key with "1" if you
> need to find a valid unique key before you do the search for normal
> live/included record searches.
>
> Personally I prefer the Prefix option as it easily allows you to find all
> "non included" records.
>
> Also you can switch a record's Included/Excluded status very easily 
> without
> having to change the main key. Obviously if you do it this way then either
> the Unique_Key could become the Primary Key by definition or you could use
> the existing primary Key if you have one.
>
> Jean Maurice's idea about using a filtered index is OK but you won't be 
> able
> to use Rushmore to optimize your index access on a filtered index.
>
> Remember that database rules regarding uniqueness are there for a reason,
> but only when the database has been properly designed.
>
> Dave Crozier
>
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
> Of Ajoy Khaund
> Sent: 11 November 2008 06:44
> To: Profox
> Subject: index column Unique or empty
>
>
> Hi
>
> I have field which can be empty else it must be unique. Can I create such 
> a
> index which will throw an error.
>
> TIA
>
> Ajoy Khaund
> Neamati Road
> Near Bhogdoi Bridge
> Jorhat 785001-21
> Assam, India
>
> Tel: 91-376-2351288
> Cell: 91-94350-92287
> Mail: [EMAIL PROTECTED]
> Mail: [EMAIL PROTECTED]
>
> "Walking on water and developing software from a specification are easy if
> both are frozen."
> - Edward  V. Berard, "Life-Cycle Approaches"
>
> --- StripMime Report -- processed MIME parts ---
> multipart/alternative
>  text/plain (text body -- kept)
>  text/html
> ---
>
[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: index column Unique or empty

[Dave Crozier]

Ajoy,
Your requirement breaks all the standard Database rules. Either the index
should be unique or not. The constraint of being unique is there for a
reason - else why have it. 

You are better off leaving the unique field as it is and always filling it
in, then adding a sub field, for example lInclude (Boolean).

Field Name  TypeLength
Unique_Key  C   5
lIncludeLogical 1

Next create a new composite index based upon the two fields using long hand
or a UDF which will convert the Logical field to a character expression:

Index on iif(lInclude, "1","0")+Unique_Key to tag Live
Note that personal preference may dictate that you swap the order of the
index so that we postfix the unique key as to prefixing it.

Then all you have to do is always prefix/postfix your key with "1" if you
need to find a valid unique key before you do the search for normal
live/included record searches.  

Personally I prefer the Prefix option as it easily allows you to find all
"non included" records.

Also you can switch a record's Included/Excluded status very easily without
having to change the main key. Obviously if you do it this way then either
the Unique_Key could become the Primary Key by definition or you could use
the existing primary Key if you have one.

Jean Maurice's idea about using a filtered index is OK but you won't be able
to use Rushmore to optimize your index access on a filtered index.

Remember that database rules regarding uniqueness are there for a reason,
but only when the database has been properly designed.

Dave Crozier

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Ajoy Khaund
Sent: 11 November 2008 06:44
To: Profox
Subject: index column Unique or empty


Hi

I have field which can be empty else it must be unique. Can I create such a
index which will throw an error.

TIA

Ajoy Khaund
Neamati Road
Near Bhogdoi Bridge
Jorhat 785001-21
Assam, India

Tel: 91-376-2351288
Cell: 91-94350-92287
Mail: [EMAIL PROTECTED]
Mail: [EMAIL PROTECTED]

"Walking on water and developing software from a specification are easy if
both are frozen."
- Edward  V. Berard, "Life-Cycle Approaches"

--- StripMime Report -- processed MIME parts ---
multipart/alternative
  text/plain (text body -- kept)
  text/html
---

[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: index column Unique or empty

[Jean MAURICE]

something like
INDEX ON yourfield CANDIDATE FOR !EMPTY(yourfield)
?

> I have field which can be empty else it must be unique. Can I create such a 
> index which will throw an error.
>
> Ajoy Khaund
>   
>
-- 
Jean MAURICE
Grenoble - France - Europe
www.atoutfox.org
www.aedtf.org


___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index on Deleted()

[Lee Lorentz]

Just a shot in the dark here.

The first argument you're sending into the SEEK() function is the value
sought.  You should be sending in a value of .T. but you're sending in the
DELETED() status of the current record in the currently-selected workarea.

Try:

? SEEK( .T., 'MyTable', 'DelRecs' )

 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
Behalf Of John Weller
Sent: Wednesday, February 14, 2007 16:06
To: [EMAIL PROTECTED]
Subject: Index on Deleted()

I have an index on DELETED() in most of my tables.  To check if a table
needs packing I look for a deleted record using this index.  When I look at
a table with no deleted records I'm finding that the line:

LOCATE FOR DELETED()
?FOUND()

returns False, whereas

? SEEK(DELETED(), 'MyTable', 'DelRecs')

is returning True - which is obviously wrong!  This happens on all of the
tables which do not have deleted records.  Any suggestions as to what I am
doing wrong?  (I'm going to change the code to use LOCATE rather than SEEK
as an interim measure).

TIA


John Weller
01380 723235
07976 393631



[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index on Deleted()

[Ted Roche]

On 2/15/07, John Weller <[EMAIL PROTECTED]> wrote:

> I can only plead old age, poverty and temporary insanity.

Boy, can I relate! Glad I could help.

-- 
Ted Roche
Ted Roche & Associates, LLC
http://www.tedroche.com


___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index on Deleted()

[MB Software Solutions]

Paul Hill wrote:
> On 2/15/07, Michael Madigan <[EMAIL PROTECTED]> wrote:
>   
>> On a related note.
>>
>> Is it still not a good idea to index on deleted() due
>> to performance issues over a network?
>> 
>
> I would agree.  The new VFP9 bit indexes should help though.
>
>   
True.  And as Ed/Ted/others have said before, this is something where 
the answer is "it depends."  You'll have to test, test, test!

-- 
Michael J. Babcock, MCP
MB Software Solutions, LLC
http://mbsoftwaresolutions.com
http://fabmate.com
"Work smarter, not harder, with MBSS custom software solutions!"



___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index on Deleted()

[John Weller]

Hadn't thought of that - I'll investigate.  Thanks Dave.

The purpose of the index is just to quickly find if a table needs packing as
there are functions which automatically delete records if the content falls
below a specified level.

John Weller
01380 723235
07976 393631

>
> John and others,
> Why not use the "for deleted()" expression in the index to create
> a filtered
> index.
>
> That way you get the smallest index size as one would assume that
> the number
> of deleted records are always minimal in comparison to the main "live"
> records and maximum performance which far outweighs the inability to use
> Rushmore in filtered indexes.
>
> I have used this method for many years with no problems. When you need to
> find a deleted record the index data transferred is minimal over
> the Lan and
> the index is not bloated from useless live records.
>
> After all the reason for having a deleted index is only to find deleted
> records to re-use.
>



___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index on Deleted()

[John Weller]

Thanks Vince.  See my response to Ted for my plea.

John Weller
01380 723235
07976 393631 
> >   
> I'm going to hem and haw my way carefully through this, so I don't look 
> much more ignorant then most people already think I am.
> I can't answer the question, but maybe this will get someone a bit 
> closer to it.
> 
> IIRC, Deleted() returns a .T. for the current record.  Since you have no 
> deleted records, you are doing a Seek(.F.)
> 
> With an index on Deleted():
> with all records non-deleted, if you literally do a Seek(.F.), it always 
> returns .T., and a Seek .T. always returns .F.
> with all records deleted, if you literally do a Seek(.F.), it always 
> returns .F., and a Seek .T. always returns .T.
> With a mix, both always return .T.
> 
> If you reverse your index, though ( "Index on !Deleted()" etc ), 
> everything works exactly as you would expect, including  your 
> Seek(Deleted())
> 
>
> 


___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index on Deleted()

[John Weller]

You are absolutely right

I can only plead old age, poverty and temporary insanity.

Thanks.

John Weller
01380 723235
07976 393631

> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of Ted Roche
> Sent: 14 February 2007 22:41
> To: [EMAIL PROTECTED]
> Subject: Re: Index on Deleted()
>
>
> On 2/14/07, John Weller <[EMAIL PROTECTED]> wrote:
> >
> > ? SEEK(DELETED(), 'MyTable', 'DelRecs')
> >
> > is returning True - which is obviously wrong!
>
> Isn't the first argument the value you are supposed to be looking for?
> If so, it should be .T. shouldn't it?
>
> With your argument, it's evaluating DELETED() for the current record,
> likely .F., and then finding that in the table.
>
>
> --
> Ted Roche
> Ted Roche & Associates, LLC
> http://www.tedroche.com
>
>
[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index on Deleted()

[Dave Crozier]

John and others,
Why not use the "for deleted()" expression in the index to create a filtered
index. 

That way you get the smallest index size as one would assume that the number
of deleted records are always minimal in comparison to the main "live"
records and maximum performance which far outweighs the inability to use
Rushmore in filtered indexes.

I have used this method for many years with no problems. When you need to
find a deleted record the index data transferred is minimal over the Lan and
the index is not bloated from useless live records.

After all the reason for having a deleted index is only to find deleted
records to re-use.

Obviously the efficiency of this method drops the higher percentage of
deleted records are present but I normally keep this to less than 5% if
possible and reuse records whenever possible.

Dave Crozier


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Paul Hill
Sent: 15 February 2007 09:12
To: ProFox Email List
Subject: Re: Index on Deleted()

On 2/15/07, Michael Madigan <[EMAIL PROTECTED]> wrote:
> On a related note.
>
> Is it still not a good idea to index on deleted() due
> to performance issues over a network?

I would agree.  The new VFP9 bit indexes should help though.

-- 
Paul


[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index on Deleted()

[Paul Hill]

On 2/15/07, Michael Madigan <[EMAIL PROTECTED]> wrote:
> On a related note.
>
> Is it still not a good idea to index on deleted() due
> to performance issues over a network?

I would agree.  The new VFP9 bit indexes should help though.

-- 
Paul


___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index on Deleted()

[Michael Madigan]

On a related note.

Is it still not a good idea to index on deleted() due
to performance issues over a network?





___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index on Deleted()

[Vince Teachout]

John Weller wrote:
> I have an index on DELETED() in most of my tables.  To check if a table
> needs packing I look for a deleted record using this index.  When I look at
> a table with no deleted records I'm finding that the line:
>
> LOCATE FOR DELETED()
> ?FOUND()
>
> returns False, whereas
>
> ? SEEK(DELETED(), 'MyTable', 'DelRecs')
>
> is returning True - which is obviously wrong!  This happens on all of the
> tables which do not have deleted records.  Any suggestions as to what I am
> doing wrong?  (I'm going to change the code to use LOCATE rather than SEEK
> as an interim measure).
>   
I'm going to hem and haw my way carefully through this, so I don't look 
much more ignorant then most people already think I am.
I can't answer the question, but maybe this will get someone a bit 
closer to it.

IIRC, Deleted() returns a .T. for the current record.  Since you have no 
deleted records, you are doing a Seek(.F.)

With an index on Deleted():
with all records non-deleted, if you literally do a Seek(.F.), it always 
returns .T., and a Seek .T. always returns .F.
with all records deleted, if you literally do a Seek(.F.), it always 
returns .F., and a Seek .T. always returns .T.
With a mix, both always return .T.

If you reverse your index, though ( "Index on !Deleted()" etc ), 
everything works exactly as you would expect, including  your 
Seek(Deleted())

I dunno.


___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index on Deleted()

[Ted Roche]

On 2/14/07, John Weller <[EMAIL PROTECTED]> wrote:
>
> ? SEEK(DELETED(), 'MyTable', 'DelRecs')
>
> is returning True - which is obviously wrong!

Isn't the first argument the value you are supposed to be looking for?
If so, it should be .T. shouldn't it?

With your argument, it's evaluating DELETED() for the current record,
likely .F., and then finding that in the table.


-- 
Ted Roche
Ted Roche & Associates, LLC
http://www.tedroche.com


___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index on collector tag collector unique

[Stephen the Cook]

Michael Madigan <> wrote:
> Yep, you're right, I should have a collector database.
>  This was a band-aid added quick and dirty.  Turned out to be too
> dirty. 

Why not create it via a select distinct into a table and then just normalize
your data?  Probably a lot of work after you hit all your input forms as
well as reports.

Stephen Russell
DBA / .Net Developer

Memphis TN 38115
901.246-0159

"Our scientific power has outrun our spiritual power. We have guided
missiles and misguided men." Dr. Martin Luther King Jr.

http://spaces.msn.com/members/srussell/

-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.432 / Virus Database: 268.16.14/636 - Release Date: 1/18/2007
4:00 AM
 



___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index on collector tag collector unique

[Michael Madigan]

Yep, you're right, I should have a collector database.
 This was a band-aid added quick and dirty.  Turned
out to be too dirty.



--- Eugene Vital <[EMAIL PROTECTED]> wrote:

> Michael Madigan wrote:
> > OK, so I have a client database and on that
> database I
> > have a collector ID.  I have a tag in the compound
> > index that is created by 
> >
> > "index on collector tag collector unique".  
> >
> > This gives me a list of unique collectors using
> the
> > system, each one assigned to one or more clients.
> >
> > I have a collector 5.  If I happen to delete the
> > record which is in the unique index, there no
> longer
> > are any more '5's in the index.  Shouldn't it add
> > another record with a collector='5' to that index?
> >
> > Am I understanding the way unique works?
> >   
> Yes but I would follow some of the other posts here
> and use candidate
> instead.
> 
> This does make me ask the question. Wouldn't you be
> better off having
> the collectors in their own parent table and just
> relate them into
> the customer table?
> 
> then you could just use a query.
> 
> 
> select distinct customer.coll_id,
> collector.coll_name ;
>from customer ;
>left outer join collector on customer.coll_id ==
> collector.col_id ;
>where customer.coll_id >0
> 
> you could also easily get a count for each collector
> too
> 
> select distinct customer.coll_id,
> collector.coll_name, count(*) as
> coll_count ;
>from customer ;
>left outer join collector on customer.coll_id ==
> collector.col_id ;
>where customer.coll_id >0
> 
> 
> Of course normal indexing rules would apply to
> optimize the query...
> 
> just a thought
> 
> 
> 
> ___
> Post Messages to: ProFox@leafe.com
> Subscription Maintenance:
> http://leafe.com/mailman/listinfo/profox
> OT-free version of this list:
> http://leafe.com/mailman/listinfo/profoxtech
> ** All postings, unless explicitly stated otherwise,
> are the opinions of the author, and do not
> constitute legal or medical advice. This statement
> is added to the messages for those lawyers who are
> too stupid to see the obvious.
> 



___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index on collector tag collector unique

[Eugene Vital]

Michael Madigan wrote:
> OK, so I have a client database and on that database I
> have a collector ID.  I have a tag in the compound
> index that is created by 
>
> "index on collector tag collector unique".  
>
> This gives me a list of unique collectors using the
> system, each one assigned to one or more clients.
>
> I have a collector 5.  If I happen to delete the
> record which is in the unique index, there no longer
> are any more '5's in the index.  Shouldn't it add
> another record with a collector='5' to that index?
>
> Am I understanding the way unique works?
>   
Yes but I would follow some of the other posts here and use candidate
instead.

This does make me ask the question. Wouldn't you be better off having
the collectors in their own parent table and just relate them into
the customer table?

then you could just use a query.


select distinct customer.coll_id, collector.coll_name ;
   from customer ;
   left outer join collector on customer.coll_id == collector.col_id ;
   where customer.coll_id >0

you could also easily get a count for each collector too

select distinct customer.coll_id, collector.coll_name, count(*) as
coll_count ;
   from customer ;
   left outer join collector on customer.coll_id == collector.col_id ;
   where customer.coll_id >0


Of course normal indexing rules would apply to optimize the query...

just a thought



___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index on collector tag collector unique

[Rick Schummer]

It is a type of index for VFP data, which enforces uniqueness and does not 
accept nulls (just like a
primary key, but you can only have one primary key per table). Check out the 
topic "Visual FoxPro
Index Types" in the VFP Help file. It clearly explains all the different index 
types (including a
tip on how to avoid using UNIQUE indexes). 


Rick
White Light Computing, Inc.

www.whitelightcomputing.com
www.rickschummer.com
586.254.2530 - office
586.254.2539 - fax
  


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael
Madigan
Sent: Wednesday, January 17, 2007 04:00 PM
To: [EMAIL PROTECTED]
Subject: RE: Index on collector tag collector unique

Thanks.  What do you mean by "candidate"?





___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index on collector tag collector unique

[MB Software Solutions]

MB Software Solutions wrote:
> Michael Madigan wrote:
>   
>> Thanks.  What do you mean by "candidate"?
>>
>>   
>> 
> Same as a Primary index, meaning that the values in that field 
> (combination) are all unique, i.e., without duplicates, but that it is 
> not the primary index (because something else is generally).
>
>
>   
Let me add this:  "...but a field that might be intelligible or 
human-understandable (like a Customer_ID), unlike a Primary Key field 
where it may simply be an autoincrementing field."

-- 
Michael J. Babcock, MCP
MB Software Solutions, LLC
http://mbsoftwaresolutions.com
http://fabmate.com
"Work smarter, not harder, with MBSS custom software solutions!"



___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index on collector tag collector unique

[MB Software Solutions]

Michael Madigan wrote:
> Thanks.  What do you mean by "candidate"?
>
>   
Same as a Primary index, meaning that the values in that field 
(combination) are all unique, i.e., without duplicates, but that it is 
not the primary index (because something else is generally).


-- 
Michael J. Babcock, MCP
MB Software Solutions, LLC
http://mbsoftwaresolutions.com
http://fabmate.com
"Work smarter, not harder, with MBSS custom software solutions!"



___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index on collector tag collector unique

[Michael Madigan]

Thanks.  What do you mean by "candidate"?



--- Rick Schummer <[EMAIL PROTECTED]>
wrote:

> Unique indexes are bad and the behavior you are
> observing is how they are designed. Direct from
> HackFox:
> 
> "A so-called unique index contains a key only for
> the first record that has a particular key value.
> That is, once a key value occurs, no other records
> with that key value get added to the index.
> There's no mechanism to enforce uniqueness here,
> just a way to find one of each. However, unique
> indexes are not properly maintained. If you delete a
> record that's represented in the index, FoxPro
> does not add the next record in the table that has
> the same key value. Don't ever use "unique"
> indexes—there's always a better way to do it."
> 
> Use candidate index tags to get and enforce
> uniqueness or be prepared to rebuild the index each
> time
> a record is deleted.
> 
> 
> Rick
> White Light Computing, Inc.
> 
> www.whitelightcomputing.com
> www.rickschummer.com
> 586.254.2530 - office
> 586.254.2539 - fax
>   
> 
> 
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of
> Michael
> Madigan
> Sent: Wednesday, January 17, 2007 03:04 PM
> To: [EMAIL PROTECTED]
> Subject: Index on collector tag collector unique
> 
> OK, so I have a client database and on that database
> I have a collector ID.  I have a tag in the
> compound index that is created by 
> 
> "index on collector tag collector unique".  
> 
> This gives me a list of unique collectors using the
> system, each one assigned to one or more
> clients.
> 
> I have a collector 5.  If I happen to delete the
> record which is in the unique index, there no
> longer are any more '5's in the index.  Shouldn't it
> add another record with a collector='5' to that
> index?
> 
> Am I understanding the way unique works?
> 
> 
[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index on collector tag collector unique

[Rick Schummer]

Unique indexes are bad and the behavior you are observing is how they are 
designed. Direct from
HackFox:

"A so-called unique index contains a key only for the first record that has a 
particular key value.
That is, once a key value occurs, no other records with that key value get 
added to the index.
There's no mechanism to enforce uniqueness here, just a way to find one of 
each. However, unique
indexes are not properly maintained. If you delete a record that's represented 
in the index, FoxPro
does not add the next record in the table that has the same key value. Don't 
ever use "unique"
indexes—there's always a better way to do it."

Use candidate index tags to get and enforce uniqueness or be prepared to 
rebuild the index each time
a record is deleted.


Rick
White Light Computing, Inc.

www.whitelightcomputing.com
www.rickschummer.com
586.254.2530 - office
586.254.2539 - fax
  


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael
Madigan
Sent: Wednesday, January 17, 2007 03:04 PM
To: [EMAIL PROTECTED]
Subject: Index on collector tag collector unique

OK, so I have a client database and on that database I have a collector ID.  I 
have a tag in the
compound index that is created by 

"index on collector tag collector unique".  

This gives me a list of unique collectors using the system, each one assigned 
to one or more
clients.

I have a collector 5.  If I happen to delete the record which is in the unique 
index, there no
longer are any more '5's in the index.  Shouldn't it add another record with a 
collector='5' to that
index?

Am I understanding the way unique works?


[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

Re: Index on ... For size

[Derek Kalweit]

Anyone know if there's a limit to how long the for ... clause can be in
an index statement. According to "Compact index file structure" in help,
2 bytes are set aside in the tag header record to indicate the size of
the "for pool." That'd make it possible hold 655536 bytes, but that
seems unbelievably generous and unlikely.


The help file says:

For a standalone index (.idx) file, the length of an index key must be
between 1 and 100 characters. For a compound index (.cdx) file, the
length of an index key must be between 1 and 240 characters.

I've hit the limit when I've had long character fields(250) and didn't
set it to left(field,200) or something...


--
Derek


___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index on ... For size

[Hal Kaplan]

65535, actually.

HALinNY 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
Behalf Of Lew Schwartz
Sent: Thursday, July 13, 2006 13:52
To: profox@leafe.com
Subject: RE: Index on ... For  size

That's 65536, of course. 


[excessive quoting removed by server]

___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.

RE: Index on ... For size

[Lew Schwartz]

That's 65536, of course. 


___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the 
author, and do not constitute legal or medical advice. This statement is added 
to the messages for those lawyers who are too stupid to see the obvious.