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 ««12

Phantom FK violation Expand / Collapse
Author
Message
Posted Thursday, June 26, 2014 9:36 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:48 AM
Points: 428, Visits: 927
Sean, I'd be happy to hear any suggestions re: perf improvement.


Post #1586526
Posted Thursday, June 26, 2014 9:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 13,103, Visits: 11,933
schleep (6/26/2014)
Sean, I'd be happy to hear any suggestions re: perf improvement.


The first suggestion is to remove the while loop that is replacing multiple spaces with one. Jeff Moden has a very clever of way doing this with no loop.

http://www.sqlservercentral.com/articles/T-SQL/68378/

The second would be to get rid of the cursor if possible. Of course we only have a portion of the actual code since the cursor is declared but never referenced again.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1586534
Posted Thursday, June 26, 2014 9:57 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:48 AM
Points: 428, Visits: 927
WHILE: Agreed. However, this sproc is called for Inserts and updates an average of 10x/day, so the perf hit is probably not noticeable.

As for the cursor, there are 2 related sprocs called for each row in hierarchy affected by a change in a keyword, each having some rather complex logic.

(I will NOT share the nested! cursor! solution we use to retrieve and present the keywords and data to which they are linked. But it generally runs in under 1 second, 20K times a day).

Thanks!

P



Post #1586544
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse