How to make index fast 2

[alisaif]

Hi,

I have one item master file (icode.dbf) which contains around 5000 records.

Item codes are like; B1236, W529, H1026 and a lot more.

All these items are heavy transaction in stock file (stock.dbf) which contains around 1 million records.

If I take records between some specific date, it is taking too much time to display.
I cannot make index of all the 5000 items like ;

Index on Icode tag Icode for Icode = 'B1236' additive

What is the best solution to tackle this criteria.

Thanks

Saif

 

[Scott24x7]

Saif,
What you want to do is use the smallest possible field (set of fields) to create unique values.
There are two ways to do this.
1)
Index on a single field like "customer number", or perhaps a compound index like "Customer number + DTOC(TransactionDate)", though i recommend use a single field if at all possible.
You can then do something like:
SELECT STOCK
SET ORDER TO (SuperFastIndex)
SELECT * FROM STOCK INTO CURSOR WHERE iCODE.CustomerNumber+DTOC(TransactionDate) = STOCK.CustomerNumber+DTOC(STOCK.TransactionDate)

Or, in the "old school method":
SELECT STOCK
SET ORDER TO (SuperFastIndex)
SEEK iCODE.CustomerNumber+DTOC(TransactionDate)
DO WHILE STOCK.TransactionDate = m.TransactionDate && (or iCODE.TransactionDate, depending on what you want. You could also make this a range)
&& (SCATTER/GATHER if you like for real old school or)
INSERT INTO WORKINGTABLE (Fields you want)
SELECT STOCK
SKIP
ENDDO

Lots of ways to go about this, depending on what you know/are comfortable with.


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCAP, CDCP, CDCS, CDCE, CTDC, CTIA, ATS, ATD

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[Tore Bleken]

5000 records is a very small number. Just create plain index tags for the necessary fields. Index For is a terrible idea...!

 

[Chris Miller]

In the first place all you need is an index

Index on Icode tag Icode

Once you have that index, it's there, so don't repeatedly execute the index command, just once.
Instead of coding it you can also use the table designer and pick "ascending" in the index column of the table designer for the field icode.

That will be usable to find data for any Icode faster. There is no need to have an index for each icode value. That one index is already there to accelerate access to any value.

Now for example

Select * from stock where icode = 'B1236'

uses that index to find all data for that one icode.

I have no idea how that is related to finding "records between some specific date", for that you'd need to have a datetime field in the data and an index on it.

Chriss

 

[mjcmkrsr]

Hi,

Your message isn't very verbose and you expect us to guess quite a bit. What are you trying to achieve and what did your try - some code snippets and a description of the encountered difficulties would help.

Btw one million records are not that much. You may take my former code example (thread184-1824094) and raise the numbers of added records (500'000). You'll see that even without an index SQL Select is very fast.

PROCEDURE Load
	LOCAL lnI
	
	CREATE CURSOR csrSalesData (Id I AUTOINC, ;
	   dSalesDate D DEFAULT DATE(YEAR(DATE()), 1, 1) + RAND() * 3600, ;
	   ySalesAmount Y DEFAULT Rand() * $500.00)
   
	For lnI = 1 To 500000
	   Append Blank
	EndFor
	
	LOCATE 
	
ENDPROC

hth

MarK

 

[alisaif]

I think I did not convey it properly. I will come again for the same soon.

Thanks

Saif

 

[GriffMG]

DTOC is a poor choice in this context, it should probably be DTOS

That way the dates are in a good order to extract the records of interest between two dates

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 !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Scott24x7]

Ah, yeah, you're right Griff. It's been a while since I created a compound index with a date. ><


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCAP, CDCP, CDCS, CDCE, CTDC, CTIA, ATS, ATD

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[GriffMG]

B-)

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 !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Chris Miller]

Surely you have the problem in indexing the 1 million record table (stock.dbf), not the 5000 record master table.

I remember under some circumstance the operation of creating the index itself can be problematic, but not in general. Even indexing all fields the CDX will end up with the same data, just differently structured and not outgrow the dbf, unless ou would index a lot of combinations of fields all into the main cdx file.

Creating a lot of smaller index by using for conditions is surely not the way to go.

Anyway, giving it another try to explain your problem will surely help before we speculate what's the real problem.

Chriss

 

[Dan Olsson]

You can then do something like:
SELECT STOCK
SET ORDER TO (SuperFastIndex)
SELECT * FROM STOCK INTO CURSOR WHERE iCODE.CustomerNumber+DTOC(TransactionDate) = STOCK.CustomerNumber+DTOC(STOCK.TransactionDate)

A small comment:

SELECT STOCK
SET ORDER TO (SuperFastIndex)

Is not only unnecessary, but actually slows the SELECT down a little bit

 

[Scott24x7]

Well the point was to introduce the OP to doing his own experimentation, while pointed in the right direction. I wasn't giving a solution.
Sometimes you guys grind these responses down to a point beyond usefulness.


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCAP, CDCP, CDCS, CDCE, CTDC, CTIA, ATS, ATD

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[Dan Olsson]

Sorry, Scott. I wasn't trying to offend you...