Nested Optimization

[Former Member]

So I am trying to optimize this query so it can run more efficient... I feel that the nested query is what is really dragging the time on. Any suggestions to optimize it?

  SELECT 
            CAST (f.pc_id AS INT) AS 'PC_ID'
           ,42 AS account_id
           ,CAST (CAST ([year] AS VARCHAR (4)) + RIGHT('0' + CAST (period AS VARCHAR (2)), 2) AS INT) AS period_pk
           ,CASE WHEN f.totalSales <> 0 
                    THEN (  SELECT   SUM(amount)
                            FROM     dbo.factActualData AS fa with (nolock)
                            WHERE   f.pc_id = fa.pc_id
                            AND f.year = fa.year
                            AND fa.period = fa.period 
                            AND fa.account_id in (8,34)) / f.totalSales 
                ELSE 0 
            END AS 'amount'
    FROM   dbo.factActualData AS f WITH (NOLOCK)
    inner join dbo.factActualData as f1 with (NOLOCK)

WHERE  (CAST (CAST ([year] AS VARCHAR (4)) + RIGHT('0' + CAST (period AS VARCHAR (2)), 2) AS INT))
     <> (SELECT FISCAL_PERIOD_PK FROM bs_tracking.CurrentFiscalPeriod WITH (NOLOCK))

[Breck_Carter]

Did you find a resolution? (the background nanny process called "Community" has popped this question to the top)

[Former Member]

What SQL Anywhere version are you running?

Can you post a graphical plan with statistics of the query, along with the CREATE TABLE statements for the 3 tables involved?

I also note you are using the "dbo" userid - this isn't recommended. The "dbo" user is used as a system userid in SQL Anywhere that has fewer restrictions than SYS.