New answers tagged

0

What could have caused the difference in the cache percentage usage and throughput? MongoDB uses both file system as well as WiredTiger's cache when required Is it safe to change explicitly this settings so Mongodb uses more of its cache? Yes. It is totally safe to change the cache settings Can I do this setting change from the command line ? The setting ...


0

Single-table multi-tenant is logically what I am describing. However from reading about it I get the impression that "tenant" refers to clients / entities whereas what I'm describing is a general data access strategy. Multi-tenancy is a general data access strategy, in that it allows multiple tenants of the same database to use it as if they were the ...


-1

I can think of one additional benefit but it is mostly due to limitations of JPA, Spring and MySQL. MySQL defaults to case insensitive unless you use MySQL specific constructs like BINARY in columnDefinition or utf8mb4_bin thus causing portability / sorting issues. In which case the workaround was to create an index column containing the SHA-1 which will ...


1

Given these queries, is it useful to create a separate (non-clustered) index on just the BillID column? First, note that an index on BillId will include TaxCode. The clustered index key is the row loacator for this table, so every index must contain it. And for non-unique indexes, the clustered index key columns are added as index key columns to make the ...


1

It's like most dbms features, if you use it in the right situation it does it's job well, the wrong situation it does it poorly. Pros: Some things just can't be done without it. Typically I have only found this to be for administrative work, and not application code. Some system commands don't allow for parameters to be used as input. So for example if I ...


0

Try flipping this predicate from where object_name ( s2.objectid, s2.dbid) = 'sp_name' to something like where object_id = object_id('sp_name') You might get a simpler plan that aviods whatever resource is blocked in TempDb.


-5

Do not use Dynamic SQL. 99% of the time Dynamic SQL is used due to the lack of knowledge on how to use optional parameters in stored procedures, the rest 1% of the time is used to create a highly complex query for a report that the customer doesn't understand even. The Curse and Blessings of Dynamic SQL doesn't show an example of why it would be a good idea ...


0

As @a_horse_with_no_name and @laurenz-albe mentioned, there should be no reason a transaction becomes slower with number of databases queries. It turned out that the cause for the slowness was a query that updated more and more records each time.


-2

could you try this query: SELECT blocker.[spid], blocker.[Database], blocker.[Login], blocker.[Hostname], -- blocker.[HostPID], -- blocker.[ProgramName], blocker.[LastWaitType], blocker.[Blocker], blocker.[Command] AS [BlockerCommand], blocker.[SQL_Text] AS [Blocker_SQL_Text], blocker.[QueryPlan] AS [Blocker_Query_Plan]...


2

The document control management system needs to perform typical document control management system tasks...for many years (at least two decades). The end of extended support for SQL Server 2014 Service Pack 3 is 7/9/2024. That's 5 years from now. So I think your first step should be to see if / when the vendor intends to get certified on a more modern ...


-1

If the company's serial number contains characters other than digits, varchar seems to be the correct data type. If it contains only digits, an integral data type would be better, since it probably saves space and makes comparisons faster.


6

Network I/O is included in the rpc_completed duration*, so I would expect you'll see improvement in the workload you've described. I enabled TCP/IP on my local SQL Server 2016 instance, and then ran a series of queries through a .NET application that uses an ORM. Here's a comparison of sp_statement_completed and rpc_completed Extended Events targets for ...


0

In given query, all the parameters are Hard coded and only TOP 100 is require so it execute very fast. Also query Plan look fine because of this reason. If same query is executed using Parameter then only its true performance will be reflected. Then only very Honest Index Tuning can be done on it. there is no option to change the query text. As per ...


1

Is it because while decreasing oracle check each column size if it can be decreased? However increasing it, no need to do some checking? Correct. When you decrease the length, Oracle needs to check the values in all rows in the table if any violates the new (reduced) length - it effectively reads all rows in the table. This isn't necessary when you ...


0

Take a look at https://github.com/a-mma/AquilaDB it is a vector database to store Feature Vectors along with JSON Metadata. Keep it along with your RDBMS and do use metadata to maintain cross reference between data.


1

If we assume that your column is an INT then, as John's analysis shows, you will see a saving of approximately 80%, at the expense of some extra work joining things back together when reading the values. As you only need values from 0 to 9 you could get a 75% saving by simply redefining the column as a TINYINT which consumes one byte instead of four, ...


0

If what you care about is speed/performance then use bigint. A disk read access will read a block of bytes, so reading 8 bytes will be the same as reading 3 bytes. But since 3 bytes integers are not native for CPUs, mediumint needs to be converted before it can be used (memcpy into a 4/8 bytes integer) causing a overhead, while 8 bytes integers are native ...


2

It's important to know what sort of queries you are running. Are they mostly based on the time of the record, or something other field? That will inform what sort of approach you should take here. Maybe your indexes should actually be multi-indexes for example. What's your EXPLAIN statement say for your queries? That's really one of the best ways to see ...


0

If you had a lot of concurrent heavy queries, the replica could help spread the workload. Or if the heavy query was interfering with other work the master database needs to do. But if you don't run multiple queries at the same time and the problem is only that they take too long, there is no reason to think a replica would help that situation. make the ...


4

Indices are bound to fragment in course of usage (time). What matters is if it is affecting the performance. Usually we do not worry about of indices with page_count < 500. (We as in, my team)However,there is not hard and fast rule for a particular page_count.In my previous shop we had set the limit in our Ola script for 1000 page count. But yes with ...


3

Main difference between your Test server and Live server is Concurrency. You haven't done Concurrency Test in your Test server. Concurrency is one of the main reason for Live Site to be slow. What code is written in Front end application like what you have written in connection string . Like connection pool,whether connection is properly close after ...


11

Based on the data that you have provided - there are 67 total pages. Index fragmentation for such a small table would not affect the performance. I would not worry about index fragmentation for the table that you have mentioned. You should update your table statistics so that sql server can generate better query plan. I would start my troubleshooting by ...


-1

Create a new index that is based on movido,status, id_empresa, referencia, archivo To convert the query to a covering index


0

Since you said that , ,INSERTs are relatively rare (at most dozens of times per day, but typically much less than that), reads are very frequent (essentially on every web request to my application).INSERT performance doesn't matter too much, but I'd like to avoid horrible index fragmentation if at all possible Alter your Table to add RowNumber ...


0

Use Fulltext: FULLTEXT(postTitle, des) and AND MATCH(postTitle, des) AGAINST("+post +test" IN BOOLEAN MODE) Construct, and optionally leave out, any tests that are not requested. That is, don't include this line if there are only 20 rooms: AND (room >= 0 and room <= 20) Don't bother will bath >= 0 since it is UNSIGNED. Gather typial queries. ...


0

I ended up using a separate table to hold translation because maintaining the custom functions and translation blob is a non necessary complexity and is not a portable solution. CREATE TABLE IF NOT EXISTS Route ( id integer PRIMARY KEY UNIQUE AUTOINCREMENT NOT NULL, name text NOT NULL, nameLocalizationId INTEGER REFERENCES Localization(id) ); The ...


0

Is this faster? This might allow the optimizer to filter by STATUS before touching the other tables. It also reduces the three-table join down to two. UPDATE orders o SET orders.status = 'ORDERED' WHERE orders.status = 'PENDING' AND orders.order_id IN ( SELECT line_items.orderId FROM line_items INNER JOIN products ON line_items....


0

The table has an UUID (random, v4) primary key and gets the records from a central server - it's a local copy. Is the UUID fetched from the central server, or is it generated locally and then attached the rest of the data from the central server? If the first, can you fetch them from the central server in sorted order? First thought was "indexes", so I ...


0

Is this something you could break into smaller batches instead of all 6+ million at once? If the hardware is capping, I would look into segmenting these.


0

In my environment, we replicate a lot of tables from production server to the reports server. On reports server we have plenty of views that are based on replicated production tables AND are nested. Before replication starts, we have to remove all views to make replication possible (we use drop and create because tables structure often change in production). ...


-6

You can also see below web page on online examination database design help . https://databaseinmysql.blogspot.com/2019/04/introduction-of-online-examination.html


3

The Process: % Processor Time (Instance - sqlservr.exe) counter takes into account all of your cores. If you divide by the cores you get an answer much similar to what you expected. This TechNet article explains it in far more detail but it uses this calculation to get the number you see: (No of Logical Cores * 100), So this is going to be a calculated ...


0

There are business reasons for this, as in a person that has access can share a particular link with another to view details. Another reason is because it does reduce some overhead on queries in a different manner, as in determining if the person has the "right" to view a particular record. If you are already authorizing user,then Why worry about ...


0

As I experienced, the clustered index on the identity type column cause lot's page latching due to sequential/last page writing, as clustered index store data in sorted order. The behavior depends upon row size and number or transaction on the table. In one of the organizations where I worked, used to receive almost 35-40 GB / day 150-200 million ...


2

Is it worth having both int and guid in the table or should I just drop the int entirely? Yes, from the perspective that you can have the identity column be a unique clustered index to take advantage of inserts being append-only to the table, and you can have a nonclustered primary key on your GUID column for both security and uniqueness when locating ...


1

The canonical answer is to create sample data using both schemas, and profile the queries you'll be doing to see which schema runs faster :-) But I'm guessing you'll actually see equivalent performance. Mongo is pretty fast at querying subdocuments as long as you index the subfields (which you're doing in the first option). So the real way to decide ...


Top 50 recent answers are included