SQL Server is taking too long to execute for a small table?2019 Community Moderator ElectionAdd a column with a default value to an existing table in SQL ServerHow to return only the Date from a SQL Server DateTime datatypeHow to check if a column exists in a SQL Server table?Check if table exists in SQL ServerHow to concatenate text from multiple rows into a single text string in SQL server?LEFT JOIN vs. LEFT OUTER JOIN in SQL ServerHow do I UPDATE from a SELECT in SQL Server?Finding duplicate values in a SQL tableFind all tables containing column with specified name - MS SQL ServerHow to Delete using INNER JOIN with SQL Server?
Do people actually use the word "kaputt" in conversation?
Unable to get newly inserted Product's Id using After Plugin for Catalog Product save controller method
Weird lines in Microsoft Word
How do you justify more code being written by following clean code practices?
I got the following comment from a reputed math journal. What does it mean?
Asserting that Atheism and Theism are both faith based positions
Writing in a Christian voice
Is this Pascal's Matrix?
Symbolism of 18 Journeyers
Unfrosted light bulb
Isn't the word "experience" wrongly used in this context?
label a part of commutative diagram
Turning a hard to access nut?
What are the rules for concealing thieves' tools (or items in general)?
Is VPN a layer 3 concept?
Should I be concerned about student access to a test bank?
PTIJ: Where did Achashverosh's years wander off to?
Why is there so much iron?
Does fire aspect on a sword, destroy mob drops?
Why doesn't the fusion process of the sun speed up?
Hot air balloons as primitive bombers
Did Nintendo change its mind about 68000 SNES?
How do researchers send unsolicited emails asking for feedback on their works?
Gauss brackets with double vertical lines
SQL Server is taking too long to execute for a small table?
2019 Community Moderator ElectionAdd a column with a default value to an existing table in SQL ServerHow to return only the Date from a SQL Server DateTime datatypeHow to check if a column exists in a SQL Server table?Check if table exists in SQL ServerHow to concatenate text from multiple rows into a single text string in SQL server?LEFT JOIN vs. LEFT OUTER JOIN in SQL ServerHow do I UPDATE from a SELECT in SQL Server?Finding duplicate values in a SQL tableFind all tables containing column with specified name - MS SQL ServerHow to Delete using INNER JOIN with SQL Server?
I'm trying to remove non-alphanumeric from two columns and output it into a brand new table. I did this last time and it executed in about 30 minutes. The table only contains about 3000 rows and I'm connecting to a remote server, and I'm not sure what is the problem here. Please note that I have no permission to create function nor procedure.
Here is my code:
SELECT
[Customer ID], [Original Product Title], [Original Product Type],
[New_Product_Title], [New_Product_Type]
INTO
Customer_Product_2
FROM
Customer_Product
WHILE @@ROWCOUNT > 0
UPDATE Customer_Product
SET New_Product_Title = REPLACE(New_Product_Title, SUBSTRING(New_Product_Title, PATINDEX('%[^a-z0-9]%', New_Product_Title), 1), ''),
New_Product_Type = REPLACE(New_Product_Type, SUBSTRING(New_Product_Type, PATINDEX('%[^a-z0-9]%', New_Product_Type), 1), '')
sql
sql-server edited Mar 7 at 20:04
marc_s
581k13011221268
asked Mar 7 at 19:05
cheklapkokcheklapkok
485
|
show 1 more comment
I'm trying to remove non-alphanumeric from two columns and output it into a brand new table. I did this last time and it executed in about 30 minutes. The table only contains about 3000 rows and I'm connecting to a remote server, and I'm not sure what is the problem here. Please note that I have no permission to create function nor procedure.
Here is my code:
SELECT
[Customer ID], [Original Product Title], [Original Product Type],
[New_Product_Title], [New_Product_Type]
INTO
Customer_Product_2
FROM
Customer_Product
WHILE @@ROWCOUNT > 0
UPDATE Customer_Product
SET New_Product_Title = REPLACE(New_Product_Title, SUBSTRING(New_Product_Title, PATINDEX('%[^a-z0-9]%', New_Product_Title), 1), ''),
New_Product_Type = REPLACE(New_Product_Type, SUBSTRING(New_Product_Type, PATINDEX('%[^a-z0-9]%', New_Product_Type), 1), '')
sql
sql-server edited Mar 7 at 20:04
marc_s
581k13011221268
asked Mar 7 at 19:05
cheklapkokcheklapkok
485
1
"I'm connected to a remove server". That sounds like a performance killer. I would suggest that you ask another question (and delete this one). That question should include sample data, desired results, and an explanation of what you are trying to do.
– Gordon Linoff
Mar 7 at 19:07
It could be. It finished after about 30 minutes when I ran it initially, but I forgot to add something to the step before so I have to rerun this query. I left it running last night and when I looked at it this morning, it was still running after 17 hours. That's insane.
– cheklapkok
Mar 7 at 19:09
1
your loops could be infinite... not the best way to update
– scsimon
Mar 7 at 19:13
1
Is that all of your code? If you're updating without aWhereclause, then just do the update without theWhileloop.
– WEI_DBA
Mar 7 at 19:22
@WEI_DBA WOW, awesome, it finished instantly. Is it because of the loop?
– cheklapkok
Mar 7 at 19:23
|
show 1 more comment
I'm trying to remove non-alphanumeric from two columns and output it into a brand new table. I did this last time and it executed in about 30 minutes. The table only contains about 3000 rows and I'm connecting to a remote server, and I'm not sure what is the problem here. Please note that I have no permission to create function nor procedure.
Here is my code:
SELECT
[Customer ID], [Original Product Title], [Original Product Type],
[New_Product_Title], [New_Product_Type]
INTO
Customer_Product_2
FROM
Customer_Product
WHILE @@ROWCOUNT > 0
UPDATE Customer_Product
SET New_Product_Title = REPLACE(New_Product_Title, SUBSTRING(New_Product_Title, PATINDEX('%[^a-z0-9]%', New_Product_Title), 1), ''),
New_Product_Type = REPLACE(New_Product_Type, SUBSTRING(New_Product_Type, PATINDEX('%[^a-z0-9]%', New_Product_Type), 1), '')
sql
sql-server edited Mar 7 at 20:04
marc_s
581k13011221268
asked Mar 7 at 19:05
cheklapkokcheklapkok
485
I'm trying to remove non-alphanumeric from two columns and output it into a brand new table. I did this last time and it executed in about 30 minutes. The table only contains about 3000 rows and I'm connecting to a remote server, and I'm not sure what is the problem here. Please note that I have no permission to create function nor procedure.
Here is my code:
SELECT
[Customer ID], [Original Product Title], [Original Product Type],
[New_Product_Title], [New_Product_Type]
INTO
Customer_Product_2
FROM
Customer_Product
WHILE @@ROWCOUNT > 0
UPDATE Customer_Product
SET New_Product_Title = REPLACE(New_Product_Title, SUBSTRING(New_Product_Title, PATINDEX('%[^a-z0-9]%', New_Product_Title), 1), ''),
New_Product_Type = REPLACE(New_Product_Type, SUBSTRING(New_Product_Type, PATINDEX('%[^a-z0-9]%', New_Product_Type), 1), '')
sql
sql-server sql
sql-server edited Mar 7 at 20:04
marc_s
581k13011221268
asked Mar 7 at 19:05
cheklapkokcheklapkok
485
edited Mar 7 at 20:04
marc_s
581k13011221268
asked Mar 7 at 19:05
cheklapkokcheklapkok
485
edited Mar 7 at 20:04
marc_s
581k13011221268
edited Mar 7 at 20:04
marc_s
581k13011221268
edited Mar 7 at 20:04
marc_s
581k13011221268
581k13011221268
asked Mar 7 at 19:05
cheklapkokcheklapkok
485
asked Mar 7 at 19:05
cheklapkokcheklapkok
485
asked Mar 7 at 19:05
cheklapkokcheklapkok
485
485
1
"I'm connected to a remove server". That sounds like a performance killer. I would suggest that you ask another question (and delete this one). That question should include sample data, desired results, and an explanation of what you are trying to do.
– Gordon Linoff
Mar 7 at 19:07
It could be. It finished after about 30 minutes when I ran it initially, but I forgot to add something to the step before so I have to rerun this query. I left it running last night and when I looked at it this morning, it was still running after 17 hours. That's insane.
– cheklapkok
Mar 7 at 19:09
1
your loops could be infinite... not the best way to update
– scsimon
Mar 7 at 19:13
1
Is that all of your code? If you're updating without aWhereclause, then just do the update without theWhileloop.
– WEI_DBA
Mar 7 at 19:22
@WEI_DBA WOW, awesome, it finished instantly. Is it because of the loop?
– cheklapkok
Mar 7 at 19:23
|
show 1 more comment
1
"I'm connected to a remove server". That sounds like a performance killer. I would suggest that you ask another question (and delete this one). That question should include sample data, desired results, and an explanation of what you are trying to do.
– Gordon Linoff
Mar 7 at 19:07
It could be. It finished after about 30 minutes when I ran it initially, but I forgot to add something to the step before so I have to rerun this query. I left it running last night and when I looked at it this morning, it was still running after 17 hours. That's insane.
– cheklapkok
Mar 7 at 19:09
1
your loops could be infinite... not the best way to update
– scsimon
Mar 7 at 19:13
1
Is that all of your code? If you're updating without aWhereclause, then just do the update without theWhileloop.
– WEI_DBA
Mar 7 at 19:22
@WEI_DBA WOW, awesome, it finished instantly. Is it because of the loop?
– cheklapkok
Mar 7 at 19:23
1
1
"I'm connected to a remove server". That sounds like a performance killer. I would suggest that you ask another question (and delete this one). That question should include sample data, desired results, and an explanation of what you are trying to do.
– Gordon Linoff
Mar 7 at 19:07
"I'm connected to a remove server". That sounds like a performance killer. I would suggest that you ask another question (and delete this one). That question should include sample data, desired results, and an explanation of what you are trying to do.
– Gordon Linoff
Mar 7 at 19:07
It could be. It finished after about 30 minutes when I ran it initially, but I forgot to add something to the step before so I have to rerun this query. I left it running last night and when I looked at it this morning, it was still running after 17 hours. That's insane.
– cheklapkok
Mar 7 at 19:09
It could be. It finished after about 30 minutes when I ran it initially, but I forgot to add something to the step before so I have to rerun this query. I left it running last night and when I looked at it this morning, it was still running after 17 hours. That's insane.
– cheklapkok
Mar 7 at 19:09
1
1
your loops could be infinite... not the best way to update
– scsimon
Mar 7 at 19:13
your loops could be infinite... not the best way to update
– scsimon
Mar 7 at 19:13
1
1
Is that all of your code? If you're updating without a
Where clause, then just do the update without the While loop.– WEI_DBA
Mar 7 at 19:22
Is that all of your code? If you're updating without a
Where clause, then just do the update without the While loop.– WEI_DBA
Mar 7 at 19:22
@WEI_DBA WOW, awesome, it finished instantly. Is it because of the loop?
– cheklapkok
Mar 7 at 19:23
@WEI_DBA WOW, awesome, it finished instantly. Is it because of the loop?
– cheklapkok
Mar 7 at 19:23
|
show 1 more comment
1 Answer
1
active
oldest
votes
You don't need the loop since you don't need to check if @@ROWCOUNT > 0 at all. An UPDATE statement will run on the set you give it, if no rows are in the set then no data will be updated.
answered Mar 7 at 20:59
elizabkelizabk
25519
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%2f55051098%2fsql-server-is-taking-too-long-to-execute-for-a-small-table%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
You don't need the loop since you don't need to check if @@ROWCOUNT > 0 at all. An UPDATE statement will run on the set you give it, if no rows are in the set then no data will be updated.
answered Mar 7 at 20:59
elizabkelizabk
25519
add a comment |
You don't need the loop since you don't need to check if @@ROWCOUNT > 0 at all. An UPDATE statement will run on the set you give it, if no rows are in the set then no data will be updated.
answered Mar 7 at 20:59
elizabkelizabk
25519
add a comment |
You don't need the loop since you don't need to check if @@ROWCOUNT > 0 at all. An UPDATE statement will run on the set you give it, if no rows are in the set then no data will be updated.
answered Mar 7 at 20:59
elizabkelizabk
25519
You don't need the loop since you don't need to check if @@ROWCOUNT > 0 at all. An UPDATE statement will run on the set you give it, if no rows are in the set then no data will be updated.
answered Mar 7 at 20:59
elizabkelizabk
25519
answered Mar 7 at 20:59
elizabkelizabk
25519
answered Mar 7 at 20:59
elizabkelizabk
25519
answered Mar 7 at 20:59
elizabkelizabk
25519
25519
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%2f55051098%2fsql-server-is-taking-too-long-to-execute-for-a-small-table%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'm connected to a remove server". That sounds like a performance killer. I would suggest that you ask another question (and delete this one). That question should include sample data, desired results, and an explanation of what you are trying to do.
– Gordon Linoff
Mar 7 at 19:07
It could be. It finished after about 30 minutes when I ran it initially, but I forgot to add something to the step before so I have to rerun this query. I left it running last night and when I looked at it this morning, it was still running after 17 hours. That's insane.
– cheklapkok
Mar 7 at 19:09
1
your loops could be infinite... not the best way to update
– scsimon
Mar 7 at 19:13
1
Is that all of your code? If you're updating without a
Whereclause, then just do the update without theWhileloop.– WEI_DBA
Mar 7 at 19:22
@WEI_DBA WOW, awesome, it finished instantly. Is it because of the loop?
– cheklapkok
Mar 7 at 19:23