SQL Server Performance

Select Big Table Again

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

  1. codemeit New Member

    Hello. Thanks for people reading this

    This is a 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 same Number of transactions for all the customers where WhenCreated is after a given "DateTime". Transactions are ordered by DateTime Asc for EACH of the customer. (Performance is important)



    Can some body give me some hints?

    Thanks in advance!


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


    select *
    from bigtable
    where WhenCreated >= @givendatetime
    order by CustomerID, TranscID, WhenCreated



    KH
  3. codemeit New Member

    Khtanm Thanks for you reply.
    But does it ensure the same number of transactions of each customer
    will be returned?



    quote:Originally posted by khtan



    select *
    from bigtable
    where WhenCreated >= @givendatetime
    order by CustomerID, TranscID, WhenCreated



    KH


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

    To get the number of transactions per customer, you need to group on this clolumn.
    consider this:

    select CustomerID,count(*) from bigtable
    where WhenCreated >= @givendatetime
    GROUP by CustomerID
    order by CustomerID

Share This Page