Wiki Home

Sql Bench Marks


Namespace: WIN_COM_API
A common question is: What is the best way to access remote data from VFP? Too bad there is no easy answer, other than: It depends on your requirements.

The scope of this comparison is: SPT, Remote Views and Cursor Adapter. Cursor Adapter is in the list because when I started this, I didn't know enough about what it was and wasn't - If I had, I would have just made this comment: out of the box, Cursor Adapter and Remote Views both use the same internal routines, but each has a different interface, and the Cursor Adapter is a real class that can be subclassed. Cursor Adapter is to Views what VFP is to VB6. However, there is a slight performance hit - about .003 seconds.

I have learned a bit doing this. Trying to figure out how to compare apples to apples has made me realize that the various options are all different in ways that may or may not lend themselves to the job.

For instance, if you need to show the user some data in a grid, let them edit it and then save the data, you will need a VFP cursor so a View is the way to go. If you are collecting data from some odd source and have no need for a VFP cursor, then SPT is may be the way to go.

Also, I am no expert at tuning SQL things. I tired changing a few properties here and there and didn't see much difference. However, even if there are some things that will drastically change the results of this test, that doesn't mean it is the right thing for something else.

My conclusion: Use whatever will be easiest for the developer. Get the project done and delivered. Make sure your server can handle the load and don't worry about what the client is doing.

So run the program, tweek it here and there as you see fit, and draw your own conclusions.

* CmpCnt.prg
* Compare and Contrast various methods of working wtih remote data.
*
* We are going to look at 2 categores: Sending and Retriving; 
* for each, we will use simple - SPT, views and cursor adaptor.  
* for each SPT test, a 'string' version, a parameterized, and a SP that should mimic what a view creates.
* so 3+1+1+3+1+1 = 10 tests total

* for each test: build the db, run a test.  re-building the db each time should prevent caching 'issues'

* It doesn't matter how we make the db, (will use vfp's spt)

* Skip the example code
#IFDEF NULL

* Here is how we are timing things:
SET UP
lnStart = Seconds()
For lnI = 1 to lnTestRecords
	DO THING BEING TESTED
Endfor
CLEAN UP
lnTime = seconds() - lnStart
?? lnTime 
??  lnTime/lnTestRecords, "seconds per Record"

* Here is how we are get around the 255 or whatever limit on the lenght of a command
lcX = "really long string" && won't work
Text To lcX noshow
 really long string
endtext

#ENDIF

* And now, the program.
*********************************************************************************

#DEFINE MYCONSTR "DRIVER=SQL Server;Network=DBMSSOCN;SERVER=tt8k;database=tempdb;uid=sa;pwd=abc"

lnTestRecords = 500
lnRetrives = 500

* clean up from previous runs
clear
Set Safety Off
SQLDisconnect(0)
On Error *
Close Databases
Delete Database foo
On error
If .f.
? "Adding records"
test1()
test2()
test3()
test4()
test5()
Endif

?
? "Retriving..."
test6()
test7()
test8()
test9()
test10()

Return

*********************************************************************************
function Test1()

* SPT - simple string
? "Test 1 SPT a..."
mkDb()

lnX = SQLStringConnect( MYCONSTR )
Text to lcSqlCmd noshow
 Insert into foo 
  (cFid1, cFid2, cFid3, cFid4, cFid5, cFid6, cFid7, cFid8, cFid9, cFid10, cFid11, cFid12, cFid13, cFid14, cFid15, cFid16, cFid17, cFid18, cFid19, cFid20, cFid21, cFid22, cFid23, cFid24, cFid25, cFid26, cFid27, cFid28, cFid29, cFid30, cFid31, cFid32, cFid33, cFid34, cFid35, cFid36, cFid37, cFid38, cFid39, cFid40, cFid41, cFid42, cFid43, cFid44, cFid45, cFid46, cFid47, cFid48, cFid49, cFid50)
  values ( 'A1', 'A2', 'A3', 'A4', 'A5', 'A6', 'A7', 'A8', 'A9', 'A10', 'A11', 'A12', 'A13', 'A14', 'A15', 'A16', 'A17', 'A18', 'A19', 'A20', 'A21', 'A22', 'A23', 'A24', 'A25', 'A26', 'A27', 'A28', 'A29', 'A30', 'A31', 'A32', 'A33', 'A34', 'A35', 'A36', 'A37', 'A38', 'A39', 'A40', 'A41', 'A42', 'A43', 'A44', 'A45', 'A46', 'A47', 'A48', 'A49', 'A50' )
endtext

lnStart = Seconds()
For lnI = 1 to lnTestRecords

	SQLExec( lnX, lcSqlCmd )

Endfor

SQLDisconnect(lnX)

lnTime = seconds() - lnStart
?? lnTime 
??  lnTime/lnTestRecords, "seconds per Record"
return

* lcSqlCmd = "Insert into foo (cFid1) values ('a1')"

*********************************************************************************
function Test2()
? "Test 2 SPT b..."

mkDb()
lnX = SQLStringConnect( MYCONSTR )
Text to lcSqlCmd noshow
 Insert into foo 
  (cFid1, cFid2, cFid3, cFid4, cFid5, cFid6, cFid7, cFid8, cFid9, cFid10, cFid11, cFid12, cFid13, cFid14, cFid15, cFid16, cFid17, cFid18, cFid19, cFid20, cFid21, cFid22, cFid23, cFid24, cFid25, cFid26, cFid27, cFid28, cFid29, cFid30, cFid31, cFid32, cFid33, cFid34, cFid35, cFid36, cFid37, cFid38, cFid39, cFid40, cFid41, cFid42, cFid43, cFid44, cFid45, cFid46, cFid47, cFid48, cFid49, cFid50)
  values ( ?lcFid1, ?lcFid2, ?lcFid3, ?lcFid4, ?lcFid5, ?lcFid6, ?lcFid7, ?lcFid8, ?lcFid9, ?lcFid10, ?lcFid11, ?lcFid12, ?lcFid13, ?lcFid14, ?lcFid15, ?lcFid16, ?lcFid17, ?lcFid18, ?lcFid19, ?lcFid20, ?lcFid21, ?lcFid22, ?lcFid23, ?lcFid24, ?lcFid25, ?lcFid26, ?lcFid27, ?lcFid28, ?lcFid29, ?lcFid30, ?lcFid31, ?lcFid32, ?lcFid33, ?lcFid34, ?lcFid35, ?lcFid36, ?lcFid37, ?lcFid38, ?lcFid39, ?lcFid40, ?lcFid41, ?lcFid42, ?lcFid43, ?lcFid44, ?lcFid45, ?lcFid46, ?lcFid47, ?lcFid48, ?lcFid49, ?lcFid50 )
EndText
Store "Ax" to lcFid1, lcFid2, lcFid3, lcFid4, lcFid5, lcFid6, lcFid7, lcFid8, lcFid9, lcFid10, lcFid11, lcFid12, lcFid13, lcFid14, lcFid15, lcFid16, lcFid17, lcFid18, lcFid19, lcFid20, lcFid21, lcFid22, lcFid23, lcFid24
Store "Ay" to lcFid25, lcFid26, lcFid27, lcFid28, lcFid29, lcFid30, lcFid31, lcFid32, lcFid33, lcFid34, lcFid35, lcFid36, lcFid37, lcFid38, lcFid39, lcFid40, lcFid41, lcFid42, lcFid43, lcFid44, lcFid45, lcFid46, lcFid47, lcFid48, lcFid49, lcFid50

lnStart = Seconds()
For lnI = 1 to lnTestRecords

	SQLExec( lnX, lcSqlCmd )
	
Endfor

SQLDisconnect(lnX)

lnTime = seconds() - lnStart
?? lnTime 
??  lnTime/lnTestRecords, "seconds per Record"
return


*********************************************************************************
Function test3()
? "Test 3 SPT c..."
mkDb()

