Would you belive that I'm working of of views instead of local tables? Sounds stupid, but my nettime soes not lie.

Master2 has indexes on anything relevant!

lv_master2_shipping is master2.* where shipdate={ / / }

I get maybe 1200 pieces instead of 45K. My time dropped from 45 seconds to 3 by changing to the view. That is opening all data from ground 0. If I set the environment for speed, index the view on ponum, it's less than a second.

go figure?

Our typical order from a customer will only be about 5 pieces in custom work. For stock those have already been done and are waiting to be shipped.

I set the showplan on. It spits some text alright! I get alot of nones. when I index the view its not so negative.

__Stephen


>>Yes that does have the data I want? I didn't see that table in my initial search of entering the transactions and this one getting updated? Looks like a traffic cop to a work order.
>>
>>Not that we use 10% of TIW, but is this the scheduling table?
>
>The SOROUTE table is what the SOTRAN transactions are actually posted against. It is the actual route used for the Work Order, starting with the RODETAIL records as a template. Once a Work Order is created, it's local route (SOROUTE) can be customized for that Work Order. Scheduling info is actually in CPSCHED.
>
>>
>>When I substituted your idea into the existing mess I had there was no reall change in performance. 33 seconds to 36 seconds respctfully.
>>
>>If I take that join out, go from 3 tables to 2, it's only 2 seconds. Thats good enough. But the half min is too much.
>
>Not really sure what tags are available on your MASTER2 table. Have you used showplan (SYS(3054,11)) to actually see how your SELECT is optimized or not?
>
>>
>>Currently I'm trying to get the proper syntax for getting a select statement to process a sub query, and then use that in another. This will be presented in SQL area.
>>
>>Thanks. When I get it done I'll pass it back to you.
>>
>
>That would be great. It's always interesting to see how the tables are actually being used.
>
>>__Stephen