SQL Server Performance Forum – Threads Archive

Puzzling View Performance

Folks, I have researched this one all day, and cannot come up with a decent explanation for what I’m seeing; maybe someone would be willing to explain it to me. I work for a school district. In one of our databases, the developers asked me to create yet another ‘schools’ table, for tying in all the popular data items together in a one-stop shop. I protested that, really, we ought to do this with a view. And that this shouldn’t hurt performance, because we’re only talking about a total of about 2,400 rows from the six tables that we’d be joining for the view. Well, so much for "shouldn’t". One of the developers showed me that, for one of his T-SQL scripts, going against the new view took over a minute. However, if he created a temp table by doing a "SELECT * INTO #temptable FROM newview" and then JOINed to that new table instead of the view, it took only five seconds. So what is the view doing? Basically, there are three tables for the current school year being joined, and then a UNION ALL is issued for an analagous set of three tables representing all prior school years. The number of rows in each of these tables being JOINed are in the hundreds, not the thousands. During testing, I never saw any appreciable performance difference whether I let the underlying SELECT run either by joining along the indexes, or by forcing table scans. And the SELECT by itself runs in about a second — it isn’t until the view is being JOINed that it slows down. I monkeyed around with several methods of coding the view, and the only time I noticed an improvement was when I took the UNION ALL out altogether, and then the whole query ran in a matter of a couple of seconds. Now, here is the frustrating part. I was able to speed things up considerably by turning the view into a (parameterless) table function. Same logic, same JOINs, same UNION, same numbers of rows — but it ran in seconds instead of over a minute. Even worse: When I wrapped this function with another view, it *still* performed quickly. By now, frustration was starting to be replaced with intrigue. Hmmmm. So I turned on the show plan and the stats, and then ran two versions of the query in the same window, one after the other — one version JOINing against the "slow" view, the other using the "fast" view/function. Interestingly, the stats said the "fast" view took 49% of the batch’s resources, and the "slow" view took 51%. In other words, from the server’s perspective, one query was about as costly as the other. Yet, one of them ran in about 1/40th of the time. Other than the execution times, the stats are practically equal. Both versions of the query showed that an ‘index scan’ on one of the other tables (i.e. not in the view) took about 90% of the resources. Also interestingly, on our weaker test and dev servers, the problem is not so egregious. I can get results from the "slow" view in fifteen seconds instead of a minute and a half. But we’re running the same SQL version on all of our servers (8.00.81<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />. That would seem to indicate there is a physical "server" factor here as well as a coding factor. To me, what it looks like is that when I put the UNION ALL into the view, it just decides to hang there for a minute, but that putting the UNION ALL in a function doesn’t offend the server so much. Reading up about partitioned views, I was wondering if the DBMS is trying to decide if the view is partitioned (due to the presence of the UNION ALL clauses) and gets wrapped around the axle setting up the overhead to handle it (even though it’s unnecessary — all tables are right together in the same server/database). Any ideas? Thanks!

Hi ya, could you post the view definition please? plus indexes that are on the underlying tables? Cheers
Twan

Please post the code for both of your scenarios, the "slow" one and the "fast" one. Try to update the statistics on all the tables that your view refers to, and then re-run your query. Are you using an indexed view?

The tables are: ACAM (163 rows) — has a nonclustered index on ‘SCHOOLNUM’ CHAR (3)
ACAM_ARC (725 rows) — has a nonclustered index on ‘SCHOOL_YR_CD’ CHAR (5)
and ‘SCHOOLNUM’ CHAR (3)
ASCH (170 rows) — has a clustered index based on the primary key ‘SCHOOLNUM’ CHAR (3)
ASCH_ARC (757 rows) — has a clustered index based on the primary key
‘SCHOOL_YR_CD’ CHAR (5) and ‘SCHOOLNUM’ CHAR (3)
ADSI (169 rows) — has a clustered index based on the primary key ‘SCHOOLNUM’ CHAR (3)
ADSI_ARC (332 rows) — has a clustered index based on the primary key
‘SCHOOL_YR_CD’ CHAR (5) and ‘SCHOOLNUM’ CHAR (3)
sasi_current_school_yr — table contains one row to indicate the ‘current’ school year
I tried several view definitions, JOINing and UNIONing them in different ways to see if I could get a different result. Here’s probably the "best" version: CREATE VIEW SCHOOLS
AS
SELECT (SELECT TOP 1 schoolyearlong
FROM sasi_current_school_yr) SCHOOLYEAR
, asch.SCHOOLNUM
, asch.STATUS
, asch.name SchoolName
, asch.SCHOOLABRV
, asch.altnumber StateNumber
, asch.ADDRESS
, asch.ZIPCODE
, asch.TELEPHONE
, asch.FAXNUM
, asch.principal PRINCIPAL
, asch.atttype AttendanceType
, asch.schedcodes SchedCodes
, asch.sembasis SemesterBasis
, asch.periodrot PeriodRotation
, acam.CAMNUMBER
, acam.fdsvccode FoodServicesCode
, acam.schltype SchoolType
, acam.titleistat Title1Status
, acam.caltype CalendarType
, acam.lsnproclvl LSNPROCLVL
, acam.schliscenter Center
, acam.TARGETSCHL
, acam.TASKSERVID
, acam.UPLOADTIME
, acam.UPLOADPATH
, acam.DATAPATH
, adsi.concurenr ConcurrentEnrollment
, adsi.summer_sch SummerSchool
FROM acam
JOIN asch ON acam.schoolnum = asch.schoolnum
JOIN adsi ON acam.schoolnum = adsi.schoolnum
UNION ALL
SELECT ’20’ + LEFT (asch.school_yr_cd, 3)
+ ’20’ + RIGHT (asch.school_yr_cd, 2) SCHOOLYEAR
, asch.SCHOOLNUM
, asch.STATUS
, asch.name
, asch.SCHOOLABRV
, asch.altnumber
, asch.ADDRESS
, asch.ZIPCODE
, asch.TELEPHONE
, asch.FAXNUM
, asch.principal
, asch.atttype
, asch.schedcodes
, asch.sembasis
, asch.periodrot
, acam.CAMNUMBER
, acam.fdsvccode
, acam.schltype
, acam.titleistat
, acam.caltype
, acam.lsnproclvl
, acam.schliscenter
, acam.TARGETSCHL
, acam.TASKSERVID
, acam.UPLOADTIME
, acam.UPLOADPATH
, acam.DATAPATH
, adsi.concurenr
, adsi.summer_sch
FROM acam_arc acam
JOIN asch_arc asch ON acam.school_yr_cd = asch.school_yr_cd
AND acam.schoolnum = asch.schoolnum
LEFT JOIN adsi_arc adsi ON acam.school_yr_cd = adsi.school_yr_cd
AND acam.schoolnum = adsi.schoolnum Here is the same logic looks like coded up as a table function, and it runs quickly done up this way… CREATE FUNCTION dbo.f_SCHOOLS ()
RETURNS @schools TABLE
(SCHOOLYEAR CHAR (9) NOT NULL
, SCHOOLNUM VARCHAR (3) NOT NULL
, STATUS CHAR (1) NOT NULL
, SchoolName VARCHAR (35) NOT NULL
, SCHOOLABRV VARCHAR (5) NOT NULL
, StateNumber VARCHAR (10) NOT NULL
, ADDRESS VARCHAR (35) NOT NULL
, ZIPCODE VARCHAR (10) NOT NULL
, TELEPHONE NUMERIC (15) NULL
, FAXNUM NUMERIC (7) NULL
, PRINCIPAL VARCHAR (35) NOT NULL
, AttendanceType CHAR (1) NOT NULL
, SchedCodes VARCHAR (12) NOT NULL
, SemesterBasis CHAR (1) NOT NULL
, PeriodRotation NUMERIC (2) NOT NULL
, CAMNUMBER VARCHAR (3) NOT NULL
, FoodServicesCode VARCHAR (4) NULL
, SchoolType VARCHAR (4) NULL
, Title1Status VARCHAR (6) NULL
, CalendarType VARCHAR (3) NOT NULL
, LSNPROCLVL VARCHAR (4) NULL
, Center VARCHAR (1) NOT NULL
, TARGETSCHL VARCHAR (3) NOT NULL
, TASKSERVID NUMERIC (2) NOT NULL
, UPLOADTIME NUMERIC (4) NULL
, UPLOADPATH VARCHAR (254) NOT NULL
, DATAPATH VARCHAR (254) NOT NULL
, ConcurrentEnrollment CHAR (1) NULL
, SummerSchool CHAR (1) NULL)
AS
BEGIN
DECLARE @school_yr_cd CHAR (5) SELECT @school_yr_cd = school_yr_cd
FROM sasi_current_school_yr INSERT INTO @schools
SELECT ’20’ + LEFT (@school_yr_cd, 3)
+ ’20’ + RIGHT (@school_yr_cd, 2)
, asch.SCHOOLNUM
, asch.STATUS
, asch.name
, asch.SCHOOLABRV
, asch.altnumber
, asch.ADDRESS
, asch.ZIPCODE
, asch.TELEPHONE
, asch.FAXNUM
, asch.principal
, asch.atttype
, asch.schedcodes
, asch.sembasis
, asch.periodrot
, acam.CAMNUMBER
, acam.fdsvccode
, acam.schltype
, acam.titleistat
, acam.caltype
, acam.lsnproclvl
, acam.schliscenter
, acam.TARGETSCHL
, acam.TASKSERVID
, acam.UPLOADTIME
, acam.UPLOADPATH
, acam.DATAPATH
, adsi.concurenr
, adsi.summer_sch
FROM acam
JOIN asch ON acam.schoolnum = asch.schoolnum
JOIN adsi ON acam.schoolnum = adsi.schoolnum INSERT INTO @schools
SELECT ’20’ + LEFT (asch.school_yr_cd, 3)
+ ’20’ + RIGHT (asch.school_yr_cd, 2)
, asch.SCHOOLNUM
, asch.STATUS
, asch.name
, asch.SCHOOLABRV
, asch.altnumber
, asch.ADDRESS
, asch.ZIPCODE
, asch.TELEPHONE
, asch.FAXNUM
, asch.principal
, asch.atttype
, asch.schedcodes
, asch.sembasis
, asch.periodrot
, acam.CAMNUMBER
, acam.fdsvccode
, acam.schltype
, acam.titleistat
, acam.caltype
, acam.lsnproclvl
, acam.schliscenter
, acam.TARGETSCHL
, acam.TASKSERVID
, acam.UPLOADTIME
, acam.UPLOADPATH
, acam.DATAPATH
, adsi.concurenr
, adsi.summer_sch
FROM acam_arc acam
JOIN asch_arc asch ON acam.school_yr_cd = asch.school_yr_cd
AND acam.schoolnum = asch.schoolnum
LEFT JOIN adsi_arc adsi ON acam.school_yr_cd = adsi.school_yr_cd
AND acam.schoolnum = adsi.schoolnum RETURN
END And finally, here is the "wrapper" view around the table function, and using this view performs well. CREATE VIEW testSCHOOLS
AS
SELECT *
FROM dbo.f_SCHOOLS ()
GO

]]>