RDBMS: Proper way to store data - comma separated variables or in different fields or tables? Fundamental question that I cannot seem to find an answer on. I have a database that stores line items from receipts along with the username and receipt number.

Right now the lineItem column is just a long string of data separated by commas (original file appears to have been just an Excel file). This information is parsed in a PHP script for viewing on the front end.

The table looks like this:

|----------|----------|----------|
|lineItem  |receiptID |customerID|
|----------|----------|----------|
|CD, DVD,  |001       |User01    |
|----------|----------|----------|
|CD, CD,   |002       |User02    |
|DVD, usb, |          |          |
|----------|----------|----------|

Ultimately, is this bad practice? Should the lineItem values be linked to related values in another table instead maybe?

Why storing data as a string a problem :

Storing multiple datums as (in this case comma separated) strings is bad practice because:

- First reason:

It breaches Codd's second rule (called the "Guaranteed Access Rule") which states that Each and every datum (atomic value) in a relational data base is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.

So, if you wish to refer to user02's USB lineItem, you have to do further processing beyond simply knowing the table name, the PRIMARY KEY and the column name.

From here: Among the conventional database models, the Relational Model of data has a simple, sound mathematical foundation based on the notions of set theory. And from the wiki on the Relational Model (RM) we have:

  The relational model was the first database model to be described in
  formal mathematical terms. Hierarchical and network databases existed
  before relational databases, but their specifications were relatively
  informal. After the relational model was defined, there were many
  attempts to compare and contrast the different models, and this led to
  the emergence of more rigorous descriptions of the earlier models;
  though the procedural nature of the data manipulation interfaces for
  hierarchical and network databases limited the scope for
  formalization.

So, basically, the only data model with a sound mathematical foundation is the relational one. Most relational databases use the SQL data definition and query language; these systems implement what can be regarded as an engineering approximation to the relational model. [ibid].

Codd derived his rules as a guide to a practical implementation of his relational calculus - given that it is the only model with a sound mathematical foundation, it seems that it would be a bad idea to breach any of them.

Caveat: now, if for example, you will NEVER EVER want to break out the line items into their individual components, then storing it as one "unit" would be acceptable, but I can see many instances of where you would want to split it up into its component parts (see the fifth reason below).

An example of where you might want to store data in .csv form might be storing somebody's name and title for an academic journal - it might be stored thus:

Citizen, Seán B., Prof.

and that's the only way you're ever going to print/process/transmit/store this information, then it is a datum, not comma separated variables - datum or data is very much a contextual concept.

- Second reason:

As mentioned in comments, your lineItem table isn't even in first normal form (see the chart here - Atomic columns (cells have single value). This is obviously related to the point above. Database normalisation is

  the process of structuring a relational database in accordance with a
  series of so-called normal forms in order to reduce data redundancy
  and improve data integrity.

These "forms" flowed from the RM/Relational Calculus and Codd's rules as a way of ensuring that data remains consistent, which is obviously of paramount importance in any database system - in simple terms, it's how we ensure that the definitive archetype of a given datum is stored in one place and one place only.

- Third reason:

You have no way of controlling what data is entered into that field - i.e. you have no way of controlling Declarative Referential Integrity (DRI). This means, for example, that there's nothing to stop you referring to products which don't exist (say, DVDx).

DRI is one of the most important benefits of using the RM - it means that internal data consistency can be maintained, the benefits of which you will greatly appreciate if you've ever you've had the misfortune to have worked with a system where this has broken down.

In point two, we said that Normal Forms were to ensure that the definitive archetype of a given datum is stored in one place and one place only - DRI ensures that all other references to that datum point to that one place and nowhere else.

- Fourth reason:

SQL is not designed for parsing strings - it can be done, it's just messy, time-consuming and error-prone. Many proprietary extensions have been developed by the various RDBMS providers to try and overcome this lacuna, but it's still far easier to deal with properly normalised tables (see the SQL below).

- Fifth reason:

Apart from the "theoretical" (more or less) reasons for not doing this, is the massive PRACTICAL problem of not being able to assign individual quantities and prices to items under your schema - suppose I'm doing my Christmas shopping and I wanted the new "U2 CD" for 3 of my friends who are U2 fanatics? No way of telling the system that there are 3 U2 CD's other than having a field value like this:

'"U2 CD", "U2 CD", "U2 CD" "UB40 CD", "U2 DVD", "Kingston USB 32GB"' -- note repetition of "U2 CD".

Suppose you want to know the number of USB's sold? The number of USB's per client? The number per client district/area/country - depending on the scale of your operation (see SQL below)? Suppose I want to know how much was spent on USB drives last week - absolutely no way of getting any of that information! The list goes on...

How to deal with the problem:

So, having dealt with the first part of your question, we can now progress to the second part - Should the lineItem values be linked to relational values in another table instead maybe?.

- First solution (extra fields):

Here is a further example of the problems associated with storing strings. In this case, adding fields to a given record is the solution - i.e. splitting the string into its component parts and making each one a field! Very helpful for enforcing DRI and controlling data correctness if one has reference tables for (in this case) ZIP codes, street names &c...

- Second solution (extra records - 1-to-many relationships):

In this particular case of your question, what we have here is a classic 1-many relationship - also known as parent-child where receipt is the parent and line_item is the child.

Your table structure is this:

CREATE TABLE line_item
(
  lineItem VARCHAR(2000),  -- could have a many items - need a very long string - parsing a nightmare!
  receiptID INTEGER,       -- "001" could be a string - MySQL has a zero-fill function
  customeID VARCHAR(20)     -- redundant - don't need to store it for every line_item - it corresponds to a receipt (1 customer/receipt), not a line_item!
);

What you should have is something like this (see the fiddle here - all data and tables are also given at the bottom of this answer):

CREATE TABLE line_item
(
  receipt_id INTEGER NOT NULL,
  item_id INTEGER NOT NULL,
  item_qty INTEGER NOT NULL,
  CONSTRAINT line_item_pk PRIMARY KEY (receipt_id, item_id),
  CONSTRAINT li_item_fk FOREIGN KEY (item_id) REFERENCES item (item_id),
  CONSTRAINT li_receipt_fk FOREIGN KEY (receipt_id) REFERENCES receipt (receipt_id)
);

and your data will (rather cryptically) look like this:

INSERT INTO line_item VALUES
(1, 1, 1), (1, 4, 1), (2, 2, 1), (2, 3, 1), (2, 5, 1);

The receipt_id fields and the item_id fields point to the PRIMARY KEYs of their respective tables - and there is no redundant, extraneous information in the table - no customer_id stored multiple times for example! This way of modelling allows one to write queries of the form:

SELECT
  c.customer_id, c.customer_name, c.customer_address_1,
  i.item_desc, i.item_price,
  r.receipt_id,
  li.item_id, li.item_qty
FROM
  customer c
JOIN receipt r
  ON c.customer_id = r.customer_id
JOIN line_item li
  ON r.receipt_id = li.receipt_id
JOIN item i
  ON li.item_id = i.item_id;

Result:

customer_id  customer_name  customer_address_1  item_desc          item_price   receipt_id  item_id     item_qty
1            Bill Gates     Redmond             Michael Jackson CD      1.50              1     1          1
1            Bill Gates     Redmond             U2 DVD                   5.00             1     4          1
2            Larry Ellison  Redwood Shores      U2 CD                    2.00             2     2          1
2            Larry Ellison  Redwood Shores      UB40 CD                 4.00              2     3          1
2            Larry Ellison  Redwood Shores      Kingston USB 32GB       25.00             2     5          1

See the fiddle (or below) for the all the DDL and DML! I challenge you to do this trivially with a .csv string containing your line_item products - especially in MySQL! It probably would be feasible in PostgreSQL using something like array_to_table after feeding the string into an array but I leave that as an exercise for you!

So, for a 1-many relationship, you add items to your line_item table - one item for each element in your .csv string - 1 receipt parent record can have 1 to many (possibly a very large number) of line_item children.

Now, the item table is also a parent of line_item and in its case, there can be 0 to many children if, for example, if an item hasn't sold at all there will be no references to it in the line_item table.

- Third solution (extra tables - many-to-many relationships):

There is an appropriate circumstance when "values SHOULD be linked to relational values in another table" (as you hint at in your question) and this is when there is an m-to-n relationship - otherwise know as a many-to-many relationship.

Consider the old favourite Databases-101 example of students and courses and the many courses taken by many students! See the fiddle here - I haven't populated the tables this time. I've used PostgreSQL for the fiddle (my favourite server) but a bit of tweaking will get it to work on any reasonable RDBMS.

Create tables course and student:

CREATE TABLE course
(
  course_id SERIAL,  -- INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY in MySQL dialect
  course_year SMALLINT NOT NULL,
  course_name VARCHAR (100) NOT NULL,
  CONSTRAINT course_pk PRIMARY KEY (course_id)
);

CREATE TABLE student
(
  student_id SERIAL,
  student_name VARCHAR (50),
  CONSTRAINT student_pk PRIMARY KEY (student_id)
);

This is where a JOINing table (aka linking table(more formally known as an [Associative Entity`]13 - as an aside, there are 17 different names for this type of table on that page) comes in.

a given student can take many courses
a given course can have many students

So, you deal with this by creating the Associative Entity - your JOINing table:

CREATE TABLE registration
(
  reg_course_id INTEGER NOT NULL,
  reg_student_id INTEGER NOT NULL,

  CONSTRAINT reg_course_fk FOREIGN KEY (reg_course_id) REFERENCES course (course_id),
  CONSTRAINT reg_student_fk FOREIGN KEY (reg_student_id) REFERENCES student (student_id)
);

I then add a PRIMARY KEY - I kept it out of the table definition to illustrate the point, but it could (and normally would) be part of the table creation DDL.

ALTER TABLE registration
ADD CONSTRAINT registration_pk
PRIMARY KEY (reg_course_id, reg_student_id);

So now,

a given student can only be enroll in a given course once and
a given course can only have the same student enrolled once

There are many other situations where this construct is useful - basically, it's the only way to meaningfully model many real-life situations.

An example from my own career:

Think of a flight table containing a flight_id field, a list of departure and arrival airports and the relevant times and then also a crew table with crew members and a crew_id field (and other details obviously).

Having the flight_id and crew_id fields in a JOINing table proved to be very useful for the system - it really helped with scheduling and rostering which was a mess with the other system - constant conflicts in both. It takes time and experience to recognise when which schema design is suitable for which scenario, but 1-many (extra records in existing table) and many-many (extra JOINing table) is a good rule of thumb!

p.s. welcome to the forum!

_____________ Full DDL and DML _______________

Customer table:

CREATE TABLE customer  -- storing the customer_id on every line item is redundant - check out 3rd normal form
(
  customer_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_name VARCHAR (100) NOT NULL,
  customer_address_1 VARCHAR (100) NOT NULL -- can have address_1..n
  --
  -- other fields of particular interest to you
  --
);

Customer data:

INSERT INTO customer (customer_name, customer_address_1) VALUES
('Bill Gates', 'Redmond'), ('Larry Ellison', 'Redwood Shores');

item table:

CREATE TABLE item
(
  item_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  item_code VARCHAR (25) NOT NULL UNIQUE,
  item_desc VARCHAR (200) NOT NULL,
  item_price DECIMAL(10, 2),
  item_supplier INTEGER NOT NULL -- refers to supplier table - not shown!
  --
  -- other fields of interest to you
  --
);

item data:

INSERT INTO item (item_code, item_desc, item_price, item_supplier) VALUES
('code_1', 'Michael Jackson CD', 1.5, 56), ('code_2', 'U2 CD', 2, 78), ('code_3', 'UB40 CD', 4, 67),
('code_4', 'U2 DVD', 5, 78), ('code_5', 'Kingston USB 32GB', 25, 23);

receipt table:

CREATE TABLE receipt  -- often called "orders" but receipt is OK
(
  receipt_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id INTEGER NOT NULL,  -- refer to customer table - see below

receipt_total DECIMAL(10, 2),  -- kept updated by a trigger (not shown)
                                 -- can be calculated on the fly or
                                 -- possibly a generated field
  receipt_dt TIMESTAMP NOT NULL,  -- date and time of sale
  receipt_asst INTEGER,  -- refers to the sales assistant table - not shown
  CONSTRAINT rec_cust_fk FOREIGN KEY (customer_id) REFERENCES customer (customer_id)
);

receipt data:

INSERT INTO receipt (customer_id, receipt_total, receipt_dt, receipt_asst)
VALUES
(1, 6.5, '2020-06-03 15:23:45.123', 34),
(2, 31 , '2020-06-05 10:54:23.123', 17);

line_item table:

CREATE TABLE line_item
(
  receipt_id INTEGER NOT NULL,
  item_id INTEGER NOT NULL,
  item_qty INTEGER NOT NULL,
  CONSTRAINT line_item_pk PRIMARY KEY (receipt_id, item_id),
  CONSTRAINT li_item_fk FOREIGN KEY (item_id) REFERENCES item (item_id),
  CONSTRAINT li_receipt_fk FOREIGN KEY (receipt_id) REFERENCES receipt (receipt_id)
);

line_item data:

INSERT INTO line_item VALUES
(1, 1, 1), (1, 4, 1), (2, 2, 1), (2, 3, 1), (2, 5, 1);

Query:

SELECT
  c.customer_id, c.customer_name, c.customer_address_1,
  i.item_desc, i.item_price,
  r.receipt_id,
  li.item_id, li.item_qty
FROM
  customer c
JOIN receipt r
  ON c.customer_id = r.customer_id
JOIN line_item li
  ON r.receipt_id = li.receipt_id
JOIN item i
  ON li.item_id = i.item_id;

Result:

customer_id  customer_name  customer_address_1  item_desc          item_price   receipt_id  item_id     item_qty
1            Bill Gates     Redmond             Michael Jackson CD      1.50              1     1          1
1            Bill Gates     Redmond             U2 DVD                   5.00             1     4          1
2            Larry Ellison  Redwood Shores      U2 CD                    2.00             2     2          1
2            Larry Ellison  Redwood Shores      UB40 CD                 4.00              2     3          1
2            Larry Ellison  Redwood Shores      Kingston USB 32GB       25.00             2     5          1


Postgres query cache analyzing table basis? The PostgreSQL application I'm currently on is solely based on functions. Every request from the front end maps to a PostgreSQL function call. Many of these functions call other functions. Like: web application server calls function A, function A calls function B, function C queries data from table D and E and so on.
Now, I'm running into big performance issues. While anyway trying to optimize the functions themselves, I'm wondering if query caching might help me.

The problem is: The cache mechanism should analyze which tables are actually concerned by a certain function call and thus refresh or invalidate the cache entry if one of these tables is updated.

Let me give you an example. Consider functions like this:

CREATE FUNCTION public.pets_get_all(user_id integer)
$BODY$
SELECT dogs_get_all($1) AS dogs
$BODY$ LANGUAGE sql IMMUTABLE;

The dependent functions being

CREATE FUNCTION public.dogs_get_all(user_id integer)
$BODY$
SELECT id, name FROM dog WHERE user_may_read_dog($1,dog_id)
$BODY$ LANGUAGE sql IMMUTABLE;

CREATE FUNCTION public.user_may_read_dog(user_id integer, dog_id integer)
$BODY$
SELECT owner_id = $1 FROM dogs WHERE dog_id = $2
$BODY$ LANGUAGE sql IMMUTABLE;

Let's say, for the first time pets_get_all is executed with 1337 as the parameter, the query works like normal and it returns.

+----------+-----------+
| ID       | Name      |
+----------+-----------+
| 1        | Bella     |
| 2        | Max       |
+----------+-----------+

So, for the next time pets_get_all(1337) is executed, the query will be fetched from the cache, the inner functions won't be executed and thus, it will be pretty much faster.
However, when the dog with the id 2 is updated, the cache entry will be updated too (taking some time of course). So, the cache will be always up to date. Of course, changes in the dog with the id 3 will not affect the cache entry for pets_get_all(1337)`, since the user the id 1337 may not read dog 3.

Does such a thing exist?

I had a rough look into "PostgreSQL Query Cache" ( https://groups.google.com/forum/#!forum/pqc-dev ) However, it seems outdated and the expiration of cache entries is only time-based (which is not what I want).

Clearing up confusion about IMMUTABLE

  So, for the next time pets_get_all(1337) is executed, the query will be fetched from the cache, the inner functions won't be executed and thus.

That's not true. In fact, even in the same query the result isn't typically "cached": which as far as I know isn't a correct description of what's going on. It's more of a planner reference trick. There is certainly no transactional memoization going on here. These are two different concepts and there is nothing about a function call especially one which may be inlined that implies what you're assuming.

IMMUTABLE and even STABLE does imply that the query-plan itself is cached. This means that future calls within the same transaction will not be re-planned.

CREATE FUNCTION stable_foo()
RETURNS void
AS $$
  SELECT pg_sleep(1);
$$
LANGUAGE sql
STABLE;

CREATE FUNCTION immutable_foo()
RETURNS void
AS $$
  SELECT pg_sleep(1);
$$
LANGUAGE sql
IMMUTABLE;

Just looking at the timing:

\timing

-- these will take 2 seconds.
SELECT * FROM immutable_foo() AS a, immutable_foo() AS b;
SELECT immutable_foo(), immutable_foo() FROM ( VALUES (1) ) AS t(x);
SELECT immutable_foo()::text || immutable_foo()::text;

-- these will take 1 second.
SELECT immutable_foo() FROM ( VALUES (1),(2) ) AS t(x);

-- but declared as `STABLE` like foo(), it takes two seconds.
SELECT stable_foo() FROM ( VALUES (1),(2) ) AS t(x);

So only under specific circumstances in the same query will a function call get optimized out.. However, you'll never find a situation where this happens across statement boundaries..

As far as I know, the contexts for which an immutable function is optimized away are undocumented. Historically, this is even something experienced folks have struggled with and there is a general try it and see mentality as to whether or not the optimization takes effect.

BEGIN;
  SELECT bar();
  SELECT bar();
END;

I prefer to think of IMMUTABILITY in terms of what it permits, things such as (a) functional indexes, (b) functional index predicates (c) check constraints and the like. They all require a function to return the same arguments given the same inputs to make sense -- and they'll check that they're given an immutable function. But, it doesn't follow that there is memoization that operates in the transaction, session, or server.

PgPool 2

If you do need an actually query-cache you should consider checking out PgPool 2 which is the industry-method of doing this.

It's essentially middleware (or a forward-cache depending on your age).

As it applies to you

Writing a system to exploit undocumented query optimizations is a bad idea. You're better of not doing this at all, stepping back and just using SQL. Sure, the results of a complex query are not cached. But the buffered heap fetches and plans are. If it's in the same transaction it's working off the same data.

I would find weak links in your application and ask questions about how to make them faster. What you're doing seems something akin to pre-optimization.


How to change the SSAS Multidimensional Cube partition location on disk? I have a SSAS Multidimensional Cube. There is a measure group with multiple partitions (based on year). I need to move old partitions to a different physical (slower) disk. When I create the partition I'm able to choose the physical location of it. But when partition has already been created I cannot find the place where I can change the path to physical disk. Do you know is it possible to change this ?

I've found the solution. To change the location of the particular partition in SSAS Multidimensional Cube (MeasureGroup) you have to Open SSDT->Open the Cube->Go to Partitions tab->Click on particular partition of the Measure Group->in the Properties window scroll down to the Configurable\StorageLocation-> click "..." button->in the Partition Type Selection window select "Specified folder"->click "..." and choose the new path. After this action partition needs to be reprocessed.


Insert into a table a foreign key I already search in here but i can't found an awnser , i have this code :

CREATE TABLE Aquario(
    ANome varchar(64) PRIMARY KEY,
    Localização varchar(8),
    Capacidade integer(15));

CREATE TABLE Habitat(
    HNome varchar(100) PRIMARY KEY,
    Iluminamento integer(10),
    Salinidade integer(2),
    pH integer(2),
    Dureza integer(3),
    Oxigenação integer(3),
    Temperatura integer(2));

CREATE TABLE contem(
    HNome varchar(64),
    ANome varchar(64),
    Percentagem_de_adequação integer(2),
    PRIMARY KEY (ANome),
    FOREIGN KEY(ANome) REFERENCES Aquario(ANome),
    FOREIGN KEY(HNome) REFERENCES Habitat(HNome));

INSERT INTO contem (Percentagem_de_adequação) VALUES (20);

INSERT INTO Aquario (ANome, Localização, Capacidade) VALUES ('Os Peixes azuis','Este',20);

INSERT INTO  Habitat (HNome, Iluminamento, Salinidade,pH,Dureza,Oxigenação,Temperatura) VALUES ('Os peixes' ,20  ,1 ,5 ,213 ,52,2);

This code don't let  me do the table " contem " why ?

Firstly, your datatypes and precision should always be identical on your FK references.

In Habitat you have HNome as a VARCHAR(100). In Cotem, you have HNome as a VARCHAR(64).  These need to be the same.

It would help if you would post the error message you are getting, but I suspect the error is you are trying to insert a NULL value into ANome in Cotem.


Property error when linking SQL Servers I'm running into an issue linking SQL servers. The servers I have are:

Server A - SQL Server 2008 R2

Server B - SQL Server 2014

I can set B as a linked server on A with no problem, but setting up A as a linked server on B gets me the error:

  Cannot write property UseRemoteCollation.This property is not
  available on SQL Server 7.0. (SqlManagerUI)

Is this just an issue with the two servers being different versions? Is there anything I can do to get around this issue?

There are two differing "best practices" for using SQL Server Management Studio when managing SQL Server.

Use the same version of SQL Server Management Studio as that of the server you are managing.  In this case that means you should use SSMS that comes with SQL Server 2014; one source would be ENU\x64\SQLManagementStudio_x64_ENU.exe.
Use the latest-and-greatest version of SQL Server Management Studio.  As of now (2018-07-05), that would be SSMS 17.8.1, which is available free from Microsoft here.


Moving mysql datadir cause info: checking for tables which need an upgrade So after moving the data directory to another partition, I get this error:

[info] checking for tables which need an upgrade, are corrupt or were not closed cleanly.

This is on Debian 7.5.

The steps I took to do this:

service mysql stop
nano /etc/mysql/my.cnf
changed datadir=/data/mysql
cp -pr /var/lib/mysql /data/
service mysql start

When I did a mysqlcheck, all tables in mysql db say OK. What else am I missing to get rid of this error?

This is an [info] message and displays due to the steps taken by the Debian mysql startup script. It is not an error.


GETUTCDATE()-2 vs DATEADD(d,-2,GETUTCDATE()) I was wondering what the difference between the following two methods is:

 GETUTCDATE()-2

and

  DATEADD(d,-2,GETUTCDATE())

I guess using DATEADD is the correct way, but was wondering why?

There's no real difference there, but when you start using DATETIME2 values, or functions that return DATETIME2 values, you'll get errors.

SELECT SYSDATETIME() - 1 AS [Incompatible]

  Msg 206, Level 16, State 2, Line 17 Operand type clash: datetime2 is
  incompatible with int

For these, you have to use date math functions.

SELECT DATEADD(DAY, -1, SYSDATETIME()) AS [Compatible]

Aaron Bertrand speaks about this issue briefly in his Bad Habits to Kick series.


Does lightweight pooling disable built-in CLR facilities? Running SQL Server in fibre mode (lightweight pooling) disables the use of SQL CLR:

  Common language runtime (CLR) execution is not supported under lightweight pooling. Disable one of two options: "clr enabled" or "lightweight pooling". Features that rely upon CLR and that do not work properly in fiber mode include the hierarchy data type, replication, and Policy-Based Management.

On the other hand, disabling SQL CLR alone (without enabling lightweight pooling) does not disable the built-in CLR types like geometry, and geography (though hierarchyid is mentioned above), as shown in How can "HierarchyID" type work when "CLR" is disabled?

Now some new language features rely on the CLR, for example the FORMAT function:

  FORMAT relies on the presence of the .NET Framework Common Language Runtime (CLR).

Does running SQL Server in fibre mode disable the FORMAT function and/or using the CLR types?

Yes.
When running in fibre mode, everything that needs the CLR is unavailable.
For example, both:
SELECT FORMAT(GETDATE(), N'd', N'en-us');

and:
DECLARE @g geometry;
SELECT @g;

...return the (slightly misleading) error message:

Msg 5846, Level 16, State 2, Line 13
Common language runtime (CLR) execution is not supported under lightweight pooling.
Disable one of two options: "clr enabled" or "lightweight pooling".

It is the lightweight pooling that is the problem, no setting of the clr enabled option will allow CLR execution of any kind under lightweight pooling.


Attempt to add DQS Cleansing Connection Manager to SSIS results in error I reopened an SSIS project in SSDT-BI (with VS2013 Shell) which had been dormant for ~3 months to see the dreaded red "x" had appeared on my DQS Data Flow Component:

I attempted to replace the existing connection manager with a new one, but both the existing and the new both resulted in the following error message:

  TITLE: DQS Cleansing Connection Manager

      Failed to connect to DQS server the connection manager 'local\mysampledb' as a result of the following value:
   A connection could not be made. Ensure that the server is running.
   System.Data.SqlClient.SqlException (0x80131904): An error occurred in the Microsoft .NET Framework while trying to load assembly id 65629. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
  System.IO.FileLoadException: Could not load file or assembly 'microsoft.ssdqs, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050) See Microsoft Knowledge Base article 949080 for more information.

The package has been checked in to source control for the intervening period and was working correctly last time it was checked out.

Open the SQL Server Data Quality Services client and attempt to
connect to the server with it.  You can sometimes get a better
explanation of a problem from the DQS Client than the SSIS
component.
If unsuccessful, run 'DqsInstaller.exe -upgrade'


SQL table with same name as catalog view When naming tables in SQL I try to stay away from SQL reserved keywords, but today a colleague questioned the use of Events as a table name. They said, anything that turns green in SSMS shouldn't be used as a table name.

Are there any conflicts or gotchas that I should be concerned with using Events as a table name in MS SQL Server?

I also generally do my best to avoid using reserved words for user created objects, but sometimes it's really hard to avoid. Some very common terms are reserved words (e.g. User and Login, etc), and so sometimes being forced to avoid them at all cost means you need to choose a less meaningful, less intuitively obvious name for your project, and that really does not benefit your project or company.

So the only real "gotcha" is what you need to do in order to prevent the conflicts: always delimit object names that are reserved words (i.e. always specify the names surrounded by [ and ]).

Now, not all words that change color in SSMS will be a technical conflict, but that doesn't mean that you shouldn't delimit them. Those words could become a technical conflict in a future version, and that could complicate (i.e. increase the time, hence cost of) upgrading.

A simple example:

USE [tempdb];
CREATE TABLE dbo.Select ([Col1] INT); -- "Select" = blue
/*
Msg 156, Level 15, State 1, Line XXXXX
Incorrect syntax near the keyword 'Select'.
Msg 102, Level 15, State 1, Line XXXXX
Incorrect syntax near 'INT'.
*/

CREATE TABLE dbo.[Select] ([Col1] INT);
-- Success

CREATE TABLE dbo.Event ([Col1] INT); -- "Event" = blue
-- Success

CREATE TABLE dbo.Events ([Col1] INT); -- "Events" = green
-- Success

Again, even though "Events" isn't required to be delimited, if you do use it, I would highly recommend that you always specify it as [Events] to guarantee no conflicts.


How to size SQL Server VM so that it is comparable to bare metal primary SQL server? Are there any guidelines for sizing a new Windows SQL Server VM in Azure so that it matches performance of the on-prem / bare-metal primary SQL server?

This is the online Microsoft guide about it:
https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices
interesting to point out:

If you are creating a new SQL Server on Azure VM and are not migrating
a current source system, create your new SQL Server VM based on your
vendor requirements. The vendor requirements for a SQL Server VM are
the same as what you would deploy on-premises.

Not to mouch after all. Said that I believe you have to do some test, but how?
You have to capture the on premise workload and reply it to an the azure vm and check the results.
A goo tool for doing it is this:
https://github.com/spaghettidba/WorkloadTools/wiki


Are there practical scenarios where inserts + deletes are faster than updates? (SQL Server) I'm just wondering if there are common scenarios where an insert/ delete combination is faster than an update else insert function.

Here is my specific example.

I have to update a database with pages that contain 1000 records at a time. (I cannot merge the pages).

About 5% of these records, or 50 rows, are duplicates that need to be 'updated' rather than inserted as brand new.

I figure instead of an "update based on ID, else insert new row" typical function it might be faster to "insert everything" and delete the duplicates, in one shot, at the very end.

Two reasons:

parallelism. If I want multiple processes working on this task at the same time, well ... I can run into row locks if I have a big commit size and transactions searching for, and updating, IDs at the same time. With the "insert everything" and delete the 'older' records later, I can have unlimited processes writing data at the same time.
I feel it's easy to optimize one big "delete lookup" at the very end. It looks like the following:

with CTE as (
   select primary_id,update_date,
          rn = row_number()over(partition by primary_id order by update_date desc)
   from MyTable
)
delete from CTE where rn > 1

I mean the performance gains are there -- I'm just wondering if this goes against best practices. Can someone see why inserts + delete duplicates seems to work faster than 'update, if not found, insert'?

I can see one danger is that there is a period of time while the Data Load is running that the Table is not accurate (before the deletions). But isn't this sort of true in the middle of any update process?

This would also be a staging table for a data warehouse, not live in-use data. I'm just wondering why I haven't seen this method more often.

There are many reasons that an insert and delete can be faster in practice than an single update that achieves the same end result. I am not even going to attempt to list all the considerations, but for example:

Updates that affect an index key might appear to do an in-place update from the execution plan, but this is not the case at the lowest level. An update that affects an index key results in a separate delete and insert at the Storage Engine level in SQL Server 2005 onward.
Updates that change the value a key in a unique index generally mean that SQL Server has to introduce a Split/Sort/Collapse combination in the execution plan to avoid transient unique key violations. The Split explicitly turns each update into separate delete and insert operations. The cost of the extra operators (particularly the Sort) can often result in reduced performance. Plans that insert or delete to a unique index do not require this same protection, though Split operators may still be seen for other reasons.
There are internal optimizations available for inserts that simply do not apply to updates (or deletes). Some of these are associated with minimal logging, and some require a hint or trace flag (like 610). Other optimizations do not require trace flags or hints, and are not exposed in execution plans.

  I mean the performance gains are there

This is the key point. I can tell you that my experience has been that updates and deletes are generally less optimized in SQL Server than inserts, but the key is to test in your environment. An insert and delete will not always be faster than a single update, in much the same way that separate operations will not always be faster than a single MERGE (though that is probably even more common).

There are even cases where an insert followed by an update (!) can be faster than a single update. Though not directly related to your example, you may find the details of that in my article, "Optimizing Update Queries" interesting.

Also related:

Optimizing T-SQL queries that change data
Minimal Logging with INSERT…SELECT and Fast Load Context
Minimal Logging with INSERT…SELECT into Empty Clustered Tables
Minimal Logging with INSERT…SELECT into Heap Tables


Backup an Oracle database to NAS I have an Oracle database (11.2.0.4) on Oracle Linux 6.7.

I want to backup this database with rman to a folder on a NAS (network attach storage).

How can I do this?

Is quite simple. Make sure that oracle can write on a folder on the NAS and run the following RMAN job:

run
{
  CONFIGURE DEFAULT DEVICE TYPE TO disk;
  BACKUP FULL DATABASE INCLUDE CURRENT CONTROLFILE FORMAT '<NAS-directory>/<some-name>_%d_F_%T_%s';
}


Locked out of my own database through invariant() failure I am trying to set up mongodb on my own server and connect to it remotely through my laptop.

I had successfully installed the database and created my own user with admin privileges. However, when I shutdown the server and restarted it, I try to run it through

mongod

and get

***aborting after invariant() failure

This error seems to be because I'm not passing in the correct credentials, but I cannot find any documentation on how to log in and start the instance using the new user I created. I've only found very old solutions through searching on my own that no longer seem to apply.

How do I connect to it, and then demand authorization from remote connections?

Deleting .ns file in data/db worked for me.

Invariant failure usually occurs if mongo is not been shut down properly, and improper shutdown will corrupt .ns (namespace that mongo creates for each database) file. So deleting .ns file will solve the issue.


Securing a MySQL database with more than just username and password We have a web app that runs in Azure and use a MySQL database, also in Azure, for its data storage. We store PII data from the various companies that use our system in this database.

The web app is written in PHP and connects to the database using a username and password. We are needing to comply with a big customers data protection requirements, and they require any access to PII data to be 2-factor authenticated.

I have no idea where to start with this or if it is even possible. Does this mean for every connection the web app makes (tens of thousands a minute), it would need to some how generate a 2-factor code as well each time??? Assuming adding 2-factor is even possible for a MySQL server??(I have looked around and can't find anything).

So my questions are:

Is 2-factor possible with MySQL
If not, how else can it be
secured so it requires more than just a username and password to
connect (that me using workbench, and our web app can do).

Thanks

Here are some basic security recommendations.

Set complex password.
Use validate_password plugin.
Disable remote root login.
Disallow incoming traffic apart from your app and report server.
Always create users with the proper host(don't use '%').
Rotate password at every month.
Restrict MySQL data, log and backup directories.
Try to implement SSL communication between the server and the clients.
Restrict SSH access to the server where MySQL installed.
Prevent my.cnf from other users.

This blog may help you to implement more security:

https://lalitvc.wordpress.com/2017/04/02/basics-of-mysql-administration-and-best-practices/


Need a better way to insert non-existent values in referenced table during insert/update in referencing one I'm looking for the best way to insert data into MariaDB with JDBC. Please take a look at the following schema

CREATE TABLE color (
  id   SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE ball (
  id       INT PRIMARY KEY AUTO_INCREMENT,
  name     VARCHAR(255) NOT NULL,
  color    SMALLINT UNSIGNED,
  KEY color (color),
  CONSTRAINT color_fk FOREIGN KEY (color) REFERENCES color(id)
);

and test data

INSERT INTO color (name) VALUES ('green'), ('yellow'), ('red');
INSERT INTO ball (name,color) VALUES ('Big Yellow Ball',(SELECT id FROM color WHERE name = 'yellow')); -- OK
INSERT INTO ball (name,color) VALUES ('Small Purple Ball',(SELECT id FROM color WHERE name = 'purple')); -- DOESN'T EXIST

and as result NULL color value is inserted for Purple Ball

MariaDB [zimbra]> select * from ball;
+----+-------------------+-------+
| id | name              | color |
+----+-------------------+-------+
|  1 | Big Yellow Ball   |     7 |
|  2 | Small Purple Ball |  NULL |
+----+-------------------+-------+

Thus I can see the following solution

try to read required color
then if it doesn't exist insert a new record for it
then extract generated key
prepare and execute next query/statement with it

which doesn't look very elegant as it requires from 2 to 3 statements preparation (in Java), with further execution and results extraction for each of them. This becomes even more ugly taking into account that there are more than one such referenced tables in my real case. Another reason to optimize this is performance: to send less SQL requests and to execute less PreparedStatements).

Ideally, I would like to be able to construct such SQL insert statement which would bother about non-existent referencing values.

Otherwise, if it is impossible, what other solution I should look at which would allow me to simplify my Java code?

Instead of trying to insert directly into the ball table you could insert your data into a temporary newdata table ...

CREATE TEMPORARY TABLE newdata (
    name VARCHAR(255),
    colorname VARCHAR(255)
    );

INSERT INTO newdata (name, colorname) VALUES
    ('Big Yellow Ball', 'yellow'),
    ('Small Purple Ball', 'purple');

... then perform an INSERT INTO color to create entries for any color names that don't already exist ...

INSERT INTO color (name)
SELECT colorname FROM newdata
WHERE colorname NOT IN (SELECT name FROM color);

... and then INSERT INTO ball with

INSERT INTO ball (name, color)
SELECT newdata.name, color.id
FROM newdata INNER JOIN color ON newdata.colorname = color.name;


SHOW PROCESSLIST: show processes from all sessions SHOW PROCESSLIST shows the list of processes for the current user and session only.

Is there a way for the current user to list all their processes for all currently opened sessions?

Obtain this info from information_schema.PROCESSLIST. If your user have PROCESS privilege, and you need to view only your processes - use WHERE user = USER() filter.

Link


Need to intentionally create blocking processes for testing My team and I have an issue where we need to be able to identify processes that are blocking other processes and kill them. There are a ton of scripts that are freely available to perform these actions. We have tried various ones and scrutinized the code. (If you are someone that has posted one of these queries, thanks!)

Before I go further, let me tell you that this is against a vendor application that we cannot modify. The vendor is also not willing to spend the time to figure out why processes are getting blocked. The only choice we have at this point is to kill the long running processes (as suggested by the vendor support). Before killing these processes, we do review the query running, but 99.9% of the time, it shows up as FETCH API_CURSOR00000000000A7E1F, which tells us nothing.

This has been a manual process up to this point. Now, we want to automate the killing of these long running blocking processes instead of someone manually killing them.

We want to test this script before putting it into production. We would like some help creating a script that would intentionally create blocking processes. We have tried with the TEST environment with this application, but unfortunately, we have had no luck replicating blocking processes.

Thanks in advance for your assistance!

BEGIN TRANSACTION
  SELECT * FROM YourTable WITH (TABLOCKX, HOLDLOCK)
    WAITFOR DELAY '00:05:00' -- 5 minutes
ROLLBACK TRANSACTION

And then in another Query Window:

SELECT * FROM YourTable


Query with a few subqueries and appropriate indicies taking, on average, 613ms to execute I have a ton of queries that do a similar thing as to what I'm doing here, but none of them have the performance issues this one does:

SELECT
  (SELECT lead_id FROM leads WHERE to_id = lead_id OR from_id = lead_id) as lead_id,
  (SELECT CONCAT(first_name, " ", last_name) FROM leads WHERE to_id = lead_id OR from_id = lead_id) as lead_name
FROM
  text_messages t
WHERE sent_to_number = "xxx" OR sent_from_number = "xxx"
  AND organization = 1 GROUP BY lead_id ORDER BY sent_at DESC;

That query right there takes on average 613ms to execute.

I have indicies on from_id, to_id, sent_to_number, and sent_from_number.

Why is this query taking so long to execute?

EDIT:

Added in EXPLAIN EXTENDED:

+----+--------------------+-------+------------+-------------+------------------------------------+-----------------------+---------+------+------+----------+----------------------------------------------------------------------------------+
| id | select_type        | table | partitions | type        | possible_keys                      | key                   | key_len | ref  | rows | filtered | Extra                                                                            |
+----+--------------------+-------+------------+-------------+------------------------------------+-----------------------+---------+------+------+----------+----------------------------------------------------------------------------------+
|  1 | PRIMARY            | t     | NULL       | index_merge | organization,from_number,to_number | to_number,from_number | 62,62   | NULL |   83 |   100.00 | Using union(to_number,from_number); Using where; Using temporary; Using filesort |
|  3 | DEPENDENT SUBQUERY | leads | NULL       | ALL         | PRIMARY                            | NULL                  | NULL    | NULL | 7910 |    19.00 | Range checked for each record (index map: 0x1)                                   |
|  2 | DEPENDENT SUBQUERY | leads | NULL       | ALL         | PRIMARY                            | NULL                  | NULL    | NULL | 7910 |    19.00 | Range checked for each record (index map: 0x1)                                   |
+----+--------------------+-------+------------+-------------+------------------------------------+-----------------------+---------+------+------+----------+----------------------------------------------------------------------------------+

Edit 2:

Server Spec:
Cloud SQL - MySQL 5.7 - 1 CPU - 3.75 GB RAM - 100 GB SSD

Your query plan shows two table scans on leads, which is not surprising given the search predicates with OR: FROM leads WHERE to_id = lead_id OR from_id = lead_id.

It also shows a spilled sort, suggesting that the sort buffer is too small.

Consider rewriting your query with joins instead of subselects, something like:

SELECT
  COALESCE(leads_to.lead_id, leads_from.lead_id) as lead_id,
  COALESCE(CONCAT(leads_to.first_name, " ", leads_to.last_name), CONCAT(leads_from.first_name, " ", leads_from.last_name)) as lead_name
FROM
  text_messages t
LEFT JOIN leads leads_to ON to_id = leads_to.lead_id
LEFT JOIN leads leads_from ON to_id = leads_from.lead_id
WHERE sent_to_number = "xxx" OR sent_from_number = "xxx"
  AND organization = 1 GROUP BY lead_id ORDER BY sent_at DESC

To address the sort spill try increasing sort_buffer_size.


How to perform count(), count(distinct) in the same output using POSTGRESQL I am trying to perform both count(distinct(keyword)) and count(keyword) from same tables. Here are the two queries.

Query-1

SELECT t1.count(distinct(keyword)) from keyword t1, sent t2,tweets t3,users t4
WHERE t4.user_id='18839785'
AND t1.id=t2.id AND t2.id=t3.id AND t3.user_id=t4.user_id AND t2.sent='Pos';

count
-------
251
(1 row)

Query-2

SELECT t1.keyword,t1.count(keyword) FROM keyword t1, sent t2,tweets t3,users t4
WHERE t4.user_id='18839785' AND t3.id=t4.user_id AND t2.id=t3.id
AND t2.id=t1.id AND t2.sent='Pos' GROUP BY t1.keyword order by count(keyword) desc ;

 nlp_keyword_name | count
 ------------------+-------
 happy            |     5
 people           |     4
 handsome         |     4
 glad             |     3
 forward          |     3
 wishes           |     3
 proud            |     2
 (7 rows)

Query-3

SELECT distinct_drivers,positive_drivers,countt
from (

SELECT t1.count(distinct(keyword)) as distinct_drivers ,
t1.nlp_keyword_name as P_drivers,
t1.count(nlp_keyword_name) as countt
FROM keyword t1 LEFT JOIN sent t2 on t1.id=t2.id
LEFT JOIN tweets t3 on t3.id=t2.id
LEFT JOIN users t4 on t4.user_id=t3.user_id
WHERE (t4.user_id)=('18839785') AND t2.sent='Pos' GROUP BY t1.keyword order by countt desc) T;

But I am getting my output as

distinct_drivers | positive_drivers | countt
------------------+------------------+--------
                1 | happy            |      5
                1 | people           |      4
                1 | handsome         |      4
                1 | glad             |      3
                1 | forward          |      3

Here after executing query-3 my output has to be below way which I am not getting like that.

 **desired_output:**

 distinct_drivers | positive_drivers | countt
------------------+------------------+--------
              251 | happy            |      5
              251 | people           |      4
              251 | handsome         |      4
              251 | glad             |      3
              251 | forward          |      3

Can anyone help me out in achieving desired output. I will be thankful.
resources I have gone through Rolling sum / count / average over date interval

WITH cte1 AS ( SELECT t1.keyword, t1.count(keyword) cnt
               FROM keyword t1, sent t2, tweets t3, users t4
               WHERE t4.user_id='18839785'
                 AND t3.id=t4.user_id
                 AND t2.id=t3.id
                 AND t2.id=t1.id
                 AND t2.sent='Pos'
               GROUP BY t1.keyword ),
     cte2 AS ( SELECT COUNT(DISTINCT keyword) cnt
               FROM cte1 )
SELECT cte2.cnt distinct_drivers, cte1.keyword positive_drivers, cte1.cnt countt
FROM cte1, cte2
ORDER BY cte1.cnt;


Database design for eCommerce website with multiple products having huge specifications index I am designing a web site for online shopping and I have a question related to that. As you know that shopping website may have different product with different statistic/specification. What is the best way to design a database? Below are the thoughts which comes to my mind

Creating different tables for different product type. Ex. Phone, Tv, Video Games, Watches, Clothes, Computer etc.

  Concern:

but let's say a to show mobile specification [just like this example] I need to have around 40-50 columns to store a mobile specification. And let's say tomorrow one new feature comes to mobile then I probably have to add one more column which will hold the value for that specific model and for every other mobile that column will be NULL.

2: Having major product tables ex. Electronics, clothing and accessories, health etc.. and a separate specification table for each table. Ex. Electronics table will hold the master details for tablets, mobile, land line etc. and Electronics_Spec will contains the columns with related specification.

  Concern:

There are many characteristic between products which does not match with others. Ex. land line phone will have a specification about wire length while as tablets won't.

  General concerns:

There are certain products which has quite less specifications. Ex: bed, toys , footwear.

Is there any other better way to design a database to hold huge specifications related to multiple products?

My intention is to save database space to the best.
I sincerely apologize for my bad English.

You could have a basic product table, then also a table to store different types of specs, then another table to store the specs themselves:

products
--------
  id
  name
  (a few other necessary fields)

product_spec_types
------------------
  id
  name

product_spec_values
-------------------
  id
  product_id (FK to product.id)
  spec_id (FK to product_spec_types.id)
  value

So you could have data that looks like this:

product
ID  |   NAME
----|-----------
1   |   TV
2   | kids boots

product_spec_types
ID  |  name
------------------
1   | refresh rate
2   | size (diagonal)
3   | shoe size
4   | colour

product_spec_values
ID  | Product_ID   | Spec_ID  | Value
-------------------------------------
1   | 1            | 1        | 60 HZ
2   | 1            | 2        | 32 inches
3   | 2            | 3        | 4
4   | 2            | 4        | blue w/grey

This is a very simple implementation of the Entity-Attribute-Value pattern. You could go further if you want, and add data type restrictions (such as numeric, alpha-numeric, enumerations, etc...) to product_spec_types

The main benefit of this approach is that it can be extrmemely flexible.

The downside of this approach is that it is not always fast for searching, since all attributes for all products are in one table. Sometimes it is better to create separate tables that your user's searches will run off of, and those tables will have real columns for all the attributes you want them to be able to search.


How to design a database for non-time invariant data I am looking at designing a database where entries will probably not be time-invariant. For example, it contain entries of a customer company. The problem with a customer company is that many things can happen to it. The company can split, merge, move, go bankrupt, be bought, buy another company and so on. If I want to track interaction with this company over time, how should I design the database to be robust against all these things? I will also most probably have people in the database. These are also volatile - they resign, change job and so on.

So what is the best practice for working with non-time invariant data in a relational database? Or should I use some other database scheme?

It sounds like a problem easily solved by placing key tables in version normal form (VNF). This is actually just a flavor of 2nf but done in a way that provides the ability to query current data and past data using the same query.

A datetime parameter is used to provide the distinction. When the value is set to NOW, the current data is returned. When the value is set to a specific datetime value in the past, the data that was current at that date and time is returned.

A brief discussion of the particulars can be found in an answer I gave here. That answer also contains links to more information if you think it is something that would work for you.


Question regarding Excel connection to SSAS cube Looking for a way to change the connection in an existing spreadsheet from old SSAS server to the new SSAS server without losing the data in the spreadsheet. Hardware refresh has made this change necessary. We use Excel 2010. Nothing I have tried so far has worked. I created a new connection file (.odc) however, I can't seem to get the existing spreadsheet to connect using it or save with the new connection. Any help would be greatly appreciated.

Easiest fix would be to have your networking folks point DNS for your old server name to your new server's IP address. Unless you rely on Kerberos that will likely work fine.

If that's not an option, then if you left click on the PivotTable, to the Data tab in the ribbon, then click Properties under the Connections section, then go to the Definition tab, please see if "Always use connection file" is checked. If it is, then you can just manually edit the .odc file in Notepad and point to the new server.

If "Always use connection file" isn't checked, then you can left click on the PivotTable, then go to the PivotTable Tools... Analyze tab in the ribbon, then click the Change Data Source button, then click Choose Connection, then choose a different .odc file to connect to. Your PivotTables should be preserved and they should now connect to your new server.


Split Mysql Table internally How to split the huge mysql table (size around 200GB) internally?
Is there any mechanism other than partition?
The table size is daily growing. Table created using innodb with indices.
Thanks in advance.

Table have around 30 columns. Most of the time, i use select * as i need to show all the field. Then there is no problem in fetching data. Problem is size of table. ibd file size is now around 200gb. There are many tables like this. So i need to split the table internally as i don't have access to modify the code.

There is few ways to do it as per below-

1) partitioning but you don't want it. Also need to remember that partitioning does not support with foreign keys.

2) You can normalize your table if there is duplicate values in table.

3) If this table contains transactional data or logs then you can move old data in historical tables year wise etc if possible in your environment.


Can an SSIS variable have a default value? I have to migrate a SQL Server 2000 database to version 2008. A large part of that work is to update all the DTS packages to SSIS. Many of the packages require using the current date to establish parameter settings as well as date-stamping files (i.e. myoutputYYYYMMDD.csv, for example). This logic has been ported over to the new SSIS packages but the problem I'm having is now during testing.

Backups and restores from the old to the new server is a bit problematic due to its size but since the packages do archiving, I could take an older backup and run tests against that and compare package results with some archived files, in lieu of real-time comparisons.

Since this is a better logistical solution, I need to redesign the packages where any of the package variables depend on the usage of GETDATE() and need to reference a new date variable that will be GETDATE() once we roll out to production but needs to be a different date during testing (e.g. I restore a 1/2/2014 and then pass in 1/3/2014 to my date variable during testing but in live production use, it will be GETDATE()).

Is there an elegant way to do this?

you should be able to do it quite easily like this:

Bring the variables editor up & add a variable to contain your preferred date
Inspect the variable property window
Choose the ellipsis for the Expressions property to get the expression editor
Define your date output using getdate() functions etc. (reasonable subset of T-SQL fns are present)
The result will be shown in the variables pane (value) & re-evaluated at runtime
For testing, remove the expression & replace with a fixed value of your choosing
Get really clever & add separate debug_date & debug_mode variables that you can use in the expression to switch between the expression & your fixed value depending which mode you are in.


Change the binlog format MIXED to ROW I have a database that is running on AWS RDS (MySQl 5.7.22).

Currently, I have one master and 2 replicas running. My binlog format is MIXED. I have plans to change it to ROW based.

I want to change it during runtime (live environment) since it's our main database.

Questions

What things do I have to consider before I change it?
What is the safest way to do it?
I am just wondering if there will be growth of the binlog size when I change it?

you can use at the runtime.

SET GLOBAL binlog_format = 'ROW';

or for the session

SET SESSION binlog_format = 'ROW';

see the manual for more information.


Join multiple Tables by date i got some problems on my approach to join a couple of tables by a date range. This are my tables:

RETURNS:

RETURN_DATE     RETURN_AMOUNT
02.05.14        2
02.05.14        3
02.05.14        1
02.05.14        7
02.05.14        5
02.05.14        2
02.05.14        1

and

ORDERS:

ORDER_DATE      ORDER_AMOUNT
02.05.14        50

As a first step, i created a date range and then tried to join these two tables to it. This is my SELECT statement:

SELECT
  DAT,
  RETURN_AMOUNT,
  ORDER_AMOUNT

  FROM
    (
      SELECT
      TO_DATE('02.05.2014','dd.mm.yyyy')+ROWNUM-1 AS DAT

      FROM
      all_objects

      WHERE
      ROWNUM <= (TO_DATE('02.05.2014','dd.mm.yyyy')-TO_DATE('02.05.2014','dd.mm.yyyy')+1)
    ) LEFT JOIN RETURNS ON RETURN_DATE = DAT
    LEFT JOIN ORDERS ON ORDER_DATE = DAT

    ORDER BY DAT;

This was the Result:

RESULT:

DAT         RETURN_AMOUNT   ORDER_AMOUNT
02.05.14    1               50
02.05.14    2               50
02.05.14    5               50
02.05.14    2               50
02.05.14    1               50
02.05.14    3               50
02.05.14    7               50

Thats a problem because when i GROUP BY DAT and then sum up the amounts, i got a wrong ORDER_AMOUNT for this day.

How can i achive, that ORDER_AMOUNT is only resulted once?

Thanks,
Stefan.

You could join to the 2 tables separately (and GROUP BY) and then join them together:

WITH dates (dat) AS
    ( SELECT
        TO_DATE('02.05.2014','dd.mm.yyyy') + ROWNUM - 1 AS dat
      FROM
        all_objects
      WHERE
        ROWNUM <= TO_DATE('02.05.2014','dd.mm.yyyy')
                  - TO_DATE('02.05.2014','dd.mm.yyyy') + 1
    )
, ret (dat, return_amount) AS
    ( SELECT
        d.dat,
        COALESCE(SUM(r.return_amount), 0) AS return_amount
      FROM dates  d
        LEFT JOIN returns  r ON r.RETURN_DATE = d.dat
      GROUP BY
        d.dat
    )
, ord (dat, order_amount) AS
    ( SELECT
        d.dat,
        COALESCE(SUM(o.order_amount), 0) AS order_amount
      FROM dates  d
        LEFT JOIN orders  o ON o.ORDER_DATE = d.dat
      GROUP BY
        d.dat
    )
SELECT
  r.dat,
  r.return_amount,
  o.order_amount
FROM
  ret  r
    JOIN ord  o ON o.dat = r.dat
ORDER BY
  r.dat ;

Or use inline subqueries:

SELECT
  d.dat,
  COALESCE( ( SELECT SUM(r.return_amount)
              FROM returns  r
              WHERE r.dat = d.dat
            ), 0)
    AS return_amount,
  COALESCE( ( SELECT SUM(o.order_amount)
              FROM orders  o
              WHERE o.dat = d.dat
            ), 0)
    AS order_amount
FROM
    ( SELECT
        TO_DATE('02.05.2014','dd.mm.yyyy') + ROWNUM - 1 AS dat
      FROM
        all_objects
      WHERE
        ROWNUM <= TO_DATE('02.05.2014','dd.mm.yyyy')
                  - TO_DATE('02.05.2014','dd.mm.yyyy') + 1
    ) d
ORDER BY
  d.dat ;


Why does an sqlite3 .dump and restore result in a smaller database file size? I have a really naive question about database file sizes. I started out with a 1.2M SQLite database, dumped it to a SQL file, and then restored it to a new database. The restored database file was only 50K. Here's what my session looked like:

$ ls -lh
total 1.2M
-rw-r--r-- 1 user group 1.2M Jul  6 18:37 db.sqlite

$ sqlite3 db.sqlite .dump > db.sql

$ ls -lh
total 1.2M
-rw-r--r-- 1 user group  16K Jul  6 18:38 db.sql
-rw-r--r-- 1 user group 1.2M Jul  6 18:37 db.sqlite

$ cat db.sql | sqlite3 db-restored.sqlite

$ ls -lh
total 1.3M
-rw-r--r-- 1 user group  50K Jul  6 18:38 db-restored.sqlite
-rw-r--r-- 1 user group  16K Jul  6 18:38 db.sql
-rw-r--r-- 1 user group 1.2M Jul  6 18:37 db.sqlite

Why is the restored file so much smaller than the original?

With the default settings, when rows (or entire tables) are deleted, SQLite does not remove empty pages from the database file. (This setting can be changed with PRAGMA auto_vacuum.)

In most cases, keeping empty pages in the database file is good idea because they will be reused by new rows that are inserted later, and adjusting the database file to remove those pages, only to reallocate them later, would be unneeded overhead.


Grant permissions on a stored procedure to tables in another schema I created a user in SQL Server 2012 database and revoked all permissions given by the public role.

Then I granted EXECUTE permission on a stored procedure.
The user can execute the procedure but cannot get the data it returns.

The procedure is in schema1, and the tables from which it selects are in schema2.

If I add the user to the db_datareader role it can read all data from all the tables in the database. I tried using WITH EXECUTE AS OWNER but it didn't work.

How can I grant only the access to the given procedure and nothing else?

No, you do not want (or need) WITH EXECUTE AS OWNER.

The problem is simply that your two schemas — Schema1 wit the stored procedure, and Schema2 with the tables — have different owners. If you execute the following query, you will see who owns which schemas:

SELECT *, USER_NAME([principal_id]) AS [UserName]
FROM   sys.schemas;

Ownership chaining, the default security mechanism, assumes DML and EXECUTE permissions on objects referenced by the object being accessed, IF the owner is the same. If no owner has been explicitly assigned to an object (default = NULL), then the "owner" is considered to be the owner of the schema in which the object exists.

So, technically your schemas could have the same owner and it's just that the tables have a different owner due to being given one explicitly (via ALTER AUTHORIZATION), but it's much more likely that the schemas have different owners.

Fortunately, this situation is easy to solve using Module Signing.

The concept is to create a certificate in the database containing these schemas and objects, which will then be used to sign the stored procedure that exists in Schema1 but selects from tables in Schema2.

A user is created from the certificate, and is then assigned whatever permission(s) are needed in order for the stored procedure to complete successfully.

No permissions are being granted to anyone that will be executing the stored procedure. The permissions are granted only to the certificate-based user. The connection between the stored procedure and the certificate-based user is (effectively) the public key of the certificate. And that public key is only loaded into the session's security context when a module that has been signed with that certificate is being executed. And that signature requires the certificate's password (in the ADD SIGNATURE statement), and is dropped if anyone changes even a single byte of the stored procedure (or the owner of it). Hence, very secure.

The following illustrates this concept:

Main Setup

CREATE SCHEMA [Schema1];
GO
CREATE SCHEMA [Schema2];
GO

CREATE USER [TestUser] WITHOUT LOGIN;

CREATE TABLE [Schema2].[Stuff]
(
  [StuffID] INT NOT NULL IDENTITY(1, 1)
    CONSTRAINT [PK_Stuff] PRIMARY KEY,
  [Stuff] NVARCHAR(50) NOT NULL
);

INSERT INTO [Schema2].[Stuff] ([Stuff]) VALUES (N'Success!!');

GO
CREATE PROCEDURE [Schema1].[GetStuff]
AS
SET NOCOUNT ON;

SELECT [StuffID], [Stuff]
FROM   [Schema2].[Stuff];
GO

GRANT EXECUTE ON [Schema1].[GetStuff] TO [TestUser];

Test Same Schema Owners

EXECUTE AS USER = N'TestUser';
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];

EXEC [Schema1].[GetStuff];
-- Success due to both Schemas having same owner

REVERT;
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];

Reproduce Current Situation

SELECT *, USER_NAME([principal_id]) AS [UserName]
FROM   sys.schemas
WHERE  [name] IN (N'Schema1', N'Schema2');
/*
name       schema_id    principal_id    UserName
Schema1    5            1               dbo
Schema2    6            1               dbo
*/

ALTER AUTHORIZATION ON SCHEMA::[Schema2] TO [guest];

SELECT *, USER_NAME([principal_id]) AS [UserName]
FROM   sys.schemas
WHERE  [name] IN (N'Schema1', N'Schema2');
/*
name       schema_id    principal_id    UserName
Schema1    5            1               dbo
Schema2    6            2               guest
*/

Test Current Situation (no extra permissions)

EXECUTE AS USER = N'TestUser';
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];

EXEC [Schema1].[GetStuff];
-- ERROR:
/*
Msg 229, Level 14, State 5, Procedure Schema1.GetStuff, Line XXXXX [Batch Start Line YYYYY]
The SELECT permission was denied on the object 'Stuff', database '......', schema 'Schema2'.
*/

REVERT;
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];

Module Signing Setup

-- Create Certificate and sign Stored Procedure with it:
CREATE CERTIFICATE [Permission$Schema2]
    ENCRYPTION BY PASSWORD = 'SomePassword'
    WITH SUBJECT = 'Used for selecting from objects in Schema2',
    EXPIRY_DATE = '2099-12-31';

ADD SIGNATURE
    TO [Schema1].[GetStuff]
    BY CERTIFICATE [Permission$Schema2]
    WITH PASSWORD = 'SomePassword';

-- Create Certificate-based User to hold permissions
CREATE USER [Permission$Schema2]
  FROM CERTIFICATE [Permission$Schema2];

-- Grant Certificate-based User ONLY the permissions needed
GRANT SELECT ON SCHEMA::[Schema2] TO [Permission$Schema2];

Test with Module Signing

EXECUTE AS USER = N'TestUser';
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];

EXEC [Schema1].[GetStuff];
-- 1    Success!!!

REVERT;
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];


Error when add new host shard server I am getting an error when shard the existing collection. Can you help me?
This my error
{"t":{"$date":"2020-11-23T15:11:05.331+07:00"}
 ,"s":"I", "c":"MIGRATE"
 , "id":23893
 , "ctx":"MoveChunk"
 ,"msg":"MigrationCoordinator delivering decision to self and to recipient"
 ,"attr":{"decision":"aborted"
 ,"migrationId":{"uuid":{"$uuid":"0d05a912-9ca1-4bf6-ba94-b9656b01da4d"}}}}
{"t":{"$date":"2020-11-23T15:11:05.332+07:00"}
 ,"s":"W"
 , "c":"MIGRATE"
 , "id":22022
 , "ctx":"MoveChunk"
 ,"msg":"Failed to clean up migration"
 ,"attr":{"chunkMigrationRequestParameters":"ns: exampleDB.exampleCollection, [{ _id: MinKey }
 , { _id: -4611686018427387902 })
 , fromShard: shard0000, toShard: shard0002"
 ,"error":"InterruptedDueToReplStateChange: Stepped down while persist migrate abort decision"
 ,"migrationId":{"uuid":{"$uuid":"0d05a912-9ca1-4bf6-ba94-b9656b01da4d"}}}}
{"t":{"$date":"2020-11-23T15:11:05.332+07:00"},"s":"W", "c":"SHARDING", "id":23777, "ctx":"MoveChunk","msg":"Error while doing moveChunk","attr":{"error":"IllegalOperation: Transaction numbers are only allowed on a replica set member or mongos"}}
{"t":{"$date":"2020-11-23T15:11:05.332+07:00"},"s":"I"
  , "c":"SHARDING", "id":22080
  , "ctx":"MoveChunk"
  ,"msg":"About to log metadata event"
  ,"attr":{"namespace":"changelog"
           ,"event":{"_id":"mongodb-shard:27017-2020-11-23T15:11:05.332+07:00-5fbb6e993cfb07ec322470fd"
          ,"server":"mongodb-shard:27017"
          ,"shard":"shard0000"
          ,"clientAddr":""
          ,"time":{"$date":"2020-11-23T08:11:05.332Z"}
                   ,"what":"moveChunk.from"
                   ,"ns":"exampleDB.exampleCollection"
                   ,"details":{"min":{"_id":{"$minKey":1}}
                   ,max":{"_id":-4611686018427387902}
                   ,"step 1 of 6":0
                  ,"step 2 of 6":2
                  ,"to":"shard0002"
                  ,"from":"shard0000"
                 ,"note":"aborted"}}}}

This done, on mongodb sharding version 4.4, shard server must be replica set althoug only 1 server in replica set.


Transaction log for standalone DB vs AlwaysON availability Group I have this question on the generation of transaction logs. For a high transaction active DB what is the ratio of log generation if it is a standalone DB and when it is part of SQL Alwayson availability group(synchronous commit).

I am pretty sure it will be high, but how much higher. Rather than doing a test on my own any answer or reference will be helpful.

Note: The perfmon counter do show the numbers for transactions/logs flushed which seems to be really in case of AG. I want to understand if there is a way to limit it as well.

For a high transaction active DB what is the ratio of log generation if it is a standalone DB and when it is part of SQL Alwayson availability group(synchronous commit).

Assuming Full Recovery mode, which AGs require, 1:1.  AGs don't require any additional logging beyond what a Full Recovery database already does.

In Simple recovery mode you might have some minimally logged operations, but for a "high transaction active" database, it's unlikely.  See eg

SQL Server 2016, Minimal logging and Impact of the Batchsize in bulk load operations


In 2-node cluster using failover with Dynamic Quorum, is Witness needed? I have a 2-node cluster on SQL Server 2016 Standard with failover clustering configured with Dynamic Quorum.

There is one thing that made me confused: do we really need a Witness in this case?

Since I've enabled Dynamic Quorum, my cluster will not go down if one of the nodes goes down.

