Breaking down job

[dorandoran]

I am updating local sql server based on web services data. When I do a query how many updates I have to make it comes to be more than 4000 records. Now the problem is time-out issue and so many other issues.

Is there a way to break the job down into pieces. Like 500 records at a time. I have added a counter and I dont how am I going to tell the system do 500 at a time.

[cloud255]

This is really not too much of an issue, post some code and we'll help you.

All you really need is a loop with a bit of logic ...

[dorandoran]

Oh, the code is long. I am going to upload the code in text format and let me know if you could please help. I am truly frustrated by the webservices.

Here is the text file

http://www.a2zlancer.c om/test/update_local_sl ip_table.txt

Thanks
Julie Jones

[Curtis Rutland]

Just off the top of my head, can you change the timeout value to something greater? If timeouts are your problem, just increase the timeout limit (if you can).

[dorandoran]

I tried timeout = -1 which is unlimited, but i dont know why this is not working. may be I am doing it in the right place. I have timeout to infinite at the login level.

[cloud255]

That is a lot of code.

I'm not really good at VB but i will give it a shot.

You have a lot of for each loops, that is what's slowing you down, but the first thing you should look at is the time out value if i'm not mistaken it is set to 1 minute, that might be a bit short expecially if you are going with huge amounts of data across the internet.

Next you have that counter "i" but you only use it to count the number of records, not split the operation. My VB really isn't that good so i will try to give you some pseudo code:

Find all the rows which need to be updated.
Perform the update locally.
Create a set of updated rows 500 or so at a time and send this to the database, repeat with the next 500 or so.

But this still doesnt look good to me, i would look for a way to either generate a query which will handle this work or a stored procedure perhaps.
Maybe a webservice which accepts a collection of objects, all of which need to be updated with only the values which need to be changed i.e. object with 9 fields for example, only 2 need to be changed, then leave the other 7 empty.

Then on the server pass each of the objects to a stored procedure which will handle it, with some simple logic telling it to leave all the empty fields unchanged. Your code seems a bit resource intense for a client machine
I hope this was of some help to you, maybe someone wiser will give us a better solution

[dorandoran]

Thank you Cloud for your input. I will try to mess with the timeout thing. I do have stored procedure but the soap (web services) that I am using is too messy. At this moment I dont have the time to change the code. I hope you understand. I will follow your suggestion as close as possible.

Again, thanks a lot for looking into this. Appreciate it.

Thanks
Julie Jones

[cloud255]

Thank you Cloud for your input. I will try to mess with the timeout thing. I do have stored procedure but the soap (web services) that I am using is too messy. At this moment I dont have the time to change the code. I hope you understand. I will follow your suggestion as close as possible.

Again, thanks a lot for looking into this. Appreciate it.

Thanks
Julie Jones

You are more than welcome, again my apologies for the limited VB knowledge.

Happy to help