>I am working with vfp8 on a win2k machine.
>
>I have a DLL which a C program calls and passes in a string that contains all the info to be entered in the SQL server table – table name is noted in the string.
> The programs I have built chops up the string into the pieces needed to figure out what table it goes into, and what action is being done to the table – add, change or delete record - I do not do a change, I delete that record and add it again. Some table actions require other tables to be changed also at that time – my code knows which ones.
> The problem is that my code takes more then twice as long as the code for just straight vfp code – one table went from 1.1 minutes to 5.03 minutes, another went from 7 seconds to 30 seconds.
> I have no DO WHILE loops in the programs. I have a lot of CASE statements and IF statements. I prep the data, split out the items needed from the string in one program, then call the next program that does the actual insert and/or delete in the tables as needed.
>
>Is this enough info for someone to help me, or should I put some of the code out?
>
>Thanks in advance.
>Beth
Beth,
I suggest you to reread OpenRowSet() sample. If you don't change anything but do a delete/add then your command turns out to be:
delete from myTable where pkid in (select pkid from openrowset(...))
insert into myTable (select < fieldlist > from openrowset(...))
or:
select * from openrowset(...) into table #temp
delete from myTable where pkid in (select pkid from #temp where action in ('change','delete'))
insert into myTable (select < fieldlist > from #temp where action in ('change','add'))
Also search for "bulk/copy" here on UT and bcp/bulk insert in SQL server books online.
Constraints and existing indexes might be a factor in slowdown. Bulk copy operations have hints (ie:default constraint checking is off). If you're doing insert/update/delete that'd affect multiple tables then consider doing them in a order that even if you turnoff constraints checking you'd not be beating integrity/concurrency.
You might try with VFP remote views or updatable SPT cursors too if you can define a set to work on - you might well use openrowset() for that (table buffered if you try).
Cetin