Tech Off Thread

8 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Why does this JOIN take so long?

Back to Forum: Tech Off
  • User profile image
    qwert231

    I'm doing some development of a view using existing views. In my test query, I'm creating a temp table and I want to join it to a second view. So, I'm doing this:

    DECLARE @TroutStamps TABLE (
     CustomerID int,
     TypeID int,
     IssuedDate datetime
    )

    INSERT INTO @TroutStamps (CustomerID, TypeID, IssuedDate)
    SELECT CustomerID, TypeID, IssuedDate
    FROM ALSReplicator.vw_ALS_License_ORA
    WHERE (TypeID = 150) AND Void = 0
    print 'Filled @TroutStamps at ' + CAST(getdate() AS varchar(50))

    SELECT * FROM @TroutStamps

    SELECT
     li.CustomerID,
     lt.TypeID,
     'L' CardType,
     li.IssuedDate,
     lt.IssuedDate OriginalIssuedDate
    FROM ALSReplicator.vw_ALS_License_ORA li
    INNER JOIN vw_ALS_LifetimeTransaction_ORA lt on lt.CustomerID = li.CustomerID -- Oracle
    LEFT JOIN @TroutStamps sTrout ON li.CustomerID = sTrout.CustomerID AND lt.IssuedDate = sTrout.IssuedDate
    WHERE li.TypeID = 180 and li.void = 0

    The table variable gets just under 600k rows and takes about 30 seconds.

    The full query, with the join takes over 5 minutes.

    If I do the full query, but drop the join, it takes  30 seconds.

    So, I feel I should be able to do a more efficient join. Any ideas? What am I doing wrong?

  • User profile image
    foreachdev

    Put an index on your temp table. While your actually in thinking mode think about whether you actually need a temp table.

  • User profile image
    qwert231

    foreachdev said:
    Put an index on your temp table. While your actually in thinking mode think about whether you actually need a temp table.
    I wanted to do an index or primary key, but really, the key is a combination of all 3 columns.

    The requirements for this call for me to check for 3 specific TypeIDs, and if they exist for the customer, return a boolean.

    So, my result set will have 3 more columns than what is shown:
    HasType150, HasType151, HasType152

    I tried doing a function to return those columns, but that took hours.

  • User profile image
    lensman

    qwert231 said:
    foreachdev said:
    *snip*
    I wanted to do an index or primary key, but really, the key is a combination of all 3 columns.

    The requirements for this call for me to check for 3 specific TypeIDs, and if they exist for the customer, return a boolean.

    So, my result set will have 3 more columns than what is shown:
    HasType150, HasType151, HasType152

    I tried doing a function to return those columns, but that took hours.
    So instead of making a primary key, create a unique index based upon all three columns.  A primary key is not a requirement but I will admit normally a good idea.

  • User profile image
    TommyCarlier

    qwert231 said:
    foreachdev said:
    *snip*
    I wanted to do an index or primary key, but really, the key is a combination of all 3 columns.

    The requirements for this call for me to check for 3 specific TypeIDs, and if they exist for the customer, return a boolean.

    So, my result set will have 3 more columns than what is shown:
    HasType150, HasType151, HasType152

    I tried doing a function to return those columns, but that took hours.
    A primary key is not necessarily a single column. You can create a primary key composed of the 3 columns, if you want to.

  • User profile image
    foreachdev

    TommyCarlier said:
    qwert231 said:
    *snip*
    A primary key is not necessarily a single column. You can create a primary key composed of the 3 columns, if you want to.
    You can also have as many different indexes as you need. Only one will be clustered because that means they are sorted by the column(s) on insert. (By Default in MSSS the primary key is, but doesn't have to be and usually is a waste) DateTime fields, quantities, and other columns that would be used in a where clause and result in multi row returns are good clustered key canidates.

  • User profile image
    Matthew van Eerde

    foreachdev said:
    TommyCarlier said:
    *snip*
    You can also have as many different indexes as you need. Only one will be clustered because that means they are sorted by the column(s) on insert. (By Default in MSSS the primary key is, but doesn't have to be and usually is a waste) DateTime fields, quantities, and other columns that would be used in a where clause and result in multi row returns are good clustered key canidates.
    Bear in mind that having lots of indexes will slow down your INSERT and DELETE queries, and such UPDATE queries as touch the indexed columns.

  • User profile image
    wacko

    Is this MSSQL ? or someother SQL Engine ? I am just curious as to which engine you are using. 

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.