Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    May 2008
    Posts
    2

    Unanswered: Large database, slow query speed. Help!

    Hi guys,

    I am asking this question on behalf of a friend. I have little knowledge of SQL 2005 but my friend is quite knowledgeable, although this is the first time he is dealing with large database for a client. So here's the story.

    His client has a database containing 1.5 million books. Now he is setting up a website which will enable users to search books. Searching by ISBN is no problem as it only takes 1 seconds. The problem is, searching by Title takes more than 20seconds, which is unacceptable. My friend has only done smaller database and he just recently thought of implementing indexing and now looking for other ideas.

    Each row contains book details such as Title, Author1, Author2, Author3, Publisher, Publication Date, ISBN, etc.

    Can anyone who are more experienced in doing large database share with me some design ideas? His client is aiming for 8seconds or less.

    Thanks in advance!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by klee_sg
    ...my friend is quite knowledgeable ...and he just recently thought of implementing indexing
    that's a wonderful idea!!!

    now he's going to be super knowledgeable

    here's a design suggestion -- remove the 3 Author columns and implement a true many-to-many relationship

    every basic sql tutorial covers this
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    As it's your fiirst post methinks r937 means

    ISBN_Authors(ISBN,Author_ID)
    Clustered Composite Primary Key

    ISBN_Books(ISBN,Title,Publisher_ID,Publication_Date)
    Clustered Index on Title

    Publishers(Publisher_ID,Name,.......)

    Generally keep your searchable table overall record length narrow as possible

    Should Fly - such are the benefits of Normalization
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Make sure Title is indexed, of course. But the problem is that if they are doing fuzzy searches, (find Book Titles that are sort of like this string, or contain this string), then indexes are not of any use.
    He could also implement SQL Server's full-text search, though.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by blindman
    He could also implement SQL Server's full-text search, though.
    Since his friend is quite knowledgeable, this should be a piece of cake.

  6. #6
    Join Date
    May 2008
    Posts
    2
    He is using Full Text Index now. Any suggestion?

    Select *
    from
    (
    select (top 500 row_number() OVER (ORDER BY(rank desc) as resnum, rank, description, table2.col, table3.col, table4.col
    from table1 inner join FREETEXTTABLE(table1, description, 'application') AS ft ON table1.id1 = ft.[KEY])
    LEFT JOIN table2 ON table1.id2 = table2.id1
    LEFT JOIN table3 ON table1.id3 = table3.id1
    LEFT JOIN table4 ON table1.id4 = table4.id1
    ) AS Results
    WHERE resnum BETWEEN 21 AND 30

    In the above query, table2, table3 table4 contains required supplementary data.
    Table 1 is where we do full text search for description.
    We retrieve only 10 records at a time to incorporate pagination and also in order to limit number of records that are handled on hand.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that sql is farked

    which parentheses closes the OVER clause?

    if you are paginating by 10s, what is the TOP 500 for?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    I lost interest at the SELECT *
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The select * is on a derived table of specified columns. I prefer explicitly listing these too but it is one of two circumstances I think I can accept a select *.

  10. #10
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Quote Originally Posted by pootle flump
    The select * is on a derived table of specified columns. I prefer explicitly listing these too but it is one of two circumstances I think I can accept a select *.
    And the other being OPENQUERY ?
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    EXISTS(SELECT *...
    Although according to Connor Cummingham there should be a teeny, weeny overhead for expanding the * to a list of columns. If you could even measure it I'd be surprised.

  12. #12
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Dunno why but I use EXISTS(SELECT 1

    Guess OPENQUERY's another one then
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I suppose what I mean is I don't mind SELECT * FROM {some inline defined column list} - so that might also be CTEs, OPENQUERY.... can't think of owt else.

    I use EXISTS(SELECT NULL
    cos it can't get any smaller than that - you're using a whole extra bit

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    actually it can, because if you do not properly type the value of NULL, it implicitly turns into an integer which is 4 bytes. SELECT ''... will result in VARCHAR(1), which is smaller
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Good Lord. Are we having a contest now to see whose is smaller?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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