I have a program written in VFP 9 which uses Sql Pass Through to Sql Server 2000. It is creating an "inventory" reference table of all our web artwork. Since there are 1.5 million files involved and we expect the number to grow considerably we choose to use SQL Server as the repository.

When I started the application it was inserting records at 500 per second, now with 300,000 records it is slowed to 5 per second. The VFP app is taking negligable CPU time so I can only assume that SQL Server is dragging it's butt.

The code first attempts an UPDATE which is structured to raise and error on the Server if the record is not found, and when the error occurs VFP program will execute an INSERT statement. The where clause of the update statement uses an index which is not the primary key.

The code is as follows:

		Set Textmerge On To Memvar lcSql Noshow
			\\update coverart set
			\\ isbn10 = '<<TRIM(lcIsbn)>>',
			\\ isbn13 = '<<TRIM(lcIsbn13)>>',
		If Not Empty(lcJFilename)
				\\JFilename = '<<lcJfilename+lcfilename>>'
		Else
				\\JFilename = '<<lcJfilename+lcfilename>>'
		Endif
			\ where isbn13 = '<<TRIM(lcIsbn13)>>'
			\IF @@ROWCOUNT = 0
			\RAISERROR ('No Column was Updated/Deleted', 16, 1)
		Set Textmerge To

		If SQLExec(This.nSql,lcSql) < 0 
			Set Textmerge On To Memvar lcSql Noshow
				\\insert into coverart (isbn10,isbn13,jfilename,tFilename) values (
				\\ '<<TRIM(lcIsbn)>>','<<TRIM(lcIsbn13)>>',
			If Not Empty(lcJFilename)
					\\'<<lcJfilename+lcfilename>>',''
			Else
					\\'','<<lcTfilename+lcfilename>>'
			Endif
				\\ )
			Set Textmerge To
			lnOk = SQLExec(This.nSql,lcSql)
		Endif

Is it typical that the SQL Server would experience such a slowdown upon heavy loading such as this?

Thanks

Glenn