Perfomance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Perfomance

Author

Topic

ranganath
Posting Yak Master

209 Posts

Posted - 2008-09-09 : 08:46:56

The Exec of Below Query returns Nearly 17 lakhs of record and Takes Nearly 2 Min.

DECLARE @Site_Id AS VARCHAR (50)
DECLARE @Status_Id AS VARCHAR (50)
DECLARE @ToDay AS DATETIME
DECLARE @Site_Content_Object_Object_Id AS VARCHAR (50)

SELECT @Site_Id = Site_Id FROM Sites WHERE Site_Name = 'FOXFAST'
SELECT @Status_Id = Status_Id FROM Status WHERE Status_Description = 'PUBLISH'
SET @ToDay = GETDATE()

SELECT @Site_Content_Object_Object_Id = Object_Object_Id
FROM Xref_Object_Object
WHERE Object_Display_Name = 'Site Content'
AND Parent_Object_Object_Id IS NULL
AND Site_Id = @Site_Id

CREATE TABLE #TempObjects
(
Object_Object_Id UNIQUEIDENTIFIER ,
Parent_Object_Object_Id UNIQUEIDENTIFIER ,
[Object_Id] UNIQUEIDENTIFIER ,
[Object_Name] VARCHAR (128),
Object_Display_Name NVARCHAR (1024),
Sort_Order INT,
Item_Name NVARCHAR (1024),
Item_Value NVARCHAR (1024)
)

CREATE NONCLUSTERED INDEX IX_Object_Object_Id ON #TempObjects(Object_Object_Id )

INSERT INTO #TempObjects
SELECT XOO.Object_Object_Id , XOO.Parent_Object_Object_Id , XOO.CHILD_Object_Id , od.Object_Name,
XOO.Object_Display_Name, XOO.Sort_Order, ITD.Item_Name , I.Item_Value
FROM Xref_Object_Object AS XOO WITH (NOLOCK)
INNER JOIN Xref_Object_Object_Def AS XOOD WITH (NOLOCK) ON XOO.OBJECT_Object_Def_ID = XOOD.OBJECT_Object_Def_ID
INNER JOIN Object_Def AS OD WITH (NOLOCK) ON XOOD.CHILD_Object_Def_ID = OD.Object_Def_ID
INNER JOIN Xref_Object_Item AS XOI WITH (NOLOCK) ON XOO.Child_Object_Id = XOI.Object_Id
INNER JOIN Item AS I WITH (NOLOCK) ON XOI.Item_Id = I.Item_Id
INNER JOIN Item_Def AS ITD WITH (NOLOCK) ON I.Item_Def_Id = ITD.Item_Def_Id
WHERE XOO.Site_Id = @Site_Id
AND XOO.Deleted_Date IS NULL
AND (@ToDay >= XOO.Effective_Date OR XOO.Effective_Date IS NULL)
AND (@ToDay <= XOO.Expiry_Date OR XOO.Expiry_Date IS NULL)
AND XOO.STATUS_ID = @STATUS_ID

the following are count of Tables

SELECT COUNT(*) FROM Xref_Object_Object --707727
SELECT COUNT(*) FROM Xref_Object_Object_Def--2177
SELECT COUNT(*) FROM Object_Def--180

SELECT COUNT(*) FROM Xref_Object_Item --1471550
SELECT COUNT(*) FROM Item --1471564
SELECT COUNT(*) FROM Item_Def --368

The Above query modifed as below
---------------------------------------------------

DECLARE @Site_Id AS VARCHAR (50)
DECLARE @Status_Id AS VARCHAR (50)
DECLARE @ToDay AS DATETIME
DECLARE @Site_Content_Object_Object_Id AS VARCHAR (50)

SELECT @Site_Id = Site_Id FROM Sites WHERE Site_Name = 'FOXFAST'
SELECT @Status_Id = Status_Id FROM Status WHERE Status_Description = 'PUBLISH'
SET @ToDay = GETDATE()

SELECT @Site_Content_Object_Object_Id = Object_Object_Id
FROM Xref_Object_Object
WHERE Object_Display_Name = 'Site Content'
AND Parent_Object_Object_Id IS NULL
AND Site_Id = @Site_Id

CREATE TABLE #TempObjects
(
Object_Object_Id UNIQUEIDENTIFIER ,
Parent_Object_Object_Id UNIQUEIDENTIFIER ,
[Object_Id] UNIQUEIDENTIFIER ,
[Object_Name] VARCHAR (128),
Object_Display_Name NVARCHAR (1024),
Sort_Order INT,
Item_Name NVARCHAR (1024),
Item_Value NVARCHAR (1024)
)

CREATE NONCLUSTERED INDEX IX_Object_Object_Id ON #TempObjects (Object_Object_Id )

CREATE TABLE #Objects
(
Object_Object_Id UNIQUEIDENTIFIER ,
Parent_Object_Object_Id UNIQUEIDENTIFIER ,
[Object_Id] UNIQUEIDENTIFIER ,
[OBJECT_NAME] NVARCHAR (1024),
Object_Display_Name NVARCHAR (1024),
Sort_Order INT
)

CREATE NONCLUSTERED INDEX IX_Object_Object_Id ON #Objects (Object_Object_Id , Parent_Object_Object_Id, [Object_Id])

INSERT INTO #Objects
Select DISTINCT XOO.Object_Object_Id , XOO.Parent_Object_Object_Id , XOO.Child_Object_Id ,
OD.OBJECT_NAME,
XOO.Object_Display_Name, XOO.Sort_Order
From Xref_Object_Object XOO
INNER JOIN Xref_Object_Object_Def AS XOOD WITH (NOLOCK) ON XOO.Object_Object_Def_ID = XOOD.Object_Object_Def_ID
INNER JOIN Object_Def AS OD WITH (NOLOCK) ON XOOD.Child_Object_Def_ID = OD.Object_Def_ID
where XOO.Site_Id = @Site_Id
AND XOO.Deleted_Date IS NULL
AND (@ToDay >= Effective_Date OR Effective_Date IS NULL)
AND (@ToDay <= Expiry_Date OR Expiry_Date IS NULL)
AND Status_Id = @Status_Id

Create Table #Item
(
Object_Id UNIQUEIDENTIFIER ,
Item_Name NVARCHAR (1024) ,
Item_Value NVARCHAR (1024)
)

CREATE NONCLUSTERED INDEX IX_Object_Id ON #Item (Object_Id )

Insert into #Item
Select DISTINCT XOI.OBJECT_ID ,
ITD.Item_Name ,
I.Item_Value
From Xref_Object_Item XOI
INNER JOIN Item AS I WITH (NOLOCK) ON XOI.Item_Id = I.Item_Id
INNER JOIN Item_Def AS ITD WITH (NOLOCK) ON I.Item_Def_Id = ITD.Item_Def_Id

INSERT INTO #TempObjects
SELECT XOO.Object_Object_Id , XOO.Parent_Object_Object_Id , XOO.OBJECT_ID , XOO.OBJECT_NAME,
XOO.Object_Display_Name, XOO.Sort_Order, I.Item_Value , I.Item_Name
FROM #Objects AS XOO
INNER JOIN #Item I ON XOO.OBJECT_ID = I.OBJECT_ID

Select * From #TempObjects

Drop Table #Objects, #Item, #TempObjects

Note :Created NonClusterd Index on Tables

but its also Taking Nearly 2 min.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-09 : 09:05:56

Of course returning 1,700,000 records will take time due to network speed and other factors.

E 12°55'05.63"
N 56°04'39.26"