Tag Info

Hot answers tagged

7

Are there any reasons for periodic/cyclic slow-down of insert performance? Yes. check point events. With a write intensive workload, big RAM server, as you describe, a large number of 'dirty' pages accumulate in memory. At the predetermined checkpoint interval all these dirty pages get written to disk, causing a spike of IO requests. This in turn slows ...


7

SQL Server does not "rebalance the tree" as a periodic event. I have last heard this term in the context of Oracle. All that SQL Server does it increase the tree height when necessary. This is an event that happens only a few times in the entire existence of a B-tree. In a DML heavy workload there can be many small tree adjustments called page splits. These ...


4

Since you started both servers, you have executed (approximately) 162509 + 33073 + 11291 = 206,873 queries on the Linux server and 44648032 + 6866308 + 994889 = 52,509,229 queries on Windows. Why would you expect similar numbers when one has done more work than the other? The ratios, however, are similar with: 162509 / (162509 + 33073) ~= 83% 44648032 / ...


3

Based on your question, I would index the Timestamp column with the clustered index. And to make the index unique, just make sure to include the identity column in the index definition: ... PRIMARY KEY CLUSTERED ([Timestamp], [Id]) If query performance for queries on Exchange_Id is still an issue after that, you can also add a non-clustered index that ...


2

You should put everything on a level playing field. How ? Without proper tuning, it is possible for older versions of MySQL to outrun and outgun new versions. Sep 25, 2013 : Why should I use InnoDB and MySql instead of XtraDB and MariaDB? Mar 26, 2012 : Percona vs MySQL Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap) Oct 05, 2011 : ...


2

Foreign keys are not required in order to execute a JOIN. Foreign keys are restrictions that are there for security reasons (so that you cannot change the value of a column, or delete/insert a row ending up with an inconsistent state between tables). It is true that in most cases foreign key are used as JOIN matching conditions. The reasons why InnoDB is ...


2

There is a situation in your current setup that would/could cause some slowdown related to an auto-incrementing key (IDENTITY, GETDATE(), NEWSEQUENTIALID()): under high-concurrency INSERT operations, there can be contention related to placing rows on the same page. This is called a "hotspot" and is one of the few drawbacks to auto-incrementing values as ...


2

Some details to clear things up Based on that msdn page : server memory settings it's a good practice to keep the default values for SQL-Servers min and max memory to keep it dynamic. No, its not written its a good practice its said its recommended and I am sure MS books online cannot write it as good this because of fact that its always good to have ...


2

The MIN value is useful when you have a server with multiple instances. You can use it to prioritise memory usage per instance. But mainly it is to guarantee that SQL with have at least that much memory to work with. This stops people running something on the server and stealing all the memory. The default setting basically leaves memory allocation to SQL ...


2

This is clear example of Many-to-Many relation (M:N). Many messages can have (be translated into) many languages. Date structure for that has three tables: create table Message ( ID int primary key, message text ); create table Language ( ID int primary key, language varchar(50) ); create table Message_Language ( message_id int, language_id ...


2

Based on your comment about the "unwieldy" SP I'd almost guarantee that is at least part of your problem. Try running the following query replacing DBName and SPName as appropriate. USE DBName GO SELECT SUBSTRING(text, statement_start_offset/2, CASE WHEN statement_end_offset = -1 THEN 999999 ELSE ...


2

(This is very similar to a question recently posted on S.O.: Time trigger database updating) There is no direct way of doing this, at least not in SQL Server. In that similar question linked above I did detail a way of getting very close to this concept in SQL Server, and someone mentioned Service Broker, but these are still indirect and have not really ...


2

Your conditions are searching for ranges. Now imagine you're looking in a dictionary and looking up all words where the first letter is "greater" than A. Of how much use is the index? You want to narrow the search range down as much as possible. MySQL most of the time can only use one index per table. Combine those begin and end indexes. CREATE TABLE ...


2

If you are 100% sure that the intervals (begin, end) are never going to be overlapping, you can use this query, which only needs an index on (begin) or (begin, end) and will be much more efficient than what you have: SELECT t.* FROM ( SELECT g.* FROM geo_ip_city AS g WHERE g.begin <= 2523596988 ORDER BY g.begin DESC -- ORDER BY ...


1

SQL Server stores all the columns for one row together in on a single disk page. (It's more complex than this but for int and char etc. this is more-or-less true.) To retrieve any column's value the whole page is read into RAM. So once you have any column available for a given row, all of the columns for that row are available. There are several buffers ...


1

An execution plan is only going to have the code that was last compiled and used, to my knowledge. In troubleshooting applications myself I have come across where a statement I was looking at through the execution plan was actually written with sp_executesql and so was dynamic T-SQL that contained a bit more logic than the end result. You don't specify the ...


1

I wrote some posts about RAM disk and temp tables Jan 04, 2013 : Is there a MySQL engine or trick to avoid writing so many temp tables to disk? Jan 17, 2012 : How can I optimize my mysql setup to create my index faster? Sep 23, 2011 : skip copying to tmp table on disk mysql The basic idea would be to set set tmp_table_size and max_heap_table_size to the ...


1

if you trying to use the same query then it could be optimized adding composite index to geo_ip_city the new index could be ALTER TABLE geo_ip_city ADD INDEX ind_begin_end (begin, end) USING BTREE with this you would need to remove you use index in the select statment to be SELECT * FROM geo_ip_city WHERE begin <= 2523596988 AND end >= ...


1

Some might say depends how big the databases are; I think regardless of the size, accessing the databases directly instead of having a "local" mySQL database to query would be best, for the following reasons: No need for disk/memory/maintenance of the mySQL database If the updates to the various databases happen every few seconds, and the users may only ...


1

No. How would the query processor recognize that it should use the materialized view. The materialized view is another object. See http://www.postgresql.org/docs/9.4/static/rules-materializedviews.html In part that explains: "When a materialized view is referenced in a query, the data is returned directly from the materialized view." So materialized ...


1

Starting with the highest and moving down: SOS_SCHEDULER_YIELD This wait type is usually caused when you have queries performing huge scans. I'd suggest checking to make sure you have the proper indexes in place and you don't have any out-of-date statistics resulting in an inefficient query plan. OLEDB OLEDB waits indicate that your query has to reach ...


1

In addition to @tombom's suggestions, creating an index on (user_id, post_id) instead of (or in addition, but the less indexes the better) separate indexes on user_id and post_id will simplify the query, probably getting rid of the filesort and temporary table, plus giving you the benefits of a covering index. This will probably lower the query execution ...


1

If you are primarily concerned with the complexity of getting the 20 most recent records, you could either write a view that does a UNION ALL of the tables, or create an empty parent table with all the common columns and have all the other table inherit using the PostgreSQL-specific inheritance feature, to hide the visual complexity of the query. Although ...


1

You can get the approximate CPU consumption with one of the connection properties: ApproximateCPUTime Returns an estimate of the amount of CPU time accumulated by a given connection, in seconds. The value returned may differ from the actual value by as much as 50%, although typical variations are in the 5-10% range. On multi-processor ...


1

I am sure that you can improve performance using indices on foreign keys i.e put an index on the accountholderid when you use it as foreign key. It's the place where you would usually put them. And you might put index on LastModificationTImestamp with desc order CREATE INDEX accountholder_LastModificationTimestamp_idx ON ...


1

What about using a union? SELECT * FROM table WHERE (col1 = '123' AND col2 = '456') UNION ALL SELECT * FROM table WHERE (col1 = '456' AND col2 = '123')


1

SELECT * FROM table1 INNER JOIN table2 ON table1.id = '123' AND table1.id = table2.col; or SELECT * FROM (SELECT * FROM table1 WHERE id = '123') A INNER JOIN (SELECT * FROM table2 WHERE col = '123') B ON B.col = A.id;


1

The ref isn't referring to a constant, it's comparing two columns from two tables. Using the = operator will result in the ref type for table2.col = table1.id. For a query this simple, it's hard to recommend any improvements without knowing data sizes, indexes, both table structures, etc. I would say that the SELECT * isn't generally advisable unless you ...


1

Your code looks mature, mostly, but see below! Once you fixed that, I don't see much that could go wrong with it. I share the doubts that it will improve performance much, though. And there may be better alternatives, depending on exact requirements. General advice For simple functions as displayed: Use simple SQL function (LANGUAGE sql) which can be ...


1

Table layout It would be more efficient to reverse the column order in nodes: fixed length NOT NULL columns first. This is just a tiny optimization. It's only the first item because table layout comes first. CREATE TABLE nodes( id bigint PRIMARY KEY , type text NOT NULL , name text ); Index Replace substring(name,1,2700) with left(name, 2700) ...



Only top voted, non community-wiki answers of a minimum length are eligible