Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 138
  • Last Modified:

Tune SQL – WHERE by 2 fileds in one Table – 2 indexes or 1 index with 2 fields?

Dear experts,

I have the task to tune one SQL 2008R2 DB. I have follow t-SQL

Select Table1.F1,Table1.F2, Table1.F3, Table1.F4,OtherTable.F10,OtherTable.F11 .... ,OtherTable.F12 FROM ….  JOIN ...  WHERE Table1.F1 <getdate() AND Table1.F2=2 and …

My question is focus on Table1 where I search by 2 criteria. I wonder I should create 2 separated indexes on every filed, or one index with 2 fields. I know the best way it to measure this via Execution plan, but I think this is a common situation with common recommended solution. I’m focused on speed; My Table1 will have less than 10 000 records, will be relative rarely write, but will be read from this SP all the time.

And I have the same question, but for other table which will have millions of records and will be heavy read and write as well. Then what is the standard solution?
0
dvplayltd
Asked:
dvplayltd
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
1 index with the 2 fields would be the general rule, and the field with the best cardinality first.
means if you have the 2 fields like gender + firstname (just an example), gender has 2-3 distinct values, while firstname has lots of different values, so you would create the filter on firstname + gender and not gender + firstname

note that such a index (firstname + gender) will also be able to help queries that will only search by firstname
note that such a index (firstname + gender) will not be able to help queries that will only search by gender
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in your concrete scenario, there come several possible solution paths:
* clustered index on F2 + F1
* partition on F2 + index on F1
* partition on F1 + index on F2
0
 
dvplayltdAuthor Commented:
10x, i got what you mean. I'll put first field of the index Date /may have many different values/, then on second position Type which have 4 possible values only.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now