Tag Info

New answers tagged

0

Folks, I was able to resolve the issue by updating the statistics . I believe it is related to outdated stats and so the query optimizer getting not so efficient execution plan. Thanks


1

I would suggest normalizing data and splitting the table into the following two tables: CREATE TABLE `file_data` ( `data_id` bigint(20) NOT NULL AUTO_INCREMENT, `hash` char(40) DEFAULT NULL, `size` bigint(20) unsigned DEFAULT '0', PRIMARY KEY (`data_id`), UNIQUE (`hash`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `file_names` ( ...


-1

You are hitting the inherent scalability limits of Postgres (or any other RDBMS). Remember that an RDBMS index is a B-Tree. A B-Tree is O(log n) for both average and worst case. This makes it a nice, safe, predictable choice for reasonable values of N. It breaks down when N gets too big. NoSQL databases are (for the most part) hash tables. A hash ...


3

Could I through indexing alone influence SQL server to run this much faster. Possibly. There are all sorts of things you could try with indexing, including creating a filtered index to exclude the 95% of UserPasswordRequestHash entries that are null, expanding existing indexes to include more columns, or adjusting indexes so the chances of finding the ...


3

Ignore the "fragmented" alarm; it's bogus. Ignore the max "possible" memory; it's bogus. Focus on the slow queries; they are causing the high CPU. What are the slow queries? Plan A: Glance at SHOW FULL PROCESSLIST; frequently. You will see one or two queries there most of the time. Plan B: Turn on the slowlog; set long_query_time = 2; and (after a ...


1

The following is probably an issue: [!!] Maximum possible memory usage: 13.4G (347% of installed RAM) Due to limited memory, the database is probably using the swap/page file a lot, as well as significant disk i/o through out. This might cause CPU utilization to remain rather high at all times.


1

I don't think that any of these suggestions are particularly improvements in performance compared to the CASE as that's already very basic. Another possible option (although again, probably not worth the effort as it's just a short-hand way of writing your CASE) for SQL Server 2012 and above. I'd be interested in seeing what the reaction was of the person ...


5

This should do what you need SELECT ISNULL(ta.[tag-value], 'N/A') AS [tag-value] FROM @AgentTableFilt ta Or you could use the following SELECT COALESCE(ta.[tag-value], 'N/A') AS [tag-value] FROM @AgentTableFilt ta


1

You can try selecting into a temp table with the zip criteria only, and then select from the temp table with the additional criteria. SELECT * INTO #MyTemp FROM (SELECT * FROM MyTable where zip='1234') data SELECT * from #MyTemp where other = 'Y' DROP Table #MyTemp Not sure if this will be faster, but since the zip-only query runs fast, then you're just ...


0

Each run of this query will scan the whole table for two reasons: The index on name will not be used as the value starts with a wild char % You are not using the proper functions that utilize the fulltext index on the other two fields. Proposed solution: add a fulltext index on name field Use the proper functions to search. in this case, it is match : ...


1

Is there a better way to write this type of query? No, the way you've written it is the "best" way. Unless, of course, that way doesn't work. The joy and frustration of using a declarative language is the optimiser. It is your best friend when it works and worst enemy when it doesn't. One way to kick the optimiser into doing the right thing is to ...


2

If the table is InnoDB, here is what's happening: The query optimizer sees SELECT * and does this Sees all columns are included in the SELECT list Uses the clustered index since all columns are included The query optimizer sees SELECT batch_id, study_id and does this Sees SELECT list has two columns, not all columns Sees the study_id index (and other ...


0

It will run through all rows. However, it might be able to run through the "rows" in some index rather than running through the entire dataset. See my blog with 8 techniques for speeding up ORDER BY RAND() Your EXPLAIN may lead to a 9th. :)


0

Without some way of tying the two tables together, you are asking for 6 million rows (6000*1000). Instead of FROM employee e, employee_categories ec, you need something like FROM employee e JOIN employee_categories ec ON e.category = ec.category AND... be sure category (or whatever it is called) is indexed (perhaps PRIMARY KEY) in one of the tables. ...


1

It really depends. First of all I'm assuming you are using SQL Server although I believe all RDBMSes will work about the same in this respect. There are two major options: If there is no index that the optimizer can use then it will do a table scan. In this particular case it will run through every row (all 8400) and look for cases where name = 'jhon'. ...


-1

A SQL Select statement is not designed to stop executing on first match. If you wish to stop executing your query once it finds the first record, you should add a limit to it. (Depending on SQL server.) In MySQL: SELECT name FROM users WHERE name = 'jhon' LIMIT 1; In MSSQL (Microsoft SQL): SELECT TOP 1 name FROM users WHERE name = 'jhon'; If you are ...


1

Again, I'm a bit unclear. If you run the SELECT in your question, and if the users field is indexed, then it will to go straight to and then stop after it finds jhon. If there are multiple jhons, it will return those and then stop, the index being used to determine the point at which the server will stop searching. If the field isn't indexed, then it will ...


0

In the future, ask to have the question migrated here instead of double posting. From the MongoDB Documentation, To calculate how much RAM you need, you must calculate your working set size, or the portion of your data that clients use most often. This depends on your access patterns, what indexes you have, and the size of your documents. Because ...


1

I have a simpler way to put it. "Batched INSERTs and LOAD DATA run 10 times as fast as single-row INSERTs." By "batching", I mean INSERT INTO t (a,b) VALUES (1,2), (2,3), .... The optimal number is between 100 and 1000 rows per INSERT. Beyond that, you get into diminishing returns. Here are some issues that impact performance, especially for Batched ...


2

QUERY #1 Each time you do an INSERT, you are doing this under the hood SET @sql = 'insert t select null'; PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s; Within the stored procedure, you fully parse, compile, execute and deallocate structures for the prepared SQL statement 2 million times. QUERY #2 Running insert t select null from(, you fully ...


3

The CONVERT_IMPLICIT is occurring because you have a collation on the column which does not match the parameter's collation. So the parameter is converted to the column's collation. To explain further - there are collation coercion rules which triggers this conversion. So if you have an implicit collation for the column and a coercible-default for the ...


5

It was all down to collation of the column. It was different from the database's (and the table's) collation. Now changed the column's collation to database's and no more implicit conversion shows up. Have no idea about the internals and why it caused the problem.


4

Why do you use this view at all for this query? The view collects violations and other stuff, but your query does not care about those at all, just the number of items. The view lists all items regardless of these because of the outer joins, so you basically perform a lot of unnecessary extra work to collect violations and other stuff (the NO_MERGE hint ...


1

You can improve efficiency of your query by (for example) replacing: ,( SELECT /*+ NO_MERGE */ pv.item_id ,count(*) AS cnt FROM policy_violation pv WHERE pv.item_id IS NOT NULL AND pv.quarantine_status = 'QUARANTINED' GROUP BY pv.item_id ) ct1 ,( SELECT /*+ NO_MERGE */ ...


1

Don't use an attribute value to indicate what entity a person owns. Instead, take advantage of the relational nature of the database in your queries. Remove the owns column from person and find related entities by joining the tables: SELECT p.id AS person_id, p.first_name, p.last_name, h.id AS home_id, h.no_of_rooms, h.area, h.city, c.id AS car_id, c.make, ...


0

Running EXPLAIN QUERY PLAN on your query gives this: 0|0|0|SCAN TABLE LEGS AS l 0|0|0|EXECUTE LIST SUBQUERY 1 1|0|5|SEARCH TABLE TRAVELDAYS AS d USING AUTOMATIC PARTIAL COVERING INDEX (Day=? AND Value=?) 1|1|4|SEARCH TABLE TRAINS AS t USING AUTOMATIC COVERING INDEX (TrainDaysUID=?) 1|2|0|SEARCH TABLE LEGS AS l USING AUTOMATIC COVERING INDEX (TrainUID=?) ...


1

You're correct: FULLTEXT search didn't hit InnoDB until MySQL 5.6. This leaves you with a few options: Update to MySQL 5.6 and use a FULLTEXT index Change the contract of your function to only allow prefix searches; that is, 'term%'. It will fulfill many use cases while saving your DB. Convert to a MyISAM table, or create a spare MyISAM table that you can ...


2

SQL Server is atrocious when it comes to the performance of scalar functions as well as the reporting of its impact. (a very useful article with details as to why: T-SQL User-Defined Functions: the good, the bad, and the ugly (part 1)) You are correct that the Query plan (actual) does not reflect the true performance of the two different approaches, this is ...


8

Why does SQL server run ths inline SVF query slower - both in CPU and elapsed time? Scalar valued functions are executed in a different context than the main query and setting that up for each call takes time. By centralising some simple logic it appears I impede performance through code reuse. Yes, for scalar valued functions that is true. ...


0

Many things can be done to speed things up... Use FLOAT or DECIMAL, not VARCHAR for latitude and longitude. (This is one of many things to shrink the record size.) Do not INDEX boolean values like clocked_in and public_post. (The optimizer is unlikely to ever use the index. And it is costly to update.) Don't split DATE and TIME; use DATETIME instead of ...



Top 50 recent answers are included