We have a column that is the price of a product and is set to data type float. We have an index on it like this:
tsql "create index xretprice on books(RETAIL_PRICE)"
but when do a query for a price between two values the search is incredibly slow. Any ideas on how we could optimize this?
our query is like this:
and RETAIL_PRICE between $$qprice and $$qpricehi
<$sqlq="select " $return " from " $table " where
AUTHOR like $$qauth and TITLE like $$qtit
and AUTHOR\TITLE like $$qat
and AUTHOR = $$qcauth and TITLE = $$qctit
and BASIC\LC_SUBJECT\GEO_CODE\KEYWORDS like $$qtopic
and BASIC\LC_SUBJECT\GEO_CODE\KEYWORDS liker $$qtopicr
and AUTHOR\TITLE\PUBLISHER\MAIN_TOPIC\KEYWORDS\EDITION\COMMENTS\NOTES\PLACE_PUB\DATE_PUB like $$query
and AUTHOR\TITLE\PUBLISHER\MAIN_TOPIC\KEYWORDS\EDITION\COMMENTS\NOTES\PLACE_PUB\DATE_PUB like $$newquery
and BOOKNUM = $$bookbin
and id = $$bookid
" $inpart "
and USER_ID = $$quserid
and PUBLISHER like $$qpub
and FL_COVER = $$binding
and FL_SIGNED = $$signed
and FL_EDITION = $$first
and FL_DUSTJACKET = $$dj
and CATLG_ISBN = $$qisbn
and DATE_PUB between $$qyear and $$qyearhi
and DATE_PUB = $$qyearonly
and RETAIL_PRICE between $$qprice and $$qpricehi ">
It only slows when we do the retail_price search portion. It looks like from our script that btreethreshold is set to 5 in that function.. does that carry through the entire execution or need to be explicit before the SQL call??
Does this provide any insight as to a direction I should head.
bash-2.04# time tsql -V "set btreethreshold ='50';select count(*) from books where BASIC\LC_SUBJECT\GEO_CODE\KEYWORDS like 'Drawing' and RETAIL_PRICE <= '6'" |more
200 Setting pred BASIC\LC_SUBJECT\GEO_CODE\KEYWORDS LIKE 'Drawing' AND RETAIL_PRICE<= 6 on table books in the function settablepred
200 Opening index /db/indexes/prod/xtopic in the function ixfmmindex
200 Opening index /db/indexes/prod/xretprice in the function ixbtindex
200 Expect to read 5% of the index in the function ixbtindex
Texis Version 03.01.971289085(20001011) Copyright (c) 1988-2000 Thunderstone EPI
count(*)
------------+
591
real 0m5.803s
user 0m4.247s
sys 0m0.423s
bash-2.04# time tsql -V "set btreethreshold ='5';select count(*) from books where BASIC\LC_SUBJECT\GEO_CODE\KEYWORDS like 'Drawing' and RETAIL_PRICE <= '6'" |more
200 Setting pred BASIC\LC_SUBJECT\GEO_CODE\KEYWORDS LIKE 'Drawing' AND RETAIL_PRICE<= 6 on table books in the function settablepred
200 Opening index /db/indexes/prod/xtopic in the function ixfmmindex
200 Opening index /db/indexes/prod/xretprice in the function ixbtindex
200 Expect to read 5% of the index in the function ixbtindex
Texis Version 03.01.971289085(20001011) Copyright (c) 1988-2000 Thunderstone EPI
There are about 20,000 records that match that Drawing search out of 16 Million records.
when you say compund metamorph index what exactly do you mean and how would I query on price ranges like less than 6 or between 10 and 20 with a metamorph index??