T-SQL: How to avoid losing digits when divided by a number We have a product 'A' which is billed once but delivered to the customer in multiple phases example 'A1', 'A2', 'A3'.
I need a report for these phases

Suppose Amount of 'A' is 100, so to fetch amount of 'A1', 'A2', 'A3' I divide the amount by 3.

Actual Output
Description Amount

A1          33.33
A2          33.33
A3          33.33

But the total comes to be 99.99 which is creating a problem for the accountants.

Is there a built-In function or a method where I could fetch the output as given below.

Desired Output

Description Amount
A1          33.33
A2          33.33
A3          **33.34**

I do not have phase wise data stored(33.33).

I have two tables: Product table, which has Name of the product and Amount i.e. 'A' and '100' and the other is the Frequency table which has the product and frequency i.e 'A' and '3'. I want any one of these 3 phases to have amount as '33.34' such that when I add these 3 amounts the total is '100' and not '99.99'

Your problem is that you try to solve something mathematically, not by accounting standards.

For the LAST payment, do not use your formula. Instead take the full price and substract all partial prices.

So, A3 would be 100 - A1 - A2 and that would then come to a perfect number.

So, either the first or the last payment are calculated as "the rest missing from the others" instead of "one equal part".


What advantages does in-memory OLAP have over traditional systems with significant memory? Do in-memory OLAP engines have advantages over the traditional OLAP engines backed by enough RAM to contain the entire cube(s)?

For example, if I use a MOLAP engine (SSAS) and GB / TB of RAM where the entire cube (or even star-schema) is RAM resident, what is the difference compared to something like TM1 / SAP HANA?

Not my field of expertise but as I understand it the difference in the majority of so-called in-memory OLAP databases (not a term I'm fond of, it's used as marketing pitch more than as a fair comparison of technologies) is column store indexes.

Column-Stores vs Row-Stores (How Different Are They Really) is a good intro to the technology if you're familiar with traditional OLTP and OLAP database structures.


I cannot restart mysql after mysql_upgrade I cannot restart MySQL.

I executed mysql_upgrade -u root -p,it did not work, and the mysqld.log contains the two errors:

  181130  8:21:21 [ERROR] Incorrect definition of table mysql.proc: expected column 'sql_mode' at position 14 to have type
  set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERRO

  181130  8:21:21 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type
  set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH'),
  found type
  set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_A

and my.cnf:

The error seems to indicate that the source version is MariaDB-10.3 and the destination version is MySQL-8.0.

There is no supported migration path in-place from MariaDB to MySQL.

Recommend taking a mysqldump of the databases and a copy of the database users and grants to recreate.


how to copy data from SQL Server table to excel Spreadsheet thru Linked Servers I try to copy data from a table in SQL Server 2005 into a excel spreadsheet. Since that sheet is linked to some external charts, so I have to overwrite the existing data with the updated data from SQL Server table. I want to do it through linked servers. But failed several times. Do someone knows how to do it?

Thanks in advance

I think you are looking at this from the wrong point of view.  You don't want SQL to upate your spreadsheet, you want your spreadsheet to connect to your database and get the latest data.

Create a local DSN  -- using "setup data sources (ODBC) -- then connect to that data source from excel.


Staging MySQL databases I'm building a Web/Web service application based on a number of (MySQL) databases. The databases are all built from externally derived data from dozens to hundreds of sources. My general plan is to have completely separate test, staging and production databases. The typical workflow is:

Check to see if any of the data distributions have been updated. The update frequency varies from daily to yearly, and everything in between.
If so, download the data, perform whatever transformations are required to turn it into SQL, and build a test database
Run unit tests with the new data
If everything passes, load the new data to a staging database
Run tests again on the staging database (because I'm paranoid)
Once a day, switch the production database to the staging database

All of the databases are read-only, once they're built. I very much like the idea of multiple versions of the database(s), but implementing this has me confused. Specifically:

Because several of the databases have foreign key constraints, I can't just drop each table in turn and recreate it. I don't see any choice but to delete the whole database and rebuild it, but then I'd need to recreate all of the grants, and I don't want to have to run something with database admin privileges like this on a daily basis.
I don't know how to swap the staging and production databases in a way that won't cost down-time.

I've looked for best practice in building databases within this general model, and come up dry. So, two questions:

Am I on the right track with this general design, and
What do I need to implement it?

You are working too hard.

Plan A -- RENAME TABLE

Assuming the production database is really readonly, you don't need FKs, just the indexes they generate.  (And you may not need all of them.)

So, before testing in Staging, turn off FK checks and drop all the FKs.  Then consider dropping any excess indexes.  Then test.

Since there is no RENAME DATABASE, you are stuck with a rather rapid series of

RENAME TABLE live.table1 TO old.table1,
             live.table2 TO old.table2,
             ...
             new.table1 TO live.table1,
             ... ;

Plan B -- 2 Instances

Better than that would be to have two instances of MySQL on the same server or (better yet) on different servers.  One for staging, one for production.  The flip-over does, however, require some way to redirect clients.  This may involve port number, proxy, IP address, etc.  Look at HAProxy and MaxScale for achieving the flip for you.  (They probably work for either case -- 2 instances on one server or 2 servers.)


mongodb array query woes I am struggling with array matching and getting multiple records back.

"players" : [
    {
        "pname" : "player_4",
        "loc" : [ -2.2, 1, 0]
    },
    {
        "pname" : "player_2",
        "loc" : [ 9, 1, 5 ]
    }
]

Now assuming above data,

How do I project correctly where I get all the "loc" records that match the criterion?

Let's say loc.0 <15 should give both records, loc.0>0 should only give "player_2" etc.

I tried "players", "players.$". Without the $ I get all the data in "players". With the $, I get the first match. Obviously I am missing something big somewhere.

My trials below:

db.worlds.find({"name":"w1", "players.loc.0":{$lt:5}},{"players":1})

db.worlds.find({"name":"w1", "players.loc.0":{$lt:15}},{"players":1})

db.worlds.find({"name":"w1", "players.loc.0":{$lt:15}},{"players.$":1})

This is on Mongo 3.6.3.

Maybe you can try with $elemMatch :
https://docs.mongodb.com/manual/reference/operator/projection/elemMatch/


Restore Oracle tablespace to its state from 2 days ago I have multiple test tablespaces in an  Oracle SID.  One of the tablespaces is updated with wrong data. It worked fine 2 days ago. I want to restore the tablespace to its state from 2 days ago.

I tried to restore the tablespace from the Coldbackups, but during startup it gave the error :

ORA-01113: file 10 needs media recovery
ORA-01110: data file 10: '/user/oracle/metadata/lmptest/gkpdbspace.dbf'

And

recover database

or

recover datafile '/user/oracle/metadata/lmptest/gkpdbspace.dbf'

didn't work.

You probably tried to restore and then recover the database. When you recover the database all the files should be in sync before you can open the database for work. The error shown tells you that some of your data files are not in sync with the rest of the database.

What your really need to use to recover a tablespace or a set of tablespaces is Tablespace Point-in-Time Recovery (TSPITR). You can do it manually or with RMAN. In the latter case the command might look like:

recover tablespace app_data
until SCN 1234567
auxiliary destination '/tmp/auxdb';

And you wouldn't need to configure the auxiliary database manually. With user-managed TSPITR, on the other hand, you have to configure the auxiliary database first, and then use Transportable Tablespaces feature (see the section "Introduction to Transportable Tablespaces" in Database Administrator's Guide) to move the data files along with metadata to the target database.

For more details read "RMAN Tablespace Point-in-Time Recovery (TSPITR)" and "Performing User-Managed TSPITR" sections in Database Backup and Recovery Advanced User's Guide, and also "Tablespace Point-in-Time Recovery" section in Concepts.


How To dump data from Oracle to Vertica I want to transfer data from Oracle to Vertica and I want to know what is best way to do it?
I s there a tool for it?

I tried to dump the Oracle data and load it to Vertica using 'Copy Direct' command but I fount lots of problems specially in the data and timestamps columns.

Try to export the data into csv format, and recreate the table in Vertica using the DDL from Oracle. Check the types as they differ from Oracle to Vertica.

Example: assuming the following table structure on Oracle:

CREATE TABLE PEOPLE
(
    ID NUMBER (10),
    NAME VARCHAR2 (50),
    ADDRESS VARCHAR2 (250),
    PRIMARY KEY (ID)
)

Assuming a compatible table structure on Vertica:

CREATE TABLE PEOPLE
(
    ID BIGINT NOT NULL    /*ORACLE datatype: NUMBER(10, 0)  */,
    NAME VARCHAR(50)      /*ORACLE datatype: VARCHAR2(50)   */,
    ADDRESS VARCHAR(250)  /*ORACLE datatype: VARCHAR2(250)  */,
    PRIMARY KEY (ID)
);

Load the table content using the copy command in Vertica:

copy "table name" from 'full csv file path'
    delimiter ',' null as '' exceptions 'full error file path';

Done!


"Thread was being aborted" error when inserting 25k rows I need to store about 25k rows worth of data into a single table about once a week. To achieve this, I'm creating an object (created from parsing XML), and sending the data within that object to a stored procedure. There's an object containing data for each row in the table.

Here's the stored procedure:

ALTER PROCEDURE [dbo].[BankImport]
    -- Add the parameters for the stored procedure here
    @bankName nvarchar(50) = null,
    @shortBankName nvarchar(50) = null,
    @branchName nvarchar(50) = null,
    @sortCode int,
    @addresseeName nchar(60) = null,
    @postalName nchar(60) = null,
    @addressLine1 nchar(100) = null,
    @addressLine2 nchar(80) = null,
    @cityOrTown nchar(50) = null,
    @areaOrCounty nchar(60) = null,
    @postCode nchar(12) = null,
    @fasterPayments nchar(10) = null,
    @directDebits nchar(10) = null,
    @chaps nchar(10) = null,
    @chequeCreditClearing nchar(10) = null

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM [dbo].[banks] WHERE sortCode = @sortCode)

        UPDATE [dbo].[banks]
        SET @bankName = bankName,
            @shortBankName = shortBankName,
            @branchName = branchName,
            @sortCode = sortCode,
            @addresseeName = addresseeName,
            @postalName = postalName,
            @addressLine1 = addressLine1,
            @addressLine2 = addressLine2,
            @cityOrTown = cityOrTown,
            @areaOrCounty = areaOrCounty,
            @postCode = postCode,
            @fasterPayments = fasterPayments,
            @directDebits = directDebits,
            @chaps = chaps,
            @chequeCreditClearing = chequeCreditClearing
        WHERE sortCode = @sortCode

    ELSE

        INSERT INTO [dbo].[banks] VALUES (
            @bankName,
            @shortBankName,
            @branchName,
            @sortCode,
            @addresseeName,
            @postalName,
            @addressLine1,
            @addressLine2,
            @cityOrTown,
            @areaOrCounty,
            @postCode,
            @fasterPayments,
            @directDebits,
            @chaps,
            @chequeCreditClearing
        )

END

It works okay on smaller datasets, but the full-size contains each branch for each bank in the UK. It manages to get through to about 15k rows before giving this error:

Thread was being aborted.

Is this likely to be more of a time-out issue, or inefficiency, or is there something else that I'm missing?

Strongly recommend you look into Table-Valued Parameters and treat your insert/update as a set (using MERGE probably) instead of calling a singleton stored procedure 25,000 times. You can skip a lot of steps here (in C# you can just stuff your 25K rows into a DataTable, for example, making it easy to pass to a stored procedure in a single call), and reduce overhead significantly.

If you create a type for your bank rows:

CREATE TYPE dbo.BankRow AS TABLE
(
  SortCode int PRIMARY KEY,
  BankName nvarchar(50),
  ShortBankName nvarchar(50),
  BranchName nvarchar(50),
  AddresseeName nchar(60),
  PostalName nchar(60),
  AddressLine1 nchar(100),
  AddressLine2 nchar(80),
  CityOrTown nchar(50),
  AreaOrCounty nchar(60),
  PostCode nchar(12),
  FasterPayments nchar(10),
  DirectDebits nchar(10),
  Chaps nchar(10),
  ChequeCreditClearing nchar(10)
);

Your procedure then might look like this:

ALTER PROCEDURE dbo.BankImport
  @BankRow dbo.BankRow READONLY
AS
BEGIN
  SET NOCOUNT ON;

  MERGE dbo.Banks AS b
    USING @BankRow AS tvp
    ON tvp.SortCode = b.SortCode
  WHEN MATCHED THEN
    UPDATE SET BankName = tvp.BankName,
               ShortBankName = tvp.ShortBankName,
               ...
  WHEN NOT MATCHED THEN
    INSERT (SortCode, BankName, ShortBankName, ...)
    VALUES (tvp.SortCode, tvp.BankName, tvp.ShortBankName, ...)
END
GO

So now you can pass your DataTable into the stored procedure once instead of calling the singleton stored procedure 25,000 times. You'll have to add the other columns where I put ... and you'll also have to adjust your C# code to work with a TVP.

I wrote up a quick article on TVPs here, including a quick example on C# syntax:

http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql

And Erland's article is useful as well:

http://sommarskog.se/arrays-in-sql-2008.html


AG secondary DB files location I have two databases db-1 (primary) and db-2 secondary
now I moved all files location on DB-2 (system databases and default location in DB settings) to be :Q instead of :D
the problem is when I create new availability group on primary replica DB-1 I find that files created in old location (:D) on secondary replica !
how can I solve this issue so that when I create new AG files will be created automatically in the new drive :Q not :D on the secondary replica DB-2
Thanks in advanced

If you want the file locations to be different on the secondary, which you should not do if possible, you must manually restore a backup of the primary database.  Instructions are here: Prepare a secondary database for an Always On availability group


Ternary Relationship in an ERD: What would the cardinalities look like in this situation? There are four entity types in the business domain of relevance:
USER, COURSE, MODULE and TOPIC.

The business rules go something like this:

A Course contains one or more Modules, and
each Module contains one or more Topics.

So, I have drawn the following entity-relationship diagram (ERD):

Now,

a User can study many Courses, and
a Course can be taken by many Users.

I want to maintain the progress of a user in that course and so I'm storing an instance of Topic in the studies relationship.

Relational representation

My Relational Table looks like this:

The topic_id column in this table stands for the Next topic the user needs to complete. So whenever the user enrolls for a course I fetch the first topic of the first module of the course and update the row. So (user_id, course_id) becomes my composite primary key. Also, when a user completes a topic, I fetch the next topic in the list and update the instance.

The question

How do I represent relationship in the ERD with the constraints mentioned above?

Since there are three participating entity types, does this make this a ternary relationship, and, if so, what should the cardinalities be like?

Any insight would be much appreciated.

Somewhat unintuitively you should drop the reference to course and just leave the references to user and topic in the Undergoing table. To answer directly your question: there should be only two participating entities so it is not a ternary relationship.

This is one of those interesting cases in normalization where Third normal form (3NF) is fulfilled but Boyce–Codd normal form (BCNF) is not. Both are desirable forms in normal database formalization. What breaks the BCNF is the dependency topic_id->course_id. In plain English: if you know the topic_id you can deduce the course_id also so you don't need an explicit reference.

When you have user_topics relation with references only to user_id and topic_id you can easily fetch the courses of a user with a regular JOIN (via module to course).

Here is more formally put the dependency that is allowed in 3NF but not in BCNF (Third normal form - Wikipedia):

  Every element of A-X, the set difference between A and X, is a prime attribute (i.e., each attribute in A-X is contained in some candidate key)

Boyce–Codd normal form (Wikipedia):

  Only in rare cases does a 3NF table not meet the requirements of BCNF. A 3NF table which does not have multiple overlapping candidate keys is guaranteed to be in BCNF. Depending on what its functional dependencies are, a 3NF table with two or more overlapping candidate keys may or may not be in BCNF.


Missing .msi files gives error while repairing SQL Server 2008R2 I've got a SQL Server 2008R2 SP1 database installed and I'm trying to run a repair on it using a 2008R2 install binaries.

In the middle of the repair loading, the window suddenly shuts down and gives me this error message, anyone knows what to do with that ? My searching skills have left me alone as of now.

The script provided by Microsoft in the Knowledge Base article:

How to restore the missing Windows Installer cache files and resolve problems that occur during a SQL Server update

...found the missing files and allowed me to advance a bit:

Save the script to your desktop
Run it using CMD

cscript scriptname.vbs scriptoutput.txt

Find all the copy commands in the text file and use them to populate your Windows installer folder. Those missing files are causing your issue.

EDIT

The step 3 needs a little more explaining:
The text file produced from the VBS script contains various recommendations and mostly suggests to copy the files from certain locations (more on that later) to the %windir%\installer path where they should be in order for any upgrade/patch/repair process to work.

In my case, I had sqlserver 2008R2 SP1 so the output textfile basically told me "I need certain vanilla files and certain SP1 files"

Here is the script asking for vanilla files, which can be found in a stock sqlserver2008r2 installer DVD

Copy "C:\Source\Sources SQL 2008\SQL 2008 R2\SQL2008R2_source\x64\setup\sql_engine_core_inst_msi\sql_engine_core_inst.msi" C:\Windows\Installer\2d620c2b.msi

And here is the script asking for SP1 files :

Copy "g:\10bb0cd00ccb163360\x64\setup\sql_engine_core_inst_msi\sql_engine_core_inst.msp" C:\Windows\Installer\2d8607e0.msp

SQLSERVER will remember where it found those files the first time it had to use them, but this location might not exist anymore if, as in my case, someone did tons of cleaning on the server and deleted all of you precious files.

You will need to modify the first part of the path so it matches the location of your extracted DVD and of your extracted SP1 patch. To extract the SP1 patch, you can run the following command in a CMD prompt

c:\path\to\file\SQLServer2008R2SP1-KB2528583-x64-ENU.exe /x

Notice, the /x at the end, this command will open a small window asking where you want the content to be extracted.


Can BCP Out cause resource contention I had these two things running in parallel in the same database -

BCP "SELECT * FROM [ServerName].[DatabaseName].[dbo].[TableName] WITH (NOLOCK)" queryout "\\LocationToMyOutputFile\TableName.csv" -c -S "ServerName" -T

A BCP out command on a PowerShell prompt which looked like above.
A stored procedure which was using the same database. The stored procedure was executing in the same database as the BCP out command (but was not using the same table). The SP drops a table and inserts records into it from a CTE (the CTE is made from tables of same database in question).

BCP out has 1.16 Billion records to output and it took 3565 seconds (~1 hour) to finish.
When the BCP out command was executing the SP kicked in and it took roughly 7 hours to finish. In general, the particular SP finishes in 15-20 minutes.

Here are a few things which I noticed while both were running in parallel -

The wait type for the BCP out command as seen from SQL Monitor was PAGEIOLATCH_SH.
The wait type for the SP were PAGEIOLATCH_SH and PAGEIOLATCH_EX.
While both of them were running in parallel the SP went into SUSPENDED state as seen when executing sp_who2.
After the BCP out command finished executing in about 1 hour, the SP continued to be in SUSPENDED state with very high DiskIO(which was continuously increasing) for another few hours.

After observing the above, these are the questions which come to my mind -

AFAIK, BCP out command doesn't lock the source table and should not affect anything, but seeing the wait types I can infer that BCP Out first loaded the entire 1.16 Billion records from the disk to memory and then output(read outputted) it to flat-file. This caused the SP to go into SUSPENDED state since memory was not available for it to load records from the disk. Is my understanding correct?
Secondly, when the BCP out finished executing, why didn't the SP change its state from SUSPENDED, since all resources would have been free. There was nothing else executing in the database other than the culprit SP. Is it because even after the BCP out finished it still had records in the memory which didn't get flush automatically?

Please let me know your thoughts as I am confused here whether my understanding is correct.

SQL Server version is Microsoft SQL Server 2014 (SP2-CU17-GDR) (KB4505419) - 12.0.5659.1 (X64).

I would guess these symptoms (PAGEIOLATCH_SH waits and longer execution times) are simply due to increased physical IO. SQL Server has to scan the entire source table during the BCP which may evict pages from buffer cache that the stored proc later references. When run by itself, the pages needed by the proc probably remain in memory.

Your assumption about BCP needing to read the entire table into memory before writing the result is incorrect. BCP streams results from the source query to the output file.


MySQL unknown column 'password_last_changed' When creating a user using this command

create user 'foo'@localhost';

this error is shown

ERROR 1054 (42S22): Unknown column 'password_last_changed' in 'mysql.user'

Using MySQL server version: 5.7.6 on linux

I tried to add it but I dont know its data-type
How can I fix this

From the 5.7.6 release notes:

  If you upgrade to this release of MySQL from an earlier version, you
  must run mysql_upgrade (and restart the server) to incorporate the
  changes to the mysql database. mysql_upgrade moves Password column
  values to the authentication_string column and removes the Password
  column. For nonupgraded installations that have no account_locked
  column, the server treats all accounts as unlocked, and attempts to
  lock or unlock and account produce an error.

Although you will find similar issues in every upgrade, so running this tool is a must. password_last_change was added in 5.7.4, so you must update the table definitions.

Please note that at the time, upgrades from 5.6 are supported, but not from 5.5 at the moment of writing these lines, so if you have upgraded directly from 5.5 or below you will likely find problems.


Storing many pandas DataFrames in SQLite with metadata I need some help with designing a database. My aim is to persistently store a number of pandas DataFrames in a searchable way, and from what I've read SQLite is perfect for this task.

Each DataFrame contains about a million rows of particle movement data like this:

              z            y            x  frame  particle
0     49.724138    45.642857   813.035714      0         0
3789  14.345679  2820.537500  4245.162500      0         1
3788  10.692308  2819.210526  1646.842105      0         2
3787  34.100000  2817.700000  1375.300000      0         3
3786   8.244898  2819.729167  1047.375000      0         4

Using sqlalchemy I can already store each DataFrame as a table in a new DataBase:

from sqlalchemy import create_engine
import pandas as pd

engine = create_engine("sqlite:////mnt/storage/test.db")

exp1.to_sql("exp1", engine, if_exists="replace")
exp2.to_sql("exp2", engine, if_exists="replace")
exp3.to_sql("exp3", engine, if_exists="replace")

But this is too basic. How can I store each DataFrame/experiment with a couple of metadata fields like Name, Date in such a way that later on it's possible to return all experiments conducted by a certain person, or on a specific date?

I will add more columns over time. Assuming each DataFrame/experiment has a column velocity, how could I retrieve all experiments where the mean temperature value is below or above an arbitrary threshold?

You've created 3 separate tables (well 2, pending the apparent typo?). If you want to unify the data, you probably shouldn't be forcibly overwriting target tables with if_exists="replace"

From the .to_sql() documentation

  replace: Drop the table before inserting new values.
  append: Insert new values to the existing table.

Assuming your similarly named files have the same schema, you can edit the last 3 lines as follows.

exp1.to_sql("exp", engine, if_exists="append")
exp2.to_sql("exp", engine, if_exists="append")
exp3.to_sql("exp", engine, if_exists="append")

This will insert all three datasets to a single table named exp instead of 3 separate tables.

If each csv isn't uniquely identified from the others within itself - for example if exp1.csv looks like this...

Name,Date,Temperature
Peter,2020-01-01,50
Paul,2020-01-01,55
Mary,2020-01-01,53
Jane,2020-01-01,49

...then you can append the experiment identifier to each dataset as needed in the dataframe. For example by...

>>> exp1['ExpName'] = 'exp1'
>>> exp1
    Name        Date  Temperature ExpName
0  Peter  2020-01-01           50    exp1
1   Paul  2020-01-01           55    exp1
2   Mary  2020-01-01           53    exp1
3   Jane  2020-01-01           49    exp1
>>>

...which will allow you to group by experiment in any follow-on SQL you may run against your database.

  ...how could I retrieve all experiments where the mean temperature value is below or above an arbitrary threshold?

...well given an arbitrary additional two datasets of...

➜  /tmp cat exp2.csv
Name,Date,Temperature
Peter,2020-01-02,51
Paul,2020-01-02,56
Mary,2020-01-02,54
Jane,2020-01-02,50
➜  /tmp cat exp3.csv
Name,Date,Temperature
Peter,2020-01-02,52
Paul,2020-01-02,57
Mary,2020-01-02,55
Jane,2020-01-02,51
➜  /tmp

...that you likewise appended the expN identifier to in the dataframe, then you would run the following SQL to retrieve experiments where the average temp was below 53

SELECT
    ExpName,
    AVG(Temperature)
FROM exp
GROUP BY ExpName
HAVING AVG(Temperature) < 53;

Which I'll leave to you to plug into SQLAlchemy as you like :)


Storing undetermined amounts of user information (MySQL) I've been looking into the best way of storing an undetermined amount of information submitted by a user. A friend of mine suggested using nested tables, however these don't appear to be a thing in MySQL.

The application will allow users to store pieces of information per day (each day is a blank slate so to speak)

What I have currently is

-Users
--ID
--email
--password

-Things
--UID (made from date and user ID)
--Thing1
--Thing2

This works fine. The UID is the users ID and the date combined (i.e 71420150404) as each day will be different but I'm open to changing this. The application checks to see if there are any entries for that UID and if there isn't, creates a new row.

The problem I have is I'd like the user to be able to select how many pieces of information they would like to add per day. So instead of the static 'Thing1, Thing2' the user could theoretically have this go up to 'Thing100', and I'm fairly sure adding these as columns isn't the best way to go about this.

I looked into if its possible to store an array in a cell and I'd access it like that through PHP but the research I came across all suggests I shouldn't do this. Creating a new table per user also seems very inefficient.

What is the best way to go about this?

EDIT - Including original PHP insertion code in response to an answer

Insert

$sql = "INSERT INTO things (uid, thing1, thing2)
VALUES (:uid, :thing1,:thing2)
ON DUPLICATE KEY UPDATE thing1 = :thing1, thing2 = :thing2";

Retrieve

$sql = "SELECT * FROM things WHERE uid=$uid";

You can use the same table with another PK(Primary Key) and just one thing field.

Example:

PK          PK
UID         thing_id  thing
71420150404 0000001   First thing
71420150404 0000002   Second thing

You can make a trigger in action BEFORE UPDATE so when you add a new row, the trigger make the job of increment the thing_id field, like SET NEW.thing_id=(SELECT MAX(thing_id)+1 FROM db1.Things WHERE UID=NEW.UID); and that's all.

With this you can add N things for the same UID.

EDIT:

I've added 2 field to your current design, thing_id and thing_date.

TEST:

mysql> # New Table structure
mysql> CREATE TABLE `test`.`Things` (
    ->   `UID` BIGINT NOT NULL,
    ->   `thing_id` INT(7) ZEROFILL UNSIGNED NOT NULL,
    ->   `thing` VARCHAR(255) NULL,
    ->   `thing_date` DATETIME DEFAULT NULL,
    ->   PRIMARY KEY (`UID`, `thing_id`));
Query OK, 0 rows affected (0.01 sec)

mysql> # First SELECT
mysql> SELECT * FROM test.Things;
Empty set (0.00 sec)

mysql> # First message
mysql> SET @UID="71420150404";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @MSG="Hello!";
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test.Things(UID,thing_id,thing,thing_date)
    -> SELECT
    -> @UID,
    ->     IFNULL(MAX(thing_id),0)+1,
    ->     @MSG,
    ->     NOW()
    -> FROM test.Things
    -> WHERE UID=@UID
    -> ON DUPLICATE KEY UPDATE thing=@MSG;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> # SECOND SELECT
mysql> SELECT * FROM test.Things;
+-------------+----------+--------+---------------------+
| UID         | thing_id | thing  | thing_date          |
+-------------+----------+--------+---------------------+
| 71420150404 |  0000001 | Hello! | 2015-10-06 15:00:20 |
+-------------+----------+--------+---------------------+
1 row in set (0.00 sec)

mysql> # Second message
mysql> SET @UID="71420150404";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @MSG="Hello2!";
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test.Things(UID,thing_id,thing,thing_date)
    -> SELECT
    -> @UID,
    ->     MAX(IFNULL(thing_id,0))+1,
    ->     @MSG,
    ->     NOW()
    -> FROM test.Things
    -> WHERE UID=@UID
    -> ON DUPLICATE KEY UPDATE thing=@MSG;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> # Third SELECT
mysql> SELECT * FROM test.Things;
+-------------+----------+---------+---------------------+
| UID         | thing_id | thing   | thing_date          |
+-------------+----------+---------+---------------------+
| 71420150404 |  0000001 | Hello!  | 2015-10-06 15:00:20 |
| 71420150404 |  0000002 | Hello2! | 2015-10-06 15:00:20 |
+-------------+----------+---------+---------------------+
2 rows in set (0.00 sec)

mysql> # Third message
mysql> SET @UID="71420150404";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @MSG="Did you see there\'s 3 messages?!";
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test.Things(UID,thing_id,thing,thing_date)
    -> SELECT
    -> @UID,
    ->     MAX(IFNULL(thing_id,0))+1,
    ->     @MSG,
    ->     NOW()
    -> FROM test.Things
    -> WHERE UID=@UID
    -> ON DUPLICATE KEY UPDATE thing=@MSG;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> # Last SELECT
mysql> SELECT * FROM test.Things;
+-------------+----------+----------------------------------+---------------------+
| UID         | thing_id | thing                            | thing_date          |
+-------------+----------+----------------------------------+---------------------+
| 71420150404 |  0000001 | Hello!                           | 2015-10-06 15:00:20 |
| 71420150404 |  0000002 | Hello2!                          | 2015-10-06 15:00:20 |
| 71420150404 |  0000003 | Did you see there\'s 3 messages?! | 2015-10-06 15:00:20 |
+-------------+----------+----------------------------------+---------------------+
3 rows in set (0.00 sec)

mysql> # fourth message
mysql> SET @UID="71420150404";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @MSG="4th!";
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test.Things(UID,thing_id,thing,thing_date)
    -> SELECT
    -> @UID,
    ->     MAX(IFNULL(thing_id,0))+1,
    ->     @MSG,
    ->     NOW()
    -> FROM test.Things
    -> WHERE UID=@UID
    -> ON DUPLICATE KEY UPDATE thing=@MSG;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test.Things;
+-------------+----------+----------------------------------+---------------------+
| UID         | thing_id | thing                            | thing_date          |
+-------------+----------+----------------------------------+---------------------+
| 71420150404 |  0000001 | Hello!                           | 2015-10-06 15:00:20 |
| 71420150404 |  0000002 | Hello2!                          | 2015-10-06 15:00:20 |
| 71420150404 |  0000003 | Did you see there\'s 3 messages?! | 2015-10-06 15:00:20 |
| 71420150404 |  0000004 | 4th!                             | 2015-10-06 15:06:01 |
+-------------+----------+----------------------------------+---------------------+
4 rows in set (0.00 sec)

mysql>

Every @ is a user defined variable, change them for your Php variables and everything work just the same.

YOUR QUERY:

INSERT INTO test.Things(UID,thing_id,thing,thing_date)
SELECT
    :uid,
    MAX(IFNULL(thing_id,0))+1,
    :thing1,
    NOW()
FROM test.Things
WHERE UID=:uid
ON DUPLICATE KEY UPDATE thing=:thing1,thing_date=NOW();


Install by command line I am using following command line to install SQL Server 2008 R2 but its not installing. Also, I am not getting any error or log in summary.txt.

  "D:\SQLEXPR32_x86_ENU (1).exe" /q /ACTION= Install /FEATURES=SQL
  /INSTANCENAME=nvt /SECURITYMODE=SQL /sapwd=abc
  /IACCEPTSQLSERVERLICENSETERMS

I've found this blog, and the solution the author described works like a charm for me.

Below is command line install command

setup.exe /QUIETSIMPLE /ACTION=install /FEATURES=SQL /INSTANCENAME=SQL2008
/SQLSVCACCOUNT=”NT Authority\System” /SQLSYSADMINACCOUNTS=”sreekanthpc\sreekanth”
/AGTSVCACCOUNT=”NT Authority\System” /SECURITYMODE=SQL /SAPWD=”Pa$$w0rd”
/SQLTEMPDBDIR=”D:\SQL2008\TempDB\\” /SQLUSERDBDIR=”D:\SQL2008\SQLData\\”
/SQLUSERDBLOGDIR=”D:\SQL2008\SQLLog\\”

I hope this helps


How do I loop through every table and alter the PK column like so? (Postgres) Using psql in Postgres 12, I need to alter every column in every table of my database that's:

int/bigint

not null

primary key

to:

identity column

auto-increment

with default value starting at max value of current rows

I know this should be doable using PL/PgSQL, but I'm not familiar with it.
Can someone help me out?

What I suggest you do is to start up psql using the -E option.
This will give you a "blow-by-blow account" of the SQL that's used to perform operations in psql.
From issuing the command man psql:

-E
--echo-hidden

Echo the actual queries generated by \d and other backslash commands. You can use this to study psql's internal operations. This
is equivalent to setting the variable ECHO_HIDDEN to on.

Then, perform your desired operations on a sample table in psql and copy and paste the resultant "background" SQL into your editor of choice. Then write a PL/pgSQL routine that cycles through the tables you wish to change which does the same thing - monkey-see, monkey-do!
As @a_horse_with_no_name pointed out, this request has been cross-posted (to StackOverflow here). As an advanced PL/pgSQL question, it probably belongs here. So, could you please put a line at the top of your question on StackOverflow pointing here and telling people that any answers are to be put here and not on StackOverflow. This eliminates duplication of effort on the part of those trying to help you - thanks! Please see this post on the topic.
p.s. welcome to the forum!


How to restore some percentage of backup file? I have a backup file size 10 GB in a remote location. When I tried to restore it in my pc it is giving error "insufficient storage."

Is there any alternative way to store some percentage of the data that includes the whole schema?

I am using SQL Server 2014 Enterprise.

You probably have an option of:

Create several filegroups and data files in these filegroups
Move some part of your data to new files using rebuild index command
When restoring your database from a backup specify remote location for some data files, and the other files restore locally.

Some useful links to begin with:

Move an Existing Index to a Different Filegroup (Microsoft Docs)
SQL Server Can Run Databases from Network Shares & NAS by Brent Ozar


Sequential GUID or bigint for 'huge' database table PK I know this type of question comes up a lot, but I've yet to read any compelling arguments to help me make this decision. Please bear with me!

I have a huge database - it grows by about 10,000,000 records per day. The data is relational, and for performance reasons I load the table with BULK COPY. For this reason, I need to generate keys for the rows, and cannot rely on an IDENTITY column.

A 64-bit integer - a bigint - is wide enough for me to use, but in order to guarantee uniqueness, I need a centralised generator to make my IDs for me. I currently have such a generator service which allows a service to reserve X sequence numbers and guarantees no collisions. However, a consequence of this is that all the services I have are reliant on this one centralised generator, and so I'm limited in how I can distribute my system and am not happy about the other dependencies (such as requiring network access) imposed by this design. This has been a problem on occasion.

I'm now considering using sequential GUIDs as my primary keys (generated externally to SQL). As far as I've been able to ascertain from my own testing, the only drawback to these is the disk space overhead of a wider data type (which is exacerbated by their use in indexes). I've not witnessed any discernible slowdown in query performance, compared to the bigint alternative. Loading the table with BULK COPY is slightly slower, but not by much. My GUID-based indexes are not becoming fragmented thanks to my sequential GUID implementation.

Basically, what I want to know is if there are any other considerations I may have overlooked. At the moment, I'm inclined to take the leap and start using GUIDs. I'm by no means a database expert, so I'd really appreciate any guidance.

I'm in a similar same situation. Currently, I'm using the sequential GUID approach and have no fragmentation and easy key generation.

I have noticed two disadavantages that caused me to start migrating to bigint:

Space usage. 8 bytes more per index. Multiply that by 10 indexes or so and you get a huge waste of space.
Columnstore indexes do not support GUIDs.

(2) Was the killer for me.

I will now generate my keys like this:

yyMMddHH1234567890

I'll be using a leading date plus hour and having a sequential part after that. That allows me to range-query my data by date without any addition index at all. This is a nice bonus for me.

I'll generate the sequential part of the bigint using a HiLo algorithm that lends itself well to being distributed.

Hope some of this transfers to your situation. I definitely recommend using bigint.


Can indexes really decrease performances in a low volume writes DB? I'm trying to do a bit of a clean up/review of the database I'm currently working on (which I didn't design, by the way). The DB is 'big' in term of objects (hundreds of tables, hundreds of SP, functions, a thousand triggers, looooots of columns, etc.) and it's designed, well...not that nicely. Not nicely at all. So, while I'm going through the long backlog of performances related issues that the customers have uselessly complained for in the last three years, I'd like to do some "easy fix" too.

Now, I've found an interesting number of things like 40% of the tables having no primary keys, 30% of tables having no indexes, and a lot of other really funny things (could go on for hours), but this question is focused on indexes.

I've been working on DBs for a while and I know that indexes can (and do) slow down the system during writes, no big surprise here. And yes, indexes still have to be stored somewhere so they increase the DB size, but...is there anything else aside from that?

The DB is ridiculously small, and the number of writes per seconds could probably be counted in writes per minutes without the need to go double digits, so I'm not concerned about those points.

I've written a bit of TSQL to find tables without indexes and 'lot' of records (like a table with four million records and not a single index or a primary key /facepalm), plus I've used sys.dm_db_missing_index_* to find what indexes the DBE think are needed.

So, bottom line: should I go on and blindly add indexes, or do I risk to kill system performances for some reason I'm not foreseeing?

You have a lot of questions and unknowns (no info on the size of db, hardware - RAM, CPU, Disk (san or local ssd), is the server VM or physical, do you have baseline as to what looks good or what is acceptable, sp_configure settings, do you run maintenance - update stats, index reorg/rebuild etc) in your question.

  should I go on and blindly add indexes

No. Read this first - Don't just blindly create those "missing" indexes!

Once you read that, do a health check sp_Blitz: Overall Health Check
Review and Address the high priority ones.
Tune your indexes - sp_BlitzIndex: Tune Your Indexes

Above will get you in good shape. If you see any issues, post the question back and we will help you :-)


Splitting a table based on business logic This is mostly a design question and I'm looking for any feedback about the merits or flaws of this approach.

Typically, I break database design into two categories, Business Logic and Recorded Data.  Business Logic are those things which bring meaning to the Recorded Data.  Eg, PartSerialNumber has meaning because we have parts with serial numbers.

So I'd create a structure like...

CREATE TABLE PartsTable
{
    PartID INT, -- NOT NULL IDENTITY PK...
    PartSerialNumber VARCHAR(20), -- NOT NULL
    CreateDate DATE, -- NOT NULL, maybe default
    -- other stuff related to the specific part
}

 -- this is a business logic table, the codes have meaning to the business
CREATE TABLE ErrorCodes
{
    ErrorCode VARCHAR(5), -- NOT NULL, PK
    ErrorDescription VARCHAR(MAX),
    -- OTHER STUFF FOR THE ERROR CODES
}

Then we have a logging system to catch errors,

CREATE TABLE PartErrors
{
    PartID INT , -- NOT NULL FK to PartsTable
    ErrorCode VARCHAR(5) -- NOT NULL, SEARCHED ON, FK to Business logic table
    ErrorDate DATE -- NOT NULL, when the error occurred, SEARCHED ON
    -- other search terms
}

Now, there is additional information about each error, but these are not searched on; maybe something like the clock frequency of the part when the error occurred.

So then I'd create another table

CREATE TABLE PartErrorsData
{
    PartID INT, -- not null, fk to PartsErrors
    ErrorCode VARCHAR(5), -- NOT NULL FK TO PARTSERRORS
    ErrorDate DATE, -- not null FK to PartsErrors
    ClockFrequencyHz INT, -- this may be null if the logger could not read the freq
    MachineNumber INT -- may be null if the logger cannot comm with the machine
    -- maybe other stuff, again, nothing that is searched upon, nor in the BL
}

So the question:  Is there any merit in separating these tables or should I have simply put all the columns from the PartErrorsData table into the PartsErrors table?

It seems like we can somewhat accomplish this with an additional index on the fields which are searched.  I'm somewhat torn between using an index on the searching fields and breaking the search fields from the data fields.

It is important to keep in mind that the fields on the search table are business logic fields; they relate to a table containing BL.  The Data table is pretty free form and is simply recording values from feedback files.

I personally like this approach as it clearly defines how the BL relates to the data so that we can join additional tables if necessary.  For instance, if there is another system which has Error related data, we can join it to the PartErrors table without having to worry about the data, just the BL.

EDIT:
Updated the tables so that the compound key is unique.  This was a mistake with the original question.  Of course it would have to have all the data required to correlate to an instance of the error.

Another note in response to the first answer.  We have [at least] 3 tables of different error data.

Some parts may provide clock freq, and machine id.  Other parts may report temperature surrounding the part, voltage coming into the part, etc.

So, using one table would have NULLs in all fields which the part does not report.

Regarding data which has no meaning to business, a clock frequency does not have meaning without the part.  A value recorded by the recording mechanism could have errors.  Maybe we recorded the frequency as 100Hz, but it was supposed to be 100 MHz.  The VALUE 100 does not have meaning until we bring it to light with the BL.  This part has a clock which is measured in MHz.

However, the value 00-139-228-AA has meaning to the business as a part number so that an incorrectly recorded part number &&^%% would be noticed immediately as it does not correlate back to the BL.  The mistaken value is not a catch-able error.

I cannot imagine any data that does not have a value to the business. If it doesn't have value why store it? Even technical logs have business value ("If we don't have these logs it is 50% more expensive to fix bug").

There are a few valid reasons to split a table:

Partition hot and cold data for cache efficiency
Too many columns (>1000 or so)
Many columns almost always null

Not sure what you gain by splitting off data by layering concerns, though. Everything gets harder to understand and to maintain (not easier).

My recommendation clearly is to have only one table. Don't artificially split the data.


How is encoding done in SQL database tables for pattern matching? Are strings in table columns represented as bit patterns or Unicode?  Does the database engine bring them into memory in a different representation character set from the disk-persisted representations for optimal performance?

I want to know for the purposes of index creation and optimal query writing.  Select statements pull data that matches a where clause.

Here is an example query:

select name from carTable where name =  'FordEscort97'

Here is the carTable:

name                        columnA columnB     columnC
FordEscort91
FordEscort92
FordEscort93
FordEscort94
FordEscort95
FordEscort96
FordEscort97
FordEscort98

Would the queries perform faster if the column in the table was designed to have the year at the left of the name in the strings that are stored?  (assuming the year component of the string was the most unique)

If there is a character-by-character, left-to-right matching process, the process could be more resource intensive than evaluating bit pattern hashes representing the strings being compared.  If the pattern being matched (the anchor string in the where clause) was a substring with a wildcard, each comparison operation would be more CPU intensive because if there are characters in the column that start out matching the wild card in a leftmost moiety of the string, a rightmost moiety may start out as a second potential match late in the string comparison.  Early non-matching recognition would obviate the need for as much evaluation.  But I don't know for sure that there is a character-by-character pattern match.

There is an implication for creating indexes on unique columns.  While a consideration should be made for a likelihood of uniqueness of strings in a column, designing data in the column can be arbitrary.  Could an index provide a greater benefit if primary column's data had its uniqueness of the string happening mostly within the leftmost characters compared to the rightmost characters?  Does it depend on the type of SQL you are using (e.g., Oracle, MySQL, Postgres, etc.)?  Sometimes underlying data is stored in hexadecimal format or with bit patterns.

Are strings in table columns represented as bit patterns or Unicode?

Computers only deal with 1's and 0's (i.e. binary); datatypes indicate how to interpret that info.

  Would the queries perform faster if the column in the table was designed to have the year at the left of the name in the strings that are stored? (assuming the year component of the string was the most unique)

If your data has "components" that need to be parsed out, that will never perform well regardless of RDBMS or indexing. The entire purpose of having different fields and different datatypes is to represent each component individually according to what it actually is. As @Michael pointed out in his comment, your one field is poorly modeled and should be three fields:

Make  VARCHAR(something)
Model VARCHAR(something)
Year  INT (or maybe SMALLINT)

Then you can index them as appropriate. Comparing 91 (or actually 1991) to 1991 as a numeric comparison is faster than comparing that value as a string to partial-match against 1991FordEscort, of even if it were a separate string field. The degree to which it is faster is related to several factors, including the size of the field, the field's collation, and vendor-specific implementations and features.

  If there is a character-by-character, left-to-right matching process, the process could be more resource intensive than evaluating bit pattern hashes representing the strings being compared.

Left-to-right vs right-to-left, case-sensitive vs case-insensitive, etc is usually determined by the collation of the field and the language/locale/lcid of the database. But searching on fields can't be matching hashes as hashes can only rule out exact matches and cannot prove a positive match (due to collisions) or help with partial matches. If you need very string character matching, look into using a BINARY collation.


postgres 9.1: role public does not exist I understand public is not a typical role in postgres:

dav-gis=# \duS public
           List of roles
 Role name | Attributes | Member of
-----------+------------+-----------

But why can I revoke all privileges on a schema from public, but not assign another role to public? E.g.,

db=# REVOKE ALL PRIVILEGES ON SCHEMA "eia" FROM "public";
REVOKE
db=# GRANT "eia-ro" TO "public";
ERROR:  role "public" does not exist

I'd like to only give public users read access through the read-only role, but perhaps this is just out of sync with how public access works in Postgres.

As @Evan Carroll said, there is no "public" role. The manual has this to say:

  The key word PUBLIC indicates that the privileges are to be granted
      to all roles, including those that might be created later. PUBLIC
      can be thought of as an implicitly defined group that always
      includes all roles. Any particular role will have the sum of
      privileges granted directly to it, privileges granted to any role
      it is presently a member of, and privileges granted to PUBLIC.

...

  Unlike the case with privileges, membership in a role cannot be
      granted to PUBLIC.


How to replace and overwrite all existing objects in Oracle with impdp for full database import? I want to replace all the existing objects in my database, not just tables, into my current database.

I ran expdp with the parameter full=yes as the system user, who I granted datapump_imp_full_database while logged in as sysdba, from the instance I am importing from. I ran impdp on the target database with the parameter table_exists_action=replace, but (understandably) only existing tables were replaced, but procedures, functions, views were not.

Is there an equivalent of table_exists_action for all objects? If not, how can I achieve this?

Both databases are Oracle 12c on Windows 10.

I don't think there is a "Replace" option for non-table objects like procedures, packages etc.

The best option would be to drop the schema entirely before the datapump import.  This way, datapump will re-create the schema and all of the contained objects.


How to optimize this query on Postgres I'm trying to make a relatively simple query, but it's taking much longer than I'd expect. I have an index in place, but it doesn't seem to be helping much.

Here's the query. It sometimes takes up to 20-30 seconds to execute:

SELECT "schedules".*
FROM "schedules"
WHERE "schedules"."client_data_provider_id" = '3001-753'
AND "schedules"."practice_id" = 753
AND (date_scheduled >= '2016-01-12')

Here's the table, with indices:

                                          Table "public.schedules"
        Column              |            Type             |                       Modifiers
----------------------------+-----------------------------+--------------------------------------------------------
 id                         | integer                     | not null default nextval('schedules_id_seq'::regclass)
 practice_id                | integer                     | not null
 data_provider_id           | character varying(255)      | not null
 source                     | character varying(255)      |
 type                       | character varying(255)      |
 client_data_provider_id    | character varying(255)      | not null
 client_pms_id              | character varying(255)      | not null
 patient_data_provider_id   | character varying(255)      | not null
 patient_pms_id             | character varying(255)      | not null
 date_scheduled             | date                        | not null
 duration                   | integer                     |
 status                     | character varying(255)      |
 reason                     | text                        |
 notes                      | text                        |
 resource_id                | character varying(255)      |
 resource_name              | character varying(255)      |
 site_id                    | integer                     |
 api_create_date            | timestamp without time zone |
 api_last_change_date       | timestamp without time zone |
 api_removed_date           | timestamp without time zone |
Indexes:
    "schedules_pkey" PRIMARY KEY, btree (id)
    "index_schedules_change_date_for_query" btree (practice_id, api_last_change_date DESC) WHERE api_last_change_date IS NOT NULL
    "index_schedules_create_date_for_query" btree (practice_id, api_create_date DESC) WHERE api_create_date IS NOT NULL
    "index_schedules_on_api_last_change_date" btree (api_last_change_date)
    "index_schedules_on_client_data_provider_id_and_date_for_query" btree (practice_id, date_scheduled, client_data_provider_id)
    "index_schedules_on_practice_id" btree (practice_id)
    "index_schedules_on_practice_id_and_date_scheduled" btree (practice_id, date_scheduled)
    "index_schedules_on_data_provider_id" btree (data_provider_id)

Here's the results of explain on the query:

=> EXPLAIN for: SELECT "schedules".* FROM "schedules"  WHERE "schedules"."client_data_provider_id" = '3001-753' AND "schedules"."practice_id" = 753 AND (date_scheduled >= '2016-01-12')
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Index Scan using index_schedules_on_client_data_provider_id_and_date_for_query on schedules  (cost=0.08..14.49 rows=1 width=706)
   Index Cond: ((practice_id = 753) AND (date_scheduled >= '2016-01-12'::date) AND ((client_data_provider_id)::text = '3001-753'::text))
(2 rows)

The schedules table has almost 4.5 million records. Only about 120,000 records have practice_id = 466, and 55 records with client_data_provider_id = '3001-753'

As you can see, there are multiple indices on the table, including one that I thought would work specifically for this query (index_schedules_on_client_data_provider_id_and_date_for_query).

We're aiming for <1 sec response. 20-30 is way too long. How would I go about improving the performance of this query?

An index on (practice_id, client_data_provider_id, date_scheduled) would be better than the one you have (practice_id, date_scheduled, client_data_provider_id), for this particular query.

Notice the difference in order. When there are multiple equality (=) conditions and one range condition (>=, >, between, etc) in the where clause, it's better to have an index with first the columns that are checked with equality and last the column in the range condition.

This way, the index scan will have to cover a much smaller part of the index, only the values with practice_id = 753 and client_data_provider_id = '3001-753' and date_scheduled >= '2016-01-12', which is exactly the rows you want.

With the current index, it will have to scan the part of the index with practice_id = 753 and date_scheduled >= '2016-01-12' and then reject the largest part (all those that don't have client_data_provider_id = '3001-753').


store two tables into a single table in mySQL create table tempA (txt1 int(3))

insert into tempA(txt1) values(25),(null),(77),(44)

create table tempA1 (txt2 int(3))

insert into tempA1 (txt2) values (null),(null),(null),(11),(88)

Can any one suggest me, to insert these two table into a single table(tempA and tempA1) into temp table. But the condition is like the temp table should hold only the values.

Thanks in Advances

If you want to stream changes from tempA and tempA1 to temp, you can use trigger to do this
create trigger for both tables

Example:

DELIMITER $$
CREATE TRIGGER `insert_to_temp` AFTER INSERT
ON `tempa`
FOR EACH ROW BEGIN
    if new.txt1 is not null THEN
        insert into temp values(new.txt1);
 END IF;
END$$
DELIMITER ;

And do this for tempa1

but if you want to do a batch insert you can run:

insert into temp select * from tempa where txt1 is not null union select * from tempa1 where txt2 is not null


Oracle BLOB store performance LOBs in Oracle are stored in-row when they are less than 4K in size and out-of-row otherwise.

I'm wondering how do SECUREFILE storage options COMPRESS and ENABLE STORAGE IN ROW work?

I have a lot of XML documents that are 4-10K in size that could easily be compressed to fit in under 4K. Would COMPRESS option result LOBs getting stored in-row therefore improving performance? Or does Oracle make in-row/out-of-row storage decision based on uncompressed size?

Consider using de-duplication if you have lots of identical XML - documentation link.

I wrote a huge unit test, but it turns out that SecureFile LOBs are always stored in a Lob Segment outside the row!


error writing input to commandERROR: Invalid username and/or password This is the error I have been getting on my RMAN notifcations on my backup jobs for the last day or so. The title is exactly how the message is formatted.

~~~End Step Output/Error Log~~~
Error Log:BackupOp
error writing input to commandERROR: Invalid username and/or password

~~~End Step Output/Error Log~~~

I only have 2 backup jobs and both are affected by this. Not sure how much information here is useful but what I have been looking at is the SYS account the jobs are using is not locked out.

select account_status from dba_users where username = 'SYS';

ACCOUNT_STATUS
--------------------------------
OPEN

The emagent.trc log is showing this pair of lines for every backup job since the first one failed.

2019-02-06 23:44:26,791 Thread-14472 ERROR command: failed to write to stdin of process 6228: (error=232, no message available)
2019-02-06 23:44:26,791 Thread-14472 ERROR command: nmejcc_feedInput: received -11 from process write

Restarting the DBConsole service has no affect and the errors persist.

I can manually run full and archivelog backups just fine as sysdba. I see no reason to blame RMAN since I can run the backup rman scripts just fine. I think this is just an EM issue.

I got more information when I tried to rebuild another backup job in EM using the credentials. I got the same error when I tried to use the same Host Credentials I got the same Invalid username and/or password error on the page.

I could not find anything authoritative on the subject but I read a list of prerequisites from this Oracle Community thread. Paraphrased..

  The user needs to be member of the ora_dba group and have local security privilege Log on as a batch job.

In my case the former was true but the latter was false. An admin on the team was "cleaning" up pcs in an OU and moved this server into another with different GPOs applied.

So in the end, I gave the local account, used as Host Credentials, Log on as a batch job rights and the backup jobs continued to work again.


I am trying to write a join using temporary table in hive I am trying to write a join using subquery/ temporary table in hive but it doesn't seem to be working:
create temporary table  q as select server, max(time) as time from ghs group by header.server;

create temporary table new_joined as
select q.server,ghs.proccount, ghs.net from ghs right outer join
q on ghs.server = q.server;

the first query gives me around 23k rows, I want the joined table also to give me only 23k rows but I see that this is joining with each row in ghs.
ghs table is like superset having 1.2 million records.
Can someone help me understand what I am doing wrong?

I just had to add time also to my join condition on clause and I got the desired reult.


If the Database is in Readonly mode how can we drop/remove those unused roles In SQL Server, if the Database is in Read_only mode, can we drop/remove unused roles? Please let me know.

Thanks in advance

If a database is in READ_ONLY mode, it is completely read-only. The read-only-ness is not limited to user data, but anything contained in the database. This means that both DML commands (ie, INSERT, UPDATE, DELETE), and DDL commands (ie, CREATE, ALTER, DROP) are prohibited.

If the database is a read-only replica of an Availability Group, or a read-only log-shipping secondary, then you would need to drop the roles on the primary replica, and the normal data movement will replicate those changes to your read-only secondary. It is not possible for the secondary to have different security roles than the primary.

If your database is a stand-alone database that has been put into READ_ONLY, then you would need to set the database to read-write before dropping the unused role(s), then set the database back to read-only:

USE master
ALTER DATABASE [<MyReadOnlyDatabase>] SET READ_WRITE;
GO
USE [<MyReadOnlyDatabase>];
DROP ROLE [<OldUnusedRole>];
GO
USE master;
ALTER DATABASE [<MyReadOnlyDatabase>] SET READ_ONLY;


SQL Server Index Update Deadlock I have 2 queries which when run at the same time are causing a deadlock.

Query 1 - update a column which is included in an index (index1):

update table1 set column1 = value1 where id = @Id

Takes X-Lock on table1 then attempts an X-Lock on index1.

Query 2:

select columnx, columny, etc from table1 where {some condition}

Takes an S-Lock on index1 then attempts an S-Lock on table1.

Is there a way to prevent the deadlock while maintaining the same queries? For example can I somehow take an X-Lock on the index in the update transaction before the update to ensure the table and index access are in the same order - which should prevent the deadlock?

Isolation level is Read Committed. Row and page locks are enabled for the indexes. It's possible that the same record is participating in both queries - I can't tell from the deadlock graph as it doesn't show the parameters.

Deadlock graph

Is there a way to prevent the deadlock while maintaining the same queries?

The deadlock graph shows that this particular deadlock was a conversion deadlock associated with a bookmark lookup (an RID lookup in this case):

As the question notes, the general deadlock risk arises because the queries may obtain incompatible locks on the same resources in different orders. The SELECT query needs to access the index before the table due to the RID lookup, whereas the UPDATE query modifies the table first, then the index.

Eliminating the deadlock requires removing one of the deadlock ingredients. The following are the main options:

Avoid the RID lookup by making the nonclustered index covering. This is probably not practical in your case because the SELECT query returns 26 columns.
Avoid the RID lookup by creating a clustered index. This would involve creating a clustered index on the column Proposal. This is worth considering, though it appears this column is of type uniqueidentifier, which may or may not be a good choice for a clustered index, depending on broader issues.
Avoid taking shared locks when reading by enabling the READ_COMMITTED_SNAPSHOT or SNAPSHOT database options. This would require careful testing, especially with respect to any designed-in blocking behaviours. Trigger code would also require testing to ensure the logic performs correctly.
Avoid taking shared locks when reading by using the READ UNCOMMITTED isolation level for the SELECT query. All the usual caveats apply.
Avoid concurrent execution of the two queries in question by using an exclusive application lock (see sp_getapplock).
Use table lock hints to avoid concurrency. This is a bigger hammer than option 5, as it may affect other queries, not just the two identified in the question.

  Can I somehow take an X-Lock on the index in the update transaction before the update to ensure the table and index access are in the same order

You can try this, by wrapping the update in an explicit transaction and performing a SELECT with an XLOCK hint on the nonclustered index value before the update. This relies on you knowing for certain what the current value in the nonclustered index is, getting the execution plan right, and correctly anticipating all the side-effects of taking this extra lock. It also relies on the locking engine not being smart enough to avoid taking the lock if it is judged to be redundant.

In short, while this is feasible in principle, I do not recommend it. It is too easy to miss something, or to outsmart oneself in creative ways. If you really must avoid these deadlocks (rather than just detecting them and retrying), I would encourage you to look instead at the more general solutions listed above.


How do I modify a JSON object? I have a table "raw_data" in which I have a field "data" containing a json array like:

{'a' : 1,
'b' : 2,
'c' : 3}

I would like two different things:

Append a new item like {'d' : 4} at the end of the array, that would give me :
{'a' : 1, 'b' : 2, 'c' : 3, 'd' : 4}
Update the key 'c' with the value 5 in this array, that would give me:    {'a' : 1, 'b' : 2, 'c' : 5}
Upsert the already existing key 'd' with the value 6 in this array, that would give me:    {'a' : 1, 'b' : 2, 'c' : 3, 'd' : 6}

I found this post, but couldn't manage to understand how it applies to my specific case

The post you linked to was mine. That was on an array, but the operator is the same for an object (||) concatentation.

Append a new item like {'d' : 4}

SELECT $${"a":1,"b":2,"c":3}$$::jsonb || '{"d":4}';

Update the key 'c' with the value 5

SELECT ($${"a":1,"b":2,"c":3}$$::jsonb || '{"d":4}') || {"c":5}';

Upsert the already existing key 'd' with the value 6

SELECT (($${"a":1,"b":2,"c":3}$$::jsonb || '{"d":4}') || '{"c":5}') || '{"d":6}';

End result,

{"a": 1, "b": 2, "c": 5, "d": 6}


Building and analyzing trends in a database I am quite new to working with databases but have spent a few hours reading about XML and trying to understand the use of CML for building databases and find myself completely at a loss.
Here is what I hope to do :
1. I have a few hundred xml files storing the service and operation history of a instrument with the following format :

<history title="History Report">
   <infos>
      <record date="01/01/2000" time="24:00">
         <item>
         <name>Name</name>
         <comment>SYS1</name>
         </item>
         <item>
         <name>Catalog No</name>
         <comment>ABCD</name>
         </item>
         .
         .
         .
         <item>
         <name>Country</name>
         <comment>XYZ</name>
         </item>
      </record>
   </infos>
   <operation>
      <record date="01/01/2000" time="24:00">
         <item>
         ....
         </item>
         .
         .
         .
         <item>
         ....
         </item>
      </record>
   </operation>
....
</history>

I want to build a database with the data from these xml files and query things like :
Q) How many of Cat No. 123 went to country ABCD

We have traditionally used excel for this kind of work, which I realize is a not a good idea in the long term. I am thus trying to figure out how to directly parse and analyze the xml files.

I am completely at a loss as to how to get started on this. Any help would be appreciated.

Although many DBMS support XML storage and processing it is not the native format. To gain the greatest benefit from a move to a DB you must pull the data from their current files and store then in tables. This is known as an Extract, Transform, Load (or ETL) job. There are specific ETL tools available. The major vendors have them, and independent and open-source tools exist. There is, of course, an overhead to learning these tools and writing the ETL jobs. They are most useful when there is an on-going need to process new files into the future.

For one-off or ad hoc tasks the native capabilities within the DBMS itself often suffice. My suggestion is you create a staging table with a single column of type XML (if your chosen technology supports it). Load the file directly into here without any processing. Then use the DBMS's native XML tools to shred the XML and populate the "real" tables.

Next comes the question of what the destination tables should look like. Relational databases work best when table design is normalised. Simply reproducing to record / item structure will not suffice. It will be a nightmare to query and performance will be awful.

Table design and normalisation is part science and part craft. It depends on the inherent structure of the data and also the meaning users impart to the data. If all your records have the same number of items, and all the items have the same meaning across records you may be OK with a single table. There will be one column per item (properly named, NOT item1, item2 ...) and a row per record. If the current Excel sheet has a very regular, ordered, no-nonsense, one-value-per-cell layout you could start by reproducing that then refining as experience and confidence grows.


Oracle backup with RMAN Options? I'm trying to learn RMAN but getting confused everytime I read Oracle docs!

Can someone help me what are the differences and when to use the following?

BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT

BACKUP FULL

Which came from:

run{
backup full tag='INC000000325026'
(database format='/u04/backups/paris/rman/%d_%U_%T' include current controlfile);
}

run{
ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT '/u01/backups/rman/%d_%U_%T';
BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT TAG ='FRISK_DB';
}

I want/need to take a cold full backup of a database in a simple way, so which one is suitable?

A Cold Backup is making a copy of the files with the database closed. These commands are both for hot backups using RMAN.  They are both preferred over cold backups due to their flexibility.

The difference between the two commands is whether or not archive logs are backed up.  If you need be able to recover to any point between your backups or anytime after your most recent backup, then you will need to be in archivelog mode.  If you are in archivelog mode then something needs to be done to backup those archive logs and use should use the PLUS ARCHIVELOG syntax.

It is advisable to periodically test a recovery as well to make sure your backups are working and you can recover.


Is it possible to represent a ‘mutual IS-A’ relationship in an entity-relationship diagram? Is the following scenario correct and why:

A and B are two entities
B is-a A
A is-a B

And what if A, B were relationship?

In databases, subtyping means that one domain is a subset of another. If A is a subset of B and B is a subset of A, then it means A = B. The same logic applies whether A and B are entity sets or relationship sets. There would be no point distinguishing A and B in an ER model.


Too Many database connections on Amazon RDS We are having problems with users running queries/views in Drupal that occasionally cause our site to freeze.  The freeze occurs because the query causes the number of database connections to go up to 400+ and basically anytime the site goes over 100 database connections the site slows down terribly and just doesn't respond.

We are running Amazon RDS using MySQL Red Hat Linux

We have a large enough EC2 on the front end app server, and a large enough RDS.

The way we are fixing this issue now is to find the offending query, and kill it.  Once the query is killed...our database connections drop to around 20 which is the normal amount you see when monitoring the site statistics.

Is there a way to stop the offending query and kill it before it runs too long and consumes the connections?  I am trying to automate the killing of the bad query before it happens, or at least realize after 30 seconds its a bad query and kill it.

Here is a Stored Procedure to kill long running SELECTs

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`Kill_Long_Running_Selects` $$
CREATE PROCEDURE `test`.`Kill_Long_Running_Selects` (time_limit INT,display INT)
BEGIN

    DECLARE ndx,lastndx INT;

    DROP TABLE IF EXISTS test.LongRunningSelects;
    CREATE TABLE test.LongRunningSelects
    (
        id INT NOT NULL AUTO_INCREMENT,
        idtokill BIGINT,
        PRIMARY KEY (id)
    ) ENGINE=MEMORY;
    INSERT INTO test.LongRunningSelects (idtokill)
    SELECT id FROM information_schema.processlist
    WHERE user<>'system user' AND info regexp '^SELECT' AND time > time_limit;

    SELECT COUNT(1) INTO lastndx FROM test.LongRunningSelects;
    SET ndx = 0;
    WHILE ndx < lastndx DO
        SET ndx = ndx + 1;
        SELECT idtokill INTO @kill_id
        FROM test.LongRunningSelects WHERE id = ndx;
        CALL mysql.rds_kill(@kill_id);
    END WHILE;

    IF lastndx > 0 THEN
        IF display = 1 THEN
            SELECT GROUP_CONCAT(idtokill) INTO @idlist FROM test.LongRunningSelects;
            SELECT @idlist IDs_KIlled;
            SELECT CONCAT('Processes Killed : ',lastndx) Kill_Long_Running_Selects;
        END IF;
    END IF;

END $$

To kill SELECTs running longer than 30 seconds, you run this

CALL test.Kill_Long_Running_Selects(30,0);

If you want to see the connections being killed, you run this

CALL test.Kill_Long_Running_Selects(30,1);

Perhaps you can create a MySQL Event to call this Stored Procedure every minute.

If Amazon does not let you have the EVENT privilege, you will have to write an external  shell script on the EC2 server to connect to the DB and run the Stored Procedure. That shell script can be put into a crontab.

If Amazon does not let you have the PROCESS and SUPER privileges, you may need to move the DB out of RDS and into another EC2 instance running MySQL to accomplish this. You could then create the MySQL Event without Amazon's hosting restrictions.


Enforce functional dependencies by using triggers in SQL99 The question is as below:

For R(A, B, C, D, E), write a trigger to enforce AB->C on insert.

This is my solution:

CREATE TRIGGER fdEnforceInsert
BEFORE INSERT ON R
REFERENCING NEW ROW AS N
FOR EACH ROW
DECLARE counter INT
BEGIN
  SELECT COUNT(*) INTO counter
  FROM R
  WHERE R.A = N.A AND R.B = N.B AND R. C <> N.C;
  IF(counter>0)
    THEN raise_exception ('AB->C on R was violated');
END;

One thing I'd like to ask is whether to use "before" or "instead of" in the second line?

An instead of trigger is exactly that; it won't perform an insert even if data satisfy the constraint, so it won't be appropriate.

I understand that yours is a contrived example and that in real life you'll use a unique constraint to enforce such a rule, but the exists predicate nevertheless might be more efficient:

BEGIN
  IF EXISTS (
    SELECT 1 FROM R
    WHERE R.A = N.A AND R.B = N.B AND R. C <> N.C;
  )
    THEN raise_exception ('AB->C on R was violated');
END;


Calculate difference between current value and next value without CTE/ inline table I have a table that has userid and his visit dates. I have created a CTE with adding a next visit column and using this to calculate day difference between visits.

With Visits AS
(
SELECT [uid],[visit_date]
            ,(SELECT MIN(visit_date)
             FROM Visit E2
             WHERE E2.visit_date > E1.visit_date AND
                   E2.uid= E1.uid ) AS next_visit_date
FROM Visit E1
)
SELECT uid, DATEDIFF(day, visit_date, next_visit_date)
FROM Visits

I know that a CTE or a temp table is not a memory efficient way, so looking to increase efficiency. Thank you

I think efficiency is more reliant on the fact that you are doing 2 scans against the table. You could try using a window function instead:

SELECT uid, visit_date
     , LEAD(visit_date) OVER (PARTITION BY uid
                              ORDER BY visit_date) as next_visit_date
FROM Visit E1

Now you can calculate the diff from there

SELECT uid, DATEDIFF(day, visit_date,
                          LEAD(visit_date) OVER (
                              PARTITION BY uid
                              ORDER BY visit_date
                          ))
FROM Visit E1


How to profile trigger database performance impact I want to set up some triggers on a DB2 box (LUW, 9.7).  The DBAs are concerned that if the trigger fires too often, it will adversely affect general performance.

What's a reasonable cheap approach to sizing up performance impact?  By "cheap", I mean I don't want to set up complicated monitoring/profiling tools; my impression is that this database was set up with a minimum of logging tools and so forth, and I don't want to change that.  I'm thinking more in terms of things like watching for page faults and also things that can be seen with db2top.

The environment:

It's an enterprise-level analytics database.  Data is fed into it via replication (Golden Gate, now owned by Oracle).  The triggers we're creating track updates.  (Upon update, a small record containing the primary key is stored in another table.  It's not a great way to do things; we're trying to ship data somewhere else, and right now we're highly constrained and can't use a technically advanced and thorough solution like replication.  An alternative is adding a column to record updates, but we've been told not to do that, though that might change.)  One reason I'm leery of just adding triggers willy-nilly is I'm concerned I might break the replication process.  My current idea is to add a trigger that performs a simple hash on the primary key (which is a BIGINT) and record updates only for a fraction of the rows, so I can get an idea of how often updates occur.  Anyway, that's why I'm asking about simple, reasonable metrics to see how triggers affect e.g. inserts into a particular table.

There's no separate "trigger performance impact"; trigger code is essentially compiled into the execution plan of the statement that causes the trigger to fire, so measuring average DML statement response time before and after introduction of triggers should give you a general idea of the impact.

Of course, triggered actions will certainly affect other workloads, not only the triggering actions. I'm sure your DBAs have records of baseline database performance that they can compare to what happens after the triggers are added (presumably on a test system).

As to monitoring tools, DB2 is fully instrumented to provide detailed views of its performance from various aspects, using event monitors and monitor views/stored procedures, so you don't need to set up anything in addition to what's always there.


Postgresql group by and count() problem, missing row with 0 result I have a problem with group by and count().
I need a result table looks like below.
Tried I guess all the variations of JOIN.

There are two types of wrong result:
- first when the row "3 C 0" is missing,
- the second when it is present but with a false value "3 C 1"

Table1:

id name
1  A
2  B
3  C
4  D

Table2:

id
1
1
2
1
4

result should look like this:

id name count
1  A    3
2  B    1
3  C    0
4  D    1

Any ideas?

Basically, you want a subquery like this:

SELECT t1.id1, t1.let, COALESCE(tab.cnt, 0) AS my_cnt
FROM t1
LEFT JOIN
(
  SELECT
    id2,
    COUNT(id2) AS cnt
  FROM t2
  GROUP BY id2
  -- ORDER BY id2
) tab
ON t1.id1 = tab.id2;

See the fiddle here.

Basically, what I did was:

Create tables t1 and t2:

CREATE TABLE t1
(
  id1 int,
  let text
);

and

CREATE TABLE t2
(
  id2 int
);

Populate them:

INSERT INTO t1
VALUES
(1, 'A'), (2, 'B'), (3, 'C'), (4, 'D');

and

INSERT INTO t2
VALUES
(1), (1), (2), (1), (4);

Ran my query:

SELECT t1.id1, t1.let, COALESCE(tab.cnt, 0) AS my_cnt
FROM t1
LEFT JOIN
(
  SELECT
    id2,
    COUNT(id2) AS cnt
  FROM t2
  GROUP BY id2
  -- ORDER BY id2
) tab
ON t1.id1 = tab.id2;

Result:

id1 let my_cnt
1   A   3
2   B   1
3   C   0
4   D   1

The inner query gets the counts and then the LEFT JOIN causes the id1 of 3 to be present - otherwise it would be missing from the result set and the COALESCE function (see here also) causes the value 0 to be present instead of NULL which would otherwise be there. I initially misread the question - p.s. welcome to the forum!


Poor performance after changing SQL Server 2008 R2 database Need some tips on what I can do to track down this problem. My client has a modest sized SQL Server 2008 R2 database (approx 6 gigs in size).

Last week I modified the database to add new a single new field to about 6 (of the hundred) tables, and made corresponding changes to the views and stored procedures that used those tables - all in in, very minor changes.

Problem is, for one stored procedure, that I did not modify, the performance has become truly horrid. What used to run in 8 seconds, now takes at least 10 minutes and since this report is run throughout the day, it tends to kill everyone else's performance when someone runs it.

I have used Red Gate's SQL Compare to try and figure out what has changed, besides the new fields I added, but can't quite find anything that would account for the difference - so the question is, how do I go about debugging this? I have a 6 month old copy of the database on the same machine that I can still run this SP in 8 seconds; the SP is the same, the indices seem all to be the same, I have updated statistics, recompiled the views and recompiled all the stored procedures, and still can't seem to get the performance back.

SO the question for you more experienced DBA's, how do I go about solving this? I've looked at the execution plans, but quite frankly the SP is so complex, with so many steps that its difficult to read - and I don't know exactly what I am looking for.

Suggestions?

EDIT: and just to clarify, I didn't upgrade the hardware OR the version of SQL Server, just some underlying table changes; and the 6 month old copy of the database, that still runs really fast, is only about 10 percent smaller than the current version, so while there has been growth, nothing that should account for a 60X slowdown in performance.

Link to slow plan:
https://www.dropbox.com/s/arg2s5psav3slj3/SlowPlan.sqlplan

Link to fast plan:
https://www.dropbox.com/s/2frmzqqajtpy9q2/GoodPlan.sqlplan

So the major differences I see on first glance is that the table scan on ARMiscIncome feeds a merge join in the slow version, which requires a sort:

But in the fast version it feeds a hash join (so no sort is required):

This sort is the highest overall cost in the slow plan, so I might suggest either finagling a hash join there (this could be simply due to different row counts in the two systems, even if statistics are out of date), or adding an index, such as this one which satisfies the output:

CREATE INDEX SortHelper ON dbo.ARMiscIncome(GuestID)
  INCLUDE (Amount, ChargeCode, DateEntered, Description, TranDate);

(Or changing the clustered index on that table.)

Usual caveats apply, e.g. adding/changing an index may help this specific query, but may not help others, and/or may cause undue stress on DML operations. But certainly worth thoroughly testing IMHO.

As an aside, I was able to find the most expensive operator by comparing the plans in SentryOne Plan Explorer. This is a free tool that makes exactly this type of investigation a lot more painless than it is in Management Studio.


SQL Server 2016 SP 1 on HP 3PAR environment causes weird head blocker My client is on HP 3PAR hardware. Three servers are on different geographical locations, connected with a 40 gbit fibre connection. The SAN is SSD only, the machines have lots of cores.

The ETL process starts a single select query joining 2 tables. The execution plan shows the use of a non-clustered index combined with a heap scan for both tables.

This simple query becomes a head blocker for all other following queries, even those that are referencing completely different tables.

My first guess is that something with the 3PAR could be involved, but I'm not really sure. Besides that, it's really weird that a

select a.[value] from table a, inner join table b where table a.column = 12345

manages to block

update table c set column a = [value].

AFAIK there are no lock hints.

There are no triggers of foreign keys on the used tables. There are no functions or other creative extra's on the database.

I'm wondering if the HP 3PAR failover can in some way cause a query to lock a database or table or data partition or HOBT.
Any thoughts? If more info is needed, let me know.

== Edit 12/28/2017
Answered the question :)

When you're troubleshooting a blocking chain happening live, start with sp_WhoIsActive:

sp_WhoIsActive @get_locks = 1

There's a blocked-by column that shows you who's blocking who. The lead blocker won't have anyone in their blocked-by column.

Once you've found the lead blocker, click on the locks column - it's an XML field that expands to show you the full list of locks held by that session.

It could be that the select is part of a longer transaction (there's also an open transactions column), or that the select isn't really what's blocking.

Your best bet for getting specific, actionable advice here would be to post a picture of the sp_WhoIsActive output, including the blocking columns, and then the contents of the XML for the locks.


Tabular mode disabled during SSAS installation I am trying to install SSAS in Tabular Mode. However, i find that the radio button is disabled. I am installing it on a separate instance as well. The regular Multidimensional SSAS  had been installed first.I am installing Standard SQL server . .

That option is only available for Enterprise and Business Intelligence editions:

Features Supported by the Editions of SQL Server 2012

And, of course, for Developer Edition.


Cache MySQL database in memory I am having problems with a website with 600MB of MySQL database. The website is way too slow. I noticed the bigger the MySQL database gets, the slower it goes. When it was 5MB, the website was very fast. When it started getting bigger it started to get slower and slower and now, at 600MB, it is really slow, taking like 10 seconds to load pages.

I checked the top processes and it is nothing related to high load or anything. It is not even related to IOPS as I tested on HDD 7.2k rpm drives and it gave same problem now with testing with Intel 320 SSD drives, so I don't think it is about high queries either.

The website is using Wordpress and there are like 9 plugins active. People said it might be the plugins...well maybe...but right now I just want to cache the whole database in memory and would like to get help and direction on where to start and how to do it.

I have 16GB RAM and i5-2400 4 cores @ 3.1 GHz. OS is centos 5.7

top - 07:23:57 up 9 days, 12:15, 0 users, load average: 0.09, 0.04, 0.05
Tasks: 162 total, 1 running, 161 sleeping, 0 stopped, 0 zombie
Cpu(s): 8.2%us, 1.0%sy, 0.0%ni, 90.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 16367532k total, 3641628k used, 12725904k free, 612140k buffers
Swap: 1046520k total, 0k used, 1046520k free, 1538896k cached

If I were you, I would switch all data to InnoDB. Table locking/row locking has long been discussed by many. I would always choose InnoDB hands down. However, there is another profound reason for choosing InnoDB...CACHING.
While most people boast that MyISAM is faster for reads, most people forget that the many cache for MyISAM, which is called the key cache (set by key_buffer_size), only caches index pages from .MYI files. It never caches data pages. It has an official maximum of 4GB in 32-bit Systems. 8GB is best maximum for 64-bit.
The InnoDB Buffer Pool caches the data and index pages. Depending on the server your have, you can cache up to the entire dataset in RAM. You can tune InnoDB for up to 80% RAM and 10% for DB Conenctions, and leave 10% for the OS. This is true even for different operating systems.
I have recommended these things for Drupal customers with marvelous success. It applies to Wordpress just as well. I have provided DB support for clients with WordPress. Same improvements.
You can always configure memory for InnoDB more effectively that you can more MyISAM. There is always a way to tweek InnoDB to suit your performance needs. As your data grows, it will eventually become a requirement.
UPDATE 2011-11-21 11:44 EST
If your complete dataset is small enough, you could execute a SELECT query on every table you have right after mysql starts up.
For all tables that are InnoDB and/or MyISAM, run this query:
SELECT DISTINCT
    CONCAT('SELECT ',ndxcollist,' FROM ',
    db,'.',tb,' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
FROM (
    SELECT
        engine,table_schema db,table_name tb,index_name,
        GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
    FROM (
        SELECT
            B.engine,A.table_schema,A.table_name,
            A.index_name,A.column_name,A.seq_in_index
        FROM
            information_schema.statistics A INNER JOIN
            (SELECT engine,table_schema,table_name
            FROM information_schema.tables
            WHERE engine IN ('InnoDB','MyISAM')) B
            USING (table_schema,table_name)
        WHERE
            B.table_schema NOT IN ('information_schema','mysql')
            AND A.index_type <> 'FULLTEXT'
        ORDER BY
            table_schema,table_name,index_name,seq_in_index
        ) A
    GROUP BY
        table_schema,table_name,index_name
) AA
ORDER BY
    engine DESC,db,tb
;

This will output every possible SELECT query you need to run that will summon all indexes to be referenced. Place this query in a file called /root/MakeSelectQueriesToLoad.sql. Run the script and collect the output /root/SelectQueriesToLoad.sql. Finally, run it:
mysql -u... -p... -AN < /root/MakeSelectQueriesToLoad.sql > /root/SelectQueriesToLoad.sql
mysql -u... -p... < /root/SelectQueriesToLoad.sql

This will definitely preload all index pages into the InnoDB Buffer Pool and MyISAM Key Cache. If all your data is InnoDB, make two changes:

replace WHERE engine IN ('InnoDB','MyISAM') with WHERE engine='InnoDB'
replace CONCAT('SELECT ',ndxcollist,' FROM ', with CONCAT('SELECT * FROM ',

This will also populate more data pages into the InnoDB Buffer Pool.
FINAL NOTE : Make sure the InnoDB Buffer Pool is large enough to hold all your InnoDB Data


I Need Help With My Database Design for Product Management I'm designing a MySQL database and the front end for my work. The database will store all of the products that we sell on our marketplaces and their relevant data. We will be able to lookup products and see their calculated cost for our different marketplaces. I plan on expanding the functionality in the future but for the moment our goal is just product pricing and dimensional data lookup.
How does this design diagram look to you guys? Is there anything that you would recommend me to add or change? I am open to criticism as this is my first professional database project.
Thanks
Here is the sql file https://github.com/ryanbbernstein/Database/blob/master/Product%20Database.sql

This is just an "at-a-glance" high-level overview.

you have "ID" as your primary key for all your tables. Despite this being anathema to purists (pace Joe Celko and Fabian Pascal), personally, I have no problem with the use of such surrogate keys. However, I would advise that you rename these fields "product_id", "supplier_id" &c. for two reasons

ultimately it will make your SQL easier to read, and (relatedly)
will greatly help debugging - i.e. if you get an error saying that "... error with field ID = 123456...", you won't know to which table it is referring, whereas with my way, it's obvious.

Your product table is already becoming quite wide. If I were you, I'd split it it up this way.

You have various markets AFAICS (Amazon, FBA, EBay &c.). What happens when you have 47 markets? Your product table will be horrendous! :-). You should move these markets out into separate tables. Have an "amazon" table something like this

CREATE TABLE amazon (amazon_id int PK, product_id int FK, amazon_price... Other Amazon fields &c.).

Do the same for all your markets. Think about using VIEWs to combine this data for a given product later - as well as SQL. Rule of thumb: Tables should be like women, tall and slim not short and fat! :-)

You could also think about doing the same thing for your alternates. What happens if you have more than 1 for a given product? Putting these alternates in a separate table will allow you that flexibility down the road.

CREATE TABLE alternate (alternate_id int PK, product_id int FK, alt_supplier int FK, &c...)

You make use of that MySQL abomination, the ENUM type. Don't! They are evil©, and here's why. Never use it or that other invention of Beelzebub, the SET type.

Firstly, they breach Codd's rules. Secondly, they are non-standard. Imagine, 5 years down the road, your business has taken off and you want to upgrade from MySQL (any change from MySQL is an upgrade IMHO :-) )? Your system will require a complete rewrite rather than just tweaking if you've used ENUMs or SETs. They're a bit like drugs, great at first but coming off them is difficult and painful.

My final two points are my own preferences/biases (take your pick :-) )

you have a mixture of singular and plural table name (products, log_book). Pick one and stick to it - my own preference is for singular (1 exception, orders, for obvious reasons - never use SQL keywords as tablenames).
if you are only starting out on this project, I would strongly urge you to use PostgreSQL instead of MySQL. Many reasons for this:

CHECK CONSTRAINTs
Window (aka analytic) functions
Full support for set operators
CTEs (Common Table Expressions)

Having these built into your RDBMS will save you a lot of time and money in the long run rather than having to implement them yourself later.