Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2010
    Posts
    24

    Unanswered: Create index on ORDER BY 2 table columns

    SELECT * FROM SCH2.dbo.BISALES T01 INNER JOIN SCH2.dbo.BISALES2 T02
    ON T01.INVNUM=T02.INVNUM ORDER BY T01.INVNUM, T02.INVLINE, T01.SALESQTY

    How to create index to make this query fast?

    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Do you care about this query and this query only? Because we can create perfect indexes for this but they will probably slow down other insert, update and delete operations.
    Indexes should be considered with all queries as a whole, not just the one you are focussing on.

    Also, what version of SQL Server?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also, SELECT * is bad practice. Please can you list all the required columns?
    There is no where clause - as such only so much optimisation is possible. Do you really require all rows?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2010
    Posts
    24
    Thanks.
    Yes I need all columns and there is no WHERE.
    I just wonder if there are general guidelines in handle this kind of query.
    Thanks.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    needing to select all columns and all rows from this table an index would more than likely slow you down rather than speed things up. As Pootle mentioned, even though you want all columns, if this is going into a piece of application code or procedure, you should still name each column rather than using the *.
    Dave

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If you need all columns and there is no where clause then all you can do is have an index for each table that contains every single column and orders your data to match the join and order by. That would either be a clustered index or a covering index that duplicates all your tables' rows.
    These are optimal for that query and that query only. Remove UNIQUE from whichever it does not apply to.
    Code:
    CREATE UNIQUE CLUSTERED INDEX ix_BISALES_INVNUM_u_c
    ON dbo.BISALES (INVNUM ASC)
    WITH    (
            FILLFACTOR = 100
            )
            
    CREATE UNIQUE CLUSTERED INDEX ix_BISALES2_INVNUM_INVLINE_u_c
    ON dbo.BISALES2 (INVNUM ASC, INVLINE ASC)
    WITH    (
            FILLFACTOR = 100
            )
    I cannot emphasise enough though that it is very unlikely you should create these two indexes.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This should speed up your query, however the difference I think is likely to only be the substitution of a HASH join with a MERGE join and\ or the removal of one sort operation.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2010
    Posts
    24
    Thanks.

    I did make index

    CREATE INDEX ix_BISALES2_INVNUM_INVLINE_u_c ON dbo.BISALES2 (INVNUM, INVLINE)

    which made this query supper fast:

    SELECT * FROM SCH2.dbo.BISALES T01 INNER JOIN SCH2.dbo.BISALES2 T02
    ON T01.INVNUM=T02.INVNUM ORDER BY T01.INVNUM, T02.INVLINE

    As soon as I add another T01.SALESQTY to 'order by' it runs forever:

    SELECT * FROM SCH2.dbo.BISALES T01 INNER JOIN SCH2.dbo.BISALES2 T02
    ON T01.INVNUM=T02.INVNUM ORDER BY T01.INVNUM, T02.INVLINE, T01.SALESQTY

    So I just wonder if there is anything to handle this ORDER:
    ORDER BY T01.INVNUM, T02.INVLINE, T01.SALESQTY

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not really.

    Can you post the plans?

    BTW - you have only created one index (I created two), and it is fundamentally different from the one I posted - you are aware of this?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2010
    Posts
    24
    No I am not aware of the difference. I am a SQL beginner (more so with MS SQL server)

    I did create indexes on the first table too:

    CREATE index1a ON dbo.BISALES (INVNUM )
    CREATE index1b ON dbo.BISALES (INVNUM, INVLINE)

    And I created indexes on the 2nd table:

    CREATE index2a ON dbo.BISALES2 (INVNUM )
    CREATE index2b ON dbo.BISALES2 (INVNUM, INVLINE)

    I literally typed them as above.

    They do make this query fast:

    SELECT * FROM SCH2.dbo.BISALES T01 INNER JOIN SCH2.dbo.BISALES2 T02
    ON T01.INVNUM=T02.INVNUM ORDER BY T01.INVNUM, T02.INVLINE

    But not this (as expected. And I am wonder if I can):

    SELECT * FROM SCH2.dbo.BISALES T01 INNER JOIN SCH2.dbo.BISALES2 T02
    ON T01.INVNUM=T02.INVNUM ORDER BY T01.INVNUM, T02.INVLINE, T01.SALESQTY

  11. #11
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    You show that you have both the columns, INVNUM, INVLINE in both tables. Should you be joining the two tables on both items. Also. do you really need to order by the INVNUM of one table and the INVLINE of the other? Or could you get away with ordering them from the same table?
    Dave

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Why bother asking for index suggestions if you then make your own up?

    I know your indexes won't make things faster - I would be surprised if they are being used at all.

    If you insist on using SELECT * and have no where clause then you have no choice - your index must be clustered or covering.
    Reasons to not use SELECT * (and when to use it)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Feb 2010
    Posts
    24
    Thanks a lot!
    This query is not from real world. I made it up to ask questions. Your responses and questions really helped.
    Bruce

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    CREATE UNIQUE CLUSTERED INDEX ix_BISALES_INVNUM_u_c
    ON dbo.BISALES (INVNUM ASC, SALESQTY)

    or

    CREATE UNIQUE CLUSTERED INDEX ix_BISALES_INVNUM_u_c
    ON dbo.BISALES (INVNUM ASC)
    include (SALESQTY)
    Last edited by rdjabarov; 07-22-10 at 16:00. Reason: formatting
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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