Microsoft KB Archive/275515

= PRB: Performance Degradation with SQL Selects from Offline Views =

Article ID: 275515

Article Last Modified on 8/27/2002

-

APPLIES TO


 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition

-



This article was previously published under Q275515



SYMPTOMS
When you issue SQL SELECT statements against offline views, the performance of an application degrades, and system resources are gradually depleted.



RESOLUTION
Issue SQL SELECT statements against online views or tables rather than offline views.



Steps to Reproduce Behavior
  Create a program file named &quot;Memdemo.prg&quot; and paste the following code: IF !FILE(&quot;MYOLV.DBC&quot;) SET SAFETY OFF SET MULTILOCKS ON  * Create a database. CREATE DATABASE MYOLV * Declare API function to create ODBC DSN. DECLARE INTEGER SQLConfigDataSource IN odbccp32.DLL ; INTEGER, INTEGER, STRING, STRING IF VAL(SUBSTR(VERSION,15,2))=6 OR VAL(SUBSTR(VERSION,15,2))=7 lcDir=HOME(2)+&quot;data\&quot; ELSE lcDir=HOME+&quot;SAMPLES\DATA\&quot; ENDIF * Information to setup ODBC DSN. lcSetUp=&quot;DSN=MyOffLine&quot;+CHR(0)+; &quot;Description=VFP Offline View Demo&quot;+CHR(0)+; &quot;SourceDB=&quot;+lcDir+&quot;testdata.dbc&quot;+CHR(0)+; &quot;SourceType=DBC&quot; * Call API function to create a DSN. =SQLConfigDataSource(0,1,&quot;Microsoft Visual FoxPro Driver&quot;,lcSetUp) CLEAR DLLS * Create a connection. CREATE CONNECTION MYOLV DATASOURCE &quot;myoffline&quot; * Create a view to take offline. CREATE SQL VIEW test REMOTE CONNECT MYOLV ; AS SELECT cust_id, company, city ; FROM customer ; ORDER BY customer.cust_id USE test * Take the view offline. * Creates file named 'Test.dbf'. =CREATEOFFLINE('test') * Close all and open the Table for OffLine view. CLOSE ALL ENDIF

CLOSE ALL CLEAR ALL OPEN DATABASE MYOLV SET MULTILOCKS ON startval=VAL(SYS(1016)) startsysval=VAL(SYS(1011)) FOR i=1 TO 10000 * Issue a SQL Select statement against an Offline View. SELECT * FROM test INTO CURSOR testing * Get the current value of User Object Memory. endval=VAL(SYS(1016)) * Get the current value of Memory Handles. endsysval=VAL(SYS(1011)) * Check the amount of available physical memory. memcheck(endval-startval,endsysval-startsysval) startval=endval startsysval=endsysval WAIT WINDOW &quot;Iteration = &quot; + ALLTRIM(STR(i)) NOWA ENDFOR CLOSE ALL
 * Get the value of User Object Memory.
 * Get the value of Memory Handles.

PROCEDURE memcheck LPARAMETERS userobj, usedhandles lcCurrent=ALIAS * Check for existence of memvals.dbf to store memory values. IF !FILE('memvals.dbf') SELECT 0 CREATE TABLE memvals FREE (availphys N(15,0), fox_mem c(15), usermem i, memhandles i, when_fire T)     USE IN memvals ENDIF * Declare GlobalMemoryStatus API. DECLARE GlobalMemoryStatus IN Win32API STRING @MemStat m.struc = long2str(32) + REPLICATE(CHR(0), 28) * Call GlobalMemoryStatus. =GlobalMemoryStatus(@m.struc) * Get Available Physical Memory. m.availphys = str2long(SUBSTR(m.struc, 13, 4)) INSERT INTO memvals VALUES (m.availphys, SYS(1001), userobj, usedhandles, DATETIME) USE IN memvals IF !EMPTY(lcCurrent) SELECT (lcCurrent) ENDIF CLEAR DLLS RETURN

FUNCTION long2str PARAMETERS m.longval PRIVATE i, m.retstr m.retstr = &quot;&quot; FOR i = 24 TO 0 STEP -8 m.retstr = CHR(INT(m.longval/(2^i))) + m.retstr m.longval = MOD(m.longval, (2^i)) NEXT RETURN m.retstr

FUNCTION str2long PARAMETERS m.longstr PRIVATE i, m.retval m.retval = 0 FOR i = 0 TO 24 STEP 8 m.retval = m.retval + (ASC(m.longstr) * (2^i)) m.longstr = RIGHT(m.longstr, LEN(m.longstr) - 1) NEXT RETURN m.retval  Save and then run the program file. Open and BROWSE the memvals table. Inspect the values that are stored in the table. Note that the availphys column shows a downward trend.  From the Command window, run the following code: SELECT AVG(availphys) AS availphys FROM memvals WHERE RECNO<=30 INTO CURSOR baseline SELECT AVG(availphys) AS availphys FROM MEMVALS WHERE RECNO>=RECCOUNT-30 INTO CURSOR endline ? baseline.availphys-endline.availphys 

