6.5 7.0 Upgrade SQL Server Performance Issues Stored Procedure

6.5 7.0 Upgrade SQL Server Performance Issues Stored Procedure

Post by Doug Mill » Sun, 29 Jul 2001 07:17:30



Just trying to save some other poor soul with my extensive list of
keywords.

SQL Server 7.0 doesn't like to combine stored procedures with views.

In 6.5 I had a posting alorythm that took about 20 minutes each night
to run. I converted to 7.0, and it then took about 6 hours.

the proc went something like this:
get first thing to post
do while not out of things to post
    create cursor of the detail stuff for main item using view and
unique key
    loop through cursur to calculate all the detail stuff up
    write stuff out to permanent file
    Get next thing to be posted.
loop

turns out that the create cursor line called a view. the view went
ahead and did a HUMOUNGOUS join of all the various detail files and
all permutations, then the cursor sorted through that to figure out
which 3 of the lines it needed. Considering what it was doing, it was
AMAZINGLY fast. And because it was in the create cursor, I never did
see it through the optimizer stuff.

sql server 6.5 had a different approach - it found the 3 lines it
needed, then did joins to get the ancillary stuff to support it.
I cut and pasted the code that made the view into the stored
procedure, and now the posting alogrythm is about 6 minutes per night.
YMMV.

All in all, sql server 7.0 Flat FLIES compared to 6.5. Went from
constant complaints about system speed to "the system is now fast
enough!".

Doug Miller