I am trying to populate a multiple dimension tables from single Base table. Sample Base Table: Required Dimension tables : emp_name_dim with values – AAA,BBB,CCC emp_surname_dim with values – ZZZ,XXX country_dim with values – USA,IND,CAN dept_dim with values – CE,IT,MECH university_dim with values – U_01,U_02 Now to populate above dimension tables from base table, I am thinking of 2 approaches
Tag: query-optimization
PostgresSQL: Get first and last values of an entities chain (self referential table)
I have a table where entities are events. Each event belongs to a group. Each time a new event for this group is inserted, it refers last event of same group on the attibute field “prior” (self referential FK). The other attributes are the id of the event itself, the group which the event belongs to and if this event
how can i optimize this mysql statement, it is very slow
Answer This query is slow because it is actually 16 x 3 = 48 queries. You have 16 SELECT correlated subqueries in your main SELECT query, and you’re looking up three values of p._id. Your first step is to transform at least some of those subqueries into independent subqueries and JOIN them. (This is a vital skill for people who
Query using CASE in left outer join takes long time to run
I am running a Postgres query with a CASE expression in a join condition. The query takes a long time to run. Is there a better way to optimize this query? Code snippet: Answer For proper answer attach full query, table structure (with indexes) and execution plan. Original CASE is quite complicated, but hard to say if it’s responsible for
How to optimize my query speed (avoid using subselect for every row)?
I have a table called CisLinkLoadedData. Is has Distributor, Network, Product, DocumentDate, Weight, AmountCP and Quantity columns. It used to store some product daily sales. AmountCP / Quantity is the price for the product at certain date. There are promo and regular sales, but no flag for it. We can tell if certain record is regular or promo by comparing
SQL query optimisation by replacing subquery
The following query works and gives me the expected result but i’d like to optimise it. Is there a way to avoid the subquery, using only the conditions in the main query to arrive at the same result: PS: In PLSQL, NVL function lets us substitute a value when a null value is encountered Thanks for any input Answer A
Order by select count(*) and LIMIT is very slow
I have this query in my program, when I do some sorting with select count(*) field from the query, I dont know why, it very slow when running that query. The problem is when i do some ordering from posts_count, it run more slower than i do ordering with the other field. Here’s the query: select ‘tags’.*, (select count(*) from
Simplifying SELECT statement
so I have a statement I believe should work… However it feels pretty suboptimal and I can’t for the life of me figure out how to optimise it. I have the following tables: Transactions [Id] is PRIMARY KEY IDENTITY [Hash] has a UNIQUE constraint [BlockNumber] has an Index Transfers [Id] is PRIMARY KEY IDENTITY [TransactionId] is a Foreign Key referencing
Select all groups that contain all specified values in a certain column
Say I have the following table: CaseRef NotificationReason NotificationDate 123 SCHEDULED 2022-01-01 234 SCHEDULED 2022-01-02 312 SCHEDULED 2022-01-01 123 RESCHEDULED 2022-01-02 123 DECIDED 2022-01-03 234 DECIDED 2022-01-02 If I want to return only rows that have a CaseRef that has both a SCHEDULED and a DECIDED value in NotificationReason. CaseRef NotificationReason NotificationDate 234 SCHEDULED 2022-01-02 234 DECIDED 2022-01-02 123 SCHEDULED
Why does this SQL query get stuck in an endless loop?
The following PostgreSQL query gets stuck loading endlessly. I know correlated sub-queries can take long, but a SELECT using the same parameters worked quickly and returned the desired results. And I have a small data set that I let run for an entire day just to make sure it wouldn’t eventually work with time. Table_A uses hierarchical data structures and