Help on Select for big table.

Hello People,

This is a big table which stores 2 million records for all the customers.

Columns of tblTransactions:
+++++++++++++++++++++++++++++++++++++++++++++++++
TranscID, CustomerID, TranscDetail, WhenCreated
+++++++++++++++++++++++++++++++++++++++++++++++++
*TranscID is PK,
*CustomerID is FK
*WhenCreated is noncluster index in DESC order

I'd like to Select the latest 1 transaction for all the customers.
(One customer may have many transactions in this table)

Can someone shed some light on how I can make that happend efficiently.

Thanks in advance!

select CustomerID , max(WhenCreated) as max_Customer_WhenCreated <br />from tblTransactions<br />group by CustomerID<br /><br />If this works to slow, maybe you can add an index for (CustomerID, WhenCreated) [<img src='/community/emoticons/emotion-11.gif' alt='8)' />]

alzdba,

I have tried your one succesfully. and it performs well

select CustomerID , max(WhenCreated) as max_Customer_WhenCreated
from tblTransactions
group by CustomerID

But, what happends if I want to get the transcID for each result?
Do I go
select TranscID, CustomerID, max(WhenCreated) as max_Customer_WhenCreated
from tblTransactions
group by TranscID, CustomerID

? I have no luck with this. it gave me million records..

Thanks for the fast reply.

Select A.CustomerID, A.max_Customer_WhenCreated
, B.TranscID
from (
select CustomerID , max(WhenCreated) as max_Customer_WhenCreated
from tblTransactions
group by CustomerID ) A
Inner join tblTransactions B
on A.CustomerID = B.CustomerID
and A.max_Customer_WhenCreated = B.WhenCreated

You are the man, I am 1/2 sleep. it is 2 am over here.
I appreciate it.



In his heart, a man plans his course, but the LORD determines his steps

It's 2pm overhere. And I need my bed too [<img src='/community/emoticons/emotion-2.gif' alt='' />]<br /><br />I'll have to wait !<br /><br />I hope you'll get out of troubles soon, or are you just hobby-ing [?]

It solved the problem.<br />Like 2 sec something to get the result which is very well.<br />[<img src='/community/emoticons/emotion-1.gif' alt='' />][8D][<img src='/community/emoticons/emotion-1.gif' alt='' />] Nice to meet you here.<br />

HTH

Further Please teach me how to SQL.<br /><br />This is a big table which stores 2 million records for all the customers.<br /><br />Columns of tblTransactions:<br />+++++++++++++++++++++++++++++++++++++++++++++++++<br />TranscID, CustomerID, TranscDetail, WhenCreated<br />+++++++++++++++++++++++++++++++++++++++++++++++++<br />*TranscID is PK,<br />*CustomerID is FK<br />*WhenCreated is noncluster index in DESC order<br /><br /><br />I'd like to Select the latest TWO transactions for all the customers<br /><br />(One customer may have many transactions in this table)<br /><br />Thanks in advance <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br /><hr noshade size="1"><br /><i><font color="black">In his heart, a man plans his course, but the LORD determines his steps</font id="black"></i>

This can be done using a correlated in-list.<br />You'll find more examples in SSP <img src='/community/emoticons/emotion-5.gif' alt='' /><br /><br />How about this :<br />Select A.CustomerID, A.max_Customer_WhenCreated <br />, B.TranscID <br />from (<br />SELECT DISTINCT T1.CustomerID , T1.WhenCreated<br />FROM tblTransactions T1<br />WHERE T1.WhenCreated IN (SELECT TOP 2 T2.WhenCreated<br />from tblTransactions T2<br />where T2.CustomerID = T1.CustomerID<br /> Group by T2.WhenCreated<br />ORDER BY T2.WhenCreated desc )<br />) A<br />Inner join tblTransactions B<br />on A.CustomerID = B.CustomerID<br />and A.WhenCreated = B.WhenCreated

Alzdba... Thanks.. My friend.
I will try out that idea.



In his heart, a man plans his course, but the LORD determines his steps

Hi Alzdba, What is SSP by the way? <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br /><br /><br /><hr noshade size="1"><br /><i><font color="black">In his heart, a man plans his course, but the LORD determines his steps</font id="black"></i>

Sql-Server-Performance.com (this site <img src='/community/emoticons/emotion-5.gif' alt='' /> )

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by alzdba</i><br /><br />This can be done using a correlated in-list.<br />You'll find more examples in SSP <img src='/community/emoticons/emotion-5.gif' alt='' /><br /><br />How about this :<br />Select A.CustomerID, A.max_Customer_WhenCreated <br />, B.TranscID <br />from (<br />SELECT DISTINCT T1.CustomerID , T1.WhenCreated<br />FROM tblTransactions T1<br />WHERE T1.WhenCreated IN (SELECT TOP 2 T2.WhenCreated<br />from tblTransactions T2<br />where T2.CustomerID = T1.CustomerID<br /> Group by T2.WhenCreated<br />ORDER BY T2.WhenCreated desc )<br />) A<br />Inner join tblTransactions B<br />on A.CustomerID = B.CustomerID<br />and A.WhenCreated = B.WhenCreated<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">This will throw an error, because the derived table A does not contain a column called max_Customer_WhenCreated. You'll need to add a GROUP BY and a MAX expression in the outer query.<br />

Thanks for the notification [:I]

Actualy it should be :
SELECT ...
, A.max_Customer_WhenCreated , A.WhenCreated ...

SELECT DISTINCT T1.CustomerID , T1.WhenCreated FROM tblTransactions T1

WHERE T1.WhenCreated IN
( SELECT TOP 2 T2.WhenCreated FROM tblTransactions T2
WHERE T2.CustomerID = T1.CustomerID AND T2.WhenCreated >= @GivenDateTime
GROUP BY T2.WhenCreated ORDER BY T2.WhenCreated DESC
)

Indexed on CustomerID AND WhenCreated

But it is really slow, if I have 5 Customers and each got million records.
And I can not wait until the query shows something.

Anyway, Thanks very much Alzdba and Adriaan,
I will figure out in an other way, maybe create a table variable for storing some
tempri data then select from the table in the memory.

At the end of the day I suppose It really depends on how long ago WhenCreated is defined for.
The closer to now, the faster.



In his heart, a man plans his course, but the LORD determines his steps

What indexes do you have on tblTransactions?

If you want to speed this up, you also need an index to support the CustomerID.
SQL will then be able to perform you query using only the indexes.

If you cannot add the index or if you did remove it because it did hurt your performance to much, then you may indeed want to consider a temporary object.
e.g;
create table #tmpCustomer (CustomerID int not null, WhenCreated datetime not null, primary key (CustomerId, WhenCreated) )

Insert into #tmpCustomer
SELECT T1.CustomerID , T1.WhenCreated
FROM tblTransactions T1
WHERE T1.WhenCreated IN (SELECT TOP 2 T2.WhenCreated
from tblTransactions T2
where T2.CustomerID = T1.CustomerID
Group by T2.WhenCreated
ORDER BY T2.WhenCreated desc )
Group by T1.CustomerID , T1.WhenCreated -- group by uses indexes (distinct does not use ix !)


-- also whith this query, the index on CustomerId would be very helpful !
Select A.CustomerID, A.WhenCreated
, B.TranscID
from #tmpCustomer A
Inner join tblTransactions B
on A.CustomerID = B.CustomerID
and A.WhenCreated = B.WhenCreated

quote:Originally posted by Adriaan

What indexes do you have on tblTransactions?

Clustered: TransacID
Non Clustered: WhenCreated CustomerID


In his heart, a man plans his course, but the LORD determines his steps

Add a new non-clustered index on (CustomerId).

Another option would be to change the non-clustered index to (CustomerId, WhenCreated) but perhaps the existing index is crucial for other queries.

Keep in mind a FK-constraint does _not_ create an index for you !
You've stated in your first request :
...
Columns of tblTransactions:
+++++++++++++++++++++++++++++++++++++++++++++++++
TranscID, CustomerID, TranscDetail, WhenCreated
+++++++++++++++++++++++++++++++++++++++++++++++++
*TranscID is PK,
*CustomerID is FK
*WhenCreated is noncluster index in DESC order...

This would mean there is no index on the FK-column.
The PK generates a unique index (clustering by default and when no clustering index exists) at creation time

alzdba, I didnt miss that part.



In his heart, a man plans his course, but the LORD determines his steps

Great. Just making sure you didn't get things mixed up.

- Is the table or its indexes rebuild at regular time(s) ?
- how well organised are the idexes ? Can you rebuild or defrag them ?

quote:Originally posted by Adriaan

Add a new non-clustered index on (CustomerId).

Another option would be to change the non-clustered index to (CustomerId, WhenCreated) but perhaps the existing index is crucial for other queries.

I just tried Both, still not ideal.

Indices are all fine.

Anyway, I have come a long this idea,
Well, this makes sense as in the real world scenarios, e.g.
GivenDateTime wont be too long ago, otherwise what is the point.

--Table Variable
DECALRE @MyCustTable TABLE
{
ID IDENTITY(1,1) NOT NULL,
CustomerID INT] NOT NULL
}

INSERT INTO MyCustTable (CustomerID)
SELECT CustomerID FROM tblCustomers WHERE CompanyID = 123

--Table Variable
DECLARE @MyTransTable TABLE
(
TransactionID [BIGINT] NOT NULL,
CustomerID [INT] NOT NULL,
WhenCreated [DateTime] NOT NULL
)

INSERT INTO @MyTransTable
(TransactionID, CustomerID, WhenCreated)
SELECT TransactionID, CustomerID, WhenCreated
FROM tblTransactions T RIGHT OUTER JOIN @MyCustTable C
ON T.CustomerID = C.CustomerID AND T.WhenCreated >= @GivenDateTime

INSERT INTO @MyCustTable(CustomerID) VALUES(-1)
Set @CurrRow = 1
SELECT @CurrCustomerID = CustomerID FROM @MyCustTable
WHERE ID= @CurrRow

While(@CurrCustomerID <> -1)
BEGIN
SELECT TOP(@NUM) * FROM @MyTransTable WHERE CustomerID = @CurrCustomerID
ORDER BY WhenCreated DESC
SET @CurrRow = @CurrRow + 1
SELECT @CurrCustomerID = CustomerID FROM @MyCustTable WHERE ID= @CurrRow
END



In his heart, a man plans his course, but the LORD determines his steps