Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Custom Sequence Numbering Expand / Collapse
Author
Message
Posted Tuesday, February 8, 2011 9:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 27, 2015 8:52 AM
Points: 103, Visits: 290
James,

I really liked this article. I especially appreciated how you spelled out the logic in text. Many articles make you puzzle it out in the code. Thank you.

Amy
Post #1060390
Posted Tuesday, February 8, 2011 9:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 12:17 PM
Points: 80, Visits: 407
I think I used zero as the starting sequence due to all the C# programming I have been doing lately - and the hardened C# community would surely spit on anyone starting a sequence or array with 1 - the mere thought of it!!!

After reading the forum I do have started to come to the conclusion that my procedure was maybe overkill in the terms of the scope of what it was trying to do - i.e. both moving the record to the desired position and renumbering the whole sequence in the same process. I am now of a mind to do the gap removal and resequencing to zero in the delete procedure and then change the proc to be more like yours and another one that was posted.

I don't have as much validation logic in as yours though, as when I last implemented this the front-end code took care of that. Nice proc though, rare nowadays to see such good error checking! Thanks for taking the time to read the article.



James
MCM [@TheSQLPimp]
Post #1060391
Posted Tuesday, February 8, 2011 9:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 12:17 PM
Points: 80, Visits: 407
Thank you Amy and apologies for the initial typo (which has now been corrected). I have learned a lot about the article writing style from the comments and the whole process has been beneficial for me. I have another one published on Thursday showing how to auto-generate stored procedures and I have triple-checked the attached code to make sure it is 100% correct!

Again, thanks for taking the time to comment, I really appreciate it.



James
MCM [@TheSQLPimp]
Post #1060395
Posted Tuesday, February 8, 2011 1:21 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 168, Visits: 663
fahey.jonathan (2/8/2011)
I have used a similar procedure to move records in a sequence....


Jonathan's code (above) made sense to me. But I couldn't really figure out what was going on in the original article's case statement. It makes sense conceptually, but the specifics of the code were too funky for me to parse. Partly this is because I'm new to this stuff, but also partly because the Jonathan's code is so much more nicely formatted and easy to reconcile. I'd humbly request that the article author reformat his code so it's more readable.

And also, please note that as of the time of this posting, the incorrect column name ("todoGroup") is still specified in the original article.



"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Post #1060597
Posted Tuesday, February 8, 2011 2:06 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 12:17 PM
Points: 80, Visits: 407
Unfortunately the funky code is very efficient. The problem with stored procedures with a lot of IF statements in is that they do not cache well, as the compiler stores a different plan each time a new path is followed, making for poor reuse. The code is complicated to make it efficient. Apolgies if it is hard to follow. I will change the typo as soon as I can.

Cheers, James


James
MCM [@TheSQLPimp]
Post #1060642
Posted Tuesday, February 8, 2011 2:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 8, 2015 10:57 AM
Points: 108, Visits: 380
The original code updates every record for the person, irrespective of whether it changes or not. I think updating only the rows that change makes up for any inefficiency with an "IF" statement.

I put the two procedures in the database and looked at the execution plans. It is not always an accurate reflection of execution time, I know, but it does provide very useful information. The original procedure was expected to take 66% of the time and the revised procedure was expected to take 34%. The compiler seems to think the "IF" statements are not a significant detriment.

I have one other thing to note. The original procedure accepts as parameters both the PersonID and the ToDoID. The PersonID is dependent on the ToDoID, so either the procedure should not accept the PersonID as a parameter and just look it up, or it should validate that the PersonID passed was the correct PersonID for the ToDoID.
Post #1060679
Posted Tuesday, February 8, 2011 3:10 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 12:17 PM
Points: 80, Visits: 407
Again, this is because my procedure also removes gaps in the sequence, hence why it updates all the records. If you try my procedure with different parameters and look at the cache hits it will always hit, whereas the other one will recompile for different parameters combinations. As I stated in an earlier post, I will remove the resequencing to remove gaps and restate at zero into the delete routine, enabling my procedure to also work on a range and drop drastically in complexity. Apologies if you have not read back this far. My procedure suffered by being to all-encapsulating, as if you had a broken sequence due to deletes of say 1,5,7,11,13, mine would set this back to 0,1,2,3,4, whereas the others wouldn't - hence why it is complex and hence why it updates all records.

Cheers, james


James
MCM [@TheSQLPimp]
Post #1060693
Posted Tuesday, February 8, 2011 3:23 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 12:17 PM
Points: 80, Visits: 407
Apolgies, didn't answer the todoid and personid question. I'd expect the business/application layer to do this, as if the application was passing me a todoid for the wrong personid then I'd be very concerned. Some business logic in the database layer is good but validation such as this should be done at a higher level. Same goes for "this is already the first one" etc.

Cheers, James


James
MCM [@TheSQLPimp]
Post #1060702
Posted Tuesday, February 8, 2011 3:50 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 4:20 PM
Points: 18,760, Visits: 17,004
thanks for the article.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1060717
Posted Wednesday, February 9, 2011 2:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 12:17 PM
Points: 80, Visits: 407
Thank you. It was a great learning experience for me in terms of writing style and content. All the feedback was beneficial and my future articles will surely improve as a result!

Cheers, James


James
MCM [@TheSQLPimp]
Post #1060910
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse