PHIL Sawyer

<div>
Will this speed up my queries and will I have to remove the clustered index before implementing the above?<br />
Excuse my ignorance on these matters.<br />
<br />
Regards
</div>


<div>
Yes and no. &nbsp;<br />
<br />
Yes in that if a query can utilize an index for seeks it will greatly speed up queries. &nbsp; (Seek = good = the query goes straight to the value being searched. &nbsp;Scan = the query has to go through the entire index to find value(s) which will be slower in most circumstances)<br />
<br />
No in that a table can have only one clustered index, as that affects the physical ordering of the data on disk, but it can have many other non-clustered indexes. <br />
<br />
Entire books and video series have been created for proper indexing, so a full description is beyond the scope of a single EE question. &nbsp;I'm currently going through the Brent Ozar videos on <a href="https://www.brentozar.com/training/fundamentals-of-index-tuning-instant-replay/" rel="ugc">indexing </a>and <a href="https://www.brentozar.com/training/performance-tuning-class-live-4-days-recording/" rel="ugc">performance tuning </a> to prepare for taking the <a href="https://www.microsoft.com/en-ph/learning/exam-70-762.aspx" rel="ugc">70-762 exam</a>, and it's a lot of ground to cover.<br />
<br />
I've even asked questions here about the course material, so by all means feel free to ask new questions whenever you need help with something.
</div>


<div>
( a data modeling (i.e. non-indexing) comment ) <br />
<br />
Regarding Country-Region-Gender, what are the column data types for these columns? &nbsp;The smaller the data type = the less disk it takes to store the values = the faster the querying. <br />
<br />
For example...<br />
&nbsp; &nbsp;Country <br />
&nbsp; &nbsp; &nbsp; &nbsp;nchar(50) will take 50 x 2 = 100 bytes to store the value. <br />
&nbsp; &nbsp; &nbsp; &nbsp;nvarchar(50) will take however many characters in the value x 2 bytes to store the value. <br />
&nbsp; &nbsp; &nbsp; &nbsp;varchar(50) will take however many characters in the value x 1 bytes to store the value. <br />
&nbsp; &nbsp; &nbsp; &nbsp;smallint, a number where you have a lookup table that contains the text name = 2 bytes<br />
&nbsp; &nbsp; &nbsp; &nbsp;tinyint, a number from 0-255 which would work if you'll never have more than 255 countries = 1 byte. <br />
<br />
You get the idea. &nbsp;In general the smaller you can make the bytes in storage for your values, the faster queries will execute.
</div>


<div>
So, do you think it may help to keep the &nbsp;clustered date index add non clustered indexes for Country, region and Gender?
</div>


<div>
Just seen your most recent comment - makes sense
</div>


<div>
The other problem is that the table does not have ID's for Country, Region or Gender - just looked at the table properties and see the following:<br />
Country = nvarchar(255)<br />
Region = varchar(66)<br />
Gender = varchar(2)
</div>


<div>
Country should be 2 or 3 bytes, whichever code you prefer to use. &nbsp;Both are standardized. &nbsp;We use the 3-char code, we find it easier to follow without looking it up.<br />
Region should also be encoded to a short alpha/numeric code.<br />
I've never seen anyone use 2 bytes for Gender; char(1) should do it. &nbsp;Btw, for lens of 4 or less, use char(), not varchar().<br />
<br />
With that minor stuff out of the way, on to the key issue (yes, that was a pun too).<br />
<br />
You <i>absolutely</i> should refine the <i>clustered</i> index. &nbsp;For example, after date, do you most often filter on Country? &nbsp;If so, the clus index should be:<br />
<br />
( date, Country_code )<br />
<br />
Do you often filter on Region after that? &nbsp;If so, the clus key should be:<br />
<br />
( date, Country_code, Region_code )<br />
<br />
Gender is less selective so more iffy, but it you do fairly often search by all 4, then include it in the clus key as well:<br />
<br />
( date, Country_code, Region_code, Gender )
</div>


<div>
So, would my clustered index be like the following :<br />
CREATE CLUSTERED INDEX ind_date_ctry_region_gende<wbr />r ON [DataWarehouse].[dbo].[MYT<wbr />ABLE](DATE<wbr />,COUNTRY,R<wbr />EGION,GEND<wbr />ER) WITH FILLFACTOR=100<br />
<br />
Is the FILL FACTOR =100 still ok?<br />
<br />
Regards
</div>


<div>
100[%] is risky. &nbsp;If you're really comfortable the row sizes won't increase (such as varchar(40) that starts out empty but gets filled in by ~20 bytes later), then specify 99. &nbsp;That's only about 80 bytes per page, but allows you to add, say, a 1- to 2-byte column to the table without causing every page to split.
</div>


<div>
So, you can have multiple columns in clustered index?<br />
CREATE CLUSTERED INDEX ind_date_ctry_region_gende<wbr />r ON [DataWarehouse].[dbo].[MYT<wbr />ABLE](DATE<wbr />,COUNTRY,R<wbr />EGION,GEND<wbr />ER) WITH FILLFACTOR=99
</div>


<div>
Yes, absolutely.<br />
<br />
And SQL will seek on as many columns as possible, <i>although if you skip any column the seeking ends there</i>.<br />
<br />
For example, say you specify:<br />
date = ... &nbsp;AND<br />
country = ... AND<br />
region = ...<br />
then SQL would do a seek predicate on all three of those values.<br />
<br />
If you specify:<br />
date = ... &nbsp;AND<br />
country = ... AND<br />
gender = ...<br />
then SQL would only seek on the first two: because you skipped the 3rd one, the 4th can't be used in a seek.<br />
<br />
If the date is a range, then SQL can't seek past that either. &nbsp;If, say, you always specific a specific country(ies) and specified a date or a date range, then you'd need to cluster by:<br />
( country, date, ... )<br />
<br />
If you'll provide a sample table structure (CREATE TABLE ...) and data (INSERT ...), then we can review the plans to see the seek predicates that SQL uses.
</div>


<div>
I strongly suspect that a non-clustered index won't do you any good here, because of the number of columns from the main table you're likely using for reporting. &nbsp;The most effective index is the clus index and getting <b>the best clus index</b> on a table is the best way to tune a table.
</div>


<div>
OK - tomorrow am I will try...<br />
CREATE CLUSTERED INDEX ind_date_ctry_region_gende<wbr />r ON [DataWarehouse].[dbo].[MYT<wbr />ABLE](DATE<wbr />,COUNTRY,R<wbr />EGION,GEND<wbr />ER) WITH FILLFACTOR=99
</div>


<div>
Hi<br />
The clustered index with date, country etc has improved my query - many thanks
</div>


<div>
You selected an answer stating to use a NONCLUSTERED index. &nbsp;A NC index wouldn't help your queries at all; likewise for anyone else who tries to copy this q's solution later for the same problem.
</div>


<div>
<div class="content wysiwyg-content">
Hi<br />
I have one table that I run a query on - it currently has around 30 million rows and growing daily by around 300,000 rows. The table has one Clustered index on the date field.<br />
In my reporting tool which interrogates this table I have 4 filters - primarily in the following order.<br />
Date (hence Clustered index on date)<br />
Country<br />
Region<br />
Gender<br />
<br />
Could I improve performance by adding indexes for Country, Region and Gender - or is the Clutsered date index sufficient?<br />
<br />
Regards
</div>
</div>


Hi
I have one table that I run a query on - it currently has around 30 million rows and growing daily by around 300,000 rows. The table has one Clustered index on the date field.
In my reporting tool which interrogates this table I have 4 filters - primarily in the following order.
Date (hence Clustered index on date)
Country
Region
Gender

Could I improve performance by adding indexes for Country, Region and Gender - or is the Clutsered date index sufficient?

Regards

Adding Indexes

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.