SQL Server Performance

How to make join performant?

Discussion in 'SQL Server 2008 Questions [Archive Only - Closed]' started by Drake, Nov 6, 2008.

  1. Drake New Member

    I have 3 tables Library, Book and Library_Book which is a many-to-many join table containing primary keys of Library and Book tables as the only 2 columns. Both these columns form a composite primary key for the join table Library_Book. LibraryId is of type GUID and BookId is of type int.
    The Library table has 1000 entries and Book table has 10 million entries. Library_Book table also has 10 million entries thus mapping each libraryId with 10,000 bookIds. When I run the following query, it takes about 16 secs, which seems to be too long. The result returns all columns for 10,000 rows (out of 10 million) in the Book table.
    I would like to have the response time of 2-3 secs. Please advise on how can I speed this up.
    (Using SQL Server 2008 x64 Enterprise on a Dell P470 desktop with Vista x64 and 3 GB RAM, Dual core Dual CPU Xeon)
    Query:
    SELECT Book.bookId, Book.Name, Book.author
    FROM Book INNER JOIN
    Library_Book ON Book.bookId = Library_Book.bookId
    WHERE (Library_Book.libraryId = 'C364811B-3723-F139-726D-067E504675DC')

    when I look at the Actual Execution plan, it says that 100% of time was spent in 'Clustered Index Seek' whose details are below.
    Number of Executions: 10000
    Object: [TEST].[dbo].[Book].[PK_Book]
    Seek Keys[1]: Prefix: [TEST].[dbo].[Book].bookId = Scalar Operator([TEST].[dbo].[Library_Book].[bookId])
  2. Adriaan New Member

    A GUID is a poor choice as a PK, especially when performance is an issue. An INT with identity would be a much better option.
  3. FrankKalis Moderator

    Is libraryID the first column is the index? If so, it probably makes sense to switch the position of the columns in the index.
  4. Drake New Member

    [quote user="FrankKalis"]Is libraryID the first column is the index? If so, it probably makes sense to switch the position of the columns in the index.
    [/quote]
    No, the bookId is the first column in the clustered index on primary key of Library_Book table. But will switching the index column order for this table help and how?
    To me it seems like clustered index seek on the Book table is the bottleneck, with the index being on the primary key bookId.
  5. Drake New Member

    However, most time (100%) is consumed in Clustered Index seek on Book table which has its clustered index on the primary key bookId which is a bigint.

Share This Page