Hi Jon,

what I've seen with FileMon (http://www.sysinternals.com/Utilities/Filemon.html) is (with the view expanded to include all commands, not just the default setting commands) that there is a QuickRead done by the file server for each and every record of the table being used in the query. Not that these take long: by definition the QuickRead does not take that long; but when it is 400K or more of the critters, it can take a while. When there is only one user, locking contention is not an issue, and the QuickRead is not needed. This is an SMB thing: put the file on the same machine as the app, and with multiple users or not, the problem goes away.

It's enough to make one give up on using views (except our apps have to be able to run on multiple backends).

Hank

>I'm perplexed on why this code is slowing down and would love some help to determine why.
>
>When I run this local view query and only one user is running I get great sub-second speed. When two users are running I get 3 second plus speed after one of the users has run the same query and it stays that way until both users get out of the application.
>
>I have run the coverage profiler and determined this select to be the problem.
>
>The view originally was part of the DBC on the server, but I have moved it to be within the code and removed it from the DBC. As I understand it this should not have a locking problem.
>
>There are four tables (all within the DBC) and indexes exist for all fields referenced in the WHERE and JOIN.
>
>Here's the statement being used.

>
>SELECT Icpart.cupartid, ;
>    Icinvt.ylist, ;
>    Icprce.crectype, ;
>    Icprce.iicsizeid, ;
>    Icprce.csellunit, ;
>    Icprce.yunitprice, ;
>    Icinvt.iitemno, ;
>    Icinvt.citemid, ;
>    Icinvt.cdescrip, ;
>    Icinvt.ndecnum, ;
>    Icinvt.citemtype, ;
>    Icinvt.cmatrix, ;
>    Icinvt.cstkunit, ;
>    Icinvt.clocation, ;
>    Icinvt.cclass, ;
>    Icinvt.lresell, ;
>    Icinvt.lnotavailable, ;
>    Icimxt.imxtno, ;
>    Icimxt.nonhndshlf, ;
>    Icimxt.nonhndwhse ;
>  FROM ta700!icpart ;
>    LEFT JOIN ta700!icinvt ON Icpart.iitemno == Icinvt.iitemno ;
>    LEFT JOIN ta700!icimxt ON Icpart.iitemno == Icimxt.iitemno ;
>    LEFT JOIN ta700!icprce ON Icpart.iitemno == Icprce.iitemno ;
>    AND Icprce.crectype = This.cRecType  ;
>    AND Icprce.cSellUnit == Icpart.cSellUnit ;
>  WHERE Icpart.cidtype <> This.cIDType  ;
>    AND Icpart.cupartid = This.cUPartID INTO CURSOR v_ICPartLookUpCmb
>

>
>Any insight would be appreciated.
>
>Jon