Tag Info

Hot answers tagged

3

Try to execute the query with gather_plan_statistics hint. Then use dbms_xplan to display exec plan. You will see E-rows(estimated) and A-rows(Actual). This is where I would start, to check whether the optimizer is wrong in it's assumptions or not. Also check v$sql_plan of the running query and check whether TEMP space is really used or not. Especially on ...


1

Query You UPDATE statement looks good, mostly. I re-formatted and made minor improvements: UPDATE line_items li SET product_id = d.latest_product_id FROM products p JOIN vendors v ON v.id = p.vendor_id JOIN vendorgroups vg ON vg.id = v.vendorgroup_id JOIN duplicate_product_sets d ON d.invtid = ...


1

query_cache_type = 0 query_cache_size = 0 Seriously, turning off the QC is probably best. especially based on what you said. Keep in mind that every INSERT (or UPDATE) to a table causes all entries in the QC to be purged. Furthermore, if the QC size is large, (say, over 50M), the purge time slows down the write. The ENGINE's cache is important to both ...


1

The following images help us to understand briefly: Source


1

As far as your query goes, it seems you've followed SQL best practices so there are no easy, significant performance boosts I could find. Now, sorry to state the obvious, but make sure you have enough indexes and that you are using those indexes, and check if all your stats are up to date. Database Engine Tuning Advisor is a great tool to check both of ...


1

First step: This is SQL. You don't need to make separated queries for every status type. SELECT sh_table.`status`, SUM( CASE WHEN ( date(temp.max_created_at) BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 1 DAY) AND DATE_SUB(DATE(NOW()), INTERVAL 0 DAY)) THEN 1 ELSE 0 END ) AS INTERVAL1, SUM( CASE WHEN ( date(temp.max_created_at) BETWEEN ...


1

Create a set of staging tables in the target database. Write rows to these as they are generated, which seems to be one or two at a time. This can be inside a transaction. Once the whole batch (200 rows?) is in these staging tables use a stored procedure in the target database to move them en masse from the staging tables to the real ones.


1

I've seen similar problems addressed by decoupling the app from the central server: Remote sites install a local SQL Server Express along with the app Apps talk to the local Express instance. Low latency, good availability. SQL Server Express uses Service Broker to deliver the updates to the central server Service Broker handles the network availability, ...


1

I don't have your dataset, so can't test if this is better, but it feels better. After building the chains, we reverse them to find the 'childest' item in each chain, then join back to the original chain. -- The same as your first CTE ;WITH RelationshipChain AS ( SELECT ID, ParentID, ChildID, 0 AS Seq, ID AS RootID FROM Relationships WHERE ParentID = '' ...


1

Try this: with me as ( select * from users where id = 2 ) select u.id, u.popularity from users u, me where u.gender = (select gender from me) order by u.latest_location::geometry <-> (select latest_location from me)::geometry ASC limit 50; When I look at the fast plan here's what jumps out at me (bolded): Limit ...



Only top voted, non community-wiki answers of a minimum length are eligible