But some guys said, for the best practice, we still need to configure a Witness. So my question is: will a Witness make any difference?

Dynamic quorum basically dynamically adjusts votes depending on available servers.

Each time when one of nodes goes down, dynamic quorum will remove the vote from that node.
In your scenario you have 2 nodes only and dynamic quorum will automatically remove the vote from your passive node, so the 1st node will have the majority of votes. In planned maintenance scenario when you are shutting down the first node quorum will transfer the vote from first to the second, and remove it from the first node.

However in scenario where first node just crashes quorum does not have time to transfer the vote and your second node wont get to vote, which basically will just shut down your cluster.

Therefore in scenario with 2 nodes only, it is recommended to have a witness.


How to avoid inner query im MYSQL I have a table with 4.7 million rows and I am executing this query on this table:

SELECT * FROM students WHERE `eid` IN
(SELECT DISTINCT `eid` FROM students WHERE code IN ('120', '330'));

This is working fine but it slows down the performance. How can I boost up performance by changing this query or how can I do the same work with some other optimized query (in better way)?

Having no idea of your table... The first thing to try would be to

CREATE INDEX i_code ON students (code);
CREATE INDEX i_eid ON students (eid);


Cassandra "Operation timed out - received only 0 responses." during write I'm using Datastax PHP Cassandra Driver and Cassandra 2.2.5 on Windows 10. This is a single-node cluster, and the keyspace replication of

{
    'class': 'SimpleStrategy',
    'replication_factor': 1
}

This is the table I'm inserting to:

CREATE TABLE MyDocuments (
    "label" varchar,
    "fileName" varchar,
    "blob" blob,
    "createdTime" bigint,
    "lastUpdateTime" bigint,
    PRIMARY KEY("label", "fileName")
);

When I tried to insert a row with a blob that is 16419KB large, it gave me this error message Operation timed out - received only 0 responses.
The row is not inserted at all.

Inserting a row with a blob that is 16379KB worked fine.

The log files don't show anything. When the operation fails, nothing is appended to any log files inside Datastax / Cassandra folders.

After a few hours of Googling, I still have no clue as to why this is happening. I have tried increasing write_request_timeout_in_ms to 20000 in the settings (cassandra.yaml), but it does not help.

Does anyone know why a write seems to fail in this way when the size exceeds ~16MB?

Update:

I updated Cassandra to v3.7, same observation, but the error message now says Operation failed - received 0 responses and 1 failures.

You will need to increase commitlog_segment_size_in_mb to a higher value. Try 64MB instead.

It increases the size of the commit record segment. This property determines the maximum mutation size (max_mutation_size_in_kb), defined as half of the segment size. By default is 32MB, which made your record were overflowing the maximum mutation size.

From the docs:

  (Default: 32MB) Sets the size of the individual commitlog file
  segments. A commitlog segment may be archived, deleted, or recycled
  after all its data has been flushed to SSTables. This amount of data
  can potentially include commitlog segments from every table in the
  system. The default size is usually suitable for most commitlog
  archiving, but if you want a finer granularity, 8 or 16 MB is
  reasonable.

  This property determines the maximum mutation size, defined as half
  the segment size. If a mutation's size exceeds the maximum mutation
  size, the mutation is rejected. Before increasing the commitlog
  segment size of the commitlog segments, investigate why the mutations
  are larger than expected. Look for underlying issues with access
  patterns and data model, because increasing the commitlog segment size
  is a limited fix.


