SQL filter only if each unique value has more than N records Here is my sample SQL statement:

SELECT DAY,
       name,
       value
FROM my_table
WHERE DAY = '${date}'
GROUP BY DAY
         name,
         value
ORDER BY name ASC

For example, 3 unique names in 'name' column: Alice, Bob, Clark.

Alice has 5 rows, Bob has 9 rows, Clark has 12 rows.

I want to add a filter if rows with same value > 10 rows. 'Clark' satisfies in this case.

How to add this? under WHERE?

Welcome.

Here is what you want. But please follow the notes given below too.

SELECT day,
       ip_address,
       user_name,
       timestamp,
       count(ip_address) AS tct
FROM my_table
WHERE day = current_date()
GROUP BY ip_address
HAVING tct> 10
ORDER BY ip_address ASC

Do not use select * instead give specific column names.
You are speaking of count per name in your question but your code groups by DAY, name, value - check if it is fine for you.
It is better (always) to list your data structure and the output needed along with your code to get best answer.


Selecting, inserting, updating and deleting in a single query - possibly? I am new to SQL Server and have only very basic knowledge in SQL. Is it possible to create a single SQL query out of the following:

Select all rows where movedTo is not empty
Copy all these entries one by one into another table, but copying only a few columns as the other table does not have the same structure (let's assume that serial and reason are the same and movedTo should be inserted into destination
Now overwrite the region column in the prior selected results with the content of the each movedTo column row by row
Now empty the movedTo and e.g. movedDate column of all at the beginning selected rows.

I know how to do it in NoSQL, as I would just use for-loops, variables, JSON arrays and stuff, but I am lost realising it in SQL.

Hope someone can help me out on this.

Sure, you can use the OUTPUT clause in an UPDATE statement.

-- create some test tables

create table #sample(
    id int identity(1,1) not null primary key,
    serial varchar(50),
    reason varchar(50),
    movedTo varchar(50),
    region varchar(50),
    movedDate date
    );

go

create table #sample2(
    id int identity(1,1) not null primary key,
    serial varchar(50),
    reason varchar(50),
    destination varchar(50),
    );

go

-- add some sample data
insert #sample(serial, reason, movedTo, region, movedDate)
select top(10) RTRIM(row_number() over( order by @@SPID)), newid(), newid(), null, GETDATE()
from sys.columns a;

go

-- perform the update/insert in one
update #sample
set region = movedTo, movedTo = null, movedDate = null
output deleted.serial, deleted.reason, deleted.movedTo
into #sample2(serial, reason, destination)
where movedTo is not null;

go

-- show the result
select *
from #sample;

select *
from #sample2;


Everything is ok and x is not disappearing Since some time ago, I've started with MySQL so I don't know Workbench very well.

That little red x next to the number of the line that indicates an error does not disappear, even though everything is okay and I fixed what was wrong.

A simple example: select * project where name='Anna' and I can execute it but x won't disappear unless I delete everything and type again. This is very annoying and I can't concentrate because of always thinking about what should be wrong.

Could somebody tell me what to do to fix this? :(

Sounds like it is a syntax check complaining:

select * FROM project where name='Anna'
         ^^^^  -- missing syntax keyword!


Do the type-specific WKT- constructors function differently from ST_GeomFromText? From the docs on MariaDB, for ST_LineStringFromText

  Constructs a LINESTRING value using its WKT representation and SRID.

  ST_LineFromText(), ST_LineStringFromText(), ST_LineFromText() and ST_LineStringFromText() are all synonyms.

It seems to be technically incorrect though, for example here we construct a point and a polygon using ST_LineStringFromText(),

WITH t(geom) AS ( VALUES
  (ST_LineStringFromText('POLYGON ((35 10, 45 45, 15 40, 10 20, 35 10),(20 30, 35 35, 30 20, 20 30))')),
  (ST_LineStringFromText('POINT( 0 0 )')),
  (ST_LineStringFromText('LINESTRING( 0 0, 1 1 )'))
)
SELECT ST_AsText(geom)
FROM t;

Returns..

POLYGON((35 10,45 45,15 40,10 20,35 10),(20 30,35 35,30 20,20 30))
POINT(0 0)
LINESTRING(0 0,1 1)

The Polygon functions are just as confusingly documented,

  Constructs a POLYGON value using its WKT representation and SRID.

  ST_PolyFromText(), ST_PolygonFromText(), PolyFromText() and ST_PolygonFromText() are all synonyms.

Yet you can replace ST_LineStringFromText with ST_PolyFromText and they work the same way.

MySQL Behavior

With MySQL 8 you seem to get an error on the above construction,

  WKT value is a geometry of unexpected type POINT in st_linestringfromtext.

However cute and undocumented, all these functions map internally to Create_func_geometry_from_text in item_create.cc,. For this reason, I would advise using ST_GeomFromText() which best reflects that.

GEOMCOLLFROMTEXT
GEOMETRYCOLLECTIONFROMTEXT
GEOMETRYFROMTEXT
GEOMFROMTEXT
LINEFROMTEXT
LINESTRINGFROMTEXT
MLINEFROMTEXT
MPOINTFROMTEXT
MPOLYFROMTEXT
MULTILINESTRINGFROMTEXT
MULTIPOINTFROMTEXT
MULTIPOLYGONFROMTEXT
POINTFROMTEXT
POLYFROMTEXT
POLYGONFROMTEXT
ST_GEOMCOLLFROMTEXT
ST_GEOMETRYCOLLECTIONFROMTEXT
ST_GEOMETRYFROMTEXT
ST_GEOMFROMTEXT
ST_LINEFROMTEXT
ST_LINESTRINGFROMTEXT
ST_MLINEFROMTEXT
ST_MPOINTFROMTEXT
ST_MPOLYFROMTEXT
ST_MULTILINESTRINGFROMTEXT
ST_MULTIPOINTFROMTEXT
ST_MULTIPOLYGONFROMTEXT
ST_POINTFROMTEXT
ST_POLYFROMTEXT
ST_POLYGONFROMTEXT


How can I set maxIdleTimeMS by command line or mongo-shell? So, I have deployed my PHP application (using Laravel). However , I've noticed a high connection number in connection pool.

I would like to know how could I to set the maxIdleTimeMS parameter through MongoDB Shell or command line (or even via configuration file)?

Thanks in advance!

UPDATE

  Please, see the important note on below comment from @jmikola

maxIdleTimeMS is applicable to MongoDB drivers and is managed on client side. It has nothing to do with MongoDB server so can't be set inside shell


Generate backwards compatible CREATE DATABASE script with all objects? I'm working on SQL Server 2016, and I want to deploy a database I have created to Sql Server 2012 SP1.

I found I was unable to create a backup in v2016 and restore to v2012. So instead in the v2016 version I:

Right click the DB
Select Tasks > Generate scripts
Script a CREATE statement for all objects

This kinda works... but I have to add a lot of objects manually. The database has tables/views/tvps/sps/scalar functions. Is there a way to script a create statement that is backwards compatible with older SQL Server versions?

You're using the correct method, you're just not setting all the right options.

Go through the Tasks > Generate scripts wizard again, there are steps in there where you can choose the all object types, and other places where you can choose what properties of those objects to include (permissions, indexes, etc).

There's also a 'Script for Server Version' option, which allows you to choose which version of SQL you're scripting for.

If your database is small enough, you can even include data in that script. If not, you'll have to create the empty database container, and then using SSIS or something to pipe all the data over.

One more option: you can create an SSIS package and use the "Transfer SQL Server Objects Task" type to do the same thing, it should have all the same options:


Optimize wildcard searching for a NVARCHAR(MAX) column I have been asked to improve the searching capabilities of a LIKE '%SOMETEXT%' predicate on an NVARCHAR(MAX) column. I am aware of full text indexes but am not sure if they are the best strategy.

A traditional rowstore index obviously wouldn't do the job given that NVARCHAR(MAX) isn't an acceptable index key value and also I'd be searching using (LIKE '%SOME TEXT%') so I wouldn't be able to write a sargable statement.

Is a full text index the best option for this case?

Yes and no.

It is the best built in way.

You COULD handle it on application layer and ALSO store the text in something made for that (i.e. a search engine style like Lucene).

You COULD roll your own. Trigram/Trigraph style. Will use a lot of programming and a lot of space, but will make queries faster.

One way to get an index seek for a leading %wildcard by Aaron Bertrand

Trigram Wildcard String Search in SQL Server by Paul White

Have what you need to get started there ;)


Is there a DBMS that allows the "= NULL" syntax? Inspired by a StackOverflow question (Why doesn't SQL support "= null" instead of "is null"?).

Is there a DBMS that actually supports the = NULL syntax?

Some implementations of SQL do recognise x = NULL as equality, the ISO/ANSI standard on the other hand does not. In SQL Server for instance, SET ANSI_NULLS OFF results in (NULL = NULL) = true.

SET ANSI_NULLS OFF
SELECT CASE WHEN NULL = NULL THEN 1 ELSE 0 END

SET ANSI_NULLS ON
SELECT CASE WHEN NULL = NULL THEN 1 ELSE 0 END


SQL Server Audit File Metrics Information I'm currently working in an environment that requires we enable SQL Server Audit, enterprise-wide.  We generate .sqlaudit files as we have retention and access guidelines, and I'm finding that we need to compile trending information about the files generated, such as number created per hour, total volume of audit information, etc.  Currently, I'm only aware of DMVs/DMFs that provide audit definition information (the list can be found here), but nothing related to the output of the SQL Server Audit process.

I'm currently running PowerShell scripts to collect this information from the metadata of the .sqlaudit files, but I'd prefer not to rely on an external process, if possible.  I've looked into extended stored procedures such as xp_dirtree, but that only returns the number of files and not size information, so it doesn't provide as complete a picture as I need here.  xp_cmdshell, while viable, is not allowed for obvious security reasons and because NIST compliance explicitly prohibits its use.
 Are there any approaches within the database engine (I'm asking about things I can hit with TSQL) where I can get this information, such as an Extended Event or hidden DMV I've not yet come across?  I feel like I've done some extensive digging, but I'm never surprised if I miss something.

I may as well post my terribad approach in case others are interested.  Basically, the following is a 2-step process.  The first is to create a table in a database of your choosing that will hold a log of the Audit file meta data that you wish to collect:

CREATE TABLE dbo.Audit_FileMetrics
(
    ID  BIGINT  IDENTITY(1,1) NOT NULL,
    [Name] VARCHAR(512),
    [LastWriteTime] DATETIME2,
    [Length] BIGINT,
    [CompressedLength] BIGINT
)

I'm keeping it simple by only tracking the name, creation date, and size (both compressed and uncompressed).  Why compressed?  Well, we have systems in our environment that generate terabytes worth of audit activity per day, so we have to compress the .sqlaudit files immediately after they are generated otherwise we run into a Tribble situation.  Because of this, the PowerShell routine below pulls some meta data out of the .zip files directly.

The second part of this process is to setup a SQLAgent job with a PowerShell step that fires off the following:

#required to read the header data from the .zip files
Add-Type -assembly "system.io.compression.filesystem"

#get filepath where audit files are written as well as the last file's timestamp
$filePath = Invoke-Sqlcmd -Query "SELECT TOP 1 sfa.log_file_path, af.LastWriteTime FROM sys.server_file_audits sfa CROSS APPLY(SELECT COALESCE(MAX(LastWriteTime), CAST('1900-01-01' AS DATETIME2)) AS LastWriteTime FROM <EnterDatabaseNameHere>.dbo.Audit_FileMetrics) af WHERE sfa.on_failure = 1" -Database "<EnterDatabaseNameHere>" #-ServerInstance "localhost"
#not "needed", but if you run this block over and over in ISE, this is handy as it clears out the array
$auditFiles = @()
#get list of .zip files we have not yet collected meta data from
$files = ls $filePath.log_file_path -Filter "*.zip" | Where-Object {$_.LastWriteTime -gt $filePath.LastWriteTime}
ForEach($file IN $files)
{
    $myPath = $filePath.log_file_path + $file.Name
    #read/append file header meta data
    $auditFiles += [io.compression.zipfile]::OpenRead("$myPath").Entries | SELECT Name, LastWriteTime, Length, @{Name="CompressedLength";Expression={$file.Length}}
}

#build out INSERT command statement
$sqlCommandInsert = 'INSERT INTO dbo.Audit_FileMetrics ([Name], [LastWriteTime], [Length], [CompressedLength]) '
$sqlCommandValues = 'VALUES '
$sqlQuery = ''
$i = 0

ForEach($auditFile IN $auditFiles)
{
    $sqlCommandValues += "('" + $auditFile.Name + "', '" + $auditFile.LastWriteTime + "', " + $auditFile.Length + ", " + $auditFile.CompressedLength + "), "
    $i++

    if ($i -eq 1000)
    {
        #cannot insert more than 1000 VALUES tuples, so commit a batch of 1000
        $sqlQuery = $sqlCommandInsert + $sqlCommandValues.Substring(0, $sqlCommandValues.Length - 2)
        Invoke-Sqlcmd -Query $sqlQuery -Database "<EnterDatabaseNameHere>" #-ServerInstance "localhost"
        $sqlCommandValues = 'VALUES '
        $i = 0
    }
}

#final insert into log table
$sqlQuery = $sqlCommandInsert + $sqlCommandValues.Substring(0, $sqlCommandValues.Length - 2)
Invoke-Sqlcmd -Query $sqlQuery -Database "<EnterDatabaseNameHere>" #-ServerInstance "localhost"

What this routine does is identify the path where the audit files are written to as well as the timestamp of the last audit file tracked.  Using that, it runs a Get-ChildItem against the identified directory looking for all *.zip files returning only files older than the last file we've identified prior.

ASSUMPTIONS:

This routine assumes the Audit Definition is configured such that the inability to write out an audit file will result with the server being shutdown.  If you do not have this requirement, you will want to adjust the query (i.e. this is the reason for the predicate WHERE sfa.on_failure = 1)
This routine is only looking for .zip files.  If you do not compress your .sqlaudit files, you will run into a scenario where the meta data on the last .sqlaudit file will likely change at the next run because the database engine is still writing to it.  In this event, I would suggest ignoring the last file returned in the array, which should avoid any duplicate/inconsistent data.

Finally, this is totally specific to my environment, but it does exactly what I need.  If you have suggestions, updates, etc. please post another answer as more ideas are always appreciated.


Data modeling with many attributes where few apply to each record I'm pretty new to data modeling, so please excuse me if I've missed something obvious. I'm trying to decide on a schema (or possibly a DBMS) to work with some data where there are quite a few possible attributes (60-100 maybe), but only a handful will apply to each record. Here are my options as I see them.

Option 1: Very wide table

Just include each of the attributes as columns and most will be NULL for any given record.

Pros: keeps schema simple, keeps queries simple

Cons: inelegant, unwieldy, adding/removing properties requires a schema change

Option 2: Entity-attribute-value model

Separate table(s) for listing attributes and their values. This is how I've seen this problem handled before. For example, WordPress uses something like this for their *meta tables.

Pros: easy to understand conceptually, flexible

Cons: query construction becomes a pain, performance suffers, you wind up with a lot of duplicated data (i.e. the same property name is listed over and over).

Option 3: NoSQL Document or Graph DBMS

Go with a DBMS that's built to handle schema-less data. I've looked at MongoDB, RethinkDB, and OrientDB. OrientDB looks really cool, but I don't know Java, and it seems like it's primarily a Java thing (the PHP driver, for example, looks problematic).

Pros: built to handle flexible schemata, fast

Cons: relationships seem difficult, I have no experience with them, seems like a lot of duplicate data (e.g. what happens if I want to change the name of a property?), querying seems way more complicated

As a side note, this application will not get very much traffic, and will have very few concurrent connections. So, scalability is a fairly minor concern. Thanks for any suggestions.

Option 4:  Class Table Inheritance

This is a design technique used to implement a class/subclass situation.  In this situation, attributes often apply only to one subclass, and not to all rows in the table. Visit the tag of the same name over in SO to see a bunch of relevant  questions and answers.

This has the advantage eliminating NULLS (mostly), providing fast and easy joins, and enforcing the 1:1 neture of the class:subclass relationship.

Your Option 1 is sometimes called "Single Table Inheritance"


"Can't find file mysql/plugin.frm" when starting up MySQL When I start my MySQL server, I get this error:

/usr/libexec/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.

I have run mysql_upgrade to rebuild it, and it works to rebuild. Before I did that, I removed and installed MySQL. However, when I tried to re-start, I get the same error. I am sure it is not a permission problem, as all files and directories are created by the mysql user.

Could you advise me on how to fix this error?

If you have mysql data directory at non-standard location and your system has apparmor, ensure that /etc/apparmor.d/usr.sbin.mysqld has your data directory whitelisted. The configuration should look like:

/mysql/data/dir/ r, #trailing slash is mandatory
/mysql/data/dir/** rwk,

Apparmor daemon has to be restarted after you make any changes to config.


How can I compare the server impact of transaction replication We are developing a system that must extract data from an existing SQL Server 2008 R2 cluster.  One of our design goals is to limit the performance impact on that cluster as much as is practical.

Our system must have access to data that is < 1 minute old.  So we are considering both a straightforward SELECT from the source clusterexecuted at regular intervals (say every 20 seconds), or alternatively, establishing transaction replication from the source cluster to our own system for subsequent processing.

We have a test instance of the cluster, and I would appreciate any recommendations on how to measure impact on the source cluster for each of these functions for the purposes of making an architectural decision.

I'm familiar with the use of the profiler, and that seems like a reasonable tool for analyzing the impact of the SELECTs,  but I am running into difficulty when trying to find similar impact data for replication.

Our system must have access to data that is < 1 minute old.
alternatively, establishing transaction replication from the source cluster to our own system for subsequent processing.

Transactional replication will give you the ability of having a near-to real time data at the subscribers.
It comes at the cost, as during the snapshot, the entire table is locked and if the table is huge, then there will be prolonged blocking happening. As an alternative, you can initialize T-Rep from a backup.
Best is to use some stress utility to simulate a workload and then see the latency that is incurred on the T-Rep.
It also makes a big difference whether you are replicating your entire database vs a subset of tables, whether the distribution is on the same server as publisher server, the n/w latency between publisher and subscriber and most obviously the hardware.
It will be tricky when you have to add new table to the publisher or do some schema modification on the publisher - as it will require a snapshot to be generated. Also, be careful with Identity columns when implementing T-Rep.
Below are some links/references that will get you started.

Enhance Transactional Replication Performance
Transaction Replication Latency Diagnosis
How do Identity Columns get Replicated?
Publishing stored proc execution in transactional replication
Transactional replication altering tables and adding stored procedures
Transactional Replication - Snapshot metrics


Migrate database to Azure I have a 600GB database that I must transfer from an on-premises machine to an on Azure VM machine (IaaS)
I would like to use the dbatools copy-database tool for this
for this I will have to know what will be the best storage method that allows me to have a fast transfer

local backup then restore on the Azure machine
remote backups directly on the machine in azure
a backup and a restore from storage azure blob

In my experience, the fastest way to get this done is to copy the backup up to Azure blob storage, then restore from there. The fastest way to do that move in the testing I've done is through the use of AZCopy. Copy to the drive that's already mapped to your VM and there's nothing you have to do special to then run the restore. At that point, it's just SQL Server doing it's thing.


SQL 2014 Availability Group Cluster or Listener We recently deployed a setup of SQL 2014 availability groups and we are having some difficulty with the failover.

Setup:

Two windows server 2012R2 VMs in a windows cluster with a CNAME pointed at the cluster IP address.
The availability group is setup synchronously.
The listener and the CNO are both in active directory.
The application is using the cluster CNAME to connect to the database.

The problem we are running into is that in the event of a failover the Windows cluster is failing over, and the AG is not.  So we get the primary of the windows cluster pointed at the secondary of the AG, so all the databases are read only.

My question is:

  should we be pointing the CNAME at the listener instead of the Cluster
  IP?  Or more generally what is the proper way to ensure correct
  failover behavior?

Turns out that yes the cname should be pointed at the listener IP.


MySQL: Is it best practice to check whether some column is NULL by `NOT ISNULL(column_name)`? Given the following schema, my question is that since I have already added the NOT NULL to IsBanned, would I have to check whether it is not null using the syntax NOT ISNULL(IsBanned)? And what's the best practice to do the same if I didn't add NOT NULL?
CREATE TABLE Viewer (
    AccountID varchar(25),
    IsBanned boolean                NOT NULL
                                    DEFAULT 0,
    ...
    CHECK                           (NOT ISNULL(isBanned)),
);

How many different values do you need?  It sounds like you need only "true" and "false".  If so, declare the column to be NOT NULL.  There is no need for a NULL check if it is declared NOT NULL.  The NOT NULL performs that CHECK.
If you need yes/no/maybe or yes/no/don't-know-yet, etc, then you could declare it to be NULL (which simply means that it has an extra possible setting, namely NULL).


Table Design - Storing Mulitple Parameters Required for Data Warehouse Report I am building a SQL Server Data Warehouse to store reporting data from a 3rd party system.

The reporting data is pulled from the 3rd party system by passing in a set of parameters (the parameters passed in could vary from minimum 3 to max 8. Depends on the report detail required by the user).

Here is my plans for the DW process:

SSIS package to run the report from the 3rd party system (using provided parameters)
Save the data to a Staging table
Use stored proc to take data from staging to the Dimension and Fact tables

My question is: Does the above process sound correct, and what is the best way to store the parameters required to run the report?

Should I store them in a SQL table which has just 2 columns (e.g. ReportSettingsV2 table) and the parameters are added as rows?
Or should the table have each parameter as a column, and if a parameter is not required for a particular load of the report then that will just be marked as NULL (e.g. ReportSettings table)?
Or a third option I am thinking would be to have the parameters stroed in an XML data type column?

In previous jobs where I had to do a similar thing (one report connecting to many parameters), I went for your first option:

store them in a [child] SQL table which has just 2 columns (I used ParameterName and ParameterValue and each parameter is added as a row.

It's nice and normalised, plus things don't break when you have to add/remove/change parameters, since you don't have to change your table structure to add a new parameter.

Try to avoid RBAR (Row-By-Agonising-Row) processing in your function that parses the (variable size) list of parameters into 1 string. Number tables or recursive CTEs come in useful to keep the function runtimes down.


execution plan TABLE ACCESS BY INDEX ROWID cardinality This is the explain plan for a certain query.
The query contains a PK order by clauses and matches on two other columns on the where clause.

Since PK is used for index full scan, I assume cardinality to be accurate. So I expect for the table access to have the same cardinality: PK values are unique, so there should be exactly 168 unique ROWIDs in index, pointing to exactly 168 rows, not 5633.

In which circumstances can TABLE ACCESS BY INDEX ROWID cardinality exceed that of INDEX FULL SCAN?

An ancient "issue" that is still with us, even in 18.4

SQL> select banner_full from v$version;

BANNER_FULL
------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 -
Production
Version 18.4.0.0.0

SQL> create table t1 as select * from dba_objects where object_id is not null;

Table created.

SQL> alter table t1 add constraint t1_pk primary key (object_id);

Table altered.

SQL> exec dbms_stats.gather_table_stats(user, 'T1', cascade=>true);

PL/SQL procedure successfully completed.

SQL> explain plan for select * from (select * from t1 order by object_id) where rownum <= 5;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3766500789

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     5 |  2405 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |       |       |       |            |          |
|   2 |   VIEW                        |       |     5 |  2405 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    | 62395 |  7616K|     3   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | T1_PK |     5 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=5)

16 rows selected.

The above is kind of explainable, but this is where the numbers get strange (3670 comes from num_rows/num_distinct):

SQL> explain plan for select * from (select * from t1 where owner = 'SYS' order by object_id) where rownum <= 5;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3766500789

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     5 |  2405 |     5   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |       |       |       |            |          |
|   2 |   VIEW                        |       |     6 |  2886 |     5   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T1    |  3670 |   447K|     5   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | T1_PK |   102 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=5)
   3 - filter("OWNER"='SYS')

17 rows selected.

rownum implicitly triggers some first_rows_N optimization that messes up the cardinality (this was discussed on Oracle-l, 12 years ago: https://www.freelists.org/post/oracle-l/ORDER-BY-and-first-rows-10-madness,4). One workaround is to run it with all_rows hint (yes, even if it is the current setting in the session), also I had to place the hint for the index, otherwise the database would have chosen FTS in this example:

SQL> explain plan for select /*+ all_rows */ * from (select /*+ index(t1 t1_pk) */ * from t1 order by object_id) where rownum <= 5;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3766500789

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     5 |  2405 |  1459   (1)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |       |       |       |            |          |
|   2 |   VIEW                        |       | 62395 |    28M|  1459   (1)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    | 62395 |  7616K|  1459   (1)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | T1_PK | 62395 |       |   131   (1)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=5)

16 rows selected.

Much better. Another reason to use analytic functions or the 12c+ syntax instead of rownum. Analytic function:

SQL> explain plan for select * from (select t1.*, row_number() over (order by object_id) as rn from t1) where rn <= 5;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 3641836603

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     5 |  2470 |  1459   (1)| 00:00:01 |
|*  1 |  VIEW                         |       |     5 |  2470 |  1459   (1)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY       |       | 62395 |  7616K|  1459   (1)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    | 62395 |  7616K|  1459   (1)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | T1_PK | 62395 |       |   131   (1)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN"<=5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_ID")<=5)

17 rows selected.

Or 12c+ syntax:

SQL> explain plan for select * from t1 order by object_id fetch first 5 rows only;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 3641836603

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     5 |  2535 |  1459   (1)| 00:00:01 |
|*  1 |  VIEW                         |       |     5 |  2535 |  1459   (1)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY       |       | 62395 |  7616K|  1459   (1)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    | 62395 |  7616K|  1459   (1)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | T1_PK | 62395 |       |   131   (1)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "T1"."OBJECT_ID")<=5)

17 rows selected.


Should I stop my PostgreSQL server before reboot? Every week I do an apt-get upgrade and dist-upgrade on the server where my PostgreSQL is running. After that, I do a sudo reboot now.

Should I stop the PostgreSQL service before this reboot?

In theory the system should send it a stop signal and then wait for it to shutdown cleanly. But, I've seen this fail, with the result that the PostgreSQL had to go through crash recovery and any unlogged tables were truncated and had to be regenerated.  So I exercise an abundance of caution and manually shut PostgreSQL down before rebooting.


Datetime Clustered Index Keeps getting fragmented I have a large table (4 million Records) named CDR, which is used for storing CDRs (Call Detail Record)s from a Cisco router in a VoIP system, the table is constantly growing and will never have a record inserted out of sequence. Nor do we update or delete rows.

Since most of our queries are based on the time a call was initiated, I created my main clustered index on this column (DATETIME data-type). However, after a brief time the index becomes fragmented and after just one week it has over 70 percent of fragmentation so we need to rebuild it. I don't understand why this keeps happening since we insert sequentially, and don't update or delete rows.

Any suggestions to reduce or remove the fragmentation will be much appreciated.

I'm using a SQL Server 2014 Enterprise version on an old Xenon server with Windows Server 2012.

Fragmentation can still occur on a table with an ever increasing key even if that key is never itself subject to updates.

If the file group is shared with other objects allocations can be interleaved causing fragmentation.
Updates that increase the size of rows can cause page splits.
Deletes can leave pages nearly empty and cause internal fragmentation.

However you say deletes and updates are rare or non existent. So to reach 70% fragmentation within a week I would assume that the cause must be a shrink database task. See Why you should not shrink your data files


How can i model this data A customer can make a reservation for an accommodation, the accommodation can be in a resort or elsewhere.

So i would say that a resort can have different types of accommodations, ie. Chalets, apartments bungalows, caravans, lodges, hotels, etc...

and if a customer wants to make a reservation for these types of accommodation but not in a resort.

How would i model it?

ps. lets assume that elsewhere* means a hotel outside the resort

ERD Diagram-

http://i.stack.imgur.com/y9W6d.jpg

Is "resort" a special type of entity? If not then you could model this as:

Customer
--------
  CustomerID - unique primary key
  Name - text
  (other customer fields)

Accomodation
------------
  AccomodationID - unique primary key
  LocationID - foreign key to "where" the accomodation is
  AccomodationTypeID - foreign key to types of accomodations
  (other stuff such as "# bathrooms", "# beds...")

Location
--------
  LocationID - unique primiary key
  LocationTypeID - foreign key to location type
  (other fields)

Reservasions
------------
  ReservationID - unique primary key
  CustomerID - foreign key to customer
  AccomodationID - foreign key to accomodation
  StartDate
  EndDate
  (any other fields you need)

LocationTypes
-------------
  LocationTypeID
  Description (such as "Resort", "campground",...)

AccomodationTypes
-----------------
  AccomodationTypeID
  Description (such as "Lean-to", "chalet", "motel",...)


SQL Bulk import vs SSIS in Simplicity We are deciding between bulk insert or SSIS. We have a simple spreadsheet with 5 columns, customerid, cost, date, product name, etc. The spreadsheet is generally only around 200 rows every week.

All we do is import the flat file CSV into a simple table, no transformations or anything. After , we conduct few calculations in T-SQL. We could care less about performance, considering only 200 rows. At the end, we want customer to run stored procedure, which imports csv, and conducts automatic T-sql calculations.

What is the best option for easy use and simplicity? Seems like more dependency with getting out of the T-SQL window, creating the SSIS package, and then calling the ssis package in t-sql. At the same token, I hear Bulk insert is old and not be used anymore.

I researched few articles SSIS vs bulk insert for performance, but have a question about simplicity.

https://www.mssqltips.com/sqlservertip/4241/data-import-performance-comparison-tsql-vs-ssis-for-large-import/

Using bulk insert is the simplest solution.

Using SSIS will require a development environment with visual studio, installing additional components like SSDT, designing the DTS package and configuring the execution of the DTS (on a SSIS server or directly via dtexec). Although this takes time, it will give you a lot more of flexibility to work later on if your requirements change.

If your task is as simple as loading a CSV or text file to a table in SQL just use bulk insert, you can do any additional transformation with SQL on a staging table and being only 200 rows (even if it increases a little), you shouldn't have any performance issues.


Create a new instance on a SQL server, but I need to use the default DB instance I need to be able to connect to mydb\wrk when I already have everything in mydb and I do not want to change anything other than being able to connect to mydb\wrk.

I can connect to my database with the following connection string

Provider = SQLOLEDB.1; Password = ****; Persist Security Info = True;
User ID = sa; Initial Catalog = Master; Data Source = MyDb

I want to use this:

Provider = SQLOLEDB.1; Password = ****; Persist Security Info = True;
User ID = sa; Initial Catalog = Master; Data Source = MyDb\ wrk

I want to change the least possible on the server

There is no need to install an extra instance if you want to connect to the default instance using an instance name.

You can create an alias for that.

Open SQL Server configuration manager on the client machine , and under SQL Server native client configuration for both the 32 and 64 bit node add an alias like this:

And then you can connect using your alias name:


Ways to Upgrade Production MySQL db server from 5.5.42 to 5.7.13 with zero downtime Our product is an enterprise application with MySQL Enterprise version 5.5.42 deployed in many customer datacenters. We're planning to migrate the MySQL db (bundled with our product) to version 5.7.13. So, when our customers upgrade from X version of product to Y version of our product, we need to upgrade their MySQL 5.5.42 to 5.7.13. Customers would hate to have downtime on this important software.

It appears that we need to migrate first to 5.6 and then to 5.7.

Some questions regarding the upgrade:

Is there a way to have zero downtime without requiring duplicate hardware?
Are there ways to reduce the downtime to a minimum ? If yes, what additional storage requirements will this add?

What else should I know?

1) No. You have to restart MySQL and that means downtime.

2) To minimize the downtime you need an identical server which you can switch to and back. The procedure on high level:

you can build a passive master using percona-xtrabackup for example without downtime
Set up master-master replication
Upgrade the passive master to 5.6 and wait for it to catch up in replication
Switch active master to be the upgraded one (running 5.6)
Stop and rebuild the old master with the same process you did in step #1 but now from the new version (5.6)
Repeat the procedure to upgrade for 5.7
If you don't need it trash the passive master


Conditionally add an index to a temp table I have a stored procedure that looks something like this:

SELECT columnlist
INTO #temptable
FROM Table
JOIN lotsofothertables
WHERE severalconditions

UPDATE anothertable
SET column = #temptable.column
FROM anothertable
JOIN #temptable
    ON anothertable.PKColumn = #temptable.PKColumn

The initial query will frequently produce just a few columns (<100) so the update is fast.  But occasionally (and these are the runs we are having problems with) it produces 1000's or even 10,000's.  Would it be reasonable to add something like this between them?

IF @@RowCount > 100
   CREATE INDEX ix_temp ON #temptable(PKColumn)

Or am I better off just creating the index on the table regardless since with so few rows the amount of time spent creating the index will be fairly small?

This is somewhat subjective but I'm not at all a fan of SELECT ... INTO anyway and normally replace it with an explicit CREATE TABLE and INSERT ... SELECT as the datatypes, column names, and nullability can then be seen much more explicitly (and both can be minimally logged).

In this case if you were to create the temp table with an unnamed primary key constraint on the column up front

CREATE TABLE #temptable
(
PKColumn INT PRIMARY KEY
)

rather than creating the table and adding an index afterwards you would be able to benefit (at least potentially as that does have some issues) from temp table object caching.

I wouldn't bother making it conditional on row count unless you've proved that the presence of the index with few rows somehow makes a significant deterioration in performance (unlikely).


SQL Stuff Function Showing All Results I am wanting to see a list of codes with their relevant chapters in my database.  A code can have more than one chapter assigned, therefore meaning more than one row in the database.

This is how the database looks:

Code     |     Chapter
000      |     1.1.0
000      |     3.0.0
123      |     1.1.0
123      |     1.2.0
456      |     1.7.0
789      |     1.2.0
789      |     2.1.4
789      |     5.1.7

I would like to do a STUFF function to display the Code and the Chapter/s on one line, but for every Code in the system.  This is how I would like it to look:

Code     |     Chapter
000      |     1.1.0, 3.0.0
123      |     1.1.0, 1.2.0
456      |     1.7.0
789      |     1.2.0, 2.1.4, 5.1.7

But I am receiving the following:

Code     |     Chapter
000      |     1.1.0, 1.1.0, 1.2.0, 1.2.0, 1.7.0, 2.1.4, 3.0.0, 5.1.7
123      |     1.1.0, 1.1.0, 1.2.0, 1.2.0, 1.7.0, 2.1.4, 3.0.0, 5.1.7
456      |     1.1.0, 1.1.0, 1.2.0, 1.2.0, 1.7.0, 2.1.4, 3.0.0, 5.1.7
789      |     1.1.0, 1.1.0, 1.2.0, 1.2.0, 1.7.0, 2.1.4, 3.0.0, 5.1.7

The SQL I am using is:

select distinct
       C.Code,
       STUFF((SELECT ', ' + CHAP.[Key]
             FROM Chapters CHAP
             ORDER BY CHAP.[Key]
             FOR XML PATH('')), 1, 1, '') AS [Chapter]
From Codes C
left outer join CodeChapterLinks CCL
ON C.Id = CCL.CodeId
left outer join Chapters CHAP
ON CCL.ChapterId = CHAP.Id
ORDER BY C.Code asc

Any assistance is much appreciated :)

Your code shows 3 tables, could you describe their structure?

Also, your code is using for xml on Chapter table, which, I assume, is the reference of the codes, not the codes that are actually used in chapter. So, may be better to use xml for path on CodeChapterLinks table.

Like:

select  distinct
       Code,
       STUFF((SELECT ', ' + c.chapter
             FROM CodeChapterLinks  c
             where c.code = t.code
             ORDER BY c.chapter
             FOR XML PATH('')), 1, 1, '') AS [Chapter]
From CodeChapterLinks  t

And by the way, stuff is not the function that concatenates the results, for xml does.


MySQL controlling shutdown We are running a financial application using MySQL as database.

For  performance reasons  we are using memory tables for a few of our live tables where the data is populated ever few milli seconds.

To ensure data consistency in all scenarios (planned and unplanned outages)  we need to ensure that the data in the memory tables are always persisted for that  we are planning to implement shutdown hooks, wherein n case of any shutdown the data would be written in normal tables  and restored during start-up.

I am unfortunately not able to find a way to do it.

Any suggestions would be appreciated.

We are using MySQL ver 5.7

Don't even try.

InnoDB has years of optimization behind it.  It persists to disk even across unplanned power failures due to its integrated logs.

