New answers tagged

0

Have you run runstats recently for at least the relevant tables and indexes? Are you sure you have index(es) to support your monthly batch? Have you checked the bufferpool hit ratio when the batch is running? db2top and db2advis may help you answering these questions. The secret optimizer was surely changed and it may see tiny details differently than the ...


1

Table definition A low hanging fruit first: The UNIQUE constraint details_id_key is a total waste of resources. It adds nothing useful over the existing PK details_pkey. Where did you get these noisy DDL statements? All the redundant default clauses cloud the view. After trimming the noise: CREATE TABLE public.details ( value numeric, created_at ...


2

the query does not return 43 rows but 1 row that contains the number 43 the execution time of a query does not depend on the number of rows it return but on the number of rows it inspects. from your query plan you can see your query makes a full table scan. This means it reads the table from begin to the end and reads all 16 millions of rows of the table ...


1

The answer to this question varies depending on what database engine backend you're using. If you're wildly unfamiliar with all of them, I really recommend that you use TokuDB, because it's entirely self-configuring with no modifications necessary out of the box. I use MariaDB (MySQL clone) with Aria tables. My configuration would be totally, and ...


2

Try creating the following index: CREATE INDEX s_contact_idx_001 ON s_contact_lupd_lastupdby_name ( last_upd, last_upd_by, last_name ); You should change the name to correspond to your naming convention and make sure that it is unique.


2

According to the statistics provided user I/O wait time is 6172 out of db time 8383. When you see "user I/O" as a major wait event, SQL tuning is the best answer, specially adding missing indexes. Since the plan shows it is using full table scan you can add indexes in the column used in where clause.


2

type int(11) NOT NULL COMMENT '1. chatter, 2. marketing', -- Change to TINYINT UNSIGNED. This will save 3 bytes per row. Ditto for several other fields. And for those fields that are also in indexes, especially the PK, that "3" gets multiplied. bigint(20) unsigned -- If you are not expecting more than 4 billion, save 4 bytes each by switching to INT ...


1

Do you need to handle this directly in the database? My inclination, since you're ok with slightly stale data, is to cache individual query results, rather than the table as a whole, in a layer like memcached or redis. This is a pretty standard approach in web application development. The primary downside is that it requires development effort on the ...


1

1000 transaction per minute = 16.67 / second = 480,000 / 8 hr day 16.67 / second is not the fast. I am getting over 100 / second on just a regular active big table. Pick your PK or at least one index that you can sort the incoming data by so you have minimal fragmentation of that index. If you can hold records to insert 100 or 1000 at a time and ...


6

For SQL Server 2014 and later my recommendation is rather radical: switch to a clustered columnstore index. 1000 records/min is well within the range of columnstore bulk load capabilities, on even modest hardware. See Clustered Columnstore Index: Data Load Optimizations – Minimal Logging and SQL Server clustered columnstore Tuple Mover. The query performance ...


2

If it's just a single table, think about the idea of having a second copy of the table used just for reporting purposes. I wrote a two-part series about my solution here: Part 1 | Part 2. Essentially you have a table that represents a copy of your transactional table, but it is optimized for your reporting workload (as such, perhaps it only has a subset of ...


1

I think Daniel's answer is probably better than mine, but just to give you the basic alternatives: Transaction replication with only that table replicated, to a different server. Pros: Instant, readable data Read locks will only block the replicated server Transactional replication is read through your transaction log, with an Agent reading all ...


2

Without touching on the obvious hardware possibilities and HA solutions, I would consider building a "staging table" which is minimally indexed or even a heap, where you could offload incoming transactions with maximum performance. A scheduled/recurring process could then asynchronously move that data into the main fact table, which could have indexes that ...


2

Using GB or MB wont work. However, any variable that you needs to be defined as a numeric value can use the shorthand byte suffixes K, M or G. From the docs: For variables that take a numeric value, the value can be given with a suffix of K, M, or G (either uppercase or lowercase) to indicate a multiplier of 1024, 1024^2 or 1024^3. (For example, ...


2

As mentioned in the comment by jkavalik constraints are there to enforce data integrity. While most modern optimizers can use the information in constraints to help make access decisions that is not their purpose. Here is a question you need to ask yourself - if the integrity of the data is not important - then how important is the data? If it is worth ...


3

There is absolutely nothing wrong with joining on columns that are not PKs/FKs. If you are concerned about efficiency then the key is to have appropriate indexes defined to support the join operations you are using. Also, don't assume that the existence of a foreign key implies the existence of an index - some databases automatically create such an index but ...


1

I recently experienced this same issue which brought me to this page. @MartinSmith was on to something when he recommended updating your statistics and explain plan. I will like to add that you should also try to ensure you take a look at running jobs/queries which may create locks and thereby slow down response time. In my case the culprit was the job ...


0

if your tables are innodb you can use https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html which will optimize the table without blocking it. It copies the table in chunks to aovid locking and sets trigger that update the copy table in all the changes that happen while optimizing. You can run dry-runs to make sure it works before ...


-2

I guess it's a locking issue. While such inserts take so long use admin view below. The column HLD_CURRENT_STMT_TEXT shows the blocker statement (what cause the insert took so long): db2 select * from SYSIBMADM.MON_LOCKWAITS


0

I had this same issue on my database. I had a table that was constantly being updated and used INSERT...ON DUPLICATE KEY. The updates would take longer and longer and the connections would build up in the queue until mysql crashed. I tested innotop, instat, reinstalled and restored the tables from a snapshot and nothing worked. Finally I tracked down ...


2

For the time being I have settled on not normalizing the string arrays, and just keeping them as string columns as it has not been a problem so far. While I realize that I do not have any userbase yet to accurately judge performance, I have come to realize that it might be premature to try and performance optimize this problem until I run into actual ...


1

Lots of good answers already. I just want to add a rule of thumb and a worst case scenario. Rule of thumb: if an index is not used frequently by SEEK operation, it can be considered "bad", and should be revised or removed. Worst scenario: a clustered index in sql server is composed of GUID (non-sequential) column, and thus frequent inserts may cause ...


7

Well, I think you have some mixed concepts: An index improves performance of READ OPERATIONS ( those of SELECT ) while increase the processing time of INSERT/UPDATE OPERATIONS ( So they don't improve all CRUD operations, as you've heard ). As each time you insert a new row, you should update the index, if you have too much indexes you are increasing the ...


1

This is probably best to insert all items at once or at least avoid have to split string on the SQL Server side. You could convert json to xml similar to this: <Order OrderId="" OrderDate="" CustomerId="" Price="300"> <Items> <Item ItemId="103" Qty="2" Price="100" /> <Item ItemId="123" Qty="2" Price="100" /> ...


2

Trying to stay database-neutral: Reading, filtering Indexes radically speed up ordering and filtering operations on a table - often by a factor of 1000 times or more. Compared to a phone book, an index lets you look up a single person up directly, because it's alread sorted alphabetically. If the phone book were just an unordered list of a million names ...


3

Having too many indexes can indeed cause performance problems. If many indexes have very similar statistics it is possible that the optimizer cannot reliably decide on the most useful choice of indexes. (I learned this when working with a database where almost every column was indexed.) In that case, we reduced the number of indexes significantly by ...


2

Community Wiki answer generated from a comment on the question by @a-horse-with-no-name You might want to look into Postgres' hstore data type. A very efficient (indexable!) key/value store. Plus it has index types that efficiently support like '%ab%' PostgreSQL hstore documentation


0

Despite of the database you are using, normally you don't add thousands of fields to a table. Your second model is more 'normalized' ( and it's easy to index ), and should work better ( I'm not an expert, anyway ). My opinion is based on normal limits of databases, that are not meant to work that way, so you're going to hit some inner limit of the engine, or ...


1

Vertica query performance depends highly on the predicate used in the query . To get the gist of your performance , try getting the projection name of the selected columns of the query you are firing . The columns in the order by clause of the projection is very important in deciding the performance of your select. you can get that by running explain on ...


8

Couple of things .. Stop using the undocumented sp_MSforeachtable From Erin's blog - Understanding What sp_updatestats Really Updates sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows Note that ...


1

SQLIO is technically deprecated - it's been replaced by the newer utility Diskspd. However, the same basic answer applies. If you look at an existing server and guess how much storage throughput it's USING, then look at the counters: IOPs - Physical Disk Reads/Sec and Writes/Sec IO size - Physical Disk Read Bytes/sec divided by Reads/sec (and same for ...


8

The warning is there because of the XML function value(). The second parameter to value() is what you want the value stored in the XML to be converted to. You could argue that this is not in fact an implicit conversion but a very explicit conversion since you are asking for it to happen. Perhaps something for a connect item to suggest to Microsoft. Simplest ...


8

While I agree with @Kin about data types, I don't think this warning is as troublesome as you think. You're performing grouped concatenation, which is going to be orders of magnitude more expensive than any conversions anyway (and as Daniel said, unless your catalog views are massive - as in larger than physical memory - it is unlikely to affect anything in ...


3

The warning in the query plan means that because you have an implicit datatype conversion, SQL Server won't be able to accurately guess the correct number of rows returned, which in turn might lead to a less-than-optimal plan. This is important in queries that have to perform well, normally because they work with a lot of data, but in your situation, this ...


1

I've never handled anything like this before but why not do a combination of the +1 and the decimal versions. Do the decimal versioning while they are editing .. then at the end of an editing session (when they do a final save) go ahead and re-write all of the values as integers. Presumably in any given editing period there won't be so many edits that you ...


0

We have had this answer back from AWS Support which shows where the bottleneck is occurring: If you would like to reach 250MB/s, the maximum throughput of r3.4xlarge, you can change the volume to Provisioned IOPS(io1). IO1 has maximum throughput of 320 MiB/s. Another approach is to build RAID 0 array with 2 gp2 EBS volume. You can see the guidance from link ...



Top 50 recent answers are included