Time Complexity of SQL Cursor
0
I am using CURSOR to implement the following in SQL Server, I am only iterating through the table - The time complexity will be O(n) I think (?). But everywhere I read about CURSOR, it says CURSOR is a bad practice. So is there a better way to implement the following ?
Existing Table
month value
1 92
4 20
9 92
New Table
month value
1 92
2 92
3 92
4 20
5 20
6 20
7 20
8 20
9 92
10 92
11 92
12 92
sql-server time-complexity asked Nov 12 '18 at 20:09
Rudra Roy
326
|
show 3 more comments
0
I am using CURSOR to implement the following in SQL Server, I am only iterating through the table - The time complexity will be O(n) I think (?). But everywhere I read about CURSOR, it says CURSOR is a bad practice. So is there a better way to implement the following ?
Existing Table
month value
1 92
4 20
9 92
New Table
month value
1 92
2 92
3 92
4 20
5 20
6 20
7 20
8 20
9 92
10 92
11 92
12 92
asked Nov 12 '18 at 20:09
Rudra Roy
326
Personally, since there are only 12 lines/rows, I would simply copy/paste/alter the sql and run it.
– UnhandledExcepSean
Nov 12 '18 at 20:20
1
Big-O complexity is mostly irrelevant when we're talking cursors -- the reason they're to be avoided is because using them implies you're doing loops in horribly slow interpreted code, as opposed to basking in the goodness of compiled execution plans with operators that can employ parallelism, caching, readahead and all that beautiful stuff. Technically, both the cursor and the set-based equivalent are typically linear time (ignoring parallelism), but you still want the set-based equivalent. This kind of query (filling gaps) is typically handled with a numbers table or equivalent CTE.
– Jeroen Mostert
Nov 12 '18 at 20:21
@UnhandledExcepSean I have simplified the data. the actual data has years and it's a lot of data
– Rudra Roy
Nov 12 '18 at 20:24
1
Yes, there are cases where cursors are faster than any set-based query, but these are quite rare, and get rarer with each new release of SQL Server that introduces more operators and windowing expressions. SQL is primarily a "describe the desired result and I'll figure out the execution" language, not a "specify the exact operations yourself" language. This reason alone, that authors of both queries and the engine itself are encouraged to think in terms of sets, speaks against using cursors even in the rare cases where you can see a strategy that the engine cannot be compelled to undertake.
– Jeroen Mostert
Nov 12 '18 at 20:32
1
I actually would argue that the only time cursors should be used is if you need to call a system stored procedure (like db mail). Cursors can perform ok, but I've never found where they were faster than a set based approach.
– UnhandledExcepSean
Nov 12 '18 at 20:40
|
show 3 more comments
0
0
0
I am using CURSOR to implement the following in SQL Server, I am only iterating through the table - The time complexity will be O(n) I think (?). But everywhere I read about CURSOR, it says CURSOR is a bad practice. So is there a better way to implement the following ?
Existing Table
month value
1 92
4 20
9 92
New Table
month value
1 92
2 92
3 92
4 20
5 20
6 20
7 20
8 20
9 92
10 92
11 92
12 92
asked Nov 12 '18 at 20:09
Rudra Roy
326
I am using CURSOR to implement the following in SQL Server, I am only iterating through the table - The time complexity will be O(n) I think (?). But everywhere I read about CURSOR, it says CURSOR is a bad practice. So is there a better way to implement the following ?
Existing Table
month value
1 92
4 20
9 92
New Table
month value
1 92
2 92
3 92
4 20
5 20
6 20
7 20
8 20
9 92
10 92
11 92
12 92
asked Nov 12 '18 at 20:09
Rudra Roy
326
asked Nov 12 '18 at 20:09
Rudra Roy
326
asked Nov 12 '18 at 20:09
Rudra Roy
326
asked Nov 12 '18 at 20:09
Rudra Roy
326
asked Nov 12 '18 at 20:09
Rudra Roy
326
326
Personally, since there are only 12 lines/rows, I would simply copy/paste/alter the sql and run it.
– UnhandledExcepSean
Nov 12 '18 at 20:20
1
Big-O complexity is mostly irrelevant when we're talking cursors -- the reason they're to be avoided is because using them implies you're doing loops in horribly slow interpreted code, as opposed to basking in the goodness of compiled execution plans with operators that can employ parallelism, caching, readahead and all that beautiful stuff. Technically, both the cursor and the set-based equivalent are typically linear time (ignoring parallelism), but you still want the set-based equivalent. This kind of query (filling gaps) is typically handled with a numbers table or equivalent CTE.
– Jeroen Mostert
Nov 12 '18 at 20:21
@UnhandledExcepSean I have simplified the data. the actual data has years and it's a lot of data
– Rudra Roy
Nov 12 '18 at 20:24
1
Yes, there are cases where cursors are faster than any set-based query, but these are quite rare, and get rarer with each new release of SQL Server that introduces more operators and windowing expressions. SQL is primarily a "describe the desired result and I'll figure out the execution" language, not a "specify the exact operations yourself" language. This reason alone, that authors of both queries and the engine itself are encouraged to think in terms of sets, speaks against using cursors even in the rare cases where you can see a strategy that the engine cannot be compelled to undertake.
– Jeroen Mostert
Nov 12 '18 at 20:32
1
I actually would argue that the only time cursors should be used is if you need to call a system stored procedure (like db mail). Cursors can perform ok, but I've never found where they were faster than a set based approach.
– UnhandledExcepSean
Nov 12 '18 at 20:40
|
show 3 more comments
Personally, since there are only 12 lines/rows, I would simply copy/paste/alter the sql and run it.
– UnhandledExcepSean
Nov 12 '18 at 20:20
1
Big-O complexity is mostly irrelevant when we're talking cursors -- the reason they're to be avoided is because using them implies you're doing loops in horribly slow interpreted code, as opposed to basking in the goodness of compiled execution plans with operators that can employ parallelism, caching, readahead and all that beautiful stuff. Technically, both the cursor and the set-based equivalent are typically linear time (ignoring parallelism), but you still want the set-based equivalent. This kind of query (filling gaps) is typically handled with a numbers table or equivalent CTE.
– Jeroen Mostert
Nov 12 '18 at 20:21
@UnhandledExcepSean I have simplified the data. the actual data has years and it's a lot of data
– Rudra Roy
Nov 12 '18 at 20:24
1
Yes, there are cases where cursors are faster than any set-based query, but these are quite rare, and get rarer with each new release of SQL Server that introduces more operators and windowing expressions. SQL is primarily a "describe the desired result and I'll figure out the execution" language, not a "specify the exact operations yourself" language. This reason alone, that authors of both queries and the engine itself are encouraged to think in terms of sets, speaks against using cursors even in the rare cases where you can see a strategy that the engine cannot be compelled to undertake.
– Jeroen Mostert
Nov 12 '18 at 20:32
1
I actually would argue that the only time cursors should be used is if you need to call a system stored procedure (like db mail). Cursors can perform ok, but I've never found where they were faster than a set based approach.
– UnhandledExcepSean
Nov 12 '18 at 20:40
Personally, since there are only 12 lines/rows, I would simply copy/paste/alter the sql and run it.
– UnhandledExcepSean
Nov 12 '18 at 20:20
Personally, since there are only 12 lines/rows, I would simply copy/paste/alter the sql and run it.
– UnhandledExcepSean
Nov 12 '18 at 20:20
1
1
Big-O complexity is mostly irrelevant when we're talking cursors -- the reason they're to be avoided is because using them implies you're doing loops in horribly slow interpreted code, as opposed to basking in the goodness of compiled execution plans with operators that can employ parallelism, caching, readahead and all that beautiful stuff. Technically, both the cursor and the set-based equivalent are typically linear time (ignoring parallelism), but you still want the set-based equivalent. This kind of query (filling gaps) is typically handled with a numbers table or equivalent CTE.
– Jeroen Mostert
Nov 12 '18 at 20:21
Big-O complexity is mostly irrelevant when we're talking cursors -- the reason they're to be avoided is because using them implies you're doing loops in horribly slow interpreted code, as opposed to basking in the goodness of compiled execution plans with operators that can employ parallelism, caching, readahead and all that beautiful stuff. Technically, both the cursor and the set-based equivalent are typically linear time (ignoring parallelism), but you still want the set-based equivalent. This kind of query (filling gaps) is typically handled with a numbers table or equivalent CTE.
– Jeroen Mostert
Nov 12 '18 at 20:21
@UnhandledExcepSean I have simplified the data. the actual data has years and it's a lot of data
– Rudra Roy
Nov 12 '18 at 20:24
@UnhandledExcepSean I have simplified the data. the actual data has years and it's a lot of data
– Rudra Roy
Nov 12 '18 at 20:24
1
1
Yes, there are cases where cursors are faster than any set-based query, but these are quite rare, and get rarer with each new release of SQL Server that introduces more operators and windowing expressions. SQL is primarily a "describe the desired result and I'll figure out the execution" language, not a "specify the exact operations yourself" language. This reason alone, that authors of both queries and the engine itself are encouraged to think in terms of sets, speaks against using cursors even in the rare cases where you can see a strategy that the engine cannot be compelled to undertake.
– Jeroen Mostert
Nov 12 '18 at 20:32
Yes, there are cases where cursors are faster than any set-based query, but these are quite rare, and get rarer with each new release of SQL Server that introduces more operators and windowing expressions. SQL is primarily a "describe the desired result and I'll figure out the execution" language, not a "specify the exact operations yourself" language. This reason alone, that authors of both queries and the engine itself are encouraged to think in terms of sets, speaks against using cursors even in the rare cases where you can see a strategy that the engine cannot be compelled to undertake.
– Jeroen Mostert
Nov 12 '18 at 20:32
1
1
I actually would argue that the only time cursors should be used is if you need to call a system stored procedure (like db mail). Cursors can perform ok, but I've never found where they were faster than a set based approach.
– UnhandledExcepSean
Nov 12 '18 at 20:40
I actually would argue that the only time cursors should be used is if you need to call a system stored procedure (like db mail). Cursors can perform ok, but I've never found where they were faster than a set based approach.
– UnhandledExcepSean
Nov 12 '18 at 20:40
|
show 3 more comments
1 Answer
1
active
oldest
votes
2
The use of cursor isn't (primarily) bad because it has poor time complexity, but because it is more error-prone and harder to read than a simple query. You are correct that iterating over a table via cursor is O(n).
On to your problem at hand. If you have the months (1..12) stored somewhere, say Months, then you can do it like this:
WITH matchingMonths AS (
SELECT m.month, MAX(mav.month) as matchedMonth
FROM Months m, MonthsAndValues mav
WHERE m.month >= mav.month
GROUP BY m.month
)
SELECT mm.month, mav.value
FROM matchingMonths mm
JOIN MonthsAndValues mav on mav.month = mm.matchedMonth
Without such a table Months, you could generate it on-the-fly:
WITH Months(month) AS (
SELECT 1
UNION ALL
SELECT month + 1 FROM Months WHERE month < 12
),
matchingMonths AS (
SELECT m.month, MAX(mav.month) as matchedMonth
FROM Months m, MonthsAndValues mav
WHERE m.month >= mav.month
GROUP BY m.month
)
SELECT mm.month, mav.value
FROM matchingMonths mm
JOIN MonthsAndValues mav on mav.month = mm.matchedMonth
answered Nov 12 '18 at 20:27
Henning Koehler
1,129610
Since I am dealing with a lot of data, harder to read is a low priority concern. Considering my table is not indexed. What is the time complexity of the above ? (I am new to SQL and finding it hard to aggregate complexity as I see a Select, MAX and a Join)
– Rudra Roy
Nov 12 '18 at 20:40
1
Without indexes the complexity of this is quadratic (though the query optimizer might decide to create an index on-the-fly). So yes, this might turn out to be too slow - my suggestion would be to run it and check how fast it actually is.
– Henning Koehler
Nov 12 '18 at 20:43
1
@RudraRoy: more often than not you'll find that even in cases where the query is theoretically an O(n^2) operation due to lack of indexing or whatnot, it will still beat a cursor-based approach for realistic table sizes, simply because it executes that much faster (and can exploit parallelism). What you consider "a lot" of data and what SQL Server considers "a lot" can be quite different...
– Jeroen Mostert
Nov 12 '18 at 20:54
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
);
);
draft saved
draft discarded
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%2f53269371%2ftime-complexity-of-sql-cursor%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
2
The use of cursor isn't (primarily) bad because it has poor time complexity, but because it is more error-prone and harder to read than a simple query. You are correct that iterating over a table via cursor is O(n).
On to your problem at hand. If you have the months (1..12) stored somewhere, say Months, then you can do it like this:
WITH matchingMonths AS (
SELECT m.month, MAX(mav.month) as matchedMonth
FROM Months m, MonthsAndValues mav
WHERE m.month >= mav.month
GROUP BY m.month
)
SELECT mm.month, mav.value
FROM matchingMonths mm
JOIN MonthsAndValues mav on mav.month = mm.matchedMonth
Without such a table Months, you could generate it on-the-fly:
WITH Months(month) AS (
SELECT 1
UNION ALL
SELECT month + 1 FROM Months WHERE month < 12
),
matchingMonths AS (
SELECT m.month, MAX(mav.month) as matchedMonth
FROM Months m, MonthsAndValues mav
WHERE m.month >= mav.month
GROUP BY m.month
)
SELECT mm.month, mav.value
FROM matchingMonths mm
JOIN MonthsAndValues mav on mav.month = mm.matchedMonth
answered Nov 12 '18 at 20:27
Henning Koehler
1,129610
Since I am dealing with a lot of data, harder to read is a low priority concern. Considering my table is not indexed. What is the time complexity of the above ? (I am new to SQL and finding it hard to aggregate complexity as I see a Select, MAX and a Join)
– Rudra Roy
Nov 12 '18 at 20:40
1
Without indexes the complexity of this is quadratic (though the query optimizer might decide to create an index on-the-fly). So yes, this might turn out to be too slow - my suggestion would be to run it and check how fast it actually is.
– Henning Koehler
Nov 12 '18 at 20:43
1
@RudraRoy: more often than not you'll find that even in cases where the query is theoretically an O(n^2) operation due to lack of indexing or whatnot, it will still beat a cursor-based approach for realistic table sizes, simply because it executes that much faster (and can exploit parallelism). What you consider "a lot" of data and what SQL Server considers "a lot" can be quite different...
– Jeroen Mostert
Nov 12 '18 at 20:54
add a comment |
2
The use of cursor isn't (primarily) bad because it has poor time complexity, but because it is more error-prone and harder to read than a simple query. You are correct that iterating over a table via cursor is O(n).
On to your problem at hand. If you have the months (1..12) stored somewhere, say Months, then you can do it like this:
WITH matchingMonths AS (
SELECT m.month, MAX(mav.month) as matchedMonth
FROM Months m, MonthsAndValues mav
WHERE m.month >= mav.month
GROUP BY m.month
)
SELECT mm.month, mav.value
FROM matchingMonths mm
JOIN MonthsAndValues mav on mav.month = mm.matchedMonth
Without such a table Months, you could generate it on-the-fly:
WITH Months(month) AS (
SELECT 1
UNION ALL
SELECT month + 1 FROM Months WHERE month < 12
),
matchingMonths AS (
SELECT m.month, MAX(mav.month) as matchedMonth
FROM Months m, MonthsAndValues mav
WHERE m.month >= mav.month
GROUP BY m.month
)
SELECT mm.month, mav.value
FROM matchingMonths mm
JOIN MonthsAndValues mav on mav.month = mm.matchedMonth
answered Nov 12 '18 at 20:27
Henning Koehler
1,129610
Since I am dealing with a lot of data, harder to read is a low priority concern. Considering my table is not indexed. What is the time complexity of the above ? (I am new to SQL and finding it hard to aggregate complexity as I see a Select, MAX and a Join)
– Rudra Roy
Nov 12 '18 at 20:40
1
Without indexes the complexity of this is quadratic (though the query optimizer might decide to create an index on-the-fly). So yes, this might turn out to be too slow - my suggestion would be to run it and check how fast it actually is.
– Henning Koehler
Nov 12 '18 at 20:43
1
@RudraRoy: more often than not you'll find that even in cases where the query is theoretically an O(n^2) operation due to lack of indexing or whatnot, it will still beat a cursor-based approach for realistic table sizes, simply because it executes that much faster (and can exploit parallelism). What you consider "a lot" of data and what SQL Server considers "a lot" can be quite different...
– Jeroen Mostert
Nov 12 '18 at 20:54
add a comment |
2
2
2
The use of cursor isn't (primarily) bad because it has poor time complexity, but because it is more error-prone and harder to read than a simple query. You are correct that iterating over a table via cursor is O(n).
On to your problem at hand. If you have the months (1..12) stored somewhere, say Months, then you can do it like this:
WITH matchingMonths AS (
SELECT m.month, MAX(mav.month) as matchedMonth
FROM Months m, MonthsAndValues mav
WHERE m.month >= mav.month
GROUP BY m.month
)
SELECT mm.month, mav.value
FROM matchingMonths mm
JOIN MonthsAndValues mav on mav.month = mm.matchedMonth
Without such a table Months, you could generate it on-the-fly:
WITH Months(month) AS (
SELECT 1
UNION ALL
SELECT month + 1 FROM Months WHERE month < 12
),
matchingMonths AS (
SELECT m.month, MAX(mav.month) as matchedMonth
FROM Months m, MonthsAndValues mav
WHERE m.month >= mav.month
GROUP BY m.month
)
SELECT mm.month, mav.value
FROM matchingMonths mm
JOIN MonthsAndValues mav on mav.month = mm.matchedMonth
answered Nov 12 '18 at 20:27
Henning Koehler
1,129610
The use of cursor isn't (primarily) bad because it has poor time complexity, but because it is more error-prone and harder to read than a simple query. You are correct that iterating over a table via cursor is O(n).
On to your problem at hand. If you have the months (1..12) stored somewhere, say Months, then you can do it like this:
WITH matchingMonths AS (
SELECT m.month, MAX(mav.month) as matchedMonth
FROM Months m, MonthsAndValues mav
WHERE m.month >= mav.month
GROUP BY m.month
)
SELECT mm.month, mav.value
FROM matchingMonths mm
JOIN MonthsAndValues mav on mav.month = mm.matchedMonth
Without such a table Months, you could generate it on-the-fly:
WITH Months(month) AS (
SELECT 1
UNION ALL
SELECT month + 1 FROM Months WHERE month < 12
),
matchingMonths AS (
SELECT m.month, MAX(mav.month) as matchedMonth
FROM Months m, MonthsAndValues mav
WHERE m.month >= mav.month
GROUP BY m.month
)
SELECT mm.month, mav.value
FROM matchingMonths mm
JOIN MonthsAndValues mav on mav.month = mm.matchedMonth
answered Nov 12 '18 at 20:27
Henning Koehler
1,129610
edited Nov 12 '18 at 20:38
answered Nov 12 '18 at 20:27
Henning Koehler
1,129610
answered Nov 12 '18 at 20:27
Henning Koehler
1,129610
answered Nov 12 '18 at 20:27
Henning Koehler
1,129610
1,129610
Since I am dealing with a lot of data, harder to read is a low priority concern. Considering my table is not indexed. What is the time complexity of the above ? (I am new to SQL and finding it hard to aggregate complexity as I see a Select, MAX and a Join)
– Rudra Roy
Nov 12 '18 at 20:40
1
Without indexes the complexity of this is quadratic (though the query optimizer might decide to create an index on-the-fly). So yes, this might turn out to be too slow - my suggestion would be to run it and check how fast it actually is.
– Henning Koehler
Nov 12 '18 at 20:43
1
@RudraRoy: more often than not you'll find that even in cases where the query is theoretically an O(n^2) operation due to lack of indexing or whatnot, it will still beat a cursor-based approach for realistic table sizes, simply because it executes that much faster (and can exploit parallelism). What you consider "a lot" of data and what SQL Server considers "a lot" can be quite different...
– Jeroen Mostert
Nov 12 '18 at 20:54
add a comment |
Since I am dealing with a lot of data, harder to read is a low priority concern. Considering my table is not indexed. What is the time complexity of the above ? (I am new to SQL and finding it hard to aggregate complexity as I see a Select, MAX and a Join)
– Rudra Roy
Nov 12 '18 at 20:40
1
Without indexes the complexity of this is quadratic (though the query optimizer might decide to create an index on-the-fly). So yes, this might turn out to be too slow - my suggestion would be to run it and check how fast it actually is.
– Henning Koehler
Nov 12 '18 at 20:43
1
@RudraRoy: more often than not you'll find that even in cases where the query is theoretically an O(n^2) operation due to lack of indexing or whatnot, it will still beat a cursor-based approach for realistic table sizes, simply because it executes that much faster (and can exploit parallelism). What you consider "a lot" of data and what SQL Server considers "a lot" can be quite different...
– Jeroen Mostert
Nov 12 '18 at 20:54
Since I am dealing with a lot of data, harder to read is a low priority concern. Considering my table is not indexed. What is the time complexity of the above ? (I am new to SQL and finding it hard to aggregate complexity as I see a Select, MAX and a Join)
– Rudra Roy
Nov 12 '18 at 20:40
Since I am dealing with a lot of data, harder to read is a low priority concern. Considering my table is not indexed. What is the time complexity of the above ? (I am new to SQL and finding it hard to aggregate complexity as I see a Select, MAX and a Join)
– Rudra Roy
Nov 12 '18 at 20:40
1
1
Without indexes the complexity of this is quadratic (though the query optimizer might decide to create an index on-the-fly). So yes, this might turn out to be too slow - my suggestion would be to run it and check how fast it actually is.
– Henning Koehler
Nov 12 '18 at 20:43
Without indexes the complexity of this is quadratic (though the query optimizer might decide to create an index on-the-fly). So yes, this might turn out to be too slow - my suggestion would be to run it and check how fast it actually is.
– Henning Koehler
Nov 12 '18 at 20:43
1
1
@RudraRoy: more often than not you'll find that even in cases where the query is theoretically an O(n^2) operation due to lack of indexing or whatnot, it will still beat a cursor-based approach for realistic table sizes, simply because it executes that much faster (and can exploit parallelism). What you consider "a lot" of data and what SQL Server considers "a lot" can be quite different...
– Jeroen Mostert
Nov 12 '18 at 20:54
@RudraRoy: more often than not you'll find that even in cases where the query is theoretically an O(n^2) operation due to lack of indexing or whatnot, it will still beat a cursor-based approach for realistic table sizes, simply because it executes that much faster (and can exploit parallelism). What you consider "a lot" of data and what SQL Server considers "a lot" can be quite different...
– Jeroen Mostert
Nov 12 '18 at 20:54
add a comment |
draft saved
draft discarded
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
draft saved
draft discarded
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%2f53269371%2ftime-complexity-of-sql-cursor%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
Personally, since there are only 12 lines/rows, I would simply copy/paste/alter the sql and run it.
– UnhandledExcepSean
Nov 12 '18 at 20:20
1
Big-O complexity is mostly irrelevant when we're talking cursors -- the reason they're to be avoided is because using them implies you're doing loops in horribly slow interpreted code, as opposed to basking in the goodness of compiled execution plans with operators that can employ parallelism, caching, readahead and all that beautiful stuff. Technically, both the cursor and the set-based equivalent are typically linear time (ignoring parallelism), but you still want the set-based equivalent. This kind of query (filling gaps) is typically handled with a numbers table or equivalent CTE.
– Jeroen Mostert
Nov 12 '18 at 20:21
@UnhandledExcepSean I have simplified the data. the actual data has years and it's a lot of data
– Rudra Roy
Nov 12 '18 at 20:24
1
Yes, there are cases where cursors are faster than any set-based query, but these are quite rare, and get rarer with each new release of SQL Server that introduces more operators and windowing expressions. SQL is primarily a "describe the desired result and I'll figure out the execution" language, not a "specify the exact operations yourself" language. This reason alone, that authors of both queries and the engine itself are encouraged to think in terms of sets, speaks against using cursors even in the rare cases where you can see a strategy that the engine cannot be compelled to undertake.
– Jeroen Mostert
Nov 12 '18 at 20:32
1
I actually would argue that the only time cursors should be used is if you need to call a system stored procedure (like db mail). Cursors can perform ok, but I've never found where they were faster than a set based approach.
– UnhandledExcepSean
Nov 12 '18 at 20:40