Sql: Joining view on computed columns vs performance
I have some Sql tables with a primary key that's includes more column. I created a view on this
tables and I added a computed column that is a concatenation of table's primary key, separated by a separator. (for example: ColumnA$ColumnB$ColumnC is concatenation of Column A, B e C that's table key).
When I use this view I filter on computed column to work with primary key.
In other case I have a query that put in join more view. Foreign key on the view is computed like primary key and the joins are on computed column.
The scope of this work is to simplified key to simplified integration with other software.
Could this execution scenario affect significatly performance?
Thanks in advance
Luca
sql-server view database-performance asked Nov 13 '18 at 11:06
Luca MurzioLuca Murzio
163
add a comment |
I have some Sql tables with a primary key that's includes more column. I created a view on this
tables and I added a computed column that is a concatenation of table's primary key, separated by a separator. (for example: ColumnA$ColumnB$ColumnC is concatenation of Column A, B e C that's table key).
When I use this view I filter on computed column to work with primary key.
In other case I have a query that put in join more view. Foreign key on the view is computed like primary key and the joins are on computed column.
The scope of this work is to simplified key to simplified integration with other software.
Could this execution scenario affect significatly performance?
Thanks in advance
Luca
sql-server view database-performance asked Nov 13 '18 at 11:06
Luca MurzioLuca Murzio
163
add a comment |
I have some Sql tables with a primary key that's includes more column. I created a view on this
tables and I added a computed column that is a concatenation of table's primary key, separated by a separator. (for example: ColumnA$ColumnB$ColumnC is concatenation of Column A, B e C that's table key).
When I use this view I filter on computed column to work with primary key.
In other case I have a query that put in join more view. Foreign key on the view is computed like primary key and the joins are on computed column.
The scope of this work is to simplified key to simplified integration with other software.
Could this execution scenario affect significatly performance?
Thanks in advance
Luca
sql-server view database-performance asked Nov 13 '18 at 11:06
Luca MurzioLuca Murzio
163
I have some Sql tables with a primary key that's includes more column. I created a view on this
tables and I added a computed column that is a concatenation of table's primary key, separated by a separator. (for example: ColumnA$ColumnB$ColumnC is concatenation of Column A, B e C that's table key).
When I use this view I filter on computed column to work with primary key.
In other case I have a query that put in join more view. Foreign key on the view is computed like primary key and the joins are on computed column.
The scope of this work is to simplified key to simplified integration with other software.
Could this execution scenario affect significatly performance?
Thanks in advance
Luca
sql-server view database-performance sql-server view database-performance asked Nov 13 '18 at 11:06
Luca MurzioLuca Murzio
163
asked Nov 13 '18 at 11:06
Luca MurzioLuca Murzio
163
asked Nov 13 '18 at 11:06
Luca MurzioLuca Murzio
163
asked Nov 13 '18 at 11:06
Luca MurzioLuca Murzio
163
asked Nov 13 '18 at 11:06
Luca MurzioLuca Murzio
163
163
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Better idea would be to keep these columns separately just as you have them natively in your tables, then you can create your index/PK based on all 3 columns not just a concentrated single one. For the performance I would probably suggest here to use indexed view here. Other way if we talk about 3 string columns you can use some hashing techniques as long as you can handle that extreme minimum hashing duplication exception on your application end.
answered Nov 13 '18 at 12:00
Bartosz XBartosz X
1,0871020
Materialized view isn't possibile in my framework, I will evaluate hashing to understand if it is good for my problem.
– Luca Murzio
Nov 14 '18 at 7:46
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f53279648%2fsql-joining-view-on-computed-columns-vs-performance%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
Better idea would be to keep these columns separately just as you have them natively in your tables, then you can create your index/PK based on all 3 columns not just a concentrated single one. For the performance I would probably suggest here to use indexed view here. Other way if we talk about 3 string columns you can use some hashing techniques as long as you can handle that extreme minimum hashing duplication exception on your application end.
answered Nov 13 '18 at 12:00
Bartosz XBartosz X
1,0871020
Materialized view isn't possibile in my framework, I will evaluate hashing to understand if it is good for my problem.
– Luca Murzio
Nov 14 '18 at 7:46
add a comment |
Better idea would be to keep these columns separately just as you have them natively in your tables, then you can create your index/PK based on all 3 columns not just a concentrated single one. For the performance I would probably suggest here to use indexed view here. Other way if we talk about 3 string columns you can use some hashing techniques as long as you can handle that extreme minimum hashing duplication exception on your application end.
answered Nov 13 '18 at 12:00
Bartosz XBartosz X
1,0871020
Materialized view isn't possibile in my framework, I will evaluate hashing to understand if it is good for my problem.
– Luca Murzio
Nov 14 '18 at 7:46
add a comment |
Better idea would be to keep these columns separately just as you have them natively in your tables, then you can create your index/PK based on all 3 columns not just a concentrated single one. For the performance I would probably suggest here to use indexed view here. Other way if we talk about 3 string columns you can use some hashing techniques as long as you can handle that extreme minimum hashing duplication exception on your application end.
answered Nov 13 '18 at 12:00
Bartosz XBartosz X
1,0871020
Better idea would be to keep these columns separately just as you have them natively in your tables, then you can create your index/PK based on all 3 columns not just a concentrated single one. For the performance I would probably suggest here to use indexed view here. Other way if we talk about 3 string columns you can use some hashing techniques as long as you can handle that extreme minimum hashing duplication exception on your application end.
answered Nov 13 '18 at 12:00
Bartosz XBartosz X
1,0871020
answered Nov 13 '18 at 12:00
Bartosz XBartosz X
1,0871020
answered Nov 13 '18 at 12:00
Bartosz XBartosz X
1,0871020
answered Nov 13 '18 at 12:00
Bartosz XBartosz X
1,0871020
1,0871020
Materialized view isn't possibile in my framework, I will evaluate hashing to understand if it is good for my problem.
– Luca Murzio
Nov 14 '18 at 7:46
add a comment |
Materialized view isn't possibile in my framework, I will evaluate hashing to understand if it is good for my problem.
– Luca Murzio
Nov 14 '18 at 7:46
Materialized view isn't possibile in my framework, I will evaluate hashing to understand if it is good for my problem.
– Luca Murzio
Nov 14 '18 at 7:46
Materialized view isn't possibile in my framework, I will evaluate hashing to understand if it is good for my problem.
– Luca Murzio
Nov 14 '18 at 7:46
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53279648%2fsql-joining-view-on-computed-columns-vs-performance%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
