Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите оптимизировать запрос не меняя сам запрос только путем создания индексов.  [new]
вовкаkju
Member

Откуда:
Сообщений: 6
Короче есть некий запрос который каким-то образом зашит в движок sharepoint.
При открытии определенной ссылки он выполняеться и входит в число трех наиболее продолжительных запросов, что определенно свойством Duration при трассировке SQl server Profile.
Запрос выглядит следующим образом

	  
  SELECT
 t1.[Type] AS c0,
t2.[tp_ID] AS c5c7,UserData.[ntext2],UserData.[datetime1],UserData.[nvarchar10],
t5.[nvarchar4] AS 
c14c8,t6.[tp_Created] AS c15c10,t5.[tp_Created] AS c14c10,
UserData.[tp_ItemOrder],UserData.[nvarchar1],UserData.[bit2],t1.[Id] AS c3,t3.[tp_Created] AS 
c11c10,UserData.[nvarchar14],
UserData.[ntext1],t5.[nvarchar1] AS c14c6,UserData.[float1],UserData.[int2],UserData.[ntext6],UserData.[datetime5],t6.[tp_ID] AS 
c15c7,t1.[DirName] AS c18,
UserData.[tp_ID],t3.[nvarchar5] AS c11c9,UserData.[nvarchar5],UserData.[bit1],t4.[Status1] AS c12c13,UserData.[tp_GUID],CASE WHEN 
DATALENGTH(t1.DirName) = 0 THEN t1.LeafName WHEN DATALENGTH(t1.LeafName) = 0 THEN t1.DirName ELSE t1.DirName 
--+ ''/'' + t1.LeafName 
END 
 AS c1,t2.[nvarchar4] AS 
c5c8,UserData.[tp_Author],t5.[nvarchar5] AS 
c14c9,UserData.[tp_Editor],
UserData.[int1],UserData.[nvarchar13],UserData.[ntext5],UserData.[nvarchar18],UserData.[datetime4],t3.[tp_ID] AS 
c11c7,UserData.[tp_ContentTypeId],UserData.[tp_WorkflowVersion],t1.[LeafName] AS c16,UserData.[tp_Version],t1.[ScopeId] AS 
c4,UserData.[nvarchar4],UserData.[nvarchar9],t5.[tp_ID] AS c14c7,UserData.[tp_IsCurrentVersion],UserData.[tp_HasCopyDestinations],
UserData.[tp_Level],t2.[nvarchar1] 
AS c5c6,UserData.[nvarchar12],UserData.[ntext4],UserData.[datetime3],UserData.[nvarchar17],UserData.[ntext9],UserData.[ntext11],t6.[nvarchar4] AS 
c15c8,UserData.[tp_HasAttachment],t1.[TimeLastModified] AS c19,t1.[MetaInfo] AS c2,UserData.[nvarchar3],t2.[nvarchar5] AS 
c5c9,UserData.[nvarchar8],UserData.[tp_Modified],UserData.[tp_UIVersion],UserData.[tp_CopySource],t2.[tp_Created] AS 
c5c10,UserData.[datetime2],UserData.[nvarchar11],UserData.[ntext3],UserData.[uniqueidentifier1],UserData.[nvarchar16],
UserData.[tp_InstanceID],t3.[nvarchar4] AS 
c11c8,UserData.[int4],UserData.[ntext8],UserData.[ntext10],t6.[nvarchar1] AS c15c6,UserData.[tp_UIVersionString],t1.[TimeCreated] AS 
c17,UserData.[nvarchar2],UserData.[nvarchar7],UserData.[bit3],UserData.[tp_ContentType],t1.[ProgId] AS 
c20,UserData.[nvarchar15],UserData.[ntext7],UserData.[int3],t6.[nvarchar5] AS c15c9,
UserData.[tp_Created],UserData.[tp_ModerationStatus],t3.[nvarchar1] AS 
c11c6,UserData.[nvarchar6],UserData.[tp_WorkflowInstanceID] 
FROM 
UserData INNER JOIN Docs AS t1 WITH(NOLOCK) ON ( 1 = 1  AND UserData.[tp_RowOrdinal] = 0 AND 
t1.SiteId = UserData.tp_SiteId
 AND t1.SiteId = '68616A8F-42CC-43D2-95E7-337A6F682BC8' 
AND t1.DirName = UserData.tp_DirName  AND t1.LeafName = UserData.tp_LeafName  AND t1.Level = UserData.tp_Level  
AND t1.IsCurrentVersion = 1 AND (1 = 1) AND UserData.tp_ListId = 'A86B30DB-EF4E-4F1C-A870-D468C3C1A9D7')
 LEFT OUTER JOIN AllUserData AS t2 WITH(NOLOCK, INDEX=AllUserData_PK) ON 
(UserData.[int1]=t2.[tp_ID] AND UserData.[tp_RowOrdinal] = 0 AND t2.[tp_RowOrdinal] = 0 AND ( (t2.tp_IsCurrent = 1) ) 
 AND t2.[tp_CalculatedVersion] = 0  AND 
t2.[tp_DeleteTransactionId] = 0x 
AND t2.tp_ListId = 'AB75DAE1-0854-4A07-A613-ED6BC0A2342F' 
AND UserData.tp_ListId = 'A86B30DB-EF4E-4F1C-A870-D468C3C1A9D7'
) LEFT OUTER JOIN AllUserData AS t3 WITH(NOLOCK, INDEX=AllUserData_PK) ON 
(UserData.[int2]=t3.[tp_ID] AND UserData.[tp_RowOrdinal] = 0 AND t3.[tp_RowOrdinal] = 0 AND ( (t3.tp_IsCurrent = 1) )  
AND t3.[tp_CalculatedVersion] = 0  AND 
t3.[tp_DeleteTransactionId] = 0x 
 AND
 t3.tp_ListId = 'AB75DAE1-0854-4A07-A613-ED6BC0A2342F' 
AND UserData.tp_ListId = 'A86B30DB-EF4E-4F1C-A870-D468C3C1A9D7'
) LEFT OUTER JOIN Workflow AS t4 WITH(NOLOCK) ON ( CAST ( CAST 
(UserData.[nvarchar16]AS VARBINARY) AS UNIQUEIDENTIFIER)=t4.[ID] AND UserData.[tp_RowOrdinal] = 0 
AND t4.SiteId = '68616A8F-42CC-43D2-95E7-337A6F682BC8' 
AND t4.ListId = UserData.tp_ListId AND 
t4.ItemId = UserData.tp_Id 
AND UserData.tp_ListId = 'A86B30DB-EF4E-4F1C-A870-D468C3C1A9D7'
) LEFT OUTER JOIN AllUserData AS t5
 WITH(NOLOCK, INDEX=AllUserData_PK) ON (UserData.[tp_Author]=t5.[tp_ID] 
AND UserData.[tp_RowOrdinal] = 0 AND t5.[tp_RowOrdinal] = 0 AND ( (t5.tp_IsCurrent = 1) )  AND t5.[tp_CalculatedVersion] = 0 
 AND t5.[tp_DeleteTransactionId] = 0x  
AND t5.tp_ListId = 'AB75DAE1-0854-4A07-A613-ED6BC0A2342F'
AND UserData.tp_ListId = 'A86B30DB-EF4E-4F1C-A870-D468C3C1A9D7'
)
 LEFT OUTER JOIN AllUserData AS t6 WITH(NOLOCK, INDEX=AllUserData_PK) ON (UserData.[tp_Editor]=t6.[tp_ID] AND 
UserData.[tp_RowOrdinal] = 0 AND t6.[tp_RowOrdinal] = 0 AND ( (t6.tp_IsCurrent = 1) )  AND t6.[tp_CalculatedVersion] = 0 
 AND t6.[tp_DeleteTransactionId] = 0x 
AND t6.tp_ListId = 'AB75DAE1-0854-4A07-A613-ED6BC0A2342F' 
AND UserData.tp_ListId = 'A86B30DB-EF4E-4F1C-A870-D468C3C1A9D7'
) 
WHERE  
UserData.tp_ListID='A86B30DB-EF4E-4F1C-A870-D468C3C1A9D7' 
AND 
( (UserData.tp_IsCurrent = 1) )  AND UserData.tp_RowOrdinal=0 AND 
((UserData.[tp_ID] = 10671)) 
ORDER BY UserData.[tp_ID] Asc OPTION (FORCE ORDER)
Таблицы используемые запросом:
Таблица AllUserData
USE [WSS_Content]
GO
/****** Object:  Table [dbo].[AllUserData]    Script Date: 12/24/2009 12:19:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AllUserData](
	[tp_ID] [int] NOT NULL,
	[tp_ListId] [uniqueidentifier] NOT NULL,
	[tp_SiteId] [uniqueidentifier] NOT NULL,
	[tp_RowOrdinal] [int] NOT NULL DEFAULT ((0)),
	[tp_Version] [int] NOT NULL,
	[tp_Author] [int] NULL,
	[tp_Editor] [int] NULL,
	[tp_Modified] [datetime] NULL,
	[tp_Created] [datetime] NULL,
	[tp_Ordering] [varchar](512) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[tp_ThreadIndex] [varbinary](512) NULL,
	[tp_HasAttachment] [bit] NOT NULL DEFAULT ((0)),
	[tp_ModerationStatus] [int] NOT NULL DEFAULT ((0)),
	[tp_IsCurrent] [bit] NOT NULL DEFAULT ((1)),
	[tp_ItemOrder] [float] NULL,
	[tp_InstanceID] [int] NULL,
	[tp_GUID] [uniqueidentifier] NOT NULL DEFAULT (newid()),
	[tp_CopySource] [nvarchar](260) COLLATE Latin1_General_CI_AS_KS_WS NULL DEFAULT (NULL),
	[tp_HasCopyDestinations] [bit] NULL DEFAULT ((0)),
	[tp_AuditFlags] [int] NULL,
	[tp_InheritAuditFlags] [int] NULL,
	[tp_Size] [int] NOT NULL DEFAULT ((0)),
	[tp_WorkflowVersion] [int] NULL,
	[tp_WorkflowInstanceID] [uniqueidentifier] NULL,
	[tp_DirName] [nvarchar](256) COLLATE Latin1_General_CI_AS_KS_WS NOT NULL,
	[tp_LeafName] [nvarchar](128) COLLATE Latin1_General_CI_AS_KS_WS NOT NULL,
	[tp_DeleteTransactionId] [varbinary](16) NOT NULL DEFAULT (0x),
	[tp_ContentType] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[tp_ContentTypeId] [dbo].[tContentTypeId] NULL,
	[nvarchar1] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar2] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar3] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar4] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar5] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar6] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar7] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar8] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext1] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext2] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext3] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext4] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[sql_variant1] [sql_variant] NULL,
	[nvarchar9] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar10] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar11] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar12] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar13] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar14] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar15] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar16] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext5] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext6] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext7] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext8] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[sql_variant2] [sql_variant] NULL,
	[nvarchar17] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar18] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar19] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar20] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar21] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar22] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar23] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar24] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext9] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext10] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext11] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext12] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[sql_variant3] [sql_variant] NULL,
	[nvarchar25] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar26] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar27] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar28] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar29] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar30] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar31] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar32] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext13] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext14] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext15] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext16] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[sql_variant4] [sql_variant] NULL,
	[nvarchar33] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar34] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar35] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar36] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar37] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar38] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar39] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar40] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext17] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext18] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext19] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext20] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[sql_variant5] [sql_variant] NULL,
	[nvarchar41] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar42] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar43] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar44] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar45] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar46] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar47] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar48] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext21] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext22] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext23] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext24] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[sql_variant6] [sql_variant] NULL,
	[nvarchar49] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar50] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar51] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar52] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar53] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar54] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar55] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar56] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext25] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext26] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext27] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext28] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[sql_variant7] [sql_variant] NULL,
	[nvarchar57] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar58] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar59] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar60] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar61] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar62] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar63] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[nvarchar64] [nvarchar](255) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext29] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext30] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext31] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[ntext32] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[sql_variant8] [sql_variant] NULL,
	[int1] [int] NULL,
	[int2] [int] NULL,
	[int3] [int] NULL,
	[int4] [int] NULL,
	[int5] [int] NULL,
	[int6] [int] NULL,
	[int7] [int] NULL,
	[int8] [int] NULL,
	[int9] [int] NULL,
	[int10] [int] NULL,
	[int11] [int] NULL,
	[int12] [int] NULL,
	[int13] [int] NULL,
	[int14] [int] NULL,
	[int15] [int] NULL,
	[int16] [int] NULL,
	[float1] [float] NULL,
	[float2] [float] NULL,
	[float3] [float] NULL,
	[float4] [float] NULL,
	[float5] [float] NULL,
	[float6] [float] NULL,
	[float7] [float] NULL,
	[float8] [float] NULL,
	[float9] [float] NULL,
	[float10] [float] NULL,
	[float11] [float] NULL,
	[float12] [float] NULL,
	[datetime1] [datetime] NULL,
	[datetime2] [datetime] NULL,
	[datetime3] [datetime] NULL,
	[datetime4] [datetime] NULL,
	[datetime5] [datetime] NULL,
	[datetime6] [datetime] NULL,
	[datetime7] [datetime] NULL,
	[datetime8] [datetime] NULL,
	[bit1] [bit] NULL,
	[bit2] [bit] NULL,
	[bit3] [bit] NULL,
	[bit4] [bit] NULL,
	[bit5] [bit] NULL,
	[bit6] [bit] NULL,
	[bit7] [bit] NULL,
	[bit8] [bit] NULL,
	[bit9] [bit] NULL,
	[bit10] [bit] NULL,
	[bit11] [bit] NULL,
	[bit12] [bit] NULL,
	[bit13] [bit] NULL,
	[bit14] [bit] NULL,
	[bit15] [bit] NULL,
	[bit16] [bit] NULL,
	[uniqueidentifier1] [uniqueidentifier] NULL,
	[tp_Level] [tinyint] NOT NULL DEFAULT ((1)),
	[tp_IsCurrentVersion] [bit] NOT NULL DEFAULT (CONVERT([bit],(1),0)),
	[tp_UIVersion] [int] NOT NULL CONSTRAINT [AllUserData_DEFAULT_UIVersion]  DEFAULT ((512)),
	[tp_CalculatedVersion] [int] NOT NULL CONSTRAINT [AllUserData_DEFAULT_CalculatedVersion]  DEFAULT ((0)),
	[tp_UIVersionString]  AS ((CONVERT([nvarchar],[tp_UIVersion]/(512),0)+'.')+CONVERT([nvarchar],[tp_UIVersion]%(512),0)),
	[tp_DraftOwnerId] [int] NULL DEFAULT (NULL),
	[tp_CheckoutUserId] [int] NULL DEFAULT (NULL),
 CONSTRAINT [AllUserData_PK] PRIMARY KEY NONCLUSTERED 
(
	[tp_ListId] ASC,
	[tp_ID] ASC,
	[tp_CalculatedVersion] ASC,
	[tp_Level] ASC,
	[tp_RowOrdinal] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY],
 CONSTRAINT [AllUserData_Url] UNIQUE CLUSTERED 
(
	[tp_SiteId] ASC,
	[tp_DeleteTransactionId] ASC,
	[tp_IsCurrentVersion] ASC,
	[tp_DirName] ASC,
	[tp_LeafName] ASC,
	[tp_CalculatedVersion] ASC,
	[tp_Level] ASC,
	[tp_RowOrdinal] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
Таблица Workflow
USE [WSS_Content]
GO
/****** Object:  Table [dbo].[Workflow]    Script Date: 12/24/2009 12:27:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Workflow](
	[Id] [uniqueidentifier] NOT NULL,
	[TemplateId] [uniqueidentifier] NOT NULL,
	[ListId] [uniqueidentifier] NOT NULL,
	[SiteId] [uniqueidentifier] NOT NULL,
	[WebId] [uniqueidentifier] NOT NULL,
	[TaskListId] [uniqueidentifier] NULL,
	[AdminTaskListId] [varbinary](16) NULL,
	[ItemId] [int] NULL,
	[ItemGUID] [uniqueidentifier] NULL,
	[Author] [int] NULL,
	[Modified] [datetime] NOT NULL,
	[Created] [datetime] NULL,
	[InternalState] [int] NULL,
	[LockMachineId] [uniqueidentifier] NULL,
	[LockMachinePID] [int] NULL,
	[InstanceDataVersion] [int] NOT NULL DEFAULT ((0)),
	[InstanceDataSize] [int] NULL,
	[InstanceData] [image] NULL,
	[Modifications] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[HistorySize] [int] NULL,
	[History] [image] NULL,
	[StatusVersion] [int] NOT NULL DEFAULT ((0)),
	[Status1] [int] NULL,
	[Status2] [int] NULL,
	[Status3] [int] NULL,
	[Status4] [int] NULL,
	[Status5] [int] NULL,
	[Status6] [int] NULL,
	[Status7] [int] NULL,
	[Status8] [int] NULL,
	[Status9] [int] NULL,
	[Status10] [int] NULL,
	[TextStatus1] [nvarchar](128) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[TextStatus2] [nvarchar](128) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[TextStatus3] [nvarchar](128) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[TextStatus4] [nvarchar](128) COLLATE Latin1_General_CI_AS_KS_WS NULL,
	[TextStatus5] [nvarchar](128) COLLATE Latin1_General_CI_AS_KS_WS NULL,
 CONSTRAINT [Workflow_PK] PRIMARY KEY CLUSTERED 
(
	[SiteId] ASC,
	[WebId] ASC,
	[ListId] ASC,
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY],
 CONSTRAINT [Workflow_IdUnique] UNIQUE NONCLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Вьюха UserData
GO
SET ANSI_PADDING OFF
USE [WSS_Content]
GO
/****** Object:  View [dbo].[UserData]    Script Date: 12/24/2009 12:31:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[UserData]
AS
    SELECT
        *
    FROM
        AllUserData
    WHERE
        tp_IsCurrentVersion = CONVERT(bit,1)
      AND
        tp_CalculatedVersion = 0
      AND
        tp_DeleteTransactionId = 0x

Вьюха Docs
USE [WSS_Content]
GO
/****** Object:  View [dbo].[Docs]    Script Date: 12/24/2009 12:36:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Docs]

AS
    SELECT
        *
    FROM
        AllDocs
    WHERE
        DeleteTransactionId = 0x
Индекс:
USE [WSS_Content]
GO
/****** Object:  Index [AllUserData_PK]    Script Date: 12/24/2009 12:49:48 ******/
ALTER TABLE [dbo].[AllUserData] ADD  CONSTRAINT [AllUserData_PK] PRIMARY KEY NONCLUSTERED 
(
	[tp_ListId] ASC,
	[tp_ID] ASC,
	[tp_CalculatedVersion] ASC,
	[tp_Level] ASC,
	[tp_RowOrdinal] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,  ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
24 дек 09, 09:55    [8111229]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос не меняя сам запрос только путем создания индексов.  [new]
вовкаkju
Member

Откуда:
Сообщений: 6
Выкладываю план запроса

К сообщению приложен файл (plan.rar - 10Kb) cкачать
24 дек 09, 10:20    [8111390]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить