Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003

    Unanswered: Seeking for the fast query

    Hello, everyone:

    There is a big table with several million records. I am developing a query that retrieve the first rowset that meets WHERE condition. Any suggestions for the fast query? Thanks a lot.


  2. #2
    Join Date
    Jul 2002
    Village, MD
    Good question! What about some details? See BOL for using indexes.

  3. #3
    Join Date
    Jul 2003
    Definitely need indexes. That's what will make the query quick. Make indexes based upon the values you search against with your WHERE clause, and don't include the column name in any functions if you can help it. Secondly, only return the columns you definitely need. If you need one column out of 10, don't use "*" as your result set, specify the name of that single column. Then use the TOP clause to get the TOP X number of records from the returned record set. TOP will speed up the transfer from SQL Server to App, but not necessarily in the initial generation of the result set, it depends.

    Without an index, this is really unoptimized...
    SELECT * FROM myTable WHERE DATEDIFF(d, mydate, GETDATE()) > 7

    With a proper index, this is optimal...
    SELECT TOP 1 RowID myTable WHERE mydate < DATEADD(d, -7, GETDATE)
    That which does not kill me postpones the inevitable.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts