Tag Info

Hot answers tagged

2

Your plans show that the only option for looking up a scalar id in the table is a scan. This clearly indicates a missing index. Your code is incorrect under concurrency. Query plans have to work for any parameter value. To give an analogy, consider this example in a non-SQL language: int x; void f(int i) { if (i != 1) x++; } You are asking the ...


2

Simplified query to make it readable: SELECT * FROM "Follows" f JOIN "Users" u ON f."followeeId" = u."id" WHERE f."followerId" = 169368 ORDER BY f."createdAt" DESC LIMIT 1000; Your index follows_followinglist_followerid_createdat_idx looks good for the job. In Postgres 9.2+ it might get a bit faster if you append "followeeId" to the index - if ...


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 = '' ...



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