SQL Query optimization - Long running query I have a database with the following tables :

CREATE TABLE [Document](
    [IdDocument] [int] NOT NULL,
    [DocumentType] [int] NOT NULL
    CONSTRAINT PK_Document PRIMARY KEY CLUSTERED (IdDocument)
)

CREATE TABLE [ExternalKey](
    [IdExternalKey] [int] NOT NULL,
    [RefDocument] [int] NOT NULL,
    [EntityType] [int] NOT NULL,
    [Value] [int] NOT NULL,
    CONSTRAINT PK_ExternalKey PRIMARY KEY CLUSTERED (IdExternalKey)
    CONSTRAINT FK_RefDocument FOREIGN KEY (RefDocument) REFERENCES Document(IdDocument),
    CONSTRAINT UC_ExternalKey UNIQUE (RefDocument, EntityType, Value)
)

Documents are mapped to physical files on a drive. Each document has a type (eg : 2 = IDENTITY_CARD)
Those documents are linked with external entities with a one to many relation.
Each external key has an entity type (eg : 50 = PERSON) and a ID (eg : 213235)

Database is quite big : Document is 10M records. ExternalKey 40M.

Example of data inside ExternalKey:

IdExternalKey  RefDocument EntityType  Value
1              1           50          3421
2              1           50          9524
3              1           60          7893
4              2           50          1752
5              2           50          8979

I want to filter documents based on their type and the entities they are linked with (simplified query) :

WHERE (DocumentType IN (10, 20, ...)
       AND ExternalKeys ANY (EntityType = 50 AND Value IN (4,5,6,7,8,9,...)))
   OR (DocumentType IN (80, 90, ...)
       AND ExternalKeys ANY (EntityType = 60 AND Value IN (110,120,130,...)))

The best i could come up is this:

SELECT TOP 50 IdDocument
FROM Document d
WHERE
    (d.DocumentType IN (SELECT Id FROM @listA1) AND d.IdDocument IN (SELECT ex.RefDocument FROM ExternalKey ex WHERE ex.EntityType = 60 AND ex.Value IN (SELECT Id FROM @listB1))) OR
    (d.DocumentType IN (SELECT Id FROM @listA2) AND d.IdDocument IN (SELECT ex.RefDocument FROM ExternalKey ex WHERE ex.EntityType = 61 AND ex.Value IN (SELECT Id FROM @listB2))) OR
    ...
    (d.DocumentType IN (SELECT Id FROM @listA3) AND d.IdDocument IN (SELECT ex.RefDocument FROM ExternalKey ex WHERE ex.EntityType = 59 AND ex.Value IN (SELECT Id FROM @listB3)))
ORDER BY IdDocument

Table value parameters contains a lot of IDs (about 100K in total)
Table definition is:

CREATE TYPE int_list_type AS TABLE(Id int NOT NULL PRIMARY KEY)

The query is slow and performance vary a lot depending the sort (from a dozen of seconds to several minutes). For example, sorting documents on IdDocument is way faster than sorting them on DocumentType.
What I have tried so far:

Creating indexes on RefDocument, EntityType, Value (mulitple column index). I have tried different column orders.
Replacing the table value parameters (eg : @listA1) by hardcoded values in the query : parsing time explode (as expected).
Replacing IN with EXISTS : performance is similar.  Replace IN with JOIN : a  lot slower

Is there some more efficient ways to perform the filter, any tips ?

Here is the query plan for 2 OR clauses (each OR clause is in RED)

EDITED after question got more details.
I get a Clustered Index Scan and an Index Scan. But I don't have a lot of rows in my table.

CREATE TABLE [Document](
[IdDocument] [int] NOT NULL,
[DocumentType] [int] NOT NULL,
CONSTRAINT PK_Document PRIMARY KEY CLUSTERED (IdDocument)
)

CREATE TABLE [ExternalKey](
[IdExternalKey] [int] NOT NULL,
[RefDocument] [int] NOT NULL,
[EntityType] [int] NOT NULL,
[Value] [int] NOT NULL,
CONSTRAINT PK_ExternalKey PRIMARY KEY CLUSTERED (IdExternalKey),
CONSTRAINT FK_RefDocument FOREIGN KEY (RefDocument) REFERENCES Document(IdDocument),
CONSTRAINT UC_ExternalKey UNIQUE (RefDocument, EntityType, Value)
)

-- Insert a few values
DECLARE @IdDocument INT, @DocumentType INT, @IdExternalKey INT;
Set @IdDocument=0;
SET @DocumentType = 10;
select @IdExternalKey=MAX(IdExternalKey)+1 from ExternalKey ;

set nocount on
WHILE (@IdDocument < 10000)
BEGIN
    INSERT INTO Document(IdDocument, DocumentType) VALUES (@IdDocument, @DocumentType);
    INSERT INTO [ExternalKey](IdExternalKey,RefDocument,EntityType,Value) VALUES (@IdExternalKey, @IdDocument, 50, 3421);
    INSERT INTO [ExternalKey](IdExternalKey,RefDocument,EntityType,Value) VALUES (@IdExternalKey+1, @IdDocument, 50, 9524);
    INSERT INTO [ExternalKey](IdExternalKey,RefDocument,EntityType,Value) VALUES (@IdExternalKey+2, @IdDocument, 60, 7893);
    INSERT INTO [ExternalKey](IdExternalKey,RefDocument,EntityType,Value) VALUES (@IdExternalKey+3, @IdDocument, 50, 1752);
    INSERT INTO [ExternalKey](IdExternalKey,RefDocument,EntityType,Value) VALUES (@IdExternalKey+4, @IdDocument, 50, 8979);
    INSERT INTO [ExternalKey](IdExternalKey,RefDocument,EntityType,Value) VALUES (@IdExternalKey+5, @IdDocument, 60, 7822);

    SET @DocumentType=CASE WHEN @DocumentType > 100 THEN 10 ELSE @DocumentType + 10 END;
    SET @IdDocument = @IdDocument + 1;
    SET @IdExternalKey = @IdExternalKey+ 6;
END

SELECT TOP 50 IdDocument
FROM Document d
JOIN ExternalKey e on e.RefDocument=d.IdDocument
WHERE
(d.DocumentType IN (10,20,30) AND e.EntityType = 60 AND e.Value IN (4,5,6,7,8)) OR
(d.DocumentType IN (50) AND e.EntityType = 61 AND e.Value IN (110,120,130) OR
(d.DocumentType IN (80,90) AND e.EntityType = 59 AND e.Value IN (1,2,3)))
ORDER BY IdDocument

I would add that if the ORDER BY is not necessary and can be done in the program instead, it can help accelerate the query.

What you really want to do to improve your query would be to fix the "Index Scan" of the ExternalKey table or the scan of the Document table.
For example, since the ExternalKey is unique by RefDocument, entityType, Value, maybe consider using it as clustered index?

The thing is right now, with the keys and indexes, SQL Server has no choice but to scan the whole table to get the rows you need. So if you have a million row, it has to scan every row even if you only get 10 as a result.


Does altering a table and adding multiple columns rebuild the table multiple times I am using MySql 5.6, and have a requirement to add multiple columns to an existing huge table (more than a million rows).

I could use:
Alter table T add column C1 varchar(255);
 Alter table T add column C2 varchar(255);
 Alter table T add column C3 varchar(255);
 Alter table T modify column C varchar(255); -- making it nullable

Or

Alter table T
   add column C1 varchar(255),
   add column C2 varchar(255),
   add column C3 varchar(255),
   modify column C varchar(255); -- making it nullable

As per this page in the MySql docs, these operations will require a table rebuild.

Questions:

Will there be any difference between the above two approaches, in terms of how expensive the operation will be?
What are the best practices when performing such operations?
What does a table rebuild, really mean?
Is there a way to avoid a table rebuild, or make the operation faster: (ALGORITHM=INPLACE) ?

Will there be any difference between the above two approaches, in terms of how expensive the operation will be?

No in practice. Because in the second approach the operations execution is one-by-one, not parallel. Cause of this is simple - in theory the next step can be dependent by previous one and so impossible until next step fully performed.

  What are the best practices when performing such operations?

Create new table + copy data + (lock source table + copy data changes in source made by another clients while first copy) + drop old + rename new + restore access rights, FKs and triggers.

  What does a table rebuild, really mean?

In practice this procedure is performed as described in answer 2 + exclusive lock on the table modified.

  Is there a way to avoid a table rebuild, or make the operation faster: (ALGORITHM=INPLACE) ?

Add separate table with new columns linked to original table as 1:1 instead.


SQL Server certificate for backups: Does expiration date matter? If I have a cert in SQL Server, used for backups:

Can backups still be created after cert expires?
Can restore still happen after cert expires?

What is the point of having these certs expire? (other than to expose me to backups failing due to expired cert...)

