March 22, 2005 at 4:33 pm
I have a base table in another database being accessed via a view that massages three fields with UDFs, all three functions are very basic, things like converting 'W' to 'WH'. I then have four views that hit that base 'view', and later a union query that joins up the four second layer views.
It's crunching 3.7 million rows down to 144,000ish. Takes about 22 minutes on our development server. Performance isn't a huge thing since this isn't a transactional system, it's building up data for export and transmission.
Ultimately I may import the table and do the UDF transform once, but that's not viable at the moment.
So the question is, is that the right place to put the UDFs? Would I get better performance by putting them at the second level, or heaven forbid, at the union level?
I may experiment with it to see what the diff was. From a design aesthetic, and from a gut-level performance feeling, I think they belong where they are, I'm just looking for more opinions.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
March 22, 2005 at 10:34 pm
We've had similar problems with Views where I work... everybody thought it was the UDF's... turns out that the "big" view had to fully "materialize" for the smaller views to run... Views live in memory, memory ran out, file swapping began, performance died.
Since this sounds like something that might be used by a once or twice a day process, you could do what we did... create a table and populate it instead of using a View.
As a side bar, we were using the Standard Edition of SQL Server which has a limit of 2 Gig of memory regardless of how much memory the box actually has. I finally convinced them of the error of their ways and they bought the Enterprise Edition which can use the 12 gig the box had... most of the view problems went away until 7 or 8 people tried to run the same query using the view. Each person gets their own instance of the view or so it would seem 'cause it just hammers memory and the CPU's peg trying to do all the swapping.
--Jeff Moden
March 23, 2005 at 8:32 am
Thanks, Jeff. I think I'm just going to turn the table into a local one and do the conversion in one pass. I'm going to have to do two more crunches on it, rolling it up even tighter. The records are small, only 30 bytes in field length, but with that many records it adds up to 467meg. I ran the whole thing last night and sucked it into Access for some quickie analysis, I'm now going to throw it back down into a table.
I have no idea what the server configuration is like. I have zero DBA or network admin access, I just play developer on this project. They have a fairly complex server farm here, 3 dozen instances over I don't know how many servers with hideous amounts of replication and such. It's quite possible that this box is skimpy on RAM, wouldn't surprise me at all.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply