New answers tagged

0 votes

Postgresql with just index storage

The way PostgreSQL stores data, it keeps transaction information in the heap table so it is impossible to store data just in a BTREE. The heap is necessary. To have a high transaction rate, Postgres ...
cevian's user avatar
  • 11
2 votes

SSMS User Mapping Slow to Load, or Not Responding

TL;DR - Ensure you have a robust user/login clean up when restoring a database from one Windows domain to another!! Full Answer: As mentioned in my question, the issue resides in our DEV environment ...
L.Moy's user avatar
  • 45
1 vote

How to determine if it is necessary to increase the sga and the pga in Oracle database

This query will show you what the database would like for the SGA. A similar query is possible for the PGA. SELECT TO_CHAR( al.execution_start, 'dd Mon yyyy HH24:MI:SS') AS finding_time aa....
Strider's user avatar
  • 11
1 vote

Why Do Primary Keys on Temp Tables Improve Performance Much More Than On Table Variables?

In facts table variables are close to the concept of ARRAYs that does not exists in SQL Server. Temp table triggered a recompilation every time there is a DDL statement (CREATE, ALTER, DROP) and when ...
SQLpro's user avatar
  • 489
-2 votes

Why Do Primary Keys on Temp Tables Improve Performance Much More Than On Table Variables?

Generally many factors affects on your query performance and i try my best to mention some of that and link to source for more explanation : Primery Key when we create primery key we create ...
Sayadian's user avatar
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,436
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.4k
0 votes

Why Do Primary Keys on Temp Tables Improve Performance Much More Than On Table Variables?

Table Variable : Suitable for small dataset. Temp Table : Suitable big dataset. Table variable performance are better when there is small dataset.When dataset is big then table variable do not perform ...
KumarHarsh's user avatar
  • 1,628
0 votes

Why filtered index on IS NULL value is not used?

I had the same situation. To me solution was to include the needed column (the one from the index filter) into the INCLUDE fields. I find it cheaper than putting it into directly searchable part of ...
userfuser's user avatar
  • 149
0 votes

Techniques for increasing speed of simple select query using DBI R package to MS SQL server where ASYNC_NETWORK_IO is the bottleneck

To piggyback off of J.D's comment, "select less data" ... if your application is aggregating, consider doing that in SQL instead. SQL Server can do that more efficiently and there will be ...
Frank DeWysockie's user avatar
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.2k
0 votes

Index for constantly updated rows

Any table update which touch a field included in the index is causing this index to be updated as well. So no, it is not really a "high-stress". Just a regular amount of work. If you update ...
White Owl's user avatar
  • 797
0 votes

Is there known such big perfomance diff between current MySQL and MariaDB?

According to your timings, each row takes several milliseconds. Pretty much the only thing that can be this slow is flushing transaction logs to disk on commit, which means you did not execute the ...
bobflux's user avatar
  • 1,436
1 vote
Accepted

Postgresql super slow on server - super fast locally?

The "waste" looks like only 32 bytes per tuple, which doesn't seem particularly egregious. I wouldn't take any action or reach any conclusion based on that. Their own documentation says ...
jjanes's user avatar
  • 39.4k
0 votes

Help with high CPU on Oracle Rac node caused by Oracle processes with *_so and *_ag in the process command name

I see that your answer is already answered, but I want to add this small hint anyway: "top -c" will show the complete command, sometimes 15 digits are just not enough.
JensG's user avatar
  • 1
2 votes

Is there known such big perfomance diff between current MySQL and MariaDB?

You touched on something that might run a lot faster and simpler in MariaDB: INSERT INTO your_table (id, val) SELECT seq, FLOOR(RAND()*POW(10, FLOOR(15 * RAND()))) FROM seq_101_to_100000; ...
Rick James's user avatar
  • 77.7k
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.2k
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

Top 50 recent answers are included