I am hoping someone here can help me. I have been given this query that takes a long time to run (over 30 min). I have been trying to rewrite the query but with no luck thus far. I am posting an example of the query so that you can get an idea of what it is I am trying
Tag: sqlperformance
Is it better to pivot data in SQL or in my application code?
I have a very long & narrow table in a MSSQL Database, one that looks a bit like: date dataItemName dataItemValue 2021-01-01 Units Sold 20 2021-01-01 # Customers 2948 2021-01-01 ARP 19 2021-01-02 Units Sold 146 2021-01-02 # Customers 157 2021-01-02 ARP 32 And I’m trying to get a table of the form: d…
Sql Performance join Vs co-related Subquery
TABLE : employee (id,mid,join_date) Question – Find all employees who joined before their managers Query 1: Query 2: Which of them is correct? If both correct, then how is the performance? Answer They are both correct. Performance questions need to be tested on your data on your system. However, with a …
Optimizing MySQL-Query for removing duplicates and adjusting foreign key references
i have following tables:address_table and customer_address_table The address table stores addresses and the customer_address table stores the relation between customer and address. A customer can have multiple addresses, therefore the second table. In the address table there are duplicate rows (different id b…
Why to use Correlated Subqueries?
As far as I’ve seen, correlated subqueries can be re-written using multiple-column subqueries or joins. And they usually perform better than correlated subqueries. So in which possible scenarios a correlated subquery can be a better option or the only option? (I use Oracle Database) Answer Oracle has a …
Optimization of Oracle function
I wrote a function in the oracle database that calculates saldo and finds the date of the operation. Here’s how it works: He will receive Start subtracting the first cell of TURNOVER_DEBIT column …
How can I efficiently paginate the results of a complex SQL query?
I have a fairly complex SQL query which first fetches some data into a CTE and then performs several self-joins on the CTE in order to compute a value. Here’s an abberivated exmaple, with some complexities of our application simplified: The query is auto-generated and can scale to a complex computation …