Hi,
I have dbf file, about 1500000 records and indexe (NTX): MONTH+Account+Client_ID. I also created CGP file.
Simple query "select * from OBR where month='12' and account='10100'" takes about 10 seconds, but query "select * from obr where account='10100' takes only 2 seconds.
Could you explain where I is the problem? Is database too big?
Thanks in advance
M.
|
>I have dbf file, about 1500000 records and indexe (NTX): MONTH+Account+Client_ID.
>I also created CGP file.
>Simple query "select * from OBR where month='12' and account='10100'" takes about 10 seconds,
>but query "select * from obr where account='10100' takes only 2 seconds.
>MONTH+Account+Client_ID.
Whether there's three NTX files for MONTH, Account, and Client_ID, or one NTX file for three columns?
>Could you explain where I is the problem? Is database too big?
If there's an MONTH+Account+Client_ID NTX file, HXTT DBF can use it to query where month='12' and account='10100'.
If there an Account NTX file, HXTT DBF can use it to query where account='10100'.
If there's not an MONTH+Account+Client_ID NTX file, but there're MONTH NTX file and Account NTX file, HXTT DBF can use both files to query where month='12' and account='10100'.
You can try "select * from OBR where account='10100' and month='12'" too, which mabye is quicker than where month='12' and account='10100'.
For month='12', maybe there is 1500000/12=125000 rows. For account='10100', maybe there 1500000/possibleAccountNumber=148 rows.
Although all of where clauses use index files, different where clause and index tree will result different speed.
|