Any ideas to optimize this type of queris

Question

4.00/5 (2 votes)

See more:

, +

Hi,

In the following query, Table 1 is used many times in order to get accurate Summary data. However when the total records in Table 1 increased to 400K, the query is taking more than 15 seconds to execute.

Inviting your suggestion / view points to improve performance of this query.

SQL

Select 
(Select Count(Distinct [Table1].[BusinessUnit]) From [Table1] (nolock)) As [No. of Buss Unit],  
(Select Count(Distinct [Table1].[Segment]) From [Table1] (nolock)) As [No. of Segments],  
(Select Count(Distinct [Table1].[Vendor]) From [Table1] (nolock)) As [No. of Vendors],  
(Select Count(Distinct [Table1].[Payment]) From [Table1] (nolock)) As [No. of Payments],  
(Select Count(Distinct [Table1].[Payee]) From [Table1] (nolock)) As [No. of Payees],  
(Select Count(Distinct [Table1].[Country]) From [Table1] (nolock)) As [No. of Countries],  
Avg([Table1].[Amount]) As [Avg Payment Amount],  
Min([Table1].[Amount]) As [Min Payment Amount],  
Max([Table1].[Amount]) As [Max Payment Amount],  
Sum([Table1].[Amount]) As [Total Payment Amount],  
Min([Table1].[Date]) As [Min Date],  
Max([Table1].[Date]) As [Max Date],  
Min([Table1].[CPI]) As [Minimum CPI Score],  
Max([Table1].[CPI]) As [Maximum CPI Score] 

From [Table1] (nolock)

Posted 13-Aug-14 22:39pm

Liju Sankar

Add a Solution

3 solutions

Solution 1

Never easy: it's going to take some work.
This: http://www.anchor.com.au/hosting/dedicated/SQL_Query_Optimisation[^] provides good background, but it's not MsSql related (it's MySql/PostgreSQL) - the methods to do the analysis are different but the techniques are the same.

Google will also help: optimise+sql+query+performance[^] - but the actual work is going to be up to you, since you are the only one with access to the DB and SQL instance!

Posted 13-Aug-14 22:59pm

OriginalGriff

Comments

[no name] 25-Aug-14 4:29am

Thanks Griff, the approach shared by Jorgan helped me to avoid redundant sub queries. However i am not able improve much on performance side.

Let me try to think-of some indexed views for performance tuning.

do you have any other thoughts ?

Solution 2

Hi
If you are using same table then why should you used "from" clause every group.
Use from clause only at once and apply multiple group function in single query.

Thanks
Mangesh

Posted 14-Aug-14 1:58am

Mangesh4

Comments

[no name] 19-Aug-14 11:34am

Mangehs - I think it is not possible because each sub quires is reading distinct count, which can't be replaced with group by.

Could you please elaborate more ...

Mangesh4 21-Aug-14 6:58am

I am talking the way Jörgen Andersson replied

Mangesh4 21-Aug-14 7:03am

its window grouping function

[no name] 25-Aug-14 4:25am

thanks Mangehs

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Accepted Answer · 2014-08-14T10:45:00

The obvious solution would normally be the use of analytical functions to limit the number of table reads to just one.
But SQLServer does not support Count(Distinct foo) over (Partition By Bar Order by Foobar)

Luckily there's a workaround:

SQL

SELECT  
        DENSE_RANK() over (order by [BusinessUnit] ASC) + DENSE_RANK() over (order by [BusinessUnit] DESC) - 1 As [No. of Buss Unit],  
        DENSE_RANK() over (order by [Segment] ASC) + DENSE_RANK() over (order by [Segment] DESC) - 1 As [No. of Segments],  
        DENSE_RANK() over (order by [Vendor] ASC) + DENSE_RANK() over (order by [Vendor] DESC) - 1 As [No. of Vendors],  
        DENSE_RANK() over (order by [Payment] ASC) + DENSE_RANK() over (order by [Payment] DESC) - 1 As [No. of Payments],  
        DENSE_RANK() over (order by [Payee] ASC) + DENSE_RANK() over (order by [Payee] DESC) - 1 As [No. of Payees],  
        DENSE_RANK() over (order by [Country] ASC) + DENSE_RANK() over (order by [Country] DESC) - 1 As [No. of Countries],  
        Avg([Amount]) As [Avg Payment Amount],
        Min([Amount]) As [Min Payment Amount],  
        Max([Amount]) As [Max Payment Amount],  
        Sum([Amount]) As [Total Payment Amount],  
        Min([Date]) As [Min Date],  
        Max([Date]) As [Max Date],  
        Min([CPI]) As [Minimum CPI Score],  
        Max([CPI]) As [Maximum CPI Score]
FROM    [Table1]