Furthermore, because of MEMORY using table locks (as opposed to InnoDB's row locks), there are benchmarks that say that InnoDB is faster than MEMORY.  There could be cases where Memory is faster than InnoDB.  But between your moderately high write rate and the JOINs, InnoDB is very likely to have less interference between the two tasks.

If you want full persistence, you need to write to the disk 500 times per second.  Are you using SSDs?  RAID?  An ordinary HDD cannot run at 500 flushes per second.  If you have a fast enough drive, you may as well let InnoDB take care of everything for you.

InnoDB's automatic saving and restoring across power failures is very likely to be faster, and much less programming effort for you.


MS Azure SQL: how do I import CSVs and set up push notifications? Recently had a client who needed me to connect his online store databases to a POS system using MS Sql Azure 2014.

However, the normal methods using BULK insert nor SQl Service broker do not work. I usually receive errors like "Not available in this version of SQL server.

I tried the online azure website but I'm on windows 10 which does not seem to work well with silverlight....and using visual studio to access the database, does not accept bulk insert commands either....

Any guides on how to use to utilize these... wityh azure...normal SQL server 2014 ways do not work.

Use the BCP utility to import and Export.
as the following states, Azure doesn't seem to support the Bulk insert...
see here


How do I get data from multiple tables if the agency_code doesn't match I have a table with these fields

Employment_Form (table name)
employee_number
full_name
agency_code

The second table is

HumanResources(table name)
employee_number
full_name
agency_code

The assignment is to find the records that have a typo for agency_code on the Employment_Form table.
The data in the HumanResources table is said to be 100% correct.

I am not sure how to best approach this. I am open to suggestions.

You can achieve that by using this select:

select ef.employee_number,ef.full_name,ef.agency_code
from Employment_Form  ef, HumanResources hr
where ef.employee_number = hr.employee_number
and ef.agency_code <> hr.agency_code;

Here you select all the columns of the Employment_Form table, fetching only those rows with matching employee_number and wrong agency_code.

If that full_name is the same on both tables, you can either use it, instead of employee_number , like this

select ef.employee_number,ef.full_name,ef.agency_code
from Employment_Form  ef, HumanResources hr
where ef.full_name = hr.full_name
and ef.agency_code <> hr.agency_code;

The output would be the same.

Hope this helps.


Unify specific tables in one product table Today my database has the tables in this first image. The problem is that now I need to do stock control and other products will be added too.

To support many types of products, first I designed the tables in the next image (design 1). This will be a solution to generic products that don't have any kind of serial number to differentiate they.

After doing some research I found the solution in the next image (design 2). This supports generic and the specific products (like SIM cards and devices). I think that the complexity will increase greatly this way. Also there is a problem that I have FKs in simcards and devices. One great thing about this solution is that will support future products with specific data.

What is the best approach to solve this problem?

Why not add product_type_attributes into design 1? If product_type_attributes is necessary and it is in multiple types then design 2 is much better compared to design 1 because it is more normalized.


I want to update mysql vertion 5.5.59 to 5.7.21 I'm trying to update my mysql version but it showing error I'm using Linux mint 17.3 version

used command

sudo apt-get install mysql-server mysql-client mysql-common mysql-server-core-5.7 mysql-client-core-5.7

it showing this

  Reading package lists... Done
Building dependency tree
Reading state information... Done
Package mysql-client-core-5.7 is not available, but is referred to by another package.
This may mean that the package is missing, has been obsoleted, or
is only available from another source
However the following packages replace it:
  mysql-cluster-community-client:i386 libmysqlclient-dev:i386
  mysql-community-client:i386 mysql-cluster-community-client
  libmysqlclient-dev mysql-community-client

Package mysql-server-core-5.7 is not available, but is referred to by another package.
This may mean that the package is missing, has been obsoleted, or
is only available from another source
However the following packages replace it:
  mysql-common:i386 mysql-cluster-community-server:i386
  mysql-community-server:i386 mysql-common mysql-cluster-community-server
  mysql-community-server

E: Package 'mysql-server-core-5.7' has no installation candidate
E: Package 'mysql-client-core-5.7' has no installation candidate

  mysql -v

mysql  Ver 14.14 Distrib 5.5.59, for debian-linux-gnu (x86_64) using readline 6.3

how can i fix this??

First, never try to uninstall or purge your mysql packages. Your db be drop from your system for ever…

Secondly, you should try to use apt-cache policy mysql* to check which versions are available on your system.

Thirdly, you should do a backup of ALL you DB.

If it is a simple update, you should try sudo apt-get upgrade mysql-client mysql-server mysql-common.

Or more aggressive sudo apt-get dist-upgrade mysql-client mysql-server mysql-common

If not, use the version available for update from apt-cache policy mysql* output.

And, from what I can see, your source.list seems not to contain any repo that contains mysql-server-core-5.7. So use apt-cache policy mysql*.


SQL SERVER: Header Blocker Across Multiple Databases with wait type CXCONSUMER We have an instance of SQL Server which has multiple databases. A process in one database seems to be blocking a process in another database. When I look in activity monitor I can see a header blocker (A one in the header blocker column). This seems to be blocking other processes in different databases. I can see their ids in the blocked by column when I select one from the drop down. Am I correct that it is cross database blocking? I didn't think this was possible. They are all running exactly the same stored procedure, but they have their own instance in each database. They are doing updates and inserts, but only within their own databases.

eg

UPDATE  SCA
SET SCA.date_last_read_only = TDR.date_seen
FROM [dbo].[SINGLE_CERT_ACC] SCA
INNER JOIN #TMP_DELTA_READONLY TDR
ON SCA.id = TDR.id
SET @RecsUpdated = @RecsUpdated + @@ROWCOUNT

On further investigation I believe the update itself might be causing the locking. It is on a large table 500+ million rows. As I understand it the table will become locked as soon as SQL starts to find the join. Perhaps the way activity monitor displays the locking is a little misleading though


Schema Design - would you split this table? Table A

- auto-incrementing integer primary key
- 3 dates
- 17 booleans
- 7 varchars (less than 40 bytes in these combined varchars in 99% of rows)
- 5 foreign keys

Table B

- auto-incrementing integer primary key
- 1 date
- 2 boolean fields
- 3 varchars (1 unique UUID string in each row, other 2 varchars empty 99% of the time)
- 3 foreign keys

Table A has 25 million rows - SELECT COUNT(*) takes approx 70 seconds
Table B has 20 million rows - SELECT COUNT(*) takes approx 6-7 seconds

I only included Table B to illustrate my assuption, which is that the 900% more time it takes to count Table A, despite having only 25% more rows than Table B, is that Table A has 33 columns, compared with only 10 columns in Table B. I realize 33 columns isn't a "big" table, but it might be bigger than we need, since we only use a few of those columns in many operations.

We aren't experiencing any serious issues, aside from counting taking too long to be useful within the span of a web request (e.g., for pagination in a Django admin list view).

However, we are about to increase the size of Table A, from 25 million, by about 1-2 million rows per month, because of a new feature, which adds to our fears about performance in general.

We're considering the following options:

Split Table A

We'd put most of Table A's columns into a new child table (Table A Meta) with a 1-to-1 relation to Table A. Only 4-6 most commonly used columns would remain in Table A, and presumably this would help make read queries and counting quicker at the expense of needing a separate query (or JOIN) to get the additional details for a given row, as well as a small amount of additional write overhead.

Wait for trouble, then partition

The other option is to ignore the fact that SELECT COUNT(*) is basically unusable, since other things are functioning properly (querying via indexed fields, etc.), and continue using Table A even past 50, 75 million rows, etc., eventually partitioning at some point in the future.

I realize these options aren't mutually exclusive, but I'd like to get some general feedback on those options and whether you would bother to do the first, etc.

Execution

Here is the execution plan for the longer COUNT mentioned:

db=# EXPLAIN ANALYZE SELECT COUNT(*) FROM "message";
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1236008.12..1236008.14 rows=1 width=8) (actual time=79357.273..79357.293 rows=1 loops=1)
   ->  Seq Scan on message  (cost=0.00..1173727.10 rows=24912410 width=0) (actual time=1.460..65375.321 rows=24926666 loops=1)
 Planning time: 0.543 ms
 Execution time: 79357.350 ms
(4 rows)

count(*) is always slow, so don't do it unless you have to.

The most important factor is usually the physical size of the table, so check that for an explanation for what you observe.

Splitting the table along a one-to-one relationship might speed up counting, but will slow down other queries, which seems like a bad deal.

Partitioning won't help, but done correctly, it can make it easy get rid of old data. Using a recent PostgreSQL version and parallel query with a lot of processes is more promising for fast counting.


Transfer data from DB2 to Oracle I want to transfer data from an old DB2 system to a new Oracle database.

How should I go about doing this?

Another approach is to use the Oracle Gateway for DB2 which can be used to create an Oracle database link to the DB2 database. Then use a SQL script to "pull" all the data from each table in turn by

insert into oracle_table select * from db2_table@dblink;


Dynamically changing tables in datasource in SSRS I have an SSRS report that I have set up to query two tables, let's call them ABC_Audit and ABC_AuditDetail. I have now added XYZ_Audit and XYZ_AuditDetail to the same database, and want to be able to switch between querying the ABC... or XYZ... tables in the report. The tables are set up exactly the same, so the only part of the query that would need to be changed is the table name in the FROM ... clauses.

In my non-DBA, not-very-SSRS-knowledgeable eyes, there might be two ways to achieve this

either dynamically change the data source (have one data source for each set of tables to query),
or use a parameter in the SQL query to choose between the sets of tables to query, either of which is accessed using a drop-down on the report page.

Creating a separate report for each set of tables is not a viable option, as the number of sets will grow over time, and keeping all those reports up-to-date if/when it changes will be grueling.

Does anyone have any advice on how to achieve this, and which option would be the better option to pursue?

Given that you expect the number of data sets to grow over time, your best bet would be to create a stored procedure in the database that uses a parameter value to determine the source tables and dynamic SQL to build the query.

Create an SSRS parameter to match and pass this value through to the data set and the stored procedure will return the results from the table specified in the parameter.

Basic Example:

CREATE PROCEDURE spGetData
(
    @SourceTable NVARCHAR(255)
)
AS
BEGIN

    DECLARE @SQL NVARCHAR(MAX)

    SET @SQL = 'SELECT [Column1], [Column2], [Column3]
    FROM [dbo].[' + @SourceTable + '_Audit]'

    EXEC sp_executesql @SQL
END


Number of times a table is read, written to and updated Is there a way I can find out the number of times a table is read, updated, added to or has had rows deleted during a trace?

I am creating the trace in SQL using sp_trace_setstatus, not using the Sql Profiler UI.

I am using sp_trace_setevent and ::fn_trace_gettable to get my current stats, but cannot find out how to get the stats regarding the table.

I would like to run this trace on both SQL 2000 and SQL 2008 R2.

In SQL Server Profiler, I tested this using SQL Server 2008. I don't know if the output will be the same in MSSQL 2000.

Using the template: TSQL_Replay

Select the Events Selection Tab
Check Show all events
Expand Performance then check Showplan XML
When you run your trace you will see the following events appear

When you select Showplan XML, you will see the Execution plan generated by the query

If you hover your mouse over the Delete event, as in my example you will get the number of
rows and other information related to the query

Update 15:50

Here is the SQL exported by profiler for the showplan XML event.

exec sp_trace_setevent @TraceID, 122, 8, @on
exec sp_trace_setevent @TraceID, 122, 1, @on
exec sp_trace_setevent @TraceID, 122, 9, @on
exec sp_trace_setevent @TraceID, 122, 2, @on
exec sp_trace_setevent @TraceID, 122, 10, @on
exec sp_trace_setevent @TraceID, 122, 14, @on
exec sp_trace_setevent @TraceID, 122, 26, @on
exec sp_trace_setevent @TraceID, 122, 3, @on
exec sp_trace_setevent @TraceID, 122, 11, @on
exec sp_trace_setevent @TraceID, 122, 35, @on
exec sp_trace_setevent @TraceID, 122, 51, @on
exec sp_trace_setevent @TraceID, 122, 12, @on
exec sp_trace_setevent @TraceID, 122, 60, @on

122 is the event and then there is an id for each column selected in profiler for Showplan XML when I exported the trace to a file.

Here is the list of events you can trace from Microsoft: Trace Events


Computed Column Index Not Used I want to have a fast lookup based on if two columns are equal.  I tried to use a computed column with an index, but SQL Server doesn't seem to use it.  If I just use a statically populated bit column with an index, I get the expected index seek.

Seems there is some other questions like this out there, but none focused on why an index wouldn't be used.

Test Table:

CREATE TABLE dbo.Diffs
    (
    Id int NOT NULL IDENTITY (1, 1),
    DataA int NULL,
    DataB int NULL,
    DiffPersisted  AS isnull(convert(bit, case when [DataA] is null and [DataB] is not null then 1 when [DataA] <> [DataB] then 1 else 0 end), 0) PERSISTED ,
    DiffComp  AS isnull(convert(bit, case when [DataA] is null and [DataB] is not null then 1 when [DataA] <> [DataB] then 1 else 0 end), 0),
    DiffStatic bit not null,
    Primary Key (Id)
    )

create index ix_DiffPersisted on Diffs (DiffPersisted)
create index ix_DiffComp on Diffs (DiffComp)
create index ix_DiffStatic on Diffs (DiffStatic)

And the Query:

select Id from Diffs where DiffPersisted = 1
select Id from Diffs where DiffComp = 1
select Id from Diffs where DiffStatic = 1

And the resulting execution plans:

Try with COALESCE instead of ISNULL. With ISNULL, SQL Server doesn't seem capable of pushing a predicate against the narrower index, and therefore has to scan the clustered to find the information.

