none
How to search fast in large Database?

    Question

  • I want to search a record from a database.But this database contains millions of records.So,what should i have to do for search data faster from this database?
    Sunday, July 10, 2011 3:05 PM

Answers

  • Hi Kavin,

    Thanks for your question and other partners’ replies.

    In order to increase the speed of SQL SELECT query, you can analyze the following issues:

                                  RequestLive property value


                                  Available indexes for conditions from WHERE clause

                                  Rewriting a query with OR conditions as a UNION

                                  Available indexes for JOIN conditions

                                  Available indexes for ORDER BY clause

                                  Available indexes for GROUP BY clause

                                  Select from in-memory tables

                                  SELECT INTO vs INSERT SELECT

     

    The more information about Improving SQL Performance, please refer to: http://www.componentace.com/help/absdb_manual/increasesqlperformance.htm.

    Hope it helps you. If you have any other question, please feel free to let me know.

    Thanks,
    Sharp Wang


    Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
    Tuesday, July 12, 2011 1:23 AM
    Moderator

All replies

  • Hi,

    You should create index on table, try to design covering index. For more information please check this article: http://msdn.microsoft.com/en-us/library/ms190806.aspx

    I hope it helps.

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    Sunday, July 10, 2011 3:25 PM
  • Hi Kavin,

    1. Locate MDF and LDF file of the database in different drives as it reduces DISC I/O, hence performance.

    2. Try to create Filter based , non clustered and covering index.

     

     

    These are basic things which you should keep in mind while creating database and hence reduces searching time in database, please let me know the more detail of your database so that i will suggest more on the same.

     

     

    Amit

    Please mark as answer if helpful.

    Sunday, July 10, 2011 5:32 PM
  • Indexes can only help a Reporting Services query IF you're selecting fewer than (about) 20% of the pages in a data table. If you fetch a single row (unusual for a report) an Index will bring the row to the report processor quickly. If you're fetching all of the information about a particualar entity indexes can help but only to a point. I discuss the SQL Server Query Optimizer in my intermedial webinar series that begins July 12-14th and is held every-other month. See my blog for details.

    __________________________________________________________________
    William Vaughn
    Author, Mentor, Trainer, MVP
    Beta V Corporation
    William Vaughn's blog

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    “The Owl Wrangler” a fantasy fiction novel

    Please click the Mark as Answer button if a post solves your problem!

    Sunday, July 10, 2011 6:55 PM
  • Hi Kavin,

    Thanks for your question and other partners’ replies.

    In order to increase the speed of SQL SELECT query, you can analyze the following issues:

                                  RequestLive property value


                                  Available indexes for conditions from WHERE clause

                                  Rewriting a query with OR conditions as a UNION

                                  Available indexes for JOIN conditions

                                  Available indexes for ORDER BY clause

                                  Available indexes for GROUP BY clause

                                  Select from in-memory tables

                                  SELECT INTO vs INSERT SELECT

     

    The more information about Improving SQL Performance, please refer to: http://www.componentace.com/help/absdb_manual/increasesqlperformance.htm.

    Hope it helps you. If you have any other question, please feel free to let me know.

    Thanks,
    Sharp Wang


    Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
    Tuesday, July 12, 2011 1:23 AM
    Moderator