Hot answers tagged

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 ...


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

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 ...


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 ...


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 ...


3

I've worked with a 30+ billion row monthly partitioned table with page compression and 10 years of history. The table schema was fairly simple with a datetime2(2) clustered index and 3 non-clustered indexes on varchar columns and a couple of non-indexed columns. Storage was about 2TB and it performed reasonably well. SqlBulkCopy was used to insert about ...


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 ...


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 ...


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 ...


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 ...


2

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

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 ...


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

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

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 ...


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 ...


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

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

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 ...


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


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 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 ...


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 ...


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 ...


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" /> ...


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 ...


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 ...


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 ...


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 ...


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 ...



Only top voted, non community-wiki answers of a minimum length are eligible