New answers tagged

2 votes

Simple query on sys.dm_db_partition_stats is running for 30 minutes

I know that this is an old post but I just ran into the same issue on an Azure SQL Managed Instance. I have very similar code running that checks a table that is partitioned by year and month and ...
Dave's user avatar
  • 21
0 votes

Performance impact when querying without partition key on a partitioned table

(To many questions to fit in a Comment.) I assume that id is not AUTO_INCREMENT? Please provide the query; many details could make a difference. In particular, I cannot answer your "why" ...
Rick James's user avatar
  • 77.6k
0 votes
Accepted

Need help identifying opportunities for improvement in table config and query design

Using this pastebin code to build test data. Using postgres 16.1. A btree index works like a phonebook, which I will use as an example. Entries are ordered by (last_name,first_name). Thus queries like ...
bobflux's user avatar
  • 1,401
0 votes

Need help identifying opportunities for improvement in table config and query design

For the index to be used, you should have product as the first column in your primary key. For the third index column to be used effectively with an IN condition, all previous index columns would have ...
Laurenz Albe's user avatar
  • 50.3k
0 votes

Table partitioning for data pruning purposes

DO NOT USE DELETE on that huge table; it could take hours and would probably overflow disk. And interfere with the other activity! Instead, Create a new table for the 60K rows you need to keep for ...
Rick James's user avatar
  • 77.6k
1 vote

Identifying Rows Where Conditions Occur

I don't have access to a Netezza instance, but I can give you a hint. Your problem is often referred to as an "island and gap" problem. A simplified setup: create table t ( name varchar(20) ...
Lennart - Slava Ukraini's user avatar
1 vote

Table partitioning for data pruning purposes

A solution that I have used in some of my projects is To flag the expired records. Use a procedure to insert a new record, which uses an expired record if it exists, otherwise it creates a new one. ...
Rohit Gupta's user avatar
  • 1,584
1 vote

Partition table and partition elimination

I've got a large table of about 300 millones rows. Ok, it's a decent size, nothing concerning though. My question is: if I partition by a datepart ,lets say a month,and adjust my quries to only ...
J.D.'s user avatar
  • 37.1k
0 votes

How do I design a table which has limited key diversity?

When modelling data in Cassandra, the primary objective is to design tables which exactly match the application queries. This means that you need to start the modelling process by listing all the ...
Erick Ramirez's user avatar
1 vote
Accepted

Storing terabytes of data in Azure SQL Server

Would the community have any inputs on my problem? Sure, these are my thoughts... a user item recommendations based on the user's purchase history, user health profile and item recommendations(...
J.D.'s user avatar
  • 37.1k
0 votes

Storing terabytes of data in Azure SQL Server

Sounds like an good case for Azure Synapse Analytics. https://azure.microsoft.com/en-ca/products/synapse-analytics
SergeyA's user avatar
  • 429
0 votes

Why has the file group for the partition grown in size after the alter?

If you would like to make it simple meta data fix, you could try parition switching to fix this. First, you need to create a new table using a partition boundary of 2023-12-01 (FG-01-2024), for which ...
S.D.'s user avatar
  • 746
0 votes

Why has the file group for the partition grown in size after the alter?

Seems the problem is that the FH_12_2023 actually contains November data, meaning the December data of 2023 doesn't have a FG_13_2023 to go to so it went to PRIMARY. Now that you defined January 2024 ...
Alocyte's user avatar
  • 422

Top 50 recent answers are included