StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>First and foremost I would create an index to cover your search parameters, at the very least this should cover the column that contains your date. If that's not enough, you might want to look into Maheswaran's suggestion and use partitioning and filegroups, which works especially well with indexes as they can cover each partition separately.</p> <p>But all in all it's really hard to say as your question is way too broad. How many columns and which types of data are fetched from the table, what's the total amount of columns in the table? What are the filters in your WHERE clause (your index will use these filters). How much data in size would your 3-month batch contain (could be viable to create filegroups per each 3 month period making it easier to archive and use bulk operations against said data). Etc.</p> <p>There's too much guessworks to be done right now.</p> <p>Edit: Since the numbers changed to far less than what you originally had, a simple index could be quite sufficient for now. Try this:</p> <pre><code>CREATE NONCLUSTERED INDEX CHLNDATA_QUARTER_IDX ON ChnlData (ChnlId, ChnlDataLogTime) </code></pre> <p>Then if you want the data for the past three months for instance, you can get it like so:</p> <pre><code>SELECT * FROM [ChnlData] WHERE ChnlId in (519, 520) AND YEAR(ChnlDataLogTime) IN (YEAR(DATEADD(MONTH, -3, GETDATE())), YEAR(GETDATE())) AND MONTH(ChnlDataLogTime) BETWEEN MONTH(DATEADD(MONTH, -3, GETDATE())) AND MONTH(GETDATE()) </code></pre> <p>Didn't check the syntax but that should be right or close enough.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload