Tag Info

Hot answers tagged

4

Exists and IN will give the same query plan because they are both "LEFT SEMI JOINs" internally. I prefer EXISTS generally because of consistency. NOT EXISTS is safer then NOT IN. Caching does not apply and don't think about it. This is a set based operation and the SQL Server optimiser will work out the best way: It may spool the subquery or it may not. ...


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

The question, as Remus pointed out, is too generic to get an answer as the answer depends on the context of what functionality is to be used and how will it be used. Regarding "Security": If you are asking about anything that can be done in an assembly marked with PERMISSION_SET = SAFE, then there aren't any issues that I have ever been able to find. And ...


3

SQLCLR assemblies can be installed with three levels of security access: SAFE | EXTERNAL_ACCESS | UNSAFE. This is amply documented, refer to CREATE ASSEMBLY and Designing Assemblies: Managing Assembly Security You can control how much an assembly can access resources protected by .NET Code Access Security when it runs managed code. You do this by ...


3

On principle, I would always use EXISTS rather than IN. EXISTS has the opportunity to short circuit, though it won't always. IN has the opportunity to be optimized as an EXISTS, though it won't always. So, since EXISTS can never be worse than an equivalent IN, but can sometimes be better...


3

Don't go to DTA. It can have severe performance impacts in production and can really do more harm than good (I learned that lesson the hard way when I started out as a DBA). The first step is to find out what exactly is causing your performance issues. Is it a CPU issue? Memory? Improper indexes? Also, how do you know you are experiencing a performance ...


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

(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 ...


2

Before I start, I am proponent of FIXING MAX SERVER MEMORY always 1. I will first address SQL Server versions before SQL Server 2012. That means if you are using SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2 (no I will not include SQL Server 2000) If you are using SQL Server version before 2012 and your operating system is Windows Server 2008 R2 ...


2

Generally speaking, no, there is no detrimental effect from maxing out memory once the OS and other applications have been accounted for. This is exemplified through the default settings of SQL Server: The default setting for min server memory is 0, and the default setting for max server memory is 2147483647 MB. SQL Server Memory Configurations (MSDN) ...


2

1. Table definition Better: CREATE TABLE test( test_id serial PRIMARY KEY, age INT, name text, data JSONB ); Due to alignment requirements of the types integer and varchar / text it's better to put the two int columns first and together. More: Configuring PostgreSQL for read performance Also, "age" is a dubious column to begin with. Normally ...


2

You can test this variation. In theory, it would use an (id, value) index to find the min and max and would not have to count the distinct values at all: SELECT id FROM t GROUP BY id HAVING MIN(value) < MAX(value) ;


1

No, please don't store this data redundantly - you already have all of the information you need in the base table, without incurring the overhead of triggers or additional writes. Just create an index that supports your query, somewhat similar to what @Colin suggested, but I'm adding the output columns to avoid key lookups: CREATE INDEX ix_last_position ...


1

From the output the two inserts are doing gap locking on the index index_shipments_on_ci_ai_tn_sti. Without knowing the table definition, I think the index is a UNIQUE index. Here is a decent blog explaining the reason UNIQUE constraints can cause deadlocks on concurrent inserts. Snippet here: Mysql innodb engine performs row locking on inserts. If ...


1

I am a SQL Server DBA - I am biased towards SQL Server. However if the budget is limited, I would suggest trying a mature open source database, perhaps MySQL, PostGreSQL or MariaDB. Performance isn't your real issue here. The security of your data and the safety of strong ACID transactions are what you should really prioritise. How are you going to backup ...


1

SQL Server Compact is mostly used for building standalone and occasionally connected applications for mobile devices, desktops, and Web clients. There are lots of features not supported in SQL Server Compact check differences and with lack of these features hosting provider with exceptional bandwidth, up-time, etc will not help in terms of performance and ...


1

I have not used SQL Server Compact, but because of its design, there are warnings in the SQL Server 2012 documentation about concurrency issues. See: http://technet.microsoft.com/en-us/library/ms171845(v=sql.110).aspx for warnings on: Lost updates. Inconsistent analysis (nonrepeatable reads). Phantom reads. If you need a free and much more functional ...


1

You say Then we gather statistics on the table (and implicitly on the index): but statistics on indexes aren't gathered unless you do this: dbms_stats.gather_table_stats(null,'PART',cascade=>true); What appears to be happening is index statistics are not being gathered (or not gathered correctly) on 10.2.0.5; if specifying cascade=>true ...


1

Assuming that the app will be treating these various shop-types in the same manner for some operations (operations that do not care what type of shop that it is), then my preferred approach is to use a subclass / inheritance model. I have detailed this out in a couple of other answers here: Don't know how to transform variable entity into relational ...


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

The amount of memory specified in innodb_buffer_pool_size is allocated when the MySQL Server daemon starts up, and if the system doesn't have that much memory free at that moment, MySQL won't start. The amount of memory you specify is what the buffer pool gets, and it neither grows nor shrinks. InnoDB reads pages from disk into the pool as they are ...


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 ...



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