What is a suitable,stable, high performing database for Large Financial Time Series Data? I realise this question may have be answered in some form but I would like to gather the thoughts and opinions of actual users with experience in this field of Database design and development. I have a 'large' financial time series database, close to 150GB and growing at a rate of about 4GB per day. Currently I am using MySQL as my database engine, however I am concerned about scalability and performance in future. I have read conflicting reports on alternative solutions (MySQL, Postgres, Cassandra, Datomic, KDB+ etc etc), and with the 100s of Database technologies available one is left somewhat unsure. That's why personal experience of users I think would be invaluable.

Before anyone mentions the obvious such as KDB+, the issue I have with that is that it is predominantly an 'in-memory' database - not ideal for me. I understand it would be great for high frequency traders but that is not the nature of my research.I would prefer a scalable, on disk, SQL solution? Happy to hear thoughts and discuss.

In my opinion you must be, or you need, a DBA.
Not a single rdbms can solve problems with big data out of the box.

In your case partitions can be useful for example. So data compression, etc...

I can just suggest 3 alternatives:

SQL Server Enterprise Edition
Oracle Enterprise Edition
Postgresql

And the skills to manage data.


Will deadlocks occur when deleting rows from a table that is being added to by another process? I have to archive data from a table into another table whilst the original table is in use. The archival process is to copy the rows into another table and then delete the rows from the original table. The rows to be archived are selected by a column that has the Identity property.

A factor is that the table from which the rows are being archived will be in use - new rows will be written into the table as the archival process is running. This is a 24/7 application that cannot be stopped.

The rows that are being deleted are at the low end of the Identity column numbering whilst the new rows will be being added at the end of the table.

My concern is deadlocks. Is there a chance that deadlocks could occur between the 2 processes?

The archival process will be run from a Stored Procedure? Should I set the DEADLOCK_PRIORITY to LOW to ensure that if a deadlock does occur that the archival process will be killed and not the running application?

Should I also set TRANSACTION ISOLATION LEVEL?

Thanks

Table Definition:

    CREATE TABLE [dbo].[DummyName](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Payment Reference] [nvarchar](18) NULL DEFAULT (N''),
    [Payment Amount] [decimal](28, 10) NULL,
    [Payment Date] [datetime] NULL,
    [Payer ID] [nvarchar](34) NULL DEFAULT (N''),
    [Payer Account] [nvarchar](174) NULL DEFAULT (N''),
    [Payer Name] [nvarchar](174) NULL DEFAULT (N''),
    [Payer Type] [nvarchar](35) NULL DEFAULT (N''),
    [Bank Reference] [nvarchar](16) NULL DEFAULT (N''),
...

Index Definition:

ALTER TABLE [dbo].[DummyName] ADD PRIMARY KEY NONCLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

The rows to be archived are selected by a column that has the Identity property

In this scenario, you're inserting rows at the end of the index, and deleting rows from the beginning.  So there shouldn't be overlapping locks that would lead to deadlocks.

This assumes that your archive process won't ever run concurrently.  Multiple concurrent archive operations could conceivably deadlock with each other, since they'll be operating on the same range of rows.  You should make sure that doesn't happen by, for instance, scheduling this as a SQL Server Agent job.

Just make sure not to delete too many rows at once from the "live" table after archiving them, as this could lead to lock escalation (which sounds like it would be undesirable in your situation).  Check out Michael Swart's excellent article about that topic of doing batch processing in a way that promotes performance and concurrency: Take Care When Scripting Batches

Even though your main table is a heap, you should still be okay with regards to deadlocks, as all your queries will tend to seek on the nonclustered PK and delete using the RID from there.  It seems possible that you could experience page level blocking (or deadlocks) within the heap itself, depending on a lot of factors, but it would be hard to know for sure without seeing the workload in action.

  Should I set the DEADLOCK_PRIORITY to LOW to ensure that if a deadlock does occur that the archival process will be killed and not the running application?

Yes, that's a reasonable step to take.  And as you mentioned is a good way to make sure the archive workload is re-prioritized with regards to concurrency.

  Should I also set TRANSACTION ISOLATION LEVEL?

The default isolation level (READ COMMITTED) should be okay in this situation.


How to backup SQL Server database once evaluation period has expired I'm using Microsoft SQL Server Management Studio from last one month that is evaluation period software. I'm not able to login in management studio.
when I enter my user name and password it gives the error:

  TITLE: Connect to Database Engine

  Cannot connect to sony-PC

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or
  was not accessible. Verify that the instance name is correct and that
  SQL Server is configured to allow remote connections. (provider: Named
  Pipes Provider, error: 40 - Could not open a connection to SQL Server)
  (Microsoft SQL Server, Error: 2)

How can I take backup of my database?

I found a solution myself. Once SQL Server Management Studio expires the evaluation period it will not open anything. It shows go to this site to upgrade product or something else.

First, change the system date to 2 or 3 days back, then open the SQL Server Management Studio. Now you might get this error:

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
  (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

Now follow this step:

Check if your SQL Server service is up and running properly:

Go to All Programs > Microsoft SQL Server 2008 > Configuration Tools >
SQL Server Configuration Manager > SQL Server Services
Check to make sure SQL Server service status is Running.

In addition, ensure that your remote server is in the same network. Run sqlcmd -L to ascertain if your server is included in your network list.


Get list of DB2 databases and tables through linked server Is there a way to get a list of the databases on a DB2 server through a linked server query?  I tried using an openquery to run "list database directory" but that didn't work.

I am attempting to do this because I do not have direct access to the DB2 server.

Please note that I already have the linked server set up.

You cannot get a list of databases via ODBC; ODBC requires a connection to a specific database, and a database does not know anything about the outside world, specifically, if there are any other databases. Depending on a particular DB2 platform, even the concept of "other databases" may be nonexistent.

list database directory is a DB2 CLP command, not an SQL statement, and as such is not understood by the SQL engine.

As for the list of tables in the connected database, you can query the catalog view SYSCAT.TABLES (provided we're talking about DB2 for LUW; refer to the appropriate product manual if not).


Why doesn't more memory necessarily make Postgres more performant? The Postgres resource consumption docs state, under the shared_buffers:

  ...it is unlikely that an allocation of more than 40% of RAM to shared_buffers will work better than a smaller amount.

Why is this? I'm always told that the more RAM a server has, the better Postgres will perform. Isn't shared_buffers the quintessential memory setting for Postgres? If I'm only allocating 3GB of 12GB (the recommended 25% starting point) in my server to shared_buffers, where can I expect Postgres to take advantage of at least 6GB more?

In fact, effective_cache_size coupled with shared_buffers could more appropriately be considered to be the quintessential memory settings. Keeping shared_buffers a bit lower (e.g., 25%) is useful because Postgres also relies on operating system caches as well, which may account for utilization of some of the other "6GB" of RAM in the OP.

According to the official Postgres "tuning" page, setting effective_cache_size to half of total memory is considered to be conservative. However, this isn't a memory allocation, rather a guideline to help Postgres plan its queries based on what's available to it.

Also note that understating resources to Postgres by a slight degree can be helpful, in allowing some breathing room for future scaling. Imagine your Postgres server was optimized to take 100% advantage of all the physical resources in your machine, and then you reached your server's limit. There would be little you could do at this point to stave off disaster (e.g., swapping, extreme performance degradation, etc.), so leaving a bit of wiggle room can come in handy when you need a week's time to upgrade your server.


SQL Server: Replace with wildcards? Does Microsoft SQL Server natively support some sort of replace function using wild cards? I gather that Regular Expressions are not available natively.

I note that there is a PATINDEX function which can be used to bolt together a solution — is there one something which does it more simply?

For example REPLACE(data,'[xy]','q') to replace x or y with q.

The REPLACE built-in function does not support patterns or wildcards; only LIKE and PATINDEX do.

Assuming that you really just want the simple single-character replacement as shown in the question, then you can call REPLACE twice, one nested in the other, as follows:

SELECT REPLACE(
               REPLACE('A B x 3 y Z x 943 yy!',
                       'x',
                       'q'),
               'y',
               'q');

Returns:

A B q 3 q Z q 943 qq!

If you do need more complex pattern matching / replacement, then you will need to do that via Regular Expressions, which can only be done via SQLCLR. Several SQLCLR RegEx functions are available in the Free version of the SQL# SQLCLR library (which I wrote), one of them being RegEx_Replace[4k]() (the 4k version is for when you are certain that you will never need more than 4000 characters, hence you can get better performance from not using NVARCHAR(MAX) as an input parameter or return value).

The equivalent of the two nested REPLACE calls would be done as follows (and using the pattern syntax shown in the question):

SELECT SQL#.RegEx_Replace4k(N'A B x 3 y Z x 943 yy!', N'[xy]', N'q', -1, 1, NULL);

Returns:

A B q 3 q Z q 943 qq!

But, since we are talking about the possibility of a more complex pattern, one that cannot be done easily in T-SQL, we can use a quantifier on the pattern to have it replace any number of contiguous x or y characters with a single q:

SELECT SQL#.RegEx_Replace4k(N'A B x 3 y Z xx 943 yyxxyxy!', N'[xy]+', N'q', -1, 1, NULL);

Returns:

A B q 3 q Z q 943 q!

Please note that the input string was changed slightly from the previous two examples to add an extra x after the Z, and to add an extra xxyxy to the yy at the end. And, in both cases, the multi-character fragment was replaced with a single q.

For more info on working with strings and collations, please visit: Collations Info


How to Best Implement nearest neighbour search in mysql? So, in short,

What should be the data type of latitude and longitude?
What SQL command I should call to get the first 100 nearest restaurants for example?

Detail:

I have 100k biz record each with lattitude and longitude. I see that MySQL actually support a data type called point. Should I use that instead?

Does MySQL support KDTree storage system http://en.wikipedia.org/wiki/File:KDTree-animation.gif

Is it best to use point data type rather than regular float data type to store latitutude and longitude?

Eventually I want to find things like the first 100 restaurants closest to points 105,6 for example and my databases contains a lot of biz and points. Obviously computing the distance one by one for every records and for every points would be O(n) and hence sucks.

Notice that I am aware of a simpler solution described in How do Application Like Yelp Retrieve distance information from data base efficiently and will implement that my self too for a start. That's a good answer.

However, I think there is one creme of the crop answer that should outperform that right? In fact, storing location based on latitude and longitude and finding stuffs nearest to it is a very common problem I expect mysql to have a special design pattern for that. Does it have that?

Where can I learn more about it? Thanks.

As far as design patterns, the Yelp question is pretty standard stuff.

For a more complex answer, you will probably need the geospatial distance. Here is a fascinating powerpoint about that topic (and here is a pdf version of that as well).  However, the math involved is quite ugly.

From their slide:

set @orig_lat=122.4058; set @orig_lon=37.7907;
set @dist=10;

SELECT *, 3956 * 2 * ASIN(SQRT(
POWER(SIN((@orig_lat - abs(dest.lat)) * pi()/180 / 2), 2) +  COS(@orig_lat * pi()/180 ) * COS(abs(dest.lat) * pi()/180) *  POWER(SIN((@orig_lon – dest.lon) * pi()/180 / 2), 2) )) as  distance
FROM hotels dest
having distance < @dist
ORDER BY distance limit 10

There's a longer, more in-depth answer about geospatial distance on Stack Overflow.

But you still want to limit the results by latitude and longitude.

Ultimately, I would avoid the POINT datatype and go with latitude/longitude.  There's currently no way to determine the distance between two POINTs, so you're going to have to store latitude/longitude for that calculation anyways.

One last link:  you may also want to check out this SO thread regarding speeding up the queries using spatial indexes.


Trigger Performance - 1 or 2 Triggers? I have a question about triggers performance.

CREATE TABLE [dbo].[_test](
[ID] [INT] IDENTITY(1,1) NOT NULL,
[Date] [DATETIME] NULL,
[DateYearID] [INT] NULL,
[DateQuarterID] [INT] NULL,
[Date1] [DATETIME] NULL,
[Date1YearID] [INT] NULL,
[Date1QuarterID] [INT] NULL)

Now I want to have trigger that must update DateYearID and DateQuarterID columns if I update Date column (or insert a new row) , and must update Date1YearID and Date1QuarterID columns if I update Date1 column (or insert a new row). What is better, have a single trigger like

IF UPDATE(DATE)
   UPDATE _test SET DateYearID = ... , DateQuarterID = ...
IF UPDATE (DATE1)
   UPDATE _test SET Date1YearID = ... , Date1QuarterID = ...

or have two different triggers, first of them updating DateYearID,DateQuarterID columns, and the second one updateing DateYear1ID,DateQuarter1ID columns.

I am using SQL Server 2014.

Thanks a lot for help.

I would suggest a single trigger, with a single pass through inserted and/or the base table, and just updating all four values for any row that is affected. I have proven before that avoiding updating columns in a row where other column values have changed is not worth the effort, and that updating the same row twice from two different triggers will be slower.

IF UPDATE(DATE) OR UPDATE(DATE1)
BEGIN
  UPDATE t
    SET DateYearID   = ...,
        DateQuarterID  = ...,
        Date1YearID    = ...,
        Date1QuarterID = ...
  FROM dbo._test AS t
  INNER JOIN inserted AS i
  ON t.ID = i.ID;
END

(Also, just as an aside, note that UPDATE() tells you the column was referenced in an update statement, but it doesn't guarantee that the value has changed.)

And yes, I agree with Gianluca's comment: depending on how these four columns are calculated, you may not need a trigger at all. You may not even need a computed column - simple calculations (like DATEPART(YEAR,Date1)) are often best done at query time and only when you need them. Storing redundant data is overrated - I/O and memory are much more important than CPU in almost all systems, even with today's SSD and PCIe flash drives...


SQL Server Query against a View failing I have a view definition that references a linked JDE server. Querying the view is now giving some very strange results. If I perform a SELECT * I get all the results (619 rows) back but if I try SELECT TOP 1000 * or query for each column individually I get the following error:

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'A' to data type int.

UPDATE: Thanks for the suggestions. It turned out to be a data issue where someone has added codes A, G, and R to the JDE table used to hydrate a relationship that requires an int code in SQL Server. It doesn't explain the weird behaviour but it does address it.

I suspect you need to run sp_refreshview

See this on SO "“select * from table” vs “select colA, colB, etc. from table” interesting behaviour in SQL Server 2005"


Mirroring sql server , how to change the port? I have two servers on the same cluster, in fact my goal is to set up "high availability", but the databases that I have integrated are not synchronized, so I think it is " mirroring "that I created but the port on which the two servers is not the same, I have a 1433 on the other and a 5022 on the other. what should i do ?

Just specify the port when you set it up

ALTER DATABASE <DBNAME> SET PARTNER = 'TCP://<ServerName>.<Domain>.com:5022'


Passing parameters in MySql procedure with prepare execute I am trying to write a procedure that has a location parameter, this parameter is later used in the queries inside the procedure. Also, I want to be able to limit the query results dynamically.
I have never done anything similar, so I read the appropriate documentation and studied some examples. My code is the following:
DROP PROCEDURE IF EXISTS lower10_avg_price ;
DELIMITER $$
CREATE procedure lower10_avg_price (loc varchar(45))
BEGIN
SET @a = (SELECT ceil(COUNT(*)/10) FROM UNION_SALES WHERE location = loc and current_Price >5000);

SET @quer = concat(
'SELECT ROUND(avg(prices.price),2)
FROM
(SELECT current_Price as price FROM UNION_SALES WHERE location = ', loc,' and current_Price >5000
ORDER BY current_Price ASC LIMIT ?) as prices;');

PREPARE STMT FROM @quer;
EXECUTE STMT USING @a;

END $$
DELIMITER ;

The error I get when I call the procedure is:
CALL lower10_avg_price('Αρχαία Αγορά            ');

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Αγορά             and current_Price >5000 ORDER BY current_Price ASC LIMIT ' at line 3
Can someone please help me understand what is causing this error?
Thank you for any suggestions!

Modify your SP for to look at the SQL text built:
fiddle
It is clear that you forget to wrap string literal value with single quotes. You must use
SET @quer = concat(
'SELECT ROUND(avg(prices.price),2)
 FROM ( SELECT current_Price as price
        FROM UNION_SALES
        WHERE location = ''', loc,'''
          and current_Price >5000
        ORDER BY current_Price ASC LIMIT ?) as prices;');

location can be used for SQL injection. You could fix it either by using a bound parameter (preferred), or by using QUOTE(location).


Shrink a mirrored and replicated DB I have dropped one huge  table from the production DB

Current DB size on disk = 264 GB

The  real  size of the Tables = 32.164 GB

I am going to shrink this DB. Although it is not recommended, but this is the only option i have to reclaim this space, i will do monthly archiving for this table so i am sure it will not grow to this size again

My Questions are

There are Mirroring and replication on this DB, What should i do?

Is there a way to do this with no downtime? if no, what should be the plan to do this with the minimal downtime

Is Shrinking just affect indexes or it has other impact on performance?

Shrinking can have a massive impact on the transaction log, which in turn can dramatically impact the performance of mirroring and, I think to a lesser degree, replication.

My suggestion is: don't try to wave some wand and recover 200+GB right now, but instead, do it in chunks, gradually. Every night or every 6 hours or on some other reasonable interval, run one of these commands:

-- day 1
DBCC SHRINKFILE(data_file_name, 245000);

-- day 2
DBCC SHRINKFILE(data_file_name, 232500);

-- day 3
DBCC SHRINKFILE(data_file_name, 225000);

Yes, that is less satisfying, and obviously takes longer, but who cares? How badly do you need that space right now? Your features and users will be better off without that massive disruption IMHO.

An alternative would be to turn off mirroring and/or replication, and reinitialize them completely once the shrink operation is done (and you have backed up the log and made it the normal size, too). But I think just shrinking a bit at a time is far less complicated, disruptive, and risky.


How can I reduce time spent acquiring the mongodb schema lock? We have a mongodb 4.0.10 cluster, backed by WiredTiger, in production, with a 3 node replica set consisting of a master and two slaves.  One of the slaves has another service co-located that queries the slave extensively.  In addressing some slowness in the co-located service I'm seeing a lot of surprisingly slow queries.  This one took 3.3 seconds:

  find: "myColl",
  filter: { myField: "myValue" },
  projection: { name: 1 },
  $db: "myDb",
  $clusterTime: { clusterTime: Timestamp(1568198047, 3), signature: { hash: BinData(0, 0000000000000000000000000000000000000000), keyId: 0 } },
  lsid: { id: UUID("2ed823aa-e6af-4898-a4c1-c039d28a32ab") },
  $readPreference: { mode: "secondary" } }
  planSummary: IXSCAN { myField: 1 } keysExamined:0 docsExamined:0 cursorExhausted:1 numYields:0 nreturned:0 reslen:232
  locks:{ Global: { acquireCount: { r: 1 } },
          Database: { acquireCount: { r: 1 } },
          Collection: { acquireCount: { r: 1 } } }
  storage:{ data: { bytesRead: 355, timeReadingMicros: 4 }, timeWaitingMicros: { schemaLock: 3284692 }

The line that stands out to me here, is the last one, indicating that it spends 99.9% of its time waiting to acquire something called a schema lock.

I checked this particular database and collection and it turns out the collection had 50 items at query time and the database itself was tiny (less than 1k documents in total).  Furthermore, there's also an index on myField.

Here's some other data about our particular usage of mongodb that might be relevant:

Multi tenancy by having a database per customer
Most documents are small
Most documents will have a similar size throughout their life cycle (I read some others here had performance issues related to padding and documents getting moved around as they grew in size)
Customer data grows through document count, not document size

I've been monitoring these slows queries for a while now and I can't see any pattern.  It's like mongodb is doing some maintenance task every so often and whatever query runs at that time is forced to wait.

Why is a read query waiting to acquire a schema lock?  What can I do to eliminate this long wait?

This is a general architectural problem with the WiredTiger storage engine.
It is now being discussed here: https://jira.mongodb.org/browse/WT-5479

Long story short, your number of open files is too high.
If you can, consider removing unnecessary indexes, e.g. indexes on small collections.
You could also explore the new wildcard indexes introduced in MongoDB 4.2


Any gotchas at all with converting from MyISAM to InnoDB? I'm ready to move from MyISAM to InnoDB but wanted to know if there was a full list of things to look for? For example, I haven't seen any list mention that running DISABLE KEYS on an InnoDB table will throw a warning, except the manual page for ALTER TABLE. It's that kind of thing I need to know about before converting over. I thought I'd be fine with my queries but apparently not.

Here are some gotchas
Memory Usage
MyISAM

only caches index pages.
shared keycache (sized by key_buffer_size).
You can also set up dedicated keycache, one or more tables per cache table.

InnoDB

caches data pages and index pages.
one buffer pool and one size before MySQL 5.5
1 or more buffer pools starting with MySQL 5.5

Here are some queries I wrote and posted earlier on how to choose a proper size for the MyISAM Key Cache and InnoDB Buffer Pool.
FULLTEXT Indexes
MyISAM

Supports FULLTEXT indexes

InnoDB

Starting with MySQL 5.6, yes, but still in beta (UPDATE: MySQL 5.6 exists and has FULLTEXT indexes. If you are using FULLTEXT indexing in MySQL 5.6, make sure you are using the InnoDB-specific FULLTEXT options)
Before MySQL 5.6, This means you cannot convert MyISAM to InnoDB.

MySQL 5.5 and back
To locate which MyISAM tables have a FULLTEXT index run this query:
select tbl.table_schema,tbl.table_name from
(
    select table_schema,table_name
    from information_schema.tables
    where engine='MyISAM'
    and table_schema NOT IN ('information_schema','mysql')
) tbl
INNER JOIN
(
    select table_schema,table_name
    from information_schema.statistics
    where index_type='FULLTEXT'
) ndx
USING (table_schema,table_name);

Whatever comes out of this query cannot be converted to InnoDB until you upgrade to MySQL 5.6.
OPTIMIZE TABLE
MyISAM

The MyISAM table is shrunk
ANALYZE TABLE runs index statistics on all indexes

InnoDB

ANALYZE TABLE is totally useless because index stats are always being recollected
With innodb_file_per_table disabled, ibdata1 will grow bigger
With innodb_file_per_table enabled, tablespace (.ibd) file is shrunk


What is the maximum length of a comment in PostgreSQL? PostgreSQL has a special piece of a functionality called COMMENT which attaches metadata to an identifier or any type (schema, table, column, function, type, domain, etc). It looks like this,

COMMENT ON TABLE mytable IS 'This is my table.';

Is there a maximum size for the comment?

COMMENT has no impositions other than it fit into a TEXT field (verified in IRC with RhodiumToad),

From the docs,

  Very long values are also stored in background tables so that they do not interfere with rapid access to shorter column values. In any case, the longest possible character string that can be stored is about 1 GB.

So if you're under a GB or thereabout, you're probably fine.


Delete query does not end I have a following table with 1 million records :

CREATE TABLE `activity` (
  `id` int(9) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `subscription_id` int(10) unsigned NOT NULL,
  `vid` int(10) unsigned NOT NULL,
  `action` enum('init','confirm','suspend') DEFAULT NULL,
  `source` varchar(20) DEFAULT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `num` varchar(32) NOT NULL,
  `model` varchar(20) DEFAULT NULL,
  `price` float(5,2) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `num` (`num`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

I want to delete entries for same num value but that are old. So I came up with following query :

delete activity
 from activity
 inner join (
   select max(id) as maxid, num
     from activity
    group by num
   having count(*) > 1) duplic on duplic.num = activity.num
where activity.id < duplic.maxid;

The select query works fine, returns around 400.000 records. But if I try delete, the query executes infinitely. What might be wrong with this? How to investigate?

Plan A:

Create temp table with num and max(id).  400K rows
Chunk that table to do only 1000 deletes at a time; there would be a JOIN in the DELETE to the temp table.

More on Chunking for DELETE.

Plan B:

You need to keep 60% of the table, correct?  So, it may be faster to rebuild the table with only the desired rows.

CREATE TABLE new LIKE activity;
ALTER TABLE new ADD UNIQUE(num);   -- in order to catch dups
INSERT IGNORE   -- silently ignore dup nums
    INTO new SELECT * FROM activity
    ORDER BY num, id DESC;   -- same effect as MAX
RENAME TABLE activity TO old, new TO activity;
DROP TABLE old;

Plan C:

ALTER TABLE IGNORE activity
    DROP KEY num,
    ADD UNIQUE(num);

Problem (with Plan C): you cannot predict which of dup rows will be deleted.


Help with stored procedure and update table I am trying to write a stored procedure that has parameters and updates a table with the supplied title and middleName for the person having the businessEnityID.

Create Procedure sp_PersonEditor
 @BusinessEntityId (int, not null),
 @PersonType (nchar(2), not null),
 @NameStyle (bit, not null),
 @Title (nvarchar(8),  null),
 @FirstName (nvarchar (50), not null),
 @MiddleName (nvarchar (50), null),
 @LastName (nvarchar (50), not null),
 @Suffix (nvarchar (10), null),
 @ModifiedDate (datetime, not null)
as
 update BussinessEntityId
 set Title = @NewTitle, MiddleName = @NewMiddleName
 where BussinessEntityId = @BussinessEntityId
end

This is the error message I am getting:

  Incorrect syntax near ','.

These are my columns for the database.

BusinessEntityId (int, not null)
PersonType (nchar(2), not null)
NameStyle (bit, not null)
Title (nvarchar(8),  null)
FirstName (nvarchar(50), not null)
MiddleName (nvarchar(50), null)
LastName (nvarchar(50),not null)
Suffix (nvarchar(10,)null)
ModifiedDate (datetime, not null)

If this is SQL Server procedure, leave out the parenthesis and commas:

Create Procedure sp_PersonEditor
  @BusinessEntityId int not null,
  @PersonType nchar(2) not null,
  @NameStyle bit not null,
  @Title nvarchar(8) null,
  @FirstName nvarchar (50) not null,
  @MiddleName nvarchar (50) null,
  @LastName nvarchar (50) not null,
  @Suffix nvarchar (10) null,
  @ModifiedDate datetime not null
as
  update BusinessEntityId
  set Title = @Title, MiddleName = @MiddleName
  where BusinessEntityId = @BusinessEntityId
end

Also, I would not recommend naming procedures sp* since that's meant for system procedures and causes performance overhead. Fixed also the typos (Business vs Bussiness). You don't have variables New* so I removed that prefix too.


decrement all columns entry when row is deleted I have a column in a table containing the row_number, some kind of id number to identify the row. When I delete one row, I need the other rows to have their row_number decremented, to keep them sorted. What's the best way to do it? I've seen that auto_increment is not good, so what do can I use? Programatically updating all tables entries sounds bad for me, isn't it?

If the objective is to be able to sort the rows according to row_number then don't bother updating values on a delete.  The rows will come out in the same order after a row is deleted as they would before.  It is not worth the effort to close up the gaps.  In the worst case, if you delete the lowest-numbered row, you will have to update every remaining row in the table, in a transaction, with locking and all the concurrency problems that involves.  Don't do it.

If you absolutely must have some visual indication to users then let the application UI produce one, or use a function like ROW_NUMBER() at run time (SQL Server; other RDBMSs have similar).


SSAS Tabular model Date Dimension with Time possible? I am in the process of building my first SSAS Tabular model and thought everything was going well, until trying to create a measure by DateTime.

In my warehouse, I have a DIm_Time dimension, which has a DateTime column, with a row for every 5 minutes for the last 2 years.
5 Minutes is the granularity that we require. I addition, there is a TimeID Identity column on the table. IN my ETL, I assign a Time ID to each fact table depending on what 5-minute range it slots into.
So the end result, is a relationship between the 2 tables on the ID, with let's say 10 facts per TimeID.

Now I am trying to do a simple count of rows per time range. For example, how many facts for the current hour. The problem I'm finding is that I am seeing no data in either PowerBI or Excel when testing my measure, and I'm 90% sure it's related to the fact that my model is filtering my DateTime as a date when calculating the measure.

Fcts by Date:=
CALCULATE ( COUNTA(Fct_Table[IDColumn]), Dim_Time[DateTime])

Please, can someone help point me in the right direction as I am struggling to find anything with regards to working with a DateTime dimension.
Thank you very much.

I am, using SQL/SSAS 2017

Generally I keep Date and Time in separate dimensions as this reduces the overall cardinality but even with your date dimension having a time element, you should not have a problem obtaining this value. The example below was written on my own system and tested in DAX Studio:

DEFINE
    MEASURE Sales[TransactionCountPerTimePeriod] = COUNTROWS('Sales')
EVALUATE
SUMMARIZECOLUMNS (
    'Date'[DateKey],
    "Transaction Count", [TransactionCountPerTimePeriod]
);

In order to get this work within a model, just take the definition of the measure and include it, like so:

TransactionCountPerTimePeriod:=COUNTROWS('Sales')

Then, after publishing your model you should see it show up in DAX Studio, Power BI, Excel or any other reporting tool:

-- no need to define the measure if it is already in the model
-- just use it
EVALUATE
SUMMARIZECOLUMNS (
    'Date'[DateKey],
    "Transaction Count", [TransactionCountPerTimePeriod]
);


Should I create a nonclustered index on all searchable columns or just each column separately? I am not sure as to properly use non-clustered indexes. The SSMS plan estimator said to add another index when the following index was on the table already.

    CREATE NONCLUSTERED INDEX [ix_zone_fetch_shipping] ON [dbo].[tbl_shipping_rates_zones]
    (
        [iso] ASC, [mzone] ASC, [postal] ASC
    )
    INCLUDE
    (
        [region], [zone_dom], [zone_emi], [zone_pmi], [zone_fci],
        [zone_ups], [zone_fed]
    )

My query was

SELECT * FROM tbl_shipping_rates_zones WHERE postal = '10001'

My question is... should I be creating an index for all possible look up keys? I search by mzone, iso, and postal in different queries.

Thanks

SQL Server will show you an Index it would like to use, since it estimates that index would make life easier.  There is certainly no need to create an index on every searchable field, in fact doing so will make write performance substantially worse.  If you have less than say 5 to 10 indexes on the given table, and you run that query all the time, you probably want to add the suggested index exactly as SSMS shows it.

JNK's Edit Below

It's also important to look at the suggested indexes together. The suggestions are all in silos, so to speak, so one entire suggested index could be replaced by adding an INCULDEd field on another index.

For instance, it's possible to have suggestions for these two indexes:

CREATE INDEX ix_IndexA ON Table (ColA, ColB)

CREATE INDEX ix_IndexB ON Table (ColA) INCLUDE (ColC)

When in fact you could meet the needs of both with:

CREATE INDEX ix_IndexC ON Table (ColA, ColB) INCLUDE (ColC)

I've seen this with much more complicated indexes (10-20 key fields, 10-20 included fields) where adding one field to the INCLUDE list will eliminate the need for the second index.


SET extra_float_digits = 3 I'm using PostgreSQL 9.2 with my web application that has three servlets with three selects.

I turn on the log.

When I send my first query: select * from "Nomi" limit 4

before it the log shows me this other line:

execute <unnamed>: SET extra_float_digits = 3

What is this command?

Looks like that is a default setting in postgresql 8.5 onwards to guarantee recovery of higher precisions floats..

http://archives.postgresql.org/pgsql-bugs/2009-09/msg00171.php


Unable to read master log position while setting up MySQL slave Hi I am trying to setup a data replication server.
I am using MariaDB and phpMyAdmin to interface with the 2 DB's

I have set up my master with adding the following configuration to my my.ini file

server-id=691391
log_bin=mysql-bin
log_error=mysql-bin.err
binlog_do_db=mytestdb

I have then restarted the MySQL server so that phpMyAdmin now shows that it is set up correctly as a master as seen in the screenshot below:

I have then logged into my slave DB with phpMyAdmin and tried to connect it as a slave. When I enter in the details of the slave user that I have already created, I get the error message on the slave

Unable to read master log position. Possible privilege problem on master

Does anyone know what the issue could be here as I cant seem to work it out.

Thanks

You need to give privileges to replicate the Master.

Below is the example.

in Master DB to give privileges.

CREATE USER 'replication_User'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replication_User'@'%';
flush privileges;

Run below command on slave db to see master status.

SHOW MASTER STATUS\G;

Run below command on Master to see slave status.

SHOW SLAVE STATUS\G;


Intellisense Not Working in SSMS 2016 I am working with an Azure SQL Server database in SSMS 2016. I have Intellisense enabled in Tools -> Options -> Text Editor -> Transact-SQL -> Intellisense, however I have never had Intellisense working on this computer. Any advice on how to resolve this would be appreciated.

Please answer the following questions:

Can you tell us the performance tier of the Azure SQL DB you’re connecting to? Is it Basic or Standard or Premium? How many DTUs?
How many tables/views etc. do you have in the database?
Aside from IntelliSense not working, are you able to connect normally to this database, run queries and view and edit data as you expect from SSMS?

The reason behind all these questions is to understand whether you’re hitting the DTU limit for your database. It can sometimes take longer for SSMS to build the initial cache for IntelliSense and for T-SQL completions to work. While Microsoft continues to optimize them, some of the queries SSMS uses are expensive so a low DTU database with many objects may take longer to respond.

Newer version of SSMS run the most optimized queries for intellisense. And if you are using Azure Managed Instance (MI) you should go to with SSMS v18, although is still in preview has a lot of fixes for MI.


Licensing SQL Server 2019 on VMWARE Our company has SQL Server 2014 and 2016 and will acquire licenses for SQL Server 2019.
Our environment is in VMWARE, and as I understand it, one license is required for each V-Core that the server is using.

So if my server has 12 V-Core will I need 12 licenses?

I read this document from Microsoft and understood it this way, is that correct?
SQL Server 2019 Licensing Guide
Thanks.

Unfortunately as mustaccio mentioned, we don't answer licensing questions, partly because it can be situational. Your best bet is to talk to Microsoft directly or work with a Microsoft licensed VAR. That being said, in general what you said is true if you're doing Core (not CAL) licensing, you would need a 12 core license (licenses are sold by 2-core increments).


Help with Postgres 9.1 data import (is 4x expected data size, 4x slower than MySQL, and index still needed) I have 25 billion <int, int, float> rows that I'm trying to import into Postgres, and after 77% of the data being imported, the Postgres data folder is taking up 840GB, about 4x the storage requirements for that many rows at 12 bytes each.  Additionally, importing is taking 4x longer than my same import on MySQL (as described in MySQL MyISAM index causes query to match no rows; indexes disabled, rows match).

Here are my commands:

mydb=# CREATE TABLE mytable (id1 int, id2 int, score float)
$ psql mydb -c "COPY mytable (id1, id2, score) FROM 'file_000'"
$ psql ...
$ psql mydb -c "COPY mytable (id1, id2, score) FROM 'file_099'" # 100 files total

I'm running Postgres 9.1.  There are no other tables in the database.  This is not a production environment. The files are TSV text files. The only output from each COPY command is something like "COPY 256448118" -- at least until I ran out of disk space.

Am I doing something wrong here, or is this the expected behavior?

Subquestion 1: Where is this extra storage overhead coming from, and can I avoid it?

Update: It looks like there is a HeapTupleHeader of 23 bytes on each row, so that probably explains this overhead (source: StackOverflow post). Any way to avoid this?

Subquestion 2: If storage requirements are indeed 4x that of expected size, can I speed up importing (i.e. with some configuration change, etc)?

Subquestion 3: I need an index on id1, so what will storage requirements be for that during and after creation (I planned to run CREATE INDEX id1x ON mytable (id1))?

SubQuestion 1: I don't know of any way to get rid of the 23 byte header. There is also a 24 byte header on the page. http://www.postgresql.org/docs/8.3/static/storage-page-layout.html

SubQuestion 2: This guide should help. As suggested, increasing maintenance_work_mem is advised, but also increasing checkpoint_segments should help (to reduce the frequency of checkpoints). You've set your work_mem at 64GB, which seems really high. I would suggest keeping it under 2GB even on a system like yours. work_mem is for doing sorts and operations of that nature. http://www.postgresql.org/docs/current/interactive/populate.html

SubQuestion 3: I am not able to find any information on the physical layout of indexes within the pages. I would create a temporary table, fill it with test data (1 million rows?), create a temporary index, get the size of the index from the system, then divide by the number of rows to get the average. I can't show you an SQLFiddle version because it doesn't allow access to system functions. Try these techniques: http://postgresql.cc/postgres-index-size

PS - 25 billion rows? That's more than I've ever dealt with. I'm in the 4 billion range for one particular table ... For comparison, other database servers, like SQL Server 2008+, have a minimum 9 byte header for each row, so you can't entirely get rid of it but I've always thought that PostgreSQL's 23 byte header was a bit excessive.


Database migration, how to I have two databases DB1 and DB2. In both databases exists this two tables mo_sms and mt_sms. This is the structure of those tables:

CREATE TABLE IF NOT EXISTS `mo_sms` (
  `id_MO` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sms_proceso` char(1) NOT NULL COMMENT 's=SMS c=CHAT d=Descargas',
  `msisdn` varchar(20) NOT NULL,
  `texto_sms` varchar(160) DEFAULT NULL,
  `brand_id` int(10) unsigned NOT NULL,
  `fecha_sms` datetime NOT NULL,
  `comando_id` int(10) unsigned NOT NULL DEFAULT '0',
  `alias_desc` varchar(25) DEFAULT NULL,
  `shortcode_id` int(10) unsigned NOT NULL,
  `precio` float(11,2) unsigned DEFAULT '0.00' COMMENT 'Precio del MO',
  `id_user` int(10) unsigned NOT NULL,
  `state` char(1) NOT NULL DEFAULT '0' COMMENT '0=Por procesar 1=Procesado',
  `tipo_sms` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0=Normal, <>0 dependera del tipopredeterminado',
  `anio_sms` smallint(4) unsigned NOT NULL DEFAULT '0',
  `mes_sms` smallint(2) unsigned zerofill NOT NULL DEFAULT '00',
  PRIMARY KEY (`id_MO`),
  KEY `ix_carrier` (`brand_id`),
  KEY `ix_fecha_sms` (`fecha_sms`),
  KEY `ix_fecha_carrier_keyword` (`fecha_sms`,`brand_id`,`alias_desc`),
  KEY `ix_msisdn` (`msisdn`),
  KEY `ix_sms_proceso` (`sms_proceso`),
  KEY `ix_sms_proceso_state` (`sms_proceso`,`state`),
  KEY `ix_id_user` (`id_user`),
  KEY `ix_fecha_sms_user` (`fecha_sms`,`id_user`),
  KEY `ix_varios` (`anio_sms`,`mes_sms`,`comando_id`,`shortcode_id`,`brand_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Tabla de MO' AUTO_INCREMENT=82972 ;

CREATE TABLE IF NOT EXISTS `mt_sms` (
  `id_MT` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sms_proceso` char(1) NOT NULL DEFAULT 'c' COMMENT 's=SMS c=CHAT d=Descargas',
  `msisdn` varchar(20) NOT NULL,
  `texto_sms` varchar(160) DEFAULT NULL,
  `brand_id` int(10) unsigned NOT NULL,
  `fecha_sms` datetime NOT NULL,
  `comando_id` int(10) unsigned NOT NULL DEFAULT '0',
  `alias_desc` varchar(25) DEFAULT NULL,
  `shortcode_id` int(10) unsigned NOT NULL,
  `id_user` int(10) unsigned NOT NULL,
  `tipo_sms` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '0=Normal, <>0 dependera del tipopredeterminado',
  `id_MO` int(11) unsigned NOT NULL,
  `state` char(1) DEFAULT '0' COMMENT '0=Por Procesar 1=Procesado',
  `anio_sms` smallint(4) unsigned NOT NULL DEFAULT '0',
  `mes_sms` smallint(2) unsigned zerofill NOT NULL DEFAULT '00',
  PRIMARY KEY (`id_MT`),
  KEY `ix_carrier` (`brand_id`),
  KEY `ix_fecha_sms` (`fecha_sms`),
  KEY `ix_fecha_carrier_keyword` (`fecha_sms`,`brand_id`,`alias_desc`),
  KEY `ix_msisdn` (`msisdn`),
  KEY `ix_sms_proceso` (`sms_proceso`),
  KEY `ix_id_user` (`id_user`),
  KEY `ix_fecha_sms_user` (`fecha_sms`,`id_user`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Tabla de MT' AUTO_INCREMENT=93853;

I have some values in DB2 that should be migrated (inserted) in DB1. My problem here is I don't know how to get the records from DB2.mo_sms tables and those relations from DB2.mt_sms and then insert to DB1.mo_sms and DB2.mt_sms. For example I'll get all the records from mo_sms with fecha_sms LIKE '%2013-04-19%' then if I insert those records in DB1.mo_sms new IDs will be generated then the integrity will be lost. Should I make this programatically or there is any way to do this using just SQL queries? I'm using MySQL as DBMS.

Data Extraction into a Sandbox

CREATE DATABASE IF NOT EXIST sms_sandbox;
CREATE TABLE IF NOT EXISTS sms_sandbox.mo_sms LIKE DB2.mo_sms;
CREATE TABLE IF NOT EXISTS sms_sandbox.mt_sms LIKE DB2.mt_sms;
#
# Drop All Indexes From the Sandbox
#
ALTER TABLE sms_sandbox.mo_sms
    DROP INDEX `ix_carrier`,
    DROP INDEX `ix_fecha_sms`,
    DROP INDEX `ix_fecha_carrier_keyword`,
    DROP INDEX `ix_msisdn`,
    DROP INDEX `ix_sms_proceso`,
    DROP INDEX `ix_sms_proceso_state`,
    DROP INDEX `ix_id_user`,
    DROP INDEX `ix_fecha_sms_user`,
    DROP INDEX `ix_varios`;
ALTER TABLE sms_sandbox.mt_sms
    DROP INDEX `ix_carrier`,
    DROP INDEX `ix_fecha_sms`,
    DROP INDEX `ix_fecha_carrier_keyword`,
    DROP INDEX `ix_msisdn`,
    DROP INDEX `ix_sms_proceso`,
    DROP INDEX `ix_id_user`,
    DROP INDEX `ix_fecha_sms_user`;
ALTER TABLE sms_sandbox.mo_sms AUTO_INCREMENT=1;
ALTER TABLE sms_sandbox.mt_sms AUTO_INCREMENT=1;
#
# Load the Data From 2013-04-19
#
INSERT INTO sms_sandbox.mo_sms
SELECT * FROM DB2.mo_sms
WHERE fecha_sms >= '2013-04-19 00:00:00'
AND fecha_sms <= '2013-04-19 23:59:59';
INSERT INTO sms_sandbox.mt_sms
SELECT * FROM DB2.mt_sms A INNER JOIN sms_sandbox.mo_sms B USING (id_MO);

Data Insertion From the Sandbox

#
# Get Maximum id_MO from DB1.mo_sms
# Increment all id_MOs by the Max Value
#
SELECT MAX(id_MO) INTO @Max_id_MO FROM DB1.mo_sms;
UPDATE sms_sandbox.mo_sms SET id_MO = id_MO + @Max_id_MO;
UPDATE sms_sandbox.mt_sms SET id_MO = id_MO + @Max_id_MO;
#
# Load the New Data into DB1
#
ALTER TABLE DB1.mo_sms DISABLE KEYS;
INSERT INTO DB1.mo_sms SELECT * FROM sms_sandbox.mo_sms;
ALTER TABLE DB1.mo_sms ENABLE KEYS;
ALTER TABLE DB1.mt_sms DISABLE KEYS;
INSERT INTO DB1.mt_sms
(`sms_proceso`,`msisdn`,`texto_sms`,`brand_id`,
`fecha_sms`,`comando_id`,`alias_desc`,`shortcode_id`,
`id_user`,`tipo_sms`,`id_MO`,`state`,`anio_sms`,`mes_sms`)
SELECT
    `sms_proceso`,`msisdn`,`texto_sms`,`brand_id`,
    `fecha_sms`,`comando_id`,`alias_desc`,`shortcode_id`,
    `id_user`,`tipo_sms`,`id_MO`,`state`,`anio_sms`,`mes_sms`
FROM sms_sandbox.mt_sms;
ALTER TABLE DB1.mt_sms ENABLE KEYS;

WARNING : Practice this on a Staging Server, Please.

SUGGESTION

All the mt_sms tables should have an index on id_MO

ALTER TABLE mt_sms ADD INDEX (id_MO);

Give it a Try !!!


cannot create view in oracle 12c I have a simple query like this:
select d.file_name, d.tablespace_name, s.segment_type, s.segment_name,
round(s.bytes/1024/1024,2) MB
from dba_data_files d
inner join user_segments s on d.tablespace_name = s.tablespace_name;

It works fine. But what i really want is to create a view with this query.
So i put:
create view espaco as
select d.file_name, d.tablespace_name, s.segment_type, s.segment_name,
round(s.bytes/1024/1024,2) MB
from dba_data_files d
inner join user_segments s on d.tablespace_name = s.tablespace_name;

But it doesnt work, it says:

ERROR at line 4:
ORA-01031: insufficient privileges

So, how come I can do this query normally, but if I create view it doesnt work?
I can create other views, but when I create a view to search from dba_data_files it doesn`t work.
The user has create view privileges, dba privileges, any privileges, and still doesnt work.
Does anyone have any idea why?

It sounds like you don't have direct access to the dba views and have it granted through a role. You can check out this answer on Stack Overflow for a little more information.

https://stackoverflow.com/questions/28936449/grant-create-view-using-system-view-with-dba-privilage-in-oracle


PostgreSQL isn't using indices in FULL OUTER JOIN I have these tables I'd like to do a FULL OUTER JOIN between. They both have an index on (ts_minute, user_id, campaign_id, host_id) (which matches the join condition). In the following query, if I remove FULL OUTER from the query, it runs 10,000 times faster because it uses the indices (confirmed using EXPLAIN SELECT ...). Problem is, I need FULL OUTER.

select user_id, campaign_id, sum(i.cost), sum(b.bids_internal), sum(b.eligible_requests)
    from impr_summary_test i
    join bid_summary_test b USING (ts_minute, user_id, campaign_id, host_id)
    where ts_minute > now() - interval '5 min'
    group by 1, 2 order by 1,2;

There are indices on just (ts_minute) as well. It would be nice if it could use this index. The number of rows returned in the slow case is ~330, the number of rows returned in the fast case is ~220.

Postgres 9.3.

I've tried re-writing this as two separate sub queries, each with their own (identical) WHERE clause, and then JOIN'ing those results, and that does speed it up, but the basic INNER JOIN case is still 5 times faster than the re-write.

How can I speed this up by getting it to use the indices?

Number of rows in bid_summary_test: 8,292,673

Number of rows in bid_summary_test that match predicate ts_minute > now() - interval '5 min' : 28,849

Number of rows in impr_summary_test: 2,523,534

Number of rows in impr_summary_test that match predicate ts_minute > now() - interval '5 min' : 11,081

When combining two tables with FULL OUTER JOIN all  rows are included in the result. Typically, it doesn't make sense to use indexes for the joining columns. That's why attempts with indexes on (ts_minute, user_id, campaign_id, host_id) will be fruitless. It just so happens that the first column also fits the relevant predicate on ts_minute. See below.

The query also returns substantially fewer rows with an [INNER] JOIN, which explains some of the performance difference - but certainly not factor 10.000. Your updated info confirms that fewer rows are returned (220 vs. 330) but that's still not the whole story. The difference could be much bigger before aggregation.

Your predicate on ts_minute is the better candidate for index support. There are two indices that would seem applicable for the predicate ts_minute > now() - interval '5 min' (pending information on actual data types). But the appearance is deceitful: The resulting column ts_minute after the FULL [OUTER] JOIN is a combination of the columns in both underlying tables. It's like an expression that's not sargable and cannot use indices on ts_minute on the underlying tables.

You have to apply the predicate before the FULL [OUTER] JOIN to allow index support. That's what you already stumbled upon in your added test with two separate sub queries. You'll find index (only) scans or probably bitmap index scans if you inspect the EXPLAIN output.

Since you GROUP BY user_id, campaign_id, it is irrelevant whether you add the columns ts_minute and host_id to the join condition in this query. ts_minute is only necessary for the WHERE condition.

SELECT user_id, campaign_id
     , i.sum_cost, b.sum_bids_internal, b.sum_eligible_requests
FROM  (
   SELECT user_id, campaign_id
        , sum(cost) AS sum_cost
   FROM   impr_summary_test
   WHERE  ts_minute > now() - interval '5 min'
   GROUP  BY 1, 2
   ) i
FULL JOIN (
   SELECT user_id, campaign_id
        , sum(bids_internal) AS sum_bids_internal
        , sum(eligible_requests) AS sum_eligible_requests
   FROM   bid_summary_test
   WHERE  ts_minute > now() - interval '5 min'
   GROUP  BY 1, 2
   ) b USING (user_id, campaign_id)
ORDER  BY 1,2;

Assuming that only a very small percentage of the rows in each table lie within the "last 5 minutes", an index on ts_minute in both tables should make this very fast.

If your write patterns and autovacuum allow index-only scans, these indices would give you best performance:

CREATE INDEX ON impr_summary_test (ts_minute, user_id, campaign_id, cost);
CREATE INDEX ON bid_summary_test  (ts_minute, user_id, campaign_id, bids_internal, eligible_requests);

Weigh cost and benefit for highly specialized indexes.


optimize a 'having count (distinct ) ' query for big data I have below query that I need to run on a table with 100million records but it's extremely slow (been running for 5 hours so far)

I am not sure how to optimize it, would be grateful for any help.

The table has an index on DID and week_no, and contains several other columns not indexed, and a primary key (id) indexed

DELETE FROM test
WHERE "DID" IN (SELECT "DID"
                FROM test
                GROUP BY "DID"
                having count(distinct week_no) < 4 );

A big part of the performance problem is DELETE, which will save the old values in case of a crash or other ROLLBACK.

If there is a composite INDEX(DID, week_no) (in that order), there is some hope.  With that index, walk through it a few DIDs at a time.  That is, put the delete in a loop that gradually increases DID.

See this for discussion of ways to do the walking, especial with a composite index.

The overall time taken by chunking may actually be less, but it will still be a long time.  If you need to run this query frequently, let's discuss other techniques to prepare for the need.


Mongo db sharded "the collection's metadata lock is taken" issue coming very frequently I am seeing this warning in mongoS log consistently for one collection.

[conn96] warning: splitChunk failed - cmd: { splitChunk: "ibeat20150105.dgrpCount", keyPattern: { articleId: 1, host: 1 }, min: { articleId: MinKey, host: MinKey }, max: { articleId: MaxKey, host: MaxKey }, from: "shard0000", splitKeys: [ { articleId: "01a12144225c646875aeea79990432", host: "xyz.com" } ], shardId: "ibeat20150105.dgrpCount-articleId_MinKeyhost_MinKey", configdb: "x.x.x.x:27017,x.x.x.x:27017,x.x.x.x:27017" } result: { who: { _id: "ibeat20150105.dgrpCount", state: 1, who: "ibeatdb61:27017:1420207410:680478249:conn105:432206596", ts: ObjectId('54aa4f1c253720e5ba254fde'), process: "ibeatdb61:27017:1420207410:680478249", when: new Date(1420447516889), why: "split-{ articleId: MinKey, host: MinKey }" }, ok: 0.0, errmsg: "the collection's metadata lock is taken" }

This is coming continuously for one collection.
Also when i checked the data distribution this whole data is on one shard only for this particular collection.
I check lock with id: balancer it seems to be working fine. as i did not get a time for which it has locked indefinitely.
I have 5 mongos servers running in my application

This is creating load on my primary shard which is impacting write operations.
Could you please help me.

There are two things which take a meta data lock on a collection - the balancer (to move a chunk) and a mongos (to split a chunk).  Now, sometimes you will get this warning (it is just that, a warning) if, for example, a mongos attempts to split while another mongos is already performing a split.  Or, it might be that the balancer is moving chunks and so the split cannot get a lock for that reason.

In either case, the chunk will usually get split eventually and things will settle down, but you have indicated that is not happening.  If the balancer is running constantly then you may want to re-evaluate your shard key choice and whether you need to pre-split etc. to alleviate the imbalances you are creating.

In any case, one thing that you can do to give the mongos a better chance to be able to split without an error is to temporarily disable the balancer to see if stopping it allows the splits to happen.  This is covered as part of the more detailed and complicated procedure I am about to cover for the more extreme case.

That extreme case would involve some sort of issue with stale locks.  As mentioned in the comments, this is intended as a break-fix type of intervention to get that particular collection split.  It is a lot of work, and it is not really for beginners.  The lock that is blocking the splits should expire eventually on its own eventually, but you may not have the time to wait for that to happen.  To force the issue, we need to basically do the following:

Reduce contention for the meta data lock (balancer, and splits)
Connect to a mongos, split the chunk on the collection manually
Manually move some chunks for the collection onto other shards (manual balancing)
Restore normal balancing and splitting

Note: this will require you to restart mongos processes if you want to be absolutely sure.  That will usually mean down time for your application and some errors getting thrown back to your app when you do the restarts. For this reason, and for general safety I would recommend doing this at a low traffic or scheduled maintenance time. For reference in terms of how splitting happens, see this answer.

First, turn off the balancer:

sh.stopBalancer()

The command will wait for balancing to complete before returning, so this may take some time if the balancer is currently actively balancing chunks.  To verify the state of the balancer you can use sh.getBalancerState() and sh.isBalancerRunning(). This is also the piece you should try initially as mentioned at the start of the answer.

Next, restart all of your current mongos processes with the --noAutoSplit option. By doing these two things you have essentially guaranteed that:

Nothing else will be trying to make meta data changes on this cluster (which will allow you to do so manually without error)
If there are any remaining locks blocking you from taking action in the config.locks collection, they are now definitely stale and can be removed if necessary.

Next, you need to manually split the chunk on the collection that has been failing.  The easiest command to do so is splitFind().  It will basically work with any valid query for you since you only have one (Min to Max) chunk:

sh.splitFind( "ibeat20150105.dgrpCount", { articleId: $SOMEVALUE, host: $SOMEVALUE } )

This will split the chunk in two, and you can repeat for the resulting chunks (with different values to target different chunks) to get to a decent number - I would aim for at least the same number of chunks as you have shards so that you can move data to them, but basically the only real limit is that the new chunks must be less than you max chunk size (64MB by default) in order to be moved successfully.  If you know your current approximate data size (from db.collection.stats()) for example, then it should be easy to figure out how many chunks you are going to need.

If you encounter a similar error regarding locks being taken and you are sure you have disabled the balancer and splitting on all mongos processes then you can remove the entries from the config.locks collection (because they must be stale) manually and retry.  As long as you run the remove command from a mongos and not directly on the config servers, this should be safe enough.

Once you have your split and appropriately sized chunks you can move them from shard0000 with the moveChunk() command, something like this (to move to the theoretical shard0001):

sh.moveChunk("ibeat20150105.dgrpCount", { articleId: $SOMEVALUE, host: $SOMEVALUE }, "shard0001")

Once your chunks have been moved to your satisfaction, you can restart your mongos processes without the --noAutoSplit option, returning them to normal.  When that is complete, re-enable normal balancing with sh.startBalancer().


How to make a duplicate field cheap? There are two tables associated with each one to many in a field.
In Table 2, there are fields that duplicate the value in Table 1.

Is there a cheaper way to ensure (guarantee) the correspondence fields except FK-type (id, x, y, z) in unique index in Table 1?

Creation of an index of type (id, x, y, z) and one FK (there certainly can be replaced by FK, but FK on one field looks easier) is very expensive because the tables are very large.

I don't know if there's a cheaper way to create a guarantee such as you are asking. But Foreign Key constraints are the tool for the job you are wanting to accomplish. It is probably better to use the right tool for the job in the long run. A hack solution might end up being a nightmare in administration and performance down the road.

Here is one possibility to get the constraint in with minimal downtime (logic only)

Create duplicate table structure of table 1 and table 2 (no data)
Create Unique Index on table1, and FK on table 2
Insert columns from table 1 into duplicate table 1, do the same for table 2
After you get all the info ported over, rename table1/table2 to old_table1/old_table2
Rename table1_new/table2_new to table1 and table2


How to access PostgreSQL Functions from Oracle through DBLink? I'm having two DB server (PostgreSQL, Oracle).
I'm able to access the table in PostgreSQL from Oracle through DB-Link.
I've use the below query to access the table.

select "Cust_Id", "Mobile_No" from "schema_name"."tablename"@Dblink where "cust_id" = '123456789';

As same as I'm trying to access the function in PostgreSQL from Oracle server through DBLink. But I can't access it.
I've used below query

select "Cust_id" from "schema_name"."function_name"@DbLink('123456789');

The function will return the mobileno and cust_id in table type.

I'm getting error like.

  SQL Command not properly ended

Pls help on this issue. Thnx in advance.

Have you tried creating a view in PostgreSQL and then query the view from Oracle? You need PostgreSQL to present the function like a view since Oracle doesn't really have that function.
I don't currently support PostgreSQL. However, if you create a view in PostgreSQL and test it to verify that it works, you should be able to select from that view in Oracle. It looks like the issue is selecting from a function, not selecting from PostgreSQL.


How to ignore columns in parquet/polybase import? I'm using polybase to import a parquet file.

Over time, it is likely we may add or remove named columns in the file.

When I add an additional column, I get the below error:

  External file access failed due to internal error: 'File test.parquet: HdfsBridge::CreateRecordReader - Unexpected error encountered creating the record reader: HadoopExecutionException: Column count mismatch. Source file has 16 columns, external table definition has 15 columns.'

This is because I added an additional column that wasn't in the external table definition.

As parquet contains a file schema and the external table knows the name of each column, is there a way it can be set to ignore the extra unused column?

Polybase does not have this ability.  The source file and external table definition must match for it to work.  Simply add or remove additional column(s) in your external table definition.  Then when you are physically creating your table in the database eg via CTAS, add/remove the additional column(s) from your CTAS statement.

Alternately, consider the new COPY INTO statement.  This does have the ability to specify a column list, eg

COPY INTO test_parquet ( col1, col2, col3, col4 )
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/*.parquet'
WITH (
    FILE_FORMAT = myFileFormat
    CREDENTIAL = ( IDENTITY = 'Shared Access Signature', SECRET = '<Your_SAS_Token>' )
)

However please note this feature is i) currently in preview, ii) for copying into physical tables (ie not external tables) and iii) I haven't tested the column list specifically with Parquet.


Selecting "consecutive" rows with same values I have a table with the following data:

userID  tStamp                status
------  -------------------   ------
Jason   2017-10-18 03:20:00   idle
Brown   2017-10-18 03:20:28   idle
Brown   2017-10-18 03:25:28   idle
Brown   2017-10-18 04:00:28   active
Brown   2017-10-18 04:10:28   active
Brown   2017-10-18 04:35:28   idle
Brown   2017-10-18 04:45:28   idle

I want to extract the consecutive rows with the same status. For example, I want to see the time spent by a user in the 'idle' status, followed by 'active' status, followed by 'idle' status, and so on.

How do I do this in a single SQL query?

My desired output is the following:

userID        staus          Duration_in_this_status (min)
------  -------------------   ------
Jason         idle             ---
Brown         idle              5
Brown         active           10
Brown         idle             10

SELECT userID, status, TIMESTAMPDIFF(minute, MIN(tStamp), MAX(tStamp)) AS duration
FROM (
    SELECT
    t.*
    , @groupNumber := IF(@prev_userID != userID OR @prev_status != status, @groupNumber + 1, @groupNumber) AS gn
    , @prev_userID := userID
    , @prev_status := status
    FROM t
    , (SELECT @groupNumber := 0, @prev_userID := NULL, @prev_status := NULL) var_init_subquery
    ORDER BY userID, tStamp
) sq
GROUP BY gn, userID, status

see it working live in an sqlfiddle

Here's how it works. We define three variables. One holding a group number and two holding the values of the previous row values of status and userId. Note, that in a relational database there is no order unless you specify it. This is very important. In the select clause, we first check, if the variable values differ from the current row. If yes, we increment the group number, if not we leave it as it is. After that, we assign the values of the current row. So when the variables are evaluated when the next row is processed, they still hold the values of the previous row. So the order is important here, too. In the outer query we simply can group by this group number to get the min and max values of the timestamp.

read more about user-defined variables here


What would be the Candidate key (s) of this relation? R(ABCDEPG)

F={AB->CD, DE->P, C->E, P->C, B->G}

To my consideration, the candidate keys are: AB, AG, CD, DE.

Am I correct? Why or why not?

Unless I have mistaken the question, your response of candidate keys is incorrect.

Candidate Key AB:
AB -> CD, C->E, DE->P, B -> G. This is a candidate key.

Candidate Key AG:
A, G, and AG have no inherent relations. Just because B->G doesn't mean that G->B (a similar mistake was made after assuming B was solved and getting the columns of A and B). This is not a candidate key.

Candidate Key CD/DE:
This is not a candidate key. There is no possible way for F to have a candidate key that does not include A, as there is no way to determine A without it being part of the key. Similarly, nothing implies B and thus it is required to be part of any candidate key.

Since AB is a candidate key, there is no reason to go further. Any candidate key must include AB and AB is a candidate key, therefore the only candidate key is AB. A and B must both be supplied as part of the key or else there can be no unique identifiers to their values. Since keys are typically generated with the intent to use the least information possible to determine uniqueness, AB being required for row uniqueness, and AB being a potential key then you can select it as your primary composite key and move on.


Data Source connects but linked server cannot initialize data source object in MS SQL Server I'm trying to create a linked server to an instance of MySQL. I've created a System DSN to the instance and that passes the connection test, but when I try to create a linked server using that data source, I get an error (specifically Cannot initialize data source object of OLE DB provider MSDASQL for linked server "MyServerName") The system error is 182, MS Sql Server error 7303. Screen shot below:

Below is a screen shot of the general settings (TEST_MYSQL is the name of the data source I've previously and successfully configured and tested):

I'm using the "use this security context" with a username and password that work
in the data source name. Below is a screen shot of the server options.

I'm not sure what I'm missing or doing wrong, but this isn't working. I would assume that something is configured wrong, but the data source connects successfully using the same creds. Is there something missing that would make the data source connect fine but prevent the linked server using that data source from doing so? Please advise, thanks!

So after trying a ton of things with options in the driver, settings in the MSDASQL provider, etc. Replacing the MySQL ODBC driver with an older version worked immediately. This solved my issue. thanks!


SQL Server datetime column datatype stores offset Does the SQL Server datetime column have the capability to store the offset of the time?

I'd like to store a value like

YYYY-MM-DDThh:mm:ss±hh

e.g.

2015-07-14T10:08:15+01

But when I insert the above value in the datetime column, the error that SQL Server returns is as follows:

insert into TestTable(testdatetimeoffset) values ('2015-07-14T10:08:15+01')

  Msg 241, Level 16, State 1, Line 1 Conversion failed when converting
  date and/or time from character string.

I also tried

insert into TestTable(testdatetimeoffset) values ('2015-07-14 10:08:15+01')

>

  Msg 241, Level 16, State 1, Line 1 Conversion failed when converting
  date and/or time from character string.

and

insert into TestTable(testdatetimeoffset) values ('2015-07-14 10:08:15 01')

  Msg 242, Level 16, State 3, Line 1 The conversion of a varchar data
  type to a datetime data type resulted in an out-of-range value. The
  statement has been terminated.

If datetime is unable to support the offset, what are the best ways to do this?

You might want to consider looking at the following page:

Date and Time Data Types and Functions (Transact-SQL)

Ther you will find a link to the following information:

datetimeoffset (Transact-SQL)

The datetimeoffset data type is defined as:

  Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.

An example is given as follows:

DECLARE @datetimeoffset datetimeoffset(4) = '12-10-25 12:32:10 +01:00';
DECLARE @date date= @datetimeoffset;

SELECT @datetimeoffset AS '@datetimeoffset ', @date AS 'date';

@datetimeoffset                  date
-------------------------------- ----------
2025-12-10 12:32:10.0000 +01:0   2025-12-10

(1 row(s) affected)

You might also want to look at the AT TIME ZONE (Transact-SQL) function when referencing a datetimeoffset value:

  Converts an inputdate to the corresponding datetimeoffset value in the target time zone. If inputdate is provided without offset information, the function applies the offset of the time zone assuming that inputdate value is provided in the target time zone. If inputdate is provided as a datetimeoffset value, than AT TIME ZONE clause converts it into the target time zone using time zone conversion rules.


MySQL migration with table verification I'm in the process of migrating from one MySQL server (5.0.95) to out new MySQL server (5.1.73).  I have a script that is transferring the schema's over and doing a table checksum using CHECKSUM TABLE schema.table; on both the source and destination servers.
This process on my testing schema can validate all but 90 of my 930 tables.  For those 90 how can I find out what the differences are?

I've tried saving them both, source and destination, out with mysqldump and using Beyond Compare on the resulting sql files.  This doesn't go well since Beyond Compare will split the insert lines where it chooses and in turn finds differences where it has done that.
Is there a tool or other MySQL check that I can do to find out why tables aren't the same?

Due to company policy I have to use RHEL 6 thus is why the old server is going away since it is on RHEL 5 and won't support RHEL 6.

MySQL Utility mysqldbcompare (https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqldbcompare.html) may be of help. It does provide details on any mismatches it finds when comparing objects (not just tables) on two MySQL Servers.


Using JOIN to find duplicates in another table taking too long Have a MySQL 8 database with the following tables.
people
id | mobile | name

registrations
id | people_id | other_stuff

I am trying to find the mobile number and the number of times it's duplicated by registrations. Some records in the people table do not have foreign records in the registrations table, so need to exclude them from our calculation.
If I do the following, the query runs relatively fast (500ms), but it excludes two or more registrations that have the same people_id
select mobile, count(mobile) as count from people where exists
(select people_id from registrations where people_id = people.id) group by mobile having count > 1;

The following returns what I need, it returns the above but also takes into account registrations with the same people_id, but the problem is, this takes about 6 seconds for around ~250,000 rows in patients and registrations.
select mobile, count(mobile) as count from people inner join registrations on
registrations.people_id = people.id group by mobile having count > 1;

I have indexes on the mobile and people_id columns.
Wondering if there's a faster way that returns exactly what I need.

I can't imagine that the first query would work.  The subquery needs count but that is not available until the entire query is finished.  The outer select can't move forward until the subquery provides the existence of each row.
Try to turn the entire query into a JOIN; no subquery.  In the ON you can test for one "people_id" being less than another -- this implies that there are two rows.


MySQL Master 5.5 - Slave 5.6 - unknown system variable 'binlog_checksum', Error_code: 1193 I see the below warning on the my slave server:

2016-11-18 22:48:03 12808 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2016-11-18 22:48:03 12808 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.018098' at position 920330293, relay log '/mnt/data/mysql/mysqld-relay-bin.318142' position: 473446279
2016-11-18 22:48:03 12808 [Note] Slave I/O thread: connected to master 'repl@xxx:3306',replication started in log 'mysql-bin.018103' at position 271452699
2016-11-18 22:48:03 12808 [Warning] Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Error_code: 1193
2016-11-18 22:48:03 12808 [Warning] Slave I/O: Unknown system variable 'SERVER_UUID' on master. A probable cause is that the variable is not supported on the master (version: 5.5.42-log), even though it is on the slave (version: 5.6.31-log), Error_code: 1193

From what I understand it is happening when master is not compatible with the 5.6 slave, is it correct ?
Should I ignore these warning ?
if not How do I fix this without damage anything the MYSQL MASTER ?

Master version:

Variable_name            Value
-----------------------  ------------------------------
innodb_version           5.5.42
protocol_version         10
slave_type_conversions
version                  5.5.42-log
version_comment          MySQL Community Server (GPL)
version_compile_machine  x86_64
version_compile_os       Linux

Slave version:

Variable_name            Value
-----------------------  ------------------------------
innodb_version           5.6.31
protocol_version         10
slave_type_conversions
version                  5.6.31-log
version_comment          MySQL Community Server (GPL)
version_compile_machine  x86_64
version_compile_os       Linux

Thanks !

If your run SHOW SLAVE STATUS\G on the Slave, and you see Slave_IO_Running: Yes, then the IO Thread is fine.
Notwithstanding, please note the Warnings

2016-11-18 22:48:03 12808 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0

Error Code 0 means it's not an error, just informational.

2016-11-18 22:48:03 12808 [Warning] Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Error_code: 1193

You mentioned the error involves the variable binlog_checksum.
According to the MySQL Documentation for binlog_checksum

Enabling this option causes the master to write checksums for events written to the binary log. Set to NONE to disable, or the name of the algorithm to be used for generating checksums; currently, only CRC32 checksums are supported. As of MySQL 5.6.6, CRC32 is the default.
This option was added in MySQL 5.6.2.

Since your Master is 5.5.42, there is no binlog_checksum available to compare the Master to the Slave. The warning is valid.
Perhaps setting binlog_checksum to 'NONE' on the Slave might disable this check.

2016-11-18 22:48:03 12808 [Warning] Slave I/O: Unknown system variable 'SERVER_UUID' on master. A probable cause is that the variable is not supported on the master (version: 5.5.42-log), even though it is on the slave (version: 5.6.31-log), Error_code: 1193

This is the same problem. The option server_uuid does not exist in MySQL 5.5.42 (the Master) as the message itself says.
EPILOGUE
As long as replication is running you are OK for the moment. It strongly recommend get your Master to MySQL 5.6.31 ASAP. Why ?
MySQL 5.6 introduced microseconds to DATETIME. If the Master (5.5.42) sends a DATETIME to the Slave (5.6.31), the IO Thread will break immediately. Please read my post from 2015-01-02 where I mentioned this.
You could work around this DATETIME mess by switching to STATEMENT replication (setting binlog_format to STATEMENT) on both Master (5.5) and Slave (5.6).


how to resolve "InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table" in mysql I am continuous getting above error in mysqld log. Please tell me what is this error about and how to solve it. I am using mysql 5.7 (not mariadb)

A guess:  A FULLTEXT index on that table is corrupted.

ALTER TABLE tablename ENGINE=InnoDB;

should rebuild the FT index, thereby fixing the problem.

If that is not sufficient, please provide SHOW CREATE TABLE; there may be some other clues there.


mysqldump: ERROR 2006: MySQL server has gone away when attempting to push data to RDS instance I'm attempting to migrate a MySQL database (10M+ records) from an EC2 instance into an RDS Instance using a script:

#!/bin/bash

mysqldump --databases my_db \
    --compress \
    --order-by-primary \
    --single-transaction \
    -u dba \
    -ppassword | mysql \
        --host=host.us-east-1.rds.amazonaws.com \
        --port=3306 \
        --max_allowed_packet=500M \
        -u dba \
        -ppassword

The script connects and begins to transfer data as expected, but then errors out after about 5 minutes with the following error:

ERROR 2006 (HY000) at line 1406: MySQL server has gone away
mysqldump: Got errno 32 on write

The script works fine for smaller databases, and for the larger database it transfers several million records before the error.

I'm not sure how to get past this issue.  I'd love to get some suggestions (parameter adjustments on EC2 or RDS, how to get more detail on error etc.)

The error
mysqldump: Got errno 32 on write

simply means you ran out of space.
The question is: Where is the space issue coming from ???
--order-by-primary

Using this requires creating a temp table in the folder configured in tmpdir
Bigger mysqldumps need a much larger tmpdir when using --order-by-primary
If you get rid of --order-by-primary, you can handle bigger dumps.
You just asked

what are the implications of removing --order-by-primary ?

There are improvements to the dump's creation
IMPROVEMENT #1 : Faster Dump
Every time you dump a table using --order-by-primary, a temp table must be created. That can pause the mysqldump on the source side. Such pausing will never occur anymore
IMPROVEMENT #2 : No bloated indexes
When you load a table in PRIMARY KEY order, it can generate up to 45% additonal wasted space in the indexes due to lopsided page splits in your BTREE indexes.

Jun 28, 2012 : Benefits of BTREE in MySQL
Oct 26, 2012 : How badly does innodb fragment in the face of somewhat out-of-order insertions?
Nov 26, 2012 : Does the Int type primary key insert order affect query performance in MySQL? (discusses this from MyISAM point-of-view)

IMPROVEMENT #3 : AUTO_INCREMENT PRIMARY KEYs
If the majority of your tables has a single integer that is auto incremented and is the PRIMARY KEY, the table is already ordered. No need for the overkill of --order-by-primary
Concerning your next comment

I removed --order-by-primary, got the same error at the same time :(

Please make sure you have extra diskspace on the RDS Instance. Why ?
It is possible for a dump to be bigger when reloaded, especially when exacerbated by having keys ordered.

Sep 09 , 2016 : mysqldump: ERROR 2006: MySQL server has gone away when attempting to push data to RDS instance
May 01, 2014 : Why would the size of MySQL MyISAM table indexes (aka MYI file) not match after mysqldump import?


Best way to increase no of partition in SQL Server Table I am maintaining a database with a big big table, which portioned for each month. There is already tens of partition there but its till January 2013, So its time to create new partitions.

Can anyone please advise, what's the best way to create new number of partitions.

I am doing the following step (Yet not did)

From SSMS, get the properties of Database and move to Files tab.
Click Add button on the bottom right to add a new line.
Put a new file name (e.g. FN_DW_Archieve_2013_02)
Put a new Logical Name (e.g. LN_DW_Archieve_2013_02)
Create a new Filegroup by selecting  in the dropdown under filegroup cell.
(e.g. FG_DW_Archieve_2013_02)
Hit Ok

This will create a new file group in my database.

Now the biggest problem which make me worry is to modify the Partition Function and Partition Scheme. Which expert advise, can I drop and recreate them again, does it affect my existing data. Or can I alter them with the new partition in it.

Thanks in advance.

It sounds like what you want to do is to create a new boundary for your existing partitioning implementation, and this can be one through ALTER PARTITION FUNCTION ... SPLIT RANGE ....

You will also need to run prior to this an ALTER PARTITION SCHEME ... NEXT USED ... to indicate the next filegroup that will hold the new partition (there are a few caveats to this, as quoted below).  Here is a quote from BOL regarding this operation:

  A filegroup must exist online and be marked by the partition scheme that uses the partition function as NEXT USED to hold the new partition. Filegroups are allocated to partitions in a CREATE PARTITION SCHEME statement. If a CREATE PARTITION SCHEME statement allocates more filegroups than necessary (fewer partitions are created in the CREATE PARTITION FUNCTION statement than filegroups to hold them), then there are unassigned filegroups, and one of them is marked NEXT USED by the partition scheme. This filegroup will hold the new partition. If there are no filegroups marked NEXT USED by the partition scheme, you must use ALTER PARTITION SCHEME to either add a filegroup, or designate an existing one, to hold the new partition.


The ALTER TABLE statement conflicted with the FOREIGN KEY constraint I am trying to add a new foreign key to an existing table where there is data in the column I am wanting to make a change to.

In dev, I have tried this where data does and does not exist. Where there is no data this works fine.

ALTER TABLE [rpt].ReportLessonCompetency WITH CHECK
ADD CONSTRAINT [FK_Grade_TraineeGrade_Id]
FOREIGN KEY (Grade) REFERENCES [rpt].TraineeGrade(Id)

Where there is data I get the following error

  The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Grade_TraineeGrade_Id". The conflict occurred in database "T_test", table "Core.Report.TraineeGrade", column 'Id'.

I would be grareful if someone could let me know what I need to do in order to ensure that this works where data does and does not exist as I cannot control if the live database will or will not have any existing data.

Thanks

Simon

You probably receive the error because you have orphaned records in the [rpt].[ReportLessonCompetency] table.
An orphaned record is a record in a child table without a corresponding parent record in the parent table. In your case the [rpt].[ReportLessonCompetency] will contain values
for [Grade] that don't exist in the [rpt].TraineeGrade(Id) table.

There are 2 options to get the foreign key created (though there's only 1 valid option imo).

Cleanup your data
First you could look up the records in the child table that don't have a corresponding parent record. Next, you should either delete/update those records in the child table or add the missing parent records to your parent table. Afterwards you'll be able to create the foreign key constraint. This is the best option by far since your referential integrety is guaranteed and your foreign key will be trusted.
You can find orphaned records by executing following query:

SELECT *
FROM [rpt].ReportLessonCompetency rlc
WHERE NOT EXISTS
(
    SELECT 1
    FROM [rpt].TraineeGrade tg
    WHERE tg.Id = rlc.Grade
)

WITH NOCHECK
The other option would be to create the foreign key with WITH NOCKECK. SQL Server will create the foreign key without verifying the existing data in the table. When you update/insert data in the child table, those records will still be checked.
Consequently, your foreign key will be marked as untrusted and the query optimizer won't consider your constraint to generate an execution plan.
Here you can find an example of how performance can be impacted.


Query design for tracking order status I have the following tables in MySQL

`orders` (
  `orderID` int(10) unsigned NOT NULL auto_increment,
  `agentID` int(10) unsigned NOT NULL,
  `order_date` datetime NOT NULL,
  `senderID` int(10) unsigned NOT NULL,
  `receiverID` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`orderID`)
)

 `order_details` (
  `order_details_id` int(10) unsigned NOT NULL auto_increment,
  `orderID` int(10) unsigned NOT NULL,
  `packageID` int(10) unsigned NOT NULL,
   PRIMARY KEY  (`order_details_id`),
   KEY `fk_orderID` (`orderID`)
)

`order_status` (
  `status_id` int(10) unsigned NOT NULL auto_increment,
  `order_details_id` int(10) unsigned NOT NULL,
  `descrID` int(10) unsigned NOT NULL,
  `date_time` datetime NOT NULL,
  PRIMARY KEY  (`status_id`),
  KEY `theDate` (`date_time`),
  KEY `stat` (`descrID`,`order_details_id`,`date_time`)
)

 `order_statuses` (
  `descrID` int(10) unsigned NOT NULL auto_increment,
  `descr` varchar(255) collate utf8_bin NOT NULL,
  `public` smallint(6) NOT NULL COMMENT '1 or 0',
  `leg` tinyint(4) NOT NULL COMMENT 'carrier code',
  PRIMARY KEY  (`statusID`)
)

The third and fourth tables are used to keep track of all the stages the orders go through. The flow is as follows: an order is placed at an agent of the company, the agent gives the order to a carrier, the carrier transfers the order to a hub owned by the company, another carrier picks it up and then hopefully delivers it.
2 questions;

First, any comments on the design?

Second, I need to run a query that will give me the all the orders based on the specific LATEST status per carrier. Right now the query that I am using is taking about 1.5 seconds to complete and I only have one row in the tables.

select odd.order_details_id
from order_details odd, orders od
where odd.orderID = od.orderID and (
    select oas.statusID
    from order_status oas, order_statuses oss
    where oas.descrID = oss.descrID and oss.leg=1 and odd.order_details_id =oas.order_details_id
    order by oas.date_time desc limit 1)=2

Is there a way to optimize this?
Thanks

A good start.

use InnoDB (or better yet, Postgres). You can use MyISAM slaves if you want full-text search, but otherwise, foreign keys are your friends.
watch your under_scores and camelCase. Use lowercase + underscores for max compatibility.
An order is sold by a vendor to a customer and is paid for by the bill_to party (who is often the customer). Can someone pay for the order that is not the customer? (ex. Grandma buys for Grandson, but uses Grandpas credit card). Don't forget the bill_to_address, that is often required.
order_details is usually called order_items. An order_item can be shipped to someone that is different from the customer or bill_to party. You could add ship_to and ship_to_address fields on order_details.
Are you selling only packages, or might you sell other goods, services, etc? Look into Table Inheritance.
Different parties play different roles in an order, in particular split sales commission. see def below
Can an order have the same status more than once? If not, remove order_status.status_id
I would rename order_statuses to order_status_types

Good luck!

order_role (table)
order_id FK
party_id FK
role_type FK order_role_type
commission_percent null


SSLServerHasCertificateAuthority is false but the certificate is signed by a trusted CA I am enabling TLS/SSL encryption on a remote MongoDB instance (running on Windows Server 2012 R2). On the instance's server, devserver.external, I have created an SSL certificate. The CN on the subject is devserver.external and the issuer is company-issuer. On my computer (the one with which I am connecting to the remote mongo instance, Windows 10) I have company-issuer in my Intermediate Certification Authorities (when viewed through the Windows certificate manager). This is an internal company operated CA that is trusted on all company machines, including the dev server and my machine.

When connecting to mongo, via either mongo on the command line or the Robo3t client, the connection succeeds over SSL but db.serverStatus().security.SSLServerHasCertificateAuthority is false. From the mongo docs:

A boolean that is true when the TLS/SSL certificate specified by net.ssl.PEMKeyPassword is associated with a certificate authority. **false when the TLS/SSL certificate is self-signed.**

So it would seem mongo is interpreting my CA-signed certificate as a self-signed certificate. This is obviously unwanted behaviour.

My mongod.cfg is:

systemLog:
    destination: file
    path: E:\MongoData\Log\mongod.log
storage:
    dbPath: e:\MongoData\DB
setParameter:
    enableLocalhostAuthBypass: false
security:
    authorization: enabled
net:
    ssl:
        mode: requireSSL
        PEMKeyFile: C:\Program Files\MongoDB\Server\3.4\mongodb2.pem

My mongodb2.pem is:

-----BEGIN PRIVATE KEY-----
<base64 encoded private key>
-----END PRIVATE KEY-----
-----BEGIN CERTIFICATE-----
<base64 encoded certificate>
-----END CERTIFICATE-----

I've not included a CA file because as stated in the configuration reference:

Changed in version 3.4: If --sslCAFile is not specified when connecting to an TLS/SSL-enabled server, the system-wide CA certificate store will be used.

My interpretation of this is: As I have not included a net.ssl.CAFile in my mongod.cfg, my mongo server will look to the Windows certificate store for a list of trusted CAs. In which it would find my company-issuer CA, which is the issuer for the certificate in mongodb2.pem.

As MongoDB BOL documentation here to connect to a mongod or mongos instance that requires encrypted communication, start mongo shell with --ssl and include the --sslCAFile to validate the server certificates.

mongo --ssl --host hostname.example.com --sslCAFile /etc/ssl/ca.pem

For example, given an TLS/SSL certificate located at /etc/ssl/mongodb.pem, configure mongod to use TLS/SSL encryption for all connections with the following command:

mongod --sslMode requireSSL --sslPEMKeyFile /etc/ssl/mongodb.pem <additional options>

Set Up mongod and mongos with Certificate Validation

To set up mongod or mongos for TLS/SSL encryption using an TLS/SSL certificate signed by a certificate authority, include the following run-time options during startup:

net.ssl.mode set to requireSSL. This setting restricts each server to
use only TLS/SSL encrypted connections. You can also specify either
the value allowSSL or preferSSL to set up the use of mixed TLS/SSL
modes on a port. See net.ssl.mode for details.
PEMKeyfile with the    name of the .pem file that contains the
signed TLS/SSL certificate    and key.
CAFile with the name of the .pem file that contains the root
certificate chain from the Certificate Authority.

  Note:

  Specify the  file and the  file with either the full path
  name or the relative path name.
  If the  is encrypted, specify
      the passphrase. See TLS/SSL
      Certificate Passphrase.

You may also specify these options in the  configuration file, as in the following examples:

If using the YAML configuration file format, include the following configuration in the file:

net:
   ssl:
      mode: requireSSL
      PEMKeyFile: /etc/ssl/mongodb.pem
      CAFile: /etc/ssl/ca.pem

Connect to MongoDB Instance that Requires Client Certificates

To connect to a mongod or mongos that requires CA-signed client certificates, start the mongo shell with --ssl, the --host option to specify the host to which to connect, the --sslPEMKeyFile option to specify the signed certificate-key file, and the --sslCAFile to validate the server certificates.

mongo --ssl --host hostname.example.com --sslPEMKeyFile /etc/ssl/client.pem --sslCAFile /etc/ssl/ca.pem


Are indexes need for parts of primary key? In Postgres, is it beneficial to query performance to create an index on field2 in the following table if I intend to sometimes use queries with only field2 in the WHERE clause?
CREATE TABLE my_table (
    field1   INT NOT NULL,
    field2   INT NOT NULL,

    CONSTRAINT pk_my_table PRIMARY KEY (field1, field2)
);

example query ("get all the records where field2 = my_value"):
SELECT * FROM my_table WHERE field2 = my_value;

In other words should I also include:
CREATE INDEX ix_my_table_field2 ON my_table(field2);

A compound index can also support queries that compare columns that are at the beginning of the indexed values (but aren't all). So an index on (field1, field2) (which is implicitly created for a primary key) would also support queries that compare only field1.
But it cannot support queries that only compare columns of its end (but aren't all). So (field1, field2) won't support queries that only compare field2.
So yes, you need to create an extra index only on (field2) to have a chance it supports queries only comparing field2.


POSTGRES 9.4: Function parameter as empty array I'm trying to create an empty array as a parameter so it's optional. I want to be able to call it without having the need to give a 2nd and 3rd parameter. Can somebody help?

CREATE OR REPLACE FUNCTION myschema.fnc_calc(
    p_par1 text,
    p_par2 text DEFAULT NULL::text,
    p_par3 par_type DEFAULT[] '{}'::par_type[]
)
RETURNS void AS

When I call it like this:

select * from myschema.fnc_calc('what_the_hell');

I get this message:

ERROR:  function myschema.fnc_calc(unknown, unknown, par_type) does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

BTW, this is has nothing to do with the public schema or calling it from a different schema. All the references are right. I checked this. I'm thinking that the declaration of the array parameter (p_par3) in the function should be something different (maybe not subscribe to an array with the '[]', but leaving it out doesn't work as it requires the parameter to be populated). Any ideas?

Your p_par3 is defined as par_type which is not the declaration for an array. You need to define it as par_type[] if that should be a parameter that is an array.

The following works for me:

CREATE OR REPLACE FUNCTION fnc_calc(
    p_par1 text,
    p_par2 text DEFAULT NULL::text,
    p_par3 par_type[] DEFAULT '{}'::par_type[]
)
RETURNS integer AS
$$
  select 42;
$$
language sql;

Also: default[] is wrong - your whole statement will not work.


How to show the related (suggested) keywords? We do a program for cataloging images by assigning keywords (tags) to them.

An image has the following keywords "John", "Jane", "Portrait".

Another image has the following keywords "John", "Jane", "Wedding".

When the user tries to catalog the next image and adds the "John" keyword, we want to appear in the 'Suggested' area the keyword "Jane".

How can we extract (using SQL) the suggested keywords based on the most possible combinations?

Note that Images can have more keywords assigned and in 'Suggested' area is best to have more suggestions.

The relevant table is called TagsTree and it has the following creation DDL:

CREATE TABLE TagsTree(ImageID INTEGER NOT NULL, TagID INTEGER NOT NULL, UNIQUE (ImageID, TagID))

...where ImageID is FK to the photo entity and TagID is FK reference to the keyword entity.

Also, any other hints regarding schema etc. are greatly appreciated.

PS: The DB engine is SQLite3, if it matters.

I am assuming that your tags are stored in the "Tag" table, but if not it should be an easy fix for the below SQL. I have not used SQLite3 before, but this should be generic SQL. I used the literal "John," but just replace with a variable for whatever the user enters. Also, if you wanted to limit the count to at least x number then put in a having clause.

select t2.keyword
from TagsTree tt
join Tag t on tt.TagID = t.TagID
join TagsTree tt2 on tt.ImageID = tt2.ImageID
join Tag t2 on tt2.TagID = t2.TagID and t.TagID <> t2.TagID
where t.keyword = 'John'
group by t2.keyword
order by count(*) desc, t2.keyword