I have tested this on SQL Server 2016 (Developer Edition, though that shouldn't matter), and can confirm:

No, after the Certificate expires, using it to encrypt a backup will cause the following error:

  Msg 3096, Level 16, State 1, Line xx
  The Certificate specified for backup encryption has expired.
  Msg 3013, Level 16, State 1, Line xx
  BACKUP DATABASE is terminating abnormally.

Yes, after a Certificate expires, backups encrypted from that Certificate can still be restored. BUT, you still need to have the Certificate loaded into master in order to do the RESTORE.

Just create a new Certificate, and be sure to specify the EXPIRY_DATE = 'utc_date' clause, else the expiration date will default to 1 year from the moment it was created.

As to why Certificates have expiration dates, that is probably to give more control over the security mechanism, similar to how you can set a password policy for Logins to expire. Certificates can be backed-up and used in multiple places, so it can help ensure that you don't have a piece of information sitting out there, somewhere, forever that can be used to compromise your system.


Setup new Window Server Failover SQL Cluster using an existing database I see lots of resource but all of them are instructions on how to setup SQL fail over cluster (not availability group) from scratch using empty database instance.

I have a single non cluster database running now and I would like to convert it into HA fail over by adding another node/vm.   I'm not 100% sure but some Googled resources say you cannot convert a non-clustered SQL Server instance into a clustered instance.

What would be the best method or approach in my case to go about converting my existing Database Instance into HA using FCI (not Availability Group) ?

Build a new cluster with an FCI and migrate the database using backup/restore.  If you are not using VMs and need to reuse the existing server, then create a new 1-node cluster with an FCI, migrate the database, then rebuild the existing server, add it to the cluster and expand the FCI onto the second node.


Mongodb: Possible to enlarge a capped collection? If a collection is created as capped, is it possible to increase the cap without losing data?

Or to increase the cap without recreating the whole collection?

Thanks

No, you can't alter a capped collection size to make it larger (or smaller for that matter) without dropping it and recreating it - it is created with a fixed size.  However you could use mongodump to dump out the existing data, then drop the collection, recreate it with the larger size and then restore the dumped data.

Assuming you are making it bigger, all the data will fit in the new collection. Unfortunately, this will mean that you will need to stop writing to that collection for the duration of this work, there is no way to do it without taking some down time for the collection in question.

There is an existing feature request to add resizing capabilities to MongoDB, but as of writing this it is not yet scheduled. See here for voting and watching purposes:

https://jira.mongodb.org/browse/SERVER-1864


linked servers between availability group to a server I had three servers(server1, server2, server3) in availability group. And the listener name is "Production". I have a server name "Acceptance". I need to run a script that which will copy the tables from acceptance to production. Unfortunately, due to some reasons the only option left with me is running the script by linking the acceptance server with production.

1.How can I link the "acceptance" with "Production"?

2.Is it same as just linking 2 servers or is there anything different to link a server with the listener of the availability group?

3.If we copy the tables by linking server with listener, is these tables gonna save in all the three servers which are in availability group?

You will need to create an identical linked server on each of your 3 Availability Group nodes (server1, server2, server3). Linked servers (like logins, alerts, and jobs) are instance level objects so do not sync between the Availability Group nodes. You need one on each node in case of a failover, and you need to keep them in sync manually if they ever change.

Once you import the data, referencing the linked server in your script on your primary node (Production listener) and preferably with a synonym,  it will sync to the secondary node DBs in the Availability Group automatically. Synonyms are a Best Practice when using linked servers. If your linked server name changes you just update the linked server, not the code that imports the data.


MySQL - What to store in orders or product_order I am building my first e-commerce website and this is my current (relevant) database schema.

I am very confused about what to store in the orders table or in the pivot table (order_product).

I read that you should store a copy of the single_price in the pivot table because the price may change at a later date. If this is correct should i also store copies of product-details in my order_product table ? Because those values may change like the price? . If a user changes their Shipping or Billing Address it should change for the next orders. This should work with the current setup or will it not?

Thanks in advance

Edit

My table names follow a naming convention from Laravel (5.2). This is the reason why my joining tables (pivot tables) have these names. The convention states that pivot tables should be the singular table names in alphabetical order.

In the Orders (Or Order_Header) table, you should store the Orders header - i.e. Order_ID, Client_ID (FK to Client table), Order_Date... &c.

You may want to have a trigger to update an Order_Total field for the total charge/cost. This is not strictly necessary, since you can query that from the Order_Detail table (see below).

A total may be useful if you apply discounts to an entire order (10% above sum X, say, or between dates or ad-hoc), rather than to individual products.

Order_product (I think that Order_Detail is a better, more descriptive, name) should be a joining/linking table between Orders and Product with fields Order_ID (FK), Product_ID (FK), quantity, price, tax, discount, charged_price per item and total for that item in that order.

To answer your question, the individual Product price will remain the price applying at Order_Date and not change subsequently (unless you apply a discount). You can't later refer back to the Product table for price since, as you say, it may change and customers don't like surprises (unless they're discounts :-) )

The Product_price (for a given order) is only valid at the time of that order and not subsequently, therefore it becomes "fixed" at that date. This is why, when calculating Order_total, you either refer to the Order_total (in the Orders table) or the Product_price (in the Order_detail table).

The Product_price field is useful at the date of the order or possibly for providing a sum of the value of current stock for example.

You might like to take a look here (search for "e-Commerce" and "shop" - eCommerce is just commerce after all - and "customers"). I've found this site helpful in the past as a starter.

Finally, for your schema naming, choose singular or plural table names (my preference is singular - with the exception of the word "order", an SQL keyword), but choose one and stick to it.

A final word of advice, if you haven't gone far down the road into this project, I would strongly urge you to consider using PostgreSQL instead of MySQL - it is a vastly superior database product and you (and your customers) will be thankful in the long run.

Best of luck with your project.


Postgresql 9.1 replication delay I just set up a PostgreSQL 9.1.2 database server on debian squeeze.
I configured a standby server (hot standby) on a second identical server (squeeze, PG 9.1.2).

On the master I set these parameters (postgresql.conf) :

wal_level = hot_standby
archive_mode = on
archive_command = 'test ! -f /mnt/pg_replication/myserver/%f && cp %p /mnt/pg_replication/myserver/%f'
archive_timeout = 30
max_wal_senders = 3
wal_keep_segments = 100

On the slave, I set the following parameters (postgresql.conf) :

hot_standby = on

On the slave (recovery.conf) :

standby_mode = 'on'
primary_conninfo = 'host=master_ip port=5432 user=repluser password=xxxx'
restore_command = 'pg_standby -d -s 2 -r 30 -t /tmp/pgsql.trigger.5442 /mnt/pg_replication/myserver/ %f %p %r 2>>/var/log/standby.log'
recovery_end_command = 'rm -f /tmp/pgsql.trigger.5442'
archive_cleanup_command = '/usr/lib/postgresql/9.1/bin/pg_archivecleanup /mnt/pg_replication/myserver/ %r'

This works well but I noticed that the replication delay can take up do 1 minute.
Is there a way to reduce the delay for changes to apply on the standby to a few seconds ? I reduced archive_timeout to 30 seconds but I don't know if this is the right way to make the replication faster...

Another question : how can I monitor the replication delay between the two servers ? I tried the following query on the master but it returns 0 rows... :-?

postgres=# SELECT * from pg_stat_replication;
(Aucune ligne)

Did I missed something ?

Thank you,
Nicolas

EDIT 2012-01-03 : I changed my recovery.conf to this and it is now working well :

restore_command = 'cp /mnt/pg_replication/myserver/%f %p'
standby_mode = on
primary_conninfo = 'host=master_ip port=5432 user=repluser password=xxxx
application_name=mycluster'
trigger_file = '/tmp/trig_mycluster'

The fact that you have no lines in pg_stat_replication on the master indicates that your streaming replication is not working - and it has instead fallen back on the file based replication, which has more delay.

Check your logs on both master and slave, they should tell you why it's not starting up in streaming mode.

Also, your recovery.conf file is incorrect - you should not be using pg_standby together with standby_mode. That's a leftover from pre-9.0 days.


SQL Dependent Percentage Sample data

Excepted output

Math Operation

Sum all Period time for each EmpName as Total Employee Time
Sum Similar Tasks For EmpName, divide it by Total Employee Time and multiply by 100%

please help me

I am using SQL Server Management 2008 and here is my try:

select EmpName,Task,Period_Time,
sum(Period_Time)/(select sum(Period_Time) as total from dbo.TableName)
as percentage
from  dbo.TableName
group by EmpName,Task,Period_Time
order by EmpName

You were close with your approach. The two issues with it that I can see are:

The subquery is not correlated with the outer query. Your subquery needs to have a WHERE clause to limit the rows only to those where EmpName is the same as the outer query's EmpName.

In order to be able to reference the outer query's columns, you need to assign a different alias to either instance of dbo.TableName. It would probably be best to assign one to each – something like this:

SELECT
  ...
  (SELECT ... FROM dbo.TableName AS b WHERE b.EmpName = a.EmpName)
FROM
  dbo.TableName AS a
...

The main query does not need to group by Period_Time, because it is aggregating that value.

Just remove the Period_Time column from both the GROUP BY and SELECT.

So, this is how your query could look like after addressing both issues:

SELECT
  EmpName,
  Task,
  Percentage  = SUM(Period_Time) * 100.0
              / (
                  SELECT
                    SUM(b.Period_Time)
                  FROM
                    dbo.TableName AS b
                  WHERE
                    b.EmpName = a.EmpName
                )
FROM
  dbo.TableName AS a
GROUP BY
  EmpName,
  Task
ORDER BY
  EmpName
;

However, you could do better than using a correlated subquery here. Since SQL Server 2005, Transact-SQL has started supporting window aggregate functions. They allow you to obtain aggregate results alongside detail results and use both in various calculations – exactly what you need for your problem.

Basically, the syntax would go like this:

SELECT
  SomeKey,
  DetailData,
  TotalData = SUM(DetailData) OVER (PARTITION BY SomeKey),
  -- or you can get percentage directly, like this:
  -- Percentage = DetailData * 100.0 / SUM(DetailData) OVER (PARTITION BY SomeKey)
FROM
  dbo.SomeTable
;

There is only one issue: you cannot calculate the total Period_Time like this:

SUM(Period_Time) OVER (PARTITION BY EmpName)

because in your case Period_Time would be an invalid reference: that column is not in GROUP BY and may not be referenced without being enclosed in a (non-window) aggregate function. Your main query already aggregates it using SUM – that is how it can be used in SUM ... OVER as well:

SUM(SUM(Period_Time)) OVER (PARTITION BY EmpName)

For reference, here is an example of a complete query that calculates percentage using window aggregation instead of a correlated subquery:

SELECT
  EmpName,
  Task,
  Percentage  = SUM(Period_Time) * 100.0 / SUM(SUM(Period_Time)) OVER (PARTITION BY EmpName)
FROM
  dbo.TableName
GROUP BY
  EmpName,
  Task
ORDER BY
  EmpName
;


Table size unaffected after deleting 400k rows When I ran the below query

select bytes/1024/1024,segment_name,owner from dba_segments where tablespace_name='DATA_L1' and segment_name='Step_Status';

I got the below result for a segment which is a table named 'Step_Status'

BYTES/1024/1024         SEGMENT_NAME                OWNER
-----------------------------------------------------------------
 3192                    STEP_STATUS                FLSTD04

It had about 18 million entries

select count(*) from flstd04.step_status;

  COUNT(*)
----------
  18178090

After deleting nearly 4 million enteries

select count(*) from flstd04.step_status;

  COUNT(*)
----------
  14114186

When I again checked the size of the table using dba_segments
I got the same result

select bytes/1024/1024,segment_name,owner from dba_segments where tablespace_name='DATA_L1' and segment_name='Step_Status';

  BYTES/1024/1024         SEGMENT_NAME                OWNER
    -----------------------------------------------------------------
   3192                    STEP_STATUS                FLSTD04

Why is it that even after deleting 4 million rows the size of the table is same?

Deleting data isn't expected to change the size of the table segment.  It will create free space in blocks that are part of the table segment and it will probably create at least a few empty blocks.  That free space will be made available for subsequent insert operations on the table (assuming you're doing conventional-path inserts) as well as update operations that cause the size of rows to increase.

If you want to actually shrink the size of the table segment (generally not a good idea if you're just going to insert more data into this table in the future), depending on the Oracle version, the type of tablespace, and whether you've enabled row movement you can probably shrink the segment

ALTER TABLE step_status
  SHRINK SPACE CASCADE;

Be aware, however, that this may be a very costly operation that may involve physically moving most or all of the rows in the table.

There is a chapter in the Database Administrator's Guide on reclaiming wasted space that discusses this and related issues in much more depth.  It also walks through using the Enterprise Manager GUI and the various advisors to reclaim space either manually or automatically.


How to find out which data I just imported into Oracle? I imported two dump files (no idea what they contained, my goal is precisely to find out what they contain) into Oracle, but now I am struggling to identify what is the data I imported, and what data was originally there. On a fresh install of Oracle 11g.

The number of schemas does not seem to have changed, 37. I have browsed around and most of what I see seems to be Oracle's own data (note: I am an Oracle beginner).

To import, I use the first command for the first file, then the second because I was told by imp that DBA was needed for that file:

imp nico/nico file=C:\data\FILE1.DMP
imp system/thepassword file=C:\data\FILE2.DMP full=yes

Does the command line output of imp show the identifiers of all imported data?
Is there a sort of history logs where I can see which tables/rows have been imported recently?

I have spotted one imported table in SYSTEM, but would like to have an exhaustive list.

Using import with show=y won't actually do the import, but will show which tables the import will run against.  There's no practical way, however, to show what rows import will write; it's assumed that you, running the import, would know that.

You may wish to look at some of Oracle's extensive documentation about these utilities to determine what options are available and what they do.


Is there a non-blocking operation to back up MyISAM tables using Percona XtraBackup? I've been reading through the website of Percona XtraBackup and it claims that:

  Percona XtraBackup can back up the following storage engines by briefly pausing writes at the end of the backup: MyISAM, Merge, Archive ...

Is there a flag or option I've missed with the xtrabackup binary to make this functionality happen?

More info...

I installed XtraBackup 2.3.4 onto a CentOS 7.2 server, as well as MySQL 5.6.30. I created a new MyISAM table with approximately 32 million rows and started the backup process with the following:

xtrabackup --user=user --password=password --no-timestamp --compress --backup --target-dir /tmp/my_backup

While the backup was underway I attempted to write a new row to the table, fully expecting it to be inserted into the table as this is what the website implies will happen. However, it was not. During the entire backup process the MyISAM table is locked.

I understand why the table is locked, hence why I was quite excited to read that XtraBackup could perform a backup without any significant write pauses until the end. I just don't understand how to make this functionality happen.

No - the MyISAM architecture prohibits hot backups - it uses a locking mechanism. You might be interested to my response in a related thread.

Take a mysqldump of your database, change the bit where it says ENGINE=MyISAM to ENGINE=InnoDB (InnoDB uses a MVCC architecture) and then restore - XtraBackup will then perform hot backups. It's relatively easy to use sed to do the edit that I have suggested

mysqldump [options] | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/g' > my_schema.sql

Then restore your dumpfile in the normal way.

MyISAM is going to be deprecated in the near future anyway! [EDIT - added 19/04/2018] - the system tables in MySQL 8 are now InnoDB!

In reply to the OP's question about the Percona's XtraBackup page being a "complete fabrication" -  the quote is "can back up the following storage engines by briefly pausing writes at the end of the backup".

I think that the keyword on that page is  "briefly"! :-) Take a look here - Bill Karwin is a big hitter on this forum.

Also, take a look at what Shlomi Noach says about MyISAM hot backups (or lack of!) - he's also a big hitter in the MySQL world. The file system snapshot to which he refers is mylvmbackup - which appears to have a reasonably good rep. here.


Can I automate the "on" statement from MySQL queries? Background

In the process of QA/QC, I am writing lots of queries with multiple joins. I am using MySQL.

I would like to know how (if) I can simplify join statements, e.g. by setting default fields to join on.

My tables are named with a plural form of the object that they contain:

 names
 types
 actions
 surnames
 names_surnames

where names_surnames is a many-many lookup table

the primary keys are always id and the foreign keys are always, e.g.

 names.type_id
 names.action_id
 names_surnames.name_id
 names_surnames.surname_id

My joins would be something like

select names.col1
from names join types on names.type_id = type.id
           join actions on names.action_id = actions.id;

Question

Is there a way to get MySQL to always assume that tables will be joined with on thistables.thattable_id = thattable.id?

If the columns were named the same in "both" tables, I think you could use NATURAL JOIN. Although that won't work for you, your consistency in naming should still let you generate queries--or at least the join clauses--with a scripting language.


MySQL using trigger instead of check Is there any way that I could use trigger instead of check? like I have

  CREATE
      /*!50017 DEFINER = 'root'@'localhost' */
      TRIGGER test BEFORE UPDATE ON apply
      FOR EACH ROW BEGIN      IF NEW.cname = "hi" THEN
              SET NEW.cname = "hello";
          ELSE
              RETURN 1;
          END IF;
      END; $$

but return 1 doesn't work. Is there anyway that I can prevent the query from executing?

You do not need a RETURN 1 because Trigger is a Stored Procedure, not a Stored Function.

If you want to break it on purpose, that's acceptable.

I wrote two posts about how to break a trigger midstream

Trigger in MySQL to prevent insertion (Apr 25, 2011)
check constraint does not work? (Dec 23, 2011)

Try this:

CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `test`
BEFORE UPDATE ON `apply`
FOR EACH ROW
BEGIN
    DECLARE dummy INT;

    IF NEW.cname = "hi" THEN
        SET NEW.cname = "hello";
    ELSE
        SELECT no_such_column INTO dummy
        FROM information_schema.no_such_table;
    END IF;

END; $$


Cluster communication - SQL Server Failover Cluster I am running a lab enviornment on my laptop two node SQL 2008 R2  running on
windows 2008 R2 clustered Vms

Based on my understanding

a) Public Network or all Communication network

