Tag Info

New answers tagged

0

A RDBMS solution may actually be slower than a file system based one due the extra work it does, such as concurrency management, transactional control and constraint enforcement. This site is full of questions about SQL queries that should take seconds but actually run for hours. Have you measured where the majority of the time is spent currently? From your ...


1

You might look at trying out Luca Canali's PerfSheet4 tool, which helps extract and visualize data from AWR tables in Excel. It comes with a video and should help you get started with some of the necessary data gathering and analysis for troubleshooting. You probably want to start with Query: Top 3 wait events and CPU option from the sheet which according ...


3

You have to concentrate on Memory, Disk IO and CPU utilization to balance out your environment. Best is to start collecting and dumping the results in a utility database and to give you a good baseline using sql agent on a scheduled basis. Alternatively, you can use Performance data collector (applies to sql server 2008 and up) MEMORY: You can look ...


2

Maybe this is just over-engineered. Have you actually tried using a single full index? Partial indices covering the whole table together do not provide much gain, if any, for index lookups, and from your text I infer that you have indices for all run_ids? There may be some advantages to index scans with partial indices, still I would benchmark the simple ...


2

For a table with a primary key (PK) on an identity column, it will be clustered by default. Could it be better as nonclustered? If you're asking if the default for a primary key on an identity column (in particular) ought to be nonclustered, I would say no. Most tables benefit from having a clustered index, so making clustered the default for a primary ...


-1

i would go for the second query as it will give you the exact records for the month. SELECT * FROM customers WHERE MONTH(birthday) = 4;


0

MySQL FT search isn't that fast as we hope to be. I'd recommend Sphinx - http://sphinxsearch.com/ - it provides fast and relevant full-text search. If you are wondering how fast is Sphinx (from my experience), it can take few minutes to search 200M records with MySQL, but with Sphinx, less than 2 seconds, and better accuracy.


-2

A couple of points to consider. While an index (clustered or not) on a monotonously increasing value saves you page splits during mass inserts, it creates a new hot spot at the tail end of the index. Though it may not be a problem with a single thread bulk insert, it will definitely increase contention for a multithreaded application inserting new tuples at ...


1

The optimizer rarely does anything useful OR constructs. What you can attempt to do is Get ids from each of the MATCHs UNION the results JOIN back to the necessary tables to get the desired field. Something like this: SELECT i2.*, c2.name AS category, ... FROM ( SELECT i.id FROM phppos_items AS i WHERE MATCH(...) ...


0

I have tested for 100.000 row SELECT * FROM customers WHERE MONTH(birthday) = 4; ==> 18 second SELECT * FROM customers WHERE birthday LIKE "2015/04/%"; ==> 21 second


4

Actually, you do not need a Clustered Index nor a Primary Key to be created, since Unique Indexes and Non-Unique Indexes can handle the work. SQL Server has supported a Clustered Index since at least version 1.1, but the Primary Key was just a "concept" that programmers enforced by defining a unique index. But it seems that both Primary Keys and Clustered ...


0

I would recommend to reduce the amount of occurrences in the inner query over phppos_items. A way to do that is to get only the heavier records using a subquery. I see that you are using LIMIT 20, so I assume that you need online 20 records from the overall. Documentation What you can do to do so, is to trim the working set using a condition over the match ...


5

PostgreSQL relies on the operating system's disk cache for most caching. This cache is usually reported as "free" RAM by most tools, because modern operating systems use all but a little bit of the currently-free RAM for disk cache. This is normal. To confirm, use a better tool that shows buffers/cache separately from truly free memory. On Linux, free -m ...


2

Bug 11872813 - V$PGA_TARGET_ADVICE VIEW IS EMPTY. After setting PGA_AGGREGATE_TARGET > approximately 12G the V$PGA_TARGET_ADVICE view is empty. This issue will be fixed in version 12.1 and a fix is expected to be included in patch set 11.2.0.4. Apply Patch 11872813


4

Given that this is an existing database that already has tables defined in it, there are some very serious implications to the action of changing the database collation, beyond the potential performance impact to DML opertions (which actually was already there). There is very real impact to performance and functionality, and this change not only did not ...


6

Collations in SQL Server determine the rules for matching and sorting character data. Normally, you would choose a collation first based on the comparison semantics and sorting order the consumers of the data require. Humans generally do not find that binary collations produce the sorting and comparison behaviours they expect. So, although these offer the ...


7

By default the PK is clustered and in most cases, this is fine. However, which question should be asked: should my PK be clustered? which column(s) will be the best key for my clustered index? PK and Clustered index are 2 differences things: PK is a constraint. PK is used to uniquely identify rows, but there is no notion of storage. However by ...


1

Based on your configurations and output, the only suggestions I can make for you to try are the following. Disable Transparent Huge Pages. Evidence from the PostgreSQL mailing lists, and Red Hat themselves recommend disabling THP for database workloads here Set vm.zone_reclaim_mode=0. Discussion here Set your elevator to deadline from cfq. Red Hat ...


1

When you have a stored procedure that takes a lot of time to retrieve the data in the production phase and you need to make a performance tuning. This specific stored procedure is part of critical function for a webpage. The stored procedure is used daily. My questions are: Is it recommended to do a performance tuning in the production ...


6

Have you seen this? https://community.oracle.com/thread/889338?start=0&tstart=0 It states you can only exchange partitions from a partitioned table to a non-partitioned table, or vice-versa. You'd need to temporarily exchange the partition into an interim, non-partitioned, table, then into the target partitioned table. There are some interesting ...


1

"Performance" is a much broader topic than is being assumed here. There are quite a few factors that influence performance: The Data Model: Are you using appropriate data types? Do you have Primary and Foreign Keys defined? Do you have repeating string values (bad) instead of lookup tables? Do you have an appropriate structure or something that is ...


1

Below is not complete (and longer than a comment)- since this is a very open ended question and will be closed (I am voting it to close as well). I have laid below down to give you guidance and help you get started. You have to install sql server. Go for a Developer edition. download and restore Adventure Works 2014 Sample Databases If you want to work ...



Top 50 recent answers are included