Best option to index an application log table for quick insertion and retrieval in date order? ...
How did the audience guess the pentatonic scale in Bobby McFerrin's presentation?
Do working physicists consider Newtonian mechanics to be "falsified"?
How many people can fit inside Mordenkainen's Magnificent Mansion?
He got a vote 80% that of Emmanuel Macron’s
Is it ethical to upload a automatically generated paper to a non peer-reviewed site as part of a larger research?
How do you keep chess fun when your opponent constantly beats you?
Finding the path in a graph from A to B then back to A with a minimum of shared edges
does high air pressure throw off wheel balance?
system() function string length limit
Can a 1st-level character have an ability score above 18?
Are my PIs rude or am I just being too sensitive?
Make it rain characters
When did F become S in typeography, and why?
Why is superheterodyning better than direct conversion?
Match Roman Numerals
Did God make two great lights or did He make the great light two?
Relations between two reciprocal partial derivatives?
Was credit for the black hole image misattributed?
Is above average number of years spent on PhD considered a red flag in future academia or industry positions?
How should I replace vector<uint8_t>::const_iterator in an API?
Semisimplicity of the category of coherent sheaves?
Wolves and sheep
High Q peak in frequency response means what in time domain?
Is it ok to offer lower paid work as a trial period before negotiating for a full-time job?
Best option to index an application log table for quick insertion and retrieval in date order?
The 2019 Stack Overflow Developer Survey Results Are In
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)Best index strategies for read-only table?clustered and covering index ignored on delete statement. Table scan occursPrimary Key choice on table with unique identifierHow best to index large table with many index keysShould I mark a composite index as unique if it contains the primary key?Should I remove this clustered index?Order of composite key for best lookup performanceDoes an index where the last column is unique and already indexed make sense?Unique, Non-Clustered Index and Unique Clustered Index on Same ColumnClustered Index Maintenance vs. Nonclustered Index Maintenance
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
Suppose I have an application trace log table with three columns: (id uniqueidentifier, message nvarchar(max), and started datetime2).
The primary key (clustered) is on [id]. I need to be able to insert records as quickly as possible, but I'm not sure if that's the best primary key. It makes sense because I'm using EntityFramework and that's the default, but the uniquidentifier type has pretty random values for each insertion, so it would insert rows all over the place when clusted on id. I think that's good, because it lowers page contention (vs inserting them all on the last data page all the time), but I'm not sure about that. At the same time, I need a way of retrieving the rows quickly in order by [started].
My question is, would it be better to:
1. Change the primary key to be composite on (started, id) so it's the only index and the data pages are in order by date, or should I
2. Leave the primary key on [id] and add a unique non-clustered index on (started, id).
My reasoning for including both (started,id) in the 2nd option is so it can be a unique index that would include the clustering key (which is always implicitly included anyway). I don't want to include any other columns in the index because it would duplicate a lot of [message] data unnecessary, when all I'm really interested in is an index that speeds retrieval of rows in order. Or, is there a better option than the one's I mentioned?
sql-server index sql-server-2017
edited Oct 17 '17 at 14:54
LowlyDBA
7,27952746
asked Aug 31 '17 at 19:34
TriynkoTriynko
228210
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
Suppose I have an application trace log table with three columns: (id uniqueidentifier, message nvarchar(max), and started datetime2).
The primary key (clustered) is on [id]. I need to be able to insert records as quickly as possible, but I'm not sure if that's the best primary key. It makes sense because I'm using EntityFramework and that's the default, but the uniquidentifier type has pretty random values for each insertion, so it would insert rows all over the place when clusted on id. I think that's good, because it lowers page contention (vs inserting them all on the last data page all the time), but I'm not sure about that. At the same time, I need a way of retrieving the rows quickly in order by [started].
My question is, would it be better to:
1. Change the primary key to be composite on (started, id) so it's the only index and the data pages are in order by date, or should I
2. Leave the primary key on [id] and add a unique non-clustered index on (started, id).
My reasoning for including both (started,id) in the 2nd option is so it can be a unique index that would include the clustering key (which is always implicitly included anyway). I don't want to include any other columns in the index because it would duplicate a lot of [message] data unnecessary, when all I'm really interested in is an index that speeds retrieval of rows in order. Or, is there a better option than the one's I mentioned?
sql-server index sql-server-2017
edited Oct 17 '17 at 14:54
LowlyDBA
7,27952746
asked Aug 31 '17 at 19:34
TriynkoTriynko
228210
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
I would Remove the uniqueid key and use the date as a clustered index.
– eckes
Sep 1 '17 at 0:17
add a comment |
Suppose I have an application trace log table with three columns: (id uniqueidentifier, message nvarchar(max), and started datetime2).
The primary key (clustered) is on [id]. I need to be able to insert records as quickly as possible, but I'm not sure if that's the best primary key. It makes sense because I'm using EntityFramework and that's the default, but the uniquidentifier type has pretty random values for each insertion, so it would insert rows all over the place when clusted on id. I think that's good, because it lowers page contention (vs inserting them all on the last data page all the time), but I'm not sure about that. At the same time, I need a way of retrieving the rows quickly in order by [started].
My question is, would it be better to:
1. Change the primary key to be composite on (started, id) so it's the only index and the data pages are in order by date, or should I
2. Leave the primary key on [id] and add a unique non-clustered index on (started, id).
My reasoning for including both (started,id) in the 2nd option is so it can be a unique index that would include the clustering key (which is always implicitly included anyway). I don't want to include any other columns in the index because it would duplicate a lot of [message] data unnecessary, when all I'm really interested in is an index that speeds retrieval of rows in order. Or, is there a better option than the one's I mentioned?
sql-server index sql-server-2017
edited Oct 17 '17 at 14:54
LowlyDBA
7,27952746
asked Aug 31 '17 at 19:34
TriynkoTriynko
228210
Suppose I have an application trace log table with three columns: (id uniqueidentifier, message nvarchar(max), and started datetime2).
The primary key (clustered) is on [id]. I need to be able to insert records as quickly as possible, but I'm not sure if that's the best primary key. It makes sense because I'm using EntityFramework and that's the default, but the uniquidentifier type has pretty random values for each insertion, so it would insert rows all over the place when clusted on id. I think that's good, because it lowers page contention (vs inserting them all on the last data page all the time), but I'm not sure about that. At the same time, I need a way of retrieving the rows quickly in order by [started].
My question is, would it be better to:
1. Change the primary key to be composite on (started, id) so it's the only index and the data pages are in order by date, or should I
2. Leave the primary key on [id] and add a unique non-clustered index on (started, id).
My reasoning for including both (started,id) in the 2nd option is so it can be a unique index that would include the clustering key (which is always implicitly included anyway). I don't want to include any other columns in the index because it would duplicate a lot of [message] data unnecessary, when all I'm really interested in is an index that speeds retrieval of rows in order. Or, is there a better option than the one's I mentioned?
sql-server index sql-server-2017
sql-server index sql-server-2017
edited Oct 17 '17 at 14:54
LowlyDBA
7,27952746
asked Aug 31 '17 at 19:34
TriynkoTriynko
228210
edited Oct 17 '17 at 14:54
LowlyDBA
7,27952746
asked Aug 31 '17 at 19:34
TriynkoTriynko
228210
edited Oct 17 '17 at 14:54
LowlyDBA
7,27952746
edited Oct 17 '17 at 14:54
LowlyDBA
7,27952746
edited Oct 17 '17 at 14:54
LowlyDBA
7,27952746
7,27952746
asked Aug 31 '17 at 19:34
TriynkoTriynko
228210
asked Aug 31 '17 at 19:34
TriynkoTriynko
228210
asked Aug 31 '17 at 19:34
TriynkoTriynko
228210
228210
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
I would Remove the uniqueid key and use the date as a clustered index.
– eckes
Sep 1 '17 at 0:17
add a comment |
I would Remove the uniqueid key and use the date as a clustered index.
– eckes
Sep 1 '17 at 0:17
I would Remove the uniqueid key and use the date as a clustered index.
– eckes
Sep 1 '17 at 0:17
I would Remove the uniqueid key and use the date as a clustered index.
– eckes
Sep 1 '17 at 0:17
add a comment |
1 Answer
1
active
oldest
votes
I would:
Change the date to a clustered (non primary) index.
Change the id to a non clustered primary key.
Changing the clustered index to the date (assuming those happen mostly in order which I assume it would for a log) will speed up the insertion process. Right now the id keys are essentially random which means that you may have a new row that gets inserted at the beginning of the values and that means that every value must be moved since it's clustered. This slows down inserts. Since you usually query log tables by date and not by id keys changing to a clustered index on the date will also speed up your retrievals so that's a win-win.
Because your date is not guaranteed to be unique you still need the id of course. You can leave it as the Primary Key if you prefer since it's unique but just modify it to be unclustered (they are created clustered by default if no other clustered index exists but that's not required). By leaving it as the primary key (unclustered) you still have a primary point of reference if you did need a foreign key.
answered Aug 31 '17 at 22:41
indiriindiri
2,026211
Unique-id Cluster-index inserts won't move all the data but it will fragment the index (aka table storage) by inserting new pages at random places.
– eckes
Sep 1 '17 at 0:18
A unique NON cluster index won't move the data. A clustered index will move it. By definition a clustered index is physically ordered on disk so it would have to be moved.
– indiri
Sep 1 '17 at 4:15
Yes but inserts don't move new data, at least not all of it, it enters new pages.
– eckes
Sep 1 '17 at 9:45
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f184840%2fbest-option-to-index-an-application-log-table-for-quick-insertion-and-retrieval%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I would:
Change the date to a clustered (non primary) index.
Change the id to a non clustered primary key.
Changing the clustered index to the date (assuming those happen mostly in order which I assume it would for a log) will speed up the insertion process. Right now the id keys are essentially random which means that you may have a new row that gets inserted at the beginning of the values and that means that every value must be moved since it's clustered. This slows down inserts. Since you usually query log tables by date and not by id keys changing to a clustered index on the date will also speed up your retrievals so that's a win-win.
Because your date is not guaranteed to be unique you still need the id of course. You can leave it as the Primary Key if you prefer since it's unique but just modify it to be unclustered (they are created clustered by default if no other clustered index exists but that's not required). By leaving it as the primary key (unclustered) you still have a primary point of reference if you did need a foreign key.
answered Aug 31 '17 at 22:41
indiriindiri
2,026211
Unique-id Cluster-index inserts won't move all the data but it will fragment the index (aka table storage) by inserting new pages at random places.
– eckes
Sep 1 '17 at 0:18
A unique NON cluster index won't move the data. A clustered index will move it. By definition a clustered index is physically ordered on disk so it would have to be moved.
– indiri
Sep 1 '17 at 4:15
Yes but inserts don't move new data, at least not all of it, it enters new pages.
– eckes
Sep 1 '17 at 9:45
add a comment |
I would:
Change the date to a clustered (non primary) index.
Change the id to a non clustered primary key.
Changing the clustered index to the date (assuming those happen mostly in order which I assume it would for a log) will speed up the insertion process. Right now the id keys are essentially random which means that you may have a new row that gets inserted at the beginning of the values and that means that every value must be moved since it's clustered. This slows down inserts. Since you usually query log tables by date and not by id keys changing to a clustered index on the date will also speed up your retrievals so that's a win-win.
Because your date is not guaranteed to be unique you still need the id of course. You can leave it as the Primary Key if you prefer since it's unique but just modify it to be unclustered (they are created clustered by default if no other clustered index exists but that's not required). By leaving it as the primary key (unclustered) you still have a primary point of reference if you did need a foreign key.
answered Aug 31 '17 at 22:41
indiriindiri
2,026211
Unique-id Cluster-index inserts won't move all the data but it will fragment the index (aka table storage) by inserting new pages at random places.
– eckes
Sep 1 '17 at 0:18
A unique NON cluster index won't move the data. A clustered index will move it. By definition a clustered index is physically ordered on disk so it would have to be moved.
– indiri
Sep 1 '17 at 4:15
Yes but inserts don't move new data, at least not all of it, it enters new pages.
– eckes
Sep 1 '17 at 9:45
add a comment |
I would:
Change the date to a clustered (non primary) index.
Change the id to a non clustered primary key.
Changing the clustered index to the date (assuming those happen mostly in order which I assume it would for a log) will speed up the insertion process. Right now the id keys are essentially random which means that you may have a new row that gets inserted at the beginning of the values and that means that every value must be moved since it's clustered. This slows down inserts. Since you usually query log tables by date and not by id keys changing to a clustered index on the date will also speed up your retrievals so that's a win-win.
Because your date is not guaranteed to be unique you still need the id of course. You can leave it as the Primary Key if you prefer since it's unique but just modify it to be unclustered (they are created clustered by default if no other clustered index exists but that's not required). By leaving it as the primary key (unclustered) you still have a primary point of reference if you did need a foreign key.
answered Aug 31 '17 at 22:41
indiriindiri
2,026211
I would:
Change the date to a clustered (non primary) index.
Change the id to a non clustered primary key.
Changing the clustered index to the date (assuming those happen mostly in order which I assume it would for a log) will speed up the insertion process. Right now the id keys are essentially random which means that you may have a new row that gets inserted at the beginning of the values and that means that every value must be moved since it's clustered. This slows down inserts. Since you usually query log tables by date and not by id keys changing to a clustered index on the date will also speed up your retrievals so that's a win-win.
Because your date is not guaranteed to be unique you still need the id of course. You can leave it as the Primary Key if you prefer since it's unique but just modify it to be unclustered (they are created clustered by default if no other clustered index exists but that's not required). By leaving it as the primary key (unclustered) you still have a primary point of reference if you did need a foreign key.
answered Aug 31 '17 at 22:41
indiriindiri
2,026211
answered Aug 31 '17 at 22:41
indiriindiri
2,026211
answered Aug 31 '17 at 22:41
indiriindiri
2,026211
answered Aug 31 '17 at 22:41
indiriindiri
2,026211
2,026211
Unique-id Cluster-index inserts won't move all the data but it will fragment the index (aka table storage) by inserting new pages at random places.
– eckes
Sep 1 '17 at 0:18
A unique NON cluster index won't move the data. A clustered index will move it. By definition a clustered index is physically ordered on disk so it would have to be moved.
– indiri
Sep 1 '17 at 4:15
Yes but inserts don't move new data, at least not all of it, it enters new pages.
– eckes
Sep 1 '17 at 9:45
add a comment |
Unique-id Cluster-index inserts won't move all the data but it will fragment the index (aka table storage) by inserting new pages at random places.
– eckes
Sep 1 '17 at 0:18
A unique NON cluster index won't move the data. A clustered index will move it. By definition a clustered index is physically ordered on disk so it would have to be moved.
– indiri
Sep 1 '17 at 4:15
Yes but inserts don't move new data, at least not all of it, it enters new pages.
– eckes
Sep 1 '17 at 9:45
Unique-id Cluster-index inserts won't move all the data but it will fragment the index (aka table storage) by inserting new pages at random places.
– eckes
Sep 1 '17 at 0:18
Unique-id Cluster-index inserts won't move all the data but it will fragment the index (aka table storage) by inserting new pages at random places.
– eckes
Sep 1 '17 at 0:18
A unique NON cluster index won't move the data. A clustered index will move it. By definition a clustered index is physically ordered on disk so it would have to be moved.
– indiri
Sep 1 '17 at 4:15
A unique NON cluster index won't move the data. A clustered index will move it. By definition a clustered index is physically ordered on disk so it would have to be moved.
– indiri
Sep 1 '17 at 4:15
Yes but inserts don't move new data, at least not all of it, it enters new pages.
– eckes
Sep 1 '17 at 9:45
Yes but inserts don't move new data, at least not all of it, it enters new pages.
– eckes
Sep 1 '17 at 9:45
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f184840%2fbest-option-to-index-an-application-log-table-for-quick-insertion-and-retrieval%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I would Remove the uniqueid key and use the date as a clustered index.
– eckes
Sep 1 '17 at 0:17