I need help in understanding a performance issue question. The application is in VFP 8, I have a form that populates a list based off the results of running a stored procedure, the contents of the stored procedure is shown below. When run from within the application, it takes 6-7 seconds at the client site. This is way too long and on our server with their data from the application it is taking less than a second. I brought their data back since I thought it was a query problem, but it runs fine here. So I then logged into their server, opened up the QA and ran the stored procedure:

exec dbo.ct_lu_SurgeonList_WithConflictSearch

and it returned in less than 1 second. So now I'm wondering why SQL Server can return it so quick in QA, but not from within the application. I'd appreciate any idea's.

Kirk

CREATE PROCEDURE ct_lu_SurgeonList_WithConflictSearch
@tiDept int,
@ttStart datetime

as
set nocount on
SELECT t.lastname, t.firstname, t.coPerid, 
   /* This next line returns the room name 
      where the selected surgeon has a conflict */
   (SELECT Top 1 dbo.corooms.roomname 
           FROM  dbo.meetings with (nolock) INNER JOIN 
                 dbo.meetproc with (nolock)  ON dbo.meetings.meetingnumber = dbo.meetproc.meetingnumber INNER JOIN 
                 dbo.corooms with (nolock) ON dbo.meetings.roomid = dbo.corooms.roomid  
           where @ttStart  between meetings.begintime and meetings.endtime 
  	   and meetproc.coperid=t.coPerID and isblock=0 and iscancelled=0) as RoomName,
   isnull(t.isresident,0) as isResident,
   isnull(t.issuspended,0) as isSuspended,
   0 as NumCancelled,
   0 as NumSched,
   isnull((Select specdesc from dbo.coSpec with (nolock) where coSpec.specID=t.SpecID),' ') as specdesc
FROM dbo.coPer t with (nolock)
where EXISTS (select *
              from dbo.coPerDept
              WHERE dbo.coPerDept.deptid=@tiDept  
              AND dbo.coPerDept.coPerID=t.coperid) 
and t.isactive=1
and t.perTypeID=1
order by t.lastname, t.firstname