Is there any alternate way for pivot

Question

Is there any alternate way for pivot

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
September 26, 2014 at 1:42 pm

#296840

Is there any way i can change pivot to something else like case statement? Its taking too much time to retreive the results.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Did you actually read the comments I posted about performance I posted on your thread on this exact same topic from yesterday? Here is the link in case you forgot. http://www.sqlservercentral.com/Forums/Topic1619893-3387-1.aspx

There are some problems with performance in your code the pivot is the least of your worries. Not to mention that this huge pile of code is yet another subselect as made obvious by the fact that your code here has the remnants of joining this monstrosity to something else.

I mean no disrespect but you need to hire a consultant to help you sort this out. This looks like somebody throwing code after more code until something works. Then when the output is correct the performance is horrible. I for one am still not surprised that this moves slower than molasses on the tundra in January. Lots of nonSARGable predicates, repeatedly hitting the same base tables for slightly different criteria.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Yes, there is. you can read about that on the articles in Sean's signature.

However, I agree with what Sean has told you multiple times. You need to fix your non-SARGable code. Why are you changing all your dates to 4 days back? It makes no sense.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 3

mcfarlandparkway

SSCertifiable

Points: 7699

September 26, 2014 at 6:28 pm

#1748625

Thanks for the suggestions.....

LutzM SSC Guru Points: 107049 More actions · Answer 4

@mcfarlandparkway: I've seen you edited almost all your posts and replaced the original context with some rather meaningless information.

Please note that this is inappropriate on a forum since it places the replies/discussion out of context.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]