Results 1 to 11 of 11

Thread: Help with Index

  1. #1
    Join Date
    Mar 2007
    Posts
    97

    Unanswered: Help with Index

    Hi guys,

    I have kind of a standart query that loads some data into a temporary table. The temptable is created by another program dynamicly and later used for data aggregation. Some queries seem really slow to me. Can you please give me a hint, which index is usefull here?
    At the moment there is just one unique PK for tbl_master on (id) and a second unique PK for tbl_transactions on (year,quarter, id). No FK yet.

    The tables look like

    tbl_master: id decimal, type1id char(1), type2id char(1), type3id char(1), price decimal, size decimal, name varchar(100)

    tbl_type1: type1id char(1), type1 varchar(50)

    tbl_type2:type2id char(1), type2 varchar(50)

    tbl_transaction:year char(4), quarter char(1), id decimal, owner varchar(100)


    Typical Query is:

    Code:
    Select tbl_master.*, tbl_transaction.*
    Into #temptable
    From
    tbl_master 
    join tbl_transaction on tbl_master.id = tbl_transaction.id
    join tbl_type1 on tbl_master.type1id=tbl_type1.type1id
    join tbl_type2 on tbl_master.type2id=tbl_type2.type2id
    Where
    type1 = 'Flat' And price < 2000 And year < 2000 and name like 'abc%'
    Thank you!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    indexing is a bit of an iterative process. What table(s) do the type1, price, and name fields (in your Where clause) come from?

  3. #3
    Join Date
    Mar 2007
    Posts
    97
    Quote Originally Posted by MCrowley
    indexing is a bit of an iterative process. What table(s) do the type1, price, and name fields (in your Where clause) come from?
    They come from tbl_type1 and tbl_master. See table definitions above

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Assuming that tbl_master and tbl_transaction are your big tables, and that the type1 and type2 tables hold only on the order of 1,000's of rows or less, then try an index on tbl_transaction (id). i am betting that if you look at the estimated query plan in query analyzer, you will see a full table scan (or clustered index scan) on the transaction table.

  5. #5
    Join Date
    Mar 2007
    Posts
    97
    tbl_type1 and tbl_type2 only have about 20 rows. They are just Dimension tables.

    Actually, I'm trying to get into the Table Optimizer. Not sure what all those create statistics are good for.

    There already is a PK for tbl_transaction on (year,quarter,id). Do I really need an additional index on id?

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Yes. Think of a phone book. you can think of it as having an index on lastname, firstname. Now. Explain how you will use this index to retrieve all people named "john". The query optimizer will do the same thing. A table scan.

  7. #7
    Join Date
    Mar 2007
    Posts
    97
    I thought, when a column is a member of composite index, there is no need for a seperate index on this column. Seems not to be so.
    Another thing I detected is the "include" statement in combination with "create index". Query Optimizer suggested me to use an index (year,quarter id include owner) on tbl_transaction. Although there is this grouped PK (year,quarter,id).
    Not to mention the bunch of statistics. BTW, what are statistics for?? Whatever, I should look for some introduction to indexes. Do you know any? Free ones favored of course.
    Thanks
    silas

  8. #8
    Join Date
    May 2007
    Posts
    49
    How about using Database Engine Tuning Advisor ?
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  9. #9
    Join Date
    Mar 2007
    Posts
    97
    2 More Questions:

    Assuming there are about 6 dimension tables, some with 20 some with 1000 rows. Those tables are always joined with the master table on their id's. By id, I mean the FK. I read, that when joining two tables, it makes sense to use an index for the affected column. But in my case, does it make sense to create a composite index that includes the 6 FK's, or is better to create 6 single Indexes?

    Second Question:
    Assume there is scenario 1 with index A on (Col1, Col2, Col3)
    and scenario 2 with index A on Col1, B on Col2 and C on Col3.

    The query is:
    Select Col1,Col2,Col3,Col4,Col5,Col6 from t where Col1=1 and Col3=5

    which scenario is best for the query?

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11

  11. #11
    Join Date
    May 2007
    Posts
    49
    use Database Engine Tuning Advisor, you will get answers to all of your questions.
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.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
  •