SQL Server : Select INTO From Pivot Running Slowly
0
SOLVED: I found the problem and was an easy fix. My own fault really, the logs for tempdb was at 99%, so I shrank them back down to 5MB and the query runs in seconds.
I've written a pivot to transpose a range of values into columns. When just running the query without inserting the data into any tables, it runs in just under 1 minute. When I add an INTO ##CandidateSummaryTable it takes ~2 hours and 48 minutes. I have no idea why.
The table contains about 1.4million records, I have put a clustered index on CandidateID, QuestionAnswerDate and Question which reduced the time significantly but not enough.
The estimated query plan suggests it would take even longer if I
INSERT INTO ##CandidateSumamryTable
SELECT ...*
instead so I've not run that yet.
The estimated query plan for the SELECT INTO ... puts the Table Insert at 98%.
Is there a better way to transpose this data?
This is the query:-
SELECT
CandidateID,
QuestionAnswerDate,
[Academic Title]
,[Address]
,[Address2]
,[Administrative Information Section]
,[AIM]
,[Alternative Email Address]
,[Assigned Location]
,[Availability?]
,[Availability]
,[Candidate Name Section]
,[CitizenOf]
,[City]
,[City2]
,[Class Rank (out of)]
,[Class Rank]
,[Comments]
,[Compensation]
,[Contact Information Section]
,[Contact Method (preferred)]
,[Contractor Information Section]
,[Contractor Rates]
,[Country]
,[Current]
,[Currently attending]
,[Custom Question]
,[Daily Rate Sort]
,[Data Privacy Statement]
,[Date of Birth]
,[Degree Date]
,[Degree Major Name]
,[Degree Measure]
,[Degree Name]
,[Degree Type]
,[Degree]
,[Department Name]
,[Department Type]
,[Description]
,[Education History Section]
,[Education History]
,[EducationCountry]
,[E-mail address]
,[Employee Code]
,[Employee Information Section]
,[Employer Organization Name]
,[Employer Organization Type]
,[Employer Organization]
,[Employment History Section]
,[Employment History]
,[End Date]
,[Ending Compensation Currency]
,[Ending Compensation Interval]
,[Ending Compensation]
,[Facebook]
,[FAX]
,[First Name]
,[Flickr]
,[Form of address]
,[GaduGadu]
,[Gender]
,[Google Talk]
,[ICQ]
,[Jabber]
,[Last Name]
,[Link To Pool Requision]
,[LinkedIn]
,[Manager Name]
,[Marital Status]
,[Middle Name]
,[MySpace]
,[Notice period]
,[Number of Children]
,[Orkut]
,[Other benefits]
,[Personal Information Section]
,[Personal Web Site]
,[Phone (home)]
,[Phone (mobile)]
,[Phone (work)]
,[Plaxo]
,[Position History]
,[Position Title]
,[Position Type]
,[Rate per day]
,[Reason for leaving]
,[Salary Sort]
,[School Name]
,[School or Institution]
,[School Type]
,[Skype]
,[Social networking and instant messaging accounts]
,[Sourcing And Referral]
,[Sourcing Medium]
,[SSN]
,[Start Date]
,[Sub-School / Department]
,[Tagged]
,[Twitter]
,[Viadeo]
,[Windows Live Messenger]
,[Windows Live Spaces]
,[XING]
,[Yahoo Messenger]
,[ZIP]
INTO
##CandidateSummaryTable
FROM
(SELECT CandidateID, Question, QuestionAnswerDate, Answer
FROM #CandidateSummary) d
PIVOT (MAX(Answer)
FOR Question IN ([Academic Title]
,[Address]
,[Address2]
,[Administrative Information Section]
,[AIM]
,[Alternative Email Address]
,[Assigned Location]
,[Availability?]
,[Availability]
,[Candidate Name Section]
,[CitizenOf]
,[City]
,[City2]
,[Class Rank (out of)]
,[Class Rank]
,[Comments]
,[Compensation]
,[Contact Information Section]
,[Contact Method (preferred)]
,[Contractor Information Section]
,[Contractor Rates]
,[Country]
,[Current]
,[Currently attending]
,[Custom Question]
,[Daily Rate Sort]
,[Data Privacy Statement]
,[Date of Birth]
,[Degree Date]
,[Degree Major Name]
,[Degree Measure]
,[Degree Name]
,[Degree Type]
,[Degree]
,[Department Name]
,[Department Type]
,[Description]
,[Education History Section]
,[Education History]
,[EducationCountry]
,[E-mail address]
,[Employee Code]
,[Employee Information Section]
,[Employer Organization Name]
,[Employer Organization Type]
,[Employer Organization]
,[Employment History Section]
,[Employment History]
,[End Date]
,[Ending Compensation Currency]
,[Ending Compensation Interval]
,[Ending Compensation]
,[Facebook]
,[FAX]
,[First Name]
,[Flickr]
,[Form of address]
,[GaduGadu]
,[Gender]
,[Google Talk]
,[ICQ]
,[Jabber]
,[Last Name]
,[Link To Pool Requision]
,[LinkedIn]
,[Manager Name]
,[Marital Status]
,[Middle Name]
,[MySpace]
,[Notice period]
,[Number of Children]
,[Orkut]
,[Other benefits]
,[Personal Information Section]
,[Personal Web Site]
,[Phone (home)]
,[Phone (mobile)]
,[Phone (work)]
,[Plaxo]
,[Position History]
,[Position Title]
,[Position Type]
,[Rate per day]
,[Reason for leaving]
,[Salary Sort]
,[School Name]
,[School or Institution]
,[School Type]
,[Skype]
,[Social networking and instant messaging accounts]
,[Sourcing And Referral]
,[Sourcing Medium]
,[SSN]
,[Start Date]
,[Sub-School / Department]
,[Tagged]
,[Twitter]
,[Viadeo]
,[Windows Live Messenger]
,[Windows Live Spaces]
,[XING]
,[Yahoo Messenger]
,[ZIP]
)) piv
sql-server pivot insert-into select-into asked Nov 14 '18 at 15:09
ChazChaz
12
add a comment |
0
SOLVED: I found the problem and was an easy fix. My own fault really, the logs for tempdb was at 99%, so I shrank them back down to 5MB and the query runs in seconds.
I've written a pivot to transpose a range of values into columns. When just running the query without inserting the data into any tables, it runs in just under 1 minute. When I add an INTO ##CandidateSummaryTable it takes ~2 hours and 48 minutes. I have no idea why.
The table contains about 1.4million records, I have put a clustered index on CandidateID, QuestionAnswerDate and Question which reduced the time significantly but not enough.
The estimated query plan suggests it would take even longer if I
INSERT INTO ##CandidateSumamryTable
SELECT ...*
instead so I've not run that yet.
The estimated query plan for the SELECT INTO ... puts the Table Insert at 98%.
Is there a better way to transpose this data?
This is the query:-
SELECT
CandidateID,
QuestionAnswerDate,
[Academic Title]
,[Address]
,[Address2]
,[Administrative Information Section]
,[AIM]
,[Alternative Email Address]
,[Assigned Location]
,[Availability?]
,[Availability]
,[Candidate Name Section]
,[CitizenOf]
,[City]
,[City2]
,[Class Rank (out of)]
,[Class Rank]
,[Comments]
,[Compensation]
,[Contact Information Section]
,[Contact Method (preferred)]
,[Contractor Information Section]
,[Contractor Rates]
,[Country]
,[Current]
,[Currently attending]
,[Custom Question]
,[Daily Rate Sort]
,[Data Privacy Statement]
,[Date of Birth]
,[Degree Date]
,[Degree Major Name]
,[Degree Measure]
,[Degree Name]
,[Degree Type]
,[Degree]
,[Department Name]
,[Department Type]
,[Description]
,[Education History Section]
,[Education History]
,[EducationCountry]
,[E-mail address]
,[Employee Code]
,[Employee Information Section]
,[Employer Organization Name]
,[Employer Organization Type]
,[Employer Organization]
,[Employment History Section]
,[Employment History]
,[End Date]
,[Ending Compensation Currency]
,[Ending Compensation Interval]
,[Ending Compensation]
,[Facebook]
,[FAX]
,[First Name]
,[Flickr]
,[Form of address]
,[GaduGadu]
,[Gender]
,[Google Talk]
,[ICQ]
,[Jabber]
,[Last Name]
,[Link To Pool Requision]
,[LinkedIn]
,[Manager Name]
,[Marital Status]
,[Middle Name]
,[MySpace]
,[Notice period]
,[Number of Children]
,[Orkut]
,[Other benefits]
,[Personal Information Section]
,[Personal Web Site]
,[Phone (home)]
,[Phone (mobile)]
,[Phone (work)]
,[Plaxo]
,[Position History]
,[Position Title]
,[Position Type]
,[Rate per day]
,[Reason for leaving]
,[Salary Sort]
,[School Name]
,[School or Institution]
,[School Type]
,[Skype]
,[Social networking and instant messaging accounts]
,[Sourcing And Referral]
,[Sourcing Medium]
,[SSN]
,[Start Date]
,[Sub-School / Department]
,[Tagged]
,[Twitter]
,[Viadeo]
,[Windows Live Messenger]
,[Windows Live Spaces]
,[XING]
,[Yahoo Messenger]
,[ZIP]
INTO
##CandidateSummaryTable
FROM
(SELECT CandidateID, Question, QuestionAnswerDate, Answer
FROM #CandidateSummary) d
PIVOT (MAX(Answer)
FOR Question IN ([Academic Title]
,[Address]
,[Address2]
,[Administrative Information Section]
,[AIM]
,[Alternative Email Address]
,[Assigned Location]
,[Availability?]
,[Availability]
,[Candidate Name Section]
,[CitizenOf]
,[City]
,[City2]
,[Class Rank (out of)]
,[Class Rank]
,[Comments]
,[Compensation]
,[Contact Information Section]
,[Contact Method (preferred)]
,[Contractor Information Section]
,[Contractor Rates]
,[Country]
,[Current]
,[Currently attending]
,[Custom Question]
,[Daily Rate Sort]
,[Data Privacy Statement]
,[Date of Birth]
,[Degree Date]
,[Degree Major Name]
,[Degree Measure]
,[Degree Name]
,[Degree Type]
,[Degree]
,[Department Name]
,[Department Type]
,[Description]
,[Education History Section]
,[Education History]
,[EducationCountry]
,[E-mail address]
,[Employee Code]
,[Employee Information Section]
,[Employer Organization Name]
,[Employer Organization Type]
,[Employer Organization]
,[Employment History Section]
,[Employment History]
,[End Date]
,[Ending Compensation Currency]
,[Ending Compensation Interval]
,[Ending Compensation]
,[Facebook]
,[FAX]
,[First Name]
,[Flickr]
,[Form of address]
,[GaduGadu]
,[Gender]
,[Google Talk]
,[ICQ]
,[Jabber]
,[Last Name]
,[Link To Pool Requision]
,[LinkedIn]
,[Manager Name]
,[Marital Status]
,[Middle Name]
,[MySpace]
,[Notice period]
,[Number of Children]
,[Orkut]
,[Other benefits]
,[Personal Information Section]
,[Personal Web Site]
,[Phone (home)]
,[Phone (mobile)]
,[Phone (work)]
,[Plaxo]
,[Position History]
,[Position Title]
,[Position Type]
,[Rate per day]
,[Reason for leaving]
,[Salary Sort]
,[School Name]
,[School or Institution]
,[School Type]
,[Skype]
,[Social networking and instant messaging accounts]
,[Sourcing And Referral]
,[Sourcing Medium]
,[SSN]
,[Start Date]
,[Sub-School / Department]
,[Tagged]
,[Twitter]
,[Viadeo]
,[Windows Live Messenger]
,[Windows Live Spaces]
,[XING]
,[Yahoo Messenger]
,[ZIP]
)) piv
asked Nov 14 '18 at 15:09
ChazChaz
12
for test purpose limit the data to say 1 month or 1 week and first do insert into temp table and then using the temp table to pivot data. Also create the table definition and actually declaring Primary key values on desired columns to better keep your data sorted in tempDB, BUT following the same route of first inserting data into temp table and then doing pivot.
– junketsu
Nov 14 '18 at 20:34
add a comment |
0
0
0
SOLVED: I found the problem and was an easy fix. My own fault really, the logs for tempdb was at 99%, so I shrank them back down to 5MB and the query runs in seconds.
I've written a pivot to transpose a range of values into columns. When just running the query without inserting the data into any tables, it runs in just under 1 minute. When I add an INTO ##CandidateSummaryTable it takes ~2 hours and 48 minutes. I have no idea why.
The table contains about 1.4million records, I have put a clustered index on CandidateID, QuestionAnswerDate and Question which reduced the time significantly but not enough.
The estimated query plan suggests it would take even longer if I
INSERT INTO ##CandidateSumamryTable
SELECT ...*
instead so I've not run that yet.
The estimated query plan for the SELECT INTO ... puts the Table Insert at 98%.
Is there a better way to transpose this data?
This is the query:-
SELECT
CandidateID,
QuestionAnswerDate,
[Academic Title]
,[Address]
,[Address2]
,[Administrative Information Section]
,[AIM]
,[Alternative Email Address]
,[Assigned Location]
,[Availability?]
,[Availability]
,[Candidate Name Section]
,[CitizenOf]
,[City]
,[City2]
,[Class Rank (out of)]
,[Class Rank]
,[Comments]
,[Compensation]
,[Contact Information Section]
,[Contact Method (preferred)]
,[Contractor Information Section]
,[Contractor Rates]
,[Country]
,[Current]
,[Currently attending]
,[Custom Question]
,[Daily Rate Sort]
,[Data Privacy Statement]
,[Date of Birth]
,[Degree Date]
,[Degree Major Name]
,[Degree Measure]
,[Degree Name]
,[Degree Type]
,[Degree]
,[Department Name]
,[Department Type]
,[Description]
,[Education History Section]
,[Education History]
,[EducationCountry]
,[E-mail address]
,[Employee Code]
,[Employee Information Section]
,[Employer Organization Name]
,[Employer Organization Type]
,[Employer Organization]
,[Employment History Section]
,[Employment History]
,[End Date]
,[Ending Compensation Currency]
,[Ending Compensation Interval]
,[Ending Compensation]
,[Facebook]
,[FAX]
,[First Name]
,[Flickr]
,[Form of address]
,[GaduGadu]
,[Gender]
,[Google Talk]
,[ICQ]
,[Jabber]
,[Last Name]
,[Link To Pool Requision]
,[LinkedIn]
,[Manager Name]
,[Marital Status]
,[Middle Name]
,[MySpace]
,[Notice period]
,[Number of Children]
,[Orkut]
,[Other benefits]
,[Personal Information Section]
,[Personal Web Site]
,[Phone (home)]
,[Phone (mobile)]
,[Phone (work)]
,[Plaxo]
,[Position History]
,[Position Title]
,[Position Type]
,[Rate per day]
,[Reason for leaving]
,[Salary Sort]
,[School Name]
,[School or Institution]
,[School Type]
,[Skype]
,[Social networking and instant messaging accounts]
,[Sourcing And Referral]
,[Sourcing Medium]
,[SSN]
,[Start Date]
,[Sub-School / Department]
,[Tagged]
,[Twitter]
,[Viadeo]
,[Windows Live Messenger]
,[Windows Live Spaces]
,[XING]
,[Yahoo Messenger]
,[ZIP]
INTO
##CandidateSummaryTable
FROM
(SELECT CandidateID, Question, QuestionAnswerDate, Answer
FROM #CandidateSummary) d
PIVOT (MAX(Answer)
FOR Question IN ([Academic Title]
,[Address]
,[Address2]
,[Administrative Information Section]
,[AIM]
,[Alternative Email Address]
,[Assigned Location]
,[Availability?]
,[Availability]
,[Candidate Name Section]
,[CitizenOf]
,[City]
,[City2]
,[Class Rank (out of)]
,[Class Rank]
,[Comments]
,[Compensation]
,[Contact Information Section]
,[Contact Method (preferred)]
,[Contractor Information Section]
,[Contractor Rates]
,[Country]
,[Current]
,[Currently attending]
,[Custom Question]
,[Daily Rate Sort]
,[Data Privacy Statement]
,[Date of Birth]
,[Degree Date]
,[Degree Major Name]
,[Degree Measure]
,[Degree Name]
,[Degree Type]
,[Degree]
,[Department Name]
,[Department Type]
,[Description]
,[Education History Section]
,[Education History]
,[EducationCountry]
,[E-mail address]
,[Employee Code]
,[Employee Information Section]
,[Employer Organization Name]
,[Employer Organization Type]
,[Employer Organization]
,[Employment History Section]
,[Employment History]
,[End Date]
,[Ending Compensation Currency]
,[Ending Compensation Interval]
,[Ending Compensation]
,[Facebook]
,[FAX]
,[First Name]
,[Flickr]
,[Form of address]
,[GaduGadu]
,[Gender]
,[Google Talk]
,[ICQ]
,[Jabber]
,[Last Name]
,[Link To Pool Requision]
,[LinkedIn]
,[Manager Name]
,[Marital Status]
,[Middle Name]
,[MySpace]
,[Notice period]
,[Number of Children]
,[Orkut]
,[Other benefits]
,[Personal Information Section]
,[Personal Web Site]
,[Phone (home)]
,[Phone (mobile)]
,[Phone (work)]
,[Plaxo]
,[Position History]
,[Position Title]
,[Position Type]
,[Rate per day]
,[Reason for leaving]
,[Salary Sort]
,[School Name]
,[School or Institution]
,[School Type]
,[Skype]
,[Social networking and instant messaging accounts]
,[Sourcing And Referral]
,[Sourcing Medium]
,[SSN]
,[Start Date]
,[Sub-School / Department]
,[Tagged]
,[Twitter]
,[Viadeo]
,[Windows Live Messenger]
,[Windows Live Spaces]
,[XING]
,[Yahoo Messenger]
,[ZIP]
)) piv
asked Nov 14 '18 at 15:09
ChazChaz
12
SOLVED: I found the problem and was an easy fix. My own fault really, the logs for tempdb was at 99%, so I shrank them back down to 5MB and the query runs in seconds.
I've written a pivot to transpose a range of values into columns. When just running the query without inserting the data into any tables, it runs in just under 1 minute. When I add an INTO ##CandidateSummaryTable it takes ~2 hours and 48 minutes. I have no idea why.
The table contains about 1.4million records, I have put a clustered index on CandidateID, QuestionAnswerDate and Question which reduced the time significantly but not enough.
The estimated query plan suggests it would take even longer if I
INSERT INTO ##CandidateSumamryTable
SELECT ...*
instead so I've not run that yet.
The estimated query plan for the SELECT INTO ... puts the Table Insert at 98%.
Is there a better way to transpose this data?
This is the query:-
SELECT
CandidateID,
QuestionAnswerDate,
[Academic Title]
,[Address]
,[Address2]
,[Administrative Information Section]
,[AIM]
,[Alternative Email Address]
,[Assigned Location]
,[Availability?]
,[Availability]
,[Candidate Name Section]
,[CitizenOf]
,[City]
,[City2]
,[Class Rank (out of)]
,[Class Rank]
,[Comments]
,[Compensation]
,[Contact Information Section]
,[Contact Method (preferred)]
,[Contractor Information Section]
,[Contractor Rates]
,[Country]
,[Current]
,[Currently attending]
,[Custom Question]
,[Daily Rate Sort]
,[Data Privacy Statement]
,[Date of Birth]
,[Degree Date]
,[Degree Major Name]
,[Degree Measure]
,[Degree Name]
,[Degree Type]
,[Degree]
,[Department Name]
,[Department Type]
,[Description]
,[Education History Section]
,[Education History]
,[EducationCountry]
,[E-mail address]
,[Employee Code]
,[Employee Information Section]
,[Employer Organization Name]
,[Employer Organization Type]
,[Employer Organization]
,[Employment History Section]
,[Employment History]
,[End Date]
,[Ending Compensation Currency]
,[Ending Compensation Interval]
,[Ending Compensation]
,[Facebook]
,[FAX]
,[First Name]
,[Flickr]
,[Form of address]
,[GaduGadu]
,[Gender]
,[Google Talk]
,[ICQ]
,[Jabber]
,[Last Name]
,[Link To Pool Requision]
,[LinkedIn]
,[Manager Name]
,[Marital Status]
,[Middle Name]
,[MySpace]
,[Notice period]
,[Number of Children]
,[Orkut]
,[Other benefits]
,[Personal Information Section]
,[Personal Web Site]
,[Phone (home)]
,[Phone (mobile)]
,[Phone (work)]
,[Plaxo]
,[Position History]
,[Position Title]
,[Position Type]
,[Rate per day]
,[Reason for leaving]
,[Salary Sort]
,[School Name]
,[School or Institution]
,[School Type]
,[Skype]
,[Social networking and instant messaging accounts]
,[Sourcing And Referral]
,[Sourcing Medium]
,[SSN]
,[Start Date]
,[Sub-School / Department]
,[Tagged]
,[Twitter]
,[Viadeo]
,[Windows Live Messenger]
,[Windows Live Spaces]
,[XING]
,[Yahoo Messenger]
,[ZIP]
INTO
##CandidateSummaryTable
FROM
(SELECT CandidateID, Question, QuestionAnswerDate, Answer
FROM #CandidateSummary) d
PIVOT (MAX(Answer)
FOR Question IN ([Academic Title]
,[Address]
,[Address2]
,[Administrative Information Section]
,[AIM]
,[Alternative Email Address]
,[Assigned Location]
,[Availability?]
,[Availability]
,[Candidate Name Section]
,[CitizenOf]
,[City]
,[City2]
,[Class Rank (out of)]
,[Class Rank]
,[Comments]
,[Compensation]
,[Contact Information Section]
,[Contact Method (preferred)]
,[Contractor Information Section]
,[Contractor Rates]
,[Country]
,[Current]
,[Currently attending]
,[Custom Question]
,[Daily Rate Sort]
,[Data Privacy Statement]
,[Date of Birth]
,[Degree Date]
,[Degree Major Name]
,[Degree Measure]
,[Degree Name]
,[Degree Type]
,[Degree]
,[Department Name]
,[Department Type]
,[Description]
,[Education History Section]
,[Education History]
,[EducationCountry]
,[E-mail address]
,[Employee Code]
,[Employee Information Section]
,[Employer Organization Name]
,[Employer Organization Type]
,[Employer Organization]
,[Employment History Section]
,[Employment History]
,[End Date]
,[Ending Compensation Currency]
,[Ending Compensation Interval]
,[Ending Compensation]
,[Facebook]
,[FAX]
,[First Name]
,[Flickr]
,[Form of address]
,[GaduGadu]
,[Gender]
,[Google Talk]
,[ICQ]
,[Jabber]
,[Last Name]
,[Link To Pool Requision]
,[LinkedIn]
,[Manager Name]
,[Marital Status]
,[Middle Name]
,[MySpace]
,[Notice period]
,[Number of Children]
,[Orkut]
,[Other benefits]
,[Personal Information Section]
,[Personal Web Site]
,[Phone (home)]
,[Phone (mobile)]
,[Phone (work)]
,[Plaxo]
,[Position History]
,[Position Title]
,[Position Type]
,[Rate per day]
,[Reason for leaving]
,[Salary Sort]
,[School Name]
,[School or Institution]
,[School Type]
,[Skype]
,[Social networking and instant messaging accounts]
,[Sourcing And Referral]
,[Sourcing Medium]
,[SSN]
,[Start Date]
,[Sub-School / Department]
,[Tagged]
,[Twitter]
,[Viadeo]
,[Windows Live Messenger]
,[Windows Live Spaces]
,[XING]
,[Yahoo Messenger]
,[ZIP]
)) piv
asked Nov 14 '18 at 15:09
ChazChaz
12
asked Nov 14 '18 at 15:09
ChazChaz
12
edited Nov 16 '18 at 14:18
Chaz
asked Nov 14 '18 at 15:09
ChazChaz
12
asked Nov 14 '18 at 15:09
ChazChaz
12
asked Nov 14 '18 at 15:09
ChazChaz
12
12
for test purpose limit the data to say 1 month or 1 week and first do insert into temp table and then using the temp table to pivot data. Also create the table definition and actually declaring Primary key values on desired columns to better keep your data sorted in tempDB, BUT following the same route of first inserting data into temp table and then doing pivot.
– junketsu
Nov 14 '18 at 20:34
add a comment |
for test purpose limit the data to say 1 month or 1 week and first do insert into temp table and then using the temp table to pivot data. Also create the table definition and actually declaring Primary key values on desired columns to better keep your data sorted in tempDB, BUT following the same route of first inserting data into temp table and then doing pivot.
– junketsu
Nov 14 '18 at 20:34
for test purpose limit the data to say 1 month or 1 week and first do insert into temp table and then using the temp table to pivot data. Also create the table definition and actually declaring Primary key values on desired columns to better keep your data sorted in tempDB, BUT following the same route of first inserting data into temp table and then doing pivot.
– junketsu
Nov 14 '18 at 20:34
for test purpose limit the data to say 1 month or 1 week and first do insert into temp table and then using the temp table to pivot data. Also create the table definition and actually declaring Primary key values on desired columns to better keep your data sorted in tempDB, BUT following the same route of first inserting data into temp table and then doing pivot.
– junketsu
Nov 14 '18 at 20:34
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
);
);
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%2f53303281%2fsql-server-select-into-from-pivot-running-slowly%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
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.
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%2f53303281%2fsql-server-select-into-from-pivot-running-slowly%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
for test purpose limit the data to say 1 month or 1 week and first do insert into temp table and then using the temp table to pivot data. Also create the table definition and actually declaring Primary key values on desired columns to better keep your data sorted in tempDB, BUT following the same route of first inserting data into temp table and then doing pivot.
– junketsu
Nov 14 '18 at 20:34