Query directly from secondary index

#11

Jim

Just a warning using the index file in such a way. There are operations that
doesn't work when applied on an index file. e g isTable("Customer.x03") will
cause a GPV.

--
Bertil Isberg - CTECH
Paradox buglist:
online: http://hem.bredband.net/bertilisberg/

"Jim Moseley" <jmose (AT) mapson (DOT) attglobal.net> skrev i meddelandet
news:4716f748$1 (AT) pnews (DOT) thedbcommunity.com...

Quote:

Greetings all.

I think I've stumbled onto a way to speed up queries, but am looking for
a second opinion.

I have a table with several secondary indexes, one of which contains the
'Name' & 'Key' fields. I want to perform a generic match on the 'Name'
field,
to return all rows that have Name = "..Jim..".

If I perform my query on the DB file, it will open & search the entire
table,
ignoring the 2nd index because of the wildcards.

If I perform my query directly against the appropriate XG* file, it will
perform the query on a 'mini-table' of just the Name & Key columns, which
should be much smaller than the entire DB table.

Does this make sense?

For example:

Query
Customer.XG1 | Key | Name |
| Check | CheckPlus ..Jim.. |
endQuery

Note that I tried to open a tcursor against the 'Customer.XG1' file & it
wouldn't let me. The query engine must work differently.

Extra credit: by doing a enumIndexStruct on the table, does the iIndexID
column map directly to the 'XG*' name, as 'X' + string(hex(iIndexID + 1))?
I'm guessing this is the case, because the iIndexID values start with 256
and go up by 1 for each additional index.

Thanks,
Jim Moseley

#12

Bertil,

Quote:

There are operations that don't work when applied on an index file.

Point well taken. I'm only going to query them, and I definitely won't use
an Update, Insert or Delete query on the XG1!

Thanks,
Jim Moseley

#13

Jim

A couple of suggested work arounds.

Depending on how often the customer table changes, you can cache a local
table for look-up with an update button and a table older than X days when
the application opens that allows them to update the local cached table.
I've used that on a table that has meter stations and the meter station
additions are in the tens per month. If they can't find the customer and
think they should be there they can just update the local table.

The other option is to have a second table of only the customer data you
want the look-up for and update the look-up table when the customer table
changes.

Just my 2 cents.

"Jim Moseley" <jmose (AT) mapson (DOT) attglobal.net> wrote

Quote:

Anders,

Imagine a 100MB file on network server. With a 500k index file.

This was exactly my problem. One of my clients had a 20MB Customer table,
whereas all others have under 1MB. Their network is dying, and I'm trying
to speed it up. Sorry I didn't post this number earlier, but I didn't
have
an exact size on the XG1 file.

How about a tcursor then? Will a scan of all records
eventually bring the enterie table over the
network, even the columns that is not refered to by the tcursor?

Sadly, you can't open a tcursor on the XG1 directly - it gives a message
'Table is not indexed' (ironically).

If you open a tcursor on the base table, it will 'pre-fetch' 8 pages based
on the block size. Then, as you jump around the table, it will grab
whatever
pages are needed. So, if you scan the entire table, you'll need all 20MB
in my above example.

One other benefit to the XG1: if you open a tcursor on a table, it will
open all table lookups also. I'm assuming it doesn't want to slow down
tc.edit()
(or the f9 key), so it always assumes you'll edit. I'm not sure, but
these
might also get 8 pages 'pre-fetched'.

I'm getting somewhat proficient with the free WireShark network monitor.
http://www.wireshark.org I you have speed issues, this is the best tool
I've found. It replaced Ethereal a while back.

Thanks,
Jim Moseley

#14

If anyone is interested, here's the library routine I wrote to determine which
file could be used for a query - X01..XG9 if 2nd index, DB otherwise:

;---- start ------
var
saveTableExtAR array[] string
saveTableExtDyn dynArray[] string
endVar

method getQueryTableExt(var tcIn tcursor, curTab string, curField string)
string
var
idxTC,
tc tcursor
endVar

curExt = "db"

while true
if saveTableExtAR.size() > 0 then
if saveTableExtAR.contains(curTab) then ; only first time
quitloop
endif
endif

iTab = "

riv:queryExt.db"
ok = tcIn.isAssigned()
if ok then
ok = tc.attach(tcIn)
else
ok = tc.open(curTab)
endif
if ok then
ok = tc.enumIndexStruct(iTab)
endif
if ok then
ok = idxTC.open(iTab)
endif
if not ok then
errorShow()
return(curExt)
endif

extStr = ""
addIt = true
scan idxTC :
if not idxTC.iIndexID.isBlank() then
curIdxStr = toHex(idxTC.iIndexID)
maxI = curIdxStr.size()
if maxI > 3 then
if curIdxStr.subStr(maxI - 2,1) <> "0" then
extStr = "XG" + curIdxStr.subStr(maxI,1) ; 256: 0x00000100 = XG0
else
extStr = "X" + curIdxStr.subStr(maxI-1,2) ; 1: 0x00000001 = X01
endif
endif
endif
curSlot = curTab + ":" + upper(idxTC.FieldName)
if extStr <> "" then
saveTableExtDyn[curSlot] = extStr
if addIt then
saveTableExtAR.addLast(curTab)
addIt = false
endif
endif
endScan
quitloop
endWhile

if saveTableExtAR.contains(curTab) then
curSlot = curTab + ":" + curField
if saveTableExtDyn.contains(curSlot) then
curExt = saveTableExtDyn[curSlot]
endif
endif

return(curExt)
endMethod
;------- end -------

The calling form's code can be this easy, though if you have multiple columns
or tables it gets more complicated:

Uses ObjectPal
getQueryTableExt(var tc tcursor, curTab string, curField string) string
endUses

;...
masterTab = "Customer"
if tc.dmattach("MASTER") then
tabExt = lUtil.getQueryTableExt(tc, masterTab, srchFld)
if tabExt <> "" then
masterTab = masterTab + "." + tabExt
endif
endif

qry1 = "Query "
qry1 = qry1 + "\n\n" + masterTab + " | CustNo | "
qry1 = qry1 + "\n" + " | Check > 0 |\n\n"

qry1 = qry1 + masterTab + " | " + srchFld + " | "
qry1 = qry1 + "\n | " + srchFor + " |"
qry1 = qry1 + "\n\n" + "EndQuery "

ok = q.readFromString(qry1)
if ok then
ok = qrySave.executeQBE()
endif
if not ok then
errorShow()
endif

#15

Bertil,

Well, so much for brilliant ideas! One of my clients has been getting 'Lock
Time Out' errors since I put my new code in. Since these are maintained
indexes, there must be a conflict when someone updates a row on the table
(or just the 2nd index columns?).

Isn't there a way to ignore table changes when running a query?

Thanks,
Jim Moseley

#16

<<<
Isn't there a way to ignore table changes when running a query?

Quote:

ignoring query changes can be used to prevent the query from restarting when
the table is updated. IMO, it won't prevent the locks placed.

--
Bertil Isberg - CTECH
Paradox buglist:
online: http://hem.bredband.net/bertilisberg/

"Jim Moseley" <jmose (AT) mapson (DOT) attglobal.net> skrev i meddelandet
news:4717cd57$1 (AT) pnews (DOT) thedbcommunity.com...

Quote:

Bertil,

Well, so much for brilliant ideas! One of my clients has been getting
'Lock
Time Out' errors since I put my new code in. Since these are maintained
indexes, there must be a conflict when someone updates a row on the table
(or just the 2nd index columns?).

Isn't there a way to ignore table changes when running a query?

Thanks,
Jim Moseley

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Query directly from secondary index

comp.databases.paradox comp.databases.paradox

Re: Query directly from secondary index - 10-18-2007 , 02:26 PM

Re: Query directly from secondary index - 10-18-2007 , 03:36 PM

Re: Query directly from secondary index - 10-18-2007 , 03:44 PM

Re: Query directly from secondary index - 10-18-2007 , 03:46 PM

Re: Query directly from secondary index - 10-18-2007 , 04:17 PM

Re: Query directly from secondary index - 10-19-2007 , 11:55 AM