locked
Index on Large data table RRS feed

  • Question

  • Hi There,

    I have one SQL Server database table with huge amount of data ( Billions of records ).   just wondering about performance issues and please advice where and when to use index on the columns if there is no unique column in the table.

    How to improve performance for this table.( i am using parameterized stored procedure to retreive this huge table data )

    Table design :

    PostedDate(date, not null)

    Account (bigint, not null)

    Amount ( decimal(12,2), not null)

    DebitCredit ( Char(1), not null)

    PostedFlag (char(1), null)

    ExcptnFlag (char(1),null)

    StmtDate(date, null)

    SeqNum(bigint, not null)

    Check(bigint, not null)

    ExpItem( char(1), null)

     

    Thank You.

    Learning bee

     

     





    Monday, January 16, 2012 7:21 AM

Answers

All replies

  • What query/s do you run against that table? What is a WHERE condition? How much does do you return?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, January 16, 2012 7:24 AM
  • Hi Uri,

    Thanks for the prompt reply

    i am planning to use stored procedure against this table to report this data on SSRS ( if this info helps )

    The general query against this table is :

    select PostedDate, Account, Amount, DebitCredit,  PostedFlag, ExcptnFlag, StmtDate, SeqNum, Checknumber, ExpItem

    from Bank

    where (Account=@Account or @Account is null or @Account = '') and (Amount=@Amount or @Amount is null or @Amount = '') and (Checknumber=@Checknumber or @Checknumber is null or @Checknumber = '') and (Posteddate between @fromDate and @todate)

    The returing data depends on USER  Search criteria ( probable 1 Million ) which i am using this data as report in SSRS report.

     

    Thank You




    Monday, January 16, 2012 7:35 AM
  • I would suggest you reading Erland's article for the subject

    http://www.sommarskog.se/dyn-search-2005.html

    http://www.sommarskog.se/dyn-search-2008.html


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Kent Waldrop Tuesday, January 17, 2012 12:57 PM
    • Marked as answer by Knowing_Tech Tuesday, January 17, 2012 5:03 PM
    Monday, January 16, 2012 8:26 AM
  • OK thank You for sharing these links Uri
    Tuesday, January 17, 2012 12:15 AM