Pivot table takes too long to run

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
AnuPutchaAsked:
Who is Participating?
 
rafranciscoCommented:
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
 
Anthony PerkinsCommented:
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
 
AnuPutchaAuthor Commented:
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
 
AnuPutchaAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.