CODEWITHSUNDEEP
sql-serverperformancet-sql
voluminat0
voluminat0

Reputation: 906

Faster way to write specified query

So in our company we have a very large amount of articles. We have a customized search function to search in these articles, and here is a piece of code that generates the a part of the searchquery that needs optimalization.

        dtModif = dalDocType.FetchByAnyIdentifier(False, "+")
        sb.Append("(SELECT ( ")
        For Each row In dtModif.Rows
            sb.Append("ISNULL((SELECT -SUM(amount) ")
            sb.Append("FROM Sales." & row("TableName") & "Detail ")
            sb.Append("WHERE ArticleID = articles.Article.ArticleId ")
            sb.Append("AND DATEADD(Year,-1,GETDATE()) < Timestamp ")
            sb.Append("),0) ")
            sb.Append("+ ")
        Next

        dtModif = dalDocType.FetchByAnyIdentifier(False, "-")
        For Each row In dtModif.Rows
            sb.Append("ISNULL((SELECT +SUM(amount) ")
            sb.Append("FROM Sales." & row("TableName") & "Detail ")
            sb.Append("WHERE ArticleID = articles.Article.ArticleId ")
            sb.Append("AND DATEADD(Year,-1,GETDATE()) < Timestamp ")
            sb.Append("),0)")
            sb.Append("+ ")
        Next

        sb = sb.Remove(sb.Length - 2, 2)

        sb.Append(") )")

I get every article where the stockmodifier is positive (so more sold then returned), and I add the negative sum of all articles where the stockmodifier is negative. In that way I can get the number times that article is sold. (Can be negative).

I want to know now, is there a better way to structure this query? Because this is basically a SELECT over multiple SELECTS, so affects performance in large databases.

Thanks in advance.

Upvotes: 0

Views: 115

Answers (2)

Christian Phillips
Christian Phillips

Reputation: 18759

First thing to establish is where the bottleneck is coming from. So, run the query in SSMS and Include the Actual Execution Plan, which you will find here...

enter image description here

specifically, using this..

enter image description here.

Take a look at this article, Basic Analysis of Execution Plan, which will help you identify DB issues.

Once this is done, if you are still experiencing issues, then use a tool like Antz Profiler to profile the application. This will identify issues within the code, and assist in pinpointing lines of code which are heavily used or need refactoring.

If your code base looks OK, then take a look at any UI related performance issues, you might have 3rd party controls, or binding issues that could be hampering performance.

Also, consider the environment, are you running on a shared server? is the server heavily used etc?

Upvotes: 2

me1
me1

Reputation: 21

The first thing I would do is take the query and check its execution plan to see where performance degrades the most. Also, ensure you have indexes on the columns where you do your joins and where clauses.

Upvotes: 0

Related Questions