I see checkbox is enabled for cluster communication on this network also.

b) Private or HeartBeat or Cluster communication only network.

It appears to me that currently public network is used for cluster communication?

Is this correct? Does heartbeat network is used only for redundancy and cluster communication is primarily done on public network?

The issue is not the version of SQL but the version of Windows you're working with. Win 2008 changed things up quite a bit, private heartbeat network is no longer required.

Pre Win 2008 cluster reqs.


Is it normal to use many Triggers? I have many so-called summary columns throughout my database to count number of rows in other tables. For example, counting the number of comments by a user (a column in the user table). Currently, I update this column by an extra query:

UPDATE user_table SET comments = comments + 1 WHERE user_id='x'

Now I want to introduce Triggers to my database instead of running the second query. Two Triggers for each column: one DELETE and one INSERT. Then, I will have 30 - 40 Triggers!!!

Benchmarks show that using Triggers is about 50% faster than executing a second query. Apparently, it is reasonable to use the modern features of mysql. However, the reason that I am asking this question is that many programmers are strongly against Triggers, as it makes coding vague. Moreover, adding lots of Triggers may make Information_Schema messy.

Am I on the right track?

The major problems with triggers are:

a) They are difficult to debug

b) Trigger cascades can be fatal to your database - take for example if you have an after update trigger in one table which updates another table, which also has an after update trigger, then you will run into lots of issues

A few questions:

a) How often are the "computed" summaries used within your application?

b) How often are the "computed" summaries required to be updated?

c) Would you consider creating a "reporting" table which is refreshed on a regular basis with the major "computations" that you need?


Cannot Add "Active Directory - Password" as Linked Server into my SSMS I'm trying to add Azure Database with Active Directory - Password as Linked Server, but its giving error while login.
I'm using Domain account (for e.g. abc@my-domain.com and password)
These credentials are working and I'm able to connect normally BUT NOT WORKING IN LINKED SERVER
EDIT:
Adding the screenshots.

ERROR DETAILS BELOW:

===================================
Create failed for LinkedServer ''.  (SqlManagerUI)
------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17213.0+((SSMS_Rel).171128-2020)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+LinkedServer&LinkId=20476
------------------------------ Program Location:
at
Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerPropertiesData.ApplyChanges()
at
Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerProperties.DoPreProcessExecution(RunType
runType, ExecutionMode& executionResult)    at
Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType
runType)    at
Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo
executionInfo, ExecutionMode& executionResult)    at
Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo
executionInfo, ExecutionMode& executionResult)    at
Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType
runType, Object sender)
===================================
An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------ Program Location:
at
Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType, Boolean retry)    at
Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection
sqlCommands, ExecutionTypes executionType, Boolean retry)    at
Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection
queries, Boolean retry)    at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection
queries, Boolean includeDbContext, Boolean executeForAlter)    at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplFinish(StringCollection
createQuery, ScriptingPreferences sp)    at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
===================================
Name cannot be NULL. (.Net SqlClient Data Provider)
------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.1000&EvtSrc=MSSQLServer&EvtID=15004&LinkId=20476
------------------------------ Server Name: . Error Number: 15004 Severity: 16 State: 1 Procedure: sys.sp_validname Line Number: 17
------------------------------ Program Location:
at
Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction
action, Object execObject, DataSet fillDataSet, Boolean
catchException)    at
Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType, Boolean retry)

First, you need to use the new Microsoft OLEDB Driver for SQL Server, aka "MSOLEDBSQL", not the old Microsoft OLEDB Provider for SQL Server, aka "SQLOLEDB".
The old driver doens't support Azure Active Directory Authentication.
Second you have to set the "Authentication" connection string keyword:

Then when you map the login using AAD credentials, the driver will interpret them correctly, and not try to use SQL Auth.

Or in script form:
USE [master]
EXEC master.dbo.sp_dropserver @server=N'AZURE', @droplogins='droplogins'
GO
EXEC master.dbo.sp_addlinkedserver @server = N'AZURE', @srvproduct=N'', @provider=N'MSOLEDBSQL', @datasrc=N'xxxxxxx.database.windows.net', @provstr=N'Authentication=ActiveDirectoryPassword'

GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'collation compatible', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'AZURE', @locallogin = NULL , @useself = N'False', @rmtuser = N'xxxxxxx@xxxxxxx.onmicrosoft.com', @rmtpassword = N'xxxxxxxxx'
GO
exec ('select 1 a') at AZURE


Getting "Conversion failed when converting the nvarchar value to data type int." I have a SQL Server table pairing zipcodes and lat/long geometry, such as below

ID  ZIP5    geom
1   356HH   0xE610000001044E00....FFFFFFFF0000000003ID
2   35677   0xE6100000010404000000068....000003

The problem I'm having is that I cannot seem to convert from one type to the other due to the ZIP5 field having characters.

What I have tried is an approach wherein I pull only records that are fully integer-friendly:

SELECT
    zip,
    coordinates
