How to Make This SQL More Efficient

How can I make large IN clauses more efficient in SQL Server?

Add the elements of the IN to an indexed temporary (if the elements change) or permanent table (if the elements are static) and inner join on them.

How to re-write SQL query to be more efficient?

Sometimes aggregating first, then joining to the aggregated result is faster. But this depends on the actual DBMS being used and several other factors.

select client_id, e.max_everyday_transaction_date, s.max_savings_transaction_date
from client_table c
  left join (
    select client_id, max(transaction_date) as max_everyday_transaction_date
    from everyday_account 
    group by client_id
  ) e on c.client_id = e.client_id
  left join (
    select client_id, max(transaction_date) as max_savings_transaction_date
    from savings_account
  ) s on c.client_id = s.client_id

The indexes suggested by Tim Biegeleisen should help in this case as well.

But as the query has to process all rows from all tables there no good way to speed up this query, other than throwing more hardware at it. If your database supports it, make sure parallel query is enabled (which will distribute the total work over multiple threads in the backend which can substantially improve query performance if the I/O system can keep up)

Is there a way to make this SQL more efficient?

This is shorter and probably performs faster too

SELECT e1.empname, d.deptname
from (
      SELECT e2.deptid
      FROM employee AS e2
      GROUP BY e2.deptid
      HAVING COUNT(e2.empid) >= 4
    ) G
inner join employee AS e1 on e1.deptid = G.deptid
INNER JOIN department AS d on d.deptid = G.deptid
ORDER BY e1.empname;

Start with the grouping. You don't need COUNT from the inner query.
Then, join to both tables just to get the names.

INNER JOIN is used because once the count is complete, we already know that

1. the employees exist
2. the department exists

Can I make this SQL query more efficient?

Try prequerying, but your data is STILL requiring the results to go through EVERY RECORD.

select
      PreQuery.name,
      sum(case when PreQuery.Geode < 10.0 then 1 else 0 end) 10mCount,
      sum(case when PreQuery.Geode < 50.0 then 1 else 0 end) 50mCount,
      sum(case when PreQuery.Geode < 1000.0 then 1 else 0 end) 1000mCount 
   from
      ( select 
              a.name,
              ST_GeodesicLengthWGS84( ST_SetSRID( ST_LineString(a.lat, a.lon, b.lat, b.lon),4326)) as Geode
           from 
              a join b  
                 (YOU ARE MISSING the JOIN 'ON' clause... how related) ) PreQuery
   GROUP BY 
      PreQuery.name
   ORDER by 
      1000mCount desc
   LIMIT 10;

How can I make this sql query faster?

Big IN() lists are inherently slow. Create a temporary table with an index and put the values in the IN() list into that tempory table instead, then you'll get the power of an indexed join instead of giant IN() list.

How to make SQL query faster?

Try this way:

select * 
from t1
left join t2 on t1.id1 = t2.id
where t2.id is null

Make this SQL query more efficient

In SQL, the goal should be to write operations on entire tables at once. The SQL server can be very efficient at doing so, but will need a significant overhead on any interaction, since it needs to deal with consistency, atomicity of transactions, etc. So in a way, your fixed cost per transaction is high, for the server to do its thing, but your marginal cost for additional rows in a transaction is very low - updating 1m rows may be 1/2 as fast as updating 10.

This means that the foreach is going to cause the SQL server to constantly go back and forth with your application, and that fixed cost of locking/unlocking and doing transactions is being occurred every time.

Can you write the query to operate in SQL, instead of manipulating data in C#? It seems you want to write a relatively simple update based on your select statement (See, for instance, SQL update from one Table to another based on a ID match.

Try something like the following (Not code tested, since i don't have access to your database structure, etc.):

UPDATE MYTABLE 
  SET REMAININGITEMS = remainingQty, 
  REMAININGDELIVERYSIZE=remainingBoxes
From 
(SELECT t.PONUM, t.ITMNUM, t.ordered, 
       SUM(t.received) as received, 
       t.ordered - ISNULL(SUM(t.received),0) as remaining, 
       SUM(t.orderedcartons) as orderedcartons, 
       SUM(t.cartonsreceived) as cartonsreceived, 
       SUM(t.remainingcartons) as remainingcartonsFROM(SELECT pod.PONUM, 
       pod.ITMNUM, pod.QTY as ordered, ISNULL(grd.QTYRECEIVED, 0) as received, 
       pod.DELIVERYSIZE as orderedcartons, 
       ISNULL(grd.DELIVERYSIZERECEIVED, 0) as cartonsreceived, 
       (pod.DELIVERYSIZE - ISNULL(grd.DELIVERYSIZERECEIVED, 0)) as remainingcartons
FROM TBLPODETAILS pod 
  LEFT OUTER JOIN TBLGRDETAILS grd 
    ON pod.PONUM = grd.PONUM and pod.ITMNUM = grd.ITMNUM) t
GROUP BY t.ITMNUM, t.PONUM, t.ordered
ORDER BY t.PONUM ) as x

join MYTABLE on MYTABLE.ITMNUM=x.itmnum AND MYTABLE.PONUM=i.ponum

---

Related Topics