New answers tagged

0

Are the 37 indexes you have on the table global indexes? Option 1: Where you truncate and then rebuild This method is most appropriate if the partition that you are truncating has at least 10% of the total data in the table. But rebuilding these 37 indexes is going to take time. Option 2: Where you delete and truncate the partition The delete statement ...


3

You could merge the subqueries using this model: SELECT bool_or(B.tags&1<>0) as "has_children_tag_1", bool_or(B.tags&2<>0) as "has_children_tag_2", bool_or(B.tags&4<>0) as "has_children_tag_3", bool_or(B.tags&8<>0) as "has_children_tag_4" FROM A LEFT JOIN B ON A.id = B.parent_id WHERE [conditions] ...


0

My solution of this would be use relation tables instead of merging ids, use uniqueness for tables phone number, email, and website etc. and insert with IGNORE command like Insert ignore into emails values (5,a.a@a.com); If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a ...


0

OK, after experimenting a bit further with queries and a set statistics time on, here's what I found: On a small IN-list (4-12 items) - ALMOST identical The performance is almost identical, the hardcoded variant uses a little bit more CPU, the overall "elapsed time" is either same or a little bit longer. On a bigger IN-list (150+ items) - subquery wins BY ...


2

Estimated sub tree cost is a SUM of cost of all operators preceding the one you are looking at. The easiest example is to look at the left-most icon - it will have an Estimated sub tree cost of whole query plan. There are a lot of sign in a query plan that show it needs optimization, however, I've seen a lot of situations when even perfect plans caused ...


1

I'd recommend looking into pre-splitting and/or using a hashed shard key to do the insertion and stick with dropping the collection (with remove you are basically doing a delete for every write, so it will always be slow). The hashed shard key is usually the easiest one to get started with. If you are looking to measure write throughput then each of those ...


3

I would not worry about the 100% The big number are the big number A lot repeats so start optimizing just one This is just a subset of your query DECLARE @date SMALLDATETIME SELECT Reffd AS NAME , ( SELECT ( ( SELECT count(*) FROM [cal_reg].[dbo].[customer] WHERE upper(Reffd) = upper(main.reffd) ...


7

The sum of operator costs is more than 100% in execution plan is a known bug and is closed as by design ! Also, AaronBertrand filed a similar bug - SSMS : Execution plan sometimes exceeds 100% If you want to understand how plan costing works .. Paul White explains it at his best here. From the query processor team - What’s this cost? General guidelines ...


2

I want to load my entire database into memory, but how can I do this? Before figuring out "how" to do something, it is often best to be clear on "why" that something should be done. So, why exactly do you want to load your entire database into memory? Memory is a finite resource, so it needs to be used efficiently / wisely. I have about 256 GB memory ...


2

I'm not sure why you need to join all three tables every time. For your specific example, what about the following query: WITH rel AS ( SELECT prm.power_relation_id FROM power_relation_members prm JOIN power_lines pl ON prm.member_id = pl.id WHERE pl.geom = :BIND_VAR_HERE -- in this case, 'abc' GROUP BY prm.power_relation_id ) SELECT pl.id, pl....


0

You can increase the innodb_buffer_pool_size and innodb_log_file_size for faster query processing. Also you can use https://tools.percona.com/wizard to find out the best configuration for Mysql engine depending upon the H/W configuration and workload. I would recommend increase cache limit and related parameters for processing faster select operations. ...


-1

If you have a server is necessary to increase the number of authorized packages and that will serve you for many applications together puedad send information, if they want to use disconnected programs and then send 'replication' need to modify this option. max_allowed_packet = 512M


0

Microsoft has Contoso BI Demo Dataset https://www.microsoft.com/en-us/download/details.aspx?id=18279 which is big enough to see the difference when plaing with indexes, queries etc.


1

The large number of nscanned key comparisons is explainable by the skip value: the query is skipping 903,462 documents (ntoskip) in order to return 21 (ntoreturn). The nscanned value in your output is the sum of ntoskip and ntoreturn. The number of nscannedObjects (identical to nscanned) is because the skip stage in MongoDB 2.6.x query processing happens ...


2

Benjamic Navarez has an excellent article on how the cost-based optimizer in SQL Server works. Essentially, when SQL Server is asked to read a table it looks at several methods of returning the requested data, and chooses the "best" path it can by assigning costs to each type of operation. It then chooses the cheapest path, or plan. This may or may not ...


4

Yes, adding indexes could cause IO wait to increase. Perhaps without the index, you are doing a lot of full scans of the table, thousands or millions of blocks, to get just one piece of data. But the IO wait is very low, because the kernel read ahead keeps the pump primed so your process doesn't wait on IO (instead it uses a lot of User CPU to filter ...


1

Add an index to (post_id) in postlocks, remove the subquery against that table and the reference to that column in HAVING, and add WHERE NOT EXISTS (SELECT * FROM postlocks pl WHERE pl.post_id = p.id). ...for a start. You want that condition to be evaluated early since it will eliminate some of the other lookups, and this should help ensure that the ...



Top 50 recent answers are included