FROM (
    SELECT
        CONVERT(int, [ZIP5]) AS zip,
        CONVERT(varchar(max), geom) AS coordinates
    FROM
        [SpatialData].[dbo].[zip5]
    WHERE
        ISNUMERIC([ZIP5]) = 1
) AS t1
WHERE
    zip >= 85000 AND
    zip < 86000

I assumed that since the subquery returned an INT type, that there would be absolutely no problem doing normal comparison operators in the outer WHERE. I get the following error when executing this query:

  Msg 245, Level 16, State 1, Line 1
  Conversion failed when converting the nvarchar value '356HH' to data type int.

Omitting the outer WHERE, I get a full result set with 100% integers in the ZIP5 column.  Using the outer WHERE, it seems to look past the previous CONVERT and fail on conversion.

As a few extra notes:

The nature of this query is OK with not getting the rows that have letters.
It would be perfectly OK as well for the HH (or any other double char pair) to be replaced with numerical zeroes (tried REPLACE and STUFF to no avail, however)
I've tried TRY/CATCH but it does not appear to work when within a WHERE statement --  when more broadly catching around the entire query, the try/catch will end up returning an empty set.

What approach would be the best to address without making a temp table--table variable?

Write your query this way.  It includes a better test for integers and dumps ISNUMERIC that returns 1 for '-.', for example.

SELECT
    zip,
    coordinates
FROM (
    SELECT
        CASE WHEN ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%' THEN
            CONVERT(int, [ZIP5]) END zip,
        CONVERT(varchar(max), geom) AS coordinates
    FROM
        [SpatialData].[dbo].[zip5]
    WHERE
        ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%'
) AS t1
WHERE
    zip >= 85000 AND
    zip < 86000

See this Connect item

SQL Server is free to evaluate the WHERE/SELECT clause in the order that it decides is optimized. A view or derived table is not materialized can easily be expanded into, from the outer query.

What SQL Server is compiling is really a query that looks like this

SELECT
    CONVERT(int, [ZIP5]) AS zip,
    CONVERT(varchar(max), geom) AS coordinates
FROM
    [SpatialData].[dbo].[zip5]
WHERE
    ISNUMERIC([ZIP5]) = 1
    AND CONVERT(int, [ZIP5]) >= 85000
    AND CONVERT(int, [ZIP5]) < 86000

You can inspect the query plan of your original, but my guess from looking at the structure is that the WHERE clause uses the expression CONVERT(int, [ZIP5]) twice, so it makes sense to streamline the resolution (calculation to a result) of the expression in the process of retrieving data from the table.  This puts the processing of the SELECT clause before the WHERE, so your ISNUMERIC() = 1 never got a chance to filter the bad eggs.

It is by-design.


What if all the non clustered indexes on my table were filtered indexes? I have a table that is used heavily. This table has 370,370 records.
When I run the following select:
select  [ImageSize] , the_num = count(*)
from  [dbo].[ProductImages]
group by [ImageSize]
order by [ImageSize]

I get this result:

Basically there are only 5 different imageSize objects in that table.
This is the current table definition:
IF OBJECT_ID('[dbo].[ProductImages]') IS NOT NULL
DROP TABLE [dbo].[ProductImages]
GO

CREATE TABLE [dbo].[ProductImages] (
[ProductImageID]  INT              IDENTITY(1,1)   NOT NULL,
[SeasonItemID]    VARCHAR(5)                       NOT NULL,
[Language]        SMALLINT                         NOT NULL,
[Tier1]           VARCHAR(10)                      NOT NULL,
[Tier2]           VARCHAR(10)                      NOT NULL,
[Gender]          CHAR(1)                          NOT NULL,
[SortOrder]       SMALLINT                         NOT NULL,
[ImageFormat]     VARCHAR(20)                      NOT NULL,
[ImageSize]       VARCHAR(10)                      NOT NULL,
[ImageWidth]      SMALLINT                         NOT NULL,
[ImageHeight]     SMALLINT                         NOT NULL,
[BodenDomainURL]  NVARCHAR(40)                     NOT NULL,
[BodenImageURL]   NVARCHAR(210)                    NOT NULL,
CONSTRAINT   [PK_ProductImages]
PRIMARY KEY NONCLUSTERED ([SeasonItemID] asc,
                          [Language] asc, [Tier1] asc, [Tier2] asc, [Gender] asc,
                          [SortOrder] asc, [ImageFormat] asc,
                          [ImageSize] asc))

GO

And here are the definitions of the current inexes

1 clustered index

4 non clustered  indexes

1 filtered index

and the primary key non clustered defined above
  CREATE CLUSTERED INDEX
  [CIX_ProductImages__ProductImageID]
             ON [dbo].[ProductImages] ([ProductImageID] asc)

  CREATE NONCLUSTERED INDEX
  [IDX_SeasonItemID_Tier1_Tier2_ImageSizeBodenImageURL_INC_BodenDomainURL]
             ON [dbo].[ProductImages]
      ([SeasonItemID] asc, [Tier1] asc, [Tier2] asc,
      [ImageSize] asc, [ImageFormat] asc,
      [BodenImageURL] asc)
             INCLUDE ([BodenDomainURL])

  CREATE NONCLUSTERED INDEX
  [IX_ProductImages_ImageFormat_ImageSize_INCL]
      ON [dbo].[ProductImages]
      ([ImageFormat] asc, [ImageSize] asc)
             INCLUDE ([SeasonItemID], [Language],
       [Tier1], [Tier2], [BodenDomainURL],
      [BodenImageURL])

  CREATE NONCLUSTERED INDEX
  [IX_ProductImages_Language_Tier1_Tier2_ImageFormat_INCL]
      ON [dbo].[ProductImages]
      ([Language] asc, [ImageSize] asc, [Tier1] asc, [Tier2] asc,
      [ImageFormat] asc)
             INCLUDE ([ProductImageID], [BodenImageURL])

This is a filtered index:
    CREATE NONCLUSTERED INDEX
    [IDX_ProdImages_GetProductListingPageDenormalisedData]
        ON [dbo].[ProductImages]
        ([SeasonItemID] asc, [Tier1] asc, [Tier2] asc,
        [ImageFormat] asc, [ImageWidth] asc, [ImageHeight] asc)
               INCLUDE ([BodenImageURL])
               WHERE ([ImageWidth]=(195)
        AND [ImageHeight]=(252)
        AND [ImageFormat]='ProductMain')

Can I expect significant gains by changing the 4 non clustered indexes above to 20 FILTERED INDEXES according to the imageSize?
Most queries use the ImageSize as one of the parameters. It is even in the clustered index.
I could modify the indexes and table structure.
This table is mostly used for reading. There is only 1 write a day, and that would not normally affect many records.

Significant gains? No, I shouldn't think so.
SQL Server's indexes are B-Trees. These have a hierarchical nature. To read a row the server starts at the root node of the index then steps through one or more intermediate nodes before reading the row's values via the leaf node. (Details vary between clustered & non-clustered indexes, and heaps.) The speed-limiting factor is often the number of levels there are in the index and, hence, the number of pages (each index node is its own page) that have to be read. This, in turn is controlled by the fan-out, the number of lower-level pages a higher-level page can reference, which is itself determined by the total size of the key. Let's do some hand-wavy maths.
Index IDX_SeasonItemID_Tier1_Tier2_ImageSizeBodenImageURL_INC_BodenDomainURL is defined as
Column          Definition     Effective bytes  Note
------------    -------------  ---------------  -----------------------------------
SeasonItemID    VARCHAR(5)                   5  Use the full size
Tier1           VARCHAR(10)                  5  Assume 50% of defined size
Tier2           VARCHAR(10)                  5  Assume 50% of defined size
ImageSize       VARCHAR(10)                  7  Minimum from given data
ImageFormat     VARCHAR(20)                 10  Assume 50% of defined size
BodenImageURL   NVARCHAR(210)              210  Assume 50% of defined size, 2 bytes per character.
                                          ====
Total                                      242 bytes

I'll ignore the included column for fan-out since it's only present in the leaf nodes. Since there's about 8060 usable bytes per page that gives roughly 8060 / 242 = 33 rows per page. So a one-level index can reference up to 33 rows, a two-level index can reference 33 * 33 = 1,089 rows, a three-level 35,937 rows, and a four-level 1,185,921 rows. Given your counts of 74,000 rows per picture size and 370,000 rows in total you'll need a four-level index1 for both filtered and non-filtered indexes. There will be no reduction in the number of pages referenced and so no improvement in run-time.
These numbers are a lower limit as the included columns and the column of the clustered index will take further space in the leaf pages of the non-clustered index.
Adding these indexes will introduce risks, however. One is that the optimizer can be finicky about when it uses them. Another is that bringing on a new size (a data change) will require five more indexes to be defined (a DDL change). This is unwelcome maintenance overhead but without performing it the new-sized images will, in effect, be un-indexed. Schema changes will be complicated by the additional objects. Index maintenance will be slightly more complicated, if scripted manually. The overhead of the upper level of the indexes will increase the DB size fractionally, and with it backup and restore times.

1For the value used by an actual index see sys.dm_db_index_physical_stats.index_depth (docs).


map non unique attribute to a unique attribute I have a mapping table that uses a non unique attribute.  I need a way to map that attribute to one of the attributes it is referring too, to get the unique attribute.  Here is an example of what i am trying to do:

DECLARE @TABLE1  TABLE (id int, table1_name varchar(max))
DECLARE @TABLE2  TABLE (id int, table2_name varchar(max))
DECLARE @TABLE1_TABLE2 TABLE (table1_name varchar(max), table2_name varchar(max))

insert into @TABLE1 (id, table1_name) values (1, 'table1_name1')
insert into @TABLE1 (id, table1_name) values (2, 'table1_name2')
insert into @TABLE1 (id, table1_name) values (3, 'table1_name3')

insert into @TABLE2 (id, table2_name) values (1, 'table2_name1')
insert into @TABLE2 (id, table2_name) values (2, 'table2_name2')
insert into @TABLE2 (id, table2_name) values (3, 'table2_name2')

insert into @TABLE1_TABLE2 (table1_name, table2_name) values ('table1_name1',
    'table2_name1')
insert into @TABLE1_TABLE2 (table1_name, table2_name) values ('table1_name2',
    'table2_name2')
insert into @TABLE1_TABLE2 (table1_name, table2_name) values ('table1_name3',
    'table2_name2')

select t1.id as t1id,min(t2.id) as t2id
    from @TABLE1_TABLE2 tt
    inner join @TABLE1 t1 on tt.table1_name = t1.table1_name
    inner join @TABLE2 t2 on tt.table2_name = t2.table2_name
    GROUP BY t1.id

This returns:

1  1
2  2
3  2

I need it to be either

1  1
2  2
3  3

or

1  1
2  3
3  2

