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.
Select tbl_master.*, tbl_transaction.*
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
type1 = 'Flat' And price < 2000 And year < 2000 and name like 'abc%'
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.
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.
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.
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?
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