Optimizing TSQL
There is a select query that runs repeatedly with the first column in the select clause being the only thing which gets replaced by other column and the rest of the query structure remain the same.
So that this query plan get cached, is there any way that we can parametrize the first column.
Select c1,p,a
From table1,
Select c2,p,a
From table1
Select c3,p,a
From table1
Select c4,p,a
From table1
Every time the query runs ,only the first column is the one changing . Is there any way to optimize this kind of query?
tsql optimization
asked Nov 19 '18 at 8:31
M. SolM. Sol
11
add a comment |
There is a select query that runs repeatedly with the first column in the select clause being the only thing which gets replaced by other column and the rest of the query structure remain the same.
So that this query plan get cached, is there any way that we can parametrize the first column.
Select c1,p,a
From table1,
Select c2,p,a
From table1
Select c3,p,a
From table1
Select c4,p,a
From table1
Every time the query runs ,only the first column is the one changing . Is there any way to optimize this kind of query?
tsql optimization
asked Nov 19 '18 at 8:31
M. SolM. Sol
11
1
I don't think there's a way to "parameterise" this. You don't have awhereclause, so you're getting all records regardless. Since you're getting all records and not ordering / anything special, they'll just be returned from the table itself (i.e. there's no benefit looking for these values in indexes), so this is the best you'll get.
– JohnLBevan
Nov 19 '18 at 8:37
If this is really your query, I doubt you will gain anything IF query caching is possible. The query optimizer takes a decent time when it has to decide one among multiple ways of executing the query; A simple select columns from table has no alternatives, maybe an index, but it won't take too much.
– George Menoutis
Nov 19 '18 at 9:56
@JohnLBevan I'm pretty sure that the above was shortened for brevity... I take "the rest of the query structure remain the same" as "there is a rather complex query behind"... What you cannot parameterise will be the column's alias, but the content should be rather easy...
– Shnugo
Nov 19 '18 at 11:29
add a comment |
There is a select query that runs repeatedly with the first column in the select clause being the only thing which gets replaced by other column and the rest of the query structure remain the same.
So that this query plan get cached, is there any way that we can parametrize the first column.
Select c1,p,a
From table1,
Select c2,p,a
From table1
Select c3,p,a
From table1
Select c4,p,a
From table1
Every time the query runs ,only the first column is the one changing . Is there any way to optimize this kind of query?
tsql optimization
asked Nov 19 '18 at 8:31
M. SolM. Sol
11
There is a select query that runs repeatedly with the first column in the select clause being the only thing which gets replaced by other column and the rest of the query structure remain the same.
So that this query plan get cached, is there any way that we can parametrize the first column.
Select c1,p,a
From table1,
Select c2,p,a
From table1
Select c3,p,a
From table1
Select c4,p,a
From table1
Every time the query runs ,only the first column is the one changing . Is there any way to optimize this kind of query?
tsql optimization
tsql optimization
asked Nov 19 '18 at 8:31
M. SolM. Sol
11
asked Nov 19 '18 at 8:31
M. SolM. Sol
11
asked Nov 19 '18 at 8:31
M. SolM. Sol
11
asked Nov 19 '18 at 8:31
M. SolM. Sol
11
asked Nov 19 '18 at 8:31
M. SolM. Sol
11
11
1
I don't think there's a way to "parameterise" this. You don't have awhereclause, so you're getting all records regardless. Since you're getting all records and not ordering / anything special, they'll just be returned from the table itself (i.e. there's no benefit looking for these values in indexes), so this is the best you'll get.
– JohnLBevan
Nov 19 '18 at 8:37
If this is really your query, I doubt you will gain anything IF query caching is possible. The query optimizer takes a decent time when it has to decide one among multiple ways of executing the query; A simple select columns from table has no alternatives, maybe an index, but it won't take too much.
– George Menoutis
Nov 19 '18 at 9:56
@JohnLBevan I'm pretty sure that the above was shortened for brevity... I take "the rest of the query structure remain the same" as "there is a rather complex query behind"... What you cannot parameterise will be the column's alias, but the content should be rather easy...
– Shnugo
Nov 19 '18 at 11:29
add a comment |
1
I don't think there's a way to "parameterise" this. You don't have awhereclause, so you're getting all records regardless. Since you're getting all records and not ordering / anything special, they'll just be returned from the table itself (i.e. there's no benefit looking for these values in indexes), so this is the best you'll get.
– JohnLBevan
Nov 19 '18 at 8:37
If this is really your query, I doubt you will gain anything IF query caching is possible. The query optimizer takes a decent time when it has to decide one among multiple ways of executing the query; A simple select columns from table has no alternatives, maybe an index, but it won't take too much.
– George Menoutis
Nov 19 '18 at 9:56
@JohnLBevan I'm pretty sure that the above was shortened for brevity... I take "the rest of the query structure remain the same" as "there is a rather complex query behind"... What you cannot parameterise will be the column's alias, but the content should be rather easy...
– Shnugo
Nov 19 '18 at 11:29
1
1
I don't think there's a way to "parameterise" this. You don't have a
where clause, so you're getting all records regardless. Since you're getting all records and not ordering / anything special, they'll just be returned from the table itself (i.e. there's no benefit looking for these values in indexes), so this is the best you'll get.– JohnLBevan
Nov 19 '18 at 8:37
I don't think there's a way to "parameterise" this. You don't have a
where clause, so you're getting all records regardless. Since you're getting all records and not ordering / anything special, they'll just be returned from the table itself (i.e. there's no benefit looking for these values in indexes), so this is the best you'll get.– JohnLBevan
Nov 19 '18 at 8:37
If this is really your query, I doubt you will gain anything IF query caching is possible. The query optimizer takes a decent time when it has to decide one among multiple ways of executing the query; A simple select columns from table has no alternatives, maybe an index, but it won't take too much.
– George Menoutis
Nov 19 '18 at 9:56
If this is really your query, I doubt you will gain anything IF query caching is possible. The query optimizer takes a decent time when it has to decide one among multiple ways of executing the query; A simple select columns from table has no alternatives, maybe an index, but it won't take too much.
– George Menoutis
Nov 19 '18 at 9:56
@JohnLBevan I'm pretty sure that the above was shortened for brevity... I take "the rest of the query structure remain the same" as "there is a rather complex query behind"... What you cannot parameterise will be the column's alias, but the content should be rather easy...
– Shnugo
Nov 19 '18 at 11:29
@JohnLBevan I'm pretty sure that the above was shortened for brevity... I take "the rest of the query structure remain the same" as "there is a rather complex query behind"... What you cannot parameterise will be the column's alias, but the content should be rather easy...
– Shnugo
Nov 19 '18 at 11:29
add a comment |
1 Answer
1
active
oldest
votes
I must admit, that this smells a bit. Might be that there are better approaches, but we do not know your use cases.
You can try this:
DECLARE @mockup TABLE(c1 INT, c2 INT, c3 INT, p VARCHAR(100),a VARCHAR(100));
INSERT INTO @mockup VALUES(1,2,3,'Row 1','blah 1')
,(11,22,33,'Row 2','blah 2')
,(111,222,333,'Row 3','blah 3');
DECLARE @FirstColumn VARCHAR(10)='c3';
SELECT CASE @FirstColumn WHEN 'c1' THEN c1
WHEN 'c2' THEN c2
WHEN 'c3' THEN c3
ELSE NULL END AS DynamicFirstColumn
,p
,a
FROM @mockup;
The idea is to use a CASE to decide which column is seen in the first place following a parameter. If you want to see a varying column name too (I used DynamicFirstColumn) you can
- create a VIEW for each case or
- use dynamically created SQL.
As a workaround you can include the handed in parameter and return it with the result set. In this case the consumer would know, which column was used...
SELECT CASE @FirstColumn WHEN 'c1' THEN c1
WHEN 'c2' THEN c2
WHEN 'c3' THEN c3
ELSE NULL END AS DynamicFirstColumn
,p
,a
,@FirstColumn --<-- Here we include the source we use above
FROM @mockup
answered Nov 19 '18 at 11:24
ShnugoShnugo
49.5k72668
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%2f53370876%2foptimizing-tsql%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 must admit, that this smells a bit. Might be that there are better approaches, but we do not know your use cases.
You can try this:
DECLARE @mockup TABLE(c1 INT, c2 INT, c3 INT, p VARCHAR(100),a VARCHAR(100));
INSERT INTO @mockup VALUES(1,2,3,'Row 1','blah 1')
,(11,22,33,'Row 2','blah 2')
,(111,222,333,'Row 3','blah 3');
DECLARE @FirstColumn VARCHAR(10)='c3';
SELECT CASE @FirstColumn WHEN 'c1' THEN c1
WHEN 'c2' THEN c2
WHEN 'c3' THEN c3
ELSE NULL END AS DynamicFirstColumn
,p
,a
FROM @mockup;
The idea is to use a CASE to decide which column is seen in the first place following a parameter. If you want to see a varying column name too (I used DynamicFirstColumn) you can
- create a VIEW for each case or
- use dynamically created SQL.
As a workaround you can include the handed in parameter and return it with the result set. In this case the consumer would know, which column was used...
SELECT CASE @FirstColumn WHEN 'c1' THEN c1
WHEN 'c2' THEN c2
WHEN 'c3' THEN c3
ELSE NULL END AS DynamicFirstColumn
,p
,a
,@FirstColumn --<-- Here we include the source we use above
FROM @mockup
answered Nov 19 '18 at 11:24
ShnugoShnugo
49.5k72668
add a comment |
I must admit, that this smells a bit. Might be that there are better approaches, but we do not know your use cases.
You can try this:
DECLARE @mockup TABLE(c1 INT, c2 INT, c3 INT, p VARCHAR(100),a VARCHAR(100));
INSERT INTO @mockup VALUES(1,2,3,'Row 1','blah 1')
,(11,22,33,'Row 2','blah 2')
,(111,222,333,'Row 3','blah 3');
DECLARE @FirstColumn VARCHAR(10)='c3';
SELECT CASE @FirstColumn WHEN 'c1' THEN c1
WHEN 'c2' THEN c2
WHEN 'c3' THEN c3
ELSE NULL END AS DynamicFirstColumn
,p
,a
FROM @mockup;
The idea is to use a CASE to decide which column is seen in the first place following a parameter. If you want to see a varying column name too (I used DynamicFirstColumn) you can
- create a VIEW for each case or
- use dynamically created SQL.
As a workaround you can include the handed in parameter and return it with the result set. In this case the consumer would know, which column was used...
SELECT CASE @FirstColumn WHEN 'c1' THEN c1
WHEN 'c2' THEN c2
WHEN 'c3' THEN c3
ELSE NULL END AS DynamicFirstColumn
,p
,a
,@FirstColumn --<-- Here we include the source we use above
FROM @mockup
answered Nov 19 '18 at 11:24
ShnugoShnugo
49.5k72668
add a comment |
I must admit, that this smells a bit. Might be that there are better approaches, but we do not know your use cases.
You can try this:
DECLARE @mockup TABLE(c1 INT, c2 INT, c3 INT, p VARCHAR(100),a VARCHAR(100));
INSERT INTO @mockup VALUES(1,2,3,'Row 1','blah 1')
,(11,22,33,'Row 2','blah 2')
,(111,222,333,'Row 3','blah 3');
DECLARE @FirstColumn VARCHAR(10)='c3';
SELECT CASE @FirstColumn WHEN 'c1' THEN c1
WHEN 'c2' THEN c2
WHEN 'c3' THEN c3
ELSE NULL END AS DynamicFirstColumn
,p
,a
FROM @mockup;
The idea is to use a CASE to decide which column is seen in the first place following a parameter. If you want to see a varying column name too (I used DynamicFirstColumn) you can
- create a VIEW for each case or
- use dynamically created SQL.
As a workaround you can include the handed in parameter and return it with the result set. In this case the consumer would know, which column was used...
SELECT CASE @FirstColumn WHEN 'c1' THEN c1
WHEN 'c2' THEN c2
WHEN 'c3' THEN c3
ELSE NULL END AS DynamicFirstColumn
,p
,a
,@FirstColumn --<-- Here we include the source we use above
FROM @mockup
answered Nov 19 '18 at 11:24
ShnugoShnugo
49.5k72668
I must admit, that this smells a bit. Might be that there are better approaches, but we do not know your use cases.
You can try this:
DECLARE @mockup TABLE(c1 INT, c2 INT, c3 INT, p VARCHAR(100),a VARCHAR(100));
INSERT INTO @mockup VALUES(1,2,3,'Row 1','blah 1')
,(11,22,33,'Row 2','blah 2')
,(111,222,333,'Row 3','blah 3');
DECLARE @FirstColumn VARCHAR(10)='c3';
SELECT CASE @FirstColumn WHEN 'c1' THEN c1
WHEN 'c2' THEN c2
WHEN 'c3' THEN c3
ELSE NULL END AS DynamicFirstColumn
,p
,a
FROM @mockup;
The idea is to use a CASE to decide which column is seen in the first place following a parameter. If you want to see a varying column name too (I used DynamicFirstColumn) you can
- create a VIEW for each case or
- use dynamically created SQL.
As a workaround you can include the handed in parameter and return it with the result set. In this case the consumer would know, which column was used...
SELECT CASE @FirstColumn WHEN 'c1' THEN c1
WHEN 'c2' THEN c2
WHEN 'c3' THEN c3
ELSE NULL END AS DynamicFirstColumn
,p
,a
,@FirstColumn --<-- Here we include the source we use above
FROM @mockup
answered Nov 19 '18 at 11:24
ShnugoShnugo
49.5k72668
answered Nov 19 '18 at 11:24
ShnugoShnugo
49.5k72668
answered Nov 19 '18 at 11:24
ShnugoShnugo
49.5k72668
answered Nov 19 '18 at 11:24
ShnugoShnugo
49.5k72668
49.5k72668
add a comment |
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%2f53370876%2foptimizing-tsql%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
1
I don't think there's a way to "parameterise" this. You don't have a
whereclause, so you're getting all records regardless. Since you're getting all records and not ordering / anything special, they'll just be returned from the table itself (i.e. there's no benefit looking for these values in indexes), so this is the best you'll get.– JohnLBevan
Nov 19 '18 at 8:37
If this is really your query, I doubt you will gain anything IF query caching is possible. The query optimizer takes a decent time when it has to decide one among multiple ways of executing the query; A simple select columns from table has no alternatives, maybe an index, but it won't take too much.
– George Menoutis
Nov 19 '18 at 9:56
@JohnLBevan I'm pretty sure that the above was shortened for brevity... I take "the rest of the query structure remain the same" as "there is a rather complex query behind"... What you cannot parameterise will be the column's alias, but the content should be rather easy...
– Shnugo
Nov 19 '18 at 11:29