lnX = SQLStringConnect( MYCONSTR )
Text to lcSqlCmd noshow
 exec sp_executesql 
  N'INSERT INTO foo (cFid1,cFid2,cFid3,cFid4,cFid5,cFid6,cFid7,cFid8,cFid9,cFid10,cFid11,cFid12,cFid13,cFid14,cFid15,cFid16,cFid17,cFid18,cFid19,cFid20,cFid21,cFid22,cFid23,cFid24,cFid25,cFid26,cFid27,cFid28,cFid29,cFid30,cFid31,cFid32,cFid33,cFid34,cFid35,cFid36,cFid37,cFid38,cFid39,cFid40,cFid41,cFid42,cFid43,cFid44,cFid45,cFid46,cFid47,cFid48,cFid49,cFid50) 
  VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21,@P22,@P23,@P24,@P25,@P26,@P27,@P28,@P29,@P30,@P31,@P32,@P33,@P34,@P35,@P36,@P37,@P38,@P39,@P40,@P41,@P42,@P43,@P44,@P45,@P46,@P47,@P48,@P49,@P50)',
  N'@P1 char(10),@P2 char(10),@P3 char(10),@P4 char(10),@P5 char(10),@P6 char(10),@P7 char(10),@P8 char(10),@P9 char(10),@P10 char(10),@P11 char(10),@P12 char(10),@P13 char(10),@P14 char(10),@P15 char(10),@P16 char(10),@P17 char(10),@P18 char(10),@P19 char(10),@P20 char(10),@P21 char(10),@P22 char(10),@P23 char(10),@P24 char(10),@P25 char(10),@P26 char(10),@P27 char(10),@P28 char(10),@P29 char(10),@P30 char(10),@P31 char(10),@P32 char(10),@P33 char(10),@P34 char(10),@P35 char(10),@P36 char(10),@P37 char(10),@P38 char(10),@P39 char(10),@P40 char(10),@P41 char(10),@P42 char(10),@P43 char(10),@P44 char(10),@P45 char(10),@P46 char(10),@P47 char(10),@P48 char(10),@P49 char(10),@P50 char(10)',
  'A1        ', 'A2        ', 'A3        ', 'A4        ', 'A5        ', 'A6        ', 'A7        ', 'A8        ', 'A9        ', 'A10       ', 'A11       ', 'A12       ', 'A13       ', 'A14       ', 'A15       ', 'A16       ', 'A17       ', 'A18       ', 'A19       ', 'A20       ', 'A21       ', 'A22       ', 'A23       ', 'A24       ', 'A25       ', 'A26       ', 'A27       ', 'A28       ', 'A29       ', 'A30       ', 'A31       ', 'A32       ', 'A33       ', 'A34       ', 'A35       ', 'A36       ', 'A37       ', 'A38       ', 'A39       ', 'A40       ', 'A41       ', 'A42       ', 'A43       ', 'A44       ', 'A45       ', 'A46       ', 'A47       ', 'A48       ', 'A49       ', 'A50       '
endtext

lnStart = Seconds()
For lnI = 1 to lnTestRecords

	SQLExec( lnX, lcSqlCmd )

Endfor

SQLDisconnect(lnX)

lnTime = seconds() - lnStart
?? lnTime 
??  lnTime/lnTestRecords, "seconds per Record"
Return


*********************************************************************************
function Test4()
? "Test 4 View..."
mkDb()

Create Database foo
Create Connection dbccon connstring MYCONSTR
CREATE SQL VIEW v_foo REMOTE CONNECTION dbccon AS ;
		select * from foo
DBSetProp("v_foo", 'View', 'SendUpdates',.T.)

USE v_foo nodata

lnStart = Seconds()
For lnI = 1 to lnTestRecords

	Insert into v_foo ;
		(cFid1, cFid2, cFid3, cFid4, cFid5, cFid6, cFid7, cFid8, cFid9, cFid10, cFid11, cFid12, cFid13, cFid14, cFid15, cFid16, cFid17, cFid18, cFid19, cFid20, cFid21, cFid22, cFid23, cFid24, cFid25, cFid26, cFid27, cFid28, cFid29, cFid30, cFid31, cFid32, cFid33, cFid34, cFid35, cFid36, cFid37, cFid38, cFid39, cFid40, cFid41, cFid42, cFid43, cFid44, cFid45, cFid46, cFid47, cFid48, cFid49, cFid50);
		values ( 'A1', 'A2', 'A3', 'A4', 'A5', 'A6', 'A7', 'A8', 'A9', 'A10', 'A11', 'A12', 'A13', 'A14', 'A15', 'A16', 'A17', 'A18', 'A19', 'A20', 'A21', 'A22', 'A23', 'A24', 'A25', 'A26', 'A27', 'A28', 'A29', 'A30', 'A31', 'A32', 'A33', 'A34', 'A35', 'A36', 'A37', 'A38', 'A39', 'A40', 'A41', 'A42', 'A43', 'A44', 'A45', 'A46', 'A47', 'A48', 'A49', 'A50' )

Endfor
TableUpdate(1)

lnTime = seconds() - lnStart
?? lnTime 
??  lnTime/lnTestRecords, "seconds per Record"
Return


*********************************************************************************
function Test5()
? "Test 5 - CA..."

Local loDO as CursorAdapter
mkDb()
lodo = CreateObject("CursorAdapter")
lodo.DataSourceType="ODBC"
lodo.DataSource = SQLStringConnect( MYCONSTR )
lodo.Tables="foo"
lodo.SelectCmd="select * from foo"
Text to lodo.UpdatableFieldList noshow
 cFid1, cFid2, cFid3, cFid4, cFid5, cFid6, cFid7, cFid8, cFid9, cFid10, cFid11, cFid12, cFid13, cFid14, cFid15, cFid16, cFid17, cFid18, cFid19, cFid20, cFid21, cFid22, cFid23, cFid24, cFid25, cFid26, cFid27, cFid28, cFid29, cFid30, cFid31, cFid32, cFid33, cFid34, cFid35, cFid36, cFid37, cFid38, cFid39, cFid40, cFid41, cFid42, cFid43, cFid44, cFid45, cFid46, cFid47, cFid48, cFid49, cFid50
EndText 
lodo.KeyFieldList = "kFoo_pk"
Text to lodo.UpdateNameList noshow
 cFid1 foo.cFid1, cFid2 foo.cFid2, cFid3 foo.cFid3, cFid4 foo.cFid4, cFid5 foo.cFid5, cFid6 foo.cFid6, cFid7 foo.cFid7, cFid8 foo.cFid8, cFid9 foo.cFid9, cFid10 foo.cFid10, cFid11 foo.cFid11, cFid12 foo.cFid12, cFid13 foo.cFid13, cFid14 foo.cFid14, cFid15 foo.cFid15, cFid16 foo.cFid16, cFid17 foo.cFid17, cFid18 foo.cFid18, cFid19 foo.cFid19, cFid20 foo.cFid20, cFid21 foo.cFid21, cFid22 foo.cFid22, cFid23 foo.cFid23, cFid24 foo.cFid24, cFid25 foo.cFid25, cFid26 foo.cFid26, cFid27 foo.cFid27, cFid28 foo.cFid28, cFid29 foo.cFid29, cFid30 foo.cFid30, cFid31 foo.cFid31, cFid32 foo.cFid32, cFid33 foo.cFid33, cFid34 foo.cFid34, cFid35 foo.cFid35, cFid36 foo.cFid36, cFid37 foo.cFid37, cFid38 foo.cFid38, cFid39 foo.cFid39, cFid40 foo.cFid40, cFid41 foo.cFid41, cFid42 foo.cFid42, cFid43 foo.cFid43, cFid44 foo.cFid44, cFid45 foo.cFid45, cFid46 foo.cFid46, cFid47 foo.cFid47, cFid48 foo.cFid48, cFid49 foo.cFid49, cFid50 foo.cFid50
EndText 
lodo.CursorFill( , .t.)  && no data

lnStart = Seconds()
For lnI = 1 to lnTestRecords

	Insert into (lodo.Alias) ;
		(cFid1, cFid2, cFid3, cFid4, cFid5, cFid6, cFid7, cFid8, cFid9, cFid10, cFid11, cFid12, cFid13, cFid14, cFid15, cFid16, cFid17, cFid18, cFid19, cFid20, cFid21, cFid22, cFid23, cFid24, cFid25, cFid26, cFid27, cFid28, cFid29, cFid30, cFid31, cFid32, cFid33, cFid34, cFid35, cFid36, cFid37, cFid38, cFid39, cFid40, cFid41, cFid42, cFid43, cFid44, cFid45, cFid46, cFid47, cFid48, cFid49, cFid50);
		values ( 'A1', 'A2', 'A3', 'A4', 'A5', 'A6', 'A7', 'A8', 'A9', 'A10', 'A11', 'A12', 'A13', 'A14', 'A15', 'A16', 'A17', 'A18', 'A19', 'A20', 'A21', 'A22', 'A23', 'A24', 'A25', 'A26', 'A27', 'A28', 'A29', 'A30', 'A31', 'A32', 'A33', 'A34', 'A35', 'A36', 'A37', 'A38', 'A39', 'A40', 'A41', 'A42', 'A43', 'A44', 'A45', 'A46', 'A47', 'A48', 'A49', 'A50' )
	TableUpdate(1,.t.,lodo.Alias)

EndFor
lnTime = seconds() - lnStart

SQLDisconnect(lodo.DataSource)

?? lnTime 
??  lnTime/lnTestRecords, "seconds per Record"
Return 

*********************************************************************************
function Test6()
? "Test 6 SPT a..."

mkDb(.t.)
lnX = SQLStringConnect( MYCONSTR )
lcSqlCmd = "select * from foo where kFoo_pk = 1"
	SQLPre( lnX, lcSqlCmd ) 

lnStart = Seconds()
For lnI = 1 to lnRetrives 

	SQLExec( lnX )

Endfor

SQLDisconnect(lnX)

lnTime = seconds() - lnStart
?? lnTime 
??  lnTime/lnRetrives, "seconds per query"
return


*********************************************************************************
function Test7()
? "Test 7 SPT b..."

mkDb()
lnX = SQLStringConnect( MYCONSTR )
lcSqlCmd = "select * from foo where kFoo_pk = ?lkX"
lkX = 1

lnStart = Seconds()
For lnI = 1 to lnRetrives 

	SQLExec( lnX, lcSqlCmd )

Endfor

SQLDisconnect(lnX)

lnTime = seconds() - lnStart
?? lnTime 
?? lnTime/lnRetrives, "seconds per query"
return


*********************************************************************************
Function test8()
? "Test 8 SPT c..."
?? "(nothing to test.)" && Same as 8a
Return

*********************************************************************************
function Test9()
? "Test 9 View..."

mkDb(.t.)
On Error *
Close Databases
Delete Database foo
On error
Create Database foo
Create Connection dbccon connstring MYCONSTR
CREATE SQL VIEW v_foo REMOTE CONNECTION dbccon AS ;
		select * from foo where kFoo_pk=1
USE v_foo nodata

lnStart = Seconds()
For lnI = 1 to lnRetrives 

	Requery()

Endfor

lnTime = seconds() - lnStart
?? lnTime 
??  lnTime/lnRetrives, "seconds per query"
return

*********************************************************************************
function Test10()
? "Test 10 - CA..."

Local loDO as CursorAdapter
mkDb(.t.)
lodo = CreateObject("CursorAdapter")
lodo.DataSourceType="ODBC"
lodo.DataSource=SQLStringConnect( MYCONSTR )
lodo.SelectCmd="select * from foo where kFoo_pk=1"

lnStart = Seconds()
For lnI = 1 to lnRetrives 

	lodo.CursorFill()

EndFor
lnTime = seconds() - lnStart

SQLDisconnect(lodo.DataSource)

?? lnTime 
??  lnTime/lnRetrives, "seconds per query"
Return 


*********************************************************************************
Function mkDb( tlAddData )

