How to make index on Item Code

[alisaif]

Hi

I have a stock table which contains at least 1 million records.
This table contains the details of products In and Out and if take the details of a particular code it takes a long time to display.
My requirement is as follows:

micode = 'B1236'
dt1 = {01/01/2009}
dt2 = {31/12/2013}

Syntax I used:

select * from stock where icode = micode and between(date,dt1,dt2) into curs tmpStock

What I want is I do not want to create index on particular code, can I filter records based on any code like, B1236,W529,C1560 etc etc. and what is the syntax for that for both (Dos FPD26a AND VFP9).

Please guide

Thanks

Saif

 

[Mike Lewis]

Hello Saif,

There is absolutely no benefit in performance of creating a filter. Whether you use a WHERE clause in a SELECT statement, or you create a filter, you still need an index if you want to improve performance. (In fact, the WHERE clause is a sort of filter, even though it is not called that.)

Can you explain why you don't want to create an index on your stock code? Doing so will solve your performance problem.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[RobSpencer]

E

http://wwww,

you have a field called date? Unlucky.

I agree with Mike - you'll need to index the table to get any type of performance. We have a similar table with over 7.5 million records with indexes on the equivalent of your icode and date fields (ie an index on icode and another index on date) and the performance is fine. The query optimiser decides which index is the most efficient to use depending on the query we are using.

If you're really worried, you could try a composite index (as created in the following code) but it makes the code much less obvious.

index on icode + dtoc(date, 1) tag stockdate     && look at help for dtoc(date, 1). You also probably want an index on date alone.

select * from stock where between(icode + dtoc(date, 1), micode + dtoc(dt1, 1), micode + dtoc(dt2, 1)) into curs tmpStock

As I said, I wouldn't bother and would simply index on icode and on date but this might give you ideas for future code.

Just remember that index expressions should (that should probably read *must*) be fixed length.

Rob Spencer
Caliptor Pty Ltd

http://www.caliptor.com.au/

 

[GriffMG]

For me, personally, I would create the index like this:

index on idoce+dtos(date) tag stockdate

Then I would create the cursor programatically like this:

select stock
copy to (m.TempDir+"TmpStock") stru
select 0
use (m.tempdir+"TmpStock") exclusive
select stock
set order to stockdate
set near on
seek (m.iCode+dtos(dt1))
do while .not. eof() .and. icode=m.icode .and. dtos(date) <= dtos(dt2)
    scatter to tmpfields memo
    select tmpstock
    append blank
    gather from tmpfields memo
    select stock
    skip
enddo
set near off
select tmpstock

I don't think any kind of optimisation could be faster... you narrowed the selection start using a seek, and then it's end point using the master criteria of
the stock item and the last date required. You could optimise performance of the scatter / append / gather sequence... but my exp is that this way is readable
and effective. Not sure if a SCAN/ENDSCAN would help.

Of course, there might be issues with empty dates... but they are easy enough to allow for.


Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​

 

[Mike Lewis]

Griff, I'm not sure I agree that a SEEK / DO WHILE would be faster than a SELECT ... WHERE ... INTO. I think a SELECT would usually be faster.

But I'm not sure. If I was Saif, I would try them both and see which is faster.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

Oops the index should have been

index on icode+dtos(date) tag stockdate

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​

 

[GriffMG]

Hi Mike,

For the select to be quicker it would have to optimize the request, I'm not sure it could do that with the dates - certainly with the stock code, but
I don't think the dates element would be quicker, I'm not 100% sure.

I think the inclusion of the 'near on/off' makes this particular approach about as efficient as it can be. It's using the programmer's knowlege of the actual
data, and the requests likely to be made of it, to get the blindingly fast results that we've come to expect from VFP (well, from dBase II upwards really).

Try it and see Saif.

Let us know if you can

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​

 

[RobSpencer]

