Better way to full text query across multiple tables? I've got a rather large query across multiple tables and their fulltext-indices which (unsurprisingly) is rather slow:

SELECT t0.[Key], t0.[Nr]
FROM dbo.[Customers_View] AS t0
 WHERE (t0.[Deleted] IS NULL)
AND (EXISTS (SELECT [Key] FROM dbo.[Customers] AS t1 WHERE t0.[Key] = t1.[Key] AND CONTAINS(*, '"smith"'))
 OR EXISTS (SELECT [Key] FROM dbo.[CustomersAddresses] AS t2 WHERE t0.[Key] = t2.[CustomerKey] AND CONTAINS(*, '"smith"'))
 OR EXISTS (SELECT [Key] FROM dbo.[CustomersContacts] AS t3 WHERE t0.[Key] = t3.[CustomerKey] AND CONTAINS(*, '"smith"'))
 OR EXISTS (SELECT [Key] FROM dbo.[CustomersPartners] AS t4 WHERE t0.[Key] = t4.[CustomerKey] AND CONTAINS(*, '"smith"')))
AND (EXISTS (SELECT [Key] FROM dbo.[Customers] AS t5 WHERE t0.[Key] = t5.[Key] AND CONTAINS(*, '"munich"'))
 OR EXISTS (SELECT [Key] FROM dbo.[CustomersAddresses] AS t6 WHERE t0.[Key] = t6.[CustomerKey] AND CONTAINS(*, '"munich"'))
 OR EXISTS (SELECT [Key] FROM dbo.[CustomersContacts] AS t7 WHERE t0.[Key] = t7.[CustomerKey] AND CONTAINS(*, '"munich"'))
 OR EXISTS (SELECT [Key] FROM dbo.[CustomersPartners] AS t8 WHERE t0.[Key] = t8.[CustomerKey] AND CONTAINS(*, '"munich"')))

Is there a good way to speed up this Kind of query? Usually it is a bit more complex, I simplyfied it to the full text part.

Maybe there is a better way to to fulltext queries across multiple tables?

I do not know how dynamic your queries are, but naturally each table needs a separate query for that table.

Assuming that you are constantly querying this 4 table group, it might be worthwhile to create an indexed view that included the searchable fields you need from the 4 tables into a single view.

This means the overhead of maintaining the indexed view, but may help in this case.

Of course, if you have a widely varying array of tables that might be joined, you would need to consider whether this is a good idea for you or whether you should query each table separately as you are doing.


Dockerized Sysbench could not connect to Mysql I'm trying to follow this tutorial.

It does not give Mysql version requirements. So I'd tried to use Mysql 8.0.18 Community Server to connect to using those commands.

I've created what have been required inside of Mysql 8:

mysql> CREATE SCHEMA sbtest;
mysql> CREATE USER sbtest@'%' IDENTIFIED BY 'MyPassword+1';
mysql> GRANT ALL PRIVILEGES ON sbtest.* to sbtest@'%';

Than created script file with the first command:

[root@host ~]# cat sbpreparing
docker run \
--rm=true \
--name=sb-prepare \
severalnines/sysbench \
sysbench \
--db-driver=mysql \
--oltp-table-size=100000 \
--oltp-tables-count=24 \
--threads=1 \
--mysql-host=192.168.1.200 \
--mysql-port=3306 \
--mysql-user=sbtest \
--mysql-password=MyPassword+1 \
/usr/share/sysbench/tests/include/oltp_legacy/parallel_prepare.lua \
run

and launched it:

[root@host ~]# ./sbpreparing
sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 1
Initializing random number generator from current time

Initializing worker threads...

Threads started!