I was thinking my best bet would be to write an aggregate function, but CLR is not an option right now.  Rebuilding the relationship is not an option either, as we are stealing it from another database so the pk doesn't map, only the name.
The only thing that is working is doing it in a cursor, which unsurprisingly is slow enough that I have to get it set-wise.

EDIT:

If there are multiple entries of TABLE1, then this will breakdown.  For instance if we use this for TABLE1_TABLE2 instead (notice that we had to keep the old, no longer valid pk):

insert into @TABLE1_TABLE2 (oldid, table1_name, table2_name) values (123451234,
    'table1_name1','table2_name1')
insert into @TABLE1_TABLE2 (oldid, table1_name, table2_name) values (4563456,
    'table1_name1','table2_name2')

insert into @TABLE1_TABLE2 (oldid, table1_name, table2_name) values
    (3563456,'table1_name2', 'table2_name2')

insert into @TABLE1_TABLE2 (oldid, table1_name, table2_name) values
    (4563456,'table1_name3', 'table2_name2')
insert into @TABLE1_TABLE2 (oldid, table1_name, table2_name) values
    (123451234,'table1_name3', 'table2_name1')

Then to get the query to work, you have to map the old pk to a new one, like so:

 select * from @TABLE1_TABLE2 ga
    inner join ( select oldid, id as newid from (
    select oldid,max(table2_name) as table2_name, row_number() over(partition by
        max(table2_name) order by oldid) as rn from @TABLE1_TABLE2
    GROUP BY oldid ) tmp
inner join ( select table2_name,id, row_number() over(partition by table2_name order by
    id) as rn from @TABLE2) g on tmp.table2_name = g.table2_name and g.rn = tmp.rn )
    ids on ga.oldid = ids.oldid
inner join @TABLE2 g on ids.newid = g.id
inner join @TABLE1 a on ga.table1_name = a.table1_name

Thanks for the help!
Let me know if the edit is off topic now, wanted to share my discovery in case it saves someone some frustration.

This will work with the sample data you have provided here. Your data could be entered into your tables in a way so that this will not work. If that is the case, please update your question with data that will show the issues you have.

;with TT as
(
  select table1_name,
         table2_name,
         row_number() over(partition by table2_name order by (select 1)) as rn
  from @TABLE1_TABLE2
),
T2 as
(
  select id,
         table2_name,
         row_number() over(partition by table2_name order by id) as rn
  from @TABLE2
)
select T1.id as T1ID,
       T2.id as T2ID
from TT
  inner join @TABLE1 as T1
    on TT.table1_name = T1.table1_name
  inner join T2
    on TT.table2_name = T2.table2_name and
       TT.rn = T2.rn


Is this considered a "light load" trace to run on SQL Server Profiler This is my first experience of using SQL Server (2012 Enterprise Edition) Profiler. I've set up a trace to log when views are being accessed on a particular database.

I'm running Profiler on my personal PC, connecting to our Live/Production Server and letting the trace run.

It's only monitoring a single database, and writes events when views are accessed (of which there are approx. 300 potential Views in total).

I have reservations that leaving Profiler to run for long periods of time may affect server performance (especially as I'm querying a Live/Production server).

Is what I've proposed considered a fairly "light load" to be running?

Using Profiler via the UI can cause serious loads on Production systems. What you should bear in mind is that you must always consider using a Server-Side Trace instead of using Profiler's User Interface.

You might think of it as of almost the same. There is very nice article by Gail Shaw that you should definitely read and understand.


Delete relations efficiency in PostgreSQL I've got a collection of duplicate relations of relations that I need to tidy up.

The table might be described like this

posts
id: 1

impressions
id: 1, post_id: 1, created_at: 2020-01-02
id: 2, post_id: 1, created_at: 2020-01-02
id: 3, post_id: 1, created_at: 2020-01-03

demographic_distribution
id: 1, impression_id: 1
id: 2, impression_id: 1
id: 3, impression_id: 2
id: 4, impression_id: 2
id: 5, impression_id: 3

I intend to remove the impression with id 1, but due to foreign key constraints, I would need to remove the related demographic_distributions first.

From demographic_distributions I would want to remove ids 1 & 2.

The following query works, but it's not very efficient. I have around 78 millions rows in the demographic_distribution table, and I have other similar sized related tables.

DELETE FROM demographic_distribution
WHERE impression_id NOT IN (
  SELECT id
  FROM impressions AS i1
  WHERE id IN (
    SELECT MAX(id)
    FROM impressions AS i2
    WHERE i2.post_id = i1.post_id
      AND i2.created_at::date = i1.created_at::date
    GROUP BY i2.post_id
  )
);

You can do all in a single data modifying CTE

with deleted_impressions as (
  delete from impressions i1
  where exists (select *
                from impressions i2
                where i2.id > i1.id
                  and i2.created_at = i1.created_at)
  returning id
), delete_reg_impressions as (
  delete from regional_impressions
  where impression_id in (select id
                        from deleted_impressions)
)
delete from demographic_distribution
where impression_id in (select id
                        from deleted_impressions);

First the duplicates from impressions are removed and all IDs that are returned from that query are then used to delete the corresponding rows in demographic_distribution as this is done in a single (atomic) statement the foreign keys are still valid.


Insert Into Existing Table Using OpenQuery I am attempting to insert data into a linked server using openquery.  I have attempted the below syntax, but get an error of

  Cannot get the column information from OLE DB provider "MSDASQL" for linked server "Aurora".

What is the proper way to write this syntax so that my update statement will execute as it should?

INSERT INTO OPENQUERY(AURORA, 'Select firstname, lastname, address, city, state from testdb.address_info') VALUES
('jo', 'bob', '11111 no where drive', 'city1', 'state1')

Provided that AURORA is a linked server, maybe you could simplify the operation by eliminating the OPENQUERY construct?

INSERT INTO AURORA.testdb.some_schema.address_info (firstname, lastname, address, city, state)
VALUES ('jo', 'bob', '11111 no where drive', 'city1', 'state1');

You might have to set/change some of the connection options on the linked server for this to work properly.

Note that you seem to be missing the schema name or the database name; when you're referencing a linked server, the four-part naming is server.database.schema.object, where you can choose to leave the schema blank to use the default schema (like dbo).


Why does Oracle hang on "ALTER TABLE ADD ... NOT NULL"? Using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0, the operations below have completely stumped me. The first locks up (table has DDL lock, finally killed query after 20+ minute wait). The second does not.

###This hangs:
ALTER TABLE customer ADD primary_seg varchar2(9) NOT NULL;

###This does not:
ALTER TABLE customer ADD primary_seg varchar2(9);
ALTER TABLE customer MODIFY primary_seg NOT NULL;

There are no records in the table, there are no other users than myself on the instance, and I have no other queries executing which might cause a lock.  The table previously had a large number of rows that were removed via TRUNCATE TABLE.   There are no foreign keys or other referential constraints.

My case is similar, my talble has 3 million rows. It hangs about 2 hours and I abort it.

I found  the reason is some other sessions lock this table. After I killed the sessions, It completes adding column in miliseconds.

I don't know why Oracle don't throw "resource busy" when adding column.


Advices for modeling facts, time hierarchies based on two conditions Sorry if my question does not belong to that SE but I tried to find the best place and it ended up here.

My problem is this :

I've got a table modeled as this in our datawarehouse env. :

MYTABLE (service_id, date_input, date_when_active, date_when_inactive, bunch_of_values...)

Each night this table is updated with new records from our prod env. When a record changes in prod and already exist in dw, a new record is created in dw with the same serviceid and the old record gets updated with sysdate in when_inactive field. The newly created record has its when_inactive field to null.

We use a query to extract data from this table using some conditions to get exactly the value of all services in a precise period. The query goes like this :

select *
from mytable
where service_date_input between :p_date_start and :p_date_end
and :p_date_end between date_when_active and nvl(date_when_inactive,:p_date_end)

What I would like to do is to model a time hierarchy in Cognos Framework Manager so I can drill through fiscal years, periods, weeks, months, etc.

What I can't figure out is if I need to remodel the table to include calculated fiscal_year_id, period_id, week_id, month_id, etc. directly in the DB or is it possible to do it directly in Cognos FWM?

One of the requirement would be to not force the use of two date sets on the user (filter by input date AND fiscal year). The input date should be filtered transparently : When you use a fiscal year parameter to filter or display the data, the input date rule should apply accordingly.

You need a date dimension table, and it should look something like this:

create table calendar (
  date_id int primary key,
  name text not null unique,
  date_iso date unique,
  year smallint,
  quarter_of_year smallint,
  month_of_year smallint,
  day_of_year smallint,
  day_of_month smallint,
  fiscal_year smallint,
  quarter_of_fiscal_year smallint,
  month_of_fiscal_year smallint,
  ...

);

and insert data like this:

insert into calendar values
(0, 'N/A', null, ...),
(20130703, '2013-07-03', '2013-07-03', 2013, 3, 7, 184, 3, ...),
(20130704, '2013-07-04', '2013-07-04', 2013, 3, 7, 185, 4, ...);

Notes:

use a smart key (with the date as the key, like 20130703) as the primary key for your date dimension, for easier fact table partitioning. this is the only time you should use a smart key in your warehouse. use meaningless surrogates otherwise.
use a text 'name' field to allow for 'N/A' dates
you should have ONE date dimension table and your fact table date keys should all point to it
you should not include times in your date dimension table. Consider a time dimension table if you need to track times, with say minute granularity.


Cloning a PDB from one CDB into another CDB with TDE enabled at source and target What are the possible ways to Clone a PDB from one CDB into another CDB (in a different host) with TDE enabled at both source and target.

At present i am able to think of the following two

a) Data pump export and import

b) Unplugging in source and plugging into target

Option a) requires creation of tablespaces manually before import at target pdb and is quite slow since the data we have is huge.

Option b) is easy without TDE but with TDE their is a concept of exporting and importing encryption keys , and what ever i have used it till now it becomes quite complex during plugging and importing keys .

Can someone suggest and alternative way to achieve the mentioned requirement or may be a way to do a) or b) with more ease and accuracy .

If you are trying to avoid exportation of encryption keys, your only chance is Data Pump, because that's the whole purpose of TDE.

Yes, you have different ways to clone your PDB, such Data Guard, but you'll need to export the encryption keys too.


Seeking help to test a use case on sql server memory This is a question which came out of curiosity to know of how SQL Server handles memory.

Assume I set a very low value for max server memory and I increase my workload like running dbcc checkdb, rebuild large indexes, select * from really big tables and so on.
Again, this is only my assumption. Since the memory will not sufficient, some part of data/plans resided in RAM will be flushed out of memory.
Now my question is, whether the flushed out data will be placed/pageout to OS pagefile or it spills to tempdb.
Can anybody provide me same use case/test queries to test this behaviour and confirm(DMV query) the "before" and "after" effect to tell,

sql server data has been page out ?
how much data in bytes/KB it has been paged out?
sql server data has been spilled to tempdb ?
how much amount of data has been spilled out?

Basically I am looking for a proper methodology(step-by-step process) to test this behavior. I am getting ideas but was unable to implement properly.

SQL Server does not page, not in the sense the OS pages.

Data does not need to be paged becase the memory is just a cache of the data files. Data can simply be removed from memory and read back when needed.

Caches (eg. Procedure cache) are not paged either. Entries are removed and, if needed, they can be recreated from scratch (eg. compile).

The closest thing to paging are spills. These are operators that need intermediate data that doesn't fit in memory. They spill to tempdb. The typical example is the sort operator.


I need to Select 100 records (or less if there are not 100 records) per group from multiple tables The following query gets me the entire universe of a.transactionId with b.dataItemValue:

SELECT DISTINCT a.transactionId, b.dataItemValue 'Round Type', c.transactionRoundTypeName 'Round Type Name'

FROM TransactionId_tbl a

INNER JOIN TransactionDataInteger_tbl b
ON a.transactionId = b.objectId
AND b.dataItemId = 601257

INNER JOIN TransactionRoundType_tbl c
ON b.dataItemValue = c.transactionRoundTypeId

WHERE b.dataItemValue is NOT NULL
AND b.dataItemValue != 7
ORDER BY b.dataItemValue

I need to limit this query to get only 100 records (or the number of existing records if there are less than 100) of a.transactionId for every record of b.dataItemValue

Thanks!

This should do what you need:

WITH MyCTE as (
    SELECT DISTINCT
        a.transactionId,
        b.dataItemValue,
        c.transactionRoundTypeName,
    FROM TransactionId_tbl a
    INNER JOIN TransactionDataInteger_tbl b
        ON a.transactionId = b.objectId
        AND b.dataItemId = 601257
    INNER JOIN TransactionRoundType_tbl c
        ON b.dataItemValue = c.transactionRoundTypeId
    WHERE b.dataItemValue is NOT NULL
        AND b.dataItemValue != 7
    ) Src
    ORDER BY transactionId,
        dataItemValue
    ),
MyCTE2 as (
    SELECT transactionId,
        dataItemValue,
        transactionRoundTypeName,
        ROW_NUMBER() OVER (GROUP BY transactionId ORDER BY dataItemValue) RowId
    FROM MyCTE
    )
SELECT transactionId,
    dataItemValue as 'Round Type',
    TransactionRoundTypeName as 'Round Type Name'
FROM MyCTE2
WHERE RowId <= 100
ORDER BY TransactionId,
    RowId  -- Proxy for dataItemValue

This gives you up to 100 rows per DISTINCT transactionid and dataItemValue combination, and orders the data by transaction and rowId (proxy for 'Round Type'). If you really WANT it ordered by 'Round Type', change the last ORDER BY to

ORDER BY dataItemValue


Script to generate insert statements I am trying to write an SQL script to create an insert statement for each row of table TEST which has field A (int) and field B varchar(30), in my MS SQL database.

I would like to save those insert statements into a txt file to be able to apply that data back to another database.

Any idea about how to do it?

Right-click on your Database in Object Explorer
Select Tasks -> Generate Scripts...
In that wizard you will be able to select the desired object(s) that you'd like to script out
The next portion has an Advanced button, click that
Under General, you will see the option called Types of data to script.  The options are Schema only, Schema and data, or Data only.  I believe you want the last option (Data Only)


How to determine column character set used on creation? I have this SQL creation statement:
CREATE TABLE `testdb`.`new_table` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `text_default` VARCHAR(45) NULL,
  `text_latin` VARCHAR(45) CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci' NULL,
  PRIMARY KEY (`id`));

where testdb has the default charset latin1.
Later, without knowing the create statement, I want to figure out if a column was defined to have latin1 as character set or it just inherited it from the database.
What I have tried
Export from the database using MySQLWorkbench
The CHARACTER SET instruction is not there.
Change the default character set of the table
ALTER TABLE `testdb`.`new_table` CHARACTER SET utf8;

Both columns text_default and text_latin preserve the latin1 as charset.
Convert the whole table to another charset
ALTER TABLE testdb.new_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
All columns are now utf8.
So basically I found no solution to track back to creation if the column was specifically as latin1 created. Any ideas?

This shows the collation and character set for one table:

SELECT column_name,
       character_set_name,
       collation_name
FROM information_schema.columns
WHERE table_name = 'your_table_name'


Where do caching servers conceptually fit in my architecture? Given the simple well-known architecture below:

Where (conceptually) does the caching server(s) sit?

Your caching layer sits between Model and View Controller. You should not hit database for needless requests. These requests include in my opinion.

Almost all lookup tables. You read city and state list already. Why go to database again.
Facts used in almost every page. if you show User's detail every page. Hit to Database once and cache it.
Slow queries you can afford to show with some delay. For example you can cache How many orders your customer have and show it without going to database.

Note that Caching does not need to be for database only. For example you may cache messages from mail server, or expensive file parsing operation result also.

Caching is the number one layer which increases speed of application.


Coding an accounting database from scratch? My new medical practice is picking up fast and my homemade Libreoffice Calc spreadsheet is quickly becoming cumbersome to track patient visits, accounts receivable, overhead reconciliation, etc.  Most practices use some sort of practice management software to handle accounting as well as scheduling, paying bills, submitting insurance claims, etc. but my budget can't afford to pay for overpriced software.  Besides, I like to do things my own way, as well as support open source software.  (I've already determined that I don't need this function to come from my electronic charts)

Google searching didn't yield any templates and all of the accounting programs found were built for traditional businesses with purchasing, warehousing, human resource management, etc. that aren't adaptable for my needs and are unusable.  You wouldn't think that I would be the first physician to attempt something like this, but the only thing that I could find is a design plan here: http://www.databaseanswers.org/data_models/

So now, I'm considering importing my spreadsheet into Libreoffice Base and slowly developing a custom accounting program for myself.  However, I have no experience with databases and I don't want to spend a large amount of time on this if the amount or complexity of coding is beyond what I am willing to sacrifice from my study time.  After browsing through textbooks at the local B&N, the first 1/3 of most books on MySQL, Filemaker Pro, and Access seem applicable for what is needed (complex mathematical modeling and data analysis is overkill), but I'm still hesitant about heading into this endeavor if the work load is larger than I care to handle.

So, is it doable for someone with remote programming experience (qbasic, visual basic) and basic spreadsheet knowledge to code a simple custom accounting database?  If so, is LibreOffice Base sufficient? What program or textbook would you recommend?

Thank you!

I have used almost all accounting software, currently use and modify Sql-Ledger - and so turning my database client's workflow system into a full, double-entry accounting system was so easy it was almost a relief; client had been using my system to bill their own (news-copy) clients because their internal itemisation of services spoke their own language whereas no accounting package would. Their accounts section then had to go and reproduce it all again externally for book keeping.

Finally the client's accountant and I sat down and exchanged notes. I modified the application within a week. It has never broken, produced a bug, or failed. It does all their accounts and no none else's. It tracks assets, cash flow, everything except payroll - which they didn't need.

Every business of any size knows, you just can't get anything off the shelf that works.

My advice is don't listen to the doom-and-gloom advice of anybody who doesn't understand both database schema and accounting. This combination seems to be just unusual enough to have software vendors rubbing their hands with glee, issuing warnings of dire consequences to would-be developers, spending most of their time organising vendor-lockin components to their software, or arrangements with large accounting franchises. And, like successful snake-oil vendors, they laugh all the way to the bank.


Are parallel queries with mysqlnd actually possible? So I have been playing with asynchronous database requests in PHP using mysqlnd in an attempt to achieve parallel database requests and cut down total execution time i.e. rather than:

total time = query 1 time + query 2 time + query 3 time + query 4 time

instead:

total time = max(query 1 time, query 2 time, query 3 time, query 4 time)

My thinking is that using MYSQLI_ASYNC makes the query non blocking allowing you to kick of a number of queries right after another with a new connection, and as MySQL handles queries in parallel natively, thus achieve execution of parallel database requests.  I am however seeing absolutely no improvement in execution time whatsoever and instead am seeing the same execution time as if the queries are run one after the other.

Where am I going wrong and is this actually possible?  (note I am a developer not a sysadmin)

Recommend you work on speeding up each query. Parallel execution in PHP, especially on Windows, is mostly a myth.

Better evidence


How to check if specific number of rows exists without count(*) I know we can check if at least one row of a table exists using EXISTS keyword, for example:
select * from A where exists(select from B where B.id = A.id)

What if I want to check if at least N rows exist, without using COUNT?
Performance matters to me. I don't want the whole table to be scanned to count matching rows. I just need my query to stop when N rows are found. I mean, the existence of at least N rows is what matters, and not the total count. That could save time if table is large.
Is there such feature in PostgreSQL?

You can add this in the EXISTS subquery:
LIMIT 1 OFFSET (N-1)

In other words, the subqery first tries to finds (N-1) rows, skip them and return the next (Nth) row. So, it will return something (one row) if and only if there are N+ rows.
You need appropriate indexes, i.e. an index on B(id) in this case, to make it as fast as possible, obviously.


Data deleted with sqlplus still showing up in SQL Developer I wrote a script to run with SQLPlus which deletes the previous seven days of data in my Oracle XE database. I logged into the database with SQLPlus, ran the script, and it showed me the correct rows had been deleted. I tried to query the same date range again just to make sure, and the query returned no results (i.e. the records were deleted).
However, when I then connect to the database using Oracle SQL Manager, I can go to the same table and all the data is still there! If I run the query in SQL Manager, it is then deleted from the table in SQL manager.
Anyone know why this is happening? The credentials I use to log in are the same in both instances and I've made sure to be logged out of SQL Manager when running SQLPlus and vice-versa. I'm running version 18c of XE database.

Does your SQLPlus script execute a COMMIT when it finishes the DELETE? If not, then the DELETE transaction hasn't been committed (persisted) yet. From the same connection in SQLPlus it'll look like the rows were deleted, but that's because it's still in the middle of the same active transaction.


Can listen_addresses really be set to a list? I have a VM with IP address 192.168.0.192 running postgreSQL.

If I specify

listen_addresses = '*'

then I can connect from another VM at 192.168.0.191 and from localhost.

But I can't seem to use a list to tell postgreSQL to use those two addresses.  If I change listen_addresses to a list:

listen_addresses = '192.168.0.191, localhost'

