Skip to main content

New answers tagged

-2 votes

SQL Server Disk throughput slower than diskspd

I think you need to run diskspd tool using 8k, 64k blocks and compare the results. Using 2MB block size for OLTP workload is non-sense. Try: 8 KB block size => resembles the page size that SQL ...'s user avatar
3 votes

Trouble with slow stored procedure performance on 35M rows with CTE/Temp table

This is a classic Kitchen Sink Query. While you could add OPTION (RECOMPILE) as a quick fix, this comes at a cost of recompiling on every run. Instead, you should build a dynamic query. Don't forget ...
Charlieface's user avatar
  • 13.6k
0 votes

Manage the amount of data on tables

10M rows/year is less than 1/sec. 100 simple queries per second is not a problem What do the SELECTs look like? If they are getting slower and slower, then they are probably doing "table scans&...
Rick James's user avatar
  • 78.6k
1 vote

PostgreSQL uses slow Index Scan instead of Bitmap Heap + Index scan with ORDER BY and specific LIMIT

Fastest solution for skewed data would be partial index. In your case an index like below will be fast as well super compact. You can tweak and test the index by changing index column or filtering ...
mediocre's user avatar
  • 168
2 votes

PostgreSQL uses slow Index Scan instead of Bitmap Heap + Index scan with ORDER BY and specific LIMIT

PostgreSQL has two choices to process the query: it can use an index for the WHERE clause, then sort and return the first few results (that's your fast plan) it can use an index for the ORDER BY ...
Laurenz Albe's user avatar
  • 53.8k
0 votes

Optimizing a recursive CTE or replacing it with a temporary table

Discussions on optimal ways to store, represent and query hierarchical structures aside, the first optimization that I see is your use of IN, for large sets that can kill performance. Have you tried ...
Jonathan Fite's user avatar
1 vote

How do we optimize a transaction-log VIEW in MySQL that will use a LIMIT when executed?

I see that joining the view to a subset of key values appears to avoid the full table scan. This could be a workaround.. but shouldn't be necessary if the optimizer worked as I think it ought. e.g. --...
Rob R's user avatar
  • 9
0 votes

Question related to Database Design in SQL Server

resulting in a not so optimal performance How so? If I'm inferring the situation correctly, you'd probably want to have on Product: PRIMARY KEY CLUSTERED (ApplicationId, IsDisabled, ProductId) This ...
Steve's user avatar
  • 529
4 votes

How to optimize query with multiple many-to-many relationships

It would seem you can radically simplify to: SELECT e.* FROM events e WHERE EXISTS ( SELECT FROM events_assets ea JOIN groups_assets ga USING (asset_id) WHERE ea.event_id = ...
Erwin Brandstetter's user avatar
0 votes

Oracle optimizer does not use column group information from multiple tables and misses correlation

Oracle, and most other DBMS I know of, will only gather statistics on the content of single tables. It can collect statistics at the table level (number of rows) and at the column level (number of ...
Andrea B.'s user avatar
  • 1,613
1 vote

How to Make Queries on a DATETIME Column Efficient If My Primary Query Pattern is an Hour?

SQL Server and Oracle are different products. SET IDENTITY_INSERT table_name OFF is SQL Server syntax which has no equivalent on Oracle , the most similar might be generated by default as identity ...
Ergest Basha's user avatar
  • 4,000

Top 50 recent answers are included