470,619 Members | 1,469 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,619 developers. It's quick & easy.

Spilled Sorts (better Indexing??)

1
Here is the old query (very slow):

select stgprobillnum
from stgdoc001, icmstri001001 where
targetitemid = '$filename' and
stgdoc001.itemid=icmstri001001.sourceitemid and stgstatus in (4, 5, 6 )

Here is my new query (much faster):

select stgprobillnum from stgdoc001 where stgstatus in (4,5,6)
and stgprobillnum = (select stgprobillnum from
stgdoc001,icmstri001001 where targetitemid = '$FILENAME'
and stgdoc001.itemid=icmstri001001.sourceitemid) with ur


the query is wicked fast.

Here are my indexes:

Indexes Created
Table: ICMUT01058001
STGDOC1_ITEMID (ITEMID)
STGDOC1_ITMSTAT (ITEMID, 1087)
STGDOC1_PROBILL (1097)
STGDOC1_STATUS (1087)

Table: ICMSTRI001001
STG_SOURCEITEMID (SOURCEITEMID)
STGDOC1_TARGET (TARGETITEMID)
STGDOC1_TRGSRCID (TARGETITEMID, SOURCEITEMID)

Although my query is fast, I keep getting spilled sorts.

Any help is appreciated.

Thanks.

Chris.
Dec 1 '06 #1
0 1066

Post your reply

Sign in to post your reply or Sign up for a free account.

By using this site, you agree to our Privacy Policy and Terms of Use.