Here have a complete ms is essentially view
Constructed after only about 1000 lines of data, but the query all need about 1000 milliseconds,
Add or modify data are not frequently, but the system does not seem to be loaded into the cache it?

Three suspected case of field leads to query is slow, but it seems there is no other way,
I don't know whether the great spirit guide

 ALTER the VIEW [dbo] [t_VT_SHOP] AS the SELECT SHOP. Id, SHOP. ShopName, 
SHOP mall, mall mallName, 
SHOP. FloorID, FLO floorName, 
SHOP. BrandID, brand brandName, 
SHOP. BussinessTypeID, bus typeName AS bussinessTypeName, 
SHOP. ShopGroupID, gro groupName, 
SHOP, createTime, SHOP, updateTime, SHOP. Valid, SHOP. Does that SHOP. CreaterId, SHOP. CreaterName, SHOP. IsShowPortal, 
The CASE WHEN the EXISTS 
(SELECT TOP 1 ID 
The FROM [DBO] [T_SALE_REWARDCOUPON] 
WHERE PUBLISHSTATUS=1 AND VALID=1 AND does=0 
AND CHARINDEX (CONVERT (NVARCHAR (10), SHOP. The ID), ISNULL (SHOPIDS, ' ')) & gt; THEN one ELSE 0 0) END AS IsCoupon, 
The CASE WHEN the EXISTS 
(SELECT TOP 1 ID 
The FROM [DBO] [t_Sale_Enroll] 
WHERE PUBLISHSTATUS=1 AND VALID=1 AND does=0 
AND CHARINDEX (CONVERT (NVARCHAR (10), SHOP. The ID), ISNULL (SHOPIDS, ' ')) & gt; THEN one ELSE 0 0) END AS IsEnroll, 
The CASE WHEN the EXISTS 
(SELECT TOP 1 ID 
The FROM [DBO] [t_Sale_Promotion] 
WHERE PUBLISHSTATUS=1 AND VALID=1 AND does=0 
AND CHARINDEX (CONVERT (NVARCHAR (10), SHOP. The ID), ISNULL (SHOPIDS, ' ')) & gt; THEN one ELSE 0 0) END AS IsPromotion 
The FROM dbo. T_Shop AS SHOP LEFT OUTER JOIN 
(SELECT id, floorname FROM dbo. T_Mall_Floor) AS FLO ON FLO. Id=SHOP. FloorID LEFT OUTER JOIN 
(SELECT id, brandName FROM dbo. T_Info_Brand) AS brand ON brand. The id=SHOP. BrandID LEFT OUTER JOIN 
(SELECT id, typeName FROM dbo. T_Info_BusinessType) AS bus ON bus. Id=SHOP. BussinessTypeID LEFT OUTER JOIN 
(SELECT id, groupName FROM dbo. T_Shop_ShopGroup) AS gro ON gro. Id=SHOP. ShopGroupID LEFT OUTER JOIN 
(SELECT id, mallName FROM dbo. T_Mall) AS mall ON mall. Id=SHOP. The mall; 

CodePudding user response:

You look at the execution plan and see where is the main consumption, so slow is better positioning in where

CodePudding user response:

First, the view is not a real table, there is no cache, such as only the index view, can be seen as a data table, with real data set
Second, the three case corresponding table, table design has a problem, SHOPIDS this design, do not conform to the requirements of the basic paradigm, cannot do index processing, seriously affect the efficiency of the query

CodePudding user response:

refer to the second floor fonee response:

first, the view is not physical table, there is no cache, such as only the index view, can be seen as a data table, with real data set
Second, the three case corresponding table, table design has a problem, SHOPIDS this design, do not conform to the requirements of the basic paradigm, cannot do index processing, seriously affect the relevance of the query efficiency

Three case corresponding table are similar to activities, promotion, each event can have multiple shop, so use shopids such design,
Then this view is to shop the whole show, connect the above three is a case in order to can from each participated in the activities of the shop into the activity pages,

So if you want to achieve this goal, I say besides shopids scheme, only the dbo. T_Shop add fields, a new activity when the update once participated in the activities of the shop?

If simple SQL statements from the main building, it is no way out?

CodePudding user response:

reference 1/f, sunny 8 response:

you look at the execution plan, and see where is the main consumption, so where is the better positioning slow

Is excuse me this how do?

CodePudding user response:

Not necessarily t_Shop add fields, this is the base table, should not record specific business data, or a shop took part in more activities, is each to an activity, you add a new field, or use a field, and to like SHOPIDS splicing,

You can for activities, to promote each add a list of associated and SHOP, table structure is similar to the primary key, ID, SHOPID,