CREATE TABLE dbo.Diffs
    (
    Id int NOT NULL IDENTITY (1, 1),
    DataA int NULL,
    DataB int NULL,
    DiffPersisted  AS COALESCE(convert(bit, case when [DataA] is null
      and [DataB] is not null then 1 when [DataA] <> [DataB]
      then 1 else 0 end), 0) PERSISTED ,
    DiffComp  AS COALESCE(convert(bit, case when [DataA] is null
      and [DataB] is not null then 1 when [DataA] <> [DataB]
      then 1 else 0 end), 0),
    DiffStatic bit not null,
    Primary Key (Id)
    );

That said, if you stick with a static column, a filtered index might make more sense, and will have lower I/O costs (all depending on how many rows typically match the filter predicate) e.g.:

CREATE INDEX ix_DiffStaticFiltered
  ON dbo.Diffs(DiffStatic)
  WHERE DiffStatic = 1;


Resolving issue with mysql.proc after upgrading MySQL from 5.0.* to 5.1.* Like many others, we upgraded our instance of MySQL from 5.0.* to 5.1.49. Since then, we've been running into an error that repeats itself when we perform certain actions:

Attempting to view or run a stored procedure
Attempting to insert into a table after adding a trigger for ON INSERT actions
Executing SELECT LENGHT(description), id FROM table (with the misspelling... when LENGTH was spelled correctly, the error did not display)

The error in question:

  Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted.

This has been reported as a bug (basically, lack of backwards compatibility), but there appears to be some confusion as to how to resolve the issue:

MySQL's documentation says the solution is to dump your stored procedures again after the upgrade to 5.1, then restore them again. The bug report I mentioned earlier shows that this has dubious results.
The bug report suggests running mysql_upgrade as a possible solution (albeit with some concerning warnings). However, it has been reported elsewhere that this solution doesn't always work.

I am in the unfortunate circumstance of not having a test box under which to test possible solutions as our development box is pending an OS upgrade to allow us to install the versions of MySQL we want to install. I am hesitant to try any solution on our production environment unless it has been verified as working by people who have encountered this issue.

What is the correct way to resolve this issue outside of waiting for an official patch or solution from MySQL?

While losing our stored procedures is not ideal, it is also a route we are willing to take to resolve the issue provided that there are no better alternatives and it will solve the issue cleanly.

I was having this issue, and thought I'd mention that running "mysql_upgrade --force" fixed it for me.

I upgraded from 5.0 to 5.1 to 5.5.  The interesting this is that I had already run mysql_upgrade which is why I needed to add the --force option.  Hope this helps someone.


Database design: same products in inventory but with different cost I am trying to design a database for a Stock and Sale System and I have the following issue.

Suppose there is an item in stock that costs $ 100 (product cost, not the selling price), but there are only 3 pieces to sell, then the customer decides to buy another batch of the same product, but this time the lot comes to a different price, $ 76 for example.

The purchase cost is very important, because on it the percentage of profit is calculated, so if the cost is different, the selling price will be different too.

I wonder how to make the seller sell each piece to its corresponding price.

I am a novice in designing databases and I have the following tables:

PRODUCT     -> Record all products, whether or not available in stock.

product_id (PK)

PURCHASE    -> Record all purchase of new products to sell.

purchase_id(PK)
product_id(FK)

SALE        -> Record each sale operation

sale_id (PK)

SALE_ITEM   -> Records which product was sold in a sell operation.

sale_item_id(PK)
product_id(FK)

The SALE_ITEM table is related to the PRODUCT table through an attribute product_id.

So, should I relate the sale of each product with the identifier of the table PURCHASE instead of the identifier in PRODUCT table?

Or, should I force the client to sell the old lot before selling the new ones?

How you would handle this situation?

Thanks, and sorry for my english, I am not a native.

First, you must ask the Controller (or CFO) what the appropriate costing model is for inventory of this sort - LIFO, FIFO, Average, or some less common pattern. This is very important as if you use a different calculation from that published in Financial Reports of the corporation, you will be engaged in fraud of the shareholders and be in violation of Sorbanes-Oxley.

If the financial records are mailed to the shareholders, you might be in violation of RICO also.

All of the methods I listed above are appropriate in various industries, at various times - you cannot make an a priori decision on one being better than the others.

One absolute principle of GAAP is that when any change is made to the traditional accounting policies of a corporation, a number of prior years financial statements must be restated according to the new calculation in the current year's financial statements. This can only be done as authorized by the Controller and/or CFO, and possibly the corporate auditors as well.

Never, ever program an accounting calculation of any sort without verifying the technique with an appropriate corporate authority. There are many aspects of GAAP (Generally Accepted Accounting Principles) that are non-intuitive to those without an accounting background.

Update:

Note that even when inventory items have serial numbers, and thus the actual cost may be known, it is not necessarily appropriate accounting practice to use the actual cost instead of a calculated cost based on all equivalent items in inventory. That is to say: The cost of selling a unit of product XYZ is a calculated accounting value that may not be the actual purchase price of the particular unit sold, even when that is known.

I repeat: You must discuss this with your Controller and/or CFO.


In SQL Server 2008, is possible to one database subscribe 2 publications for the same database? (merge replication) I'm trying to set up an high availability architecture using merge replication.
I have 3 databases, and I'm trying to keep them all up to date with merge replication.

So far I've manage to create one publisher and 2 subscribers, and all works fine...except when the publisher goes down.

So I thought about creating one more publisher and make the other databases also subscribe to it. I can do the publication normally, but when I create the local subscription, even though the GUI says it went successfully, the new subscription does not appear under "Local Subscriptions" folder.

Is this possible? or is it a limitation of sql server? (if it is possible...what may be wrong in my setup?)

regards

What you are asking is not possible.  Do not use SQL replication for high availability.  It is an exotic and soon-to-be deprecated feature set.


Why are some views listed as Materialized in MySQL explain statements if Materialized views don't exist in MySQL? Hopefully the question is self explanatory.  I've read that Materialized views do not exist in MySQL, yet sometimes I see something like this in EXPLAIN

Can somebody clear this up for me?  What's the "(materialized)" mean if materialized views aren't a thing in MySQL?  Using MySQL Server 5.6, Workbench 6.3.

To the outside world (if I am not mistaken), a "materialized view" is a permanent table that is automatically inserted/updated in based on data flowing into some other table.

In MySQL, it has a rather limited meaning.  It refers to certain "derived" tables (eg, FROM ( SELECT ... )).  The Optimizer has multiple ways to deal with such constructs.

Before 5.6, for a single such construct, the Optimizer would simply use that as the first "table" without having to "materialize" anything.
Before 5.6, for multiple derived tables (for FROM (SELECT...) JOIN (SELECT...)) the Optimizer would have to repeatedly re-execute all but the first derived table.
5.6 introduced the automatic ability to create a temp table for one of the derived tables.  Furthermore, the Optimizer became smart enough to discover the optimal index for this temp table and create it.  (A side note:  A suitable rewrite of the entire query can usually perform better.)

There is essentially nothing that you, the programmer, can do to control this version of "materialized view".  (Well, rewrite to avoid the need.)

5.7 and 8.0 have even more performance improvements.  But still no "materialized view" as the outside world knows.


Poor MySQL for Drupal on Shared Hosting My Drupal site is on a shared host where I cannot modify the my.cnf configurations but the database seems to be performing poorly.  Here are some of the flagged issues that I can see...

In phpMyAdmin, the following are flagged RED in under the Status tab:

Slow_queries    613.2 k
Innodb_buffer_pool_reads    15.9 M
Innodb_log_waits    10
Innodb_row_lock_time_avg    740
Innodb_row_lock_time_max    45.1 k
Innodb_row_lock_waits   296
Handler_read_rnd    14.7 G
Handler_read_rnd_next   1.2 T
Qcache_lowmem_prunes    142.2 M
Slow_launch_threads 17
Created_tmp_disk_tables 31.7 M
Select_full_join    1.2 M
Select_range_check  212.8 k
Sort_merge_passes   174.8 k
Opened_tables   36.5 M
Table_locks_waited  512.5 k

Running http://day32.com/MySQL/tuning-primer.sh also flagged these issues:

QUERY CACHE
Query cache is enabled
Current query_cache_size = 512 M
Current query_cache_used = 488 M
Current query_cache_limit = 32 M
Current Query cache Memory fill ratio = 95.38 %
Current query_cache_min_res_unit = 4 K
However, 136119288 queries have been removed from the query cache due to lack of memory
Perhaps you should raise query_cache_size

JOINS
Current join_buffer_size = 4.00 M
You have had 1117666 queries where a join could not use an index properly
You have had 207168 joins without keys that check for key usage after each row
join_buffer_size >= 4 M
This is not advised

TABLE CACHE
Current table_open_cache = 2048 tables
Current table_definition_cache = 256 tables
You have a total of 200 tables
You have 2048 open tables.
Current table_cache hit rate is 0%
, while 100% of your table cache is in use
You should probably increase your table_cache

TEMP TABLES
Current max_heap_table_size = 256 M
Current tmp_table_size = 256 M
Of 53194631 temp tables, 36% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables

I'm not a DBA and have very little experience in DB optimization.  I would really appreciate some help.  What can I do to improve the database performance if I can't modify my.cnf configurations accordingly?

EDIT:
The tables have indexes, but perhaps they aren't optimal?  My database knowledge is limited so it's hard for me to say.  Drupal generates TONS of queries to the database.  I did check the slow query logs and for one of the queries, here is the result of EXPLAIN:

EXPLAIN SELECT menu_links.link_path AS link_path, menu_links.mlid AS mlid, menu_links.router_path AS router_path, menu_links.updated AS updated
FROM
dr_menu_links menu_links
WHERE ( (updated = '1') OR( (router_path NOT IN  ('rss.xml', 'node', 'checkout', 'import', 'image_captcha', 'my-favourites', 'my-orders', 'print', 'search')) AND (external = '0') AND (customized = '1') ))

+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | menu_links | ALL  | router_path   | NULL | NULL    | NULL |  757 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

Other queries in the slow query logs include commands like show databases!

