I would start by trying to replace

IF 0 < (SELECT COUNT(*)

with

IF EXISTS (SELECT 1

 

Next check your statistics.  the massive disparity between estimated and actual...

So, based on your sample data, what is your expected result?

Try

FOR stateID IN ( [36], [11] )

Because the logical query processing order will process the join before the where, it MIGHT improve performance slightly if you search for the Commodities before searching for the Suppliers.

            
November 11, 2019 at 1:14 pm
            
#3696695

            
            
                                                
        

I would start by using an INNER JOIN, rather than a sub-query

SELECT      cs.SuppllierID
FROM        CommoditySupplier AS...

As a general rule of thumb, you want to avoid looping.

So, I would be looking to rewrite the proc that is being called from the loop to process the data...

If you have the value of the SetOptions, this will identify what options are ON|OFF

DECLARE @options int = 4347;

SELECT so.SetOption
     , OptionState =...

Hye all,

My below T-SQL is that accurate calculation?

SELECT  
get_customeVal_3 = ISNULL( SUM( CASE WHEN year_num = 2015 AND chart_code <> '36101' AND chart_code <> '39102'...

Something like this?

DECLARE @batch_Id uniqueidentifier = '4245a9c9-4e34-4178-94f9-002a0fe7846b';

DECLARE @year1 int = 2014; /*current year*/
DECLARE @year2 int = 2013; /*last year*/

WITH cteBase AS (
    SELECT ...

Although Jonathan's solution works for the given sample data, I believe that this is actually an ISLANDS problem.

Unfortunately, I don't have the time to put together a solution at this...

The syntax appears to be correct

What didn't work?  How are we supposed to help you debug when you don't give us anything to work with?

Perhaps you are looking for the machine name, and not the...

Show us what you have tried, and what you are trying to achieve.

Then we can assist you to correct any errors in the code.

You would get much better performance by creating an inline Table Valued Valued Function (iTVF).

Something like this

CREATE FUNCTION dbo.fnPadNum (
    @Num   ...

I have used the following pattern for similar requirements

CREATE PROCEDURE Foo
    @FilterCondition ....
  , @PageNum      int = 1
...

• This reply was modified 5 years, 11 months ago by DesNorton.