VFP 9 ->SQL Srv 2K, using SQLCOMMIT not faster

[SteveDingle]

Heya All,

A while back my client came to me saying the current process of 'migrating' about 250,000 records from a coupla of VFP table to SQL Server 2K was taking too long. Current between 30-40 min. Initially I was setting transactions 1 Auto (letting SQL Server handling them) and sending 1 Insert/Update/Delete at a time. So I thought I would try instead using Transactions = 2 and SQLCOMMIT. I thought this would help but instead it was actually taking longer.

So I have a coupla questions:

1) Anyone have an idea of what times I can expect if all is set up correctly. The tables have quite a few fields (79 for one table)

2) Are there any SQL Server/VFP settings I can change which might help

I should add I am build the INSERT/UPDATE/DELETE commands on the fly using my own class and then submitting them using SQLEXEC(). I am going to start tracing through my code to see if I can refactor some processes but thought I would start a thread here to see if there is anything else I can do

Thanks for any and all thoughts
Steve

 

[AndyKr]

Steve

This is completely counter to anything that I have ever seen. I routinely do this sort of thing in applications I work on and have never seen a manual transaction be slower than an automatic (Ceil made this change recently and her 40,000 record insert whent from 20 mins to 5 mins!)

Somethiong else is going on here but without seeing any code its going to be tought to know what.

----
Andy Kramek
Visual FoxPro MVP

 

[SteveDingle]

Heya Andy,

Well I'm currently going through my data classes, just changing how the SQLExec/SQLCommit are handled. Suppose what I need to do is get it down to the basics where I'm not using my classes and have it all in a prg

Thanks for the input

Toodles,
Steve Dingle

http://www.dsbusinesssolutions.co.uk

D&S Business Solutions Ltd

 

[AndyKr]

Steve

I just tested something locally, Adding 9649 records using SQLEXEC to a table in SQL I got the following results
multiple runs of the code)

With Automatic Transactions: 20 - 22 seconds
With Manual Transactions: 13 - 15 seconds

As you can see, the Manual Transactions are significantly faster.Of course the COMMIT takes a little time (remember I said that this was a trade-off - the more records you send before committing, the longer the COMMIT takes and the more you have to re-do if it fails. But this is almost 10000 records (which is about as much as I would want to do)

Here is the actual code I used:
LOCAL lnCon, lnRes, lcSql
*** Attempt a connection
lnCon = SQLCONNECT( 'Admin_whouse' )
IF lnCon < 1
MESSAGEBOX( "Unable to connect to Admin_whouse", 16, "Login Failed" )
RETURN
ELSE
lnRes = SQLEXEC( lnCon, [TRUNCATE TABLE idwex01_ppt_det] )
IF lnRes < 1
AERROR(laErr)
MESSAGEBOX( laErr[2], 16, 'Truncate Failed' )
SQLDISCONNECT( lnCon )
RETURN
ELSE
lnRes = SQLSETPROP( lnCon, "Transactions", 2 )
IF lnRes # 1
AERROR(laErr)
MESSAGEBOX( laErr[2], 16, 'Settings Failed' )
SQLDISCONNECT( lnCon )
RETURN
ENDIF
ENDIF
ENDIF

IF NOT USED( 'idwex01_ppt_det' )
USE idwex01_ppt_det IN 0
ENDIF
SELECT idwex01_ppt_det
GO TOP
lcStat = IIF( SQLGETPROP( lnCon,"Transactions" ) = 1, "Automatic", "Manual" )
lnSt = SECONDS()
SCAN
TEXT TO lcSQL NOSHOW PRETEXT 15
INSERT INTO idwex01_ppt_det ( transmitted_dte, corp_rtrmt_plan_id, ssn, int_planid, int_prtid )
VALUES ( '20070730', '<<corp_rtrmt_plan_id>>', '<<ssn>>', <<int_planid>>, <<int_prtid>> )
ENDTEXT
lnRes = SQLEXEC( lnCon, lcSql )
IF lnRes < 1
AERROR(laErr)
MESSAGEBOX( laErr[2], 16, 'Insert Failed' )
SQLDISCONNECT( lnCon )
RETURN
ENDIF
ENDSCAN
lnEn = SECONDS()
IF lcStat = 'Manual'
SQLCOMMIT( lnCon )
ENDIF
? "Added " + TRANSFORM( RECCOUNT()) + " records with " + lcStat + ' Transactions in ' + STR( lnEn - lnSt, 8, 4 ) + " seconds"
*** Disconnect
SQLDISCONNECT( lnCon )





----
Andy Kramek
Visual FoxPro MVP

 

[SteveDingle]

Heya Andy,

Below are the numbers I got using my test table. As you can see I don't see that drastic difference tho it is lass time fer sure. Basically this is inserting 10K records, table has 79 fields and I did use AFIELDS in a loop and a String Converion routine to build the insert commands but other than that the only diffent is single has transaction as 1, batch has transaction as 2 using SQLCOMMIT

Wondering if there is a SQL Server setting I'm missing, I am really ignorant when it come to the specifics of configuring.

Single - Direct:
36.996
38.947
36.444
39.202

Batch - Direct:
32.128
36.113
32.103
33.966



Toodles,
Steve Dingle

http://www.dsbusinesssolutions.co.uk

D&S Business Solutions Ltd

 

[AndyKr]

>>Wondering if there is a SQL Server setting I'm missing

Can't think of one off-hand, though one possibility is the transaction handling model.

What "Recovery Model" are you using? If it's "FULL" then you could try changing it to "Simple" that should also speed things up. (You can find out by checking the "Properties" tab for the database - right click and choose properties, then "options")

----
Andy Kramek
Visual FoxPro MVP

 

[SteveDingle]

Sorry for the long post but I need a still trying to figure out why I'm not seeing much difference between using Manual vs Auto Transactions. The connection is create using SQLCONNECT( DSN Name ). Default used except for transactions, changed to 2.

Hoping maybe someone could see if the logic is flawed. The SingleDirect is basically the same except transaction = 1 and there is no Transaction Loop or SQLCOMMIT used.

- Table has 79 fields of varying types.

m.liRecords = 10000 (Records to insert)
m.liTranCount = 500 (Record to include in transaction)
m.liTestLoop = 10 (Number of time to run test)

Auto Manual
------ -----
22.159 21.096
20.408 25.621
23.510 26.185
29.278 27.898
26.104 23.408
31.481 26.745
31.439 24.514
30.147 26.756
28.288 27.235
27.479 27.783

=SQLSETPROP(m.liHandle,"Transactions",2)
m.lcType = "Batch - Direct"

FOR m.liTestLoop = 1 TO m.liTestLoops
	*-- Position the record pointer
	SELECT (m.lcTable)
	GO BOTTOM
	SKIP -(m.liRecords)

	*-- Clear the table
	=SQLEXEC(m.liHandle,"TRUNCATE TABLE "+m.lcTable)
	=SQLCOMMIT(m.liHandle)

	m.liSeconds	= SECONDS()
	=BatchDirect(m.liHandle)
	DEBUGOUT m.lcType+": "+TRANSFORM( SECONDS() - m.liSeconds )
ENDFOR

**** 
BatchDirect( tiHandle )
****
FOR m.liTranLoop = 1 TO m.liRecords/m.liTranCount

	SCAN NEXT m.liTranCount

		m.lcFields = ""
		m.lcValues = ""
      FOR m.liField = 1 TO m.liFieldCount
         m.lcFields = m.lcFields + ALLT( laFields[ m.liField, 1])+", "
         m.lcValues = m.lcValues +;
         				m.loStrings.ConvertValueToString( EVALUATE(ALLT( laFields[ m.liField, 1])),;
         														  dcSTR_VAL_TO_STR_SQL_SRV,;
         														  laFields[ m.liField, 2] )+", "
      ENDFOR

      *-- Strip off extra comma
      m.lcFields = LEFT(ALLT(m.lcFields),LEN(ALLT(m.lcFields))-1)
      m.lcValues = LEFT(ALLT(m.lcValues),LEN(ALLT(m.lcValues))-1)
		      
      *-- Build the command
      m.lcInsert = "INSERT INTO "+ALLT( m.lcTable )+"( "
      m.lcInsert = m.lcInsert + m.lcFields+") "
      m.lcInsert = m.lcInsert + " VALUES ( " + m.lcValues + ")"

		*-- Trap for error
		IF SQLEXEC( tiHandle , m.lcInsert) < 1
			m.llCommit = .F.
			EXIT
		ENDIF

	ENDSCAN
	SKIP IN (m.lcTable)

	*-- Commit
	IF m.llCommit 
		IF SQLCOMMIT( tiHandle ) < 1
			m.llCommit = .F.
			SQLROLLBACK( tiHandle )
			EXIT
		ENDIF	
	ELSE
		SQLROLLBACK( tiHandle )
		EXIT
	ENDIF
ENDFOR

Thanks for any and all input



Toodles,
Steve Dingle

http://www.dsbusinesssolutions.co.uk

D&S Business Solutions Ltd

 

[AndyKr]

Steve

Have you timed the different parts of this process? I suspect that the SQL end is the fastest part of the whole thing, and that in fact what is slowing you down is all your VFP code.

First, why are you building the entire INSERT string for each row?

The column names can't change during processing so why not build the SQL String in two parts - first (outside the SCAN loop) build the field list, then, inside the loop just build the VALUES string.

I am not sure about anything else, off-hand, but you are making an awfully large function call here, an awfully large number of times...

m.loStrings.ConvertValueToString( EVALUATE(ALLT( laFields[ m.liField, 1])),dcSTR_VAL_TO_STR_SQL_SRV,laFields[ m.liField, 2] )+", "

Do you really need the EVALUATE() in there? Again you may be better off just getting these values into variables directly. (I don't know, but I do know that that is how I have always done it...)




----
Andy Kramek
Visual FoxPro MVP

 

[SteveDingle]

Heya Andy,

>> why are you building the entire INSERT string for each >> row?

Basically because this code was copy/pasted from my Data classes which doesn't know before hand which table it'll be inserting/updating/deleting. Is a generic method when passed table name etc.. builds things on the fly. Could change that for this circumstance of course so thank for the pointer..

>> Do you really need the EVALUATE() in there?
Again, from class so can change for this testing. Unfortunately even with the fact this being slow code, it would be slow for both tests and not explain no difference between Manual/Auto

FWIW, have just had Volker run a tests on his setup (he's been away on holiday) and he sees about 25% difference using exact same code/data and we think we may have determined the issue... been having problems with my server so the SQL Server I am inserting into is on the same PC. So we're thinking that part of the savings might be the network traffic which for me doesn't exist. Dunno, he's currently testing that for me now

Thanks as always

 

[SteveDingle]

Just to let ya know the results for completions sake. It turns out there was an almost 50% speed increase when Volker compared Manual/Auto transaction using the same machine. So there must be something wrong with how my SQL Server is setup, can't image i's anything else as he was using the exact same dataset/prg etc...