Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Pivot table takes too long to run

Posted on 2005-04-28
5
Medium Priority
?
318 Views
Last Modified: 2008-03-06
This pivot table below takes 1 minute to run on my sql server for pulling out 50 records. I feel that too much time to get 50 records. Can anyone tell me what can be wrong?If I remove any of the code in it, it won't give me right results.

If I remove Top 50, this query is suppossed to return 1500 records, for which it takes 7 minutes. Is it common ?


SELECT TOP 50 company_name_english AS Companies,
Replace(1,1,(SELECT Totals FROM Total_By_Company_PreQuery c
      WHERE [Event_ID ] IN ( '132')
      AND a.company_name_english = c.company_name_english
      and c.forum_element = 'systems')) as [systems],
Replace(1,1,(SELECT Totals FROM Total_By_Company_PreQuery c
      WHERE [Event_ID ] IN ( '132')
      AND a.company_name_english = c.company_name_english
      and c.forum_element = 'combo')) as [combo],
Replace(1,1,(SELECT Totals FROM Total_By_Company_PreQuery c
      WHERE [Event_ID ] IN ( '132')
      AND a.company_name_english = c.company_name_english
      and c.forum_element = 'solutions')) as [solutions]
FROM  Total_By_Company_PreQuery a  
WHERE  [Event_ID ] IN ( '132')
AND (a.company_name_english  > ' ' )
GROUP BY a.company_name_english
ORDER BY a.company_name_english
0
Comment
Question by:AnuPutcha
  • 2
  • 2
5 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13891528
Please maintain your many open questions:
1 03/30/2005 125 Visual Studio.NET/ASP.NET -  Remote web ...  Open Active Server Pages (ASP)
2 03/30/2005 125 IIS Error  Open Internet Information Server
3 03/30/2005 125 Remote Database server  Open VB.NET
4 04/07/2005 125 ListBox Is not getting selected  Open Active Server Pages (ASP)
5 04/11/2005 500 Access is denied: 'Interop.Excel'.  Open .NET
6 04/11/2005 50 Uploading the Project files  Open .NET
7 04/12/2005 250 HTML overlap with button_click output  Open .NET
8 04/12/2005 250 HTML Overlap with button_click output  Open ASP.NET
9 04/22/2005 500 Multi-statement table-valued function   ...  Open Databases
10 04/25/2005 500 Problems uploading asp.net application  Open .NET
11 04/28/2005 250 Pivot table takes too long to run  Open Microsoft SQL Server
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 1000 total points
ID: 13891770
Try this query:

SELECT TOP 50 A.[Company_Name_English],
              SUM(Systems.Totals) AS Systems,
              SUM(Combo.Totals) AS Combo,
              SUM(Solutions.Totals) AS Solutions
FROM Total_By_Company_PreQuery A
     INNER JOIN
    (SELECT [Company_Name_English], [Totals] FROM Total_By_Company_PreQuery
     WHERE Event_ID IN ('132') AND Forum_Element = 'systems') Systems
          ON A.[Company_Name_English] = Systems.[Company_Name_English]
    (SELECT [Company_Name_English], [Totals] FROM Total_By_Company_PreQuery
     WHERE Event_ID IN ('132') AND Forum_Element = 'combo') Combo
          ON A.[Company_Name_English] = Combo.[Company_Name_English]
    (SELECT [Company_Name_English], [Totals] FROM Total_By_Company_PreQuery
     WHERE Event_ID IN ('132') AND Forum_Element = 'solutions') Solutions
          ON A.[Company_Name_English] = Solutions.[Company_Name_English]
WHERE A.[Event_ID] IN ('132') AND A.[Company_Name_English] > ' '
GROUP BY A.[Company_Name_English]
ORDER BY A.[Company_Name_English]
0
 

Author Comment

by:AnuPutcha
ID: 13896272
I just had to remove the SUM( ) function on system.total, combo.total & solution.total and it works like a charm in 2 sec's. You are a genius!!!!!!!!

Thanks,
Anu.
0
 

Author Comment

by:AnuPutcha
ID: 13896716
I don't know if I can go ahead and close my questions. But at this point all  my questions can be closed. I think so far I got answers to all my questions. If the admin wants to close my questions, please go ahead and do  that.

Anu.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13896830
>>If the admin wants to close my questions, please go ahead and do  that.<<
That is your responsibility.  Here's how:
What are my choices?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi67
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question