I am using AIMS 2014. I created a SQL Server view by using eight inner joins.
The layer using the SQL Server view is displayed with normal speed, but when
I only selected about five features on the map. It spent more than 20
seconds to load the selection info.
My view is created by following SQL Scripts:
SELECT C.Asset_ID, ...... E1.Extra_Field_Value AS DistanceToValve,
E2.Extra_Field_Value AS NumberOfOutlets,
E3.Extra_Field_Value AS OutletConn1Size, E4.Extra_Field_Value AS
OutletConn2Size, E5.Extra_Field_Value AS OutletConn3Size,
E6.Extra_Field_Value AS Storz,
E7.Extra_Field_Value AS DateInstallationEstimated,
E8.Extra_Field_Value AS MainSize
FROM dbo.vw_Map_Core_Point AS C INNER JOIN
dbo.Asset_Extra_Fields AS E1 ON C.Asset_ID =
E1.Asset_Id AND E1.Extra_Field_Id = 2 AND C.Asset_Class_ID =
E1.Asset_Class_Id INNER JOIN
dbo.Asset_Extra_Fields AS E2 ON C.Asset_ID =
E2.Asset_Id AND E2.Extra_Field_Id = 124 AND C.Asset_Class_ID =
E2.Asset_Class_Id INNER JOIN
dbo.Asset_Extra_Fields AS E3 ON C.Asset_ID =
E3.Asset_Id AND E3.Extra_Field_Id = 125 AND C.Asset_Class_ID =
E3.Asset_Class_Id INNER JOIN
dbo.Asset_Extra_Fields AS E4 ON C.Asset_ID =
E4.Asset_Id AND E4.Extra_Field_Id = 126 AND C.Asset_Class_ID =
E4.Asset_Class_Id INNER JOIN
dbo.Asset_Extra_Fields AS E5 ON C.Asset_ID =
E5.Asset_Id AND E5.Extra_Field_Id = 127 AND C.Asset_Class_ID =
E5.Asset_Class_Id INNER JOIN
dbo.Asset_Extra_Fields AS E6 ON C.Asset_ID =
E6.Asset_Id AND E6.Extra_Field_Id = 128 AND C.Asset_Class_ID =
E6.Asset_Class_Id INNER JOIN
dbo.Asset_Extra_Fields AS E7 ON C.Asset_ID =
E7.Asset_Id AND E7.Extra_Field_Id = 129 AND C.Asset_Class_ID =
E7.Asset_Class_Id INNER JOIN
dbo.Asset_Extra_Fields AS E8 ON C.Asset_ID =
E8.Asset_Id AND E8.Extra_Field_Id = 130 AND C.Asset_Class_ID =
E8.Asset_Class_Id
WHERE (C.Asset_Class_ID = 64) AND (C.Disposal_Date IS NULL)
Table C has 438 records, and Table Asset_Extra_Fields has 242,430 records.
However, when I select all the records from the view, SQL Server spent less
then 1 sec to load the whole view.
Any solutions or comments will be appreciated!
Cheers!
--
View this message in context:
http://osgeo-org.1560.x6.nabble.com/SQL-Server-Inner-Joint-Table-Slow-To-Display-Selection-Info-On-AIMS-2014-tp5304096.html
Sent from the MapGuide Users mailing list archive at Nabble.com.
_______________________________________________
mapguide-users mailing list
mapguide-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/mapguide-users
