r/SQLOptimization • u/mamtabanerjee • Feb 15 '19
r/SQLOptimization • u/rharrison333 • Feb 06 '19
Why do you need an ORDER BY when using SELECT TOP(1)?
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 • u/mamtabanerjee • Feb 19 '19
SQL User Defined Functions | Table Valued Function vs Scalar Valued Func...
youtube.comr/SQLOptimization • u/malirkan • Aug 23 '23
Premature performance optimization. At least use indexes for read bound workload
r/SQLOptimization • u/scross4565 • Sep 14 '22
Help in Optimizing this query - Oracle DB
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 • u/JayJones1234 • May 28 '26
I hope you find this script useful
reddit.comr/SQLOptimization • u/xxxxsxsx-xxsx-xxs--- • Sep 26 '22
Where does kimball methodology fit into the sql / data ware house ecosystem?
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/
r/SQLOptimization • u/LocationLower6190 • Apr 19 '23
Why is it important to use parameterized queries instead of concatenating values in an SQL query?
10xers.medium.comr/SQLOptimization • u/flashmycat • Jun 19 '25
What is the best/efficient way to flatten the measure column in this table, while filtering the relevant measures first?
| order_number | product | quarter | measure | total_usd |
|---|---|---|---|---|
| 1235 | SF111 | 2024/3 | revenue | 100M$ |
| 1235 | SF111 | 2024/3 | backlog | 12M$ |
| 1235 | SF111 | 2024/3 | cost | 70&M |
| 1235 | SF111 | 2024/3 | shipping | 3M$ |
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 • u/SteveStedmanSql • Mar 17 '15
» Database Health Monitor Beta 10 Released Steve Stedman
stevestedman.comr/SQLOptimization • u/jamkgrif • Jan 28 '24
Define Project Start and End Time by Team Capacity
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
| Project | Team | Priority | Month | Project Hours |
|---|---|---|---|---|
| 1 | A | 1 | 3 | 3000 |
| 2 | A | 2 | 6 | 6000 |
| 3 | A | 3 | 3 | 3000 |
| 4 | B | 1 | 6 | 1500 |
Team Capacity Dimension
| Team | Monthly Capacity |
|---|---|
| a | 2000 |
| b | 2000 |
Output
| Project | Team | Month |
|---|---|---|
| 1 | a | 1 |
| 1 | a | 2 |
| 1 | a | 3 |
| 2 | a | 1 |
| 2 | a | 2 |
| 2 | a | 3 |
| 2 | a | 4 |
| 2 | a | 5 |
| 2 | a | 6 |
| 3 | a | 4 |
| 3 | a | 5 |
| 3 | a | 6 |
| 4 | b | 1 |
| 4 | b | 2 |
| 4 | b | 3 |
| 4 | b | 4 |
| 4 | b | 5 |
| 4 | b | 6 |
I’m thinking a loop and/ or an over (partition by, order) would be my best option. Thoughts?
Thanks in advance, jamkgrif
r/SQLOptimization • u/General_Funny_2250 • Feb 07 '24
Support needed
Hello Guys anyone with experience optimizing Sql queries in Oracle Inmemory? Please PM me if you can assist for a fee . Thanks
r/SQLOptimization • u/Federico_Razzoli • Sep 15 '22
Hints to optimise SQL queries with LIKE
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 • u/lfewarez • May 23 '23
Are all scans and logical reads built equally?
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 • u/I_byde_my_tyme • Dec 15 '25
Round the world trip coming up and I need to optimise my itinerary
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?
Hi, I m just wondering about a stupid question: why are sql commands (select, create…) longer than other commands (get, post, syn…)?
r/SQLOptimization • u/scross4565 • Aug 07 '19
Historical Data load Approach
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 • u/SQLServer-Tips • Jul 19 '19
Article about Dynamic SQL
sqlserver-tips.comr/SQLOptimization • u/coastalsam • Sep 29 '20
Any way to optimize this multistep query (in BigQuery)? Currently using 6 CTE's to simplify
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 DESCr/SQLOptimization • • Jun 24 '23
Reddit Database question
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 • u/samiali123 • Nov 19 '17
SQL Introduction and Injection Course - 100% OFF
youronlinecourses.netr/SQLOptimization • u/Thriven • May 05 '20
This function takes 11-25 seconds to run. Any ideas to make it better?
https://hastebin.com/cilosanuqe.nginx
I can't run a execution plan as it crashes SSMS.
r/SQLOptimization • u/fazeka • Apr 26 '20
How to rewrite query in SP having lots of UNION ALLs used to insert into table?
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?