thread prepare0
Creating table 'sbtest1'...
FATAL: unable to connect to MySQL server on host '192.168.1.200', port 3306, aborting...
FATAL: error 2059: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib/x86_64-linux-gnu/mariadb18/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
FATAL: `thread_run' function failed: /usr/share/sysbench/tests/include/oltp_legacy/common.lua:66: Failed to connect to the database
Error in my_thread_global_end(): 1 threads didn't exit

I could connect to the MySQL server from the same machine using command:

mysql -h 192.168.1.200 -u sbtest -pMyPassword+1

But sysbench couldn't. How could I fix it? Thank you!

With the help of mustaccio I've resolved the issue by following instructions in this answer:

Removed a comment sign before the next string in /etc/my.cnf:

default_authentication_plugin=mysql_native_password
Connected as root:

mysql -u root -pMyPassword+1
Executed the next query:

ALTER USER 'sbtest'@'%' IDENTIFIED WITH mysql_native_password BY 'MyPassword+1';

After following these instructions Sysbench successfully connected to Mysql 8 and executed all preparation tasks successfully.


You must pivot your data.

This can be done using the Pivot operator:

SELECT ID, [date]
    , [1] as Hour1, [2] as Hour2, [3] as Hour3, [4] as Hour4
    , total as [Sum]
FROM (
    SELECT * FROM data d
    CROSS APPLY (SELECT total = SUM([money]) FROM data
        WHERE [date] = d.[date] AND ID = d.[id]) a
) t
PIVOT (
    MAX([money])
    FOR [hour] IN ([1], [2], [3], [4])
) piv

You are using SQL Server 2005 and therefore the total is calculated using CROSS APPLY. See SQL Fiddle.

Without PIVOT, you can also use a GROUP BY with CASEs:

SELECT ID, [date]
    , Hour1 = SUM(CASE WHEN [hour] = 1 THEN [money] END)
    , Hour2 = SUM(CASE WHEN [hour] = 2 THEN [money] END)
    , Hour3 = SUM(CASE WHEN [hour] = 3 THEN [money] END)
    , Hour4 = SUM(CASE WHEN [hour] = 4 THEN [money] END)
    , [Sum] = SUM([money])
FROM data d
GROUP BY ID, [date]

See SQL Fiddle.

With SQL Server 2012 you could easily calculate the total using the SUM(...) OVER(...) window function:

SELECT ID, [date], [1] as Hour1, [2] as Hour2, [3] as Hour3, [4] as Hour4, [Sum]
FROM (
       SELECT *, [Sum] = SUM([money]) OVER(PARTITION BY [date], ID) FROM data
) d
PIVOT (
       MAX([money])
       FOR [hour] IN ([1], [2], [3], [4])
) piv

See SQL Fiddle.

Note that a window function with an aggregate such as SUM(...) OVER(...) also works with SQL Server 2005 when the OVER(...) clause only contains a PARTITION BY ... and no ORDER BY .... See OVER Clause (2005) (Thanks Andriy M for the link).

Output:

ID  date    Hour1   Hour2   Hour3   Hour4   Sum
1   20-1    10      20      30      40      100
1   21-1    50      10      20      30      110
2   20-1    40      50      10      20      120


How to store table values in order I created a table and inserted values into it but they are not storing in orderly manner.
The table looks like:

I can order them by using ORDER BY but it does not change them permanently. Is there a way to save them in orderly manner permanently.

Clarification: I don't want how they are stored internally, what I want is when ever I execute select * from cricket; the results to come sequentially. So that I don't want to use ORDER BY clause every time. Is there a way?

Without an ORDER BY clause, you have no guarantees as to the order the rows are presented to you, even if they 'look' ordered when you run a select.  Bullet 1 of this link

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


Is there any view or table to show how long a session have been in Idle state in Oracle? As my question title, Is there any view or table to show how long a session have been in IDLE state in Oracle ?

So that I can monitor how long this session have been in IDLE state.

It doesn't mean how to set session expired-time.

Yes, v$session stores this information.

v$session.LAST_CALL_ET:

  If the session STATUS is currently ACTIVE, then the value represents
  the elapsed time in seconds since the session has become active. If
  the session STATUS is currently INACTIVE, then the value represents
  the elapsed time in seconds since the session has become inactive.

Documentation link.


PostgreSQL covering index on large table as alternative to CLUSTER command I'm looking for an alternative to executing the CLUSTER command on large tables to keep data efficiently organized on disk, since it requires an ACCESS EXCLUSIVE lock on tables being clustered, making them unavailable for a considerable amount of time.

After reading about covering indexes I got the idea of creating a btree multi-column index specifying all of my table columns, and not just those relevant for the query condition.

The benefits, I suppose, would be twofold:

The query plan would be optimized due to the condition relevant columns being specified first on the index, according to the query's expected data fetch pattern.
All remaining columns would also be included in the index, meaning this index would be a copy of the table and already organized efficiently on disk due to the btree structure.

I believe queries over this index would be very efficient and since it is covering the whole table wouldn't need to access table data scattered on disk.

Would like to ask if this a reasonable solution, and what are the potential downsides to this approach? (besides the additional storage space required for the covering index and expected - small? - decrease in insert/update/delete performance)

PostgreSQL does not have covering indexes.  There has been some work on them, but it has not yet been accepted into the code tree.

So there are limitations.  If you have unique constraint on some prefix of the indexed columns, you will have to have a separate index to support that, it can't piggy-back on your larger index.  Also, some data types do not support Btree operators which means they cannot be included into a btree index. For example, XML or many geometric types.

Also, an indexed value cannot exceed 1/3 of a page, or about 2700 bytes, so inclusion of some wide columns into the index could fail on this grounds (I don't think covering indexes, if they existed, would solve this problem)

Finally, you only avoid consulting the table for those table pages marked as "all visible".  If the table is mostly static, then is probably not a problem.  But it the table is very dynamic, it will take very aggressive vacuuming to maintain the "all visible" count.

Partitioning the table may be another option.  It is not nearly as fine-grained as clustering it, but can impose less of an ongoing burden.  Particularly if all the active updates go against 1 or 2 partitions, while the rest are effectively read-only.


Isolation levels and rows not yet read Under RR in transaction T1 I do a
SELECT * FROM mytable WHERE city='London'

In another T2 I do
UPDATE mytable SET price=100 WHERE city='London' and id=5000

Is it possible that T2 reads,updates and commits the row in question BEFORE T1 gets to read it ?
1.If that is so,then RR would not have an effect,or as a matter in this scenario no isolation level would have an effect becasue isolation concerns only rows that they've been ALREADY read,not those not yet read.True?
2.Is however SERIALIZIBLE different? Does it take key range locks which means it locks the rows BEFORE it has read them? So would SERIALIZIBLE in the code example above
SELECT * FROM mytable WHERE city='London'

take any key ranges locks that would prevent the scenario of T2 getting to the row and updating before it is read?

Q1

Is it possible that T2 reads,updates and commits the row in question
BEFORE T1 gets to read it ?

A1
Yes, as noted by Paul White here:

The repeatable read isolation level provides a guarantee that data
will not change for the life of the transaction once it has been read
for the first time.

Also due to lock escalation the query can start with row locks and then escalate to a full table lock. If the update runs while row locks are being taken and the row has not been accessed yet, then the update will not be blocked.
Q2

If that is so,then RR would not have an effect,or as a matter in this scenario no isolation level would have an effect becasue
isolation concerns only rows that they've been ALREADY read,not those
not yet read.True?

A2
Yes,
you could use full table locking with a TABLOCK hint to make sure that the entire table is locked in RR but that reduces concurrency.
SELECT * FROM dbo.mytable WITH(TABLOCK) WHERE city='London';

Of course, if you are selecting a lot of data anyway the table lock escalation might happen nonetheless.
This is commonly around the 5000 row mark but can be higher/lower.
Q3

Is however SERIALIZIBLE different? Does it take key range locks which means it locks the rows BEFORE it has read them? So would
SERIALIZIBLE in the code example above take any key ranges locks that
would prevent the scenario of T2 getting to the row and updating
before it is read?

A3
Yes, it takes range locks and the considerations for equality range locks are noted by Sunil Agarwal here:

Equality Predicate

If the key value exists, then the range lock is only taken if the        index is non-unique. In the non-unique index case, the ‘range’ lock
is taken on the requested key and on the ‘next’ key. If the ‘next’
key does not exist, then a range lock is taken on the ‘infinity’
value. If the index is unique then a regular S lock on the key.
If the key does not exist, then the ‘range’ lock is taken on the    ‘next’ key both for unique and non-unique index. If the ‘next’ key
does not exist, then a range lock is taken on the ‘infinity’ value.

In your case the key exists, and the index is probably non-unique so you would have a range lock up to the next value or the infinity value.
However I would be wary of using the serializable isolation level, it can introduces deadlocks and other concurrency issues.


SQL Agent powershell context reference At my new job, we have multiple named instances on each servers. e.g.

Server1\Dev
Server1\DevIntegrated
Server1\QA

I have a SQL PowerShell script in the works that calls out to the OS, invokes Foo.exe but needs to pass a command line parameter (the connection string). A SQL Agent job will exist on each instance, with a step of type PowerShell, that needs to know what the current context is. i.e. This execution began on DevIntegrated.

I have no desire to have every script start with...

$thisInstance = "Dev"

...especially since I'd have to edit that when we migrate to environments (new servers and named instances) in the upcoming months.

If I start SQLPS, I can determine my instance by slicing and dicing the results of Get-Location or running

(Invoke-Sqlcmd -Query "SELECT @@servername AS ServerName" -SuppressProviderContextWarning).ServerName

When the SQL Agent starts a job of type PowerShell, it starts in the C:\windows\system32 and the Get-Location route doesn't work as it's not in the SQLSERVER context. I can change into that context but I'll be at the "root" of SQL Server and won't know what instance I should be in. Using the Invoke-Sqlcmd route won't work either for same reason (technically, it times out as there is no default instance)

To the best of my knowledge, I've enumerated all the basic "things" I can get into the job log but nothing seems to show SQLSERVER:\SQL\Server1\DevIntegrated

Get-ChildItem
Get-Host
Get-Location
Get-Process
Get-PSDrive
Get-PSProvider
Get-Service
Get-TraceSource
Get-Variable

Get-Process seems like I could use that and some voodoo of trying to cobble things together by hitting the instances and matching spids but that just sounds like a bloody-hack from hell. There must be something basic that I'm missing, can anyone shed some light?

Alternatives to PowerShell investigated

I had investigated using other job types and didn't get a satisfactory resolution. Research indicated the PowerShell listed under SQL Agent was SQLPS and starting an instance of it by right clicking on the Agent automagically dropped me into the correct location. It was only when I pasted my interactive code into the job step that I learned of the difference as previously mentioned.

Job type of OS put me into an identical state in that I could not find a way to determine which instance dropped me into the command shell. Sure, I could sqlcmd and get the value of @@servername but if I knew what connection to start sqlcmd, I wouldn't need to to query the database ;)

TSQL could probably work if we enable xp_cmdshell but I'm not sure if they have it turned on---government facility and they can be persnickety on non-default settings. Even then, I'm stuck futzing with dynamic SQL and losing a lot of the expressiveness and power that PowerShell lends.

While a bit ungainly, I thought defining a variable at the first step and passing that to successor steps but research turned up this article Handling Multiple Job Steps (BOL)

  Job steps must be self-contained. That is, a job cannot pass Boolean values, data, or numeric values between job steps. You can, however, pass values from one Transact-SQL job step to another by using permanent tables or global temporary tables. You can pass values from job steps that run executable programs from one job step to another job step by using files.

I can't use common tricks like a well-known file/environment variables/registry setting that Foo.exe looks for since that would prevent concurrent execution across instances.

TL;DR:

In a SQL Agent Job step of type PowerShell, how can you determine the instance of SQL Server that launched the process?

If you look in SQL Server BOL, SQL Server Agent provides a set of "tokens" that it will substitute into both the job step command text and the output file (the later will prevent the GUI "view" button from working). These tokens seem to work for any type of step except T-SQL.

https://docs.microsoft.com/en-us/sql/ssms/agent/use-tokens-in-job-steps#sql-server-agent-tokens

So, if you have a SQL 2008 PowerShell step, you can start it with:

$sqlInstance = "$(ESCAPE_DQUOTE(SRVR))"

You may need to use MACH (machine name) and INST (just instance name) instead, because with the default instance SRVR == MACH, but with named instances SRVR == MACH\INST.


Is there a query to set a table's engine to innodb if it's not already? I can set a (MyISAM) table's engine to InnoDB using the query:

alter table tablename engine=InnoDB

which takes some time to complete for large tables. It appears though that if I run the same query again on the same table, it takes some time again (much less but still considerable). I would expect the query to be instantaneous, since the engine is already set to innodb.

My questions are:

Is there a single query that conditionally sets the engine, for example

alter table tablename engine=InnoDB <if not already innodb>
Why does the second query have such a delay? (out of curiosity mostly)

This will mass convert all MyISAM tables to InnoDB

SQLSTMT="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE engine='MyISAM' AND"
SQLSTMT="${SQLSTMT} table_schema NOT IN "
SQLSTMT="${SQLSTMT} ('information_schema','mysql','performance_schema')"
SQLSTMT="${SQLSTMT} ORDER BY (data_length+index_length)"
echo "SET SQL_LOG_BIN = 0;" > ConvertMyISAMToInnoDB.sql
mysql -uroot -p -ANe"${SQLSTMT}" > ConvertMyISAMToInnoDB.sql
mysql -uroot < ConvertMyISAMToInnoDB.sql

Why does running ALTER TABLE mydb.mytable ENGINE=InnoDB; take long ?

Under the hood it runs something like this:

CREATE TABLE mydb.mytabletmp LIKE mydb.mytable;
INSERT INTO mydb.mytabletmp SELECT * FROM mydb.mytable;
DROP TABLE mydb.mytable;
ALTER TABLE mydb.mytabletmp RENAME mydb.mytable;

The delay comes when building a brand new Clustered Index (home of PRIMARY KEY entries)

If you want to totally automate the conversion of just one table, you need this:

SET @mydb = 'mydb';
SET @mytb = 'mytable';
SET @newengine = 'InnoDB';
SELECT IF(dbtb2='.',CONCAT('ALTER TABLE ',dbtb1,' ENGINE=InnoDB'),
CONCAT('SELECT ''',dbtb1,' is Already ',engine,''' as \"No Need to Convert\"'))
INTO @ConvertEngineSQL FROM (SELECT CONCAT(A.db,'.',A.tb) dbtb1,
CONCAT(IFNULL(B.db,''),'.',IFNULL(B.tb,'')) dbtb2,engine
FROM (SELECT table_schema db,table_name tb,engine FROM information_schema.tables
WHERE table_schema=@mydb and table_name=@mytb) A LEFT JOIN
(SELECT table_schema db,table_name tb FROM information_schema.tables
WHERE table_schema=@mydb and table_name=@mytb AND engine=@newengine) B
USING (db,tb)) AA;
SELECT @ConvertEngineSQL;
PREPARE st FROM @ConvertEngineSQL; EXECUTE st; DEALLOCATE PREPARE st;

Here is a sample from my PC's MySQL setup

mysql> show create table codewaggle.mytable\G
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SET @mydb = 'codewaggle';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @mytb = 'mytable';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @newengine = 'InnoDB';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT IF(dbtb2='.',CONCAT('ALTER TABLE ',dbtb1,' ENGINE=InnoDB'),
    -> CONCAT('SELECT ''',dbtb1,' is Already ',engine,''' as \"No Need to Convert\"'))
    -> INTO @ConvertEngineSQL
    -> FROM (SELECT CONCAT(A.db,'.',A.tb) dbtb1,
    -> CONCAT(IFNULL(B.db,''),'.',IFNULL(B.tb,'')) dbtb2,engine
    -> FROM (SELECT table_schema db,table_name tb,engine FROM information_schema.tables
    -> WHERE table_schema=@mydb and table_name=@mytb) A LEFT JOIN
    -> (SELECT table_schema db,table_name tb FROM information_schema.tables
    -> WHERE table_schema=@mydb and table_name=@mytb AND engine=@newengine) B
    -> USING (db,tb)) AA;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @ConvertEngineSQL;
+----------------------------------------------+
| @ConvertEngineSQL                            |
+----------------------------------------------+
| ALTER TABLE codewaggle.mytable ENGINE=InnoDB |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> PREPARE st FROM @ConvertEngineSQL; EXECUTE st; DEALLOCATE PREPARE st;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

Query OK, 0 rows affected (1.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

mysql> SET @mydb = 'codewaggle';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @mytb = 'mytable';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @newengine = 'InnoDB';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT IF(dbtb2='.',CONCAT('ALTER TABLE ',dbtb1,' ENGINE=InnoDB'),
    -> CONCAT('SELECT ''',dbtb1,' is Already ',engine,''' as \"No Need to Convert\"'))
    -> INTO @ConvertEngineSQL
    -> FROM (SELECT CONCAT(A.db,'.',A.tb) dbtb1,
    -> CONCAT(IFNULL(B.db,''),'.',IFNULL(B.tb,'')) dbtb2,engine
    -> FROM (SELECT table_schema db,table_name tb,engine FROM information_schema.tables
    -> WHERE table_schema=@mydb and table_name=@mytb) A LEFT JOIN
    -> (SELECT table_schema db,table_name tb FROM information_schema.tables
    -> WHERE table_schema=@mydb and table_name=@mytb AND engine=@newengine) B
    -> USING (db,tb)) AA;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT @ConvertEngineSQL;
+-----------------------------------------------------------------------+
| @ConvertEngineSQL                                                     |
+-----------------------------------------------------------------------+
| SELECT 'codewaggle.mytable is Already InnoDB' as "No Need to Convert" |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> PREPARE st FROM @ConvertEngineSQL; EXECUTE st; DEALLOCATE PREPARE st;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+--------------------------------------+
| No Need to Convert                   |
+--------------------------------------+
| codewaggle.mytable is Already InnoDB |
+--------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

Give it a Try !!!


permission denied on mongod.lock, despite it being owned by mongodb this is my mongod.lock:

-rw-r--r-- 1 mongodb mongodb    0 Dec  3 17:09 mongod.lock

hence this solution doesn't apply: https://dba.stackexchange.com/a/183018

this is how I start mongodb:

sudo systemctl start mongodb

and this is the output in mongod.log:

2019-12-03T17:12:01.676+0100 I  STORAGE  [initandlisten] exception in initAndListen: Location28596: Unable to determine status of lock file in the data directory /run/media/minze/ssd/mongodb: boost::filesystem::status: Permission denied: "/run/media/minze/ssd/mongodb/mongod.lock", terminating

thanks in advance. I really don't see how mongodb would not be permitted to access a file owned by mongodb

It works after I did:

sudo chmod +r /run/media/minze/ssd
sudo chmod +x /run/media/minze/ssd

I don't understand why.

mongodb.lock is located in /run/media/minze/ssd/mongodb/mongodb.lock

ls -lha gives me (permissions of '.' are important here):

drwxr-xr-x 8 minze   minze   4.0K Dec  3 17:40 .
drwxr-xr-x 3 root    root      60 Dec  3 10:49 ..
drwx------ 2 root    root     16K Oct 15 20:24 lost+found
drwx------ 4 mongodb mongodb 4.0K Dec  3 17:54 mongodb

now


Completely removing InnoDb tables To cut a long story short, I deleted several .idb files and then tried to re-import them from a .sql file.

I'd imagine there is some relic of the old tables somewhere. Does anyone know what to remove or if I can have MySQL re-build its internal tables. These are the only InnoDb tables I have; the rest are MyISAM

You deleted the *.ibd files, but entries in the InnoDB dictionary remain.

Create empty temporary database. Let it be tmp1234
Move all tables from the original database to tmp1234
Drop the original database (it’s empty by now, all tables are in
tmp1234)
Create the original database again
Move all tables from the temporary database to the original one.
Drop the empty temporary database.

See more details on a similar case https://twindb.com/resolving-error-1050-42s01-at-line-1-table-sakilasql-ib712-already-exists/


Why full index scan over full table scan? I have a query that pulls from small tables in MySQL:

SELECT C.cid, C.email, R.flight_number
FROM customer C INNER JOIN reservation R ON C.cid = R.cid
WHERE C.email LIKE '%d.com';

There exists an index in the customer table on the email column.

Based on what I've read on the subject, my thinking is that a full index scan would only slow down the query, and performing a full table scan and ignoring the index would be better than performing a full index scan in this case. However the visual plan shows that the query uses a full index scan over a full table scan. Why?

If the index contains both cid and email, there is no need to look at the records.  All the data will be retrieved from the index.  This is a common optimization.

The where clause should (on average) reduce the record count to less than four percent of the records.  This should be a small enough set to make using the index faster than table scanning.  Different engines have a different cost cutover point. Matching the wildcard on the index is likely faster than than matching against the record (column position matters).

In a case like this, the optimizer will be guessing data distribution.  The optimizer likely doesn't have stats on a trailing match, so it will likely be guessing based on 5 random characters.  This would severely tilt the query in favor of an index scan, as the expectation is that only a small (much less than 1) percentage of records will match.


Insert ID from previous insert POSTGRES I am trying to daisy chain ids through 3 tables. I need to insert a line, take the inserted id and insert that into the next insert, which then takes that new id and inserts it into the final table. My current code looks like:

CREATE OR REPLACE PROCEDURE record_tenant_transaction(UUID,VARCHAR,NUMERIC,UUID,UUID)
LANGUAGE plpgsql
AS '
BEGIN

        INSERT INTO transaction_info(tenant_id)
        VALUES ($1);

        INSERT INTO entry(transaction_info_id)
        VALUES(NEW.id);

        INSERT INTO entry_detail(description,amount,credit,debit,entry_id)
        VALUES ($2,$3,$4,$5,NEW.id);

        COMMIT;
END;
'

but I've tried a bunch of other configs.

This code returns ERROR: missing FROM-clause entry for table "new".

I've tried using the RETURNING clause but then I get ERROR: query has no destination for result data. It seems to me that if I use RETURNING then the sequence stop there? Something like that.

I've also tried using the WITH clause but I REALLY don't understand how that works yet. I can't figure out from the documentation and I'm having trouble understanding a lot of the questions/answers about the WITH clause.

Everything I can find seems to be aimed at gathering SERIALS but my ids are UUID so I can't use the lastval() type clauses I believe.

I'm very new at this so I apologize if this has already been answered and I just don't understand it but I've been searching and trying to figure this out for 2 days now and I'm really hitting a brick wall. I only have a couple of hours each night to learn and code so it would mean a lot if someone could point me in the right direction.

You can use a data modifying CTE to chain the inserts and pass the generated ID on to the next statement:

CREATE OR REPLACE PROCEDURE record_tenant_transaction(UUID,VARCHAR,NUMERIC,UUID,UUID)
LANGUAGE plpgsql
AS $$
BEGIN

  with new_info as (
    INSERT INTO transaction_info(tenant_id)
    VALUES ($1)
    returning id  --<< adjust here for the correct column name
  ), new_entry as (
    INSERT INTO entry(transaction_info_id)
    select id --<< adjust here for the correct column name
    from new_info
    returning id  --<< adjust here for the correct column name
  )
  INSERT INTO entry_detail(description,amount,credit,debit,entry_id)
  VALUES ($2,$3,$4,$5, (select id from new_entry) );
  COMMIT;

END;
$$

You have to adjust the column name in the line I marked, as I don't know what the generated column in the table transaction_info is.

A more procedural way (which I wouldn't recommend) is to store the generated IDs in variables:

CREATE OR REPLACE PROCEDURE record_tenant_transaction(UUID,VARCHAR,NUMERIC,UUID,UUID)
LANGUAGE plpgsql
AS $$
DECLARE
  l_info_id uuid;
  l_entry_id uuid;
BEGIN
  INSERT INTO transaction_info(tenant_id)
  VALUES ($1)
  returning id --<< adjust here for the correct column name
  into l_info_id;

  INSERT INTO entry(transaction_info_id)
  values (l_info_id)
  returning id --<< adjust here for the correct column name
  into l_entry_id;

  INSERT INTO entry_detail(description,amount,credit,debit,entry_id)
  VALUES ($2,$3,$4,$5, l_entry_id);

  COMMIT;
END;
$$

You should also give your parameters proper names, rather then referencing them through $1, $2 and so on.


How do I automate converting heaps into clustered indexes? I have around 40 tables in one of our production databases that, for varying reasons, where not created with a clustered index.

What is the best automated method for converting these heaps?

Since I'm a developer by nature, I really really don't want to do this manually.

I started creating a procedure for this, as documented in Why does this cursor produce results in the incorrect order?, however the responses I got to that post made me question what I was doing.

I would say that I agree with @MartinSmith.  Determining the clustered index key is something that requires some thought and planning.

But if you were looking to get a head start and generate some T-SQL code that, say, creates the CREATE INDEX commands on heaps that have an IDENTITY column (a generally accepted clustered index key), then you could do something like this:

declare @create_indexes nvarchar(max);
set @create_indexes = N'';

select
    @create_indexes = @create_indexes +
        'create clustered index ' + quotename('IX_' + object_name(i.object_id)) +
        char(13) + char(10) +
        'on ' + quotename(object_name(i.object_id)) + '(' + quotename(c.name) + ');' +
        char(13) + char(10) +
        'go' +
        char(13) + char(10)
from sys.indexes i
inner join sys.columns c
on i.object_id = c.object_id
where i.type = 0
and c.is_identity = 1;

print @create_indexes;

Again, I will restate that like @MartinSmith said, I wouldn't just blindly execute any DDL that will impact performance and design like this.  But the above is a start, and will give you the T-SQL for heaps that have an IDENTITY column.


How are precision and scale calculated when decimals are multiplied? When decimals are divided, the precision and the scale of the result value are calculated due to this formula:

  max precision = (p1 - s1 + s2) + MAX(6, s1 + p2 + 1) -- up to 38

  max scale = MAX(6, s1 + p2 + 1)

What's the formula when decimals are multiplied?

For example:

IF OBJECT_ID('tempdb..#DataSource') IS NOT NULL
BEGIN
    DROP TABLE #DataSource;
END;

SELECT CAST(1 AS DECIMAL(9,0)) / CAST(2 AS DECIMAL(9,0)) AS C1
      ,CAST(1 AS DECIMAL(9,0)) / CAST(2 AS DECIMAL(9,0)) * 100 AS C2
      ,CAST(1 AS DECIMAL(9,0)) / CAST(2 AS DECIMAL(9,0)) * CAST(100  AS DECIMAL (3, 0)) AS C3
INTO #DataSource;

EXEC tempdb.dbo.sp_help '#DataSource';

The precision and the scale of column C1 are calculated based on the specified formula:

p1 = 9
p2 = 9
s1 = 0
s2 = 0

max precision = (9 - 0 + 0) + MAX(6, 0 + 9 + 1) --> 19
max scale = MAX(6, 0 + 9 + 1) --> 10

When this result is multiplied by 100 (the value 100 is converted to DECIMAL(3,0)), the result has precision is 23 and scale 10.

If the same formula is applied, the values are as follows:

p1 = 19
p2 = 3
s1 = 10
s2 = 0

max precision = (19 - 10 + 0) + MAX(6, 10 + 3 + 1) --> 9 + 14 = 23
max scale = MAX(6, 10 + 3 + 1) --> 14

So, the precision is correct, but the scale is bigger - 14 instead 10.

Could anyone tell what the formula is for calculating precision and scale when decimals are multiplied?

I have an answer to a very similar question on StackOverflow that goes over this:

How do I control the datatype of a computed column?

But, to state it more completely here:

I suggest reviewing the source documentation used in the posted linked in the Question as it describes the formulas to use for 6 different operations involving decimals: Precision, Scale, and Length. That chart shows:

Operation: e1 * e2
Result precision: p1 + p2 + 1
Result scale: s1 + s2

In the case of having a DECIMAL(19, 10) * DECIMAL(3, 0), we are starting with the following values:

p1 = 19
p2 =  3

s1 = 10
s2 =  0

Which gives us:

Precision = (19 + 3 + 1) = 23
Scale     = (10 + 0) = 10

And that matches the DECIMAL(23, 10) as reported by SQL Server.

And to make it even clearer, I added a 4th column to the test query to reduce the initial division computation to the resulting DECIMAL(19, 10) so there is no question of the order of the 3 mathematical operations happening in C2 and C3. I also am using the sys.dm_exec_describe_first_result_set DMV so that there is no need to create a temp table :-)

SELECT rs.column_ordinal, rs.name, rs.system_type_name, rs.max_length,
       rs.[precision], rs.[scale]
FROM   sys.dm_exec_describe_first_result_set(N'
SELECT CAST(1 AS DECIMAL(9,0)) / CAST(2 AS DECIMAL(9,0)) AS C1
      ,CAST(1 AS DECIMAL(9,0)) / CAST(2 AS DECIMAL(9,0)) * 100 AS C2
     ,CAST(1 AS DECIMAL(9,0)) / CAST(2 AS DECIMAL(9,0)) * CAST(100 AS DECIMAL (3, 0)) AS C3
      ,CAST(0.5 AS DECIMAL(19,10)) * CAST(100 AS DECIMAL(3,0)) AS C4
', '', 0) rs

Returns:

column_ordinal    name    system_type_name    max_length    precision    scale
--------------    ----    ----------------    ----------    ---------    -----
1                 C1      decimal(19,10)       9            19           10
2                 C2      decimal(23,10)      13            23           10
3                 C3      decimal(23,10)      13            23           10
4                 C4      decimal(23,10)      13            23           10

The full chart of Precision and Scale calculations from the linked MSDN page above is copied below for convenience:

Operation           Result precision                          Result scale *
---------           -----------------------------------       ------------
e1 + e2             max(s1, s2) + max(p1-s1, p2-s2) + 1       max(s1, s2)

e1 - e2             max(s1, s2) + max(p1-s1, p2-s2) + 1       max(s1, s2)

e1 * e2             p1 + p2 + 1                               s1 + s2

e1 / e2             p1 - s1 + s2 + max(6, s1 + p2 + 1)        max(6, s1 + p2 + 1)

e1 UNION |          max(s1, s2) + max(p1-s1, p2-s2)           max(s1, s2)
  EXCEPT |
 INTERSECT e2

e1 % e2             min(p1-s1, p2 -s2) + max( s1,s2 )         max(s1, s2)

* The result precision and scale have an absolute maximum of 38.
  When a result precision is greater than 38, the corresponding scale
  is reduced to prevent the integral part of a result from being truncated.


Oracle - Archivelog files , can we delete them? We have RHEL 7.2 machine where Oracle is installed. Recently this machine ran out of space and we figured out that most of the space is going into "/home/oracle/ORA12C/fast_recovery_area//archivelog"

Within this folder , we have multiple datewise folder and each folder contains .arc files
Is it ok to directly delete folder which are one year ago?

It sounds like you have not been doing regular housekeeping by performing an rman backup of the archivelogs and using the DELETE INPUT option of the BACKUP ARCHIVELOG command.  If this is truly a test machine, I wou
ld first manually delete the archives using the proper OS command. That will physically free up the disk space.  Then you need to inform Oracle of that fact:

rman target /

rman> crosscheck archivelog all; -- this will detect that the logs have gone missing and mark their records in the control file as 'expired'

rman> delete noprompt expired archivelog all; -- this will remove the record of the archivelogs from the control file and adjust the FRA usage accordingly

rman> backup database plus archivelog all delete all input;

An important concept that many miss is that the FRA is merely a bookeeping mechanism.  It does not physically reserve space on the disk and does not guarantee that the specified space is actually available.  As regards the archlogs, when one is created, a record of that is written to the control file.  If the log is deleted manuall, the FRA bookeeping mechanism doesn't know and thinks the file is still there consuming space.

If you are in archlog mode, you need to be taking regular backups, including backups of the archlogs with the DELETE INPUT option.


Database-design for a small filter / search tag function I'm building a search function in php for a database with 1k to 4k articles. It's going to be built on a tag system. I've been reading some tutorials, blog posts, q&a etc about it.

Most important for me is performance, I want it to be fast and light. In my special case I'm only going to have a set of few fixed tags. Example: Color:blue, Size:large etc. So if user filters/search "Large Blue" only the articles with these tags should show. There's going to be "Ajaxed" queries, so a light and fast query is important.

I can think of three ways of doing this:

Option 1:
Articles table
 - id
 - name
 - tags (with a data string of tags comma separated)
 - etc

Option 2.
Articles table
- id
- name
- etc

Tags table
- id
- name
- article ids (with a data string of article ids comma separated)

Option 3.
Articles table
- id
- name
- etc

Relational Tag Arts table
- Fkey tags id
- Fkey article id

Tags table
- id
- name
- etc

Which one of these would be fastest / best performance. I know it's not good practice to use comma separated data strings in a table field. But for me, since I just have a few set of tags, option 1 or 2 looks simple and faster than option 3?

Any other suggestions is of course welcome! Thanks

In my opinion option 3 is the best.

Option 1 has the problem that you always have to use a like on the tags field to find the articles.

Option 2 will cause a rewrite of the tag each time that an article is added or deleted. When there are a lot of articles for the tag then the record becomes long.

Both option 1 and 2 will end up with 'big' rows which means less rows in a physical block which means more physical reads and 'slower' result.

Option 3 has a simple setup. It is the way to present an M-to-N relation. Put an index on the name of the tag to help to speed up the finding of the tags (will not help much if you have a very limited number of tags). Adding an article or adding a tag to an article will be fast too.


GIS Point or discrete longitude and latitude columns? I see that the Point type is made to store geometric coordinates, however I don't see what problem it is trying to solve, or what advantages it offers over simply using two numerical columns. It's 16 bytes, so is it just an abstraction for two 8 byte floats? Or is it a string containing some kind of geohash that optimizes the performance of certain queries?

Let's say I want to get all the points for a specific viewport on a Leaflet map... with numerical columns representing latitude and longitude, I'd just filter for all the rows that have a latitude number between the N/S bounds and a longitude number between the E/W bounds, easy. I looked into using Point here, but the syntax is cumbersome and it seems to me like unnecessary overhead for development and for database performance... is it?

Points are useful for spatial relationships.  You can determine what points are within polygons (addresses within a city boundary, as a city is rarely a simple square), what point is closest to another point (bakeries near your home), etc.  Using Lat/Long you can calculate some of this but there are times that Lat/Long will get the answer very very wrong (http://workshops.boundlessgeo.com/postgis-intro/geography.html).

A great intro to geometry in Postgres:
http://workshops.boundlessgeo.com/postgis-intro/

Another way of looking at it:
Looking at a map of the United States how would you quickly determine if a point is within the country?  By using min/max lat/long you would have to include AK and HI (and maybe Puerto Rico, Guam, etc) and the min/max box of lat/long suddenly won't work because it overlaps Canada/Mexico/etc.  But using a point you can then check via ST_Within() in a quick geometry check.


Find Unique Index that does not have Unique Constraint I need to find list of unique indices that does not have unique constraint on that column. How to achieve this?
I know, following query will give me unique constraints.
SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE='UNIQUE'

Note: I am using SQL Server 2012.

sys.indexes includes columns that identify if the index is unique, supports a unique constraint, or supports a primary key constraint. The query below lists unique indexes that do not support a constraint:
SELECT *
FROM sys.indexes
WHERE is_unique = 1 AND is_unique_constraint = 0 AND is_primary_key = 0;


Why do MySQL threads often show "freeing items" status when the query cache is disabled? When I run SHOW PROCESSLIST, there are often a large number of INSERT/UPDATE threads in the "freeing items" state.

The MySQL manual suggests that at least part of the reason for a thread to be in this state involves the query cache - presumably it would be invalidating cached queries due to the changed data.

However, my query_cache_size is set to 0 which should disable the query cache altogether.

What other reasons would there be to have so many threads in this state?

The relevant tables use the InnoDB storage engine.

Check the value of innodb_thread_concurrency.

For my system increasing the value from 8 to 32, per the guidelines in the MySql documentation, caused a discernible decrease in the number of threads concurrently reporting the "freeing items" state. Also, the obesrved average query time dropped by an order of magnitude.

While this made a large difference in overall server performance, it was not the "freeing items" silver bullet. My hardware ecosystem leads me to hypothesize that this state is mostly seen on systems with "slow" disks (2x10k disks raid 1), and is less prevalent on systems with faster storage (12x15k disks raid 10). So, a check of disk performance may also be warranted.

Good Luck!

Also:

It is worth noting that the default value of innodb_thread_concurrency is radically different depending on what 5.0 point release is being used.

  The default value has changed several times: 8 before MySQL 5.0.8, 20 (infinite) from 5.0.8 through 5.0.18, 0 (infinite) from 5.0.19 to 5.0.20, and 8 (finite) from 5.0.21 on. -- source

This means that a seemingly innocuous upgrade from 5.0.20 to 5.0.21 changed the default from infinite to 8, and brought along with it the performance ramifications.


mysql -> show changes in fields between the entries I have a table that keeps records of account settings. When someone (administrator) makes a change to an account, a new entry gets recorded for that particular account at that time (updatetime). The changes in the record set are updatetime, operator and any settings that were changed (setting1, 2, 3 or any combination of them).

CREATE TABLE settingslog(
    updatetime DATETIME,
    account VARCHAR(20),
    administrator VARCHAR(20),
    setting1 VARCHAR(20),
    setting2 VARCHAR(50),
    setting3 VARCHAR(20)
);

INSERT INTO settingslog (updatetime, account, administrator, setting1, setting2, setting3)
    VALUES
('2017-05-02 15:00:25', 'account1','admin2','enabled','enabled','enabled'),
('2017-05-02 14:00:13', 'account1','admin1','enabled','disabled','enabled'),
('2017-05-02 13:10:11', 'account1','admin1','enabled','enabled','enabled');

The desired output would be:

'2017-05-02 14:00:13', 'account1', 'admin1', 'setting2' changed from 'enabled' to 'disabled';
'2017-05-02 15:00:25', 'account1', 'admin2', 'setting2' changed from 'disabled' to 'enabled';

Thank you all in advance for your help! It is much appreciated.


How to insert fields to different tables at once? 5 Main Tables: TEMP, PAYMENT, CUSTOMER, PURCHASE, and PRODUCT.

TEMP:     TempID, Email, Name, Product1, Product2, Date, Amount
PAYMENT:  PaymentID, Status, Amount, Date
CUSTOMER: CustomerID, Email, Name
PURCHASE: CustomerID, PaymentID, ProductID, Quantity
PRODUCT:  ProductID, ProductName, Price

How to insert each field from TEMP where TEMP.Email = '$Email' to PAYMENT, CUSTOMER, PURCHASE, and PRODUCT:

TEMP.Email    >> CUSTOMER.Email
TEMP.Name     >> CUSTOMER.Name
TEMP.Product1 >> PURCHASE.ProductID
TEMP.Product2 >> PURCHASE.ProductID
TEMP.Date     >> PAYMENT.Date
TEMP.Amount   >> PAYMENT.Amount

You could write an (AFTER?) INSERT trigger, like this:

CREATE TRIGGER temp_inserts_trg AFTER INSERT ON temp
    FOR EACH ROW BEGIN
        INSERT INTO customer VALUES(NEW.ID, NEW.Email, NEW.Name);
        INSERT INTO payment...
        ...
    END;

Check the MySQL CREATE TRIGGER documentation for more info.


Have Postgresql accept 1 and 0 as true and false for boolean I have a large file of dumped sql data to load in to postgresl (600mb+). In that dump, fields that are boolean true and false are expessed as 1 and 0 without quotes. Trying to load them in to postgresql, results in complains as it doesnt know how to cast them.

Loading like this...

psql --disable-triggers -1 -f /foo/bar/dump.sql

Due to the large amount of data and the sprinkling of boolean fields in the - regexing through to replace them with true/false appropriately has proved impractical.

After much googling i havent been able to find a way to have postgresql accept that = true and 0 = false for the duration of this data load. It seems simple enough, but its apparently not!

Hopefully someone knows exactly how to nail it and can help me out!

Digging in to the dark magic of postgresql, i discovered that the integer to boolean cast can be changed from an exlicit to an automatic coersion. Then changed back when done...

update pg_cast set castcontext='a' where casttarget = 'boolean'::regtype;

Then load with pg_dump.

When done, set back the casting to its default with...

update pg_cast set castcontext='e' where casttarget = 'boolean'::regtype;

Important note: don't do this unless you know what it involves.   The catalogs aren't guaranteed to be compatible version to version, and something that's safe(ish) to do now could be quite bad to do in a future version, or not work at all.


Much slower replication on Innodb compared to myisam Good morning. I wanted to test how InnoDB will work on my servers and for this I made a slave and connected it to my master with myisam.
The problem is that when the slave works on innodb, replication significantly slows down and at peak moments (about 6000 entries / min) begins to significantly delay.
It is definitely not a problem with the connection with the master because the download does not have any delays. The database can not perform queries from relay logs quickly enough. However, when the table to which 99.9% of the replicated entries get is of the type myisam, everything returns to normal and replication is not delayed.
Replicated entries are 95% inserts.

The database is very small because the application after processing data immediately deletes them so the number of records is a maximum of several thousand. The problem with replication occurs even when mysql is the only running process.

Is there any way to speed up replication in case I want to use innodb?

Specification:

MySQL version: 5.7.25
Server: c5.xlarge https://aws.amazon.com/ec2/instance-types/c5/
Database ssd: 900 IOPS
Ubuntu 14.04

MySQL config:

innodb_buffer_pool_size = 4G

innodb_log_file_size = 500M

innodb_file_per_table = 1

innodb_flush_method = O_DIRECT

innodb_flush_log_at_trx_commit = 2

key_buffer_size = 32M

tmp_table_size = 32M

max_heap_table_size = 32M

query_cache_type = 0

query_cache_size = 0

max_connections = 700

thread_cache_size = 10

open_files_limit = 65535

server-id =  2

symbolic-links = 0

log-bin = mysql-bin

log-slave-updates=ON

gtid-mode = on

enforce-gtid-consistency = 1

binlog_checksum = NONE

expire_logs_days = 1

slow_query_log = 1

log_slow_slave_statements = 1

Exemplary insert from relaylog:

abc.abc.BEGIN
abc.abc.INSERT INTO table (id,aciD,timestamp,statusCode,latitude,longitude,gpsAge,speedKPH,heading,altitude,transportID,inputMask,outputMask,ignitionState,address,dataSource,rawData,distanceKM,odometerKM,odometerOffset
KM,geozoneIndex,geozoneID,creationTime,streetAddress,city,stateProvince,postalCode,country,subdivision,speedLimitKPH,isTollRoad,gpsFixType,gpsFixStatus,horzAccuracy,vertAccuracy,HDOP,satelliteCount,batteryLevel,batteryVolts,batteryTemp,s
ignalStrength,sequence,priority,entityID,entityType,driverID,driverStatus,driverMessage,sensorLow,sensorHigh,costCenter,jobNumber,rfidTag,attachType,attachData,etaTimestamp,etaUniqueID,etaDistanceKM,etaLatitude,etaLongitude,stopID,stopSt
atus,stopIndex,messageTimestamp,messageID,messageStatus,fuelPressure,fuelUsage,fuelTemp,fuelLevel,fuelLevel2,fuelEconomy,fuelTotal,fuelTrip,fuelIdle,fuelPTO,fuelEngineOn,engineRpm,engineHours,engineOnHours,engineLoad,engineTorque,idleHou
rs,workHours,workDistanceKM,transOilTemp,oilCoolerInTemp,oilCoolerOutTemp,coolantPressure,coolantLevel,coolantTemp,engineTemp,intakeTemp,brakeGForce,acceleration,accelerometerXYZ,brakePressure,massAirFlowRate,oilPressure,oilLevel,oilTemp
,airPressure,airFilterPressure,turboPressure,ptoEngaged,ptoHours,ptoDistanceKM,throttlePos,brakePos,vBatteryVolts,j1708Fault,faultCode,malfunctionLamp,tirePressure,tireTemp,tankLevel,doorStateMask,lightsStateMask,thermoAverage0,thermoAve
rage1,frequencyHz,tripStartTime,tripStopTime,tripDistanceKM,tripIdleHours,tripPtoHours,tripMaxSpeedKPH,tripMaxRpm,tripStartLatitude,tripStartLongitude,tripElapsedSeconds,tripBrakeCount,tripClutchCount)
VALUES ('sth','sth232',155379534,344,26.33,-91.1790616,0,32.828,34.0,1.05,'',1,0,1,'','UDP','0x434352STHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH
HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH',0.0,5453.285,0.0,0,'',4554353,'','','','','','',0.0,0,0,2,0.0,0.0,0.8,12,0.0,4.172,0.0,-81.0,34,0,'',0,'',0,'',0,0,0,'','','','',0,0,0.0,0
.0,0.0,0,0,0,0,0,0,0.0,36.9,0.0,0.7080000000000001,0.0,0.0,0.0,0.0,0.0,0.0,0.0,44,2.54,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,85.0,0.0,0.0,0.0,0.0,'',0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0,0.0,0.0,0.876,0.0,12.01400000000
0001,0,'',0,'','',0.0,0,0,0.0,0.0,0.0,0,0,5143.809,0.0,0.0,0.0,0,0.0,0.0,0,0,0)abc.abc.COMMIT

Make sure your disks are fast enough to catch up master.

The problem is that innodb is a transactional storage engine. It writes first into undo log and only then modified data to the tablespaces. The second issue is that mysql replication by default is single threaded. It means that transactions applied on the master in parallel will be in the binlog in sequential order, and all of them will be applied sequentially on the replica. You can try parallel replication toprocess binlog events in parallel.


Is it safe to remove an entry in a "config.mongos" MongoDB collection? There are old entries in my "config.mongos"; they reference hosts which previously had "mongos" running on them but no longer do.

Can I safely remove those entries?

e.g.,:
# Old entries
{ "_id" : "silicon:27117", "ping" : ISODate("2012-12-27T19:51:04.560Z"), "up" : 9301, "waiting" : true }
...
# Current entries
{ "_id" : "app1:27017", "ping" : ISODate("2013-06-07T17:12:02.856Z"), "up" : 5593404, "waiting" : true }
{ "_id" : "app2:27017", "ping" : ISODate("2013-06-07T16:17:49.812Z"), "up" : 5590271, "waiting" : false }
{ "_id" : "app4:27017", "ping" : ISODate("2013-06-07T17:12:02.110Z"), "up" : 5593413, "waiting" : true }
{ "_id" : "app3:27017", "ping" : ISODate("2013-06-07T17:12:02.854Z"), "up" : 5593578, "waiting" : true }

Update:

One reason why having these old entries in config.mongos is a problem is that making changes to the shard takes a long time, while we wait for old mongoses to time out.

For example, stopping the balancer takes a long time (several minutes):

mongos> sh.stopBalancer()
Waiting for active hosts...
Waiting for active host magnesium:27117 to recognize new settings... (ping : Thu Dec 22 2011 03:08:14 GMT+0000 (UTC))
Waited for active ping to change for host magnesium:27117, a migration may be in progress or the host may be down.
Waiting for active host Boron:27017 to recognize new settings... (ping : Tue Jan 31 2012 21:48:12 GMT+0000 (UTC))
Waited for active ping to change for host Boron:27017, a migration may be in progress or the host may be down.
Waiting for active host Beryllium:27017 to recognize new settings... (ping : Wed Feb 01 2012 17:34:15 GMT+0000 (UTC))
Waited for active ping to change for host Beryllium:27017, a migration may be in progress or the host may be down.
Waiting for active host helium:27017 to recognize new settings... (ping : Mon Jun 10 2013 19:07:41 GMT+0000 (UTC))
Waited for active ping to change for host helium:27017, a migration may be in progress or the host may be down.
Waiting for active host boron:27017 to recognize new settings... (ping : Sun Jun 02 2013 08:46:57 GMT+0000 (UTC))
Waited for active ping to change for host boron:27017, a migration may be in progress or the host may be down.
Waiting for the balancer lock...

You can remove these entries, but there is really no reason to do so.  The config.mongos collection just records all mongos processes that have connected to the config server in question.  Hence any that reconnect at any point will just show up again - it's a book keeping thing, and only takes up a few KB at most, there's no need to delete them.


Update column with the sum of column in previous rows in MySQL We have a table, essentially:

id, amount, end balance, account_id

I now want to re-calculate the end balance values (because of possible corruption)

End balance is basically "end balance of previous entry with the same account_id" + amount.

Assume that there are a lot of entries, but I want to do this for one account at a time (or for a couple of accounts).

(So, if we have the corrupt table:

Id  Amount Balance Account
1     100       0     1
2     100     100     2
3     -40     -40     1
4     100      60     1

I want the resulting db table to be:

Id  Amount Balance Account
1     100     100     1
2     100     100     2
3     -40      60     1
4     100     160     1

)

Let make the table called nuoji and load it with the above data

USE test
DROP TABLE IF EXISTS nuoji;
CREATE TABLE nuoji
(
  id int not null auto_increment primary key,
  amount INT NOT NULL,
  balance INT NOT NULL,
  account INT NOT NULL,
  KEY (account)
) ENGINE=MyISAM;
INSERT INTO nuoji (amount,balance,account) VALUES
(100,100,1),(100,100,2),(-40,-40,1),(100,60,1);
SELECT * FROM nuoji;

Let's run that code and Make the Data:

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS nuoji;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE nuoji
    -> (
    ->   id int not null auto_increment primary key,
    ->   amount INT NOT NULL,
    ->   balance INT NOT NULL,
    ->   account INT NOT NULL,
    ->   KEY (account)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO nuoji (amount,balance,account) VALUES
    -> (100,100,1),(100,100,2),(-40,-40,1),(100,60,1);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM nuoji;
+----+--------+---------+---------+
| id | amount | balance | account |
+----+--------+---------+---------+
|  1 |    100 |     100 |       1 |
|  2 |    100 |     100 |       2 |
|  3 |    -40 |     -40 |       1 |
|  4 |    100 |      60 |       1 |
+----+--------+---------+---------+
4 rows in set (0.00 sec)

mysql>

Here is the Query You Need

SET @rt=0; SET @ndx=0; SET @given_account=1;
UPDATE nuoji A INNER JOIN  (SELECT * FROM
(SELECT (@ndx:=@ndx+1) ndx,id,amount,
(@rt:=@rt+amount) newbalance FROM nuoji
WHERE account=@given_account) BB) B USING (id)
SET A.balance=B.newbalance;

Here is that Query Executed:

mysql> SET @rt=0; SET @ndx=0; SET @given_account=1;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE nuoji A INNER JOIN  (SELECT * FROM
    -> (SELECT (@ndx:=@ndx+1) ndx,id,amount,
    -> (@rt:=@rt+amount) newbalance FROM nuoji
    -> WHERE account=@given_account) BB) B USING (id)
    -> SET A.balance=B.newbalance;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3  Changed: 0  Warnings: 0

mysql> SELECT * FROM nuoji;
+----+--------+---------+---------+
| id | amount | balance | account |
+----+--------+---------+---------+
|  1 |    100 |     100 |       1 |
|  2 |    100 |     100 |       2 |
|  3 |    -40 |      60 |       1 |
|  4 |    100 |     160 |       1 |
+----+--------+---------+---------+
4 rows in set (0.00 sec)

mysql>

That's it !!!

If other accounts are messed up in the same way, set @given_account, and then execute the code.

Here is That Code Formatted:

SET @rt = 0;
SET @ndx = 0;
SET @given_account = 1;
UPDATE
    nuoji A INNER JOIN
    (
        SELECT * FROM
        (
            SELECT
                (@ndx:=@ndx+1) ndx,id,
                amount,(@rt:=@rt+amount) newbalance
            FROM nuoji
            WHERE account=@given_account
        ) BB
    ) B USING (id)
SET A.balance=B.newbalance;

Give it a Try !!!


Check all MySQL tables at once I'd like to run a CHECK TABLE on all my tables from the MySQL command-line.

I can do it for a single table:

mysql> check table Restaurant;
+--------------------+-------+----------+----------+
| Table              | Op    | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| foodbox.Restaurant | check | status   | OK       |
+--------------------+-------+----------+----------+
1 row in set (0.02 sec)

Is there an equivalent command that would check all tables in the current database?

You could simplify your life with a shell script. Here is one I created for Linux, it is based on the mysqlcheck application

This script will check all tables in all databases except mysql and information_schema

vi check_all_databases.sh

#!/bin/bash
USER=root
Port=3306
PASSWD=password

DBS="$(/usr/bin/mysql --connect_timeout 10 -u $USER -P $Port -p$PASSWD -Bse 'SHOW DATABASES')"
for db in $DBS
        do
                if [ "$db" != "mysql" ] && [ "$db" != "information_schema" ];
                then
                        /usr/bin/mysqlcheck -u $USER -p$PASSWD -c $db
                fi
        done

Save the file

\wq

Then execute it

sh check_all_databases.sh

dbt2.customer                                      OK
dbt2.district                                      OK
dbt2.history                                       OK
dbt2.item                                          OK
dbt2.new_order                                     OK
dbt2.order_line                                    OK
dbt2.orders                                        OK

For Windows you would use a batch script or vbs to do the same thing.

The other option would be just to run the mysqlcheck application for the one database you are interested in checking.

/path/to/mysqlcheck -u username -pPASSWORD -c database_name

mysqlcheck will be in the directory you installed your mysql binary files in.

In Linux, you can locate it with the which command

which mysqlcheck


Sentry One Plan Explorer History Tab While I'm working on index tuning I would love the history bar to populate with each run so I can see the differences, I'm probably using the tool incorrectly. My current workflow is:

Create New Plan Explorer Session
Paste Query
Click "Get Actual Plan"
go to Index Analysis
either use the <S> or manually script out an index change
click "Get Actual Plan" again and instead of adding history version 2 it just overwrites history version 1.

In the Plan Explorer user preferences (Tools -> User Preferences), uncheck the box reading "Only save history if command text or connection settings change".

If this does not work, try adding something to the query itself that is inconsequential - a line break, an extra space between tokens, something of that nature. This will both trigger a change to the command text and cause a new query plan hash to be generated by SQL Server.


Forcing view to use certain execution plan Can I somehow force an execution plan to a view?

I came across to parameter sniffing and I cannot add option recompile to a query because it is generated by DevExpres, so the only option is to change the view

You can't force an execution plan on a view. Depending on how the view is accessed, SQL Server will generate a different plan.

Querying a view without a where clause may result in table scans, but querying the same view with a predicate on AccountID could result in seeks.

It sounds like you might want to research plan guides, which will allow you to supply query hints (including the USE PLAN hint) without modifying the incoming query. If you're using SQL Server 2016 or newer, you can also use Query Store to force a plan.


conversion of a non correlated sub query to a join statement Is it possible to get rid of the sub query and convert the entire query into a JOIN query?

SELECT student_id,
       COUNT(attendance)
FROM   student_attendance
WHERE  attendance = 'ABSENT'
GROUP  BY student_id
HAVING COUNT(attendance) = (SELECT max_allowed
                            FROM   configurations);

I tried the following:

SELECT student_id,
       COUNT(attendance)
FROM   student_attendance,
       configurations c
WHERE  attendance = 'ABSENT'
GROUP  BY student_id
HAVING COUNT(attendance) = c.max_allowed;

and got this error:

  Column 'c.max_allowed' is invalid in the HAVING clause because
  it is not contained in either an aggregate function or the GROUP BY
  clause.

I want to do this for to a possible increase in query speed in case of thousands of records. Is my concern even valid?

Environment: SQL Server 2000

Your initial query shows that configurations must have no more than one row (or it would fail). You could rewrite this as a join using

SELECT student_id,
       COUNT(attendance)
FROM   student_attendance
       CROSS JOIN configurations c
WHERE  attendance = 'ABSENT'
GROUP  BY student_id
HAVING COUNT(attendance) = MAX(c.max_allowed);

Or as

SELECT student_id,
       COUNT(attendance)
FROM   student_attendance CROSS JOIN
       configurations c
WHERE  attendance = 'ABSENT'
GROUP  BY student_id, c.max_allowed
HAVING COUNT(attendance) = c.max_allowed;

Or as

SELECT t.*
FROM   (SELECT student_id,
               COUNT(attendance) AS count_attendance
        FROM   student_attendance
        WHERE  attendance = 'ABSENT'
        GROUP  BY student_id) AS t
       JOIN configuration AS c
         ON t.count_attendance = c.max_allowed;

But I don't see any benefit of doing so. The original query is clearer and likely to be equally or more efficient.

The execution plan I see is

The select against configurations is executed once - then the rest of the query is run as an independent sub tree with the final elimination of aggregate rows not matching the HAVING happening in the INNER JOIN.

To optimise this query you should consider creating an index on attendance, student_id. This will allow rows matching the attendance = 'ABSENT' predicate to be efficiently identified. And they will be ordered so can be processed by the stream aggregate without needing a sort.

One other option would be to pre-calculate the aggregation using an indexed view.


multiple case statements using multiple columns I have two columns in a table , say "colA", "colB" and "colC" need to be computed.

Case 1: Here, colC is a combination of colA and colB with delimiter as space.

For example , if colA = 'abc' and colB = 'xyz' then colC = 'abc xyz'.

Case 2: Here , if either of the colums (colA , colB) is null then colC should contain the non-null value among colA and colB.

For example , if colA = null and colB = 'xyz' then colC = 'xyz'. if colA = 'abc' and colB = null then colC = 'abc'.

How can I achieve this in a select statement using CASE or some other best approach?

CREATE TABLE #three
(
  colA varchar(32),
  colB varchar(32)
);

INSERT #three(colA, colB) VALUES
  ('abc', 'xyz'),
  ('def', NULL),
  (NULL,  'ghi'),
  (NULL,  NULL);

SELECT ColA, ColB,
  ColC = LTRIM(COALESCE(colA, '') + COALESCE(' '+colB,''))
FROM #three;

DROP TABLE #three;

Results:

ColA  ColB  ColC
----  ----  -------
abc   xyz   abc xyz
def   NULL  def
NULL  ghi   ghi
NULL  NULL


Shrinking a SQL Server 2008 Standard database to move it into SQL Server 2014 Express I need to shrink a popular CRM database with SSMS to move it onto a new server. I am going from SQL Server 2008 Standard to SQL Server 2014 Express. Database is in Simple Recovery Model. It needs to be under the 10 gig limit.

There's 39% space to erase but shrink does not work. I've done this many times including this database after removing 100000's of rows in different tables but it does not want to shrink this time.

No errors just runs and does nothing.

Normally the thing holding my data files back from being shrunk is that a CHECKPOINT is needed.  I have had to do several iterations of:

CHECKPOINT;
GO
DBCC SHRINKFILE(...)
GO

Before the file would finally shrink.

If the data files still doesn't shrink, try the troublshooting steps here to confirm that you really have the free space that you think you have.  Here's the query from the docs link:

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;


Update the value when the row already exists I have table 'A' in schema 'USER'. There are three columns in this table.
below are the names of the columns:

id (primary key)
name
age

I want to insert a row in this table, but if the row is already present (with same id) then the name and age should be updated instead of giving error sqlcode -803.

Is there any way to do this in a single query?

As mentioned in comments on the question, MERGE is the answer you are looking for.

Check out this great blog as well:

MERGE statement explained by Serge Rielau (瑞赛奇) and Rick Swagerman.

It will look somewhat like this for your case:

MERGE INTO <tab> AS ...
  WHEN MATCHED THEN
     UPDATE ...
  WHEN NOT MATCHED THEN
     INSERT...


Is there any index with O(1) complexity for lookup in PostgreSQL? Is there any index type in PostgreSQL that gives O(1) complexity for lookups? on strings?

You'll need to the read the PostgresSQL Index Types documentation for the version that you are using. I think you are looking for a Hash Index, but as as the documentation explains the real world performance and pros and cons depend on the implementation details and recommendations of the particular version that you are using.


SQL Server database design for "archived but available" data We have this large database (>1TB) that we intend to "shrink".  The database revolves around one main entity, let's call it "Visit".  For discussion, let's say it is a database for a medical practice.

There are a total of 30 visit "types", such as procedure, annual, follow-up, immunisation etc, each of which is a subsidary table to "Visit", e.g. "visit_immuno".

The database has accummulated some 12 years of data since 2000.  Someone has proposed that we keep about 3 years of data in the "live" version and have the rest live in an "old_data" database.  The date is ONLY stored in the "Visit" table since it is normalised.  The Visit table also contains a ROWVERSION column and a BIGINT pseudo-identity (clustered) column.  For all intents and purposes, let's say the clustering key is populated by a SEQUENCE (SQL Server 2012 Enterprise) - we shall name it cid.

The visit.date is not always in the same order as the clustering key, for example when a doctor goes on extended visitations and returns with his "briefcase" of data, it gets merged into the main table.  There are also some updates to the "visit" table that will cause the ROWVERSION column to be out of sync with both the cid and date columns - to put it simply, neither ROWVERSION nor cid would make suitable partition keys for this reason.

The business rule for removing data from the "live" is that the visit.date must be greater than 36 months and a child visit_payment record must exist.  Also, the "old_data" database does not contain any of the base tables except visit%.

So we end up with:

Live DB (daily use) - All tables
Old-Data DB - older data for the visit% tables

The proposal calls for a Combined DB that is a shell containing Synonyms to ALL the base tables in the Live DB (except visit%) plus Views that UNION ALL across the visit% tables in the two databases.

Assuming the same indexes are created in the Old-Data DB, will the queries perform well on the UNION-ALL Views?  What type of query patterns might trip up the execution plan for the UNION-ALL Views?

For convenience, assume that the live database is called LiveDb and the achive database is called ArchiveDb

Add a UNION ALL view in LiveDb pointing to the tables in ArchiveDb database via a synonym (There is no need to do a combined db with synonyms)
"Partition" on visit.date and denormalise this column to visit_payments too if it isn't there already (this improves co-located join performance)
Only archive the two large tables if possible (reduces chance of tripping up the optimiser). Keep the UNION ALL view and the other tables in LiveDb so all joins to the smaller tables are kept local
Add a CHECK constraint on the tables in both LiveDb and ArchiveDb  that describes the range of visit.date contained in the table. This helps the optimiser eliminate the archive table from both seeks and scans that contain the column visit.data. You will have to periodically update this constraint.
In the UNION ALL view, add a WHERE criteria which filters on visit.data. This is in addition to the hint you already provided in the check constraint. This maximises the chance of filters being pushed down
If you have EE, partition the table in the archive database (But NOT in the live database). If you want to get really fancy, use filegroup level backup/restore of the archive databases to save on backup times.
Consider putting AchiveDb in SIMPLE recovery mode if it isn't already. You are not likely to need transaction log backups of ArchiveDb
Use INSERT... WITH (TABLOCK) SELECT ... WITH (ROWLOCK) to force minimal logging on the destination when moving data between  LiveDb and ArchiveDb

All of the above does not guarantee that the optimiser will eliminate the archive tables from seeks and scans, but it makes it more likely.

When the elimination doesn't happen. These are the effect you may see (this list may be incomplete). For seeks, you will get an additional seek on every query (this drives up IOPS). For scans, the results could be disastrous for performance as you may end up scanning both the archive and live tables. Here are the typical ways you can trip up the optimiser:

If you join the visit% tables together and don't include the visit.data in the join criteria (this is why you want to denormalise). Because of this, you may wish to modify some of your queries
If you get a hash join between visit.data and another table (for example a date dimension), you may not get the right elimination of tables
If you try to aggregate data over the archived tables
If you filter on anything BUT the visit.data, for example a seek directly on the key of the view.

For the last scenario, you can guard yourself against the worst effects by adding another check constraint on the cid - if this is possible. You did mention that the sequence of cid not "clean" with respect to the dates and progression of rows in the table. However, could you maintain a table that contains the information: "There are no cid above this number since this visit.data" or similar? This could then drive an additional constraint.

Another thing to be careful about is that parallel queries may spawn a LOT more threads once you query the partitioned view (as both "sub-tables" will be exposed to the same parallel optimisations). For that reasons, you may want to limit MAXDOP on the server or the queries that are parallel.

By the way, if you know the queries well - you may not even need the same indexes in the two databases (this assumes you are 100% sure you will get the right elimination of tables). You could even consider using column stores for ArchiveDb.


How to get Super Priviledge for a database on an online server Can someone please look at this MySQL statement and suggest to me what to do, for each time I run the code below, I get the following error:

  1227 - Access denied; you need the SUPER privilege for this operation

CREATE
   ALGORITHM=UNDEFINED
   DEFINER=`bbamas_cat`@`localhost`
   SQL SECURITY DEFINER
VIEW `pay_rep_view`  AS
select
   `pay`.`id` AS `id`,`pay`.`pay_date` AS `pay_date`,`pay`.`pay_amount` AS `pay_amount`
   ,`pol`.`pol_number` AS `pol_number`,`pol`.`pol_hold` AS `pol_hold`
   ,`pol`.`pre_amnt` AS `pre_amnt`,`pol`.`com_date` AS `com_date`
   ,`in_comp`.`id` AS `in_comp_id`,`in_comp`.`in_comp` AS `in_comp`
   ,`in_brok`.`id` AS `brok_id`,`in_brok`.`in_brok` AS `in_brok`
   ,`pay`.`pay_type` AS `pay_type`
from ((
  (`pay` join `pol` on((`pay`.`pol_id` = `pol`.`id`)))
  left join `in_comp` on((`pol`.`in_comp_id` = `in_comp`.`id`)))
  left join `in_brok` on((`pol`.`brok_id` = `in_brok`.`id`)))

I question whether you asr asking the correct question:

Are you bbamas_cat@localhost?  If so, leave out the DEFINER clause.
Do you really want the VIEW to be run only as bbamas_cat@localhost?  Usually one wants to run things as root or as INVOKER.

To get SUPER, normally you connect to MySQL as the user root.  I say "normally" because that is nothing to force root to be GRANTed SUPER, nor is there anything to prevent other users from being GRANTed SUPER.


Show Query Results Vertical I am wanting to show these results vertical as opposed to the default horizontal.  My desired output is like this

  Passed w/ notes	   26
  Passed w/o notes	    28
  Passed w/ notes 	    52
  Passed w/o notes	    14

Here is my query - how should I write this so that the results are displayed vertically?

Select
COUNT(case when storenum = '1463' AND CAST(notes As VARCHAR(MAX)) IS NOT NULL THEN id else NULL end) As [Passed w/ notes]
,COUNT(case when storenum = '1463' AND CAST(notes As VARCHAR(MAX)) IS NULL THEN id else NULL end) As [Passed w/o notes]
,COUNT(case when storenum = '1638' AND CAST(notes As VARCHAR(MAX)) IS NOT NULL   THEN id else NULL end) As [Passed w/ Notes]
,COUNT(case when storenum = '1638' AND CAST(notes As VARCHAR(MAX)) IS NULL THEN id else NULL end) As [Passed w/o notes]
FROM monster.dbo.storesales

I looked at using a PIVOT and from my readings syntax would be something PIVOT(SUM(ID) FOR fieldname IN ()

But I do not have a specific fieldname to use for the "FOR" clause of my pivot.  How would I work around that?

You have transposed your desired result so it is rather straightforward to achieve what you want:

select storenum
     , case when CAST(notes As VARCHAR(MAX)) IS NOT NULL then 1 else 0 end as x
     , count(1)
from monster.dbo.storesales
group by storenum
    , case when CAST(notes As VARCHAR(MAX)) IS NOT NULL then 1 else 0 end

Now you can map each storenum / notes combination to a text, something like:

select case when storenum = '1463' and x = 0 then 'Passed w/ notes'
            when storenum = '1463' and x = 1 then ...
            when ...
     , cnt
from (
    select storenum
         , case when CAST(notes As VARCHAR(MAX)) IS NOT NULL then 1 else 0 end as x
     , count(1) as cnt
    from monster.dbo.storesales
    group by storenum
           , case when CAST(notes As VARCHAR(MAX)) IS NOT NULL then 1 else 0 end
) as T


HA options for a VLDB We have 2 VLDB's - 6TB (primary db, not much OLTP traffic) and 4TB (archive db). Data will be moved/archived (delete from primary and insert to archive) daily from 5 large tables. Currently this server is on 2016 Standard Edition and holds 2 VLDB's and 1 small db. We are planning for an HADR strategy, considering standard edition i can only think of log shipping (sometimes log backup ranges 2-5gb) or FCI. If, we upgrades to Enterprise edition we can use AlwaysOn AG's. My question is what is the best strategy for these 2 VLDB's. And considering more features are getting added in future for primary DB, so size will grow and becomes more high OLTP system.

In my opinion, your best bet would be to upgrade to Enterprise Edition and implement AlwaysOn Availability Groups. Depending on how much reporting you do and how high the availability needs to be, you might even want to set it up with two secondary replicas (for a total of three nodes).
Alternatively if you do stick with Standard Edition, aside from Log Shipping and regular FCI, you can also use Replication to synchronize your data across multiple servers in near real-time, especially since your OLTP is not that much, actually. SSIS is another way you can synchronize data between servers too, though it's a bit more clunky and might not be as real-time as Replication.
Basic Availability Groups are also an option in Standard Edition. It's not really a "group" since each AG can only contain 1 DB, and only span 2 nodes, among some other limitations--but essentially provide the same functionality previously supported via mirroring.


How to include a datetime parameter within a stored procedure along with string query? My stored procedure is as follows,

    -- Add the parameters for the stored procedure here
   @FromDate datetime,
   @ToDate datetime

       --Select query
      DECLARE @query nvarchar(max)

      set @query='SELECT [col1]
                  FROM [Table1]
                  WHERE ([col2] BETWEEN '''+@FromDate+''' AND'''+@ToDate+''')'

       execute sp_executesql @query

Executing this string query results in following error,

"Conversion failed when converting date and/or time from character string."

Any one please help me to sort this out...

The fundamental issue is that TSQL cannot implicitly convert datetime (or integer or floating point) into character data types. It actually goes backwards, it attempts to implicitly convert the character data to the datetime (int/floating point value) based on the data type precedence rules. This is what your error message is telling you by the way, it couldn't convert the strings to datetime values. You have to explicitly ask it to mash values into character strings.

This example demonstrates the principal with integers as they are easier to understand expected values.

DECLARE
    @stringInt nvarchar(3)
,   @intint int

SELECT
    @stringInt = N'3'
,   @intint = 5

SELECT
    @stringInt + @intint AS implicit_conversion
,   @stringInt + CAST(@intint AS nvarchar(5)) AS explicit_conversion

The value of the implicit conversion shows the @stringint is converted to integer first and then the + is treated as numeric addition and results in 8. The explicit conversion of @intint to a character data type results in the + sign treated as concatenation with the returned string of 35

implicit_conversion explicit_conversion
------------------- -------------------
8                   35

To solve your supplied problem, you must explicitly cast your datetime values to a character type so the query string can be concatenated as expected.

set @query='SELECT [col1]
FROM [Table1]
WHERE ([col2] BETWEEN ''' + CONVERT(nvarchar(24), @FromDate, 121) +''' AND'''+ CONVERT(nvarchar(24), @ToDate, 121) +''')'

But as indicated above, you really don't want to do that for a number of reasons, SQL Injection being one of them. It also makes your maintenance much harder when you're slicing and dicing a query string in TSQL.

A better approach is to parameterize your query and use the power of sp_executesql. A nice thing about the sp_executesql parameter is that you don't have to use all the parameters supplied. Depending on what you are truly trying to do, this may be beneficial.

Sample table and data

CREATE TABLE
dbo.table1
(col1 int, col2 datetime)

INSERT INTO
    dbo.table1
SELECT
3, '2009-04-06'
UNION ALL SELECT
1, '2001-09-11'

Demo of using parameters

DECLARE
    @FromDate datetime,
    @ToDate datetime

SELECT
    @FromDate = '2005-03-17'
,   @ToDate = current_timestamp

DECLARE
    @query nvarchar(max)
SET @query = N'
SELECT [col1]
FROM [Table1]
WHERE ([col2] BETWEEN @start AND @end)'

-- gratuitous use of parameter assignment here
-- could just as easily used @FromDate and @ToDate
-- in the @query and the parameter list
EXECUTE sp_executesql
    @query
,   N'@start datetime, @end datetime'
,   @start = @FromDate
,   @end = @ToDate

Results

col1
3


Can I set order of query execution? Below query is executing in approx 5 min:

     SELECT COUNT(distinct B.CALL_ID), C.IVR_APPLN_NAME
      FROM PTDT.TDT_CALL_DATA AS A, PTDT.TDT_IVR_CALL_APPLICATIONS AS B,
           PTDT.TDT_IVR_APPLN_REF AS C
      WHERE A.CALL_ID = B.CALL_ID AND B.IVR_APPLN_ID = C.IVR_APPLN_ID
      GROUP BY C.IVR_APPLN_NAME;

By adding additional where clause (IVR_APPLN_NAME) it's executing in 120 min.

    SELECT COUNT(distinct B.CALL_ID), C.IVR_APPLN_NAME
            FROM PTDT.TDT_CALL_DATA AS A, PTDT.TDT_IVR_CALL_APPLICATIONS AS B,
            PTDT.TDT_IVR_APPLN_REF AS C
            WHERE A.CALL_ID = B.CALL_ID AND B.IVR_APPLN_ID = C.IVR_APPLN_ID
        AND C.IVR_APPLN_NAME in ('DSC','EMS','FAO','RET','YAS')
      GROUP BY C.IVR_APPLN_NAME;

So I tried to run it like below:

       select * from (
                SELECT COUNT(distinct B.CALL_ID), C.IVR_APPLN_NAME
                   FROM PTDT.TDT_CALL_DATA AS A, PTDT.TDT_IVR_CALL_APPLICATIONS AS B,
                   PTDT.TDT_IVR_APPLN_REF AS C
                WHERE A.CALL_ID = B.CALL_ID AND B.IVR_APPLN_ID = C.IVR_APPLN_ID
                GROUP BY C.IVR_APPLN_NAME) as D
       where D.IVR_APPLN_NAME in ('DSC','EMS','FAO','RET','YAS')

I assumed that internal query will run first (like before it was running in 5 min) and then I will filter results out of that (from outer filter D.IVR_APPLN_NAME in ('DSC','EMS','FAO','RET','YAS')).
But this was not the case, DB2 manipulated in such way that it applied outer filter (IVR_APPLN_NAME) to internal query and overall query executed again in 120 min.
  Other option could be to store internal result in temporary table but temp table use in DB2 itself is not as simple as in SQL.

Any suggestions?

Try following change. It might work.

select d.count1 , d.IVR_APPLN_NAME
from (
        SELECT COUNT(distinct B.CALL_ID) as count1,
               C.IVR_APPLN_NAME ,
               C.IVR_APPLN_NAME || '' as  test_name
        FROM PTDT.TDT_CALL_DATA AS A,
             PTDT.TDT_IVR_CALL_APPLICATIONS AS B,
             PTDT.TDT_IVR_APPLN_REF AS C
        WHERE A.CALL_ID = B.CALL_ID AND B.IVR_APPLN_ID = C.IVR_APPLN_ID
        GROUP BY C.IVR_APPLN_NAME
     ) as D
where D.test_NAME in ('DSC','EMS','FAO','RET','YAS')

This SQL is different from the SQL in the question.  By using a derived column (test_name) instead of original column (IVR_APPLN_NAME) in where clause, we might be able to outsmart the optimizer. I tested this strategy on our system using different tables and it worked.


How to use LIKE with subquery? In a SQLITE3 DB, I have a table A with a column 'word' containing search words that partially match a column in another table B.

I'd like to SELECT all rows FROM table B WHERE B.name LIKE %any_of_the_words_in_a.word%';

My first idea was to use a REGEX, but I can't get it to work with REGEX and a subquery.

I think this is what you are after, no need for Regex. It's just a correlated subquery, with the only difference being the LIKE condition instead of the common equality condition:

SELECT b.*
FROM tablename AS b
WHERE EXISTS
      ( SELECT *
        FROM words AS w
        WHERE b.name LIKE '%' || w.word || '%'
      ) ;


Is there really any benefit in wrapping random ad-hoc queries in a transaction? I am no db expert, but I was very surprised when someone working at our client's company asked us to send all queries wrapped in a transaction like below:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRANSACTION;
    SELECT * FROM <table_name> WHERE ..etc
COMMIT TRANSACTION;

I have never had such a request before when all we're doing is reading data (random ad-hoc queries). Is there any sense in this? Can anyone tell me why this would be necessary?

Many thanks

The BEGIN TRANSACTION/COMMIT TRANSACTION statements are mostly superfluous in this specific case.

As for running all queries with READ UNCOMMITTED isolation level, I'd strongly advise against it, unless you have a specific need, and aren't worried about the implications of dirty reads:

You may end up querying data from a half-finished transaction (e.g. only one half of a double-entry accounting ledger entry, only some of an order's line items, etc.)
You may end up querying data from a transaction that is eventually rolled back.

If you're concerned about write queries blocking read queries, I would recommend either using SNAPSHOT isolation, or setting the READ_COMMITTED_SNAPSHOT database option, which effectively uses snapshot isolation for all queries. The net result is that instead of being blocked, read queries will see data as it existed prior to any currently running write transactions.

Be advised that this option increases tempdb usage, as that's where the snapshot data is stored (and it has to be stored for all write transactions in databases where it's enabled). If this overhead is unacceptable, then I would only use READ UNCOMMITTED after evaluating queries that need it on a case-by-case basis.

I know you aren't the one making the decision to use it, but you can use these points as ammo if you need to push back on it. :)


Query optimization for multiple joins postgres ER Diagram

Brief description

The database to be used is postgres.

The database might contain upto 2 million AccountHolder records.

Since the AccountHolder table is accessed and updated frequently autovacuum_vacuum_scale_factor for AccountHolder table is set to 0.01

The application only loads latest 5000 records during startup.

As specified in the ER diagram, an account holder record can have multiple addresses,file
attachments,tags and versions(1 to n)

An account holder record is associated with a group and for a given account holder record, there are balance and credit fields (1 to 1)

Since latest records are to be fetched, an index is created on LastModificationTimestamp field of the AccountHolder table.

While joining tables with 1 to 1 relationship INNER JOIN is used.

While joining tables with 1 to n relationship LEFT OUTER JOIN is used.

AccountHolderId in AccountHolder table is the PRIMARY KEY and this is used as foreign key in other tables.

Keeping the above things in mind, I have designed this query to fetch the latest 5000 account holder records as follows.
SELECT *
FROM
     ( SELECT *
       FROM AccountHolder
       ORDER BY LastModificationTimestamp DESC
       LIMIT 5000 ) AS AccountHolderRecord

INNER JOIN
     Groups ON AccountHolderRecord.AccountHolderId = Groups.AccountHolderId
INNER JOIN
      BalanceAndCredit ON AccountHolderRecord.AccountHolderId = BalanceAndCredit.HolderId
LEFT OUTER JOIN
      FileAttachments ON AccountHolderRecord.AccountHolderId = FileAttachments.HolderId
LEFT OUTER JOIN
      Addresses ON AccountHolderRecord.AccountHolderId = Addresses.HolderId
LEFT OUTER JOIN
      RevisionHistory ON AccountHolderRecord.AccountHolderId = RevisionHistory.AccountHolderId
LEFT OUTER JOIN
      Tags ON AccountHolderRecord.AccountHolderId = Tags.HolderId

Question
I am new to Postgres and SQL joins. I want to know whether this query can be further improved. Are there any additional things that I need to take care of to improve the performance?

I am sure that you can improve performance using indices on foreign keys i.e put an index on the accountholderid when you use it as foreign key. It's the place where you would usually put them. And you might put index on LastModificationTImestamp with desc order CREATE INDEX accountholder_LastModificationTimestamp_idx ON acountholder(LastModificationTimestamp DESC NULLS LAST);

Since you are using top 5000 entries indexing is not actually good thing. And index maintenance is expensive. You will be faster by doing sequential scan. ;) If you use timestamps in your search queries that use index, otherwise not.
Useful article:
PostgreSQL Indexes

And if you often hit the same query without the data being updated you can have the materialized view:
Materialized views in PostgreSQL docs

I would put not changing things into materialized view (you can refresh it when need it).
So you will have less joins, and when playing with it use explain (planning) or explain analyize (planning and executing). Use analyze so you update your statistics.

After looking at the diagram I would do the following:

Extract the changing part of the AcountHolder into separate table with 1:1 relationship, and leave Revision history into separate table (because there is a last modification timestamp and refreshing materialized view is very expensive) and put everything else into materialized view.


How to best handle the situation where my "table creation code" and my actual live tables have become desynced? I'm developing a system which, relevant to this question, consists of PostgreSQL database tables.
When I first created the SQL code to CREATE TABLE, I did think it through. However, over the years, I've thought of improvements, fixed bugs, and implemented new functionality. Each time I do this, on the "live" table containing my data, I then make sure to make the appropriate change to the code file, so that (at least in theory) I could run it on a different computer or fresh PostgreSQL installation and get an identical "structure".
However, as we all are painfully aware of, it's impossible to change the column order. So already that is a change between my live table and the code. There's also the possibility that I simply forget to change it after fiddling with some problem for hours.
Either way, it's not a good feeling that my table creation code and my live tables are "slightly different", if only in the column order and some of the identifiers for constraints which I never refer to in my code, and thus "don't matter". But it's not a nice feeling.
I can't just nuke the tables and re-run my code to recreate the structure, because then I lose all the data in them!
Is there any reasonable way for me to "sync" my code and the live table structure while retaining the data, in a safe and problem-free manner?

There are times where someone wants to write code and not specify each individual column. If I write a script to split out one customers data to another database, not specifying the column names means that I don't need to change the script every time I add a new column. As long as the column order is constant. If there is one table that you want to change the column order, you can drop the constraints and indexes, rename the table, create the table again without the indexes and constraints, insert the data by specifying column names and create the indexes and constraints. It is a lot of work and requires downtime. But it would allow you to change the column order. Once you do that, you should update the tables using a tool like Flyway or ant where you can run code against each database as a release to make sure that the changes happen in the same way.


sp_configure set at code execution - Pro/con's of turning on/off The right answer of course is 'it depends', but on the surface it seems to make sense to turn on/off some options when needed as the code sample below for Ole Automation Procedures would do. Could also be xp_cmdshell or Ad Hoc Distributed Queries among a handful of others. EDIT: The question - is this a good or bad idea? why/why not

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

--Sample Code:
Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
 Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get', '<URL>&output=XML','false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

Select CONVERT(xml,@ResponseText)

Exec sp_OADestroy @Object;

GO
sp_configure 'Ole Automation Procedures', 0;
GO
RECONFIGURE;
GO

A) Those options can only be switch by logins with elevated permissions. Therefore to alter them at run-time the application must run with elevated permissions. This is a security risk.

B) Actions performed in this way run in the DBMS's process. Any instability (bug, exception) they cause will affect the stability of SQL Server and all clients it is serving.


Granting password protected role to another role problem in Oracle I can grant a role to another role unless the granted role is password protected, even when I know the password.  I keep getting "ORA-00990: missing or invalid privilege" The system privileges the Schema Owner of the roles has are RESOURCE, DBA and CONNECT.

grant OBLAH_DEE IDENTIFIED BY "15gx9FR7GG" to "OBLAH_DAH"

First of all, your command is syntactically incorrect. It should be:

grant OBLAH_DEE to "OBLAH_DAH" IDENTIFIED BY "15gx9FR7GG";

The problem with that: you can not grant password protected roles to another role. See https://support.oracle.com/epmos/faces/DocContentDisplay?id=1663997.1 for details.

ERROR
-----------------------
ORA-28405: cannot grant secure role to a role

...

Starting With Oracle 11.2.0.4.0 and 12c granting secure or password
protected role to any role  (normal or password protected) we raise
this expected error.

grant::=

grant_system_privileges::=

grantee_identified_by:

As you can see in the 3rd picture, when using the IDENTIFIED BY clasue, the grantee can be only an user, but not a role. (By the way, that clasue is used for something else.) Using the regular grantee_clause, this is the error you would receive:

SQL> create role r1;

Role created.

SQL> create role r2 identified by 1;

Role created.

SQL> grant r2 to r1;
grant r2 to r1
*
ERROR at line 1:
ORA-28405: cannot grant secure role to a role

And what you tried:

SQL> grant r2 to r1 identified by 1;
grant r2 to r1 identified by 1
*
ERROR at line 1:
ORA-01920: user name 'R1' conflicts with another user or role name

As a workaround, the below parameter can be set to revert to the old behaviour:

 alter system set "_grant_secure_role"=true scope=spfile;

You must restart the database for the change to take effect.


SQL count by n-tuples Took me more time than I'd like to admit to figure out how count the number of n-tuples occurrences via Oracle SQL.

Lets say table TABLE :

| ID         | Order number| Value        |
|:-----------|------------:|:------------:|
| 1          |        10   |     A
| 2          |        10   |     B
| 3          |        10   |     C
| 4          |        10   |     D
| 5          |        15   |     A
| 6          |        15   |     B
| 7          |        15   |     C
| 8          |        15   |     D
| 9          |        20   |     X
| 10         |        20   |     Y
| 11         |        20   |     Z
| 12         |        30   |     M
| 13         |        30   |     P
| 14         |        40   |     X
| 15         |        40   |     Y
| 16         |        40   |     Z

I would like to count the number of order that share the same "Value" n-tuple occurrences. Output format is irrelevant as long as I can get the data, something like :

(A,B,C,D) = *2   // [Since Both order 10 and 20 share the same values]

(X,Y,Z) = *2

(M,P) = 1

n-tuple order is irrelevant (A,B,C,D) or (D,A,C,B) means the same.

Any tip or hint greatly appreciated.

You can use LISTAGG() function to gather the groups/tuples, then another GROUP BY to count occurrences of every group/tuple.

Assuming that (order_number, value) is unique, i.e. that no two rows have the same order_number and value:

WITH groups AS
  ( SELECT LISTAGG(value, '-') WITHIN GROUP (ORDER BY value) AS value_group
    FROM employees
    GROUP BY order_number
  )
SELECT value_group,
       COUNT(*) AS occurrences
FROM groups
GROUP BY value_group ;

You could also get the order numbers that belong to each group:

WITH groups AS
  ( SELECT LISTAGG(value, '-') WITHIN GROUP (ORDER BY value)
             AS value_group,
           order_number
    FROM employees
    GROUP BY order_number
  )
SELECT value_group,
       COUNT(*) AS occurrences,
       LISTAGG(order_number, ', ') WITHIN GROUP (ORDER BY order_number)
         AS order_numbers
FROM groups
GROUP BY value_group ;