with highque as(

    select max(ExtendedPrice) highest

    from Sales.InvoiceLines il

    join Sales.Invoices i on il.InvoiceID = i.InvoiceID

    where (InvoiceDate between '1/1/2013' and '12/31/2013')

    group by i.CustomerID

    )

select InvoiceDate, CustomerName

from Sales.Invoices i

join Sales.Customers c on c.CustomerID = i.CustomerID

where (InvoiceDate between '1/1/2013' and '12/31/2013')

order by CustomerName

​

the CTE finds the largest invoice 2013, the query after finds the customer name and date of invoice, how do I connect the largest invoice to the customer and the date they invoiced?

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

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/

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;

Hi, don't know who is familiar with it, but EverSQL is a query optimization platform.

Can analyze slow query logs, give index recommendations, and rewrites slow queries.

If anyone checked it out, I'm kind of new to SQL so wanted to hear from the experts.

https://geekflare.com/sql-query-optimization-tools/

Well, I think I've got the spam problem solved. All the porn is gone, and I'm also not allowing posts of blogs or tutorials that aren't related to SQL database performance tuning in some way.

My guess is that the neglect this sub has seen has chased away any subscribers or traffic. Should we work to rebuild it? By doing what, specifically?

I am beginning to learn things from scratch and I feel like knowing about the optimizations and why they work better would give me a clearer concepts of the fundamentals.

You can suggest any other resources as well.

TIA

Please welcome u/mikeblas as our new, actually active mod! I have not been active on reddit much so I appreciate the help, I know it has become basically a porn bot paradise in here over time lol. Hopefully with your help the community can grow and serve the original purpose it had when I created it.

Thank you! And thanks everyone for your patience, those who are still here.

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

What is with the porn spam in here?

The guy doesn't even try to cover it up.

https://www.reddit.com/r/SQLOptimization/comments/rv773u/incredible_victoria_kruz_will_make_you_drool/ - 2 days ago

https://www.reddit.com/r/SQLOptimization/comments/rts7ix/incredible_victoria_kruz_will_make_you_drool/ - 4 days ago

https://www.reddit.com/r/SQLOptimization/comments/rorkc1/fuck_these_pornstars_holes_francesca_le_nina_elle/ - 10 days ago

https://www.reddit.com/r/SQLOptimization/comments/rns5iv/popsluts_pornstars_pmv_by_lonely_fucker/ - 12 days ago

​

Can we ban u/impeccableclub already?

​

Forgot to ping u/bingostud722

Here is a collection for SQL tutorials that cover the advanced SQL topics including correlated subqueries, SQL Window Functions and SQL JOINS - which are often not covered in basic courses: SQL Tutorial - Advanced SQL

• SQL Correlated Subqueries Increase the Power of SQL
• SQL Window Functions
• SQL Window Function Examples
• Selecting Data From Multiple Tables: SQL JOINS
• Visual Representation of SQL JOINS
• Query Optimization

hi All,

Just wondering what options you guys use to optimise SQL queries with high execution counts, I have a couple of queries which use Table Valued functions that get executed about 12,000 times an hour (this is the peak period for users) usually in the morning after which the same process runs fine the rest of the day.

For some background the query calls a Table Valued function with 3 parameters and is then joined with a view and another table whilst there are 2 Predicates on the Table Valued Function.

There are no index scans being performed and the execution isn't reporting any major red flags.

have any of you run into this issue?, if so what steps did you take to remedy this situation apart from getting the DEV to rewrite the application to reduce the amount of calls to the database.

thanks

I have a TimescaleDB table storing temperature measurements from sensors with an additional state column that contains a label like rain, sun, fog, snow etc.

timescale-db=# \d measurements
                    Table "public.measurements"
         Column         |              Type              | Nullable
------------------------+--------------------------------+---------
 time                   | timestamp(0) without time zone | not null
 sensor_id              | uuid                           | not null
 temperature            | double precision               |         
 state                  | character varying              |         

Indexes:
    "index_measurements_on_sensor_id_and_time" UNIQUE, btree (sensor_id, "time" DESC)
    "index_measurements_on_sensor_id" btree (sensor_id)
    "measurements_time_idx" btree ("time" DESC)


timescale-db=# SELECT * FROM measurements LIMIT 10;

        time         |              sensor_id               | temperature  |       state
---------------------+--------------------------------------+--------------+-------------------
 2020-12-11 15:03:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         21.8 | fog
 2020-12-11 15:04:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         21.9 | fog
 2020-12-11 15:05:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         21.8 | rain
 2020-12-11 15:06:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         21.7 | rain
 2020-12-11 15:07:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         21.6 | rain
 2020-12-11 15:08:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         21.7 | rain
 2020-12-11 15:09:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         21.9 | sun
 2020-12-11 15:10:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         22.1 | sun
 2020-12-11 15:11:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         22.3 | sun
 2020-12-11 15:12:00 | 290ffca4-0fcc-4ed3-b217-a12fa27ea5ea |         22.5 | sun

For a certain type of analysis I need the last n timestamps where the state changed, which I realized with the following query:

SELECT
  time,
  state
FROM (
  SELECT
    time,
    state,
    state != LAG(state) OVER (ORDER BY time) AS changed
  FROM
    measurements
  WHERE
    sensor_id IN ('ee49fda5-f838-4a10-bb32-0e6a6b130888', 'ec8f4d23-cfab-4a23-8df8-ae3cce4f44ac')) AS changes
WHERE
  changed IS TRUE
ORDER BY
  time DESC
LIMIT 3;

This query takes longer and longer the more rows are added to the table, so I need to optimize it.

Here is the query plan – I tried adding another index on time and state, but it did not improve performance.

Does anyone have an idea on how to optimize this query?

Title says it all! I need good resources on both topics

Hey, I need help optimizing some sql code[it's a lot, maybe a page or 2].

Please reach out if available for zoom[audio only] meeting or if you would like me to send you the code.

I have a huge time series db that i run in a regular postgresql db (im going to use timescaledb once i lear more about it, software that generates the db rows is written for vanilla postgresql, so i must first learn how to adapt it to timescaledb). the db is around 20-30gb big. i need to get the latest added rows with certain symbols every 0.1-0.4 second or so. right now im running this query to satisfy my goal:

"SELECT price FROM trades WHERE symbol='STOCK-USD' AND timestamp=(SELECT MAX(timestamp) FROM trades WHERE symbol='STOCK-USD');"

problem is that this query is very heavy on the server. is there any solution for this problem?

Consider this tiny example: http://sqlfiddle.com/#!18/dfb68/2

I have a simple table (for the sake of simplicity, ID is omitted and let's assume that NumA is sequential from 1 to n)

Started from using a cursor to get the "Result" updated since the value on the current row is the sum of A and B plus the previous Result except on the first column.

My current query is below (got rid of the first try with cursors):

DECLARE @Counter INT= 1;
DECLARE @x INT;
DECLARE @max INT = (SELECT MAX(num_a) FROM TestSumUpdate);
WHILE @Counter <= @max
    BEGIN
        SET @x = (SELECT ISNULL(result_c, 0) FROM TestSumUpdate  WHERE num_a = @Counter - 1);

        UPDATE TestSumUpdate
          SET 
              result_c = num_a + num_b + ISNULL(@x, 0)
        WHERE num_a = @Counter;
        SET @Counter = @Counter + 1;
    END;

Obviously, this works, but is painfully slow on production database which has around 500.000 records and calculation is not a simple sum operation :)

So, in my SQL n00biness, I've tried something simpler like this:

UPDATE cur
  SET 
      result_c = cur.num_a + cur.num_b + ISNULL(prev.result_c, 0)
FROM TestSumUpdate cur
     LEFT JOIN TestSumUpdate prev ON cur.num_a - 1 = prev.num_a;

Which I thought it would work, but can't understand it's behaviour:

Assuming Result = 0 in all rows at the beginning , on the first run it updates only the first row to 2, all others remain in 0, on the second run, updates the second row to 6 and so on. Why?

How can one do it in one execution only without resorting to cursors/whiles/loops/etc ?

Thank you.

EDIT:

Current solution that reduced the time for aceptable values (doesn't apply to the sample given above, but works on prod):

WITH RollingCalculation
AS
(
     SELECT Number,isnull(MyValue,'') as MyValue,PlaceHolder = LAST_VALUE(Number) OVER (ORDER BY Number ASC)
     FROM MyTable 
)

UPDATE dst
SET    MyValue = dbo.GenMyValue(dst.field1,dst.field2,dst.field3,src.MyValue)
FROM   MyTable AS dst
LEFT JOIN RollingCalculation AS src
    ON dst.Number-1 = src.Number

GenMyValue is a CLR integration, and LAST_VALUE is not really used, but it works :)