-1

Tables:
Sales Quantity Daily Data
Stock Quantity Weekly Data

Visual Data Display

I want both tables to be displayed (even when stock is empty) as long as there's daily data, so I used a left join.

SELECT dbo_EPOS_Daily_split.EndDay, dbo_EPOS_Daily_split.Item_Code, dbo_EPOS_Daily_split.SalesUnitsUK, [dbo_C_ Stock].Total_Units, [dbo_C_ Stock].STOCK_DATE
FROM dbo_EPOS_Daily_split LEFT JOIN [dbo_C_ Stock] 
ON (dbo_EPOS_Daily_split.Item_Code = [dbo_C_ Stock].Material) 
    AND (dbo_EPOS_Daily_split.EndDay = [dbo_C_ Stock].STOCK_DATE)
WHERE (((dbo_EPOS_Daily_split.EndDay)>=#1/1/2022# And (dbo_EPOS_Daily_split.EndDay)<=#2/1/2022#) 
    AND ((dbo_EPOS_Daily_split.Item_Code)="8830231"))
ORDER BY dbo_EPOS_Daily_split.EndDay DESC;

This takes 2-3 minutes.

I can't modify or index the tables.

How do I speed it up?

philipxy
  • 14,867
  • 6
  • 39
  • 83
SeekingAssistance97
  • 1
  • I don't think will speed up but more compact code: `dbo_EPOS_Daily_split.EndDay BETWEEN #1/1/2022# AND #2/1/2022#`. – June7 Mar 12 '23 at 13:48
  • [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [mre] [ask] [Help] – philipxy Mar 12 '23 at 23:10
  • [Tips for asking a good SQL question](https://meta.stackoverflow.com/q/271055/3404097) [Asking query performance questions](https://dba.meta.stackexchange.com/q/3034/43932) [How to Optimize Queries in a Database - The Basics](https://stackoverflow.com/q/3191623/3404097) [What are your most common sql optimizations?](https://stackoverflow.com/q/1332778/3404097) [When and why are database joins expensive?](https://stackoverflow.com/q/173726/3404097) [Re SQL "performance".](https://stackoverflow.com/a/24196511/3404097) – philipxy Mar 12 '23 at 23:16
  • Good style is to use a tiny mnemonic table alias with every column of a joined table. – philipxy Mar 12 '23 at 23:20
  • @June7 Please don't put inappropriate images inline, tell a poster to use text for text & a [mre] for debug questions. – philipxy Mar 12 '23 at 23:31
  • I usually do make that request but they already had a good answer posted so abandoned. – June7 Mar 12 '23 at 23:53

1 Answers1

1

Make sure you have an index on each of these fields:

dbo_EPOS_Daily_split.Item_Code
dbo_EPOS_Daily_split.EndDay
[dbo_C_ Stock].Material
[dbo_C_ Stock].STOCK_DATE

Run it server-side as a Pass-Through query using T-SQL syntax:

SELECT 
    dbo.EPOS_Daily_split.EndDay, 
    dbo.EPOS_Daily_split.Item_Code, 
    dbo.EPOS_Daily_split.SalesUnitsUK, 
    dbo.[C_ Stock].Total_Units, 
    dbo.[C_ Stock].STOCK_DATE
FROM 
    dbo.EPOS_Daily_split 
LEFT JOIN 
    dbo.[C_ Stock] ON
        (dbo.EPOS_Daily_split.Item_Code = dbo.[C_ Stock].Material) AND
        (dbo.EPOS_Daily_split.EndDay = dbo.[C_ Stock].STOCK_DATE)
WHERE 
    dbo.EPOS_Daily_split.EndDay >= '1/1/2022' AND 
    dbo.EPOS_Daily_split.EndDay <= '2/1/2022' AND 
    dbo.EPOS_Daily_split.Item_Code = '8830231'
ORDER BY 
    dbo.EPOS_Daily_split.EndDay DESC;
Gustav
  • 53,498
  • 7
  • 29
  • 55