SQL Server Performance

Help on Select for big table.

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by codemeit, Jan 11, 2007.

  1. codemeit New Member

    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!

  2. alzdba Member

    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)' />]
  3. codemeit New Member

    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.
  4. alzdba Member

    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
  5. codemeit New Member

    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
  6. alzdba Member

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

    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 />
  8. alzdba Member

  9. codemeit New Member

    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>
  10. alzdba Member

    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
  11. codemeit New Member

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



    In his heart, a man plans his course, but the LORD determines his steps
  12. codemeit New Member

    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>
  13. alzdba Member

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

    <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 />
  15. alzdba Member

    Thanks for the notification [:I]

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

  16. codemeit New Member

    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
  17. Adriaan New Member

    What indexes do you have on tblTransactions?
  18. alzdba Member

    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
  19. codemeit New Member

    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
  20. Adriaan New Member

    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.
  21. alzdba Member

    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
  22. codemeit New Member

    alzdba, I didnt miss that part.



    In his heart, a man plans his course, but the LORD determines his steps
  23. alzdba Member

    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 ?
  24. codemeit New Member

    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

Share This Page