Just trying to save some other poor soul with my extensive list of
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
loop through cursur to calculate all the detail stuff up
write stuff out to permanent file
Get next thing to be posted.
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.
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