performance - 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)

performance

Author

Topic

avijit_mca
Posting Yak Master

109 Posts

Posted - 2010-04-01 : 05:34:15

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 05:57:01

are you sure you will have always single record for below cases?
SELECT tp_ID
FROM dbo.AllLists
WHERE (tp_Title = 'offered courses')

and

SELECT tp_ID
FROM dbo.AllLists AS AllLists_1
WHERE (tp_Title = 'Course Detail')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

avijit_mca
Posting Yak Master

109 Posts

Posted - 2010-04-01 : 06:23:56

yes.
that is confirm

Regards,
avijit

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 06:29:04

You ought to have UNIQUE CONSTRAINT on tp_Title then ... and that, in itself, will speed up your query

avijit_mca
Posting Yak Master

109 Posts

Posted - 2010-04-01 : 06:41:04

yes. but i want to find out which query will be more fast compare to other.

Regards,
avijit

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-04-01 : 06:43:41

What did the execution plans say for both queries?

Harsh Athalye
http://www.letsgeek.net/

avijit_mca
Posting Yak Master

109 Posts

Posted - 2010-04-01 : 07:29:15

execution plan really confuse me.
but in query 2 parallel execution happend.

Regards,
avijit

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 07:38:47

quote:
Originally posted by avijit_mca

execution plan really confuse me.
but in query 2 parallel execution happend.

Regards,
avijit

you were looking at graphical of xml one?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 08:07:10

" execution plan really confuse me"

Post the execution plan (i.e. the text format one) here and we'll explain it to you.


SET SHOWPLAN_TEXT ON
GO

... Your Query Here ... 

SET SET SHOWPLAN_TEXT OFF
GO

avijit_mca
Posting Yak Master

109 Posts

Posted - 2010-04-07 : 02:07:37

Query 1 :
SELECT C.nvarchar1 AS TempAB_ID, C.nvarchar2, C.nvarchar3 AS PermAB_ID, C.nvarchar4 AS [Course_ID.], C.nvarchar5 AS Course_N, C.nvarchar6 AS Expr33, C.nvarchar7 AS Expr34, CD.nvarchar1 AS Course_ID_CD, CD.nvarchar3 AS Course_Name, CD.nvarchar4 AS MinQlf FROM dbo.AllUserData AS C INNER JOIN dbo.AllUserData AS CD ON C.nvarchar4 = CD.nvarchar1 inner join dbo.AllLists L1 on C.tp_ListId=L1.tp_ID inner join dbo.AllLists L2 on CD.tp_ListId=L2.tp_ID where L1.tp_Title = 'offered courses' and L2.tp_Title = 'Course Detail'

Showplan :
|--Nested Loops(Inner Join, OUTER REFERENCES:([L1].[tp_WebId], [L1].[tp_ID]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([C].[tp_ListId]))
| |--Hash Match(Inner Join, HASH:([CD].[nvarchar1])=([C].[nvarchar4]), RESIDUAL:([wss].[dbo].[AllUserData].[nvarchar4] as [C].[nvarchar4]=[wss].[dbo].[AllUserData].[nvarchar1] as [CD].[nvarchar1]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([CD].[tp_SiteId], [CD].[tp_RowOrdinal], [CD].[tp_DirName], [CD].[tp_LeafName], [CD].[tp_DeleteTransactionId], [CD].[tp_Level], [CD].[tp_IsCurrentVersion], [CD].[tp_CalculatedVersion], [Expr1008]) WITH UNORDERED PREFETCH)
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([L2].[tp_ID]))
| | | | |--Clustered Index Scan(OBJECT:([wss].[dbo].[AllLists].[AllLists_PK] AS [L2]), WHERE:([wss].[dbo].[AllLists].[tp_Title] as [L2].[tp_Title]=N'Course Detail'))
| | | | |--Index Seek(OBJECT:([wss].[dbo].[AllUserData].[AllUserData_PK] AS [CD]), SEEK:([CD].[tp_ListId]=[wss].[dbo].[AllLists].[tp_ID] as [L2].[tp_ID]) ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT:([wss].[dbo].[AllUserData].[AllUserData_Url] AS [CD]), SEEK:([CD].[tp_SiteId]=[wss].[dbo].[AllUserData].[tp_SiteId] as [CD].[tp_SiteId] AND [CD].[tp_DeleteTransactionId]=[wss].[dbo].[AllUserData].[tp_DeleteTransactionId] as [CD].[tp_DeleteTransactionId] AND [CD].[tp_IsCurrentVersion]=[wss].[dbo].[AllUserData].[tp_IsCurrentVersion] as [CD].[tp_IsCurrentVersion] AND [CD].[tp_DirName]=[wss].[dbo].[AllUserData].[tp_DirName] as [CD].[tp_DirName] AND [CD].[tp_LeafName]=[wss].[dbo].[AllUserData].[tp_LeafName] as [CD].[tp_LeafName] AND [CD].[tp_CalculatedVersion]=[wss].[dbo].[AllUserData].[tp_CalculatedVersion] as [CD].[tp_CalculatedVersion] AND [CD].[tp_Level]=[wss].[dbo].[AllUserData].[tp_Level] as [CD].[tp_Level] AND [CD].[tp_RowOrdinal]=[wss].[dbo].[AllUserData].[tp_RowOrdinal] as [CD].[tp_RowOrdinal]) LOOKUP ORDERED FORWARD)
| | |--Clustered Index Scan(OBJECT:([wss].[dbo].[AllUserData].[AllUserData_Url] AS [C]))
| |--Index Seek(OBJECT:([wss].[dbo].[AllLists].[Lists_FullText] AS [L1]), SEEK:([L1].[tp_ID]=[wss].[dbo].[AllUserData].[tp_ListId] as [C].[tp_ListId]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([wss].[dbo].[AllLists].[AllLists_PK] AS [L1]), SEEK:([L1].[tp_WebId]=[wss].[dbo].[AllLists].[tp_WebId] as [L1].[tp_WebId] AND [L1].[tp_ID]=[wss].[dbo].[AllLists].[tp_ID] as [L1].[tp_ID]), WHERE:([wss].[dbo].[AllLists].[tp_Title] as [L1].[tp_Title]=N'offered courses') LOOKUP ORDERED FORWARD)

Query 2 :
SELECT C.nvarchar1 AS TempAB_ID, C.nvarchar2, C.nvarchar3 AS PermAB_ID, C.nvarchar4 AS [Course_ID.], C.nvarchar5 AS Course_N, C.nvarchar6 AS Expr33, C.nvarchar7 AS Expr34, CD.nvarchar1 AS Course_ID_CD, CD.nvarchar3 AS Course_Name, CD.nvarchar4 AS MinQlf FROM dbo.AllUserData AS C INNER JOIN dbo.AllUserData AS CD ON C.nvarchar4 = CD.nvarchar1 WHERE (C.tp_ListId = (SELECT tp_ID FROM dbo.AllLists WHERE (tp_Title = 'offered courses'))) AND (CD.tp_ListId = (SELECT tp_ID FROM dbo.AllLists AS AllLists_1 WHERE (tp_Title = 'Course Detail')))

Showplan :

|--Hash Match(Inner Join, HASH:([CD].[nvarchar1])=([C].[nvarchar4]), RESIDUAL:([wss].[dbo].[AllUserData].[nvarchar4] as [C].[nvarchar4]=[wss].[dbo].[AllUserData].[nvarchar1] as [CD].[nvarchar1]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([CD].[tp_SiteId], [CD].[tp_RowOrdinal], [CD].[tp_DirName], [CD].[tp_LeafName], [CD].[tp_DeleteTransactionId], [CD].[tp_Level], [CD].[tp_IsCurrentVersion], [CD].[tp_CalculatedVersion]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1014]))
| | |--Assert(WHERE:(CASE WHEN [Expr1013]>(1) THEN (0) ELSE NULL END))
| | | |--Stream Aggregate(DEFINE:([Expr1013]=Count(*), [Expr1014]=ANY([wss].[dbo].[AllLists].[tp_ID] as [AllLists_1].[tp_ID])))
| | | |--Clustered Index Scan(OBJECT:([wss].[dbo].[AllLists].[AllLists_PK] AS [AllLists_1]), WHERE:([wss].[dbo].[AllLists].[tp_Title] as [AllLists_1].[tp_Title]=N'Course Detail'))
| | |--Index Seek(OBJECT:([wss].[dbo].[AllUserData].[AllUserData_PK] AS [CD]), SEEK:([CD].[tp_ListId]=[Expr1014]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([wss].[dbo].[AllUserData].[AllUserData_Url] AS [CD]), SEEK:([CD].[tp_SiteId]=[wss].[dbo].[AllUserData].[tp_SiteId] as [CD].[tp_SiteId] AND [CD].[tp_DeleteTransactionId]=[wss].[dbo].[AllUserData].[tp_DeleteTransactionId] as [CD].[tp_DeleteTransactionId] AND [CD].[tp_IsCurrentVersion]=[wss].[dbo].[AllUserData].[tp_IsCurrentVersion] as [CD].[tp_IsCurrentVersion] AND [CD].[tp_DirName]=[wss].[dbo].[AllUserData].[tp_DirName] as [CD].[tp_DirName] AND [CD].[tp_LeafName]=[wss].[dbo].[AllUserData].[tp_LeafName] as [CD].[tp_LeafName] AND [CD].[tp_CalculatedVersion]=[wss].[dbo].[AllUserData].[tp_CalculatedVersion] as [CD].[tp_CalculatedVersion] AND [CD].[tp_Level]=[wss].[dbo].[AllUserData].[tp_Level] as [CD].[tp_Level] AND [CD].[tp_RowOrdinal]=[wss].[dbo].[AllUserData].[tp_RowOrdinal] as [CD].[tp_RowOrdinal]) LOOKUP ORDERED FORWARD)
|--Nested Loops(Inner Join, OUTER REFERENCES:([C].[tp_SiteId], [C].[tp_RowOrdinal], [C].[tp_DirName], [C].[tp_LeafName], [C].[tp_DeleteTransactionId], [C].[tp_Level], [C].[tp_IsCurrentVersion], [C].[tp_CalculatedVersion]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1012]))
| |--Assert(WHERE:(CASE WHEN [Expr1011]>(1) THEN (0) ELSE NULL END))
| | |--Stream Aggregate(DEFINE:([Expr1011]=Count(*), [Expr1012]=ANY([wss].[dbo].[AllLists].[tp_ID])))
| | |--Clustered Index Scan(OBJECT:([wss].[dbo].[AllLists].[AllLists_PK]), WHERE:([wss].[dbo].[AllLists].[tp_Title]=N'offered courses'))
| |--Index Seek(OBJECT:([wss].[dbo].[AllUserData].[AllUserData_PK] AS [C]), SEEK:([C].[tp_ListId]=[Expr1012]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([wss].[dbo].[AllUserData].[AllUserData_Url] AS [C]), SEEK:([C].[tp_SiteId]=[wss].[dbo].[AllUserData].[tp_SiteId] as [C].[tp_SiteId] AND [C].[tp_DeleteTransactionId]=[wss].[dbo].[AllUserData].[tp_DeleteTransactionId] as [C].[tp_DeleteTransactionId] AND [C].[tp_IsCurrentVersion]=[wss].[dbo].[AllUserData].[tp_IsCurrentVersion] as [C].[tp_IsCurrentVersion] AND [C].[tp_DirName]=[wss].[dbo].[AllUserData].[tp_DirName] as [C].[tp_DirName] AND [C].[tp_LeafName]=[wss].[dbo].[AllUserData].[tp_LeafName] as [C].[tp_LeafName] AND [C].[tp_CalculatedVersion]=[wss].[dbo].[AllUserData].[tp_CalculatedVersion] as [C].[tp_CalculatedVersion] AND [C].[tp_Level]=[wss].[dbo].[AllUserData].[tp_Level] as [C].[tp_Level] AND [C].[tp_RowOrdinal]=[wss].[dbo].[AllUserData].[tp_RowOrdinal] as [C].[tp_RowOrdinal]) LOOKUP ORDERED FORWARD)

Regards,
avijit

avijit_mca
Posting Yak Master

109 Posts

Posted - 2010-04-08 : 01:04:55

Hi Kristen,
i hv posted execution plan .pls check it.
if need i will post for similar query but less no of join..so it will be easy to understand.

Regards,
avijit

Kristen
Test

22859 Posts

Posted - 2010-04-08 : 03:13:59

I did make a note of the thread yesterday, but don't have enough time to spend on it at the moment. Someone else might have some time though.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources