Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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))

asked 30 Jun '10, 15:09

chipShot's gravatar image

chipShot
31111
accept rate: 0%

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

(29 Jul '10, 15:57) Breck Carter

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.

permanent link

answered 30 Jun '10, 16:35

Glenn%20Paulley's gravatar image

Glenn Paulley
10.8k577106
accept rate: 43%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×21
×17
×3

question asked: 30 Jun '10, 15:09

question was seen: 2,214 times

last updated: 11 Aug '10, 19:23