April 19, 2012 at 7:00 am
We are currently trying to query a view which takes a considerable amount of time, in order to try and speed this up i am trying to create some indexes on the view however i believe to do this i need a schemabinding I have tried to do this using the following statement however does not seem to work. Any help would be greatly appreciated to speed up the querying of my view.
CREATE VIEW GIS_Web_View
WITH SCHEMABINDING
SELECT CAST(CASE WHEN PAO_START_NUMBER IS NULL THEN NULL ELSE PAO_START_NUMBER END AS VARCHAR(10))
+ (CASE WHEN PAO_START_SUFFIX IS NULL THEN '' ELSE '-' END) + (CASE WHEN PAO_START_SUFFIX IS NULL
THEN '' ELSE PAO_START_SUFFIX END) + (CASE WHEN PAO_END_NUMBER IS NULL THEN '' ELSE '-' END)
+ CAST(CASE WHEN PAO_END_NUMBER IS NULL THEN '' ELSE PAO_END_NUMBER END AS VARCHAR(10)) AS PAO, tbl_24.PAO_TEXT,
tbl_15.STREET_DESCRIPTOR, tbl_15.LOCALITY_NAME, tbl_24.POST_TOWN, tbl_24.POSTCODE, tbl_21.X_COORDINATE, tbl_21.Y_COORDINATE,
(CASE WHEN tbl_21.ORGANISATION IS NULL OR
ORGANISATION = '' THEN '' ELSE tbl_21.ORGANISATION + ',' END) + (CASE WHEN SAO_START_NUMBER IS NULL
THEN '' ELSE (CAST(CASE WHEN SAO_START_NUMBER IS NULL THEN '' ELSE SAO_START_NUMBER END AS VARCHAR(10))
+ (CASE WHEN SAO_START_SUFFIX IS NULL THEN '' ELSE '-' END) + (CASE WHEN SAO_START_SUFFIX IS NULL
THEN '' ELSE SAO_START_SUFFIX END) + (CASE WHEN SAO_END_NUMBER IS NULL THEN '' ELSE '-' END)
+ CAST(CASE WHEN SAO_END_NUMBER IS NULL THEN '' ELSE SAO_END_NUMBER END AS VARCHAR(10)) + ',') END)
+ (CASE WHEN SAO_TEXT IS NULL OR
SAO_TEXT = '' THEN '' ELSE SAO_TEXT + ',' END) + (CASE WHEN PAO_START_NUMBER IS NULL
THEN '' ELSE (CAST(CASE WHEN PAO_START_NUMBER IS NULL THEN '' ELSE PAO_START_NUMBER END AS VARCHAR(10))
+ (CASE WHEN PAO_START_SUFFIX IS NULL THEN '' ELSE '-' END) + (CASE WHEN PAO_START_SUFFIX IS NULL
THEN '' ELSE PAO_START_SUFFIX END) + (CASE WHEN PAO_END_NUMBER IS NULL THEN '' ELSE '-' END)
+ CAST(CASE WHEN PAO_END_NUMBER IS NULL THEN '' ELSE PAO_END_NUMBER END AS VARCHAR(10)) + ',') END)
+ (CASE WHEN PAO_TEXT IS NULL OR
PAO_TEXT = '' THEN '' ELSE PAO_TEXT + ',' END) + (CASE WHEN STREET_DESCRIPTOR IS NULL OR
STREET_DESCRIPTOR = '' THEN '' ELSE STREET_DESCRIPTOR + ',' END) + (CASE WHEN LOCALITY_NAME IS NULL OR
LOCALITY_NAME = '' THEN '' ELSE LOCALITY_NAME + ',' END) + (CASE WHEN POST_TOWN IS NULL OR
POST_TOWN = '' THEN '' ELSE POST_TOWN + ',' END) + (CASE WHEN POSTCODE IS NULL OR
POSTCODE = '' THEN '' ELSE POSTCODE + ',' END) AS RESULTS
FROM dbo.LAND_AND_PROPERTY_IDENTIFIER_RECORD24 AS tbl_24 LEFT OUTER JOIN
dbo.STREET_DESCRIPTOR_RECORD_TYPE15 AS tbl_15 ON tbl_24.USRN = tbl_15.USRN LEFT OUTER JOIN
dbo.BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21 AS tbl_21 ON tbl_24.UPRN = tbl_21.UPRN
GO
April 19, 2012 at 7:16 am
You can't create an indexed view on that query. Indexed views can not contain outer joins.
April 19, 2012 at 7:19 am
Thanks for your quick response that has helped no end stopped me from heading in the wrong direction anyway. Any suggestions on optimising the performance when querying this view?
April 19, 2012 at 7:24 am
Honestly, no. The code is in desperate need of being properly formatted before I could do anything. It was hard enough to just find the joins in the code.
April 19, 2012 at 7:26 am
Also, it would assist others if you could post the DDL for the tables including indexes and the actual execution plan for the query as a .sqlplan file.
See the second link in my signature block for more information on what is needed for performance tuning.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply