Changing Indexes Based on Query
-
To simplify my question, I am trying to run a query against two tables, Table1.CustNo = Table2.CustNo.
Table1 has 1 index for the CustNo. No other keys are used. Table2 has 1 index with CustNo, State and Zip as Index_Keys.
Would it be recommended to create a second index on Table2 for CustNo only?
In my real situation, I'm joining 5 tables together, some having millions of rows. Some queries take 10+ minutes to run.
Thanks!!
-
Question 1:
What is you version of SQL Server ? Run this SQL and post the output as if you are using 7 vs 2000 vs 2005, the answer may be different.
select serverproperty('edition'), serverproperty('ProductVersion')
Question 2:
Do each of these tables have a unique clustered index ?
SQL = Scarcely Qualifies as a Language
-
Thanks for the post. Here's the output:
Standard Edition 8.00.818
the CUSTNO index on Table1 is Unique and clustered.
the CUSTNO, STATE, ZIP index on Table2 is Unique and clustered (although not a great example).
On some tables, the field that I am joining on may be the middle index_key (STATE, CUSTNO, ZIP).
Hope that helps. Thanks again!
-
No, as CustNo is the first column of a unique clustered index, this is OK.
Such as:
select
From StateZipDemographics
join Table2
on Table2.State = StateZipDemographics.State
and Table2.ZIP = StateZipDemographics.ZIP
WHERE ??
The where clause, frequency of execution and frequency of update/insert/delete of table2 will need to be considered.
Here are some examples:
1 Table2 is updated on a monthly basis and the join is performed multiple time per day, then a index should be created.
2. Table2 is updated every 5 seconds and the join is performed once per month, then the index should NOT be created.
3. Table2 is updated every 5 seconds between 8AM and 8PM and the join is performed multiple times for reports that start running at 3AM and finish at 5AM. The index could be created before 3AM and dropped after 5AM. Alternatively, a indexed view could be created that is joined once, used by the reports and then dropped.
As you can see, whether to create an index is a resource tradeoff.
SQL = Scarcely Qualifies as a Language
-
<
Would it be recommended to create a second index on Table2 for CustNo only?
>
Yes, definetely. MUST BE.
Otherwise Server will effectively find required line from PK table and than go to full table scan on FK table. It's obvious that there are more lines in FK table than in PK one. So index on CustNo is even more required on Table2.
Actually you can check all your delays on Execusion Plan. It will show you where you need to add index or at least statistics.
But keep in mind: those recommendation are based on this query only. You must to think before you follow any instruction. Even mine.
_____________
Code for TallyGenerator -
Hello Sergiy,
but there already is index on the second table. Wouldn't that be used?
"Table2 has 1 index with CustNo, State and Zip as Index_Keys."
"CUSTNO, STATE, ZIP index on Table2 is Unique and clustered"
Question was, whether an index that contains only the one column CustNo would improve performance... I agree with Carl's post above, could you please elaborate on why another, non-composite index would be needed?
-
Carl is correct. For your example with CUSTNO, where that column is the first column declared in the composite index on table2, a separate index on CUSTNO would not grant any advantages. There is already an index on CUSTNO.
It is your other cases that may benefit from either a separate index, or a rearrangement of the column ordering in those indexes.
Keep in mind that the server only has statistics on the first column of a composite index, this is why your example is already 'good enough', but your 'middle' situations could benefit from an extra index supporting those columns.
/Kenneth
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply