Tag Info

New answers tagged

0

I would have 2 columns: one for the description and one for the keyword. I would probably use MyIsam (depending on which Mysql version you're using) and use Mysql FullText search features. Innodb supports it now. I would test with both and see which ones is most performant.


2

It might be related with leaking connections exhausting the connection pool. If the connections are not released properly they will remain active and will not be returned to the connection pool until the next Garbage Collection executes. If a lot of connections are opened in a short time the connection pool might fill up before the GC runs, and the following ...


2

On your indices Ok, first things first. Assuming some structure like this { _id: new ObjectId(), date: new ISODate(), message: "Hello, Multikey Indices!", tags: ["MongoDB","Indices","Multikey"] } indexing tags would result in a multikey index. For the document above, the index would have three entries: "MongoDB", "Indices" and "Multikey", all ...


1

You may do deletes in batches from some script: 1) mark account for deletion (seems you are doing it right now) 2) in script - select one user for some marked account and delete it - as you expect many users per account, this will delete only small portion of all messages and attachments so should be fast enough 3) repeat 2 with remaining users of a given ...


-8

Joins is not recommended if you need good performance by your SQL query. Join will consume Memory and resource. Therefore if you are connecting big tables then it will take much time to finish given search process. Inline query also not recommended since it will save temporary table of given inline query output.


0

Have you considered using either mydumper or myloader? In tests that they have ran, it appears to have a significant effect on processing time. You can find more information here (https://www.percona.com/). Or is this a giant CSV type file you are trying to import?


1

With MyISAM, an INSERT/UPDATE/DELETE will request an exclusive lock, and have to wait for any SELECTs to finish. If another SELECT comes in after the write, then it will be blocked waiting for the write to get its lock and finish its action. That is, one simple write can snowball into the mess you are seeing. Changing to InnoDB is likely to avoid the ...


4

There is no hidden impact in raising the cost threshold for parallelism. As low as it is, SQL Server ships with a default cost threshold of 5, which is always taken into account when the optimizer compiles query plans. So, in this regard, the amount of work for the optimizer is the same. Whether raising the CTFP to a very high value is a good idea or not, ...


4

For Microsoft Dynamics CRM database server instance, you have to set MAXDOP 1. If using Enterprise edition, above answer using Resource governor makes sense. You can also look into using query hint OPTION (MAXDOP N) where N is a sensible value calculated based on number of logical processors and NUMA nodes of your sql server instance. Make sure that ...


5

There is an easy solution if you have Enterprise (expensive) Edition in use: Set maxdop at instance level as high as you want and use the resource governor to restrict all CRM users with their own workload group to maxdop 1.


2

I would recommend taking a look at the resource governor... In an environment where multiple distinct workloads are present on the same server, Resource Governor enables you to differentiate these workloads and allocate shared resources as they are requested, based on the limits that you specify. These resources are CPU, physical IO, and memory. By using ...


1

Community Wiki answer generated from comments on the question, from Bob Klimes, Sean Gallardy, and Aaron Bertrand. You'll need to enable Instant File Initialization (IFI) on the server holding the disk. Restart SQL Server for it to take effect. You can test if IFI is working on the share by creating a database on the share with trace flags 3004 and 3605 ...


1

Community Wiki answer generated from comments on the question by Aaron. Unless you are having performance issues, the high percentage of CXPACKET waits may only be an indicator that a large portion of the queries are going parallel and not actually a problem. What is the CXPACKET wait type and how do you reduce it? High CPU could be a indicator, but ...


1

SELECT u.usrid, u.username, u.rank, u.extras, x.dtime FROM ( SELECT userid, MAX(datetime) dtime FROM log GROUP BY userid HAVING dtime < NOW() - INTERVAL 1 YEAR ) AS x JOIN users u ON u.usrid = x.userid WHERE ( u.rank = 'P' OR u.extras LIKE '%W%' ) ORDER BY ...


1

I must say this is normal and nothing to worry about. This blog tells how target server memory calculated. Please see the forumula below Target1 = Current committed pages of SQL Server + ( Available Physical Memory - min (Total Physical Memory Pages / 20, Available Physical Memory Pages / 2)) ullAvailPageFile: The maximum amount of memory the ...


1

My reason why to not to use innodb_file_per_table is performance. I did some tests for our database with 450 tables on mysql 5.5.45 Linux CentOS release 6.7 For unit tests which inserts fixtures into the database before each test (not using all tables everytime) and also tests itself does work with database a lot (inserts, update, deletes, selects) the ...


0

I use this query to show disk x cache hits: -- perform a "select pg_stat_reset();" when you want to reset counter statistics with all_tables as ( SELECT * FROM ( SELECT 'all'::text as table_name, sum( (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as from_disk, ...


0

Create clustered index ix1_table1 on dbo.table1 (col1) with (online = on) This will take a little longer, the online part, but it will allow users to continues using the table while the index is being created. Every table (with very few exceptions) should have a clustered index.


0

There are a lot of inter-dependencies. Casual use of the Query cache can speed up some queries. Busy systems tend to be slowed down by the QC. Overcommitting RAM is deadly for performance. Here is a discussion of that, and some of the settings. 32bit system with 8GB of RAM ! -- Is that a 32-bit OS? Or a 32-bit MySQL on a 64-bit OS? If the former, then ...


2

If these are quite large databases I would ask if they are on different physical disks, with one disk significantly slower or busier than the other? (i.e. one is SSD, and the other is a standard HDD). For smaller databases though, the data will quickly be cached, and so speed of the disk would stop being relevant at that point. I'd suggest turning on ...


0

One possible reason could be locking/blocking. If there is lots of activity going on in your 'Slow' DB, your queries might be waiting for long time before being able to acquire the required locks.


1

You have PK clustered index as a uniqueidentifier? That index has massive fragmentation. You want a PK that is inserted in the order of the PK Why not just use an identity? Why are are you updating a value rather than just insert the correct value? Are you inserting just one row at a time? One row one round trip is not efficient. Fast inserts with ...


0

The answer to any performance question is "it depends." Discrete columns can be fast and byte column flags can be fast too. In absolute terms you can probably save a bitwise operation by having discrete columns here and there so discrete columns are theoretically faster. A theoretical bump shouldn't be the main reason to choose a strategy. To paraphrase ...


0

To me 30 queries does not sound like to much work in order to obtain the level of performance increase you will see having SQL Server do the work instead of Access. Especially if the data is going to expand to more users and is a more mission critical type thing. Something you might consider is running your Access database through the SQL Server Migration ...


3

If you are looking for significant performance improvements to dnoeth's answer, consider using a native C-function and creating the appropriate operator. Here is an example for int4 arrays. (A generic array variant and the corresponding SQL script). Datum _int_sequence_contained(PG_FUNCTION_ARGS) { return DirectFunctionCall2(_int_contains_sequence, ...


5

The addition of the redundant predicate can make a difference in SQL Server. In the execution plans below notice the @1 in the first plan vs the literal 'foo' in the second plan. This indicates that SQL Server considered the first query for simple parameterisation to promote execution plan reuse - however the comparison of two constants prevents this ...



Top 50 recent answers are included