New answers tagged

0

From MySQL 8 , the transaction write-set extraction algorithm is introduced which is XXHASH64. The new default will make it easy for users to enable binary log write-set parallelization on master to speed up group replication.[1] If you are not using MySQL Group Replication, then you can change the settings from XXHASH64 to OFF. And you can enable binary ...


0

Sorry this startetd as comment, but it git to big This only means that the database server should run on a hardware where it doesn't share Memory, CPU and Hard drive with other services like webserver for example, or run in a virtual hardware with other virtual environments, which share memory and CPUs. The biggest Problems you will face are huge amount of ...


0

Neither, for MySQL: SELECT ts, DATE_FORMAT(ts, '%W') AS W, DATE_FORMAT(ts, '%a') AS a FROM ... +---------------------+--------+------+ | ts | W | a | +---------------------+--------+------+ | 2020-01-26 10:53:05 | Sunday | Sun | +---------------------+--------+------+ That is, just use an expression.


0

I solved it by using the last join example and was able to string together 8 different queries using this structure: select date, sc, fc, fc10s, fc30s, ... from (select date(hit_date) as date, ...) t join (select date(hit_date) as date, ...) t2 on t.date = t2.date join (...) t3 on t.date = t3.date ... It took about 20m to run, which is OK for a report and ...


4

Using bigint compared to int has, at least, these potential performance drawbacks: the data will use more pages on disk this can affect how long it takes to read data from disk when it's not in RAM it will also make any maintenance operations involving those fields take longer (backups, index rebuilds, CHECKDB) these data pages will take up more space in ...


2

I don't think there's a way to do that in SSMS. You should use Sentry One Plan Explorer for this. It's a free tool that provides an alternative way of viewing SQL Server execution plans. I've run this code on my local copy of the Stack Overflow 2010 sample database: CREATE PROCEDURE #sp_TestQuery AS SELECT COUNT_BIG(*) FROM dbo.Users u WHERE u.Id > 10;...


-1

In our case it was caused by a query in UPDATE trigger on one of the main tables in the database. The query was inserting into the log tables from temp DELETED table. We changed the query to use INSERTED table and it solved the problem.


1

There are only 2 or 3 rows in sys.dm_exec_requests with status = running A running SQL Server request will cycle quickly (perhaps every 4ms) between running, runnable, and suspended. running means that the task is actively using a CPU core (aka a Scheduler). runnable means that it need to use a Scheduler, but it's waiting for one to be available, and ...


1

For this problem I would use the date formatting functions (after setting lc_time to the appropriate locale) SELECT to_char(timestamp, 'Day') FROM fact_table In general the table form is, to me, cleaner (when there is no suitable function) Perhaps with an in-line table if there is a good reason to not have a permanent table. SELECT day_name FROM ...


2

The query simply has to read the 98175 rows which are located in almost as many 8KB-blocks, so there is no way around reading all these blocks, and if they are not cached, that is going to be slow. Your only chance to make that faster is to make sure that the rows are lumped together in fewer blocks. That could be done by rewriting the table like this: ...


1

here are a couple of ideas. One does the constraint check in a function. the second modifies the table, creates a trigger to add in the missing data and creates a new index on the three fields that have to be checked CREATE TRIGGER check_jsonb BEFORE INSERT ON data FOR EACH ROW EXECUTE PROCEDURE CREATE FUNCTION public._check_jsonb()...


1

