Query Optimization - 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 2012 Forums

Transact-SQL (2012)

Query Optimization

Author

Topic

Doxey
Starting Member

4 Posts

Posted - 2013-10-04 : 12:56:39

Hey

I have a table called "transactions" with 22.5M rows and I need to pull out 4 columns of data. My problem is that it gets extremely slow when I 'double-dip' back into the table to run an aggregate function.

What will make this faster?

I have tried 2 ways to get the data, and both are very slow.

EDIT: updated table name from xfbtran to transactions. There is only one table being queried

Indexes:
There is 1 clustered index on two columns that make up a unique key, and there are 9 Non-Unique, Non-Clustered indexes. Of the non-unique/non-clustered indexes, there is one(index(Tran5)) that I can use which is tied to CustomerID and TranDate.

Version 1:

select
t.customerID,
t.siteID,
t.ProductID,
sum(t.Qty) as totalQuantity,
t2.StartDate
from transactions t
INNER JOIN --obtain the date of the 1st customer transaction
(

select
min(tt.trandate) as StartDate,
tt.CustomerID,
tt.SiteID
From transactions tt
Group by tt.CustomerID, tt.SiteID

)t2 ON (t2.CustomerID = t.CustomerID) and (t2.SiteID = t.SiteID)
where t.TranDate between '9/15/2013' and '9/28/2013'
Group by t.CustomerID, t.SiteID, t.ProductID, t2.StartDate

Version 2:

select
t.customerID,
t.siteID,
t.ProductID,
sum(t.Qty) as totalQuantity,
startDate = (select min(trandate) from transactions where customerID = t.CustomerID and siteID = t.SiteID) --obtain the date of the 1st customer transaction

from transactions t
where t.TranDate between '9/15/2013' and '9/28/2013'
Group by t.CustomerID, t.SiteID, t.ProductID

Neither one of these queries provide a run-time that I can live with at the moment. My ultimate goal is to try to get the 1st version working so I can easily use that "StartDate" column in some calculations within the same query. I will need that in some other joins that will be added to this query.

Is there another solution to speed up the extraction of the data?

Thanks for your help!
Doxey

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-04 : 13:11:29

What does the execution plan show? And what indexes do you have on these tables?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Doxey
Starting Member

4 Posts

Posted - 2013-10-04 : 13:17:42

quote:
Originally posted by tkizer

What does the execution plan show? And what indexes do you have on these tables?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Hi Tara

I haven't looked at the execution plan yet, but the table has an index set up for customerID and trandate. Edit: non-clustered

There is no identity set up on the table either, which I would suspect could have helped if it did. I just inherited this project from another guy that put this system together many years ago.

Thanks
Doxey

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-10-04 : 13:45:49

[code]SELECT t.CustomerID,
t.SiteID,
t.ProductID,
t.TotalQuantity,
w.StartDate
FROM (
SELECT CustomerID,
SiteID,
ProductID,
SUM(Qty) AS TotalQuantity
FROM dbo.Transactions
WHERE TranDate BETWEEN '9/15/2013' AND '9/28/2013'
GROUP BY CustomerID,
SiteID,
ProductID
) AS t
INNER JOIN (
SELECT MIN(Trandate) AS StartDate,
CustomerID,
SiteID
FROM dbo.xfbTran
GROUP BY CustomerID,
SiteID
) AS w ON w.CustomerID = t.CustomerID
AND w.SiteID = t.SiteID;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Doxey
Starting Member

4 Posts

Posted - 2013-10-04 : 14:12:39

quote:

Originally posted by SwePeso

SELECT		t.CustomerID,
		t.SiteID,
		t.ProductID,
		t.TotalQuantity,
		w.StartDate
FROM		(
			SELECT		CustomerID,
					SiteID,
					ProductID,
					SUM(Qty) AS TotalQuantity
			FROM		dbo.Transactions
			WHERE		TranDate BETWEEN '9/15/2013' AND '9/28/2013'
			GROUP BY	CustomerID,
					SiteID,
					ProductID
		) AS t
INNER JOIN	(
			SELECT		MIN(Trandate) AS StartDate,
					CustomerID,
					SiteID
			FROM		dbo.xfbTran
			GROUP BY	CustomerID, 
					SiteID
		) AS w ON w.CustomerID = t.CustomerID
			AND w.SiteID = t.SiteID;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Thanks SwePeso

I just ran your version and it is the equivalent of the Version 2 that I previously posted. After 3.5 minutes it has output only 60 rows out of 1500.

Just looked at the indexes too.
There is 1 clustered index on two columns that make up a unique key, and there are 9 Non-Unique, Non-Clustered indexes. Of the non-unique/non-clustered indexes, there is one(index(Tran5)) that I can use which is tied to CustomerID and TranDate.

Am I needing to tie into those indexes in the query? Like "FROM transactions WITH (index(Tran5))..."? I have tried that earlier with no noticeable changes in performance. But I may not be using it properly.

Any ideas?

Thanks
Doxey

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-10-05 : 08:45:26

That is an index hint and probably bad for your query.
Let the Query Optimizer do what it's designed for and only change the query with hints if you are 100% sure what you are doing.

Do you have the possibility to make a clustered index on the Transaction table on TranDate column?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Doxey
Starting Member

4 Posts

Posted - 2013-10-07 : 09:30:09

Yes, I can make one on the Trandate column only.
I'll give that a shot.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources