Tags down


How to optimize SQL query with multiple selects

By : fatchops
Date : October 18 2020, 08:10 AM
hope this fix your issue I have a query that uses multiple SELECTS, and I need to optimize this but I have no clue how to do this. , It is better to write this query using window functions:
code :
select e.* 
from (select last_name, department_id, salary, 
             avg(salary) over (partition by department_id) as avgsalary
      from employees e
     ) e
where salary < avgsalary;

Share : facebook icon twitter icon

optimize this query that selects rows before and after a pk

By : user3265135
Date : March 29 2020, 07:55 AM
it fixes the issue Get the rows with an ID less/greater than yours, and limit it to 2 rows.
code :
next_objs = Media.objects.filter(id__gt=image.id).order_by('id')[:2]
prev_objs = Media.objects.filter(id__lt=image.id).order_by('-id')[:2]

Optimize a bunch of nested SELECTs in a MS ACCESS query

By : Aviv
Date : March 29 2020, 07:55 AM
may help you . This is a MS Access query that runs very slow. The Results table is the main table and the P, B, S and C tables are child tables. The column combination Index and ResultID is unique in all of these tables. So for instance, there can only be one row in the table C where Index is "1" and ResultID is 2. I'm not using JOIN because MS Access only support inner joins and what I'd need is an outer join because I want to show all records in the table Results even if there is no corresponding record in any other table. , I am not sure where you got the following:
code :
    max(IIF(P.Index = "0", P.P, null)) as P0,
    max(IIF(P.Index = "1", P.P, null)) as P1,
    max(IIF(P.Index = "2", P.P, null)) as P2,
    max(IIF(P.Index = "3", P.P, null)) as P3,
    max(IIF(P.Index = "4", P.P, null)) as P4,
    max(IIF(P.Index = "5", P.P, null)) as P5,
    max(IIF(B.Index = "0", B.B, null)) as B0,
    max(IIF(B.Index = "1", B.B, null)) as B1,
    max(IIF(B.Index = "8", B.B, null)) as B8,
    max(IIF(S.Index = "2", S.S, null)) as S2,
    max(IIF(C.Index = "1", C.C, null)) as C1
FROM Results
    on P.ResultID = Results.Id
    on B.ResultID = Results.Id
    on S.ResultID = Results.Id
    on C.ResultID = Results.Id
GROUP BY [Results].[Id],
TRANSFORM Max(Src.Value)
from results as R
left join
    select ResultID, P as Value, "P"&Index as col
    from p
    union all
    select ResultID, B as Value, "B"&Index as col
    from B
    union all
    select ResultID, C as Value, "C"&Index as col
    from C
    union all
    select ResultID, S as Value, "S"&Index as col
    from S
) as Src
    on R.id = Src.ResultID
GROUP BY [Results].[Id], [Results].[Name],
    [Results].[ImageFileName], [Results].[Image],
PIVOT Src.col

How to optimize SQL query with multiple SELECTs from the same table?

By : railsdeveloper1990
Date : March 29 2020, 07:55 AM
I hope this helps . When you want to do a pivot in MySQL you can do Aggregate on a Case statement
code :
    SUM ( CASE WHEN other_same_field  = 0 THEN same_field ELSE 0 END) as A,
    SUM ( CASE WHEN other_same_field  = 1 THEN same_field ELSE 0 END) as B,
    SUM ( CASE WHEN other_same_field  = 2 THEN same_field ELSE 0 END) as C
FROM same_table
WHERE other_same_field IN (0, 1, 2)

Optimize Oracle query with joins and multiple selects

By : Vanneste Koen
Date : March 29 2020, 07:55 AM
this one helps. You should have indices on (Run.rn_test_config_id, Run.rn_run_id) AND (Run.rn_run_id), which should help with the partitioning and subsequent join.
I'd also probably see if tweaking your query like this would help:
code :
SELECT  Test.ts_user_11 AS "Team", 
        Test.ts_test_id AS "Test ID",
        Test.ts_name AS "Test Name",
        TEST.ts_user_06 AS "Priority",
        CASE WHEN Run.rn_status = 'Failed' OR Run.rn_status = 'Issue Found' 
                  THEN 'Ran with Issues'
             WHEN Run.rn_status = 'No Run' AND Run.rn_execution_date IS NOT NULL 
                  THEN 'Skipped'
             WHEN (Run.rn_status = 'No Run' OR Run.rn_status = 'N/A') AND Run.rn_execution_date IS NULL 
                  THEN 'Not Run'
             ELSE Run.rn_status END AS "Exec Status",
        Test.ts_user_03 AS "Type",
        TO_CHAR(Run.rn_execution_date, 'YYYY-MM-DD') AS "Execution Date",
        Run.rn_user_01 AS "Build Number"  
FROM Data_Protection_Hendrix_DB.Run Run
-- one of the few times you can exclude the grouping condition from the results!
-- Only since rn_run_id is distinct
JOIN (SELECT MAX(rn_run_id) AS run_id
      FROM Data_Protection_Hendrix_DB.Run
      GROUP BY rn_test_config_id) Latest_Run
  ON Latest_Run.run_id = Run.rn_run_id
-- kept these as a LEFT JOIN, since that's more likely the intent.
-- I'm not sure that's what you were _getting_, though.
LEFT JOIN Data_Protection_Hendrix_DB.Test
       ON Test.ts_test_id = Run.rn_test_id
          AND Test.ts_template = 'N'
LEFT JOIN Data_Protection_Hendrix_DB.All_Lists
       ON All_Lists.al_item_id = Test.ts_subject
          AND All_Lists.al_absolute_path NOT LIKE 'AAAAAPAAJ%'
          AND All_Lists.al_absolute_path NOT LIKE 'AAAAAPAAC%'

Optimize SQL query with 2 selects

By : user2656661
Date : March 29 2020, 07:55 AM
like below fixes the issue I am trying to update a single campaign.id with minimum used_time (datetime) based on user.id but the following code need about 5 seconds to execute. Backlinks table contains 1 million rows. , You can try to UPDATE with JOIN by a subquery.
code :
UPDATE `backlinks` b
    SELECT c.id
    FROM campaigns c
    WHERE exists (
        SELECT 1 
        FROM campaigns cc
        WHERE c.user_id = cc.user_id
        GROUP BY cc.user_id
        HAVING min(cc.used_time) = c.used_time
) t1 on b.`campaign_id` = t1.id
    b.`crawler_id` = 'test', 
    b.`googlebot_id` IS NULL 
    b.`used_time` IS NULL
Related Posts Related Posts :
  • Count distinct values in an SQL table for each hour in a day
  • If else in Where Clause SQL
  • Select multiple rows with matching id(s)
  • Oracle SQL query for Group By with Having clause
  • sql list of records within a path scope
  • Condition for MAX(Booking) within a specific period
  • Unable to query multiple tables via XML: Error occurred in XML processing
  • SQL first date for the first occurrence of a value
  • Update multiple columns Table from a select statement
  • Is it possible to find (in an ordered table) multiple rows in sequence?
  • Query to return month-wise count in PostgreSQL
  • adding years getting invalid number error in case statement only - ORA-01722
  • Insert into a table using multiple tables
  • How to create a SQL Server Pivot Table
  • My SQL - COUNT field on multitable group by other field
  • How to order by a different column and then fetch offset rows in SQL Server?
  • Query builder Eloquent using Eager Loading in Laravel
  • What is the complexity of the CUBE operator in SQL?
  • IIF statement MS ACCESS VBA SQL
  • SQL Server group by?
  • LIKE in Elasticsearch for large texts
  • SQL - dynamic sum based on dynamic date range
  • SQL Order By: Specific Value First, then Ordering?
  • SQL Developer - SELECT statement with AVG calculation displaying multiple averages?
  • Microsoft SQL Server : return only the rows with the most recent date for each unique ID
  • How to create a queue like structure in SQL Server
  • Cross apply on columns on SQL server. Syntax error near )
  • Select count of subquery results, along with another field
  • SQL Server 2016 - Five Column to single row
  • Insert zero values for unexisting groups in Redshift
  • SQL LIKE '%...' in vba HTTP request
  • SQL Developer - How do I get MAX & AVG to draw from one column and filter from themselves?
  • Postgresql 11 - Create Procedure to Execute COPY function
  • Oracle VIEW - Convert VARCHAR2 to DATE and find MIN/MAX of DATE
  • In SQL, Main table should match with lookup table for the multiple columns
  • Very slow performance for inner join with restrictive where clause (very small subset of rows)
  • oracle sql 2 columns in one column
  • SQL query to get conflicting values in JSONB from a group
  • Two queries with same tables and join logic but different results
  • How to update a table joined with another table multiple times in Oracle?
  • Crystal Report how can I get data for every 5th of months
  • Finding MAX() from a serial number field which contains characters in SQL Server
  • Getting all distinct values and sorting them by hour
  • Database Compatibility Level
  • Combine WITH statement with UNION
  • Self Nested SQL join
  • My store procedure only executes my first select statement while I wrote three?
  • Inserting Date gives error ORA-01861: literal does not match format string
  • Need to join row data into columns in Oracle
  • Syntax error Missing operator in my case statement
  • Filter the values from the source in merge query in mssql
  • How can i replace/convert number to string
  • Updating a table, on a button press, with selected information
  • Oracle SQL report generation without null
  • SQL get the names of the columns that are stored in a different table
  • Number masking & nls_parameter
  • Delete duplicate roles for SQL server
  • SQL Server: How to change collation of all columns of my select on the fly
  • SQL insert into statement leads to weird (cartesian product) result
  • check constraint sql server
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org