October 16, 2010 at 3:50 am
Hello all,
I use a conditinal split to check if records exists and have changed. Is so the dataflow goes to a storedprocedure to update the table. The stranges thing happens, all the records got updated with the same change, so for instance all the birthdates of users are the same after the update. It seems like the update commande holds the same values for the variables, when i look with a dataviewer I see different data then the data inserted. Anyone a clue what this can be?
within the oldb command I cal the stored procedure as "exec spUpdateall ?,?,?,?,?,?,?,?,?,?,?,?,?,?"
Where the questionmarks are the ammount of variables.
Thank you
peter
October 17, 2010 at 1:20 pm
Soulfly73 (10/16/2010)
Hello all,I use a conditinal split to check if records exists and have changed. Is so the dataflow goes to a storedprocedure to update the table. The stranges thing happens, all the records got updated with the same change, so for instance all the birthdates of users are the same after the update. It seems like the update commande holds the same values for the variables, when i look with a dataviewer I see different data then the data inserted. Anyone a clue what this can be?
within the oldb command I cal the stored procedure as "exec spUpdateall ?,?,?,?,?,?,?,?,?,?,?,?,?,?"
Where the questionmarks are the ammount of variables.
Thank you
peter
I see you're using a stored procedure named: spUpdateall
Are you sure you're using the right procedure?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 19, 2010 at 1:25 pm
Hello Alvin,
thank you for your reply, I do use the right procedure 😉 It should be fired for all records changed (all) .
🙂
Regards,
Peter
October 20, 2010 at 3:00 am
The OLE DB Command doesn't work that way.
It is fired for every row in the dataflow. So, the update statement of the last row will determine what is updated.
Instead, it is better to write all rows to be updated to a temp table and then perform one single set-based update. You will see performance going through the roof 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 25, 2010 at 6:21 am
Hi da-zero,
Thanks for your reply, the update will be on less than 1000 records per day, so performance (at night) isn't all of a issue. I have solved it by using a update command in stead of the use of a SP. Thanks,
Peter
October 25, 2010 at 1:20 pm
Soulfly73 (10/25/2010)
Hi da-zero,Thanks for your reply, the update will be on less than 1000 records per day, so performance (at night) isn't all of a issue. I have solved it by using a update command in stead of the use of a SP. Thanks,
Peter
Glad that it all worked out, but I have to ask you this question:
are you sure it will stay with those 1000 records? Maybe that is the requirement today, but maybe next year the business is so pleased with your solution they want to use it throughout the whole company 🙂 Instead of 1000 records, it becomes millions of records. And at that moment you don't want to be stuck with a "flawed" design (remark: I used quotes here, because you can argue over such things).
Of course, I'm describing worst case scenario here...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 25, 2010 at 1:24 pm
Hi Da-zero, you're very right. I could be a possible bottleneck in the future, so it stays a 'issue' to solve right.
For the moment i choose this solution cause there are still a lot of other botlenecks to pass at this moment and I want to focus at that now 🙂 I have written the issue in my documentation as a 'nice to have' if there is time left i want definitly change it to use the SP.
Thanks again 🙂
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply