join then filter or filter the join design question.

Discussion in 'microsoft.public.sqlserver.programming' started by loufuki, Nov 8, 2007.

  1. loufuki

    loufuki Guest

    Hi I have a pivot table that has about 3 million records.
    I need to do a cross join with another table that has about 10
    columns. so roughly, I will have 30 million records to filter.
    the situation I have is I have to use this table 2 times to extract
    data for two different Tables
    I.E. this table hold customer's Credit cards and bankaccount.
    Thus out of those 3 million records, 1/2 belongs to one group and 1/2
    belongs to another.

    My code is something like this:
    CASE ColumnName WHEN 'Credit1' THEN GetColumn1
    WHEN 'Credit2' THEN GetColumn2
    WHEN 'Credit3' THEN GetColumn3
    WHEN 'Credit4' THEN GetColumn4
    WHEN 'Credit5' THEN GetColumn5
    WHEN 'Credit6' THEN GetColumn6
    END AS 'Credit' ,

    My question is, should I filter the pivot table first and then do a
    cross join to reduce the return value to 1/2
    of cross join and then filter?
    Like I mentioned, this Pivot tables CONSISTS of multiple datagroup,
    THAT i HAVE TO extract for each.

    anyone had any expeerience in this?
    loufuki, Nov 8, 2007
    1. Advertisements

  2. loufuki

    ML Guest

    Have you compared the exectuion plans of both queries (even against a sample
    of data)? Have you considered using Analysis Services for this?

    ML, Nov 8, 2007
    1. Advertisements

  3. loufuki

    loufuki Guest

    I am using integration service for this.
    I haven't tested out both way to see the execution plans
    it does takes time for both approaches. I don't have time to implement
    bo approach and test for myself.Therefore, I post up here and ask if
    anyone had experience in this or at least done this before.
    loufuki, Nov 9, 2007
  4. loufuki

    ML Guest

    Generally speaking, joining data sets that have already been restricted in
    size before applying the final set of restrictions will probably yield better
    performance (i.e. joining restricted subqueries in the main query). However,
    it's difficult to say without seeing more of your code and/or data.

    So, for a more accurate answer, please, post DDL, sample data and expected
    results, as discussed in this article:

    ML, Nov 11, 2007
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.