Looking for the last record

A

ASPI

New Member
I've got a bin # table that has about 26,000 records in it(A001 TO Z963). We recently changed our bin numbering system to start with AA001. Also in the bin record there are a few erronious entered bin#s.

It's taking too long to find that last record. Below is my snippet of code. Is there a better way of doing this.

FIND LAST WhseBin WHERE WhseBin.Company = CUR-COMP AND WhseBin.WarehouseCode = "ASP"
AND (length(WhseBin.BinNum) > 5) AND (WhseBin.BinNum <> "L824+") AND (WhseBin.BinNum <> "U5021")
AND (WhseBin.BinNum <> "M0733") AND (WhseBin.BinNum <> "M724+")
AND (WhseBin.BinNum <> "N0127") AND (WhseBin.BinNum <> "N0128")
AND (WhseBin.BinNum <> "NOMTR"). NO-LOCK.

I have 1 index in the table its
Company character A
WareHouseCode character A
BinNum character A
 
Hmm

Yea that's what I'm thinking. It's just got a bunch of records to search through. So are there any ideas out there for juicing up this search?
 
Surely you only need to do this in your query:
<pre>
FIND LAST WhseBin
WHERE WhseBin.Company = CUR-COMP
AND WhseBin.WarehouseCode = "ASP"
AND length(WhseBin.BinNum) > 5
NO-LOCK.
</pre>
As all the things you are excluding explicitly have a length of 5 characters, but you have already excluded them by the length(WhseBin.BinNum) > 5 part of your query.

That should speed it up a bit

I think it would be faster still if you did this tho (not sure though, you would have try it out):
<pre>
FIND LAST WhseBin
WHERE WhseBin.Company = CUR-COMP
AND WhseBin.WarehouseCode = "ASP"
NO-LOCK.
DO WHILE LENGTH(WhseBin.BinNum) <= 5:
FIND PREV WhseBin
WHERE WhseBin.Company = CUR-COMP
AND WhseBin.WarehouseCode = "ASP"
NO-LOCK.
END.
</pre>
If nothing else, this will stop the query doing a full index search and will reduce the load on the database server

hth
 
Opps it's actually >4

Good catch, but my code actually says ">4". That being said if I run the code without my exclusions I'll get the last number as being "U5021".

Sorry for my mistake.



FIND LAST WhseBin WHERE WhseBin.Company = CUR-COMP AND WhseBin.WarehouseCode = "ASP"
AND (length(WhseBin.BinNum) > 4) AND (WhseBin.BinNum <> "L824+") AND (WhseBin.BinNum <> "U5021")
AND (WhseBin.BinNum <> "M0733") AND (WhseBin.BinNum <> "M724+")
AND (WhseBin.BinNum <> "N0127") AND (WhseBin.BinNum <> "N0128")
AND (WhseBin.BinNum <> "NOMTR"). NO-LOCK.
 
ok, well is it any faster if you do this:
<pre>
FIND LAST WhseBin
WHERE WhseBin.Company = CUR-COMP
AND WhseBin.WarehouseCode = "ASP"
NO-LOCK.
DO WHILE LENGTH(WhseBin.BinNum) <= 4
OR WhseBin.BinNum = "U5021"
OR WhseBin.BinNum = "NOMTR"
OR WhseBin.BinNum = "N0128"
OR WhseBin.BinNum = "N0127"
OR WhseBin.BinNum = "M724+"
OR WhseBin.BinNum = "M0733"
OR WhseBin.BinNum = "L824+":
FIND PREV WhseBin
WHERE WhseBin.Company = CUR-COMP
AND WhseBin.WarehouseCode = "ASP"
NO-LOCK.
END.
</pre>
Note the order of the explicit bin number checks.
 
improvement

There's somewhat of an improvement. Still takes longer than my user's patience. This is a program for our receiving department.

FIND LAST WhseBin
WHERE WhseBin.Company = CUR-COMP
AND WhseBin.WarehouseCode = "ASP"
NO-LOCK.
DO WHILE LENGTH(WhseBin.BinNum) <= 4
OR WhseBin.BinNum = "U5021"
OR WhseBin.BinNum = "NOMTR"
OR WhseBin.BinNum = "N0128"
OR WhseBin.BinNum = "N0127"
OR WhseBin.BinNum = "M724+"
OR WhseBin.BinNum = "M0733"
OR WhseBin.BinNum = "L824+":
FIND PREV WhseBin
WHERE WhseBin.Company = CUR-COMP
AND WhseBin.WarehouseCode = "ASP"
NO-LOCK.
END.
 
If you REALLY want report speed you could do this (I don't think I would though):

modify table add column fastSearch as logical.

for each table
where (length(WhseBin.BinNum) > 5)
AND (WhseBin.BinNum <> "L824+")
AND (WhseBin.BinNum <> "U5021")
AND (WhseBin.BinNum <> "M0733")
AND (WhseBin.BinNum <> "M724+")
AND (WhseBin.BinNum <> "N0127")
AND (WhseBin.BinNum <> "N0128")
AND (WhseBin.BinNum <> "NOMTR"):
fastSearch = yes.
end.

Add an index
Company character A
WareHouseCode character A
fastSearch logical A
BinNum char A

Then your query becomes:
FIND LAST WhseBin
WHERE WhseBin.Company = CUR-COMP
AND WhseBin.WarehouseCode = "ASP"
and fastSearch = yes
no-lock.

Of course you would have to add triggers or change your code to keep it all up to date

That probably doesn't help but it is an idea

Murray
 
The fact that it doesnt speed it up much suggests that you have a lot of codes with less than 5 characters, so a different approach is needed.

I agree with Murray, I think to see any real increase in speed you are gonna have to add a field. The way in which you are querying the BinNum field, ie excluding short codes and some explicit codes, suggests to me that you (or your users) are using the table to store information about things other than bins or you have various types of bins, some of which you wish to exclude from the particular query. If this is the case, then I would suggest adding a BinType field and add this to the index above BinNumber. This would give the same benefit as Murray's example for this particular query, but would also allow you to achieve the same performance gain when you wish to query the other items/bin types you are using. It would also give you the flexibility to store information for any other items / bin types that might come along in the future.

Hope this makes sense and i'm not way off base :-)
 
You must log in or register to reply here.
Back
Top