The temp table is probably preferable. Parsing the same UPDATE statement one million times is going to get slow. Opening and closing transactions one million times can also be slow, but you can put all updates into a single transaction if you want. By having separate update statements you are basically forcing it to use a nested-loop plan, where the loop ...
I would think that Option 2 is more preferable because of the following:
Less overall writes to the database (no extra table required)
Less overall WAL activity (Option 1 involves both an INSERT/COPY and an UPDATE for each row involved)
In either case, you could encounter some table bloat, in which case you'd want to make sure you VACUUM after doing this ...
For a general-purpose or OLTP design, the initial index design should be more conservative:
Clustered index on Primary Key.
Unique non-clustered index any other unique keys.
Index supporting each Foreign Key (where not already covered above).
Then, for very large tables, consider changing to a Nonclustered Primary Key and a Clustered Columnstore.
Please try this:
DELETE FROM journal
WHERE id NOT IN (
WHERE j.created_at >= 636742944000000000
ORDER BY j.created_at DESC
Please make sure to create the index on created_at and id.
In additional, I think id in a table should be unique/primary key.
For exposing it to public, we kept ID field. Its user friendly to see an ID vs UUID in URL. We replaced ID field from primary key and introduced UUID primary key. ID and UUID both exists for different reasons. For searchabilty, showing in URL, we still continue showing ID field.
This suggests something similar: https://stackoverflow.com/questions/52414414/...
What is currently happening
When running your query, the table scan, stream agg & compute scalar operators are not evaluated at runtime.
Why is it happening
The apply NL join means that for each row in #Docs, return a row from #Docsitems that matches the predicate. This predicate should be WHERE IDDocs = D.ID
But the compute scalar operator (EXPR1007)...
The other answers make valid points & I've up-voted them. I'd like to address some of your other concerns.
"I understand that there are some rules .. but .. this just seems too much".
The point of normalization is to remove update anomalies. Ideally if one fact in the real world changes then one column of one row in one table must change. If the schema ...
Analysis of VARIABLEs and STATUS:
16 GB of RAM
Uptime = 61d 02:53:06
Are you sure this was a SHOW GLOBAL STATUS ?
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.
The More Important Issues:
How much RAM? (This analysis assumes 16G.)
Your machine is generally quite ...
Don't delete more than 1000 at a time. All the rows being deleted are saved in case of a crash (or reboot) so that they can be restored. (cf Atomicity.) This also explains why the table was non-responsive after the reboot.
Index updates are delayed (cf Change Buffering). This may explain why subsequent deletes got slower -- the updates to the indexes ...
Is this an application that was developed in house? Or was it a packaged application that the company purchased (and potentially customized)? I'd generally expect that a "database for an eshop" would be purchased rather than being built internally but that's far from a guarantee. If this is a packaged application, this is quite normal.
Add composite keys:
games: INDEX(home, active, id)
games: INDEX(guest, active, id)
gamegroup: INDEX(team, game)
Shrink the data:
INT always takes 4 bytes. TINYINT UNSIGNED takes 1 bytes and is probably more than adequate for period, active etc.
If gamegroup is a many-to-many mapping between teams and games, you probably don't need id. Instead:
How many of the tables are not connected to any other table by a FOREIGN KEY relationship? You can do this by checking out the answer here.
It is possible to have tables with 0 records - if you ran a nuclear power plant for example, you'd want the table catastropic_failure to have 0 records.
Some reference tables could only have 1 or 2 reference codes - ...
if I have to access same table multiple time in the stored procedure is it a good idea to load the data in temp table and access it rather than the original table?
It sometimes is. It is a common performance optimization to "materialize" or "spool" intermediate results into a temp table, if putting the logic for returning the intermediate results in the ...
BACKUP DATABASE successfully processed 19696388 pages in 1945.648
seconds (79.088 MB/sec).
The speed you see here is a result of simple division of the whole backup duration per volume of data processed.
In your case backup duration is 1945.648 s, data volume processed is 19696388 pages * 8Kb / 1024 = 153.878,03125 Mb
The speed = 153.878,03125 Mb / ...
Lets first get the concept of reader / writer threads out of the way
During a backup, SQL Server creates one reader thread for each volume
that the database files reside on. The reader thread simply reads the
contents of the files. Each time that it reads a portion of the file,
it stores them in a buffer. There are multiple buffers in use, so the
Nice to meet you, Mischa. We discussed this in person but I thought I'd put some ideas out here for anyone else to reference and we could continue some discussion here. ;)
Looking at your information provided above, the thing that stands out most to me is that you have 8 total NUMA nodes, but only 4 of them have memory attached to them. I don't know too ...
CXCONSUMER occurs when a consumer thread waits for a producer thread to send rows.
CXPACKET occurs when trying to synchronize the query processor exchange iterator or getting a required buffer.
CXCONSUMER waits are generally benign, encountered as a normal part of parallel(CXPACKET) execution.
I think you should look into log shipping once you get it setup your 10 minutes logs can be shipped to the server, so only changes are updated.
Assuming the log shipping destination is also where you want your backups kept, you can do backups there. Other then the initial backup, you can run for years and only ship the logs.
You could take full backups ...
You will need to define an index on the voteup_count column for the query to be performant. Without this index, this query will be scanning all the rows until it reaches the first value of the Range condition.
Assuming that the data tree is in such a way that rows with low voteup_count values come first (basically, in ascending order). In that scenario, ...
I will write an answer, however really it depends on infrastructure you have, or you can afford:
Why keep read only and transaction data in one database? Maybe read only data should be on separate set of files/database/disk/servers. I don't believe you are reading continuously 500 GB data file all the time and all over. Separation gives you ability to ...
No, I think you are doing the Right ThingTM.
Performing a bigger query to get all the data you need in one round trip almost always beats fetching the data points one by one (“nested join implemented in the application”).
Your normalized database design is perfect for a transactional application that performs data modifications and small ...
It might not be direct answer to your question: whether to increase/decrease MAXDOP?
But both cannot be solution when you look at the problem and think out of box. I believe, it's time to replace LS with alternative (as anyone of following).
Developing ETL Packages that transfer the data from primary to secondary
Schedule Copy Database wizard with ...
Which join is better performing if all of them provides the same result?
I addition of the previous answer, for what I know, MySQL is optimized to have the same performance.
With good indexes, for example, JOIN vs LEFT JOIN + WHERE clause to filter, will be the same thing. Optimization vs Human Reading take sense on large queries with lot of joins.