Why the massive difference in execution times? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

Why the massive difference in execution times?

Author

Topic

fullmonty
Starting Member

1 Post

Posted - 2006-12-20 : 20:05:29

Hello All,

I have an issue with the below queries which I am running against the same database.

The first query returns results within seconds however the second query, which is more restrictive than the first (so I thought) takes around 50 minutes by the end of the day. The query progressively takes longer throughout the day as more data is inserted into the tables, while the first query is pretty consistent throughout the day and will only fluctuate dependent on the load on the system.

The tables in question have approximately the following number of rows

ReportItems – 16,250,000
ReportTransactions – 6,000,000
Locations – 125
LocationTypesLocations - 126

When looking at the execution plan for QUERY2 it appears the left outer join on VT is searching through 310,000,000 rows whereas QUERY1 does not. I am not very good at analysing the results of the execution plan and may be barking up the wrong tree.

I have run both queries through the index tuning wizard and it cannot suggest any further indexes to add.

Any help to understand the issue would be greatly appreciated!!

Thanks

Monty

QUERY1

SELECT RT.Effective_Day_Value, FLOOR(RT.Effective_Date_Value / 10000) % 100, COUNT(DISTINCT R.Report_Transaction_ID),
SUM(R.Net_Sales_Value + R.Net_Returns_Value)
FROM ReportItems R WITH(NOLOCK)
INNER JOIN ReportTransactions RT WITH(NOLOCK) ON
R.Report_Transaction_ID = RT.Report_Transaction_ID
LEFT OUTER JOIN Locations L WITH(NOLOCK) ON R.Store_ID = L.Location_ID AND L.Type = 2
LEFT OUTER JOIN (
SELECT RT2.Is_Reversal as Is_Reversal, R2.Original_Store_ID as
Original_Store_ID, R2.Original_Terminal_ID as
Original_Terminal_ID,
R2.Original_Transaction_ID as Original_Transaction_ID, R2.Original_Transaction_Date as Original_Transaction_Date
FROM ReportItems R2 WITH(NOLOCK)
INNER JOIN ReportTransactions RT2 WITH(NOLOCK) ON R2.Report_Transaction_ID = RT2.Report_Transaction_ID
WHERE R2.Item_Type = 29
AND RT2.Effective_Session_Date >= {ts '2006-12-20 00:00:00.000'}
AND RT2.Effective_Session_Date <= {ts '2006-12-20 23:59:59.000'}
GROUP BY RT2.Is_Reversal, R2.Original_Store_ID, R2.Original_Terminal_ID, R2.Original_Transaction_ID, R2.Original_Transaction_Date )
VT ON RT.Store_ID = VT.Original_Store_ID AND RT.Terminal_ID = VT.Original_Terminal_ID
AND RT.Transaction_ID = VT.Original_Transaction_ID AND RT.Transaction_Creation_Date = VT.Original_Transaction_Date
WHERE (((R.Item_Type >= 1)
AND (R.Item_Type <= 3) ) OR ((R.Item_Type >= 6)
AND (R.Item_Type <= 7) ) OR ((R.Item_Type >= 81)
AND (R.Item_Type <= 84) ) )
AND (R.Item_Voided = 0)
AND (R.Void_Type = 0)
AND (RT.Is_Reversal = 0 OR RT.Is_Reversal IS NULL)
AND (VT.Is_Reversal = 0 OR VT.Is_Reversal IS NULL)
AND (RT.Effective_Session_Date > {ts '2006-12-20 00:00:00.000'})
AND (RT.Effective_Session_Date < {ts '2006-12-20 23:59:59.000'})
AND (R.Base_Currency_ID = N'GBP')
AND (RT.Training_Mode IS NULL OR RT.Training_Mode = 0)
GROUP BY RT.Effective_Day_Value, FLOOR(RT.Effective_Date_Value / 10000) % 100
ORDER BY RT.Effective_Day_Value

QUERY2

SELECT RT.Effective_Day_Value, FLOOR(RT.Effective_Date_Value / 10000) % 100, COUNT(DISTINCT R.Report_Transaction_ID),
SUM(R.Net_Sales_Value + R.Net_Returns_Value)
FROM ReportItems R WITH(NOLOCK)
INNER JOIN ReportTransactions RT WITH(NOLOCK) ON R.Report_Transaction_ID = RT.Report_Transaction_ID
LEFT OUTER JOIN Locations L WITH(NOLOCK) ON R.Store_ID = L.Location_ID AND L.Type = 2
LEFT OUTER JOIN LocationTypesLocations LTL WITH(NOLOCK) ON L.Location_ID = LTL.Location_ID AND LTL.Location_Type = 2
LEFT OUTER JOIN (
SELECT RT2.Is_Reversal as Is_Reversal, R2.Original_Store_ID as Original_Store_ID, R2.Original_Terminal_ID as Original_Terminal_ID,
R2.Original_Transaction_ID as Original_Transaction_ID, R2.Original_Transaction_Date as Original_Transaction_Date
FROM ReportItems R2 WITH(NOLOCK)
INNER JOIN ReportTransactions RT2 WITH(NOLOCK) ON R2.Report_Transaction_ID = RT2.Report_Transaction_ID
WHERE R2.Item_Type = 29
AND RT2.Effective_Session_Date >= {ts '2006-12-20 00:00:00.000'}
AND RT2.Effective_Session_Date <= {ts '2006-12-20 23:59:59.000'}
GROUP BY RT2.Is_Reversal, R2.Original_Store_ID, R2.Original_Terminal_ID, R2.Original_Transaction_ID, R2.Original_Transaction_Date )
VT ON RT.Store_ID = VT.Original_Store_ID AND RT.Terminal_ID = VT.Original_Terminal_ID
AND RT.Transaction_ID = VT.Original_Transaction_ID AND RT.Transaction_Creation_Date = VT.Original_Transaction_Date
WHERE (((R.Item_Type >= 1)
AND (R.Item_Type <= 3) ) OR ((R.Item_Type >= 6)
AND (R.Item_Type <= 7) ) OR ((R.Item_Type >= 81)
AND (R.Item_Type <= 84) ) )
AND (R.Item_Voided = 0)
AND (R.Void_Type = 0)
AND (RT.Is_Reversal = 0 OR RT.Is_Reversal IS NULL)
AND (VT.Is_Reversal = 0 OR VT.Is_Reversal IS NULL)
AND (RT.Effective_Session_Date > {ts '2006-12-20 00:00:00.000'})
AND (RT.Effective_Session_Date < {ts '2006-12-20 23:59:59.000'})
AND (R.Base_Currency_ID = N'GBP')
AND (LTL.Location_Type_ID IN (N'FC'))
AND (RT.Training_Mode IS NULL OR RT.Training_Mode = 0)
GROUP BY RT.Effective_Day_Value, FLOOR(RT.Effective_Date_Value / 10000) % 100
ORDER BY RT.Effective_Day_Value

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 03:13:33

In query2, you have introduced a new table, named LocationTypesLocations, which do not exist in query1.
This is query1 rewritten, so it might run a little faster

SELECT		RT.Effective_Day_Value,
		FLOOR(RT.Effective_Date_Value / 10000) % 100,
		COUNT(DISTINCT R.Report_Transaction_ID), 
		SUM(R.Net_Sales_Value + R.Net_Returns_Value) 
FROM		ReportItems R WITH(NOLOCK) 
INNER JOIN	ReportTransactions RT WITH(NOLOCK) ON R.Report_Transaction_ID = RT.Report_Transaction_ID 
			AND RT.Effective_Session_Date >= '20061220'
			AND RT.Effective_Session_Date < '20061221'
			AND (RT.Training_Mode = 0 OR RT.Training_Mode IS NULL) 
			AND (RT.Is_Reversal = 0 OR RT.Is_Reversal IS NULL) 
LEFT JOIN	Locations L WITH(NOLOCK) ON R.Store_ID = L.Location_ID
			AND L.Type = 2 
LEFT JOIN	( 
			SELECT DISTINCT	RT2.Is_Reversal as Is_Reversal,
					R2.Original_Store_ID as Original_Store_ID,
					R2.Original_Terminal_ID as Original_Terminal_ID, 
					R2.Original_Transaction_ID as Original_Transaction_ID,
					R2.Original_Transaction_Date as Original_Transaction_Date 
			FROM		ReportItems R2 WITH(NOLOCK) 
			INNER JOIN	ReportTransactions RT2 WITH(NOLOCK) ON R2.Report_Transaction_ID = RT2.Report_Transaction_ID 
						AND RT2.Effective_Session_Date >= '20061220' 
						AND RT2.Effective_Session_Date < '20061221'
			WHERE		R2.Item_Type = 29
		) VT ON RT.Store_ID = VT.Original_Store_ID
			AND RT.Terminal_ID = VT.Original_Terminal_ID 
			AND RT.Transaction_ID = VT.Original_Transaction_ID
			AND RT.Transaction_Creation_Date = VT.Original_Transaction_Date 
			AND (VT.Is_Reversal = 0 OR VT.Is_Reversal IS NULL) 
WHERE		R.Base_Currency_ID = N'GBP'
		AND R.Item_Voided = 0
		AND R.Void_Type = 0
		AND R.Item_Type IN (1, 2, 3, 6, 7, 81, 82, 83, 84)
GROUP BY	RT.Effective_Day_Value,
		FLOOR(RT.Effective_Date_Value / 10000) % 100 
ORDER BY	RT.Effective_Day_Value

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources