logo
down
shadow

How to improve SQL query performance (correlated subqueries)?


How to improve SQL query performance (correlated subqueries)?

Content Index :

How to improve SQL query performance (correlated subqueries)?
Tag : sql , By : mediafarm
Date : November 24 2020, 03:41 PM


Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Correlated subqueries: How to get the ID of the outer query?


Tag : sql , By : Tony Z
Date : March 29 2020, 07:55 AM
around this issue AFAIK you can't do a correlation into a Derived Table. But you can rewrite your query to a non-correlated subquery:
SELECT People.IDPerson, People.NAME
FROM
(
    -- Selects people that never made a sticker transaction

    SELECT p.IDPerson, p.NAME
    FROM Person p
    LEFT JOIN StickerTransaction sT
    ON p.IDPerson = sT.IDPerson
    WHERE sT.IDPerson IS NULL

    UNION

    -- Selects people that have currently 0 sticker

    SELECT p.IDPerson, p.NAME
    FROM Person p
    JOIN     (
                SELECT sT.IDPerson, SUM(NbStickers) AS NbStickers
                FROM (

                       SELECT (sT.NbStickersReceived - sT.NbStickersGiven) AS NbStickers
                       FROM StickerTransaction sT

                     UNION ALL

                       SELECT pu.NbStickersBought AS NbStickers
                       FROM Purchase pu
                     ) dt
                GROUP BY sT.IDPerson
                HAVING SUM(NbStickers) = 0
             ) sT
    ON sT.IDPerson = p.IDPerson

) People

Is there a way to unify the subqueries in this with clause into a single query? Would it improve performance?


Tag : sql , By : user124112
Date : March 29 2020, 07:55 AM
I hope this helps . Your query is quite complicated; again and again you must read the same table. What you need instead is conditional aggregation. I moved the NULLIF to avoid division by zero to the WITH clause so as not to have to repeat it in every line.
    with allmilestones as
( select
    project_id,
    nullif(count(*),0) as all_milestones,

    count(case when 
            actual_date is not null 
          then 1 end) as all_actual_milestones,

    count(case when 
              actual_date is null
              and proposed_date is not null
              and revised_date is null 
          then 1 end) as all_proposed_milestones,
    count(case when 
              actual_date is null              
              and revised_date is  not null 
          then 1 end) as all_revised_milestones

    ,count(case when 
              (Actual_date is not null
                        or revised_date is not null
                        or proposed_date is not null)
    then 1 end) as all_scheduled_milestones,

    count(case when 
            is_external=1
          then 1 end) as external_milestones,
    count(case when 
            is_external=1 and
            actual_date is not null 
          then 1 end) as external_actual_milestones,

    count(case when 
    is_external=1 and
              actual_date is null
              and proposed_date is not null
              and revised_date is null 
          then 1 end) as external_proposed_milestones,
    count(case when 
    is_external=1 and
              actual_date is null              
              and revised_date is  not null 
          then 1 end) as external_revised_milestones

    ,count(case when 
    is_external=1 and
              (Actual_date is not null
                        or revised_date is not null
                        or proposed_date is not null)
    then 1 end) as external_scheduled_milestones,

    count(case when 
            is_external=0
          then 1 end) as internal_milestones,
    count(case when 
            is_external=0 and
            actual_date is not null 
          then 1 end) as internal_actual_milestones,

    count(case when 
    is_external=0 and
              actual_date is null
              and proposed_date is not null
              and revised_date is null 
          then 1 end) as internal_proposed_milestones,
    count(case when 
    is_external=0 and
              actual_date is null              
              and revised_date is  not null 
          then 1 end) as internal_revised_milestones

    ,count(case when 
    is_external=0 and
              (Actual_date is not null
                        or revised_date is not null
                        or proposed_date is not null)
    then 1 end) as internal_scheduled_milestones

  from v_projectmilestone
  group by project_id
)
select 
  project_id,
  cast( round(100.0 * all_actual_milestones / all_milestones, 0) as int) as percent_all_actual_milestones,
  cast( round(100.0 * all_proposed_milestones / all_milestones, 0) as int) as percent_all_proposed_milestones,
  cast( round(100.0 * all_revised_milestones / all_milestones, 0) as int) as percent_all_revised_milestones,
  cast( round(100.0 * all_scheduled_milestones / all_milestones, 0) as int) as percent_all_scheduled_milestones ,

  cast( round(100.0 * external_actual_milestones / nullif(external_milestones,0), 0) as int) as percent_external_actual_milestones,
  cast( round(100.0 * external_proposed_milestones / nullif(external_milestones,0), 0) as int) as percent_external_proposed_milestones,
  cast( round(100.0 * external_revised_milestones / nullif(external_milestones,0), 0) as int) as percent_external_revised_milestones,
  cast( round(100.0 * external_scheduled_milestones / nullif(external_milestones,0), 0) as int) as percent_external_scheduled_milestones, 

  cast( round(100.0 * internal_actual_milestones / nullif(internal_milestones,0), 0) as int) as percent_internal_actual_milestones,
  cast( round(100.0 * internal_proposed_milestones / nullif(internal_milestones,0), 0) as int) as percent_internal_proposed_milestones,
  cast( round(100.0 * internal_revised_milestones / nullif(internal_milestones,0), 0) as int) as percent_internal_revised_milestones,
  cast( round(100.0 * internal_scheduled_milestones / nullif(internal_milestones,0), 0) as int) as percent_internal_scheduled_milestones

from allmilestones;

Improve performance of correlated subquery with inner joins


Tag : sql , By : Techspirit
Date : March 29 2020, 07:55 AM
I hope this helps . It's not a join you're looking for, but a condition on the aggregate function.. something like this
select b.state, 
    b.city,  
    count(b.state) as totalCount, 
    sum(cast(replace(b.annual_prod, ',','.') as decimal)) AS annualProd, 
    SUM(CASE 
           WHEN cast(replace(a.size_k, ',','.') as decimal) >= 20 
           THEN 1 
           ELSE 0 END) as Above20k
    FROM opentable_clean b  
    GROUP BY b.state, b.city  
    ORDER by annualProd DESC; 

Can I improve performance of a query if I use several subqueries and use their results as condition for the main query?


Tag : chash , By : Hugo Hernan Buitrago
Date : March 29 2020, 07:55 AM
it fixes the issue The answer to question 1 is of course yes, especially if you can use something like LINQ to SQL it would be easy. LINQ converts the method Contains to the SQL operator IN.
In SQL, can you use the sub-query with IN?
SELECT EmdTable."Time [ns]", AVG(EmdTable."Upper Sideband [mV]") FROM EmdTable 
WHERE EmdTable.ResultID IN (
    SELECT ResultTable."ID"
    FROM ResultTable, ShmooTable 
    WHERE ResultTable."ShmooID"=ShmooTable."ID" 
          AND ResultTable."CommandName"="APDU: Get PO" 
          AND ResultTable."Repetition"="1" 
          AND ResultTable."StepName"="Command" 
          AND ShmooTable."Hn [A/m]"="2"
)
AND EmdTable."Time [ns]" IS NOT NULL AND EmdTable."Upper Sideband [mV]" IS NOT NULL
GROUP BY  EmdTable."Time [ns]"

Rewrite Query without using correlated subqueries


Tag : oracle , By : CrimsonGore
Date : March 29 2020, 07:55 AM
Related Posts Related QUESTIONS :
  • SQL Case Expression Syntax?
  • Drop all tables whose names begin with a certain string
  • What program can I use to generate diagrams of SQL view/table structure?
  • Suggestions for implementing audit tables in SQL Server?
  • Distribution of table in time
  • Backup SQL Schema Only?
  • SQL query for a database scheme
  • Timer-based event triggers
  • SQL query, count and group by
  • Paging SQL Server 2005 Results
  • SQL Server 2005 For XML Explicit - Need help formatting
  • How do I use T-SQL Group By
  • How do I split a string so I can access item x?
  • Date Condition with Case when Holiday exclude
  • SQL Query Select from 1 table and return data based on 2 columns
  • how to use rank/join and where together
  • How to create new column with values counting up every 9th value with SQL?
  • Most frequent combination of words/characters SQL Server
  • Why can't I access a field defined as "Select 1" from a subquery in the outer query?
  • How to solve Msg 8115, Level 16, State 2, Line 2 Arithmetic overflow error converting expression to data type int.?
  • UPDATE row inserted earlier in CTE
  • Display current month values based upon abbreviation (Oct) SQL Server
  • SQL Modify returned select value without union
  • How to put a variable in a like statement in vba?
  • More Elegant way to Batch Validate Phone Numbers
  • SQL find parent where children match over multiple rows
  • A combo box that has to be filter the same data in that field
  • Exclude updated record from trigger function
  • Insert into table from select only when select returns valid rows
  • How to fix SQLite3::SQLException: near "to": syntax error: SELECT "".* FROM "" WHERE "
  • Filter msdb.dbo.sysjobsteps.command into seperate columns
  • How to extract year or month,day from oracle function without using sub-query?
  • Reset sum when condition is met in Oracle
  • Date Between (Start & Now)
  • Reset running sum when condition is met in Oracle
  • Why is my date criteria filtering incorrectly in a MsysObjects query using DCount?
  • Stripping date/time delimiters in an SSIS csv export
  • SSIS Error on insertion - Error when new primary keys in Dimension table
  • How can I make a temp table of specific datetime data within a range?
  • AnalysisException: Syntax error in SQL After multiple join
  • SQL Query Multiple Columns Using Distinct on One Column Only and Using Order By
  • Sum inventory item groups
  • TSQL: Parsing strings with various characters
  • Using SQL Server : how to use select criteria based on sum
  • Inserting dynamic pivot Result into Temp table
  • Joining multiple fields in a dataset joined with descriptions
  • Conditional WHERE EXISTS in Oracle query
  • How to get the count of each unique value in a column between two dates?
  • SQL: Select only if multiple values are IN 1 column from CatalogTable
  • In a query (no editing of tables) how do I join data without any similarities?
  • Create nested json blobs in PostgreSQL
  • Getting extra dates while using cte
  • Way to alter one table if another was altered
  • Proper use of semicolons in functions and procedures
  • Cursor Not looping all the Records
  • Select inner join returns wrong result
  • Group By showing duplicates
  • Error in using subquery in access join query
  • How do I use a composite pk in a stored procedure?
  • Oracle Unique Indexes
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com