[mapguide-users] SQL Server Inner Joint Table Slow To Display Selection Info On AIMS 2014

[RenoSun]

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.