If the uniqueness constraint is the only issue (and I'm interested to learn more about why in the discussion above), here's an idea: remove the uniqueness constraint when you do reads (selects), do order by id asc limit 1 so that you ignore duplicates have some sort of parallel process going through the table periodically and removing duplicates


0

From an article on the internet: (emphasis mine) The Processor(_total)\ % Privileged Time counter shows the percent of time that the processor is spent executing in Kernel (or Privileged) mode. Privileged mode includes services interrupts inside Interrupt Service Routines (ISRs), executing Deferred Procedure Calls (DPCs), Device Driver calls and other ...


1

I would go with TINYINT for these reasons: You might come up with other modes - for example, "verifying" Bit can't be aggregated - that usually comes up with PIVOT function, where you have to aggregate - you first need to cast to int family type. Single bit column doesn't actually provide storage savings - it still takes a byte (the first bit in a byte) - ...


1

It's highly unlikely that there is a material performance difference here. So the choice is between create table AppUser ( Id int identity primary key, Name nvarchar(200) not null, UserAccepted bit null ) And create table UserStatus ( Id tinyint primary key, Description varchar(200) ) create table AppUser ( Id int identity primary key, ...


6

Multiple parallel threads for the same query all share the same session_id, or spid. Kendra Little did a great write-up showing that here. Brent Ozar Erik Darling also has a great, if only tangentially related, post about it here If your query is blocking itself, that indicates one thread is taking longer to complete its work; this might mean you have out-...


0

So I guess my performance impact comes down to the following Since your question is about performance I advise you to start using performance tools that you already have at your disposal to dig into this problem. This way your guess will be more precise or better yet: you won't have to guess at all. Display an Actual Execution Plan SET STATISTICS TIME (...


0

Try joining to arbancdb.gdw_replica in your FROM clause (use a LEFT JOIN to avoid filtering. That way you’ll have access to those columns you use in your CASE statements.


1

Can I do this against a live production table, or do I need to bring down the database? Yes. You can do it against a live table. Will even be very fast since citext and varchar are binary coercible, so no table rewrite is required (since Postgres 9.1). But this still acquires an ACCESS EXCLUSIVE lock on the table, which makes any concurrent access on ...


0

If you want a simple test, I would identity the top 5 most frequently executed insert queries on the table on your live system. Then on Dev, switch on the client statistics in ssms (https://www.brentozar.com/archive/2012/12/sql-server-management-studio-include-client-statistics-button/) and note the total execution times taken when you run the queries before ...


2

I want to know the methodology of testing the writing speed. I am assuming you want to know if queries are running faster. It can be read, write, or a combination of both. There are many approaches you can take. It depends on the usage pattern of the table. I am sure I won't be able to list everything, but the following points will give you enough tools to ...


2

Keywords in square brackets in the synopsis of SQL commands in the manual are optional. And in this case just noise. Add them or leave them, no functional difference. (The verbose form SET DATA TYPE conforms to standard SQL.) bigint occupies 8 bytes, integer occupies 4 bytes. A table rewrite is inevitable. The manual: As an exception, when changing the ...


0

Response time heavily dependent on the query that you're executing & how you're executing them. Linear scalability is achieved only if you're performing reads by primary or at least partition key. If you're doing something like SELECT * FROM table WHERE non_pk_column = 'something' ALLOW FILTERING or SELECT * FROM table, then Cassandra will need to get ...


1

Thanks to everyone's comments and suggestions, I tried all of the ideas @mustaccio posted, but in the end the fastest strategy was to abandon the mapping table altogether, and just go with a big ugly CASE statement to handle all of the situations, and insert that into another staging table. Execution time dropped to only a few seconds, and I can't argue with ...


0

You can use i.e. GNU Parallel which provides possibility to run arbitrary commands/programs at the same time, the syntax would be something like: parallel psql -h localhost -d databasename -U user -p port -a -q -f input{}.sql -o output{}.txt ::: 1 2 this command will execute 2 queries located in input1.sql and input2.sql at the same time. Another option ...


3

On my 2019 instance, clicking the Display Estimated Execution Plan toolbar button and expanding the execution plan XML for a given query, you should see CardinalityEstimationModelVersion="70" (old) when using the FORCE_LEGACY_CARDINALITY_ESTIMATION hint and CardinalityEstimationModelVersion="150" (new) when NOT using the ...


0

The issue is: you do use index but the number of rows returned and should be sorted is too big to fit into your instance type memory. So you can create index on shop_id + updated_at columns as btree index is always sorted. Unfortunately in your case the shop_id isn't selective enough so in addition to that the hash partition on shop_id might be useful.


2

Your view gives the correct answer, while your CTE gives the wrong answer, using the oldest date, not the newest. If you want to use an index scan (although in my hands it doesn't actually make a difference in performance), specify DESC for both ORDER BY columns, or create an index for (currency, date DESC).


Top 50 recent answers are included