lnX = SQLStringConnect( MYCONSTR )
SQLExec( lnX, "drop table foo" )
Text to lcSqlCmd NOSHOW 
 create table foo (kFoo_pk int IDENTITY primary key,
	cFid1 char(10), cFid2 char(10), cFid3 char(10), cFid4 char(10), cFid5 char(10), 
	cFid6 char(10), cFid7 char(10), cFid8 char(10), cFid9 char(10), cFid10 char(10), 
	cFid11 char(10), cFid12 char(10), cFid13 char(10), cFid14 char(10), cFid15 char(10), 
	cFid16 char(10), cFid17 char(10), cFid18 char(10), cFid19 char(10), cFid20 char(10), 
	cFid21 char(10), cFid22 char(10), cFid23 char(10), cFid24 char(10), cFid25 char(10), 
	cFid26 char(10), cFid27 char(10), cFid28 char(10), cFid29 char(10), cFid30 char(10), 
	cFid31 char(10), cFid32 char(10), cFid33 char(10), cFid34 char(10), cFid35 char(10), 
	cFid36 char(10), cFid37 char(10), cFid38 char(10), cFid39 char(10), cFid40 char(10), 
	cFid41 char(10), cFid42 char(10), cFid43 char(10), cFid44 char(10), cFid45 char(10), 
	cFid46 char(10), cFid47 char(10), cFid48 char(10), cFid49 char(10), cFid50 char(10) )
endtext

SQLExec( lnX, lcSqlCmd )

If tlAddData
	Text to lcSqlCmd noshow
	 Insert into foo 
	  (cFid1, cFid2, cFid3, cFid4, cFid5, cFid6, cFid7, cFid8, cFid9, cFid10, cFid11, cFid12, cFid13, cFid14, cFid15, cFid16, cFid17, cFid18, cFid19, cFid20, cFid21, cFid22, cFid23, cFid24, cFid25, cFid26, cFid27, cFid28, cFid29, cFid30, cFid31, cFid32, cFid33, cFid34, cFid35, cFid36, cFid37, cFid38, cFid39, cFid40, cFid41, cFid42, cFid43, cFid44, cFid45, cFid46, cFid47, cFid48, cFid49, cFid50)
	  values ( 'A1', 'A2', 'A3', 'A4', 'A5', 'A6', 'A7', 'A8', 'A9', 'A10', 'A11', 'A12', 'A13', 'A14', 'A15', 'A16', 'A17', 'A18', 'A19', 'A20', 'A21', 'A22', 'A23', 'A24', 'A25', 'A26', 'A27', 'A28', 'A29', 'A30', 'A31', 'A32', 'A33', 'A34', 'A35', 'A36', 'A37', 'A38', 'A39', 'A40', 'A41', 'A42', 'A43', 'A44', 'A45', 'A46', 'A47', 'A48', 'A49', 'A50' )
	endtext
	For lnI = 1 to lnTestRecords
		SQLExec( lnX, lcSqlCmd )
	Endfor
EndIf

SQLDisconnect(lnx)

Return

*********************************************************************************
Function mkFidList()
* Handy code to help generate the list of fields.
Local lcFidLst, lcFldNam 
lcFidLst = ""
For lnI = 1 to 50
	lcFldNam = "cFid" + Transform( lnI )
*	lcFidLst = lcFidLst + Iif( Empty(lcFidLst), "", ", " ) + lcFldNam
*	lcFidLst = lcFidLst + Iif( Empty(lcFidLst), "", ", " ) + lcFldNam + " char(10)"
*	lcFidLst = lcFidLst + Iif( Empty(lcFidLst), "", ", " ) + "'A" + Transform( lnI ) + "'"
*	lcFidLst = lcFidLst + Iif( Empty(lcFidLst), "", ", " ) + lcFldNam + " foo." + lcFldNam 
*	lcFidLst = lcFidLst + Iif( Empty(lcFidLst), "", ", " ) + "?l" + lcFldNam
*	lcFidLst = lcFidLst + Iif( Empty(lcFidLst), "", ", " ) + "l" + lcFldNam
	lcFidLst = lcFidLst + Iif( Empty(lcFidLst), "", ", " ) + lcFldNam + " with " +  "'A" + Transform( lnI ) + "'"
EndFor
_cliptext = lcFidLst
Return
*********************************************************************************

Contributors Carl Karsten
( Topic last updated: 2003.07.02 12:28:59 PM )