Combine date and time into one new column I have two columns named date and time respectively. Both of them have datatype nvarchar as they were imported from ms excel. I want to combine the column into a new column named datetime. I managed to get the result as I wanted by using:

SELECT date + ' ' + time as datetime
FROM column1

But I can't insert them into the new column.

Does the new column already exist? If it doesn't then you need to create it:

ALTER TABLE YourTable ADD [DateTime] DateTime2(0) NULL;

Then you go and "insert":

UPDATE YourTable
SET [DateTime] = CONVERT(DateTime2(0), (date + ' ' + time))


how to get only those records that have more than one values? ID           Matches
1000029049 335xxx219597989
1000029361 335xxx217190890
1000031383 335xxx219826131
1000031433 492xxx6557366
1000031433 335xxx216660374

I want to print only the ids that have different matches.
E.g I want the below output.

ID           Matches
1000031433 492xxx6557366
1000031433 335xxx216660374

You need to group and join to the aggregation

select t1.*
from the_table t1
  join (
    select id
    from the_table
    group by id
    having count(distinct matches) > 1
  ) t2 on t1.id = t2.id


Using MySQL Workbench, what is a good way to do development on an alternate server and copy changes to the main server? This is a noobie question. I'm taking over a database project from someone who recently left the company and I'm looking for advice on how to do some development on a test platform before making changes on the active server. I've got MySQL installed on both the server and my local machine, with MySQL Workbench as the interface to both. Apache and PHP are also installed on both platforms.

I think what I want is a local copy of the schema (including data) that I can occasionally synchronize with the server. So when I want to do any development I would:

pull any changes from the server
make changes locally and test them out
then copy the modifications back up to the server.

Is that what people do in practice?
If No: What is the recommended way to do development without messing up the existing database?
If Yes: Can you offer some details on how to do this with MySQL Workbench.

Thank you, and please let me know if you need any more information.

What I traditionally do, if I can't sync between databases, is take a copy of the schema, add  dummy data, and develop on that locally. Then I save all my modification scripts and run them on the main server when I'm ready to push.

I would avoid moving data from development to production. Production -> Development may be fine if you don't have large amounts of data. Otherwise, use a dummy data generator if you can.

Ultimately, you'll have to develop your own methodology just like you do when you introduce version control or backups to a project. It is dependent on the situation.


VMware vFabric Data Director https://www.brentozar.com/archive/2013/04/cloud-alternatives-to-microsoft-sql-server/

I liked the idea of VMware vFabric Data Director so I can host data in own cloud / data centre. It seems this product is now end of life.

Do you know of any similar products still available in the market? I was looking for ways of reducing our licencing overhead with the costs of SQL server increasing, I also like the concept of database as a service taking away DBA bottlenecks in projects.

Folks I see going in this direction tend to go with Amazon RDS or Microsoft Azure SQL DB. If you’re interested in reducing DBA costs, you’re also usually interested in reducing sysadmin and hardware costs, too.


Help - Error : Could not connect to master of shard replica set I have a shard, using mongo v2.4.11. It has to mongos, 3 config, and 8 replica set (2 replica for each set, means 16 node for shard)

It is keep having exception as per below.

Caused by: com.mongodb.MongoException: can't connect to new replica set master [shardA01:27017], err: couldn't connect to server shardA01:27017

I try to look for anyone who might has similar issue, but i found nothing. This happens about 1 of 1000 request.

All node of shard is hosted using aws virtual servers.

What i have done is
- check CPU and memory but everything is fine, not even > 10% of usage
- set master priority to one of the node.

Anyone know what the problem is? Or how to further investigate the issue?

the problem seems to be the version of mongodb. Somehow for mongodb < 2.6 socket problem will intermittently occurs. When it occurs, you have to restart your mongos node.


Slow postgresql query with pagination and filtering For the DB in question, I have followed the advice of Pagination Done the
PostgreSQL Way

That is, results are being paginated on a composite index with a definite sort order.

Now, my problem is that when this is combined with a where-clause filter on a join table, the query becomes very slow.

How can I make my query faster in the filtering case?

Note: pagination_index is a unique index on sort_key_1, sort_key_2, sort_key_3.

Query without filter

EXPLAIN (ANALYZE, BUFFERS) SELECT *
FROM big_table

join small_table on small_table_id = small_table.id
--WHERE small_table.filter_column = 9173

ORDER BY sort_key_1, sort_key_2, sort_key_3
LIMIT 20

Limit  (cost=0.85..124.26 rows=20 width=293) (actual time=49.947..50.254 rows=20 loops=1)
  Buffers: shared hit=64 read=1
  ->  Nested Loop  (cost=0.85..318749153.46 rows=51655908 width=293) (actual time=49.942..50.189 rows=20 loops=1)
        Buffers: shared hit=64 read=1
        ->  Index Scan using pagination_index on big_table  (cost=0.56..106181666.21 rows=51655908 width=113) (actual time=0.019..0.054 rows=20 loops=1)
              Buffers: shared hit=5
        ->  Index Scan using small_table_pkey on small_table  (cost=0.28..4.11 rows=1 width=180) (actual time=2.498..2.500 rows=1 loops=20)
              Index Cond: (id = big_table.small_table_id)
              Buffers: shared hit=59 read=1
Total runtime: 92.107 ms

Query with filter

EXPLAIN (ANALYZE, BUFFERS) SELECT *
FROM big_table

join small_table on small_table_id = small_table.id
WHERE small_table.filter_column = 9173

ORDER BY sort_key_1, sort_key_2, sort_key_3
LIMIT 20

Limit  (cost=0.85..79443.10 rows=20 width=293) (actual time=1331003.261..2376282.943 rows=16 loops=1)
  Buffers: shared hit=45840126 read=1148265
  ->  Nested Loop  (cost=0.85..108506196.66 rows=27317 width=293) (actual time=1331003.257..2376282.873 rows=16 loops=1)
        Join Filter: (big_table.small_table_id = small_table.id)
        Rows Removed by Join Filter: 154967708
        Buffers: shared hit=45840126 read=1148265
        ->  Index Scan using pagination_index on big_table  (cost=0.56..106181666.21 rows=51655908 width=113) (actual time=0.010..1544834.357 rows=51655908 loops=1)
              Buffers: shared hit=45840121 read=1148265
        ->  Materialize  (cost=0.28..14.60 rows=3 width=180) (actual time=0.002..0.007 rows=3 loops=51655908)
              Buffers: shared hit=5
              ->  Index Scan using small_table_id_index on small_table  (cost=0.28..14.59 rows=3 width=180) (actual time=0.008..0.017 rows=3 loops=1)
                    Index Cond: (filter_column = 9173)
                    Buffers: shared hit=5
Total runtime: 2376283.041 ms

Query with filter but no order by

This is relatively fast also.

EXPLAIN (ANALYZE, BUFFERS) SELECT *
FROM big_table

join small_table on small_table_id = small_table.id
WHERE small_table.filter_column = 9173

--ORDER BY sort_key_1, sort_key_2, sort_key_3
LIMIT 20

Limit  (cost=0.56..78.57 rows=20 width=293) (actual time=0.035..100.824 rows=16 loops=1)
  Buffers: shared hit=18 read=132
  ->  Nested Loop  (cost=0.56..106540.40 rows=27317 width=293) (actual time=0.031..100.759 rows=16 loops=1)
        Buffers: shared hit=18 read=132
        ->  Seq Scan on small_table  (cost=0.00..200.91 rows=3 width=180) (actual time=0.014..10.124 rows=3 loops=1)
              Filter: (filter_column = 9173)
              Rows Removed by Filter: 5670
              Buffers: shared hit=2 read=128
        ->  Index Scan using big_table_small_table_index on big_table  (cost=0.56..35179.54 rows=26696 width=113) (actual time=30.168..30.183 rows=5 loops=3)
              Index Cond: (small_table_id = small_table.id)
              Buffers: shared hit=16 read=4
Total runtime: 100.901 ms

I now believe my question to be a duplicate of Slow ORDER BY with LIMIT.

I simplified the query (taking out the complication of a join table) to what matters.

Fast query

explain (analyze, buffers)
SELECT *
FROM big_table

where small_table_id = 822573
ORDER BY sort_key_1, sort_key_2, sort_key_3

Sort  (cost=24642.79..24686.64 rows=17540 width=113) (actual time=0.983..1.471 rows=227 loops=1)
  Sort Key: sort_key_1, sort_key_2, transmitter_id
  Sort Method: quicksort  Memory: 56kB
  Buffers: shared hit=1 read=8
  ->  Index Scan using big_table_small_table_index on big_table  (cost=0.56..23406.36 rows=17540 width=113) (actual time=0.055..0.514 rows=227 loops=1)
        Index Cond: (small_table_id = 822573)
        Buffers: shared hit=1 read=8
Total runtime: 1.941 ms

Slow query

Note: only change is the addition of a  LIMIT clause.

explain (analyze, buffers)
SELECT *
FROM big_table

where small_table_id = 822573
ORDER BY sort_key_1, sort_key_2, sort_key_3

LIMIT 1

Limit  (cost=0.56..6061.27 rows=1 width=113) (actual time=206737.547..206737.550 rows=1 loops=1)
  Buffers: shared hit=13259226 read=303563
  ->  Index Scan using pagination_index on big_table  (cost=0.56..106304766.93 rows=17540 width=113) (actual time=206737.541..206737.541 rows=1 loops=1)
        Filter: (small_table_id = 822573)
        Rows Removed by Filter: 15520057
        Buffers: shared hit=13259226 read=303563
Total runtime: 206737.643 ms

Fast query, with limit

As suggested in the linked question, disabling index scans makes the limited query fast:

SET enable_indexscan = OFF;

explain (analyze, buffers)
SELECT *
FROM big_table

where small_table_id = 822573
ORDER BY sort_key_1, sort_key_2, sort_key_3

LIMIT 1

Limit  (cost=62302.77..62302.77 rows=1 width=113) (actual time=0.897..0.899 rows=1 loops=1)
  Buffers: shared hit=9
  ->  Sort  (cost=62302.77..62346.62 rows=17540 width=113) (actual time=0.894..0.894 rows=1 loops=1)
        Sort Key: sort_key_1, sort_key_2, transmitter_id
        Sort Method: top-N heapsort  Memory: 25kB
        Buffers: shared hit=9
        ->  Bitmap Heap Scan on big_table  (cost=388.50..62215.07 rows=17540 width=113) (actual time=0.040..0.477 rows=227 loops=1)
              Recheck Cond: (small_table_id = 822573)
              Buffers: shared hit=9
              ->  Bitmap Index Scan on big_table_small_table_index  (cost=0.00..384.11 rows=17540 width=0) (actual time=0.030..0.030 rows=227 loops=1)
                    Index Cond: (small_table_id = 822573)
                    Buffers: shared hit=4
Total runtime: 0.932 ms

Fast query, with Common Table Expression

The following query is also fast:

SET enable_indexscan = ON;

explain (analyze, buffers)
with t as (
SELECT *
FROM big_table

where small_table_id = 822573
ORDER BY sort_key_1, sort_key_2, sort_key_3
)
select * from t
LIMIT 1

Limit  (cost=24686.64..24686.66 rows=1 width=3172) (actual time=1.011..1.013 rows=1 loops=1)
  Buffers: shared hit=9
  CTE t
    ->  Sort  (cost=24642.79..24686.64 rows=17540 width=113) (actual time=1.001..1.001 rows=1 loops=1)
          Sort Key: big_table.sort_key_1, big_table.sort_key_2, sort_key_3
          Sort Method: quicksort  Memory: 56kB
          Buffers: shared hit=9
          ->  Index Scan using big_table_small_table_index on big_table  (cost=0.56..23406.36 rows=17540 width=113) (actual time=0.025..0.504 rows=227 loops=1)
                Index Cond: (small_table_id = 822573)
                Buffers: shared hit=9
  ->  CTE Scan on t  (cost=0.00..350.80 rows=17540 width=3172) (actual time=1.007..1.007 rows=1 loops=1)
        Buffers: shared hit=9
Total runtime: 1.070 ms


Ok to drop indexes on FK's if they have no stats in the DMVS I have used the well known query below from Kevin Kline to check for unused indexes. Several indexes created on Foreign keys returns no read stats, only writes.

Are you 100% safe to drop these indexes ? or could they be used by the optimizer for say inserts or Deletes and would not register any stats in the DMV ?? if so, how can one tell they are 100% safe to remove?

I have had the server running for 2 months so am certain I have covered our monthly workload cycle.

    SELECT o.name
    , indexname=i.name
    , i.index_id
    , reads=user_seeks + user_scans + user_lookups
    , writes =  user_updates
    , rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
    , CASE
        WHEN s.user_updates < 1 THEN 100
        ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
      END AS reads_per_write
    , 'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'
    FROM sys.dm_db_index_usage_stats s
    INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id
    INNER JOIN sys.objects o on s.object_id = o.object_id
    INNER JOIN sys.schemas c on o.schema_id = c.schema_id
    WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
    AND s.database_id = DB_ID()
    AND i.type_desc = 'nonclustered'
    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
    AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 2000
    ORDER BY  name, reads

As long as you are sure the server was up for that entire time, and that nobody cleared out DMV stats inadvertently. This can happen if the database is detached + re-attached / restored / offline + online / auto-close + online, or if the index has been explicitly dropped / re-created (the DMV is not affected by disable / rebuild / reorganize, except in the case of SQL Server 2012, where rebuild currently clears the stats - I suspect that will be fixed - thanks @MartinSmith).

You should expect that if a DML activity used the index in a read capacity for some reason (e.g. to perform an update on some other table), this would register as a read activity, not a write. All of the writes you see are index maintenance.

As an aside, here is a slightly more efficient way to write this query. I removed the twice-referenced correlated subquery and dropped the unnecessary joins to sys.objects and sys.schemas. I also fixed a few minor syntax things, such as prefixing all columns with aliases where appropriate, placing reserved words in square brackets, and removing AS 'column alias' syntax. I found it confusing that half way through the SELECT list you changed from alias = expression syntax to expression AS alias syntax - you should pick one and be consistent IMHO. I changed these both for majority within the query and also according to my personal preference. :-)

SELECT name = OBJECT_NAME(s.[object_id])
    , indexname = i.name
    , i.index_id
    , reads = s.user_seeks + s.user_scans + s.user_lookups
    , writes = s.user_updates
    , g.[rows]
    , reads_per_write = CASE
        WHEN s.user_updates < 1 THEN 100.0
        ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
      END
    , [drop statement] = 'DROP INDEX ' + QUOTENAME(i.name)
      + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(s.[object_id]))
      + '.'    + QUOTENAME(OBJECT_NAME(s.[object_id]))
    FROM sys.dm_db_index_usage_stats AS s
    INNER JOIN sys.indexes AS i
      ON i.index_id = s.index_id
      AND s.object_id = i.object_id
    INNER JOIN
    (
      SELECT [object_id], index_id, [rows] = SUM([rows])
        FROM sys.partitions GROUP BY [object_id], index_id
        HAVING SUM([rows]) > 2000
    ) AS g
      ON i.[object_id] = g.[object_id]
      AND i.index_id = g.index_id
    WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
    AND s.database_id = DB_ID()
    AND i.type_desc = 'nonclustered'
    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
    ORDER BY name, reads;


Ola Hallengren Index Script not Reindexing First, I realize a similar question was asked and the poster had page count set to 1000 for a 679 page index; not what's going on.

I have Ola's script set as

@Databases nvarchar(max)
,@FragmentationLow nvarchar(max) = null
,@FragmentationMedium nvarchar(max) = 'INDEX_REORGANIZE'
,@FragmentationHigh nvarchar(max) = 'INDEX_REBUILD_ONLINE'
,@FragmentationLevel1 int = 50
,@FragmentationLevel2 int = 75
,@PageCountLevel int = 400
,SortInTempdb nvarchar(max) = 'N'
,maxdop int = null
,fillfactor int = null
,PadIndex nvarchar(max) = null
,LOBCompaction nvarchar(max) = 'Y'
,UpdateStatistics nvarchar(max) = 'ALL'
,OnlyModifiedStatistics nvarchar(max) = 'Y'
,StatisticsSample int = null
,StatisticsResample nvarchar(max) = 'N'
,PartitionLevel nvarchar(max) = 'Y'
,MSShippedObjects nvarchar(max) = 'N'
,Indexes nvarchar(max) = 'MyDatabase.dbo.Table'
,TimeLimit int = null
,delay int = null
,WaitAtLowPriorityMaxDuration int = null
,WaitAtLowPriorityAbortAfterWait nvarchar(max) = null
,LockTimeout int = null
,LogToTable nvarchar(max) = 'Y'
,

execute nvarchar(max) = 'Y'

The index in question is as follows:

Page Fullness: 75.77%
Fragmentation: 99.14%
Avg. Row Size: 33
Depth: 4
Index Type: Nonclustered
Leaf-lvl rows: 11130800
Max Size: 33
Min Size: 33
Pages: 63585
Partition ID: 1

It's set so low because the 1000 mark isn't hitting the index. What's odd is that stats are being updated but the index isn't being reorganized or rebuilt.

I'm looking for things to explore or concepts to think about that will help me solve this issue.

Again, if this has been asked and answered, please forgive me and forgive me if the answer is so ridiculously easy and stupid that it's staring me in the face.

Thanks.

Table Schema

CREATE TABLE [dbo].[Table](
    [column1] [uniqueidentifier] NOT NULL,
    [column2] [uniqueidentifier] NOT NULL,
    [column3] [uniqueidentifier] NOT NULL,
    [column4] [uniqueidentifier] NOT NULL,
    [column5] [nvarchar](50) NULL,
    [column6] [nvarchar](128) NULL,
    [Column7] [money] NOT NULL,
    [column8] [money] NOT NULL,
    [column9] [nvarchar](128) NULL,
    [column10] [nvarchar](64) NULL,
    [column11] [image] NULL,
 CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED
(
    [Column1_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_Index] ON [dbo].[Table]
(
    [column2] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 70) ON [PRIMARY]
GO

Ah!  It's the image column.

[column11] [image] NULL,

Online only works on tables without blobs.

Guidelines for Performing Online Index Operations


Is a relation R(A,B,C) with functional dependencies {AB->C, C->B} in BCNF? If not, then how do I make it BCNF while being lossless and dependency preserving?

I can see that the primary key of R is AB. And in BCNF every functional dependency should be of the form [Key] -> [Key or Non-key]. But C->B doesn't satisfy that.

I know I will lose the AB->C dependency if I do any decomposition. But I am unable to figure out which decomposition would be lossless. I would be grateful if someone could help me out.

Given AB->C, C->B we can infer only that AB and AC are keys. It follows that C is not a superkey and therefore R does not satisfy BCNF.

The following is a possible decomposition that would satisfy BCNF

R1{A,B} KEY {A,B}
R2{A,C} KEY {A,C}
R3{C,B} KEY {C}


how to make this query run fast I have this query:

    SELECT id,type,source_id,link,short_link,cat_id,title,description,images,views,comments,published,publishedon FROM listings WHERE
(YEARWEEK( FROM_UNIXTIME( publishedon ) , 1 ) = YEARWEEK( CURDATE( ) , 1 )) AND
(id != 88587) AND
(cat_id = '64')
AND (deleted = 0)
AND (published = 1)
ORDER BY views DESC LIMIT 1 OFFSET 0

table explain

CREATE TABLE IF NOT EXISTS `listings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` tinyint(1) NOT NULL DEFAULT '1',
  `hash` char(32) NOT NULL,
  `source_id` int(10) unsigned NOT NULL,
  `link` varchar(255) NOT NULL,
  `short_link` varchar(255) NOT NULL,
  `cat_id` mediumint(5) NOT NULL,
  `title` mediumtext NOT NULL,
  `description` mediumtext,
  `content` mediumtext,
  `images` mediumtext,
  `videos` mediumtext,
  `views` int(10) unsigned NOT NULL,
  `comments` int(11) DEFAULT '0',
  `comments_update` int(11) NOT NULL DEFAULT '0',
  `editor_id` int(11) NOT NULL DEFAULT '0',
  `auther_name` varchar(255) DEFAULT NULL,
  `createdby_id` int(10) NOT NULL,
  `createdon` int(20) NOT NULL,
  `editedby_id` int(10) NOT NULL,
  `editedon` int(20) NOT NULL,
  `deleted` tinyint(1) NOT NULL,
  `deletedon` int(20) NOT NULL,
  `deletedby_id` int(10) NOT NULL,
  `deletedfor` varchar(255) NOT NULL,
  `published` tinyint(1) NOT NULL DEFAULT '1',
  `publishedon` int(20) NOT NULL,
  `publishedby_id` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8956 ;

I cannot make any changes in fields types.

Is there any way to make it fast?
It takes to execute 0.7518 sec,0.8776 sec.

This should help significantly (unless everything is cat 64):

INDEX(cat_id, deleted, published)

If 'they' won't add that index (or let you add it), warm up your resume.


postgresql copy comand with date in filename I have a query and want the output in a .csv file with today's date and time in the name (I am using PostgreSQL v12).
COPY
(SELECT * FROM mytable)
TO 'C:\Users\me\mylocation\filename_date_time.csv' CSV HEADER

where date_time is YYYY-MM-DD-h_m_s (or similar)
I have seen some solutions to this using bash. For various reasons I am on a Windows machine with no bash.
I would like to do this using psql please.

You have to use dynamic SQL, for example with a DO statement:
DO
$$DECLARE
   datestr text := to_char(current_timestamp, 'YYYY-MM-DD-HH24_MI_SS');
BEGIN
   EXECUTE format(
              'COPY '
              '(SELECT * FROM mytable) '
              'TO %L CSV HEADER',
              'C:\Users\me\mylocation\' || datestr || '.csv'
           );
END;$$;


How can we execute a log file from pg_xlogs directory postgresql? Is it possible to execute a Wal log that is copied from a machines pg_xlogs directory and all the queries inside it are executed on another machine? What command might be used to carry out this action (if there is any command)?

No, it doesn't work like that.

The transaction logs don't contain queries. They contain the changes to disk blocks - heap and indexes - along with records of transaction ID allocations, commits, etc. It's all very low level, and it makes absolutely no sense to try to apply it to another unrelated PostgreSQL instance.

You can use the transaction logs (WAL) for replication, per the PostgreSQL manual.

PostgreSQL also has the beginnings of logical replication functionality. Currently that's mainly accessible via 3rd party tools like pglogical. There are trigger-based alternatives like Londiste too.