Before you try to change the configuration file, the first thing to check is index use. This part:
JOINS
Current join_buffer_size = 4.00 M
You have had 1117666 queries where a join could not use an index properly
You have had 207168 joins without keys that check for key usage after each row

suggests that many queries cannot use indexes, probably because there are no indexes to use.
Ask for the slow query log, check which queries are slow and check/test if indexes can be added to improve performance.
A general advice is first, to have indexes on all columns that are used in Joins and second, indexes on columns that are used in WHERE, GROUP BY and ORDER BY clauses. But for this, you may need compound indexes and of course you can't create an index for every column combination. That would take too much space and would make Insert, Delete and Update statements much slower. So, you'll have to check what are the most common queries and optimize those first.
There are some tools/services that can help you identify better and faster where the bottlenecks are. One such tool is Percona Toolkit (also known as mk-query-digest, from Maatkit).
A slow query that needs 10 seconds to complete is not good. But it doesn't really affect performance if you are executing it once per hour. A query that needs 60ms can be really bad if it is a simple UPDATE and is executed several times per second. These tools can help you identify those because you can analyze the logs and find total running time, number of times a query has been run and various other figures.

For your example query:
SELECT menu_links.link_path AS link_path
     , menu_links.mlid AS mlid
     , menu_links.router_path AS router_path
     , menu_links.updated AS updated
FROM
    dr_menu_links menu_links
WHERE (  (updated = '1')
      OR (  (router_path NOT IN  ( 'rss.xml'  , 'node'         , 'checkout'
                                 , 'import'   , 'image_captcha', 'my-favourites'
                                 , 'my-orders', 'print'        , 'search'
                                 ) )
        AND (external = '0')
        AND (customized = '1')
         )
      )

possible good indexes are an index on (updated) and one on (external, customized, router_path)


Get list of databases accessed by a particular SQL Server login I'm a DBA who recently joined a new company. One of the first tasks I am given is to ensure that any SQL accounts (both logins and users) do not have elevated permissions in our production environment. They should only have access that is required by the app/jobs they support. Management is fine with filtering access at the database level (granting db_datareader, db_datawriter, ddladmin, etc.) I've reached out to the people in charge of the apps/etl but they themselves are unsure of what access is required by these service accounts.

The problem here is, we have dozens of logins on each SQL instance, and over 50 SQL instances. Whoever created these logins had just granted sysadmin access rather than ensuring only the required access was given. Now we have many custom jobs and 3rd party apps running under these logins.

It's hard to make any changes to the permissions for these accounts without the possibility of breaking production apps/jobs. We don't have a test environment where I can test the changes.

I am thinking that if I can get a list of databases accessed by these service accounts over, say, a 2 month period, it would give me a starting point. I will start by granting db_owner to the logins on the appropriate databases and narrow it down from there.

I know that you can trace the actual SQL code being run by sessions, but is there a way to filter out the "noise" and just get the names of the databases accessed?

Also, I am welcome to any suggestions on an alternative approach to dealing with this issue, or if any other DBA's have tackled on a similar project in the past to share how they did it. This environment is primarily SQL Server 2008 R2 / 2012

You coud use Extended Events for this and stream the events in memory first and then to a database table, only when needed. I have a blog post on that topic that you can find here: https://spaghettidba.com/2015/04/20/tracking-table-usage-and-identifying-unused-objects/ Skip to the "Extended Events to the rescue" section.

Basically, what you do is you set up a trace for locks acquired and then count the occurrences of the locks, first in memory and then dump to a table. The example in that blog post is for table objects, but you could narrow it down for databases:

CREATE EVENT SESSION [audit_database_usage] ON SERVER
ADD EVENT sqlserver.lock_acquired (
    ACTION(sqlserver.is_system, sqlserver.server_principal_name)
    WHERE (
            [package0].[greater_than_equal_uint64]([database_id], (5)) -- user databases only
        AND [package0].[equal_uint64]([associated_object_id], (0))     -- filter out objects
        AND [package0].[not_equal_uint64]([database_id], (32767))      -- filter out resourcedb
        AND [package0].[equal_boolean]([sqlserver].[is_system], (0))   -- only user sessions
        AND [resource_type] = (2)                                      -- DATABASE resources
        )
    )
    WITH (
            MAX_MEMORY = 4096 KB
            ,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
            ,MAX_DISPATCH_LATENCY = 30 SECONDS
            ,MAX_EVENT_SIZE = 0 KB
            ,MEMORY_PARTITION_MODE = NONE
            ,TRACK_CAUSALITY = OFF
            ,STARTUP_STATE = OFF
            )
GO

From this session it's easy to capture database access, without inspecting specifically what is being accessed inside the database, which reduces enormously the number of events captured. If you want to dig deeper and go to the object level, you can use the session in my blog post, which will produce a far greater number of events.


Stock price database optimization in MySQL I am downloading a bulk of stock data:

Ticker symbol, company name, industry sector,
Open, high, low, close

I was wondering performance-wise if separating the ticker, company name, industry sector
from the price data would be optimal, as the tickersymbol and companyname just repeat over and over again.
Or would inner joining the ticker+price every time be slower?
So:

single table (ticker/price), or
two tables (ticker)(price)?

Storing the data in one table is not necessarily faster.  If you move company name and industry out of the table, each row will be smaller.  Smaller rows means more rows per data page.  And that means fewer data pages.

You can have a situation where the rows with the company name would be larger than memory.  But the pages without the company name would fit in memory.  Clearly, the latter situation will result in faster queries.

However, to even begin answering questions about performance, one needs information about the types of queries being run.  This will inform not only the table structure, but also the indexes and data types that might be necessary.


Login cannot view definition even when granted the permission I have an AD login which is in the below AD groups.

DOMAIN\GlobalGroup1
DOMAIN\GlobalGroup2
DOMAIN\GlobalGroup3
DOMAIN\UniversalGroup1

DOMAIN\UniversalGroup1 has been granted VIEW ANY DEFINITION on this SQL Server instance (10.50.6000.34). The user is not able to view any of the databases' definitions.

Other members of the DOMAIN\UniversalGroup1 appear to be able to view definitions.

EXEC xp_logininfo 'DOMAIN\user','all'

Only displays the 3 GlobalGroups. This is a documented limitation of the xp_logininfo procedure.

EXEC xp_logininfo 'DOMAIN\UniversalGroup1','members'

Confirms the user's membership to the group.

How can I troubleshoot this problem? Is the issue in any way related to the difference between the global and universal group?

Is UAC enabled? UAC can truncate the user's list of group memberships


How to find dependencies on a table in mysql 5.0? I am trying to drop a table in MySQL but its complaining about "Cannot delete or update a parent row: a foreign key constraint fails"...

Questions

Is there a way to find out all the table dependencies and objects dependent on it?
For Sybase, DBArtisan gives a really easy way to find dependecies. Is there any such tool for MySQL?

From MySQL 5.0 on, one place you can check is information_schema.table_constraints

For example, to get the number of foreign key relationships for a given table (mydb.mytable), run this:

SELECT COUNT(1) FROM information_schema.table_constraints
WHERE table_schema = 'mydb'
AND table_name = 'mytable'
AND constraint_type='FOREIGN KEY';

From MySQL 5.1 on, you could also use information_schema.referential_constraints. Here is that table layout:

mysql> show create table information_schema.referential_constraints\G
*************************** 1. row ***************************
       Table: REFERENTIAL_CONSTRAINTS
