Select query on information_schema is too slow SELECT *
FROM information_schema.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
  AND CONSTRAINT_NAME   = 'FK_FOLDER_FILTER_CONTRACT_CATEGORY_CONTRACT_CATEGORY'
  AND CONSTRAINT_TYPE   = 'FOREIGN KEY';

The above query takes around 2 mins to execute
is there any other alternate query or how can we optimize this query

information_schema isn't a real database. To preform this query it needs to open tables.

The internal code for information_schema isn't optimized and it will frequently generate the data for the entire table requested without consideration of the query. As such your query could be opening every table regardless of the current table.


Duplicate index - Same key, one with INCLUDE columns, which one to drop? I have 2 indexes that have the same key, but one also has INCLUDE columns. Which one can I drop?

They are the same, except IndexInclude has 6 INCLUDE columns.

Both have some reads (IndexNoInclude has much higher seeks).

I believe the IndexInclude will satisfy anything that would've used IndexNoInclude right? Since the keys are exactly the same.

Thanks for all the help in advance.

Output from sp_Blitz. Sorry for the bad format

Details: db_schema.table.index(indexid)
Definition: [Property] ColumnName {datatype maxbytes}   Secret Columns  Fillfactor  Usage Stats Op Stats    Size

IndexINCLUDE [1 KEY] EXECUTION_TIME {datetime 8} [6 INCLUDES]
Reads: 101,913 (101,913 seek) Writes:2,381,661,369
0 singleton lookups; 225 scans/seeks; 0 deletes; 0 updates;     263,358,683 rows; 5.4GB

IndexNoInclude   [1 KEY] EXECUTION_TIME {datetime 8}
Reads: 1,181,595 (1,181,595 seek) Writes:2,380,483,678
0 singleton lookups; 15 scans/seeks; 0 deletes; 0 updates;  263,358,683 rows; 2.9GB

I believe the IndexInclude will satisfy anything that would've used IndexNoInclude right? since the Keys are exactly the same

Are they the same? Maybe I am misreading the output, but it certainly looks like IndexNoInclude has an extra key column: [ID] NUMERIC.

You should review your queries to see which of the 6 INCLUDE columns are actually being referenced / used. You could likely get away with "merging" the two indexes such that the remaining index has both key columns and between 1 and 6 of the INCLUDE columns.


How to convert SQL view creation script into a query, instead? I have the following SQL view creation script. I would simply like to turn it into a query. I tried to replace the initial line with a SELECT statement, but this did not work. Can someone please show me what I'm doing incorrectly?

CREATE OR REPLACE FORCE VIEW "RMS"."VW_CR_ADDITIONAL_INFO" ("COMMIT_REG_ID", "FUND_CERTIFIED", "FUND_CERTIFIED_DATE", "CANCELED", "APPROVED", "AMOUNT", "LAST_STATE_ID") AS
  WITH FUND_CERTIFIED AS (
  SELECT REQUEST_ID, MIN(TIME_STAMP) AS FUND_CERTIFIED_DATE
  FROM REQFLOW_STATE
  WHERE STATUS_ID = 6
  GROUP BY REQUEST_ID
),
APPROVED AS (
  SELECT DISTINCT REQUEST_ID
  FROM REQFLOW_STATE
  WHERE STATUS_ID NOT IN ( 5, 8 )
),
CANCELED AS (
  SELECT DISTINCT REQUEST_ID
  FROM REQFLOW_STATE
  WHERE STATUS_ID = 4
),
LAST_STATE AS (
  SELECT MAX(ID) AS LAST_STATE_ID, REQUEST_ID
  FROM REQFLOW_STATE
  WHERE STATUS_ID <> 8
  GROUP BY REQUEST_ID
)
SELECT CR.COMMIT_REG_ID,
NVL2( FC.FUND_CERTIFIED_DATE, 'Y', 'N' ) AS FUND_CERTIFIED,
FC.FUND_CERTIFIED_DATE,
NVL2( C.REQUEST_ID, 'Y', 'N' ) AS CANCELED,
NVL2( A.REQUEST_ID, 'Y', 'N' ) AS APPROVED,
SUM( NVL( CRD.AMOUNT, 0 ) ) AS AMOUNT,
LS.LAST_STATE_ID
FROM COMMITMENT_REGISTER CR
LEFT OUTER JOIN FUND_CERTIFIED FC ON ( FC.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN APPROVED A ON ( A.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN CANCELED C ON ( C.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN LAST_STATE LS ON ( LS.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN COMMITMENT_REGISTER_DETAIL CRD ON ( CRD.COMMIT_REG_ID = CR.COMMIT_REG_ID )
GROUP BY CR.COMMIT_REG_ID,
NVL2( FC.FUND_CERTIFIED_DATE, 'Y', 'N' ),
FC.FUND_CERTIFIED_DATE,
NVL2( C.REQUEST_ID, 'Y', 'N' ),
NVL2( A.REQUEST_ID, 'Y', 'N' ),
LS.LAST_STATE_ID;

Your query uses a common table expression, so replacing the first line with SELECT won't work. Comment out the first line of your script and you should have a SELECT query that returns the same results as the view. Reproducing your code in full:

WITH FUND_CERTIFIED AS (
  SELECT REQUEST_ID, MIN(TIME_STAMP) AS FUND_CERTIFIED_DATE
  FROM REQFLOW_STATE
  WHERE STATUS_ID = 6
  GROUP BY REQUEST_ID
),
APPROVED AS (
  SELECT DISTINCT REQUEST_ID
  FROM REQFLOW_STATE
  WHERE STATUS_ID NOT IN ( 5, 8 )
),
CANCELED AS (
  SELECT DISTINCT REQUEST_ID
  FROM REQFLOW_STATE
  WHERE STATUS_ID = 4
),
LAST_STATE AS (
  SELECT MAX(ID) AS LAST_STATE_ID, REQUEST_ID
  FROM REQFLOW_STATE
  WHERE STATUS_ID <> 8
  GROUP BY REQUEST_ID
)
SELECT CR.COMMIT_REG_ID,
NVL2( FC.FUND_CERTIFIED_DATE, 'Y', 'N' ) AS FUND_CERTIFIED,
FC.FUND_CERTIFIED_DATE,
NVL2( C.REQUEST_ID, 'Y', 'N' ) AS CANCELED,
NVL2( A.REQUEST_ID, 'Y', 'N' ) AS APPROVED,
SUM( NVL( CRD.AMOUNT, 0 ) ) AS AMOUNT,
LS.LAST_STATE_ID
FROM COMMITMENT_REGISTER CR
LEFT OUTER JOIN FUND_CERTIFIED FC ON ( FC.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN APPROVED A ON ( A.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN CANCELED C ON ( C.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN LAST_STATE LS ON ( LS.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN COMMITMENT_REGISTER_DETAIL CRD ON ( CRD.COMMIT_REG_ID = CR.COMMIT_REG_ID )
GROUP BY CR.COMMIT_REG_ID,
NVL2( FC.FUND_CERTIFIED_DATE, 'Y', 'N' ),
FC.FUND_CERTIFIED_DATE,
NVL2( C.REQUEST_ID, 'Y', 'N' ),
NVL2( A.REQUEST_ID, 'Y', 'N' ),
LS.LAST_STATE_ID;


How to recover MySQL table structure from FRM files What I have learned from Google searching:

With MyISAM tables you can simply copy the FRM, MYD, and MYI files. In the folder for the DB I'm trying to recover, there are only FRM files and a "db.opt" file, so they must not be MyISAM tables.

Situation:

My computer crashed, but the data is still on the hard drive. The MySQL 5.5.8 server was installed with WAMP on the crashed computer.

I have tried copying the entire data folder over, but MySQL only creates a blank database - no tables.

There is an FRM file for each of the tables I wish to recover. However, they are not readable text files. Is there any way to recover the table structure from these files?

This post https://stackoverflow.com/a/7688688/1188138 indicates that the structure is in fact in those files, but does not provide a method of retrieval.

Thank you so much - I have tried everything I can think of.

If you only see the .frm files, then there is a strong likelihood that the storage engine in use was InnoDB and innodb_file_per_table must have been off by default.
If you transferred everything from datadir on the crashed server onto another disk on another machine, you may be able to startup mysql with that folder as is.
For example, suppose ServerA is your crashed server and ServerB is where you want it placed.

Install MySQL 5.5.8 on ServerB
net stop mysql on ServerB to make sure mysql is down on ServerB
md C:\MySQLData on ServerB
Drop everything from the data folder on ServerA into C:\MySQLData on ServerB
Make C:\MySQLData the new datadir

Add this to my.ini on ServerB
[mysql]
datadir=C:/MySQLData

C:\> del C:\MySQLData\ib_logfile* on ServerB
net start mysql on ServerB

Please try this and tell us what happened
UPDATE 2012-02-03 17:06 EDT
Since you were able to recover everything NOW DO THIS:
mysqldump -u... -p... -A -d --routines --triggers > C:\MySQLSchema.sql

This will give all table structures in the MySQL Instance.


DBCC CHECKDB grows tempdb to a huge size - Legacy MS SQL Server 2008 R2 Instance I have an older Microsoft SQL Server Instance (2008 R2 SU3 GDR - Build: 10.50.6220).  When I run DBCC CHECKDB via a Maintenance Plan, the TempDB grows very large.

The largest DB on the instance is 95GB, and while running a data integrity check tempdb grew to 40GB before filling up the filesystem.

Here's the error message:

  Executing the query "DBCC CHECKDB(N'[dbname]')  WITH NO_INFOMSGS "
  failed with the following error: "Could not allocate space for object
  'dbo.SORT temporary run storage:  140907993825280' in database
  'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by
  deleting unneeded files, dropping objects in the filegroup, adding
  additional files to the filegroup, or setting autogrowth on for
  existing files in the filegroup. The transaction log for database
  'tempdb' is full. To find out why space in the log cannot be reused,
  see the log_reuse_wait_desc column in sys.databases Check terminated.
  A failure was detected while collecting facts. Possibly tempdb out of
  space or a system table is inconsistent. Check previous errors.".
  Possible failure reasons: Problems with the query, "ResultSet"
  property not set correctly, parameters not set correctly, or
  connection not established correctly.

My first action step is to move tempdb to its own drive.  However, I'd like to find a way to run a full CHECKDB (not PHYSICAL_ONLY) that uses less tempdb space.  I can't upgrade to a newer version of SQL Server at this time.

Any helpful tips?

Actual serious although unusual suggestion: drop all the indexes in the database - or some of them - then "DBCC CHECKDB" has fewer things to check.  Of course you can put them back after...

Alternatively, rebuilding indexes may produce a simpler arrangement of data for CHECKDB to analyse...  this is actually a guess, but you may use fewer pages to store a table, at least.

https://technet.microsoft.com/en-us/library/ms176064(v=sql.105).aspx
describes several WITH options which increase or decrease the workload of CHECKDB, and another option ESTIMATEONLY which causes it do skip any checking but tell you how much space in tempdb is required for checks.  It's implied that this varies when you set other parameters with ESTIMATEONLY  i.e. TABLOCK, PHYSICAL_ONLY.  These do skip some checks.

Most but apparently not all of the checks in CHECKDB can be run as different commands, as described.

https://www.mssqltips.com/sqlservertip/2399/minimize-performance-impact-of-sql-server-dbcc-checkdb/
has  performance-related suggestions including restoring the database backup to a different server to be checked.  This too might not be a perfect check if the restore server is running a later SQL Server version and quietly "fixes" flaws of the original database during restore, so that you don't see them in the copy.


Upgrade/Transition Path from MySQL Community 5.6.31 to Enterprise Full disclosure. I have never upgraded a database. I have a MySQL Community that is not the latest @5.6.31 ( latest is 8 ?) to the paid, Enterprise version. We will ask Tech Support once purchase of Enterprise is fully approved... but really trying to find a clear upgrade path online has proven difficult. I'm a novice, so feel free to be very details in replies.

Upgrade to 5.7.  Follow all the steps there, including testing mysql_upgrade.
Test your code against 5.7.
Then embark on upgrading from 5.7 to 8.0; it's more complex but still doable.


I killed a session and it is still running? I have Oracle 9.2

I view my running session with the query:

SELECT S92700.SYSUSR_VW."NAME", SID,SERIAL#, AUDSID
FROM v$session, S92700.SYSUSR_VW, S92700.SECUSR
WHERE S92700.SYSUSR_VW.SESSIONID = AUDSID AND S92700.SECUSR."NAME"=S92700.SYSUSR_VW."NAME"

Result:

 NAME      SID      SERIAL#    AUDSID
 prot6      9        6503       78239

And i run the follow query to kill the session

alter system kill session '9,6503' immediate

SQL Command execution

  Executed successfully in 0 s, 0 rows affected. Line 1, column 1

  Execution finished after 0 s, 0 error(s) occurred.

The session still running

The same query in other schema is working

From a thread on AskTom, when a session is killed:

  The session is killed but it is being cleaned up.  When you kill a session --
  outstanding work must still be cleaned up.

When you do DML on a table, Oracle may make the changes to the base table almost immediately, even if you don't commit. The other sessions don't see these changes yet thanks to multiversion read consistency but the actual physical block may already be overwritten with your uncommited work.

This is done because Oracle is optimized for commit. Most sessions should rarely rollback so Oracle anticipates by writing preemptively the changes. This is also one of the reasons that allows Oracle to have arbitrarily large transactions (limited only by the size of the undo tablespace). The changes are not delayed (much). This is also why you almost never wait for a commit in Oracle, even for multimillion-row DML.

One of the consequences is that rollback needs to undo the changes. The uncommited transaction is read back from the undo tablespace and each and every change is undone in reverse order. This can take longer than the initial DML for several reasons:

The rollback is not as well optimized, in particular multi-row operations can take a lot longer than regular DML.
Some changes are more expensive in reverse. For instance a delete which is a relatively cheap DML becomes an insert in reverse (which is more expensive).

This explains why rollback can take a lot of time. You have few means to accelerate the process. In any case you should not:

kill the rollback process
shutdown the database in the hope that this will terminate the session

If you shutdown the database normally (or transactional or immediate), Oracle will wait for the process to complete so that the database is left in a consistent state. If you shutdown ABORT, the database will be left in an inconsistent state (uncommited data written to disk) and Oracle will resume the rollback as soon as the database is restarted.

The rollback is handled by smon, I wouldn't try to kill it.

Some session, after being killed, will remain in the v$session view, even after all their changes have been entirely rolled back. This is often because Oracle is waiting for the client to return a relevant error message, such as:

ora-00028 your session has been killed

If the client itself has already left or the network communication has been severed, the killed session may stay indefinitely until you restart the instance. I don't think these sessions take resources.


Query and index optimization for a table containing 10 million records I was trying to retrieve data from a table A to retrieve data for 1 month, which has around 10 million records and 21 indexes built on it, It took me 3 min to retrieve around 20 columns and total of 300 K records from that table A joined with some other table B which has around 40000 records.

We are using SQL Server 2012 standard edition.

Will like to know how can I improve the query execution time.

Query plan can be found in the below link :

Query Plan

Server information  :
Server Info

The scan of itt is expensive:

So to take advantage of an index or partition using itt.ForwardFeed,
change

    where
      (convert(varchar(20),itt.ForwardFeed,23) >= convert(varchar(20),'2018-06-01',23)
AND     convert(VARCHAR(20),itt.ForwardFeed,23) <= convert(VARCHAR(20),'2018-06-30',23))

to

where itt.ForwardFeed >= '2018-06-01'
  AND itt.ForwardFeed <= '2018-06-30'

This should work whether itt.ForwardFeed is a Date or a varchar in ODBC Canonical YYYY-MM-DD format.


SQL Server 2000 - Is this table needed? SQL Server 2000

I've been tasked with cleaning up an SQL database of which I have no history working with so I'm not familiar with the tables within. Short from dropping a table and seeing if anyone hollers, what is the best practice for cleaning up orphan tables? I did not see a "modified date" attribute on the table, only a "created date".

Would the process be the same for SQL Server 2005/8?

Thanks,
Dave

Couple of different approaches you could try with a Profiler trace.

Capture SQL:StmtCompleted, with a column filter on TextData %TableName%.
Capture Lock:Acquired event, with a filter on ObjectID with the id of the table.


Upgrade mysql from 5.0 to 5.5 in master-master configuration leads to broken replication We created two new mysql servers running 5.5.  Our old server were 5.0.

The two servers are set up with master-master replication - this was the case for the two 5.0 servers which had no issues.

We have some long running select queries (40-50 sec), and when they are run replication breaks - we have to skip a couple of records to get replication restarted.  This is consistent and repeatable.  I suspect that some tables are getting updated on each server, but replication is paused trying to update a locked table - and when it is done, each server is trying to send data to the other and replication is broken.

I am seeing a lot of "waiting for table level lock" on select statements when this long query is running.

Is there a setting I've missed to prevent selects from locking tables? Is there some other cause, or any way to fix this?

Since all the data is MyISAM, you should expect table locks rather frequently since INSERT , UPDATE, and DELETE command issues a full table lock.

If you have a lot of tables that use auto increment keys, you should add the following

Server1

[mysqld]
auto_increment_increment = 5
auto_increment_offset = 1

Server2

[mysqld]
auto_increment_increment = 5
auto_increment_offset = 2

This will prevent PRIMARY KEY collisions with autoincrement columns

I have other suggestions in my post I have been tasked with Mysql Master-Master replication?


"INSERT INTO res_partner () VALUES ()" failure (autoincrement in postgres) This is DDL for the table:

CREATE TABLE public.res_partner (
    id serial NOT NULL,
    CONSTRAINT res_partner_pkey PRIMARY KEY (id)
);

This is SQL:

insert into public.res_partner () values ();

and the error:

SQL Error [42601]: ERROR: syntax error at or near ")"
  Position: 33

$ psql --version
psql (PostgreSQL) 11.1 (Ubuntu 11.1-1.pgdg18.04+1)

I see that this statement is absolutely legal. My code worked 2-3 days ago (PHP with Doctrine). But now I cannot execute this statement from dBeaver client and from my app.

Your linked question is for MySQL which doesn't really have a track record for valid standard SQL - and having an empty column list or empty values list is far from being "legal" syntax in standard SQL or Postgres.

If you want to insert the default values you have two choices

insert into public.res_partner (id) values (default);

Or if you do not want to specify the column name, use:

insert into public.res_partner
default values;


What User Level is needed for Transactional Replication I've been setting up Transactional Replication between two database servers I have running. My first experiment with setting it up I simply used SA and local Admin account for all of the agents. Because of security reasons I would not want to have agents running around with more rights than they actually need. I tried using DB_Owner for sql accounts and just a regular Windows user however it seems that Transactional Replication isn't working with those settings. ( I might have messed up somewhere)

My question is: What is the minimum level of rights that is required by each agent in order to run Transactional Replication successfully?

Side note: One server is running SQLServer 2012 and the other 2008

The Snapshot Agent process account used to connect to the Distributor must be db_owner in the distribution database and have read, write, and modify permissions on the snapshot share.  The account used to connect to the Publisher must be db_owner in the publication database.

The Log Reader Agent process account used to connect to the Distributor must be db_owner in the distribution database.  The account used to connect to the Publisher must be db_owner in the publication database.

The permissions required by the Distribution Agent process account depend on whether you are using push or pull subscriptions.

For push subscriptions, the Distribution Agent process account used to connect to the Distributor must be db_owner in the distribution database, be a member of the PAL, and have read permissions on the snapshot share.  The account used to connect to the Subscriber must be db_owner in the subscription database.

For pull subscriptions, the Distribution Agent process account used to connect to the Subscriber must be db_owner in the subscription database.  The account used to connect to the Distributor must be a member of the PAL and have read permissions on the snapshot share.

The is all covered in the section Permissions That Are Required by Agents in Replication Agent Security Model.


Why TRUNCATE TABLE cannot use four-part name? I was trying to truncate a table as the following:

TRUNCATE TABLE [servername].[dbname].[schema].[table]

I  got error:

  object doesn't exist or you don't have permissions.

However, if I remove the servername part, it works.

Can anyone explain why I cannot use four part name in truncating? Thanks.

It's because TRUNCATE is DDL rather than DML

From MSDN:

TRUNCATE TABLE
    [ { database_name .[ schema_name ] . | schema_name . } ]
    table_name
[ ; ]

Also, regarding permissions:

  The minimum permission required is ALTER on table_name. TRUNCATE TABLE
  permissions default to the table owner, members of the sysadmin fixed
  server role, and the db_owner and db_ddladmin fixed database roles,
  and are not transferable. However, you can incorporate the TRUNCATE
  TABLE statement within a module, such as a stored procedure, and grant
  appropriate permissions to the module using the EXECUTE AS clause.

Notice the db_ddladmin requirement.  As DDL, the four-part naming isn't allowed.  Compare it to Technet info on the FROM clause:

  If the table or view exists in another database on the same instance
  of SQL Server, use a fully qualified name in the form
  database.schema.object_name.

  If the table or view exists outside the instance of SQL Server, use a
  four-part name in the form linked_server.catalog.schema.object. For
  more information, see sp_addlinkedserver (Transact-SQL). A four-part
  name that is constructed by using the OPENDATASOURCE function as the
  server part of the name can also be used to specify the remote table
  source. When OPENDATASOURCE is specified, database_name and
  schema_name may not apply to all data sources and is subject to the
  capabilities of the OLE DB provider that accesses the remote object.

Sources:

http://msdn.microsoft.com/en-us/library/ms177570.aspx

http://technet.microsoft.com/en-us/library/ms177634.aspx

Edit:
To answer your question on how to do it, write a stored procedure in the target database.  Grant access to execute the procedure, and call truncate from the procedure.


REGEXP_SUBSTR to split delimited values I have a field called FREQUENCY and it has values like '10,0;30'. How do I write a regular expression to get the result like this:
FREQUENCY

10
0
30

If you want a single row's value returned without the commas and semi-colons then the easiest way to accomplish this is with the REPLACE() function like this: REPLACE(REPLACE(FREQUENCY, ',', ' '), ';', ' ') "FREQUENCY".


How to print to console in SQL? I'm using PostgreSQL And I want to print a message to the console.

If I use plpythonu I use plpy.notice
If I use plpgsql I use raise notice

but how do I print when the function is pure SQL?
using SELECT 'string' isn't helping me as it print the string in a column and if the message is located in the middle of the code it doesn't show it.
I want something like raise notice / plpy.notice for SQL.

CREATE OR REPLACE FUNCTION A()
  RETURNS VOID AS
$BODY$
                how do i print 'hello world' here?
 $BODY$
  LANGUAGE sql VOLATILE

if it was a plpgsql I would do:

CREATE OR REPLACE FUNCTION A()
  RETURNS VOID AS
$BODY$
        Raise Notice 'hello world'
 $BODY$
  LANGUAGE plpgsql VOLATILE

I'm looking for the equivalent in  LANGUAGE SQL

With psql you could \echo text (or \qecho if using \o), for example executing with psql -f a file with contents:

select 'Straight';
\echo BETWEEN
select 'the lines.';

outputs:

 ?column?
----------
 Straight
(1 row)

BETWEEN
  ?column?
------------
 the lines.
(1 row)


Column type unknown at creation time I have a table in a MySQL database that stores survey results.

CREATE TABLE survey_responses (
  response_id INTEGER UNSIGNED AUTO_INCREMENT,
  survey_id INTEGER UNSIGNED NOT NULL,
  question_id INTEGER UNSIGNED NOT NULL,
=>response WELL_IT_DEPENDS_REALLY,
  FOREIGN KEY (survey_id) REFERENCES surveys (survey_id),
  FOREIGN KEY (question_id) REFERENCES questions (question_id)
) ENGINE=InnoDB;

If the question is free form, a BLOB may be best for the response. If the question is "select the best answer", an INT pointing to that answer's record would be best. I'm sure I could store this in a document database and determine the type during runtime. But I would hate to maintain two databases just for this one column.

I've thought about having response_blob, response_int, response_char, and having them default to NULL. Then I would read the appropriate column at runtime. Is that acceptable or just a silly idea?

Is there a standard way this situation is handled?

Looks like you are describing a subtype/supertype structure.

Start by creating a reference table to hold the different types of response types you can create. This table can be expanded easily if you come up with more types later on.

CREATE TABLE survey_response_types (
  `response_type_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(255) NOT NULL,
) ENGINE=InnoDB;

Modify your survey_responses table to use the response_type_id value instead of actually holding the response. This is the discriminator that lets you know what type of response you have when looking at or joining into the survey_responses table.

CREATE TABLE survey_responses (
  `response_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `survey_id` INTEGER UNSIGNED NOT NULL,
  `question_id` INTEGER UNSIGNED NOT NULL,
  `response_type_id` INTEGER UNSIGNED NOT NULL,
  FOREIGN KEY (`survey_id`) REFERENCES surveys (`survey_id`),
  FOREIGN KEY (`question_id`) REFERENCES questions (`question_id`),
  FOREIGN KEY (`response_type_id` REFERENCES `response_types` (`response_type_id`)
) ENGINE=InnoDB;

Then for each type of response, create another table, whose primary key is exactly the the same as the primary key of the survey_responses table. Also, make a foreign key constraint back to the survey_responses table. Do NOT create a new auto_increment key for this table, there is no need.

CREATE TABLE text_responses (
  `response_id` INTEGER UNSIGNED NOT NULL,
  `text` TEXT NOT NULL,
  FOREIGN KEY (`response_id`) REFERENCES `survey_responses` (`response_id`),
) ENGINE=InnoDB;

You mentioned linking back to the int of an answer, so here is how this table would look.

CREATE TABLE answer_responses (
  `response_id` INTEGER UNSIGNED NOT NULL,
  `answer_id` INTEGER UNSIGNED NOT NULL,
  FOREIGN KEY (`response_id`) REFERENCES `survey_responses` (`response_id`),
  FOREIGN KEY (`answer_id`) REFERENCES `answers` (`answer_id`)
) ENGINE=InnoDB;

Add as many more tables as there are possible types of answers.

When inserting the data, you should always insert an entry both into the survey_responses table, and then within the same transaction, insert an entry into the correct subtype table. MySQL does not support check constraints, so you'll have to handle the constraint code in the application layer to ensure that the response_type_id always matches correct subtype table that is used.

Together a subtype and supertype create a single 'record' that you can access. They should always exist in pairs. Never just a super type (survey_responses table record) nor just a subtype (which the foreign key constraint fortunately automatically prevents).

You can query them however you desire though. If you just want to make sure a record exists, simply query or join into the survey_responses table. If you want the actual response, construct your joins and queries appropriately.

Using this setup you can avoid having multiple null columns, and also protect against accidents where somehow a response gets two different types of responses. Although the lack of check constraints in MySQL will actually allow two types of responses in the subtype/supertype setup, the discriminator will let you identify the correct one when querying the data. (Any incorrect ones would be a result of bad coding in the application code.)


Transferring innodb table I have a 30GB innoDB-only database that keeps having Lost connection to MySQL server during query when dumping  when running mysqldump -u root -p --compress --max_allowed-packet=1G  --net-buffer-length=32704  --single-transaction largedb> /home/backup/largedb.sql.gz from the source server

I have net read and write timeouts set to 31536000 in my.cnf, and beyond this I cannot find any other information on why it would be losing connection.

So I decided that I'll just turn off mysql and rsync them. For tables under 1GB this worked out perfectly.
I first created the database and the table struct then ran ALTER TABLE {table} DISCARD TABLESPACE move the .frm and .ibd files into the database folder and then ran ALTER TABLE {table} IMPORT TABLESPACE which fixed all the tables except the main one.

The main table is 30GB  and when importing the tablespace it says Error in query (1034): Incorrect key file for table 'main'; try to repair it There is no additional information in error.log. I googled and it seems to suggest if you get this error but its an innoDB then you might be running out of diskspace in your tmpdir. My tmpdir is /tmp and I have 400GB free.

ANALYZE on table results in

InnoDB: Tablespace has been discarded for table 'main'
InnoDB: Trying to get the free space for table `db`.`main` but its tablespace has been discarded or the .ibd file is missing. Setting the free space to zero. (errno: 11 - Resource temporarily unavailable)
Trying to get the free space for table `db`.`main` but its tablespace has been discarded or the .ibd file is missing. Setting the free space to zero. (errno: 11 - Resource temporarily unavailable)
Operation failed

CHECK on table results in

db.main: InnoDB: Tablespace has been discarded for table 'main'
db.main: Tablespace has been discarded for table 'main'
db.main: Corrupt

Can anyone help me either get mysqldump to stop losing connection or how I can repair the innoDB table?
I am about to turn the table into MyISAM and rsync it

I am running Percona Mysql 8 (based off mysql 8).

You cannot rsync a running database, but you know that since you said you stopped it.

I'd you rsync with the mysqld not running (or from a snapshot), you have to rsync the ib_logfile* and ibdata* files from the datadir. Then there is no need to DISCARD/IMPORT TABLESPACE either.

For a copy without shutting down the database, xtrabackup is probably the most efficient solution.

If you have existing databases on the target, you will need to mysqldump --no-data dbname and import on the target in order to re-create the table structure metadata, but you will have FLUSH TABLE ... FOR EXPORT in the source before copying across. Then they will be clean and can be imported with ALTER TABLE ... IMPORT TABLESPACE


Scripting SQL Server into multiple SQL scripts I have a SQL Sever table with 2GB of data. I can create the script for the file. But the script file is not opening. I want to split that script file into multiple script files.

As @vonPryz said, I have the same feeling that you're trying to open a huge sql file (and SSMS's generated files are not the best in the world, as in they're pretty verbose by default).

As you most probably saw already, SSMS is not properly working with big files. It fails to properly parse them. I would suggest that you don't generate script files for your data, because this isn't MySQL's dump. Use full backups or, if you really want only one table, use bcp exports.

Here's the link to the documentation of the bcp tool. It's easy to use and you shouldn't have any problem to import it on another server.


Is there an sp_reset or the equivalent? Let's say I was monkeying around on SQL Server and I wanted to reset everything to factory defaults, is that currently possible?

EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'stupid dangerous thingz', 1

I want to do something like

EXEC sp_factory_reset, 1

sp_blitz will show you all non-default configuration settings and the details show what the default is.

Another option would be to use dbatools to export the config on a fresh install, using export-dbaspconfigure. Then import when you want to reset, using import-dbaspconfigure.

Export-DbaSpConfigure -ServerInstance . -path c:\temp\sp_configure.sql

This will create a file with all sp_configure options in this format

EXEC sp_configure 'show advanced options' , 1;  RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'recovery interval (min)' , 0;
EXEC sp_configure 'allow updates' , 0;
EXEC sp_configure 'user connections' , 0;
EXEC sp_configure 'locks' , 0;
EXEC sp_configure 'open objects' , 0;
EXEC sp_configure 'fill factor (%)' , 0;
EXEC sp_configure 'disallow results from triggers' , 0;

Then you can either run this file or the import-spdbaconfigure command, which will run the script for you

import-DbaSpConfigure -ServerInstance . -path c:\temp\sp_configure.sql


How to perform backup on quite active 500G+ MariaDB Galera Cluster? what would be your preferred method to perform backup on quite active 500G+ MariaDB Galera Cluster?

Using mariabackup/innobackupex (fork of Percona Xtrabackup) with throttle. You must think about FLUSH TABLES WITH READ LOCK which can cause problems to whole cluster if lock lasts to long. When using Percona XtraDB Cluster this isn't big problem because you have LOCK TABLES FOR BACKUP.
Put node in Desync state, perform backup, and turn off Desync mode after backup finishes. Here potential problem could be if backup takes too long, and it will take some time for a node to catch up.
Create Slave and perform backups on Slave. You have to monitor that slave is in sync with master. Maybe perform occasional consistency checks?
Use mysqldump. I think taking backup of 500G+ database using mysqldump would take too long.

Interested to hear your opinions.

Regards,
Marko

Plan A:  Using just the nodes of the cluster:

take a node out of the cluster: SET GLOBAL wsrep_desync = ON; SET GLOBAL wsrep_on = OFF; (See wsrep_desync and wsrep_on.)
back it up via Xtrabackup (or whatever).
add it back to cluster: SET GLOBAL wsrep_on = ON; SET GLOBAL wsrep_desync = OFF;
eventually IST will get that node in sync with the others.

Cons:  You lose a node for some time -- steps 2 and 4 are time consuming.
Need gcache to be big enough to allow for IST instead of SST.

Plan B:  Permanently add a slave to one of the nodes.  Take that Slave out of replication when you want to take a backup; then put it back into replication.

Con:  Requires another server.
Pros:  Not the Cons of Plan A.


Getting nice PostgreSQL pgAdmin-style DDL In pgAdmin 3 (and pgAdmin 4 if you're prepared to wait longer) the DDL for the currently selected database object is shown in the main panel, along with corresponding triggers, constraints, etc, all nicely formatted. It looks quite different to the DDL exported from pgdump. Is there a way to get the nicer version, without manually copy-and-pasting from pgAdmin?

Digging around in the pgAdmin source code it appears that the routines to generate the DDL are embedded within the application code. For instance, I the code to generate the table DDL can be found in pgTable::GetSql.


Did Microsoft change query optimizer regarding number of files and parallelism Did Microsoft change query optimizer regarding number of files and parallelism? Does the optimizer no longer factor in the number of files to determine the degree of parallelism for a query? If so, does anyone know when the change was made? If not, can anyone provide a link to Microsoft documentation that discusses the topic (current documentation for either SQL Server 2014 or 2016)?

That was an urban legend that Microsoft's Bob Dorr debunked back in 2007:

  This information has been taken out of context and propagated
  incorrectly as SQL Server has a thread per database file so adding
  more files to a database can increase I/O performance.   Adding more
  files and properly aligning them with storage can increase the I/O
  performance for a variety of reasons but achieving new I/O threads per
  file is NOT one of them.

Read the post for much more detail.


How to GRANT Permission on many views? Azure SQL We have to grant permissions to alter 6 Views to one user.
Is there any way to do it in one query, or do I have to make one query for each different view?

I have no problem doing it one by one because there's only six of them, but if I had to do it for 100 or 200, it would became impossible.

If you were still developing database, you could create views within a single schema, and add that user or group of users/role , permissions to alter that schema alongside permission to create view.
Such as :

GRANT ALTER ON SCHEMA::ViewSchema to ViewsEditorUser
GRANT CREATE VIEW to ViewsEditorUser

When you are all set and done, you can transfer those views from ViewSchema to dbo or some other more meaningful schema with command:

ALTER SCHEMA NewSchema TRANSFER ViewsSchema.FirstView

If views are already created, you can either add grant alter schema (name of the schema where the views reside) and add permission to user to create a view. But be aware that since you granted a user alter schema, he has all the rights on that schema, including truncating/droping tables etc..

Last option is creating a dynamic query that will grant a user/role to alter each of these views

Update

Using dynamic query would look something like this:

     DECLARE  @ViewTable as table (ID int, ViewName varchar(50))
     DECLARE @ID int = 1,@MaxID int,@ViewName varchar(50),@Query varchar(200)
     INSERT INTO @ViewTable
   select ROW_NUMBER() over (order by(select null)) as ID, sys.schemas.name + '.' + sys.all_views.name as name from sys.all_views
   inner join sys.schemas on all_views.schema_id = sys.schemas.schema_id
       where sys.all_views.name like '%FilterOnDesiredViews%'

    SET @MaxID = (select max(ID) from @ViewTable)

       while(@ID <= @MaxID)
       BEGIN
       SET @ViewName = (select ViewName from @ViewTable where ID = @ID)
       SET @Query = 'GRANT VIEW DEFINITION on ' +  @ViewName +' to SomeUser'
       exec (@Query)
       SET @ID = @ID + 1
        END

Note that i filtered the view names retrieval query with some silly name, you should apply your own filtering.


oracle clob export/import across non linked instances I'm trying to migrate table data across two instances(different instance schemas, but same table schema) that are not connected(not an option).

The problem is that I can not access host file systems and the table has large number of rows with CLOB data(with newlines of course).

TOAD exports with delimited text, insert statements, SQL loader(automatic TOAD script, not custom fitted) and MS Access has failed so far; TOAD either refuses or does't fill CLOB cells.

Is there any way to do this without buying specialized software or splitting CLOBs into mutiple rows and assembling them later?

Also I'm limited to the following:
Oracle db: 10.2.0.4.0
client: 9.2.0.1.0
TOAD: 9.6.0.27

If anybody has any ideas, it would help a lot, thanks!

You can use the legacy (but still supported) EXP utility to export a table/schema/DB to your local computer, and then use IMP to import the data on another instance.


subtract missing Value from Last Value Ok, so it's kind if difficult for me as a non-native english speaker to get this into writing, so please bear with me.

I have a source-Table as following:

CREATE TABLE [dbo].[Mailbox](
[ObjectSID] [nvarchar](184) NULL, --ObjectSID of Mailbox
[Database] [nvarchar](64) NULL, --Exchange Database
[PrimarySMTP] [nvarchar](254) NULL, -- eMailAddress
[ItemCount] [int] NULL, -- SUM of eMails
[Itemsize_MB] [int] NULL, -- size of Mails
[Itemsize_del_MB] [int] NULL, -- size of deleted mails
[Arch_Database] [nvarchar](64) NULL, -- Name of the Archive Exchange Database
[Arch_ItemCount] [int] NULL, -- Sum of all archived mails
[Arch_Itemsize_MB] [int] NULL, -- Size of archived mails
[Arch_Itemsize_del_MB] [int] NULL,  --Size of deleted archived mails
[ScanTime] [date] NULL  --Date of the last SCAN
) ON [PRIMARY]
GO

As you can Imagine from the Name, Ill write Exchange-Information into this Database for every Mailbox in our Company, every month.
SAMPLE-DATA

Insert into Mailbox Values
 (111,N'Database1',N'Sample.User1@domain.com',63913,16535,1,N'ARCH1',0,0,0,'2018-10-22')
,(111,N'Database1',N'Sample.User1@domain.com',63958,16540,2,N'ARCH1',0,0,0,'2018-10-24')
,(111,N'Database1',N'Sample.User1@domain.com',64533,16664,2,N'ARCH1',0,0,0,'2018-11-19')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-11-19')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-10-24')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-10-22')
,(333,N'Database1',N'Sample.User3@domain.com',55292,12723,23,N'ARCH1',37302,7128,0,'2018-10-22')
,(333,N'Database1',N'Sample.User3@domain.com',55532,12855,25,N'ARCH1',37306,7128,0,'2018-10-24')

My query results in 1 line per Scan (ScanTime) per Mailbox (ObjectSID)

WITH
MBBB (  ObjectSID
,Itemsize_MB
,Itemsize_del_MB
,Arch_Itemsize_MB
,Arch_Itemsize_del_MB
,ScanTime
,ROW
,[Database])
AS (SELECT  ObjectSID
,Itemsize_MB
,Itemsize_del_MB
,Arch_Itemsize_MB
,Arch_Itemsize_del_MB
,Scantime
,ROW_NUMBER() OVER(PARTITION BY ObjectSID ORDER BY ScanTime) ROW
,[Database]
FROM Mailbox),
Growth( [Database]
,ObjectSID
,Itemsize_MB
,Itemsize_del_MB
,Arch_Itemsize_MB
,Arch_Itemsize_del_MB
,ScanTime
,Growth)
AS (select S.[Database]
,S.ObjectSID
,S.Itemsize_MB
,S.Itemsize_del_MB
,S.Arch_Itemsize_MB
,S.Arch_Itemsize_del_MB
,S.ScanTime
,ISNULL((S.Itemsize_MB+S.Itemsize_del_MB+S.Arch_Itemsize_MB+S.Arch_Itemsize_del_MB),0)-ISNULL((X.Itemsize_MB+X.Itemsize_del_MB+X.Arch_Itemsize_MB+X.Arch_Itemsize_del_MB),0) Growth
FROM MBBB S
LEFT JOIN MBBB X
ON S.ObjectSID=X.ObjectSID
AND S.Row=X.Row+1
where s.ROW  >= (select MAX(s.ROW)-3
from MBBB s))

select
g.[Database]
,g.ObjectSID
,SUM(g.Itemsize_MB + g.Itemsize_del_MB + g.Arch_Itemsize_MB + g.Arch_Itemsize_del_MB) as [Mailbox in MB]
,g.Growth
,g.ScanTime
from Growth g
Group By g.ObjectSID, g.[Database], g.ScanTime ,g.Growth
order by g.[Database]

My Problem:
If a Mailbox (ObjectSID) is deleted, the new Size is 0. Therefore the Growth should be negative last size.
i.e  Mailbox of User A is 12GB in size on July. The Mailbox get's deleted, and is not listed in August (since it has been deleted) I want a Growth of -12GB (negative 12GB)
BUT, since the Mailbox is deleted, it is not showing as a new row_Number(Since Mailbox = NULL == No new ROW_Number() )
The Problem comes now with grouping in SSRS:
If I group all Mailboxes per Database, I see that for a Database If have a positive growth, but the SUM of all Mailbox-Sizes is decreasing.
i.e
Database__Old Size___Growth__New Size
Database1 __ 10GB __ _4GB____9GB
I need some kind of join, if entry in left table, and not in right, then substract from 0)
I hope this is half way understandable
Thank you all very much in advance.

You didn't specify your RDBMS or version, but I looked at another question you asked here and noticed you were using at least SQL Server 2016.  The following should work on SQL 2012 and later.  I added a few more rows to your sample data to show another month of scans (December) so I could make sure I only presented the subtraction of a dropped mailbox one time.

You'll note that ObjectSID 333 did not have scan data for November or December.  We reflect the drop during the November scan.

Take a look at my solution and let me know if I have misunderstood your requirements or you see an error in the results.

--demo setup
drop table if exists dbo.mailbox
CREATE TABLE [dbo].[Mailbox](
[ObjectSID] [nvarchar](184) NULL, --ObjectSID of Mailbox
[Database] [nvarchar](64) NULL, --Exchange Database
[PrimarySMTP] [nvarchar](254) NULL, -- eMailAddress
[ItemCount] [int] NULL, -- SUM of eMails
[Itemsize_MB] [int] NULL, -- size of Mails
[Itemsize_del_MB] [int] NULL, -- size of deleted mails
[Arch_Database] [nvarchar](64) NULL, -- Name of the Archive Exchange Database
[Arch_ItemCount] [int] NULL, -- Sum of all archived mails
[Arch_Itemsize_MB] [int] NULL, -- Size of archived mails
[Arch_Itemsize_del_MB] [int] NULL,  --Size of deleted archived mails
[ScanTime] [date] NULL  --Date of the last SCAN
) ON [PRIMARY]
GO
Insert into Mailbox Values
 (111,N'Database1',N'Sample.User1@domain.com',63913,16535,1,N'ARCH1',0,0,0,'2018-10-22')
,(111,N'Database1',N'Sample.User1@domain.com',63958,16540,2,N'ARCH1',0,0,0,'2018-10-24')
,(111,N'Database1',N'Sample.User1@domain.com',64533,16664,2,N'ARCH1',0,0,0,'2018-11-19')
,(111,N'Database1',N'Sample.User1@domain.com',64533,16664,2,N'ARCH1',0,0,0,'2018-12-19')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-11-19')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-10-24')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-10-22')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-12-19')
,(333,N'Database1',N'Sample.User3@domain.com',55292,12723,23,N'ARCH1',37302,7128,0,'2018-10-22')
,(333,N'Database1',N'Sample.User3@domain.com',55532,12855,25,N'ARCH1',37306,7128,0,'2018-10-24')

My solution

;WITH MailBoxAgg
AS (
    --Select all distinct ScanTimes from Mailbox and join
    --against Mailbox where the ScanTime <= one of the distict ScanTimes
    --If the Mailbox ScanTime <> Distinct ScanTime, that means the mailbox was dropped
    --so force 0 into [Mailbox in MB]
    SELECT [database]
        ,ObjectSID
        ,CASE
            WHEN mb.ScanTime <> st.ScanTime
                THEN 0
            ELSE sum(Itemsize_MB + Itemsize_del_MB + Arch_Itemsize_MB + Arch_Itemsize_del_MB)
            END AS [Mailbox in MB]
        ,mb.ScanTime
        ,st.ScanTime AS stscan
    FROM (
        SELECT DISTINCT ScanTime
        FROM Mailbox
        ) st
    JOIN Mailbox mb
        ON mb.ScanTime = (
                SELECT max(ScanTime)
                FROM Mailbox
                WHERE [Database] = mb.[Database]
                    AND ObjectSID = mb.ObjectSID
                    AND ScanTime <= st.ScanTime
                )
    GROUP BY ObjectSID
        ,[Database]
        ,mb.ScanTime
        ,st.ScanTime
    )
SELECT [database]
    ,objectsid
    ,[Mailbox in MB]
    ,CASE
        --if the Mailbox ScanTime <> DistinctScanTime, get the
        --previous row value and negate it to represent negative
        --growth for a dropped mailbox
        WHEN scantime <> stscan
            THEN lag([mailbox in MB]) OVER (
                    PARTITION BY [database]
                    ,objectsid ORDER BY scantime
                    ) * - 1
        --if the previous row [mailbox in MB] is null,
        --this is the first row for the mailbox, so force current MB as growth
        WHEN lag([mailbox in MB]) OVER (
                PARTITION BY [database]
                ,objectsid ORDER BY scantime
                ) IS NULL
            THEN [mailbox in MB]
        --subtract the previous mailbox mb from the current to reflect growth
        ELSE [mailbox in MB] - lag([mailbox in MB]) OVER (
                PARTITION BY [database]
                ,objectsid ORDER BY scantime
                )
        END AS Growth
    ,ScanTime
    ,stscan
FROM MailBoxAgg

The results

| database  | objectsid | Mailbox in MB | Growth | ScanTime   | stscan     |
|-----------|-----------|---------------|--------|------------|------------|
| Database1 | 111       | 16536         | 16536  | 2018-10-22 | 2018-10-22 |
| Database1 | 111       | 16542         | 6      | 2018-10-24 | 2018-10-24 |
| Database1 | 111       | 16666         | 124    | 2018-11-19 | 2018-11-19 |
| Database1 | 111       | 16666         | 0      | 2018-12-19 | 2018-12-19 |
| Database1 | 333       | 19874         | 19874  | 2018-10-22 | 2018-10-22 |
| Database1 | 333       | 20008         | 134    | 2018-10-24 | 2018-10-24 |
| Database1 | 333       | 0             | -20008 | 2018-10-24 | 2018-11-19 |
| Database1 | 333       | 0             | 0      | 2018-10-24 | 2018-12-19 |
| Database2 | 222       | 10878         | 10878  | 2018-10-22 | 2018-10-22 |
| Database2 | 222       | 10878         | 0      | 2018-10-24 | 2018-10-24 |
| Database2 | 222       | 10878         | 0      | 2018-11-19 | 2018-11-19 |
| Database2 | 222       | 10878         | 0      | 2018-12-19 | 2018-12-19 |


Help with indexing narrow table with many millions of rows I have a good amount of experience with SQL Server, but not much with indexes and I fear I'm a little bit out of my depth in trying to figure out how to correctly structure a table that has 100+ million rows. I know this gets asked a lot, but I haven't found an answer that matches exactly what I'm trying to do.

The table

CREATE TABLE [dbo].[Trades]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TimeStamp] [datetime] NOT NULL,
    [Type] [int] NOT NULL,
    [Amount] [decimal](18, 2) NOT NULL,
    [Price] [decimal](18, 2) NOT NULL,
    [Exchange_Id] [int] NOT NULL
)

As you can see, this is a table that stores commodity trades. Because of that, there are currently 100+ million rows in the table, with about 10,000 rows being added daily (at the end of the day).

I'm at a bit of a loss as to how to index this table to maximize performance. I don't so much care about insert performance since the table is updated once a day and it doesn't need to be a fast operation. Rows are also never updated once inserted. The big problem is query speed. This table is queried relatively often.

By far the most common queries are on the [TimeStamp] column alone (eg, TimeStamp between two dates), or on the [TimeStamp] column together with the [Exchange_Id] column (eg, trades on a certain Exchange between two [TimeStamp]s).

Currently, there is a unique clustered index on the [Id] column, but I'm not sure if that's the right thing to do based on the circumstances. I've considered making the clustered index on the [TimeStamp] column, but it would have to be non-unique which I've read is generally bad. As for non-clustered indexes, I'm not quite sure how to arrange them or on what columns.

Any help is greatly appreciated.

Based on your question, I would index the Timestamp column with the clustered index. And to make the index unique, just make sure to include the identity column in the index definition:

... PRIMARY KEY CLUSTERED ([Timestamp], [Id])

If query performance for queries on Exchange_Id is still an issue after that, you can also add a non-clustered index that looks something like this:

UNIQUE INDEX ([Exchange_Id], [Timestamp], [Id])

.. but if you do, consider including any columns that the query may need, in order to create what's known as a covering index.

UNIQUE INDEX ([Exchange_Id], [Timestamp], [Id]) INCLUDE ([Type], [Amount], [Price])

Remember that there's a disk space issue involved as well, as you stated that your table contains a large number of rows. The clustered index will not change the amount of disk space your table consumes either way, but adding a non-clustered index will allocate extra space. If you INCLUDE all the columns from your table, like I did in the example, the non-clustered index will roughly take up as much space as the rest of the table does.


Mysqli prepare error: Unknown storage engine 'InnoDB' I'm getting this error on my XenForo installation. Server logs show out of memory because of too many php processes opened up which cause mysqld to shutdown. The WordPress installation associated with MyISAM works fine. The error goes away once I have restarted the mysql service. I'm unable to fix the issue. Would be grateful for some input!

MySQL logs:

..more similar errors above..
150117 20:18:56 [ERROR] Cannot find or open table wpdb/wp_wpsa_subscribe_author from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.

150117 21:24:38 [Warning] IP address '222.186.15.72' could not be resolved: Name or service not known
150117 21:38:35 mysqld_safe Number of processes running now: 0
150117 21:38:35 mysqld_safe mysqld restarted
150117 21:38:38 [Note] Plugin 'FEDERATED' is disabled.
150117 21:38:38 InnoDB: The InnoDB memory heap is disabled
150117 21:38:38 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150117 21:38:38 InnoDB: Compressed tables use zlib 1.2.3
150117 21:38:38 InnoDB: Using Linux native AIO
150117 21:38:39 InnoDB: Initializing buffer pool, size = 119.0M
InnoDB: mmap(127713280 bytes) failed; errno 12
150117 21:38:39 InnoDB: Completed initialization of buffer pool
150117 21:38:39 InnoDB: Fatal error: cannot allocate memory for the buffer pool
150117 21:38:39 [ERROR] Plugin 'InnoDB' init function returned error.
150117 21:38:39 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
150117 21:38:39 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
150117 21:38:39 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
150117 21:38:39 [Note] Server socket created on IP: '0.0.0.0'.
150117 21:38:40 [Note] Event Scheduler: Loaded 0 events
150117 21:38:40 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.40-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
150117 21:38:40 [ERROR] /usr/sbin/mysqld: Table './wpdb/wp_wfConfig' is marked as crashed and should be repaired
150117 21:38:40 [ERROR] /usr/sbin/mysqld: Table './wpdb/wp_wfConfig' is marked as crashed and should be repaired
...more similar errors follow..

The logs at /var/log/messages show loads of PHP processes opened (50+) and server going out of memory, shutting down mysqld. I couldn't find anything that would be making so many processes stuck up. Could the mysql error before the crash occurs - "Cannot find or open table" be causing the error?

Error:   mmap(127713280 bytes) failed; errno 12 : not enough memory to start the service:

Possible solutions:

add swap page
check innodb_buffer_pool_size value it may be too low to start the service or too high for the server

After that try to delete ib_logfile# then start the service


ODBC Data Source Administrator: Driver name is an empty string Below you will see a screen shot of a warning in Windows 7.  Why would I get this error when opening up "Data Sources (ODBC)"?  How can I resolve it?  If you can give me some tips on where to start looking, I can make edits to this question to help diagnose the issue.

It's not critical that I fix this since I'm still able to add new data sources, but it's just annoying to see it every time I go into this dialog.

Control Panel > Administrative Tools > Data Sources (ODBC) > click "System DSN" tab

One other thing I'm noticing is that when I go into the "System DSN" tab for the first time, no data sources show up.  If I click "Add" and then cancel, the list then populates with three data sources.

I had a key in this registry path that was not pointing to anything.  So I removed it.

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI

(this is the 64 bit window that pops up)

Realize that there is also a 32 bit window for ODBC that exists for this registry path:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI

C:\Windows\SysWOW64\odbcad32.exe

(Yes, even though it says WOW64, it means 32 bit.)


Trouble in querying data I'm having difficulty in retrieving data from tables.
Bellow is the scenario I'm stuck at.
TableA

Id
Name

1
ABC

2
DEF

3
GHI

4
JKL

TableB

Id
TableA_Id
Tag

1
1
X

2
2
Y

3
0
Z

TableC

Id
TableA_Id
Something

1
1
XXX

2
1
XXX

3
1
XXX

4
2
XXX

5
2
XXX

6
3
XXX

7
3
XXX

8
4
XXX

9
4
XXX

TableA joins With TableB by TableA_Id column, the only difference is that where there is no reference of TableA id then we are supposed to return the row with TableA_Id = 0 from TableB.
Required Output

Name
Tag
Something

ABC
X
XXX

ABC
X
XXX

ABC
X
XXX

DEF
Y
XXX

DEF
Y
XXX

GHI
Z
XXX

GHI
Z
XXX

JKL
Z
XXX

JKL
Z
XXX

SELECT t1.name,
       COALESCE(t2.tag, t3.tag) tag,
       t4.something
FROM tableA t1
LEFT JOIN tableB t2 ON t1.id = t2.TableA_Id
JOIN tableB t3 ON t3.TableA_Id = 0
JOIN tableC t4 ON t1.id = t4.TableA_Id

TableB must contain only one row with TableB.TableA_Id = 0. If not then use
SELECT t1.name,
       COALESCE(t2.tag, t3.tag) tag,
       t4.something
FROM tableA t1
LEFT JOIN tableB t2 ON t1.id = t2.TableA_Id
CROSS JOIN ( SELECT *
             FROM tableB
             WHERE TableA_Id = 0
             -- ORDER BY something, for example, by `Id`
             LIMIT 1 ) t3
JOIN tableC t4 ON t1.id = t4.TableA_Id

fiddle


What security threats does the SA account and other known account names pose to security? Does an known account name like sa, pose a security threat to database? When using windows authentication on SQL Server does it impose the same password policy(if it was set to say account lockout after 5 times)?

Does an known account name like sa, pose a security threat to database?

A "god" user account with a known name is generally considered a worse idea than a god user with a less well known name. It makes brute force attacks that bit easier as the attacker only has to guess the password and not the username and the password.

Also having a god user anyway can be dangerous. You are generally better off having specific users with specific rights for what they need to do. This sort of privilege based security is easier to implement from scratch than it is to retrofit into your environment later.

Disabling sa and giving specific users specific admin rights as needed in SQL server is essentially the same recommendation as disabling root and handing out admin rights as needed via sudo under Linux and similar. You can always re-enable sa once directly connected to the machine with adequate privileges should anything go wrong and you end up dropping all the rights your users need to operate (and fix the issue) just the same as you can engineer root access to a Linux box if you have physical access to the box - so disabling the account is no magic bullet (but once an attacker has physical access to your machine, or full Administrative access via RDC or SSH, all bets are off anyway).

  When using windows authentication on SQL Server does it impose the same password policy(if it was set to say account lockout after 5 times)?

When using Windows Integrated Authentication SQL server has no control over account lockouts and such - it just maps a Windows user to an SQL user and asks the OS to vouch for the fact that the user has provided appropriate credentials. For interactive human users this means any lockout would occur as the user attempted to authenticate with Windows, not as they logged in to SQL Server.


SQL Server - How does Always On in a shared nothing config maintain transaction durability during a failover? I've been reading up on Always On, and I was wondering how transactional durability is maintained during a failover when using a shared nothing configuration.

The only things I can find on google talk about log shipping, but this would mean that if a failover occurs in-between log restores, then some transactions could be lost.

You use synchronous commit mode. This means transactions committed on the primary will also be committed on the secondary (over your network). So, if you failover during a transaction it will be rolled back when the secondary is brought online after recovery.

https://msdn.microsoft.com/en-us/library/ff877931.aspx

  Synchronous-commit mode protects your data by requiring the data to be synchronized between two places, at the cost of somewhat increasing
  the latency of the transaction.


How to interpret timestamps stored as 10-digit negative integers? I've inherited an undocumented MS Access database where the date/time values are stored in an INT column and the majority of values I've seen so far are 10-digit negative integers.

How do you process these as if they were DATETIME in SQL queries?

If it simply can't be done through SQL and must instead be done through a programming/scripting language, what maths/process is required to transform these values into human-readable dates and times?

Example values:
-1358667448, -1358667348, -1358667248

The data was captured during the period 2006-2019 so the timestamps will be within that time frame.

Per Your comment the only place the values show as time stamps is in the application. You don't have access to the applications code.

The only possible solution, is to get a handful of actual time values shown in the application and relate them to values in the field, than figure how the difference is calculated. Then you will need to recalculate the values and add them to your database.

I strongly suggest that you do not replace the source data.  Possibly add columns to the table with the correctly formatted dates.


Included Column versus Filtered Index We currently work with table named tb_tranfers. This table has 40 million rows and is ~26 GB in size (11 GB data, 15 GB indexes).

10 to 15% of the rows are soft-deleted rows (DeletedDate is not null). The applicaiton only uses rows where DeletedDate is null. All queries to this table will include a clause to that effect.

There are 15 indexes on this table. The missing index DMV contains suggestions to create indexes with DeletedDate as an included column.

Would it be helpful to use a filtered index WHERE DeleteDdate IS NULL on all 11 nonclusetered indexes? Or would it be better to have the DeletedDate column as an included column?

Yes, modifying all 11 NC indexes to be filtered indexes (CREATE INDEX ... ON ... WHERE DeletedDate IS NULL) would help. This way you get two advantages:

The Query Optimizer will know that any row coming from these indexes already does satisfy your query filters on DeletedDate, so it won't have to lookup the clustered index to double check the DeletedDate
All NC indexes will be 10-15% smaller in size, requiring less memory and fewer IO operations to lookup.

The trade off is that any query that looks after soft deleted rows (and there have to be some query, otherwise why do the rows exist?) will be unable to use these NC indexes.


SQL Server 2012 IO Latency and Wait Types I'm working on a system that has a resource intenstive houskeeping job. It hits two different data files on the same LUN. One is for relational data, one for blobs. During the job, read latency on the relational data file averages over 250ms, while on the LOB data it is only 5ms. This is measured using sys.dm_io_virtual_file_stats (two samples 12 hours apart to encompass the houskeeping - during the other 12 hours, latency is 20ms).

I'm sampling wait stats using "who is active". In this I don't see any PAGEIOLATCH waits with large enough wait times to explain the 250ms. sys.dm_os_wait_stats backs this up with an average of 5ms for PAGEIOLATCH_EX and 15ms for PAGEIOLATCH_SH. What I do see is long waits for LCK_M (which is explainable, due to the nature of the housekeeping).

My question is: can the LCK_M waits contribute to the io_stall_read_ms times in the virtual file stats?

In case the details of the housekeeping matter, it is removing smallish batches of data using multiple cascading deltes, including ~1MB of LOB data per item. The system is using AlwaysOn with sync commit.

The waits that are seen in sys.dm_io_virtual_stats are aggregates instead of point values. If you are doing a lot of transactions in a certain period, it will show you an higher aggregate --> higher average latency. More information on how this dmv works can be found on https://sqlperformance.com/2013/10/t-sql-queries/io-latency

  Virtual file stats are a great starting point when you want to
  understand I/O performance for a SQL Server instance. If you see
  I/O-related waits when looking at wait statistics, looking at
  sys.dm_io_virtual_file_stats is a logical next step. However,
  understand that the data you're viewing is an aggregate since the
  stats last cleared by one of the associated events (instance restart,
  offline of database, etc).  If you see low latencies, then the I/O
  subsystem is keeping up with the performance load. However, if you see
  high latencies, it's not a foregone conclusion that storage is a
  problem. To really know what's going on you can start to snapshot file
  stats, as shown here, or you can simply use Performance Monitor to
  look at latency in real time. It's very easy to create a Data
  Collector Set in PerfMon that captures the Physical Disk counters Avg.
  Disk Sec/Read and Avg. Disk Sec/Read for all disks that host database
  files.

The LCK_M will not contribute to the IO_STALL_READ_MS as these counters are only IO related


Tracking (un)successful autovacuums in postgres I am trying to help a team of junior, senior, principal and chief (mostly JEE) developers to be more data-centric and data-aware. In some cases we look into the data-processing costs, complexity of the algorithms, predictability of the results and statistical robustness of the estimates for query plans. In other cases we blindly believe that use of indexes is always great and scan of the tables is always bad. Sometimes we just opportunistically throw gazillions of insert, update, delete queries into the DB and hope for the best. We run load tests afterwards and we notice that our tables and indexes are bloated beyond imagination, the tables became pretty much unmanageable in size and chaos rules the area.
A good way to proceed is to train and learn complexity classes, understand the costs, have the right attitude. This change is very fruitful but hard and slow. As long as I am breathing, I'll continue this journey.
For now we are trying to understand why autovacuum for some tables kicks in so seldomly. We've got a postgres server (v9.5 I believe) running in azure cloud (test environment). We pay for 10K IOPS, and we use them fully (we write to the DB like hell). In the last 24 hours I see that autovacuum was run only 2 times for two large tables through
select * from pg_stat_all_tables order by last_autovacuum desc

In order to trigger an autovacuum, I created:
create table a(a int)

ALTER TABLE a SET (autovacuum_vacuum_scale_factor  = 0.0 );
ALTER TABLE a SET (autovacuum_vacuum_threshold     = 10  );
ALTER TABLE a SET (autovacuum_analyze_scale_factor = 0.0 );
ALTER TABLE a SET (autovacuum_analyz_threshold     = 10  );

and ran the following two statements multiple times:
delete from a;
insert into a (a) select generate_series(1,10);

This should have triggered an autovacuum on the table, but pg_stat_all_tables has NULL for last_autovacuum column for table a.
We also set log_autovacuum_min_duration to a very low value (like 250ms or even 0), but the only two entries in the logs are:
postgresql-2021-02-18_010000.log:2021-02-18 01:56:29 UTC-602a2e9c.284-LOG:  automatic vacuum of table "asc_rs.pg_toast.pg_toast_3760410": index scans: 1
postgresql-2021-02-18_060000.log:2021-02-18 06:35:47 UTC-602a2e9c.284-LOG:  automatic vacuum of table "asc_rs.pg_toast.pg_toast_3112937": index scans: 1

Our settings are:

We have a feeling that autovacuum is killed on large tables because of row-locks. Can we log this information in any way? Can we also log (failing) autovacuum attempts? How does postgres decide to start  an autovacuum job  (or more generally speaking tradeoffs between regular changes in the DB vs. maintenance jobs) on a very high load system? If parameters for kicking off of autovacuum are meat, would it definitely be kicked off or wait until the load of IOs decrease?
EDIT:
We do not see any errors/breaks w.r.t. the autovacuums in the logs (thanks Laurenz Albe)
DETAIL:  while scanning block 5756 of relation "laurenz.vacme"

First figure out what PostgreSQL version you have. If it is truly 9.5, upgrade before you do anything else. I mean it. The rest of my answer deals with recent PostgreSQL versions.
A row lock won't block autovacuum progress (unless it is an anti-wraparound vacuum), but a table lock can. These are logged if you set log_autovacuum_min_duration to something else than -1:
LOG:  skipping vacuum of "vacme" --- lock not available

or
ERROR:  canceling autovacuum task
DETAIL:  while scanning block 5756 of relation "laurenz.vacme"

Keep all autovacuum settings at default values, except autovacuum_vacuum_cost_delay, which you set to 2ms.
Now why is your autovacuum not running? Some ideas:

You didn't commit the transaction that deleted the rows.

The autovacuum workers are doing something else. Check pg_stat_activity.

Check for oddities in the log file.


Dog slow query, needs optimizing, please help I found this query in our web app that has been made by hired programmers. It performs like a dog and needs optimization, but I'm no MySQL wizard (yet), unfortunately. Please help, anything is appreciated.

This is the query:

SELECT DISTINCT test_user_info_test.ID
FROM test_user_info_test, test_user_saver_test, test_user_filter_test
WHERE test_user_info_test.ACTIVE = '1'
   AND (
         ( test_user_info_test.BALANCE >= '1' AND test_user_info_test.ID IN
           (
              SELECT USERID
              FROM test_user_filter_test
              WHERE symbol = 'x'
                     AND INCLUDE ='1')
           )
        OR
           (
        HAS_IT = '1' AND EXPIRE > '1337500000'
        AND
        test_user_info_test.ID IN
              (
                 SELECT USERID
                 FROM test_user_saver_test
                 WHERE symbol = 'x'
                      AND INCLUDE = '1'
              )
          )
     )

Currently, the tables don't have any indexes.

Description of what the query is supposed to do:
it needs to select the user id's from active users that have a positive balance and have set  a filter for symbol X, in this example. Also it needs to select the user id's from users who have a special subscription that hasn't expired yet and have set a filter for symbol X.

I think the execution of the query can take up to quite a few minutes, which is clearly unacceptable.

Here's a modification you could make... (caveat - I'm a SQL Server person):

SELECT DISTINCT
  test_user_info_test.ID
FROM
  test_user_info_test,
  left outer join test_user_saver_test
    on test_user_info_test.id = test_user_filter_test.USERID
    and symbol = 'x'
    AND INCLUDE ='1'
  left outer test_user_filter_test
    on test_user_info_test.id = test_user_saver_test.USERID
    and symbol = 'x'
    AND INCLUDE = '1'
WHERE
  test_user_info_test.ACTIVE = '1'
  AND
  (
  (test_user_info_test.BALANCE >= '1' and test_user_filter_test.USERID is not null)
  or
  (test_user_info_test.HAS_IT = '1' AND test_user_info_test.EXPIRE > '1337500000' and test_user_saver_test.USERID is not null)
  )

Also add indexes on test_user_info_test.id, test_user_info_test.id, test_user_saver_test.USERID.

Those are the quick things that jump out at me...


User rank based on cumulative rows UPDATE:
Full table schema and data: https://www.db-fiddle.com/f/jT3Mfwd1BJ8GrsBxxPDC8M/1

Table structure (some fields are hidden):

+---------+---------+------------+-------------+-------+-------+
| user_id | kind_id | country_id | province_id | value | score |
+---------+---------+------------+-------------+-------+-------+
|       1 |     201 |          1 |           1 |     1 |     5 |
|       1 |     102 |          1 |           1 |    31 |   155 |
|       1 |     201 |          1 |           1 |     6 |    30 |
|       1 |     109 |          1 |           1 |    33 |   165 |
|       2 |     202 |          1 |           4 |    76 |   380 |
|       2 |     201 |          1 |           4 |     5 |    25 |
|       2 |     209 |          1 |           4 |     9 |    45 |
|       2 |     302 |          1 |           4 |     8 |    40 |
|       2 |     201 |          1 |           4 |    14 |    70 |
|       3 |     201 |          1 |           9 |     7 |    35 |
|       3 |     201 |          1 |           9 |     9 |    45 |
|       3 |     201 |          1 |           9 |     2 |    10 |
|       3 |     201 |          1 |           9 |     5 |    25 |
|       3 |     201 |          1 |           9 |     7 |    35 |
|     ... |     ... |        ... |         ... |   ... |   ... |
+---------+---------+------------+-------------+-------+-------+

Rank is calculated based on sum of score of user's activities.
I saw many questions here and on Stackoverflow but my problem is more complex.

I need 3 rank values per user: global, country (for users in same country), province (same as country). For example a rank value for user 1 may be like this {"global": 9, "country": 3, "province": 1}. I do not need rank of all users but for one individual user.

I tried a dozen queries but can't calculate this three with one query. or, at least, three clean and efficient queries.

I will cache the result for some time, but still efficiency is important since the activities table contains many records and will grow as time passes.

How can I do this?

  Server version: 10.1.37-MariaDB-0+deb9u1 Debian 9.6

I don't think I understand your question, but you may benefit from a rollup. MySQL have there own version of grouping sets that look like:

SELECT country_id
     , province_id
     , user_id
     , SUM(score) AS total_score
FROM activities
GROUP BY country_id, province_id, user_id
WITH ROLLUP;

null in user_id column means an aggregation, dito för province_id. I.e.

1   null    null    64390

is the total for country_id = 1


Foreign key on partial primary key This is implemented in Oracle, if it matters.

I understand how to build foreign keys to tables with composite primary keys. Is it possible to have a foreign key reference a table with a composite primary key with a static value for one of the key values?

Imagine I want to have a table of status codes. I could easily have a table of status codes for each purpose (i.e. one for ThingStatus, one for OtherThingStatus, etc.). Trivial, but leads to lots of small tables. I could also have a single table with all the status codes, with each record also identifying what the status code applies to (shown below).

create table StatusCode (
  StatusTable  varchar2(20) not null,
  StatusValue  varchar2(20) not null
);

alter table StatusCode add constraint pkStatusCode primary key (StatusTable, StatusValue);

create table Thing (
  ThingId integer not null, -- primary key
  TableName varchar2(20) default 'Thing',
  ThingStatus varchar2(20) not null
);

alter table Thing add constraint fkThingStatus
  foreign key (TableName, ThingStatus)
  references StatusCode(StatusTable, StatusValue);

Pretty straightforward, easy to implement, annoying to have a field (present in every record) that exists solely to satisfy the foreign key definition.

Is there a way to do something like

create table NewThing (
  NewThingId integer not null, -- primary key
  NewThingStatus varchar2(20) not null
);

alter table add constraint fkNewThingStatus
  foreign key ('NewThing', NewThingStatus)
  references StatusCode(StatusTable, StatusValue);

I could also just treat StatusCode as a look up table and not implement the foreign key reference explicitly, but I'd rather see it explicitly implemented.

From version 11g, you can use a virtual computed column:

create table StatusCode (
  StatusTable  varchar2(20) not null,
  StatusValue  varchar2(20) not null,
  primary key (statustable, statusvalue)
) ;

create table Thing (
  ThingId integer not null primary key,
  TableName varchar2(20) generated always as ('Thing') virtual,
  ThingStatus varchar2(20) not null,
  foreign key
          (TableName, ThingStatus)
      references StatusCode
          (StatusTable, StatusValue)
) ;


Performance Degradation Troubleshooting I get periodic slows where the CPU goes high for a period of time which I can occasionally simulate via a query. It almost seems like it happens when the data falls out of cache which seems to happen more than I'd expect. The ram stays at 3.1GB of 3.5GB. Yes, that's probably too low but the system had been running for a couple years without much issue until recently (not sure if over period of time or a specific event). I'm trying to decide if we need to pay for more ram, CPU, or disk IO to our hosting provider. Would low ram cause CPU to go high? CPU: AMD Opteron Processor 4171 HE 2.10 GHz

Begin Edit
It looks like sqlserv process is not causing the high cpu but aahStorage.exe is. I believe this is the process that retrieves data from proprietary files on disk for Wonderware System Platform. So I'm not sure if it's slow Disk IO, low memory available, etc. causing it.
End Edit

I created the following query

SELECT
REPLACE(counter_name, '(KB)', '(MB)'),
CASE WHEN counter_name LIKE '%(KB)%' THEN CAST((cntr_value/1024) as varchar(1000)) + ' MB' ELSE CAST(cntr_value as varchar(1000)) END
FROM
(
    select TOP 1000
    ('MemoryMgr: ' + counter_name) as counter_name,
    cntr_value
    FROM sys.dm_os_performance_counters
    WHERE object_name='SQLServer:Memory Manager'
    UNION ALL
    select TOP 1000
    ('BufferMgr: ' + counter_name) as counter_name,
    cntr_value
    FROM sys.dm_os_performance_counters
    WHERE object_name='SQLServer:Buffer Manager'
) sub1

And have the following results;

MemoryMgr: External benefit of memory     0
MemoryMgr: Connection Memory (MB)         2 MB
MemoryMgr: Database Cache Memory (MB)     485 MB
MemoryMgr: Free Memory (MB)               128 MB
MemoryMgr: Granted Workspace Memory (MB)  0 MB
MemoryMgr: Lock Memory (MB)               2 MB
MemoryMgr: Lock Blocks Allocated          12328
MemoryMgr: Lock Owner Blocks Allocated    12703
MemoryMgr: Lock Blocks                    6751
MemoryMgr: Lock Owner Blocks              6855
MemoryMgr: Maximum Workspace Memory (MB)  725 MB
MemoryMgr: Memory Grants Outstanding      0
MemoryMgr: Memory Grants Pending          0
MemoryMgr: Optimizer Memory (MB)          1 MB
MemoryMgr: Reserved Server Memory (MB)    0 MB
MemoryMgr: SQL Cache Memory (MB)          11 MB
MemoryMgr: Stolen Server Memory (MB)      1226 MB
MemoryMgr: Log Pool Memory (MB)           1 MB
MemoryMgr: Target Server Memory (MB)      2000 MB
MemoryMgr: Total Server Memory (MB)       1841 MB
BufferMgr: Buffer cache hit ratio         1121
BufferMgr: Buffer cache hit ratio base    1121
BufferMgr: Page lookups/sec               7048998623
BufferMgr: Free list stalls/sec           0
BufferMgr: Database pages                 62196
BufferMgr: Target pages                   7176192
BufferMgr: Integral Controller Slope      21
BufferMgr: Lazy writes/sec                8896
BufferMgr: Readahead pages/sec            961391
BufferMgr: Page reads/sec                 1090105
BufferMgr: Page writes/sec                1308925
BufferMgr: Checkpoint pages/sec           1204609
BufferMgr: Background writer pages/sec    0
BufferMgr: Page life expectancy           134774

I'm having a hard time interpreting the results. Any help is appreciated. Thanks

Converting my comments to answer ....

  Total Physical RAM = 3583, Virtual = 3583, (1) 21GB, (1) 13GB, (8) 4GB, a handful of smaller ones plus 17GB proprietary file storage. The power plan is currently Balanced.

Based on your above comment, with a mere 3.5GB RAM, you are running dbs totaling the size of 65+ GB. There will be resource starvation on the database server.

Increase the RAM close enough to the size of db and change the power plan to high performance.

Then monitor your db server with sp_whoisactive or SQL-Server-First-Responder-Kit and start tuning when you have data.


Extract all tags from hstore field I need to extract (copy) all hstore tags by object id from a postgres 9.2+postgis 2.1 database into an external file for post-processing. Since the keys and values vary per record, and I don't know what the keys or values are, I have to use wildcards.

Regretably, my secret decoder ring seems to have let me down, because nothing I've tried has worked, possibly because all my expertise is in vanilla sql and mysql/mariadb.

Many thanks for help with this.

You are looking for the skeys function:

select skeys(hstore_column)
from the_table;

If you also need the values for each key:

select (a).key, (a).value
from (
  select each(hstore_column) as a
  from the_table
) t;

More details in the manual: http://www.postgresql.org/docs/current/static/hstore.html


Inheritance or lot of relationship? I have to transpose a class Diagram into a Physical Data Model (more precisely MySQL database structure).

In my class diagram, I have a lot of entities (A, B, C, D) which inherit from an other entity, named PARENT.

PARENT entity have a relationship PARENT_OTHER of cardinality (0.* - 0.*) with an entity named OTHER.

I had thought of two methods :

My first method is to use "Concrete Table Inheritance". I don't transposed the PARENT entity into a table but I have one concrete table for each children entities : T_A, T_B, T_C, T_D.
However I must create the PARENT_OTHER relationship for each tables T_A, T_B, T_C, T_D.

Schema :

So, if I have N childrens entities, I have 2 * N Tables... (without the table T_OTHER).

The second method is to conserve the inheritance (Class Table Inheritance).I have one table by children : T_A, T_B, T_C, T_D and one table for PARENT entity : T_PARENT.

Schema :

So, if I have N childrens entities, I have N + 2 Tables... (without the table T_OTHER).

Out of the number of tables, the second solution offer a bit more of abstraction... I can look directly in the table T_PARENT_OTHER for all children without joins.

Some people say me that I should avoid inheritance in RDBMS (Relational Database Management System), like MySQL DBMS, and I suppose that it's true.

I think this is common problem in database modelisation.

I would like to know what is the best way in this kind of situation ?

Are there other ways ?

Thanks in advance and please excuse my english.

This is just a common answer.
Avoid MYSQL for OOP Concepts for the time being. OODBMS stil under research in some areas.If you are Maping OOP business/program logic to a RDBMS(ORDBMS)  and still expecting open source go for PostgreSQL(PGSQL). PGSQL is not a full OODBMS. But Inheritance can be achieved there.
Your methods may result in unusual processing cycles thus results in excessive workload to the system. If you are going with your methods (In between Helper Tables ), make sure you using correct Indexes.
And, still if you are preferring MYSQL, Use High level of Abstraction to achieve Specialization down the Inheritance hierarchy and link via Foreign keys with parents. Do Normalization  up to 3NF correctly. Yes! this is the usual Relational Way!!


Nonclustered index column order I have a nonclustered index (called NCIDX1) on col1, col2 of a table. I included col3, col4, col5, col6 as included columns in this order.

If I run the following query:

select
     col1,col2
from
     tbl1
where
     col3 = something
and
     col4 = something
and
     col5 = something

Is it any different than running:

select
     col3,col2 ,col4
from
     tbl1
where
     col1 = something
and
     col5 = something

?

What I am trying to say is, when we create a covering index like the above, and we change the order in which we are accessing data, will it still take advantage of this covering index?

Also, the order of equality columns (amongst themselves) and non equality columns (amongst themselves)... does it matter? Like where equalitycol2='' and equalitycol1=''.

Generally, an index should be on

(equalitycol1, equalitycol2, ..., nonequalitycol1, , nonequalitycol2, ...)
INCLUDE
(outputonlycol1, outputonlycol2, ...)

Simply (ignoring ranges) this breaks down into

equalitycol: WHERE equalitycol = something. Most selective first. SARGable. Seeks.
nonequalitycol: WHERE nonequalitycol <> something. Residual filters
outputonlycol: SELECT outputonlycol. No filtering or ordering

ORDER BY and GROUP BY columns will be equality or non-equality columns.

In this case...

Query 1 doesn't match the index at all, really, so most likely won't use the index. However, the optimiser may decide it's cheaper to use the index then use other table access such as a table scan or a RID/bookmark lookup.

For query 2, the optimiser may decide to use the index because col1 would be an equality match. And then use the unsorted col5 from the INCLUDE. YMMV, but there is a higher chance.

For the WHERE clause order, no: the optimiser works it out. Two indexes (col1, col2) and (col2, col1) are different though. Note (col1, col2) will be contained by (col1, col2, col3, col4) though so the first isn't needed.


Foreign Key problem, ORA-00907: missing right parenthesis on creating table & ORA-01735: invalid ALTER TABLE option my main problem is when I create these tables particularly the Member table, it does not add the foreign key constraints for the other tables created, I keep getting errors: ORA-00907: missing right parenthesis on creating table & ORA-01735: invalid ALTER TABLE option (if using the alter table options below the table creation, I am using oracle application express (apex.oracle.com)

I have searched other questions regarding this, but they were mostly related to stored procedures or querying a table, nothing with table creation.

CREATE TABLE MemberAddress (
 MemAddID NUMBER GENERATED AS IDENTITY START WITH 1 INCREMENT BY 1,
 Address VARCHAR2(60) NOT NULL,
 SecAddress VARCHAR2(60),
 PostCode VARCHAR2(8) NOT NULL,
 City VARCHAR2(40),
 CONSTRAINT MEM_ADD_PK PRIMARY KEY (MemAddID)
);

CREATE TABLE MemberType (
 MemTypeID NUMBER GENERATED AS IDENTITY START WITH 1 INCREMENT BY 1,
 MemType VARCHAR2(10),
 AccountStatus VARCHAR2(20),
 CONSTRAINT MEM_TYPE_PK PRIMARY KEY (MemTypeID)
);

CREATE TABLE Members (
 MemberID NUMBER GENERATED AS IDENTITY START WITH 1 INCREMENT BY 1,
 MemberFirstname VARCHAR2(50) NOT NULL,
 MemberMiddlename VARCHAR2(50),
 MemberLastname VARCHAR2(50) NOT NULL,
 MemberDOB DATE NOT NULL,
 Email VARCHAR2(60) NOT NULL UNIQUE,
 Mobile VARCHAR2(11) NOT NULL UNIQUE,
 Telephone VARCHAR2(13) UNIQUE,
 MemAddID NUMBER,
 MemTypeID NUMBER,
 CONSTRAINT MEM_ID_PK PRIMARY KEY (MemberID)
 CONSTRAINT MEM_ADD_FK FOREIGN KEY (MemAddID) REFERENCES MemberAddress (MemAddID) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT MEM_TYPE_FK FOREIGN KEY (MemTypeID) REFERENCES MemberType (MemTypeID) ON DELETE CASCADE ON UPDATE CASCADE
);

I used these alter table options to test whether the SQL could add the foreign key out of table definition, however I get the error ORA-01735: invalid ALTER TABLE option.

-- ALTER TABLE Members ADD CONSTRAINT MEM_ADD_FK FOREIGN KEY (MemAddID) REFERENCES MemberAddress (MemAddID) ON DELETE CASCADE ON UPDATE CASCADE;

-- ALTER TABLE Members ADD CONSTRAINT MEM_TYPE_FK FOREIGN KEY (MemTypeID) REFERENCES MemberType (MemTypeID) ON DELETE CASCADE ON UPDATE CASCADE;

I'm not new to SQL or a pro at it, if there is a best practise im not following here, let me know?

I do not understand what is wrong with this; whether its a syntax problem or oracle application express has problems, in MySQL it works great when the syntax is changed to suit that DB.

any suggestions?

Oracle database does not support ON UPDATE CASCADE clause.

references_clause::=

The below works:

CREATE TABLE Members (
 MemberID NUMBER GENERATED AS IDENTITY START WITH 1 INCREMENT BY 1,
 MemberFirstname VARCHAR2(50) NOT NULL,
 MemberMiddlename VARCHAR2(50),
 MemberLastname VARCHAR2(50) NOT NULL,
 MemberDOB DATE NOT NULL,
 Email VARCHAR2(60) NOT NULL UNIQUE,
 Mobile VARCHAR2(11) NOT NULL UNIQUE,
 Telephone VARCHAR2(13) UNIQUE,
 MemAddID NUMBER,
 MemTypeID NUMBER,
 CONSTRAINT MEM_ID_PK PRIMARY KEY (MemberID),
 CONSTRAINT MEM_ADD_FK FOREIGN KEY (MemAddID) REFERENCES MemberAddress (MemAddID) ON DELETE CASCADE,
 CONSTRAINT MEM_TYPE_FK FOREIGN KEY (MemTypeID) REFERENCES MemberType (MemTypeID) ON DELETE CASCADE
);


MV on remote/master table fails to resume syncing (after master db host was temporarily unreachable) I have a fast-refresh materialized view in a target db (18c) that syncs from a remote table/master db (19c):
--master db (19c)
create materialized view log on master.workorder with primary key;

--target db (18c)
create materialized view target.wo_mv
build immediate
refresh fast
start with sysdate
next sysdate + (15/(60*60*24))
as (
select
    workorderid
    --other columns
from
    master.workorder@my_dblink
);

My organization has ongoing issues where the master database's host becomes intermittently "unreachable". This happens daily and lasts for approximately 15 minutes. It's a serious issue that is being investigated and is beyond my control.

My DBA tells me that we get ORA-12514 & ORA-3150 errors in the target db when the master db is unreachable.
ORA-12514 TNS:listener does not currently know of service requested in connect descriptor

ORA-03150: end-of-file on communication channel for database link

Problem:
When the master db becomes unreachable, of course, the MV in the target db fails to sync. That makes sense.
However, what doesn't make sense to me is: the MV in the target db doesn't resume syncing once the master db comes back online.

Records fail to sync from the master MV logs to the target MV. The records just sit in the master MV log table and nothing happens.

Question:
As a novice, I would have expected the MV in the target db to automatically resume once the master db came back online. Everything else that uses the dblink between the two dbs works fine. Just not the MV sync.
Is there a reason why the MV in the target db wouldn't automatically resume when the master db comes back online?

If the controlling scheduled DBMS_JOB job fails, it may need to be manually reset/restarted. If you want more flexibility/resiliency with this, use Oracle Scheduler to control the refresh execution, rather than the default DBMS_JOB. Do this by leaving the start and next portions out of the MV definition:
create materialized view target.wo_mv
build immediate
refresh fast
as (
select
    workorderid
    --other columns
from
    master.workorder@my_dblink
);

Then build a Scheduler job with a call to DBMS_REFRESH to refresh your MV:
begin
   dbms_refresh.make(
     name                 => '15_MINUTE_REFRESH',
     list                 => '',
     next_date            => sysdate,
     interval             => '/*15:mins*/ sysdate + 15/(60*24)',
     implicit_destroy     => false,
     lax                  => false,
     job                  => 0,
     rollback_seg         => null,
     push_deferred_rpc    => true,
     refresh_after_errors => true,
     purge_option         => null,
     parallelism          => null,
     heap_size            => null);
end;
/

begin
   dbms_refresh.add(
     name => '15_MINUTE_REFRESH',
     list => 'WO_MV',
     lax  => true);
end;
/

commit;

Be sure to run all commands as the schema/user which owns the materialized view.
Also see here: https://oracle-base.com/articles/19c/dbms_job-jobs-converted-to-dbms_scheduler-jobs-19c#mview-refresh-groups


MYSQL Dense Rank Trigger or Update Statement I have a MYSQL table that I need to rank. Each group of child_no needs to be ranked by Parent_NO. I have a select statement see below, that successfully does the ranking. Now I'm looking at at taking the results of the select and actually updating the table. I would ideally like to do this with a trigger on insert,else an update statement that I can run manually will suffice.

Any help on formulating either option would be greatly appreciated. See example on sqlfiddle (I've added an auto id, and target rank column in by statements below): http://sqlfiddle.com/#!9/ebe33/18

Table

CREATE TABLE ranktest
(`parent_no` varchar(10),
 `child_no` varchar(10),
 `rankfinal` int,
 `id`   MEDIUMINT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
);

INSERT INTO ranking
(`parent_no`, `child_no`)
VALUES
('1234', '20'),
('1234', '21'),
('1234', '21'),
('9845', '50'),
('9845', '50'),
('9845', '52'),
('9845', '52'),
('9845', '53'),
('9845', '53'),
('9845', '55'),
('5678', '60'),
('2468', '10'),
('2468', '10');

Select Statement to gather rank

set @rank = 0;
set @prev_child = NULL;
set @prev_parent = NULL;
select parent_no, child_no, rank from (
    select parent_no, child_no, CASE
     when @prev_parent != parent_no then @rank := 1
     when @prev_child = child_no then @rank
     else @rank := @rank + 1
END as rank,   @prev_parent:= parent_no, @prev_child:=child_no
from ranktest
order by parent_no, child_no
) as rankcalc

Switch to MariaDB 10.2 and use their "dense rank" windowing function.

https://mariadb.com/kb/en/mariadb/dense_rank/


Multiple small deletes I have a script that loops over records of people (~4 million) and executes multiple updates (~100) and a single delete statement (all of these updates and delete are on different tables).  The problem I am facing is that the one delete statement takes about half the run time by itself.  I understand that when you execute a delete statement, it needs to update the index, but I find it rather ridiculous. I am currently testing this script with one thread using dbms_parallel_execute but I plan to multithread this script.

I am executing a query similar to the following:

DELETE FROM table1 t1
WHERE (t1.key1, t1.key2) IN (SELECT t2.key1, t2.key2
                               FROM table2 t2
                              WHERE t2.parm1 = 1234
                                AND t2.parm2 = 5678).

Following facts:

Table2 (~30 million records) is ~10 times larger than table1 (~3 million records).
There is a primary key on table1(key1, key2)
There is a primary key on table2(key1, key2)
There is an index on table2(parm1, parm2)
I have disabled the foreign key constraint on table1(key1, key2) that references table2(key1, key2)
There are no other constraints on table1, but many more constraints on table2.
All triggers on table1 have been disabled
The explain plan for this query comes up with a cost lower than that of many of my update statements (but I know this doesn't account for much).

Explain plan output:

DELETE STATEMENT                    6
DELETE
NESTED LOOPS                        6
NESTED LOOPS                        6
TABLE ACCESS        BY INDEX ROWID  4
INDEX               RANGE SCAN      3
INDEX               UNIQUE SCAN     1
TABLE ACCESS        BY INDEX ROWID  2

I was wondering if there were any way to make this delete go faster.  I tried to do a bulk delete but it didn't seem to improve the run time.  If there were any way to execute all the deletes and then update the index after, I suspect it would run faster.  Obviously doing a create table from a select is out of the picture since I am looping over records (and running through multiple conditions) from another table to do the delete.

I'd consider:

If the table is partitioned - to disable the index partitions on the specific partitions which are going to be changed, and rebuild (rebuild online probably) them after the operation.
Increase the amount of memory in disposal (for the specific session), so that it won't do any I/O operations (on temporary tablespace) at all, by changing workarea_size_policy to 'manual' and  sort_area_size, hash_area_size to the maximum size you can spare for this operation.
Run the entire operation using parallel hint (or parallel_index if you'd decide to use some indexes after all).
Switch all the accessed objects to nologging mode, so that redo logs won't be generated during the operation (and switch back to logging right after it's finished of course).


In SQL Server and other systems that support ROW_NUMBER()...

WITH GoalsWithGoalNum (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY [In]
            ORDER BY [Goal ID]
        ) AS GoalNum
    FROM Goals
)
SELECT *
FROM GoalsWithGoalNum
WHERE GoalNum <= 2;


Assignment of first group element - MySQL 4.7 I would like to assign the first element of a certain group to 0. The following tables are respectively the original and the final result.
CREATE TABLE IF NOT EXISTS t1 (
   id_ INTEGER  NOT NULL,
   value1 INT NOT NULL
)

CREATE TABLE IF NOT EXISTS result (
   id_ INTEGER  NOT NULL,
   new_col INT NOT NULL
)

INSERT INTO t1 (id_, value1) VALUES
(1, 1),
(2, 1),
(2, 1),
(3, 1),
(3, 1),

INSERT INTO result (id_, new_col) VALUES
(1, 0),
(2, 0),
(2, 1),
(3, 0),
(3, 1)

This will run so only in MySQL, in MariaDB you will need a LIMIt in the inner SELECT
The principal thing is the ORDER  of the inner SRLCT, which determines , which rownumber (rn) it will get. here i choose ORDER BY id to achieve your result.
But as mustachio pointed out you have to define an order, so that this will work

CREATE TABLE IF NOT EXISTS t1 (
   id_ INTEGER  NOT NULL,
   value1 INT NOT NULL
)

INSERT INTO t1 (id_, value1) VALUES
(1, 1),
(2, 1),
(2, 1),
(3, 1),
(3, 1)

SELECT id_, value1
FROM (SELECT
 IF(id_ =  @id,@rn := @rn +1,@rn := 0) value1
 ,@id :=  id_ as id_
FROM t1,(SELECT @id := 0 ,@rn := -1) t2
ORDER BY id_) t3

id_ | value1
--: | -----:
  1 |      0
  2 |      0
  2 |      1
  3 |      0
  3 |      1

db<>fiddle here


MS SQL: Can't connect to server with anything but 127.0.0.1 on the server machine I'm not able to connect to sql server with anything but 127.0.0.1 on the server machine.  If I try to connect via actual IP on the network it does not work locally or remotely.

The environment is SQL Server 2005 on Windows Server 2000.  My goal is to connect to this SQL Server instance from a remote machine.

I've tried telneting to port 1433 using the actual IP and that fails.
I've tried turning off Windows Firewall completely

Any ideas or help is greatly appreciated!
Thanks

Are you running multiple instances?

Make sure that TCP connections are enabled.

Make sure a connection is allowed by your firewall, or that the firewall is disabled.

If you have trouble with either of these after making changes, try re-starting the DB and SQL Server Browser (or disable Browser).