batch Mode into SQL Srv

[SteveDingle]

Heya All,

Working on a project where I'm updating data (inserts,updates,deletes) fom VFP 9 to SQL Srv 2K. Using SQL -Pass-Thru.

We're averaging about 250K records a day. I create a log of any failures at a detailed level (command used, primary key, records detaileds etc..). Because of that I am committing the data 1 record at a time (i.e. non-batch mode).

I was wondering if I wanted to use batch mode would it..

a) tell me which 3 of say 50 inserts failed, (for instance) or would I just get "failure"

b) improve performance (it's currently taking about 30 min client was hoping for quicker)



Toodles,
Steve Dingle

http://www.dsbusinesssolutions.co.uk

D&S Business Solutions Ltd

 

[Mike Lewis]

Hi Steve,

When you talk about batch mode, do you mean sending a whole stream of separate update commands in one call to SQLEXEC? In other words, you have a series of UPDATE commands, each of which updates one record, but you are concanating them into a single command for sending to the server?

Or, do you mean that you will write a single UPDATE that updates many records (by virtue of its WHERE clause)?

Either way, there's no question you'll get much better performance than sending each UPDATE from a different SQLEXEC call. The overhead for SQLEXEC is signficant.

But you'll get way better performance with the second option than with the first. The time taken for SQL Server to receive a command, and compile and optimise it, is high compared to the time it takes to execute.

As far as logging the failures are concerned, that's more difficult. If by a failure, you mean an error reported by SQLEXEC(), there's not much you can do about that. The call will fail on the first failed update within the batch.

Is it possible to log the failures within SQL Server? In other words, write a stored procedure that receives, as parameters, the data to be updated. The SP will then try the updates, test the result of each on individually, and write any failures to a log table, which you can later download into your application.

Also, keep in mind that, when you send an update that hits many records, the command is automatically wrapped in a transaction. If one of the updates fails, the whole command will be rolled back.

I hope all this makes sense. If I have misunderstood your requirements, let me know.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[SteveDingle]

Heya Mike,

Am talking about a stream of individual update/delete/insert command.

Basically we have to databases - 1 VFP , 1 SQL Server.

We have a log which record when a change is made to an of the VFP table - the change (I/U/D), table, primary key. My migrator then looks the record in SQL Server, if it doesn't exist, insert, if it does, an update is done. An in some cases a Delete.

>> As far as logging the failures are concerned
Don't mind about getting a error back, currently doing that and recording all information into a log file. The problem I do have is say I send a single SQLEXEC() which contains 50 Update commands. For whatever reason (like a constraints failure or something) 3 of the updates fail. I need to know WHICH records failed and why. Not just that "something failed"

Hope that makes sense

>> Is it possible to log the failures within SQL Server?
That would might mean a big re-write on how the migrating works and its only a temporary measure while they move to SQL Server backend. Will think about it tho.

Thanks



Toodles,
Steve Dingle

http://www.dsbusinesssolutions.co.uk

D&S Business Solutions Ltd

 

[Mike Lewis]

Steve,

The problem I do have is say I send a single SQLEXEC() which contains 50 Update commands. For whatever reason (like a constraints failure or something) 3 of the updates fail. I need to know WHICH records failed and why. Not just that "something failed"

Yeah, this is the difficulty. As far as I know, the only way would be to do what you are doing at the moment, which is to send them individually ... which is much slower.

I can't off-hand think of a better solution that wouldn't involve a big re-design. But I'll ponder it, and come back if I have any thoughts.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk