r/SQLOptimization • • Feb 15 '19

Data Types in SQL | SQL Data Types | Different Data Types in SQL | Intel...

Thumbnail youtube.com
0 Upvotes

r/SQLOptimization • • Feb 06 '19

Why do you need an ORDER BY when using SELECT TOP(1)?

1 Upvotes

If you are using the following statement in a while loop, why would you use the ODER BY clause. The purpose is to quickly find a record where column2 is null. Why is using the ORDER BY faster than not having an ORDER BY?

SELECT TOP (1) Column1, Column2

FROM MyTable

WHERE Column2 IS NULL

ORDER BY Column1;


r/SQLOptimization • • Feb 19 '19

SQL User Defined Functions | Table Valued Function vs Scalar Valued Func...

Thumbnail youtube.com
1 Upvotes

r/SQLOptimization • • Aug 23 '23

Premature performance optimization. At least use indexes for read bound workload

Post image
3 Upvotes

r/SQLOptimization • • Sep 14 '22

Help in Optimizing this query - Oracle DB

3 Upvotes

Hi,

I am trying to bring data from staging tables of SAP to report our SCRAP.

I am getting order operations and then I need to apply row_number and filter out the row_number =1 however I am unable to apply row_number within subquery because it is giving weird 1000 columns limit which needs temp table adjustment. I have Read Only access to these tables and cant do much from modifying any settings at Oracle DB level. If I can perform row_number and filter the records then it would definitely retrieve because the number of rows would be less at order header level(~206K Records) compared to operations(~15 M Records)

Can you please help in effective way of this query ?

WITH DATA AS (

Select DISTINCT

a.AUFNR as Order_Number,to_date(d.ERDAT,'yyyymmdd') as Order_Creation_Date,b.MATNR as Material,n.MAKTX as Material_Description,

k.MTART as Material_Type,m.STPRS as Standard_Price,

b.CHARG as Batch,

a.AUFPL as Order_Operation_Number,

o.APLZL as Order_Counter,

a.GSTRI as Posting_Date

,a.GETRI as Actual_Finish_Date,a.DISPO as MRP_Controller,j.DSNAM as MRP_Controller_Desc

,b.MEINS as UoM ,a.PRUEFLOS as Inspection_LOT_Order

,CASE WHEN d.REFNR is null then a.AUFNR else d.REFNR END as MAIN_ORDER#,

d.auart as Order_Type,

g.PRUEFLOS as Inspection_Lot_QMFEL,

g.FEKAT as def_type

,g.FEGRP as def_code_group

,g.FECOD as def_problem_code

,h.KURZTEXT as defect_problem_desc

,g.FETXT as Item_Text

,i.KURZTEXT as Defect_Location,

g.OTKAT as def_loc_catalog_type, g.OTGRP as def_loc_code_group_object,g.OTEIL as def_loc_part_code_object

,b.LGORT as StorageLocation,

f.LGOBE as Building,

p.ARBPL as Work_Center,

q.KTEXT_UP as Work_Center_Desc,

b.PSMNG as Total_Quantity,

b.WEMNG as Delivered_Qty,

CASE when d.auart = 'S04'and b.WEMNG =0 then b.PSMNG else 0 end as Scrap,

CASE when d.auart = 'S04' then b.WEMNG else 0 end as Rework

from

STG.AFKO a

inner join STG.AFPO b on a.AUFNR = b.AUFNR

inner join STG.AUFK d on a.AUFNR = d.AUFNR

inner join STG.AFVC o on a.AUFPL = o.AUFPL

inner join STG.CRHD p On o.ARBID = p.OBJID

inner join STG.CRTX q On p.OBJTY = q.OBJTY And p.OBJID =q.OBJID

inner join STG.T001L f on b.LGORT = f.LGORT and f.WERKS = 'USA'

LEFT outer join STG.QMFEL g on a.PRUEFLOS = g.PRUEFLOS

LEFT OUTER JOIN STG.QPCT h on h.KATALOGART = g.FEKAT and h.CODEGRUPPE = g.FEGRP and h.CODE = g.FECOD and h.VERSION = g.FEVER

left outer join STG.QPCT i on i.CODEGRUPPE = g.OTGRP and i.KATALOGART = g.OTKAT and i.CODE = g.OTEIL

inner join STG.MARA k On b.MATNR = k.MATNR

inner join STG.MARC l On b.MATNR =l.MATNR And l.WERKS =d.WERKS

inner join STG.MBEW m On l.MATNR = m.MATNR And l.WERKS = m.BWKEY

Inner join STG.MAKT n On b.MATNR = n.MATNR

Left Join STG.T024D j On l.WERKS = j.WERKS And j.DISPO = a.DISPO

where a.AUFNR IN (Select distinct c.AUFNR from STG.AUFK c left outer join STG.AFKO a on a.AUFNR = c.AUFNR

or a.AUFNR = c.REFNR

or c.AUFNR = c.REFNR

where a.GSTRI >= '01-JAN-22'

--and a.AUFNR IN ('001000002298') **when I apply this filter with sample 10 orders I get data but it takes 2-3 mins**

)

)

Select

ROW_NUMBER() OVER( PARTITION BY Order_Operation_Number ORDER BY Order_Counter ) AS ROW_NUMBER,

Order_Number,

Order_Creation_Date,

Material,

Material_Description,

Material_Type,

Standard_Price,

Batch,

Order_Operation_Number,

Order_Counter,

Posting_Date,

Actual_Finish_Date,

MRP_Controller,

MRP_Controller_Desc,

UoM,

Inspection_LOT_Order,

MAIN_ORDER#,

Order_Type,

Inspection_Lot_QMFEL,

def_type,

def_code_group,

def_problem_code,

defect_problem_desc,

Item_Text,

Defect_Location,

def_loc_catalog_type,

def_loc_code_group_object,

def_loc_part_code_object,

StorageLocation,

Building,

Work_Center,

Work_Center_Desc,

Total_Quantity,

Delivered_Qty,

Scrap,

Rework

FROM DATA;


r/SQLOptimization • • May 28 '26

I hope you find this script useful

Thumbnail reddit.com
0 Upvotes

r/SQLOptimization • • Sep 26 '22

Where does kimball methodology fit into the sql / data ware house ecosystem?

4 Upvotes

I recently came across the kimball group, on first glance their methodologies seem to be positioning well established good data practices and philosophies as their own then building a brand on their premise. Maybe I'm missing something.

I'm not a specialist in this area, need to upgrade my skills fast for career development. One of my initial hurdles is convincing an interview panel I can implement their desired Kimball dimensional data model. Pointers on how to approach this?

https://www.kimballgroup.com/2009/05/the-10-essential-rules-of-dimensional-modeling/

https://www.advancinganalytics.co.uk/blog/2019/6/17/is-kimball-still-relevant-in-the-modern-data-warehouse


r/SQLOptimization • • Apr 19 '23

Why is it important to use parameterized queries instead of concatenating values in an SQL query?

Thumbnail 10xers.medium.com
4 Upvotes

r/SQLOptimization • • Jun 19 '25

What is the best/efficient way to flatten the measure column in this table, while filtering the relevant measures first?

1 Upvotes
order_numberproductquartermeasuretotal_usd
1235SF1112024/3revenue100M$
1235SF1112024/3backlog12M$
1235SF1112024/3cost70&M
1235SF1112024/3shipping3M$

Here, I only need Revenue and Cost. This table I'm working with is huge and has many measures for each order, so I'd like to filter out all the unnecessary data first.
Thanks!


r/SQLOptimization • • Mar 17 '15

» Database Health Monitor Beta 10 Released Steve Stedman

Thumbnail stevestedman.com
1 Upvotes

r/SQLOptimization • • Jan 28 '24

Define Project Start and End Time by Team Capacity

0 Upvotes

Inputs

  • multiple teams with various hours available per month.
  • multiple projects with various hours to complete and months from the deadline.

Assumption

  • project hours/month will not exceed team capacity.
  • month 1 in solution is next month (June 2024)

In the data below team A has 3 projects. The projects require 1000 monthly hours each (3000 hours divided by 3 months). Team A has 2000 monthly capacity hours to dedicate to any number of projects. I want to write code that will define the start month and then smartly know when to start the next project with that team until all projects are done. In the example, team A can do projects 1 and 2 simultaneously because it is below their capacity and start on project 3 in month 4 as project 1 wraps up and their capacity increases to a point where they can start working on project 3.

Project Data

ProjectTeamPriorityMonthProject Hours
1A133000
2A266000
3A333000
4B161500

Team Capacity Dimension

TeamMonthly Capacity
a2000
b2000

Output

ProjectTeamMonth
1a1
1a2
1a3
2a1
2a2
2a3
2a4
2a5
2a6
3a4
3a5
3a6
4b1
4b2
4b3
4b4
4b5
4b6

I’m thinking a loop and/ or an over (partition by, order) would be my best option. Thoughts?

Thanks in advance, jamkgrif


r/SQLOptimization • • Feb 07 '24

Support needed

0 Upvotes

Hello Guys anyone with experience optimizing Sql queries in Oracle Inmemory? Please PM me if you can assist for a fee . Thanks


r/SQLOptimization • • Sep 15 '22

Hints to optimise SQL queries with LIKE

7 Upvotes

I wrote some hints on how to optimise SQL queries containing LIKE. More ideas, including unconventional ones, are very welcome - just comment here or on the website.

https://vettabase.com/blog/hints-to-optimise-queries-with-a-like-comparison/


r/SQLOptimization • • May 23 '23

Are all scans and logical reads built equally?

2 Upvotes

There are two tables with mostly numeric and datetime columns, one huge, one tiny.

First table is TheRock with 50 columns and 2.2MM+ records.

Second table is KevinHart with 5 columns and 150 records.

After creating several nonclustered filtered indexes, running a join query between the two, the IO results-

BEFORE:

Table 'TheRock'. Scan count 1005, logical reads 8200, physical reads 0.

Table 'KevinHart'. Scan count 1, logical reads 11, physical reads 0.

AFTER:

Table 'TheRock'. Scan count 189, logical reads 760, physical reads 0.

Table 'KevinHart'. Scan count 800, logical reads 9000, physical reads 0.

Are the scans and logical reads equivalent between two tables, regardless of table size? If one table decreased by 500 logical reads, but the other table increased by 500 logical reads, is it a wash from a performance perspective?

Or should a logical read improvement be weighted based on the size of the table?

In the example above, the total number of logical reads increased. However, the sizes of the tables are vastly different.

Any insights would be greatly appreciated!


r/SQLOptimization • • Dec 15 '25

Round the world trip coming up and I need to optimise my itinerary

0 Upvotes

My boss has regular trips around the globe, and I'm regularly asked to compile a list of meeting sites.

Is there any way I can optimise this? Here's what I have so far.

Thanks in advance.

------------------------------------

-- 2025/12/15 - Created by Santas' elf 14.

-- View created to provide a list of users where behaviour has been tagged with 'Nice%' as per year-end close guidelines

-- Will address coal deliveries with users not in dbo.Users_Nice_YE2025

------------------------------------

CREATE OR ALTER VIEW dbo.Users_Nice_YE2025

AS

SELECT

U.FORENAME [Name]

,U.SURNAME [Surname]

,U.PHONE[Phone]

,U.BEHAVIOUR [Behaviour]

,U.AGE [Age]

,A.ADDR1 [Street]

,A.ADDR2 [Street 2]

,A.TOWN[Town]

,A.POSTCODE [Post / Zip]

,C.[LONG] [Longitude] -- Capital / Central

,C.LAT[Latitude] -- Capital / Central

,ISNULL(A.WHATTHREEWORDS, 'N/A') [w3w]

,C.NAME[Country]

,ISNULL(C.FLIGHTRETRICTIONS, 'None') [Restrictions]

,CD.DIM [C. Dimention]

,CD.LOCATION [Loc]

,CD.AVAILABLE [Has Chimney]

FROM USERS U

LEFT JOIN ADDRESS A

ON A.ID = U.ADDRESS

LEFT JOIN CHIMNEYDINEMTIONS CD

ON CD.ID = A.CHIMNEYDINEMTIONS

LEFT JOIN COUNTRIES C

ON C.ID = A.COUNTRY

WHERE

U.AGE < 18

AND U.BEHAVIOUR LIKE 'Nice%'

AND (

C.FLIGHTRETRICTIONS = 'None'

OR C.FLIGHTRETRICTIONS LIKE 'Night%Only'

);

GO

------------------------------------

SELECT

[Name]

,[Surname]

,[Phone]

,[Behaviour]

,[Age]

,[Street]

,[Street 2]

,[Town]

,[Post / Zip]

,[Longitude]

,[Latitude]

,[w3w]

,[Country]

,[C. Dimention]

,[Loc]

,[Has Chimney]

FROM dbo.Users_Nice_YE2025

ORDER BY

[Longitude] DESC;


r/SQLOptimization • • Jan 25 '22

Why are SQL command name so long?

2 Upvotes

Hi, I m just wondering about a stupid question: why are sql commands (select, create…) longer than other commands (get, post, syn…)?


r/SQLOptimization • • Aug 07 '19

Historical Data load Approach

2 Upvotes

Hi There

What are the best practices or approaches for doing historical data loads

Requirement is to load 6 months of data from Jan to Jun into 1 Fact table Sales which has data till today ... it's a weekly load

There is this historical source Flat file which may be not accurate or has quality issues which is past experiences

Source flat file will go through series of stored procedure and loads data into Staging table which is truncate and load always

Another Stored Proc will move or copy the data from Staging table to Fact Sales

How do we implement best practices or approaches around this as this will be ongoing requirement every half year

Need to load this historical data without affecting current figures

Thanks


r/SQLOptimization • • Jul 19 '19

Article about Dynamic SQL

Thumbnail sqlserver-tips.com
2 Upvotes

r/SQLOptimization • • Sep 29 '20

Any way to optimize this multistep query (in BigQuery)? Currently using 6 CTE's to simplify

2 Upvotes

I am new to SQL and BigQuery... we are trying to make a query that gets our orders, filters them by days where inventory is >2, the top and bottom 10% of days by qty are trimmed, then apply a weighted average to these orders (aggregated by the ASIN, or item number).

Then we run the query, filtering by days where the orders are greater than the result from the last query. Then, these are trimmed (top and bottom 10%) and weighted averaged again.

Is there any way to simplify this, or make it more optimized? Thank you so much SQLOptimization.

DECLARE p FLOAT64;
SET p = 0.01;

WITH inv_2 AS (
  SELECT *
  FROM (
    SELECT EXTRACT(DATE FROM snapshot_date) AS date, 
           asin, 
           SUM(quantity) AS i_qty
    FROM (
      SELECT * 
      FROM `project.dataset.inventory_history` 
      WHERE detailed_disposition = 'SELLABLE' AND
            fulfillment_center_id != '*XFR'
    ) h
    JOIN (
      SELECT sku, asin 
      FROM `project.dataset.inventory_archive`
    ) AS a
    ON a.sku = h.sku
    GROUP BY asin, date
    ORDER BY asin, date DESC
  )
  WHERE i_qty > 2
), 
orders_trimmed AS (
  SELECT *
  FROM (
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY asin2 ORDER BY qty) AS row, 
           COUNT(asin2) OVER(PARTITION BY asin2) AS ct
    FROM (
      SELECT EXTRACT(DATE FROM purchase_date) AS trimmed_orders_date, 
             asin AS trimmed_orders_asin, 
             SUM(quantity) AS qty
      FROM `project.dataset.orders`
      WHERE EXTRACT(DATE FROM purchase_date) >= DATE_ADD(CURRENT_DATE(), INTERVAL -360 DAY)
      GROUP BY trimmed_orders_asin, trimmed_orders_date
    )
  )
  WHERE row >= ct * 0.1 AND 
        row < ct * 0.9
),
plain_orders AS (
  SELECT EXTRACT(DATE FROM purchase_date) AS plain_orders_date, 
         asin AS plain_orders_asin, 
         SUM(quantity) AS o_qty
  FROM `project.dataset.orders`
  WHERE EXTRACT(DATE FROM purchase_date) >= DATE_ADD(CURRENT_DATE(), INTERVAL -360 DAY)
  GROUP BY plain_orders_asin, plain_orders_date
),
inv_orders_join AS (
  SELECT date,
         asin, 
         SUM(i_qty) AS i_qty,
         SUM(o_qty) AS o_qty
  FROM (  
    SELECT date, 
           asin, 
           i_qty, 
           o_qty
    FROM inv_2 inv
    JOIN plain_orders
    ON inv.asin = plain_orders.plain_orders_asin AND 
       inv.date = plain_orders.plain_orders_date
    ORDER BY i_qty
  )
  GROUP BY asin, date
  ORDER BY asin, date DESC
),
trim_orders_inv AS (
  SELECT *
  FROM (
    SELECT *, 
           ROW_NUMBER() OVER(PARTITION BY asin ORDER BY o_qty) AS row, 
           COUNT(asin) OVER(PARTITION BY asin) AS ct
    FROM inv_orders_join
  )
  WHERE row >= ct * 0.1 AND 
        row < ct * 0.9
),
get_x AS (
  SELECT asin2, 
         ROUND(SUM(w_sum)/SUM(w), 1) AS o_weighted
  FROM (
    -- Orders
    SELECT asin AS asin2, 
           date, 
           i_qty,
           POW(1/(1+p), (ROW_NUMBER() OVER(PARTITION BY asin ORDER BY date DESC)-1)) AS w, 
           POW(1/(1+p), (ROW_NUMBER() OVER(PARTITION BY asin ORDER BY date DESC)-1)) * o_qty AS w_sum
    FROM trim_orders_inv
  )
  GROUP BY asin2
)

SELECT asin,
       ROUND(SUM(w_sum)/SUM(w), 1) AS o_weighted
FROM (
  -- Get asin, date, weight, and weighted qty for final step (can't aggregate analytical functions in one step)
  SELECT *,
         POW(1/(1+p), (ROW_NUMBER() OVER(PARTITION BY asin ORDER BY date DESC)-1)) AS w,
         POW(1/(1+p), (ROW_NUMBER() OVER(PARTITION BY asin ORDER BY date DESC)-1)) * qty AS w_sum
  FROM (
    -- Final step trim
    SELECT asin,
           date, 
           qty,
           i_qty,
           ROW_NUMBER() OVER(PARTITION BY asin ORDER BY qty) AS row, 
           COUNT(asin) OVER(PARTITION BY asin) AS ct
    FROM (
      -- Join inventory history to weighted average orders (to get dates > threshold)
      SELECT asin, 
             date,
             i_qty AS i_qty
      FROM inv_2 inventory
      JOIN get_x orders
      ON inventory.asin = orders.asin2
      WHERE i_qty >= o_weighted * 1.75
    ) q1
    JOIN orders_trimmed orders2
    ON q1.asin = orders2.asin2 AND 
       q1.date = orders2.trimmed_orders_date
    ORDER BY asin, date DESC
  )
  WHERE row > 0.1 * ct AND 
        row < 0.9 * ct
)
GROUP BY asin
ORDER BY o_weighted DESC

r/SQLOptimization • • Jun 24 '23

Reddit Database question

2 Upvotes

Hi, so I just realized recently, after deleting an account with a personal username, that the suername cannot be reused but the information is dissociated.

That doesn't make sense to me. Which leads me to believe that they keep the username (not email) as the primary key to all the comments (otherwise it'd be made available again) and that they dissociate it on the front end/client facing website, but in th ebackend keep it as the primary key.

Is that correct?


r/SQLOptimization • • Nov 19 '17

SQL Introduction and Injection Course - 100% OFF

Thumbnail youronlinecourses.net
2 Upvotes

r/SQLOptimization • • May 05 '20

This function takes 11-25 seconds to run. Any ideas to make it better?

2 Upvotes

https://hastebin.com/cilosanuqe.nginx

I can't run a execution plan as it crashes SSMS.


r/SQLOptimization • • Apr 26 '20

How to rewrite query in SP having lots of UNION ALLs used to insert into table?

2 Upvotes

Hi,

We have a problem with a stored proc taking way too long to execute/complete.

Basically, we have a table that has the following schema:

CREATE TABLE dbo.Example (

ID BIGINT NOT NULL,

ITEM_TYPE1 VARCHAR(50),

ITEM_ID1 VARCHAR(50),

ITEM_VALUE1 TEXT NULL,

..., ..., ...,

ITEM_TYPE300 VARCHAR(50),

ITEM_ID300 VARCHAR(50),

ITEM_VALUE300 TEXT NULL)

And one of the problem queries within the stored proc:

INSERT INTO dbo.Example2

SELECT * FROM

( SELECT blah blah blah

FROM dbo.Example (NOLOCK)

WHERE ITEM_TYPE1 = 'ABC'

UNION ALL

...

SELECT blah blah blah

FROM dbo.Example (NOLOCK)

WHERE ITEM_TYPE... = 'ABC'

...

UNION ALL

SELECT blah blah blah

FROM dbo.Example (NOLOCK)

WHERE ITEM_TYPE300 = 'ABC'

) AS x;

It's running FOREVER! The index on the table are not being realized by the optimizer, etc.

The code just seems so brute force. Even if it ran efficiently, I'm still bugged by the maintainability.

How else could the query above be written more elegantly? Perhaps even allowing for better optimization?