Create Table: CREATE TEMPORARY TABLE `REFERENTIAL_CONSTRAINTS` (
  `CONSTRAINT_CATALOG` varchar(512) DEFAULT NULL,
  `CONSTRAINT_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `CONSTRAINT_NAME` varchar(64) NOT NULL DEFAULT '',
  `UNIQUE_CONSTRAINT_CATALOG` varchar(512) DEFAULT NULL,
  `UNIQUE_CONSTRAINT_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `UNIQUE_CONSTRAINT_NAME` varchar(64) DEFAULT NULL,
  `MATCH_OPTION` varchar(64) NOT NULL DEFAULT '',
  `UPDATE_RULE` varchar(64) NOT NULL DEFAULT '',
  `DELETE_RULE` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `REFERENCED_TABLE_NAME` varchar(64) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

Just inspect table_name and referenced_table_name columns.


New to databases - does my ERD reflect good database design? I'm making a database to hold data for a media player that I'm working on. Originally, I thought to use one table with the fields:

{filePath,artist,title,album,track_number,length,date,artworkUrl}

Since I'm trying to use good DB design techniques, I've now created a DB with three tables. One for the audio file itself, one for the artists, and one for the albums:

I'd really appreciate it if some more knowledgeable people could evaluate my design and, give me some pointers. Thanks in advance!

I recommend using surrogate primary keys instead of artist or album names.  Doing this will improve the database design because currently a change in an artist's name would require updating each entry in your audio_files table for that particular artist.

You and I likely agree it would be strange for an artist to change their name, however they have a penchant for doing this.  Even beyond the infrequent artist name change, you are much more likely to encounter artist name changes due to user error.

Also, I noticed that you use the path_of_file as the primary key of the audio_files table.  Keep in mind that files may move for a variety of reasons, some of which may be out of the domain of control for your app.  You should consider how your database handles that.

To answer your follow up:

If you go with the unique key approach I recommend, updating the artist name would be straight forward:

UPDATE Artists
SET Artists.Artist_Name = 'New Name'
WHERE Artists.Artist_ID =

Updating the artist name in the design you propose would involve inserting a new record in the artist table, creating a subset of file paths with the old artist name, then updating each file path record to the new artist record, and finally deleting the old one.  Yuck!


Mysql query is using some other index We have 2 tables:

 CREATE TABLE `allcallers` (
  `id` varchar(50) NOT NULL DEFAULT '',
  `company_id` varchar(50) NOT NULL,
  `clid` varchar(30) NOT NULL,
  `clid_raw` varchar(50) NOT NULL,
  `uid` varchar(32) NOT NULL,
  `channel_id` varchar(100) NOT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime NOT NULL,
  `type` varchar(30) NOT NULL COMMENT 'Call,SMS,Fax',
  `event` varchar(30) NOT NULL COMMENT 'Incoming/Outgoing',
  `status` varchar(20) NOT NULL COMMENT 'failed/success/bridege/missed',
  `via` varchar(50) NOT NULL COMMENT 'department',
  `department_id` varchar(50) NOT NULL,
  `filename` varchar(50) NOT NULL,
  `file_url` varchar(200) NOT NULL,
  `state` int(5) DEFAULT NULL,
  `country` int(5) NOT NULL,
  `rdnis` varchar(50) NOT NULL,
  `is_billable` tinyint(4) NOT NULL DEFAULT '0',
  `is_synced` int(2) NOT NULL,
  `star_type` int(2) DEFAULT '0',
  `is_archived` int(2) DEFAULT '0' COMMENT '0=not archived ,So show in logs\n1=archived ,Don''t show ',
  `server` varchar(20) NOT NULL,
  `notification_status` tinyint(4) NOT NULL DEFAULT '0',
  `subscription_status` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `start_time` (`start_time`) USING BTREE,
  KEY `uid` (`uid`) USING BTREE,
  KEY `Index 4` (`company_id`,`clid`,`via`,`status`) USING BTREE,
  KEY `is_synced` (`is_synced`),
  KEY `remarketing1` (`subscription_status`,`start_time`) USING BTREE,
  KEY `remarketing2` (`subscription_status`,`department_id`) USING BTREE,
  KEY `remarketing3` (`company_id`,`type`,`status`,`event`) USING BTREE,
  KEY `allcaller_allcaller_logs` (`start_time`,`type`,`event`,`is_billable`,`end_time`,`company_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

and second table

CREATE TABLE `allcaller_logs` (
  `id` varchar(50) NOT NULL DEFAULT '',
  `allcaller_id` varchar(50) NOT NULL,
  `property_id` varchar(50) NOT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime NOT NULL,
  `uid` varchar(32) NOT NULL,
  `via` varchar(200) NOT NULL,
  `user_id` varchar(50) NOT NULL,
  `transfer_to` varchar(50) DEFAULT NULL,
  `file` varchar(50) NOT NULL,
  `status` tinyint(4) NOT NULL,
  `action` varchar(50) NOT NULL,
  `country_code` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `allcaller_id` (`allcaller_id`,`user_id`,`start_time`) USING BTREE,
  CONSTRAINT `FK_allcaller_logs_allcallers` FOREIGN KEY (`allcaller_id`) REFERENCES `allcallers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

We are using this query:

SELECT
  SUM(CEIL(TIME_TO_SEC(TIMEDIFF(C.`end_time`,C.`start_time`))/60)) AS `call_duration`
FROM `allcallers` A
INNER JOIN allcaller_logs C ON C.allcaller_id = A.id
WHERE A.start_time BETWEEN '2015-05-22 23:59:59' AND '2015-06-10 18:49:29'
  AND A.type = 'call'
  AND A.event = 'incoming'
  AND A.is_billable = '1'
  AND (C.`action` = 'received' OR C.`action` = 'transferred')
  AND A.end_time != '0000-00-00 00:00:00'
  AND  A.company_id = '54bcf339a0eeb309';

It should use allcaller_allcaller_logs index but it is using index 4 (which is not even matching the order of conditions in where clause ). can somebody tell me why it is happening.

Explain result for this query:

allcaller_allcaller_logs starts with start_time but in your query start_time is checked by BETWEEN - thats range scan, not direct ref, so the query cannot simply use remaining columns (I suppose you use MySQL 5.5, in 5.6 there is Index Condition Pushdown Optimization which would be able to utilize that index better). Your index seems to "cover" your query on that table, so optimizer decision is still a bit weird, but there may be other reasons (bad cardinality/statistics) which together override it.

Generally if you make special index for your query, you need to organize all direct ref ("=") columns first, then either those with range (BETWEEN or <,>...) or those used in order by if you have it.

As you have A.end_time != ... - such condition cannot be generally satisfied by index, so even if you need covering index, move such columns to the end of your index.

"which is not even matching the order of conditions in where clause" - this is misunderstanding of column order - WHERE clause evaluates all conditions as boolean expression and is free to reorder then any way it sees will be best, so order in columns in WHERE and order in index do not have to correspond in any way. For index it is better to order them by cardinality and actual usage.


PosgreSQL query slow when sorting on indexed timestamp colum I've the following issue: the table below:

    CREATE TABLE public.current_read
(
    id bigint NOT NULL DEFAULT nextval('current_read_id_seq'::regclass),
    cube_id bigint NOT NULL,
    end_time timestamp with time zone NOT NULL,
    period_type character varying COLLATE pg_catalog."default" NOT NULL,
    energy_balance double precision NOT NULL,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT current_read_pkey PRIMARY KEY (id),
    CONSTRAINT current_read_cube_id_fkey FOREIGN KEY (cube_id)
        REFERENCES cubemgmt.cube (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

-- Index: id-current_read-cube_id+end_time+period_type

-- DROP INDEX public."id-current_read-cube_id+end_time+period_type";

CREATE INDEX "id-current_read-cube_id+end_time+period_type"
    ON public.current_read USING btree
    (cube_id, end_time DESC, period_type COLLATE pg_catalog."default")
    TABLESPACE pg_default;

-- Index: ix-current_read-cube_id+end_time

-- DROP INDEX public."ix-current_read-cube_id+end_time";

CREATE INDEX "ix-current_read-cube_id+end_time"
    ON public.current_read USING btree
    (cube_id, end_time DESC)
    TABLESPACE pg_default;

-- Index: ix-current_read-cube_id+period_type

-- DROP INDEX public."ix-current_read-cube_id+period_type";

CREATE INDEX "ix-current_read-cube_id+period_type"
    ON public.current_read USING btree
    (cube_id, period_type COLLATE pg_catalog."default")
    TABLESPACE pg_default;

-- Index: ix-currrent_read-end_time

-- DROP INDEX public."ix-currrent_read-end_time";

CREATE INDEX "ix-currrent_read-end_time"
    ON public.current_read USING btree
    (end_time DESC)
    TABLESPACE pg_default;

ALTER TABLE public.current_read
    CLUSTER ON "ix-currrent_read-end_time";

has currently around 60 million entries.

When I do a simple query:

SELECT energy_balance
                  FROM public.current_read
                  WHERE cube_id = 5
                  AND period_type = '1min'
                  ORDER BY end_time desc
                  LIMIT 120

it takes around 3-4 seconds to return the results.
If I remove the ORDER BY statement, I get (random) results in just a few miliseconds.

Explain plain looks like the following:

"Limit  (cost=730066.65..730080.65 rows=120 width=16) (actual time=1560.745..1613.023 rows=120 loops=1)"
"  ->  Gather Merge  (cost=730066.65..772726.46 rows=365630 width=16) (actual time=1560.743..1613.000 rows=120 loops=1)"
"        Workers Planned: 2"
"        Workers Launched: 2"
"        ->  Sort  (cost=729066.63..729523.67 rows=182815 width=16) (actual time=1538.981..1538.991 rows=101 loops=3)"
"              Sort Key: end_time DESC"
"              Sort Method: top-N heapsort  Memory: 30kB"
"              Worker 0:  Sort Method: top-N heapsort  Memory: 30kB"
"              Worker 1:  Sort Method: top-N heapsort  Memory: 30kB"
"              ->  Parallel Bitmap Heap Scan on current_read  (cost=11257.81..721839.14 rows=182815 width=16) (actual time=432.012..1510.892 rows=138113 loops=3)"
"                    Recheck Cond: ((cube_id = 5) AND ((period_type)::text = '1min'::text))"
"                    Heap Blocks: exact=120165"
"                    ->  Bitmap Index Scan on "ix-current_read-cube_id+period_type"  (cost=0.00..11148.13 rows=438756 width=0) (actual time=360.098..360.098 rows=414338 loops=1)"
"                          Index Cond: ((cube_id = 5) AND ((period_type)::text = '1min'::text))"
"Planning Time: 0.297 ms"
"Execution Time: 1613.112 ms"

So it looks like that query is not taking index "ix-current_read-end_time" into consideration.

Any idea how to make sure the index is used or how to improve the query timing?

Thanks a lot,

Cheers

Fritz

The index on (cube_id, period_type) cannot be used for the ORDER BY. But it can implement the AND period_type = '1min' very efficiently.

The index on (cube_id, end_time, period_type) can be used for the ORDER BY, but cannot be used efficiently for the AND period_type = '1min'.  It can be used, just not efficiently.  It can filter the rows in the index without visiting the table, but it can't jump to a specific part of the index where it knows all of the '1min' values are located.  (The reason is that all the '1min' values within a given cube_id are not located together)

So, it has to make a choice, efficient ORDER BY or efficient period_type = '1min'.  Perhaps it is making the wrong choice, although you haven't shown evidence of that.

You should create the index (cube_id, period_type, end_time).  Then it does not have to make a choice, it gets the best of both.  It can jump to the part of the index where cube_id = 5 AND period_type = '1min', and once there then it can read the rows in order by end_time.


How do DTUs in Standard and Premium performance tiers in SQL Azure compare? We recently observed a serious performance degradation in a SQL Azure database running in Standard3 performance tier - CPU utilization went from ten percent to fifty percent to almost one hundred percent in just one hour. So we changed the performance tier to Premium2 and CPU utilization immediately dropped to about eight percent.

Standard3 is claimed to offer 100 DTUs and Premium2 is claimed to offer 250 DTUs. Which means that eight percent of P2 is just twenty DTUs which is very far from using all of 100 DTUs in Standard3.

Are those DTUs different? Otherwise how is this sudden drop of utilization possible when switching from a 100 DTUs performance tier to a 250 DTUs performance tier?

I was similarly confused when looking at the pricing for those two tiers. 100 DTUs on standard costs $150/mo and 125 DTUs on Premium costs $465/mo, I figured something else must explain that disparity. I think this line from https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers-dtu#choosing-a-service-tier-in-the-dtu-based-purchasing-model must explain the difference:

                            | Standard                   | Premium
IO throughput (approximate) | 2.5 IOPS per DTU           | 48 IOPS per DTU
IO latency (approximate)    | 5 ms (read), 10 ms (write) | 2 ms (read/write)

So it looks like a Premium DTU is actually worth 19x more than a Standard DTU