Mixing
SQL Server use of FETCH OFFSET with complex statements
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I use OFFSET FETCH syntax to get pagination done in SQL Server for my web application.
NOTE: the application has a user interface where a user can build his view, selecting the columns, those columns can be referenced values.
This works pretty fine with simple SQL statement but gets real slow if my SQL statements becomes too complex.
Example table with 10k rows:
SELECT *
FROM
(SELECT TOP 100 PERCENT
id, title
FROM mytable) t
ORDER BY title
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;
Assume, this query takes < 1s in SQL Server Management Studio.
Now I start implement referenced tables to my statement.
SELECT *
FROM
(SELECT TOP 100 PERCENT
id, title,
(SELECT value
FROM table_reference
WHERE id = ref_id) AS ref_value,
(SELECT value
FROM table_reference2
WHERE id = ref_id2) AS ref_value2
FROM
mytable) t
ORDER BY title
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;
This still performs well.
Now I do use the referenced values in my WHERE clause:
SELECT *
FROM
(SELECT TOP 100 PERCENT
id, title,
(SELECT value
FROM table_reference
WHERE id = ref_id) AS ref_value,
(SELECT value
FROM table_reference2
WHERE id = ref_id2) AS ref_value2
FROM
mytable) t
WHERE
ref_value LIKE '%something%'
ORDER BY title
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;
Now my query is going from < 1s to 14seconds.
As my investigations from query analyzer and web I saw that OFFSET is the performance issue here due the way it works. Most web information praise FETCH OFFSET as easy to use and fast, but I could not find any detail info about bottlenecks and performance issues.
So as the UI also allow to define which columns are used for "full like searches" I seek for a solution to manage the way those values can be used and still no not crash my performance.
I thought about creating another column which holds the selected value, but if the reference changes this value is outdated. Also a trigger won't work on the referenced table to update as this is getting to complex and not maintainable.
I never took a look at it, but might computed columns be a option to check if this works? Or any other good advice for this specific problem?
Thanks, Yves
sql-server performance edited Jan 3 at 12:28
marc_s
585k13011251272
asked Jan 3 at 11:50
YvesRYvesR
3,51253258
add a comment |
I use OFFSET FETCH syntax to get pagination done in SQL Server for my web application.
NOTE: the application has a user interface where a user can build his view, selecting the columns, those columns can be referenced values.
This works pretty fine with simple SQL statement but gets real slow if my SQL statements becomes too complex.
Example table with 10k rows:
SELECT *
FROM
(SELECT TOP 100 PERCENT
id, title
FROM mytable) t
ORDER BY title
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;
Assume, this query takes < 1s in SQL Server Management Studio.
Now I start implement referenced tables to my statement.
SELECT *
FROM
(SELECT TOP 100 PERCENT
id, title,
(SELECT value
FROM table_reference
WHERE id = ref_id) AS ref_value,
(SELECT value
FROM table_reference2
WHERE id = ref_id2) AS ref_value2
FROM
mytable) t
ORDER BY title
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;
This still performs well.
Now I do use the referenced values in my WHERE clause:
SELECT *
FROM
(SELECT TOP 100 PERCENT
id, title,
(SELECT value
FROM table_reference
WHERE id = ref_id) AS ref_value,
(SELECT value
FROM table_reference2
WHERE id = ref_id2) AS ref_value2
FROM
mytable) t
WHERE
ref_value LIKE '%something%'
ORDER BY title
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;
Now my query is going from < 1s to 14seconds.
As my investigations from query analyzer and web I saw that OFFSET is the performance issue here due the way it works. Most web information praise FETCH OFFSET as easy to use and fast, but I could not find any detail info about bottlenecks and performance issues.
So as the UI also allow to define which columns are used for "full like searches" I seek for a solution to manage the way those values can be used and still no not crash my performance.
I thought about creating another column which holds the selected value, but if the reference changes this value is outdated. Also a trigger won't work on the referenced table to update as this is getting to complex and not maintainable.
I never took a look at it, but might computed columns be a option to check if this works? Or any other good advice for this specific problem?
Thanks, Yves
sql-server performance edited Jan 3 at 12:28
marc_s
585k13011251272
asked Jan 3 at 11:50
YvesRYvesR
3,51253258
3
When you use a leading wildcard on a LIKE clause, SQL Server will perform a full-table scan, which will always be very slow with a large table. You need to implement Full Text search and useCONTAINSinstead
– Martin
Jan 3 at 11:54
UseCHARINDEX('smoething', ref_value) > 1instead ofref_value LIKE '%something%'
– Michał Turczyn
Jan 3 at 12:09
add a comment |
I use OFFSET FETCH syntax to get pagination done in SQL Server for my web application.
NOTE: the application has a user interface where a user can build his view, selecting the columns, those columns can be referenced values.
This works pretty fine with simple SQL statement but gets real slow if my SQL statements becomes too complex.
Example table with 10k rows:
SELECT *
FROM
(SELECT TOP 100 PERCENT
id, title
FROM mytable) t
ORDER BY title
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;
Assume, this query takes < 1s in SQL Server Management Studio.
Now I start implement referenced tables to my statement.
SELECT *
FROM
(SELECT TOP 100 PERCENT
id, title,
(SELECT value
FROM table_reference
WHERE id = ref_id) AS ref_value,
(SELECT value
FROM table_reference2
WHERE id = ref_id2) AS ref_value2
FROM
mytable) t
ORDER BY title
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;
This still performs well.
Now I do use the referenced values in my WHERE clause:
SELECT *
FROM
(SELECT TOP 100 PERCENT
id, title,
(SELECT value
FROM table_reference
WHERE id = ref_id) AS ref_value,
(SELECT value
FROM table_reference2
WHERE id = ref_id2) AS ref_value2
FROM
mytable) t
WHERE
ref_value LIKE '%something%'
ORDER BY title
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;
Now my query is going from < 1s to 14seconds.
As my investigations from query analyzer and web I saw that OFFSET is the performance issue here due the way it works. Most web information praise FETCH OFFSET as easy to use and fast, but I could not find any detail info about bottlenecks and performance issues.
So as the UI also allow to define which columns are used for "full like searches" I seek for a solution to manage the way those values can be used and still no not crash my performance.
I thought about creating another column which holds the selected value, but if the reference changes this value is outdated. Also a trigger won't work on the referenced table to update as this is getting to complex and not maintainable.
I never took a look at it, but might computed columns be a option to check if this works? Or any other good advice for this specific problem?
Thanks, Yves
sql-server performance edited Jan 3 at 12:28
marc_s
585k13011251272
asked Jan 3 at 11:50
YvesRYvesR
3,51253258
I use OFFSET FETCH syntax to get pagination done in SQL Server for my web application.
NOTE: the application has a user interface where a user can build his view, selecting the columns, those columns can be referenced values.
This works pretty fine with simple SQL statement but gets real slow if my SQL statements becomes too complex.
Example table with 10k rows:
SELECT *
FROM
(SELECT TOP 100 PERCENT
id, title
FROM mytable) t
ORDER BY title
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;
Assume, this query takes < 1s in SQL Server Management Studio.
Now I start implement referenced tables to my statement.
SELECT *
FROM
(SELECT TOP 100 PERCENT
id, title,
(SELECT value
FROM table_reference
WHERE id = ref_id) AS ref_value,
(SELECT value
FROM table_reference2
WHERE id = ref_id2) AS ref_value2
FROM
mytable) t
ORDER BY title
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;
This still performs well.
Now I do use the referenced values in my WHERE clause:
SELECT *
FROM
(SELECT TOP 100 PERCENT
id, title,
(SELECT value
FROM table_reference
WHERE id = ref_id) AS ref_value,
(SELECT value
FROM table_reference2
WHERE id = ref_id2) AS ref_value2
FROM
mytable) t
WHERE
ref_value LIKE '%something%'
ORDER BY title
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;
Now my query is going from < 1s to 14seconds.
As my investigations from query analyzer and web I saw that OFFSET is the performance issue here due the way it works. Most web information praise FETCH OFFSET as easy to use and fast, but I could not find any detail info about bottlenecks and performance issues.
So as the UI also allow to define which columns are used for "full like searches" I seek for a solution to manage the way those values can be used and still no not crash my performance.
I thought about creating another column which holds the selected value, but if the reference changes this value is outdated. Also a trigger won't work on the referenced table to update as this is getting to complex and not maintainable.
I never took a look at it, but might computed columns be a option to check if this works? Or any other good advice for this specific problem?
Thanks, Yves
sql-server performance sql-server performance edited Jan 3 at 12:28
marc_s
585k13011251272
asked Jan 3 at 11:50
YvesRYvesR
3,51253258
edited Jan 3 at 12:28
marc_s
585k13011251272
asked Jan 3 at 11:50
YvesRYvesR
3,51253258
edited Jan 3 at 12:28
marc_s
585k13011251272
edited Jan 3 at 12:28
marc_s
585k13011251272
edited Jan 3 at 12:28
marc_s
585k13011251272
585k13011251272
asked Jan 3 at 11:50
YvesRYvesR
3,51253258
asked Jan 3 at 11:50
YvesRYvesR
3,51253258
asked Jan 3 at 11:50
YvesRYvesR
3,51253258
3,51253258
3
When you use a leading wildcard on a LIKE clause, SQL Server will perform a full-table scan, which will always be very slow with a large table. You need to implement Full Text search and useCONTAINSinstead
– Martin
Jan 3 at 11:54
UseCHARINDEX('smoething', ref_value) > 1instead ofref_value LIKE '%something%'
– Michał Turczyn
Jan 3 at 12:09
add a comment |
3
When you use a leading wildcard on a LIKE clause, SQL Server will perform a full-table scan, which will always be very slow with a large table. You need to implement Full Text search and useCONTAINSinstead
– Martin
Jan 3 at 11:54
UseCHARINDEX('smoething', ref_value) > 1instead ofref_value LIKE '%something%'
– Michał Turczyn
Jan 3 at 12:09
3
3
When you use a leading wildcard on a LIKE clause, SQL Server will perform a full-table scan, which will always be very slow with a large table. You need to implement Full Text search and use
CONTAINS instead– Martin
Jan 3 at 11:54
When you use a leading wildcard on a LIKE clause, SQL Server will perform a full-table scan, which will always be very slow with a large table. You need to implement Full Text search and use
CONTAINS instead– Martin
Jan 3 at 11:54
Use
CHARINDEX('smoething', ref_value) > 1 instead of ref_value LIKE '%something%'– Michał Turczyn
Jan 3 at 12:09
Use
CHARINDEX('smoething', ref_value) > 1 instead of ref_value LIKE '%something%'– Michał Turczyn
Jan 3 at 12:09
add a comment |
0
active
oldest
votes
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%2f54021724%2fsql-server-use-of-fetch-offset-with-complex-statements%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f54021724%2fsql-server-use-of-fetch-offset-with-complex-statements%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
3
When you use a leading wildcard on a LIKE clause, SQL Server will perform a full-table scan, which will always be very slow with a large table. You need to implement Full Text search and use
CONTAINSinstead– Martin
Jan 3 at 11:54
Use
CHARINDEX('smoething', ref_value) > 1instead ofref_value LIKE '%something%'– Michał Turczyn
Jan 3 at 12:09