*sigh* Yes Griff. You're right - dtos(date) is better than dtoc(date, 1). How many times have I forgotten the extra parameter and wondered why my code doesn't work? (A: I don't know exactly but lots!)

Your solution would be quick ...but I'd probably still opt for the select statement.
[ol]
[li]I'd have to put protection code around the set near to make sure I reset to what it was before - this assumes that it was off.[/li]
[li]I'd have to generate a unique output filename and open it with an alias of tmpstock in case you had the code running in multiple processes at the same time on the same machine. Since it creates a table rather than a cursor, the table would then need to be cleaned up.[/li]
[li]It doesn't easily extend as the data selection gets more complicated. If you need to optionally add in an associated table with additional selection criteria, you suddenly have to start thinking a lot![/li]
[/ol]

Hmmm - would the SQL select work in FPD2.6a though? I'm not sure as I've not used it.

That's just me though

Rob Spencer
Caliptor Pty Ltd

http://www.caliptor.com.au/

 

[craigber]

As long as we're talking about optimizations, SCAN/ENDSCAN will be faster than DO WHILE !EOF()/SKIP/ENDDO

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[GriffMG]

Agreed on all the points you mention, I was kinda expecting the m.TempDir to be unique to the instance thus no chance of it
clashing - the set status for near should be saved and restored (probably immediately after the seek).

The technique, using the design to limit the scope, is something I do all the time and extending is just a question of using
a flag like this:

select mytable
do while .not. eof and myfield = m.myvar
  m.flag = .t.
  if ... whatever doesn't match
     m.flag = .f.
  endif
  if m.flag .and. ... next condition fails
     m.flag = .f.
  endif
  if m.flag
     scatter to tmpfields memo
     select temptable
     append blank
     gather from tmpfields memo
  endif
  select mytable
  skip
enddo

I think it's very readable and flexible and, usually, very fast...

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​

 

[GriffMG]

I'm not sure scan/endscan will be faster in this instance, having optimised the selection
as far as is possible, but it would look like this:

select mytable
set order to myorder
m.NearSetting = set("near","ON")
seek (m.myvar)
set("near",m.NearSetting)

SCAN do while .not. eof() .and. myfield = m.myvar
  m.flag = .t.
  if ... whatever doesn't match
     m.flag = .f.
  endif
  if m.flag .and. ... next condition fails
     m.flag = .f.
  endif
  if m.flag
     scatter to tmpfields memo
     select temptable
     append blank
     gather from tmpfields memo
  endif
ENDSCAN

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​

 

[alisaif]

Thanks for the valuable feedback, actually I am not worried about the date, I want to open the table (without sql select) directly to display the inventory details in such a manner that if user input any code (icode) i.e, B1236,C1560,W529 .. .. .. it should automatically set the index according to that code. And, I do not want to assign the specific code like the following:

use stock excl && Total Records over one million
index on icode tag B1236 for icode = 'B1236' additive[highlight #3465A4][/highlight]

(Please note that Item Master (icode.dbf) contains 22,000 codes), so the index should be for any code.
Sql-Select is taking at least 10secs to display the report.

Kindly let me know the alternate of the bold sentence above.

Thanks

Saif

 

[Mike Lewis]

You definitely don't want to index the table every time you open it. The act of indexing the table will take a long time, and will partly offset any saving you make from having an index.

If you are always only going to view the records whose code is B1236, then execute the INDEX command that you posted, but do it once only, the very first time you open the table.

More likely, you want to view a different set of records each time. In that case, execute the INDEX command but without the FOR clause. Again, do that once only. Then, when you want to view records with a particular code, either do the SELECT that you posted at the start of the thread. Or use Griff's idea of a SEEK followed by a DO WHILE loop.

The important point is to create an index, and to do it once only.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[alisaif]

Thanks Mike, I will go through it...

Saif

 

[Olaf Doschke]

>The important point is to create an index, and to do it once only.
Can't be stressed too often. Yes.

You simply INDEX on both single fields and a SQL query can make use of both indexes and optimize the query.

One thing I learn a few months ago with such large tables with a million records is, you have to give enough RAM to let rushmore create the optimization bitmaps.

A single index on an expression combining can help a SEEK better, but also a SET FILTER can make use of more than one index.

So I would simply do
INDEX ON iCode TAG xCode
INDEX ON Date TAG xDate

Just once, this can be done manually from the VFP command windows. Once the CDX file is there, any index tag in there is automatically updated with any operation on the DBF from anywhere, you only need to do this once and for all.

Set your memory allocation limit high enough or the benefit of rushmore will not work, a million records means a million bits per single clause of the where clause, eg WHERE iCode='XY12345' AND date between date1 and date2 are two conditions. Alone for the rushmore bitmaps of the query SELECT * FROM yourtable WHERE iCode='XY12345' AND date between date1 and date2 you need 2x a million bits, that's a quarter MB. Not much. But VFP caches a lot and you need RAM for many things more than rushmore bitmaps. So don't use RAM sparingly. If you SYS(3050,1,1024*1024) VFP won't take more than 1 MB RAM and will allocate further memory needs from the hard disc swap file. Put that much higher, it's not the RAM VFP will allocate right away.

Bye, Olaf.

 

[alisaif]

Thanks Olaf.

Saif