then I can no longer connect from 192.168.0.191.

I notice that almost all examples on stackexchange set listen_addresses to '*'.  Is this because the list form does not work?

Yes, listen_addresses can be set to a list of addresses on the local host to bind to for listening.

In your example:

  listen_addresses = '192.168.0.191, localhost'

If the local machine has IP 192.168.0.192, you should specify that IP, not the remote host 192.168.0.191 IP. PostgreSQL cannot bind to the IP address of a remote host.

You're not saying "who is allowed to connect", you're saying "which interfaces should PostgreSQL accept connections on". The "who's allowed to connect" bit is next, and is configured in pg_hba.conf.

So: Try '192.168.0.192, localhost'. Or just *, since you probably actually want to listen on all network interfaces.


How to get all create for index and Constraints in PostgreSQL using just SQL I want a sql to get a list of CREATE of all Index and Constraints in a table 'tablename' just using SQL.

To show all the indexes of a table, use pg_indexes  PostgreSQL List Indexes

SELECT
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    tablename = 'table_name';

Replace <table name> with the name of your table.

Constraints can be retrieved via pg_catalog.pg_constraint. pg_constraint

SELECT con.*
       FROM pg_catalog.pg_constraint con
            INNER JOIN pg_catalog.pg_class rel
                       ON rel.oid = con.conrelid
            INNER JOIN pg_catalog.pg_namespace nsp
                       ON nsp.oid = connamespace
       WHERE nsp.nspname = '<schema name>'
             AND rel.relname = '<table name>';

Replace <schema name> with the name of your schema and <table name> with the name of your table.


Dimension and Fact for same entities? I'm rather new at DW design and am working on a DW to model some IT infrastructure.

The major problem/question at this point is how to model drive information.

We will be collecting aggregate data on files and folders, and separate data on physical drives.  Drive info will include total and free space at a minimum and will be updated several times a week.

One of the business questions that will need to be answered is how drive usage is trending over time.  The drive information will also be used in a hierarchy leading down to the file/folder level as well.

The options that I can see now are:

Implement DRIVE as a Dimension

Simplifies hierarchy design
Will this cause issues with reporting?  It seems counter-intuitive to me to report time-bound data on a dimension only
It also seems problematic to have a dimension that you KNOW will change every time you refresh your data

Implement DRIVE as a Fact Table

Simplifies reporting
Complicates hierarchy(?) - I'll be using Drive to map data back to a specific server or computer as well.  Is it OK to use a fact table as an intermediate level in a hierarchy?  I don't think it is.

Implement DRIVE as both a Fact and Dimension

Fact will contain just the key, date, and facts on space
Dimension will include other non-additive data like what computer it's on, etc.
Seems to resolve both issues, but is this an anti-pattern?

I expect I would have a drive_usage fact table with a link to a snapshot time dimension, a drive dimension, a computer dimension and the various numerical facts about the drive at that instant in time.

There should probably be nothing regularly changing in the drive dimension - I guess it depends upon your definition of drive - is it a physical drive or a logical unit or what.  Perhaps your "C" drive has a serial number, and it's replaced - then the dimension will expire and a new dimension is added.  These things about a dimension are not really "facts", they are attributes.  This wouldn't affect reporting because the data for computer X, drive C has continuity.  Similarly if computer X is upgraded from dual core to quad core and so there is a change to the dimension (assuming something beyond number of cores is not tracked in a fact table, like a motherboard revision).  The capacity of a drive would be in the fact table, so changes to that over time are just new facts with new dates.  Sometimes you can even model changes to membership as facts.  i.e. if physical drives 1-5 are in logical drive C one day and then physical drives 1-6 are in logical drive C the next, that might just be a fact change in the physical drive membership fact table.  These are what some people call factless fact tables, since the only fact is the existence of the row shows membership - there is not much to be done except totalling or counting.

When you get into folders, modeling the hierarchy can be a lot more tricky depending upon what you are trying to achieve with rollups.

There is a lot of art to DW modeling in domains which aren't run-of-the-mill scenarios.


what are the implications of setting up a subscriber as a publisher? We have application database (APP_DB) that creates a "live copy" of itself via transactional replication (push) to a database (COPY_DB) on a separate server.  This COPY_DB is used for reporting and troubleshooting.

There is now a need for a small subset of the tables (4 tables out of 150) to be placed ("live") in a second database (NEW_DB).  What would be the implications of setting up COPY_DB as a publisher of these four tables to subscriber NEW_DB?  Good idea?  Horrible idea?

It seems like a silly idea to me, but I find myself in a situation where I have more control over the server where COPY_DB and NEW_DB live and could more easily implement such a solution rather than setting up a second publication on APP_DB.

This is known as the republishing model.  It is usually used for balancing distribution loads over slow or expensive links although I have seen it used in the scenario you described as well.  The republishing topology you propose is supported which can be found near the bottom of the page Republish Data, Transactional publication -> Transactional subscription/transactional publication -> Transactional subscription.

If I recall correctly, the challenge with this topology occurs if you ever have to reinitialize COPY_DB, you will also have to reinitialize NEW_DB.


What are the performance considerations between using a broad PK vs a separate synthetic key and UQ? I have several tables where records can be uniquely identified with several broad business fields.  In the past, I've used these fields as a PK, with these benefits in mind:

Simplicity; there are no extraneous fields and just one index
Clustering allows for fast merge joins and range-based filters

However, I've heard a case made for creating a synthetic IDENTITY INT PK, and instead enforcing the business key with a separate UNIQUE constraint.  The advantage is that the narrow PK makes for much smaller secondary indices.

If a table has no indices other than the PK, I don't see any reason to favor the second approach, though in a large table it's probably best to assume that indices may be necessary in the future, and therefore favor the narrow synthetic PK.  Am I missing any considerations?

Incidentally, I'm not arguing against using synthetic keys in data warehouses, I'm just interested in when to use a single broad PK and when to use a narrow PK plus a broad UK.

There is no significant disadvantage using the natural key as the clustered index

there are no non-clustered indexes
no foreign keys referencing this table (it is a parent row)

The downside would be increased page splits as data inserts would be distributed throughout the data, instead of at the end.

Where you do have FKs or NC indexes, the using a narrow, numeric, increasing clustered index has advantages. You only repeat a few bytes of data per NC or FK entry, not the while business/natural key.

As to why, read the too 5 articles from Google

Note I avoided the use of "primary key".

You can have the clustered index on the surrogate key but keep the PK on the business rules but as non-clustered. Just make sure the clustered is unique becauuse SQL will add a "uniquifier" to make it so.

Finally, it may make sense to have a surrogate key but not blindly on every table: many-many tables do not need one, or where a compound key from the parent tables will suffice


SQL Booleans vs ManyToMany As I understand it, one way to 'abstract' a parent/child relationship in SQL would be to have a table set up with a joining foreign key table. Ie in this case, the swimsets are children of different style_tags:

swimset_styletag

id          swimset_id  styletag_id
----------  ----------  -----------
1           1           1
2           2           2
3           3           1
4           3           2

swimset

id          set_description
----------  -----------------------
1           4 x 100 Free Interval A
2           4 x 100 Back Interval A
3           6 x 100 free/back on in

styletag

id          tag_name
----------  ----------
1           Free
2           back

However, I'm testing the idea of just using Booleans and therefore one single table instead of the foreignkey:

swimset

id          set_description          free_tag  back_tag
----------  -----------------------  --------  --------
1           4 x 100 Free Interval A  True      False
2           4 x 100 Back Interval A  False     True
3           6 x 100 free/back on in  True      True

My understanding is that the queries would be faster (one table), and that it will still be easy enough to query all of the swimsets that are tagged "free" (ie look up all of the children of a certain parent). Are there any downsides that I'm missing here by lumping everything into one table?

One problem with using a boolean column for each tag is that you would be limited to two tags only.  If you ever want to support tags for breaststroke, butterfly, sidestroke, doggy style, … those would all require schema changes.  You would also have to alter your queries to support those columns.  As a result, your application code won't support tags in general; each tag will feel like a new feature that you have to add code for.

(MySQL has no true boolean type.  The closest approximation would be numeric types TINYINT(1) or BIT(1).)


START WITH and CONNECT BY condition in Oracle SQL I have a question on the START WITH and CONNECT BY condition (in Oracle 12c):

This is my Oracle SQL:

SELECT
    employee_id,
    lpad(' ', level*2-1,' ') || last_name,
    manager_id,
    level,
    SYS_CONNECT_BY_PATH(last_name, '/') "Path" --returns the full path of the child
FROM
    hr.employees
start with employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
AND NOT (last_name = 'Cambrault' and first_name = 'Gerald')
order siblings by last_name

which is giving the following output:

EMPLOYEE_ID     LAST_NAME           MANAGER_ID  LEVEL   PATH
100             King                    null    1   /King
102                 De Haan             100     2   /King/De Haan
103                     Hunold          102     3   /King/De Haan/Hunold
105                         Austin      103     4   /King/De Haan/Hunold/Austin
104                         Ernst       103     4   /King/De Haan/Hunold/Ernst
107                         Lorentz     103     4   /King/De Haan/Hunold/Lorentz
106                         Pataballa   103     4   /King/De Haan/Hunold/Pataballa

But when I remove the START WITH in the above query:

SELECT
    employee_id,
    lpad(' ', level*2-1,' ') || last_name,
    manager_id,
    level,
    SYS_CONNECT_BY_PATH(last_name, '/') "Path" --returns the full path of the child
FROM
    hr.employees
CONNECT BY PRIOR employee_id = manager_id
AND NOT (last_name = 'Cambrault' and first_name = 'Gerald')
order siblings by last_name

it gives the following result:

EMPLOYEE_ID     LAST_NAME   MANAGER_ID  LEVEL   PATH
148             Cambrault   100         1       /Cambrault
172                 Bates   148         2       /Cambrault/Bates
169                 Bloom   148         2       /Cambrault/Bloom
170                 Fox     148         2       /Cambrault/Fox
173                 Kumar   148         2       /Cambrault/Kumar
168                 Ozer    148         2       /Cambrault/Ozer
171                 Smith   148         2       /Cambrault/Smith

Can you please explain why despite giving using the condition NOT (last_name = 'Cambrault' and first_name = 'Gerald') it has no effect, whereas it works only when used with START WITH (starting the hierarchy from the employee_id = 100). I understand the condition in CONNECT BY has no effect on the root row(s) but employee_id (148) is not root; it is just a parent row so why it is not working?

When you have CONNECT BY without START WITH, the root, starting points of the recursion are all the rows of the table. It is useful if you want to find all the hierarchies under all employees.

The condition in CONNECT BY is checked only for the next levels of recursion, so only for siblings.

You can modify START WITH to get everything except Cambrault:
(note that this way, an employee may appear multiple times, one as individual, one as a child of their manager, another as a child of their manager, another as a grandchild of their manager's manager, etc.
(so this is probably not what you want):

START WITH
      NOT (last_name = 'Cambrault' AND first_name = 'Gerald')

or all hierarchies under top level managers (employers without a manager). Everyone will appear once this way:

START WITH manager_id IS NULL

or all hierarchies under top level managers (employers without a manager) except Cambrault's.
Note that this will give you the same results as the previous START WITH with current data (but will differ when Cambrault is promoted to top level manager):

START WITH manager_id IS NULL
  AND NOT (last_name = 'Cambrault' AND first_name = 'Gerald')

Test in dbfiddle.uk.


Get table name by foreign key I have a two tables in the database. First table has a foreign key to second table.

Can I get second table name by the foreign key from first table?

Log in using psql, issue

\d your_table

and look up the referenced table's name. If you set \set ECHO_HIDDEN on beforehand, you'll get a bunch of queries that produce the output, those you can reuse in your own discovery script.

For example, on my test database these look like

test=# \d tfk
       Table "test.tfk"
 Column │  Type   │ Modifiers
────────┼─────────┼───────────
 t_id   │ integer │
Foreign-key constraints:
    "tfk_t_id_fkey" FOREIGN KEY (t_id) REFERENCES t(t_id)

The query you are interested in is:

SELECT conname,
  pg_catalog.pg_get_constraintdef(r.oid, true) as condef
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '32850' AND r.contype = 'f' ORDER BY 1;

With a minor change, you get the table name like

SELECT confrelid::regclass AS foreign_table_name
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '32850' AND r.contype = 'f' ORDER BY 1;


Postgres sharding installation with citusdb We are developing a prototype for a BIG data product. We have almost 2 billion records. We have used PostgresSQL 9.5 as a back-end and Python as front-end.

We are using a 16*2.4 GHz processors with 160 GB RAM on Amazon servers.

Our bench mark for query results are 10 seconds maximum, although simple count queries on tables with index are taking approximately 30 minutes to 1 Hour .

To overcome our performance issues I required changes in configuration file:

max_connections = 20
shared_buffers = 14GB
effective_cache_size = 42GB
work_mem = 367001kB
maintenance_work_mem = 2GB
checkpoint_segments = 128
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 500

I created the monthly partition on sale_date column. Even though performance are still bad. I was reading some articles and found out that PostgreSQL uses a single CPU for query processing from a single connection.

To use all the CPUs for query processing we can use sharding, and pg_shard is the opensource component created by cistusdata for this exact purpose.

I have installed the citusdb as explained on multi-node-setup-page.

When I execute CREATE EXTENSION pg_shard;, I get this error:

  ERROR:  could not open extension control file "/usr/share/postgresql/9.5/extension/pg_shard.control": No such file or directory

I manually copied the pg_shard.control file in the extension folder and I then started getting this error:

  pg_shard--1.2.sql not found.

Any help is appreciated. How can I solve this problem?

It looks like pg_shard files are not installed. You will need to build pg_shard from sources and install it using make install within pg_shard source folder.

Please be aware that pg_shard has reached end of life, and it is included in  recently released (Citus 5.0).

You can also get Citus from PGDG if you are on RPM based system.

sudo yum install -y
https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-6-x86_64/pgdg-ami201503-95-9.5-2.noarch.rpm
sudo yum install -y citus_95


UPDATE table using CTE I wanted to update the ranks of my table Performance and I found this solution, but I do not understand why this SQL code works:

WITH cte AS (
    SELECT AllTimeRank, r = RANK () OVER (PARTITION BY Distance ORDER BY TimeInSeconds)
    FROM dbo.Performance
)
UPDATE cte
SET AllTimeRank = r FROM cte;

The statement says UPDATE CTE but in fact, it really updates the table (dbo.Performance).

Can someone explain me this magic?

The following statement seems to do exactly the same:

WITH cte AS (
     SELECT Id, r = RANK () OVER (PARTITION BY Distance ORDER BY TimeInSeconds)
     FROM dbo.Performance
)
UPDATE dbo.Performance
SET AllTimeRank = r
FROM cte INNER JOIN dbo.Performance p ON p.Id = cte.Id;

No magic. CTE in this case is an updatable view.

Updatable and Insertable Views:

SQL Server
Oracle
MySQL
DB2
Teradata

  The following statement seems to do exactly the same:

In general - no. Simply imagine that id is not unique.

  Is the first option better?

Two queries with different (general) logic cannot be compared. - akina


Why doesn't row data show up in SQL Developer? I'm using Oracle database and I've created a test table "Table1" with a varchar column "column1". I ran the following loop to add some rows:

BEGIN
    FOR i in 1..10 LOOP
        INSERT INTO TABLE1 (COLUMN1)
        VALUES (i);
    END LOOP;
END;

If I run 'select count(*) from table1' I get '10' as my result. However, in SQL Developer, nothing shows up. I can add rows manually and commit them to the database(these appear in SQL Developer), but I can't seem to view the ones created in sqlplus command line. Why is that?

I see no COMMIT up there in your code.
By default SQL*Plus autocommit is off.
Data changes are not visible to other sessions until they are not commited.


sp_GetAppLock slow performance unusual behaviour We had a critical issue yesterday where by a 1 second stored procedure started to take minutes to execute. I don't know the business logic as of yet, however want to understand the impact of using sp_GetAppLock.

So scenario is:

Stored procedure called usp_abc, no parameters

Code within stored procedure looks like this:

BEGIN
BEGIN TRANSACTION

sp_getapplock @Resource = usp_abc, @LockMode = 'Exclusive'

with cte_1
( SELECT TOP 1 ID FROM tbl1 WHERE Status = 'OK')

UPDATE tbl1
SET Status = 'Complete'
WHERE ID = (SELECT ID FROM cte_1)

COMMIT TRAN

END

This stored procedure is called from the same application but on a load balancer from two different application servers. The stored procedure is constantly executed, yesterday when we had the issue more so (multiple times per second) because of backlog.

What I was seeing yesterday when querying sp_who2 was the execution of the stored procedure was being blocked by earlier executions. There were around 5 SPIDs doing the same thing. I traced back through the first SPID to take a look at resource wait. It fluctuated between SOS_SCHEDULER_YIELD and APPLICATION:5.0 "usp_abc" - basically itself.

It looked like it had got itself in a nasty loop, however the executions sometimes did complete. When running the update on its own without the lock it ran sub second as expected.

Once the load balancer was switched off and requests were coming from a single source the issue went away at the query took sub second.

This isn't our code, however we want to make recommendations on how to stop this occurring again. From what I can tell it looks like the developer put this in place for concurrency reasons and possibly to avoid deadlocks.

However judging by the code the native sql read committed should suffice in this situation because it should lock the data being updated meaning it is not possible to update the same record twice. I don't think deadlocking will be an issue with this query.

What are your thoughts?

Try adding SET_XACT_ABORT ON to the stored procedure to ensure the transaction is rolled back following termination due to an attention request. This is a best practice for procs with explicit transactions.

A scenario that could cause the symptoms you describe is that the proc execution timed out during execution due to blocking on the table.  When a client timeout occurs, the client API cancels the proc by sending an attention event to stop execution.  This cancels the executing proc but does not roll back the transaction if SET_XACT_ABORT OFF.  The transaction will remain open until the connection is closed and reused (in the case of a connection pool), the connection is physically closed, or an explicit rollback or commit is issued on the same connection.

This is especially nasty with persistent (non-pooled) connections that don't properly clean up after an exception because all subsequent work performed on the connection is unwittingly done under the context of the open transaction.


How to correctly interpret index usages from EXPLAIN of mysql? I've exported the results of an EXPLAIN run on a query. What I find confusing is that there's the key column listing out one of the indexes from the list of possible_keys(not shown in picture) however only the top row makes mention of Using index explicitely in the Extra column.

What does this mean in the 2nd row, is it not using the index listed
in the key column?
How should I interpret what the contents of the
key column is about and how it is used?
enter code here

a. Should I interpret this as
that index is used in the where stage of this query?

Using index means that the columns used for that table are all in the chosen index.  This is called a "covering index".

Note the terminology confusion:  the Key column tells you whether it is "using an index"; Using index says whether that index is "covering" in this usage.

Using index condition is something else; don't get confused.

Using temporary; Using filesort does not necessarily apply to the table in question.  See EXPLAIN FORMAT=JSON SELECT ... for specifically which GROUP BY and/or ORDER BY are creating a temp table and/or sorting.

ref=const probably means something like:  INDEX(foo) with WHERE foo=123.  key_len=8 usually refers to a BIGINT NOT NULL.

ref=Production1_ may be a truncation of Production1_db.my_table.my_column

The second row does not say Using index because the index being used in the second table (FKB8...) is not "covering".

EXPLAIN mostly ignores GROUP BY, ORDER BY, and LIMIT.  Instead, it focuses on what index(es), if any, is used for WHERE or ON.


How can I run a PostgreSQL outside the standard directories on Linux, ie outside /var/run/postgresql etc? I need to be able to run PostgreSQL databases outside the standard installation directories, ie run the existing postgres or pg_ctl process pointing to the alternative ports, configuration and data directories and get it running, with permissions etc working okay.

I don't want it to depend on the existing package based installation setup's structures in /etc/postgresql and /var/run/postgresql and the postgres user, but use their executables if possible.

I will not be using initdb much. The data will be unzipped from other installations. The only thing required is database compatibility with the existing package based installations, which I may be able to install if they are absent. Using executables of my own will be the last resort, due to the possibility of unexpected local dependencies.

Windows appears well covered in that respect with some downloads at EnterpriseDB but there doesn't seem so much available for Linux.

pg_ctl allows for a -D option which allows you to point at your data wherever it is.

I do this all the time for development environments and fun things like setting up intracomputer replication environments for testing.  It works fine.

The one thing you really need to be careful about is the configuration.  After unzipping you should probably edit the postgresql.conf appropriately.


Changing Databases Dynamically using Variables I want change into a database programatically using in code:

DECLARE @SQL nvarchar(2000)
DECLARE @DBName sysname

SET @DBName= 'foo'
SET @SQL = 'USE [' + @DBName + ']'

select @SQL
execute @SQL

When I execute the @SQL I get this error:

  Msg 203, Level 16, State 2, Line 9
  The name 'USE [foo]' is not a valid identifier.

Is there a way to do this? Thank you in advance.

The goal is to now be in the foo database after this runs.

I changed

execute @SQL

to

exec (@SQL)

and it worked for me. However, the scope of the use is only in the dynamic SQL.