Postgresql filtering by value over rolling time period I am newbie to postgresql and still getting my head around. I have used windows function etc. till date and yes I can make some sense.

I have got the following data set:

I need to find those customers whose amount > 2 for 4 minutes in a rolling fashion, considering the data for a day. By that I mean in the given data set, customer 1 is one such as it has got amount > 2 at 16:01,16:02,16:03,16:04 and then at 16:02,16:03,16:04, 16:05. If there is any such single occurrence, the customer needs to be selected. Customer 2 doesn't have so. Wondering what's the best approach to do this?

Any help will be much appreciated.

Regards,

Schematically:

SELECT DISTINCT t1.customer
FROM test t1, test t2
WHERE t2.purshase_ts BETWEEN t1.purshase_ts AND t1.purshase_ts + INTERVAL 4 minute
  AND t1.customer = t2.customer
GROUP BY t1.customer, t1.purshase_ts
HAVING SUM(CASE WHEN t2.amount > 2 THEN 1 ELSE 0 END) = COUNT(t2.amount)

or

SELECT DISTINCT t1.customer
FROM test t1
WHERE NOT EXISTS (SELECT 1
                  FROM test t2
                  WHERE t2.purshase_ts BETWEEN t1.purshase_ts AND t1.purshase_ts + INTERVAL 4 minute
                    AND t1.customer = t2.customer
                    AND t2.amount <= 2)

If time roll is to be "full", use 1st query adding relevant restriction:

SELECT DISTINCT t1.customer
FROM test t1, test t2
WHERE t2.purshase_ts BETWEEN t1.purshase_ts AND t1.purshase_ts + INTERVAL '4 minute'
  AND t1.customer = t2.customer
GROUP BY t1.customer, t1.purshase_ts
HAVING SUM(CASE WHEN t2.amount > 2 THEN 1 ELSE 0 END) = COUNT(t2.amount)
   AND COUNT(t2.amount) = 5

or

SELECT DISTINCT t1.customer
FROM test t1, test t2
WHERE t2.purshase_ts BETWEEN t1.purshase_ts AND t1.purshase_ts + INTERVAL '4 minute'
  AND t1.customer = t2.customer
GROUP BY t1.customer, t1.purshase_ts
HAVING SUM(CASE WHEN t2.amount > 2 THEN 1 ELSE 0 END) = COUNT(t2.amount)
   AND MAX(t2.purshase_ts) - MIN(t2.purshase_ts) = INTERVAL '4 MINUTE'

fiddle


Cannot read from secondary availability group I have an issue with Always On Availability Groups In SQL Server 2012.

I installed a replica secondary SQLNODE3. It was fine and everything retrievable form tables but suddenly I found I could not read anything from the database. In Activity Monitor, I see the query is suspended and the wait type is:

HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING

I wait for the updating transition for more than day but nothing happened.

I restarted the replica server and created the AG from scratch but still cannot read data.

How can I read from the replica and avoid this wait type?

This sounds like you might have a long running transaction on the primary replica.  Run this query to find any transactions that have been running longer than 10 mins:

Select  B.session_id,
    A.transaction_id,
    C.transaction_begin_time,
    DATEDIFF(Second,C.transaction_begin_time,getdate()) TimeTaken_In_Seconds,
    B.HOST_NAME,
    B.program_name,
    B.login_name,b.login_time as UserLoginTime
    from sys.dm_tran_session_transactions A Join sys.dm_exec_sessions B On A.session_id=B.session_id
    Join sys.dm_tran_active_transactions C On A.transaction_id = C.transaction_id
    WHERE DATEDIFF(Second,C.transaction_begin_time,getdate())  > 600

Once you find the transaction, either complete it, or kill it.


Way to add referencing and referenced entities of a specific entity in E-R Diagram MYSQL Is there any way to add entities that are referencing a specific entity in an E-R Diagram. I have a database containing 400 tables. I want to see a view containing a specific entity and its referencing and referenced entities. Some MySQL clients like toad give a way to add in referenced entities when you drag a specific entity but I could not find a way to add in referencing entities that is those which have that specific entity/table as foreign key in them.

P.S. Managing an E-R diagram containing 400 tables is very very tedious

SchemaCrawler is a free tool that can create database diagrams from the command-line. You can search for tables and columns that you want to include in your diagram using regular expressions. Once you have selected the tables to include, you can also include any number of levels of referenced tables in your diagram. (That is, tables related by foreign keys.) SchemaCrawler can also infer relationships even if foreign keys are not defined, if popular naming conventions are followed.

You meantion that it is tedious to manage E-R diagrams for 400 tables. Yes, it is. I would say, maintain many SchemaCrawler search expressions, which will let you generate many E-R diagrams, for example one for accounts, one for sales, and so on. The added benefit is that these diagrams never go stale - they can be regenerated instantly whenever the schema changes.

Sualeh Fatehi, SchemaCrawler


Any program / command to see a preview of .sql dump file? If I have [multiple] .sql dump files, is there a command or tool that would allow me to see a preview of the file ?

e.g: db-preview backup.sql

    tables (40)
    ----------
    - users (1200)
    - articles (450)
...

For MySQL and PostgreSQL at least, database dump files are just text files full of SQL commands. So you can use the usual command line tools to get some idea of the contents. For example

grep 'CREATE TABLE' dump.sql | wc -l

to count how many tables are created. Not as slick as what you're hoping to find, of course.


Change mysql storage engine from Innodb to MyIsam: Max locks exceeded error I have about 5 million records in a table which uses Innodb as storage engine. I have about 6000 users who use (do transactions) the same table. It sometimes becomes very slow or hangs. I decided to manually change to MyIsam. When I try to copy to another table and change the storage engine in phpmyadmin, it gives me Max Locks Exceeded Error. What should I do?

The solution is simply to edit my.cnf, and add or edit the following line:

innodb_buffer_pool_size = 16M

To make it work, I set the size in bytes, so if I wanted 256 megabytes for my buffer pool, I would write 268435456. It would look like like this thus:

innodb_buffer_pool_size = 268435456


SSRS Rectangle visibility based on Parameter Value I am creating a Report in SSRS.
Its a Dynamic 2011 CRM Report.
I have created some Tables.
And some Rectangles.
Each Rectangle contains some Tables.
Also there is a Parameter that contains value from 1 to 4.
It is a multiple values parameter.
The goal is that the user select parameters( one or more ) and then tables based on the rec visibility condition appear.( Maybe two rec appear, or maybe all of them)
We all know that there is IN operator in SQL Syntax.
But I do not know how to make it here.
I googled a lot but no success.
Here is what I tried in rec visibility.

=Parameters!type.Value.Equals("1")

And in the second table :

    =Parameters!type.Value.Equals("2")

till the last rectangle.

Even changed to :

=Parameters!type.IsMultiValue.Equals("1")

I Also Put both of them in =iif, but no success.
How can I perform this?
Visibility of rectangle based on the multiple values parameter.
Any help will appreciated.

Check out this answer. Basically, you want to combine all the values in your selected list using Join then use InStr to detect if the value in question is there.

So if you have Rectangle Rec1 which relies on param option "Rec1Visible" in parameter RecVisibility being selected, then an expression like this should work (pseudo names) for Rec1's visibility property:

=IIF(InStr(JOIN(Parameters!RecVisibility.Value,","),"Rec1Visible")>0,true,false)

Use the same code for each visibility expression, with the relevant value, to control the visibility of your rectangles.


Implementing disk quota for MariaDB I want my Linux MariaDB database to have disk quota implemented.
How can I achieve this these days?

I have found some tools but they are for MySQL, don't know if they are supposed to work on MariaDB.

I ended up running this script in cron:

#!/usr/bin/php -q
<?php

/*
 * MySQL quota script
 * written by Sebastian Marsching
 * updated by Daniel Santos
 *
 */

/*
 *  This program is free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 2 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program; if not, write to the Free Software
 *  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */

/*
 * Create table for quota data with the following statement:
 *
 * CREATE TABLE `quota` (`db` CHAR(64) NOT NULL,
 * `limit` BIGINT NOT NULL,
 * `exceeded` ENUM('Y','N') DEFAULT 'N' NOT NULL,
 * PRIMARY KEY (`db`), UNIQUE (`db`));
 *
 * The field 'db' stores the information for which database
 * you want to limit the size.
 * The field 'limit' is the size limit in bytes.
 * The field 'exceeded' is only used internally and must be
 * initialized with 'N'.
 */

/*
 * Settings
 */

$mysql_host  = 'localhost';
$mysql_user  = 'root'; // Do NOT change, root-access is required
$mysql_pass  = '';
$mysql_db    = 'quotadb'; // Not the DB to check, but the db with the quota table
$mysql_table = 'quota';

/*
 * Do NOT change anything below
 */

$debug = 0;

// Connect to MySQL Server

$con = mysqli_connect($mysql_host, $mysql_user, $mysql_pass, $mysql_db);

if (mysqli_connect_errno()) {
    echo "Connection to MySQL-server failed!";
    exit;
}

// Check quota for each entry in quota table

$sql = "SELECT * FROM $mysql_table;";
$result = mysqli_query($con, $sql);

while ($row = mysqli_fetch_array($result)) {
    $quota_db = $row['db'];
    $quota_limit = $row['limit'];
    $quota_exceeded = ($row['exceeded']=='Y') ? 1 : 0;

    if ($debug) {
        echo "Checking quota for '$quota_db'...\n";
    }

    $qsql = "SHOW TABLE STATUS FROM $quota_db;";
    $qresult = mysqli_query($con, $qsql);

    if ($debug) {
        echo "SQL-query is \"$qsql\"\n";
    }

    $quota_size = 0;

    while ($qrow = mysqli_fetch_array($qresult)) {
        if ($debug) {
            echo "Result of query:\n";
            var_dump($qrow);
        }
        $quota_size += $qrow['Data_length'] + $qrow['Index_length'];
    }

    if ($debug) {
        echo "Size is $quota_size bytes, limit is $quota_limit bytes\n";
    }

    if ($debug && $quota_exceeded) {
        echo "Quota is marked as exceeded.\n";
    }
    if ($debug && !$quota_exceeded) {
        echo "Quota is not marked as exceeded.\n";
    }

    if (($quota_size > $quota_limit) && !$quota_exceeded) {
        if ($debug) {
            echo "Locking database...\n";
        }
        // Save in quota table
        $usql = "UPDATE $mysql_table SET exceeded='Y' WHERE db='$quota_db';";
        mysqli_query($con, $usql);
        if ($debug) {
            echo "Querying: $usql\n";
        }
        // Dismiss CREATE and INSERT privilege for database
        mysqli_select_db($con, 'mysql');
        $usql = "UPDATE db SET Insert_priv='N', Create_priv='N' WHERE db='$quota_db';";
        mysqli_query($con, $usql);
        if ($debug) {
            echo "Querying: $usql\n";
        }
        mysqli_select_db($con, $mysql_db);
    }

    if (($quota_size <= $quota_limit) && $quota_exceeded) {
        if ($debug) {
            echo "Unlocking database...\n";
        }
        // Save in quota table
        $usql = "UPDATE $mysql_table SET exceeded='N' WHERE db='$quota_db';";
        mysqli_query($con, $usql);
        if ($debug) {
            echo "Querying: $usql\n";
        }
        // Grant CREATE and INSERT privilege for database
        mysqli_select_db($con, 'mysql');
        $usql = "UPDATE db SET Insert_priv='Y', Create_priv='Y' WHERE db='$quota_db';";
        mysqli_query($con, $usql);
        if ($debug) {
            echo "Querying: $usql\n";
        }
        mysqli_select_db($con, $mysql_db);
    }
}

mysqli_close($con);
?>

You first need to put your mariadb root password in the script.
You then need to create a database named quotadb and add this table to it:

CREATE TABLE `quota` (`db` CHAR(64) NOT NULL, `limit` BIGINT NOT NULL, `exceeded` ENUM('Y','N') DEFAULT 'N' NOT NULL, PRIMARY KEY (`db`), UNIQUE (`db`));

In this table you will add the values of the database you want quota for and the value for the size of the quota (in bytes).

Execute this script on cron and you are good to go


Set an SSIS SQL Agent job to unexpected error or failure I was running a series of SSIS packages in a SQL Agent job on a virtual server when the server was shutdown - hard.

The package execution report shows it as still running even though I believe it is not. I believe this is because the Agent was not able to write whatever end state record which was required for it to show as "Unexpected termination".

How do I verify that it is, in fact, not still running?
How do I tell the SSIS Reports > All executions that it is not running?

Can I update or insert a record into an SSISdb table?

If you trace the query the report is generating you'll see that the executions are stored in the [catalog].[executions] of the database behind the Integration Services Catalog.

You can just modify your [end_time] there and you should be able to 'fix' the report.

I haven't managed to reproduce your exact scenario (i.e. hard resetting a VM to get a package in the running state) but I could modify the value in that field and the All Executions report reflects the new values.

However, don't set the end_time too much behind the start_time as you'll get overflow errors in the report if your package appears to have run for days.

According to the documentation you have these statuses available to you:

  The status of the operation. The possible values are created (1),
  running (2), canceled (3), failed (4), pending (5), ended unexpectedly
  (6), succeeded (7), stopping (8), and completed (9).

So running this:

UPDATE [catalog].[executions]
SET [end_time] = DATEADD(minute, 2, start_time)
    ,[status] = 6
WHERE [execution_id] = 2;

on my system results in a package that shows "unexpected termination" after 2 minutes.


How does STRICT_ALL_TABLES work with multi-row statements and partial updates? In the docs for STRICT_ALL_TABLES it states,

  For STRICT_ALL_TABLES, MySQL returns an error and ignores the rest of the rows. However, because the earlier rows have been inserted or updated, the result is a partial update. To avoid this, use single-row statements, which can be aborted without changing the table.

What does this mean though?

CREATE TABLE foo ( a int NOT NULL );
INSERT INTO foo VALUES (NULL), (NULL);

This does what everyone would reasonably expect a totally insane database to do,

SELECT * FROM foo;
+---+
| a |
+---+
| 0 |
| 0 |
+---+
2 rows in set (0.00 sec)

But, I expected a multi-row INSERT to insert the non-violating rows that are seen first,

SET sql_mode='STRICT_ALL_TABLES';
INSERT INTO foo VALUES (5), (NULL);

To insert just one row with 5, as per the docs in STRICT_ALL_TABLES. Instead it does,

  ERROR 1048 (23000): Column 'a' cannot be null

How do I interpret the docs to STRICT_ALL_TABLES when they say earlier rows .. partial update and "single-row statements". And why is MySQL doing something less horrible then documented? I'm pleasantly surprised.

I agree that the split between _ALL_ and _TRANS_ needs clarification.

Earlier on that page it says

  With TRADITIONAL mode enabled, an INSERT or UPDATE aborts as soon as an error occurs. If you are using a nontransactional storage engine, this may not be what you want because data changes made prior to the error may not be rolled back, resulting in a “partially done” update.

I think the meat of the issue is InnoDB versus MyISAM.

MyISAM is covered by STRICT_ALL_TABLES, but not by STRICT_TRANS_TABLES.  If an UPDATE or DELETE is aborted for any reason mid-stream, some rows will have been updated or deleted.  You have no practical way to clean up the mess.  (This is a major reason never to use MyISAM.)

InnoDB is covered by both settings, but STRICT_TRANS_TABLES is more relevant, and overrides the statement about "earlier rows".  UPDATE and DELETE are atomic.

Also involved is the question of aborting with an error versus doing something so you can keep going.  For example, bad dates and defaults without an explicit DEFAULT, etc.  These are cases where MySQL (unlike some other engines) defines that a non-abort action will be taken.

If your Question is intended to point out poor phrasing in the documentation, go for it.  But do it at bugs.mysql.com , where there is a chance of it getting fixed.

MySQL 8.0 moves toward more robust default settings by changing the defaults.


ORA-01562: failed to extend rollback segment number 11 I´m trying to execute this query into a SP

 DELETE FROM table SC
      WHERE SC.evaluationMonth= 'Some month';
 COMMIT;

But for this month there are something like 10 million records. So I get the error

  ORA-01562: failed to extend rollback segment number 11.

How can I improve the SP or make something to solve this problem?
Pdta: I had increase de unto to 20G

This isn't a coding issue - the rollback segment simply isn't big enough. Speak to your DBA about getting it extended.

Another issue could be other long running jobs hogging the available rollback space. Either way, the DBA should be your first port of call.


Running a DELETE stored procedure but finding degradation in performance in WHILE loop as iterations increase Have a quick general question. I have a table I'm trying to purge a table. I'm deleting using a WHILE loop with WAITFOR DELAY time of 50ms between batches and 2000 records per batch. The real issue is that as time progresses the number of records deleted drops over time. Refer to the following:

Minute Number | Number of Records Deleted:

            1 | 162,000
            2 | 116,000
            3 |  80,000
            4 |  72,000
            5 |  62,000
            6 |  38,000
            7 |  38,000
            8 |  34,000
            9 |  20,000

Wondering if there is something fundamental about using WHILE loop batches for deleting records that causes the performance to degrade with each iteration of the loop. we have been manually watching the performance and then stopping the proc as the performance begins to drop drastically around the 5th to 6th minute and then restarting the deployment again. We don't think its a locking issue directly because as we play with the batch size the performance is always dropping at around the 5 to 6 minute mark.

You say that performance degrades with the number of executions and that "restarting the deployment" fixes it.

I'm unclear what you mean by that particular phrase but presuming that it involves stopping the loop and then restarting it after some period then one possibility is ghost records.

I created a table as below (with one row per page for easier maths)

CREATE TABLE T
  (
     X INT PRIMARY KEY,
     Y CHAR(8000)
  )

INSERT INTO T
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY (SELECT 0)),
                  'Y'
FROM   master..spt_values v1,
       master..spt_values v2

And assumed your DELETE process is clearing out older rows as identified by some ascending column.

SET STATISTICS IO ON;

DECLARE @i INT = 0;
WHILE @i < 10
  BEGIN
      SET @i+=1

      RAISERROR('Processing %d',0,1, @i) WITH NOWAIT

      DELETE TOP (2000) FROM T
      WHERE  X <= 50000

      /*50 MS Delay*/
      WAITFOR DELAY '00:00:00:050'

      SELECT ghost_record_count,
             version_ghost_record_count
      FROM   sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('T'), NULL, NULL, 'DETAILED')
      WHERE  index_level = 0
  END

Results

+---------------+--------------------+----------------------------+
| logical reads | ghost_record_count | version_ghost_record_count |
+---------------+--------------------+----------------------------+
|          2018 |               2000 |                          0 |
|          4025 |               4000 |                          0 |
|          6033 |               6000 |                          0 |
|          8026 |               7965 |                          0 |
|         10004 |               9944 |                          0 |
|         11989 |              11920 |                          0 |
|         13977 |              13902 |                          0 |
|         15963 |              15882 |                          0 |
|         17954 |              17864 |                          0 |
|         19943 |              19849 |                          0 |
+---------------+--------------------+----------------------------+

Records aren't deleted straight away. They are marked as ghosts to begin with then cleared up by a background task. In the test above very few of them were cleaned up in the 10 iterations meaning that the tenth DELETE did 10 times as many reads as the first one.

Possibly in your environment the DELETEs are occurring at a greater rate than the ghost cleanup task processes them leading to the continual degradation of performance.


Determining the optimal Column Widths from a .csv File I want to import a .CSV file into a MySQL Table.  I would like to determine the optimal column widths to use based on the actual data contained in the CSV file.  Are there any scripts or utilities to accomplish this?

Trying to guess appropriate column lengths is best avoided if possible - if you can get the source of the CSV to specify the expected format in more detail (column X will contain no more than Y characters) then try that.

I would suggest initially going for long variable length types - this may be less efficient than more constrained types but will ensure that you do not experence errors in later imports if some fields grow larger than expected (or worse silently lose data because mysql silently truncates something). Under MSSQL text fields would want to be nvarchar(max) or varchar(max) in this case, unless they need to be part of an index in which case they'll need to be more constrained (no index can be over more than 900 bytes of data under MSSQL). Unless you can get more garantees of data format from the source, this is the safest way, though of course you can use a little guesswork (a field containing a national insurance number shouldn't ever be longer than 11 characters for instance, though be careful still as a field may be incorrectly named nad/or overloaded to store things other than what its name suggests).

If you really have no choice but to guess and using larger types feels too inefficient to you then to help the guestimating process import the file(s) you have into a table where all fields are maximum length variable text types, then perform some rudementry analysis such as SELECT MAX(LEN(<field1>)), MAX(LEN(<field2>)) FROM <table> to get indications to base your guesswork on, and for non-string fields you can use more complex pattern matching to determine expected ranges (i.e. field X is always a data in ISO format, field Y is a number between -500 and 45,123 with up to two decimal places, and so forth).

With regard to "optimal": while it may be inefficient to use larger variable-length types where a short fixed length will do, efficient and optimal are not neccesarily the same: being efficient will be little benefit if you expereience errors or lose data due to accidentally setting the type too restrictively.


Does setting Query Optimizer Fixes to ON enable Scalar UDF Inlining on a database with compatibility level 140? Azure SQL Database Managed Instance. Current Compatibility Level is set to 140 to match on-prem SQL Server 2017 test and development server. I just read this article on Scalar UDF Inlining and was curious if setting Query Optimizer Fixes to ON would enable this feature? I have read several more articles on the topic but none of them address this question.

No, 'query optimizer fixes' only affects optimizer bug fixes that may result in unexpected query plan changes. It does not enable new features.

The only way to enable scalar function inlining is to set the database compatibility level to 150:

  Enabling Scalar UDF Inlining

  You can make workloads automatically eligible for Scalar UDF Inlining by enabling compatibility level 150 for the database. You can set this using Transact-SQL. For example:

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150;

  Apart from this, there are no other changes required to be made to UDFs or queries to take advantage of this feature.


Install Full Text search on existing instance We have an existing sql server 2012 instance but without full text search.
before I add the feature, I want to know if it stops or restart the instance since it is for production.

thanks in advance.

The setup program restarts the target instance when installing full text search.

However, there's an easy way to find out: test it! Don't trust what I say: test it in your test environment.


What happens if an index build is cut half way through? Today I ran a create NC index but stopped the execution after running for 1 hour 46 minutes. The main reason being I started to get worried about the disk space getting lower. It consumed roughly 10 gb of space on a rather expensive storage. Indexes of the db is on its dedicated filegroup and I created this one the same.
Even though I stopped the execution, the 10 gb of space was not released to the OS. The index file has not much free space either. My question is; where has my 10 gb gone?
Thank you,

Since you stopped the index creation, the index was not created. However, the space it had reserved already (10GB) was still added to the database size.

SQL Server will not automatically shrink the database file size back down unless you explicitly instruct it to (which is not recommended unless you are about to run out of space). You can add up to 10GB of data (rows, indexes) to the database before it will need to grow the file size again since that space is unused.

You can right click and select properties of the database in question in Management Studio to see how much space is free of its total size:

If you must shrink, you can read up on it at the MSDN article on how to Shrink a File.


SQL server 2019 adaptive join poor performance I upgrade SQL Server from 2016 to 2019, the query plan of my query changed and it used adaptive join, but unfortunately the duration of query increase to 1 minute from 1 second,
I changed the join order and the problem was solved
The T-SQL code:
SELECT TOP 100 * FROM dbo.APP App
JOIN dbo.PRS p ON App.PartyId=p.PRSId
LEFT JOIN dbo.Country ON p.NationalityId = dbo.Country.CountryId
LEFT JOIN dbo.EDUBranch b ON app.EducationBranchId=b.EDUBranchId

and it 's query plan :
https://www.brentozar.com/pastetheplan/?id=H1cFQxwdP
After change join order :
SELECT TOP 100 * FROM dbo.APP App
LEFT JOIN dbo.EDUBranch b ON app.EducationBranchId=b.EDUBranchId
JOIN dbo.PRS p ON App.PartyId=p.PRSId
LEFT JOIN dbo.Country ON p.NationalityId = dbo.Country.CountryId

and it 's query plan :
https://www.brentozar.com/pastetheplan/?id=SJv1GlPdv
Does anyone have an idea about

Why Adaptive join caused the query to slow down?
How does changing the join order change the execution plan?

The fast plan features a row goal.  This ends up favoring nested loops joins, which deliver 100 rows to the Top operator fairly quickly, satisfying the query.
The slow plan also has a row goal, but really only on the adaptive join operator.  In the case the adaptive join needs to run as a hash join, all the results from the upper input must be consumed (the "build" step of the hash join).  See Forrest McDaniel's blog for a great visualization of how this works: The Three Physical Joins, Visualized

Why Adaptive join caused the query to slow down?

The adaptive join does in fact run as a hash join, since it exceeds the threshold of 88 rows (by quite a lot).  This leads to the query having to read every row from dbo.APP, joining all the matches from dbo.PRS - around 30 GB of reads, according to the execution plan.

How does changing the join order change the execution plan?

The optimizer has the ability to reorder joins in order to filter down a resultset earlier and more efficiently, as long as the query will still produce correct results.  But it doesn't do this very much in the face of a mix of OUTER and INNER joins.  See this Q&A for details on that: Inner join elimination inhibited by prior outer join
When you manually rewrote the join order, it allowed for a plan where the join to dbo.EDUBranch came before the join to dbo.Country - which doesn't have an adaptive join, utilizes the row goal mentioned above, and turns out much better (as you noticed).


Procedure for tree view I trying to fetch data form database which is have 3 column unique_id, parent_id & node_name, but this query (Given in below) is return only two rows.

Condition: every parent has two child.

DROP PROCEDURE `treeTbl`;
CREATE DEFINER=`bizsol`@`localhost` PROCEDURE `treeTbl`(IN `childId` INT(255)) DETERMINISTIC NO SQL SQL SECURITY DEFINER BEGIN
 DECLARE tempId INT DEFAULT 0;
 SELECT *, unique_id as id FROM t_users WHERE 1 AND parent_id = childId;
 IF(tempId>0)
  THEN
    SET tempId = id;
    CALL treeTbl(tempId);
 END IF;
END

And call form here,

CALL treeTbl(3);

3 column unique_id, parent_id & node_name, but this query (Given in below) is return only two rows.

MySQL doesn't have Tail Call Optimization. Without TCO, each call will take more memory, and get a different stack. So implementing it in this fashion is at the very best insecure because you can't ensure through a constraint that the query won't result in SQL dying. Case and point, retrying the query in such a case will result in another dead backend. So now you gotta do something other than retry. Any answer that uses this method is pretty misguided.

So what do you do? Upgrade to MySQL 8 beta. That has Recursive CTEs. This is called a hierarchical query, or even better -- migrate to PostgreSQL which is a superior database that has had this feature for like a decade.

For more information, see the hierarchy tag.


Releasing free space in mdf file to OS We have a 120GB database. There was table with 60GB of data which is useless and we have truncated it.

Now database size is 120GB with free space 60GB. The database will not grow up to 60GB in at least 3 months. So can we shrink the data file.

Am aware of fragmentation issue. I can rebuild my index since our's is not a 24*7 database.

Please advice on shrinking MDF file

What exactly you are trying to achieve by shrinking the database?
You should plan for the database growth and have reserved space for this growth.

So why don't you leave it as it is to have allocated space for new data coming to your DB.

It has been discussed so many times that you do not want to shrink your databases. Check this out and think twice before shrinking:

Stop Shrinking Your Database Files. Seriously. Now. By Brent Ozar


Reclaiming unused indexes disk space Are there any hints, stats or alike available to check thoroughly which indexes can be safely deleted from a PostgreSQL 9.4.9 database? Our database keeps growing and growing and, we think there might be some lost in indexes not being used. In the past, we tried to trim down the database overall size (about 2TB now, mostly historic data), but customers kept needing access to old data, so we had to stop expiring old "supposedly unused" data because bringing it back alive, was a huge pain.

Any additional comments on saving space would be greatly appreciated.

Perhaps you could start with:

SELECT *
    FROM pg_stat_all_indexes
    WHERE schemaname NOT IN ( 'pg_catalog', 'pg_toast' )
        AND idx_scan = 0
        AND idx_tup_read = 0
        AND idx_tup_fetch = 0
        AND indexrelid NOT IN (
            SELECT indexrelid
                FROM pg_index
                WHERE indisunique = true
                    OR indisprimary = true ) ;

No guarantee that the indexes that pop up aren't ever used, but it should provide a list to start looking at.


optimal size of innodb buffer pool I read a lot of optimal number of innodb_buffer_pool_size and people say it must be larger with 10% than the innodb tables size, what is 60M. Accordyngly this the optimal size of the buffer is about 66M im my case.
Should I set this value more than 66M, for example 256M or 1G to achieve better performance? I have enough memory to add more to innodb buffer pool if that improve the performance.
Thanks

If you expect your InnoDB data to grow, you should prepare for it by having a larger InnoDB Buffer Pool.

Where does the idea of having additional 10% extra space for the Buffer Pool come from ???

Please note the following diagram of the InnoDB Architecture

Look in the upper left hand corner where you see the buffer pool. There is a section of it called the "Insert Buffer Part". The purpose of the Insert Buffer is to populate changes to non-unique indexes into the Insert Buffer inside the system tablespace file (a.k.a. ibdata1).

From the diagram, up to 50% of the Buffer Pool can be used to manage the Insert Buffer. That being the case, it would be in your best interests to assign a lot more memory to the Buffer Pool. With a data set of 60M, 256M would be a great place to start,


How do I override the dates with Temporal tables? I'm trying to use temporal tables for our data lake environment to track changing data over time.  The first hurdle I hit is loading historical data to seed the tables.  Everything is loading as "now" instead of as of the point in the past.  Searching online the only solution I have found is to turn off system versioning, disable the period in the table, make changes to the table to fix the dates, then turn system versioning back on and hope that SQL Server will let me.  Is there a better way to force the datetime values to be what I need them to be?
Also, going forward, we are loading data that was updated yesterday and captured last night and being loaded into the lake early this morning.  I need to use yesterday's date as the effective date for the temporal table effective date, not the current timestamp.  Same issue, how can I force the datetime values?

The time in a temporal table is "system time" not "business time".  That's the distinction that the feature name "System-Versioned Temporal Table" is getting at.

You inserted the data today, so it's versioned as of today.  You can use the temporal table to track the changes you make going forward, and retain change history, but it doesn't replace a slowly-changing dimension for business analytics.  As you noted, Temporal Tables belong in the "Data Lake" or "Raw" or "Staging" layer.

If you want to add additional history representing changes to the data before your initial load, you can turn system versioning off temporarily while you load data directly into the history table.


Can't find table/object error when running an index rebuild I am running a index rebuild script:

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0

BEGIN

    DBCC DBREINDEX(@TableName,' ',90)

    FETCH NEXT FROM TableCursor INTO @TableName

END

CLOSE TableCursor;
DEALLOCATE TableCursor;

but it fails with the error "Msg 2501, Level 16, State 45, Line 13
Cannot find a table or object with the name "ServiceDeploymentsTable". Check the system catalog."

The script is from https://gallery.technet.microsoft.com/scriptcenter/Script-for-rebuilding-all-8d079754

I have a felling the table might belong to a different object (like dbo or something else) but I don't know how to confirm that or modify the script to deal with that. Or maybe I am compleatly off base.

How can I best deal with this error and rebuild all the indexs in the database?

The chances are the the table you are attempting to reindex exists in a different schema than the default, and as such you will not find it.

The following update should allow you to obtain that table.

DECLARE @TableName nvarchar(512)

DECLARE TableCursor CURSOR FOR
SELECT QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0

BEGIN

    DBCC DBREINDEX(@TableName,' ',90)

    FETCH NEXT FROM TableCursor INTO @TableName

END

CLOSE TableCursor;
DEALLOCATE TableCursor;

A couple of things that you should note, DBCC DBREINDEX is deprecated, you should really be using ALTER INDEX REBUILD instead. As an additional item, rather than trying to reinvent the wheel there are free solutions out there that have been written by folks with many years of experience, such as Minionware Reindex. These are designed to be quickly and easily deployed and to provide simple management.


how to extract the last inserted value i have two table i.e tbl_Contactgroup(id,name) and tble_contactlist(id,name,address,telephoneno,mobileno,groupid)
my question is how to retrieve all the contact list value from the tble_contactgroup by using extracting last inserted value from the tbl_Contactgroup where id in tbl_Contactgroup and groupid in tble_Contactlist are related to each other ie foreign key

SELECT id,name,address,telephoneno,mobileno
FROM tble_contactlist
WHERE groupid= (SELECT MAX(id) FROM tbl_Contactgroup)

Try this.. it will give you the data for all the value in contact list for the last inserted value in contactgroup


Problem with IF / BEGIN / ELSE I'm trying to run a select IF there's something inside a folder:
   INSERT INTO #tb (SubDirectory, Depth, [File])
   EXEC master..xp_dirtree 'C:\folder\', 10, 1

This will populate a table with the name of the file (if there's one inside the folder)
Then, if there's an Excel file there (or not) I will run this query:
IF (SELECT COUNT(1) FROM  #listavarejo) > 0
        SELECT *  FROM OPENROWSET('Microsoft.ACE.OLEDB.15.0','Excel.0;Database=C:\folder\excelfile.xlsx', [Planilha1$])
ELSE
        RAISERROR('NO FILES INSIDE FOLDER.',16,10);

The problem is, when I try to run this:
IF (SELECT COUNT(1) FROM  #listavarejo) > 0
        RAISERROR('there are file inside the folder.',16,10);
ELSE
        RAISERROR('theres no files inside the folder.',16,10);

It works perfectly. If there's a file inside the folder, the error "there's is a file inside the folder", if there's no files, I can receive the error message after else.
But with the select * from openrowset, even with a file inside the folder or not, the query try to runs the select, and obviously I receive the error that the openrowset couldn't find the file.
Even using BEGIN/END I cant fix this.

I don't have an easy way to test this theory right now, but I'd bet that there are some validation checks of the OPENROWSET parameters going on when the batch compiles (the whole batch, regardless of IF / ELSE and whether or not those blocks eventually get executed).
Whenever I encounter this problem, using dynamic SQL is usually a solution.  Give that a shot:
IF (SELECT COUNT(1) FROM  #listavarejo) > 0
        EXEC sys.sp_executesql N'SELECT *  FROM OPENROWSET(''Microsoft.ACE.OLEDB.15.0'',''Excel.0;Database=C:\folder\excelfile.xlsx'', [Planilha1$])';
ELSE
        RAISERROR('NO FILES INSIDE FOLDER.',16,10);

This allows the code inside the dynamic SQL to be compiled only if it's executed.


"Limit 1000,25" vs "limit 25 Offset 1000" Recently I've found out that MySQL has a offset feature. I've been trying to find documentation about the results of offset, or the difference in between offset and the limit variant, but I can't seem to find what I'm looking for.
Lets say I have 10.000 rows in a table and I want 25 results, from row 1.000. As far as I got so far, I could do both to get the same result:
SELECT id,name,description FROM tablename LIMIT 1000,25
SELECT id,name,description FROM tablename LIMIT 25 OFFSET 1000

What I'd like to know is the difference between the two.

Does this actually do the same or is my understanding wrong?
Is one slower/faster in larger tables
Does the result of offset change when I do WHERE column=1 (say column has >100 different values)
Does the result of offset change when I do ORDER BY column ASC (asuming it has random values)

I have the feeling offset skips the first X rows found in the database, disregarding sorting and the where.

In terms of operation
SELECT id,name,description FROM tablename LIMIT 1000,25
SELECT id,name,description FROM tablename LIMIT 25 OFFSET 1000

there is absolutely no difference in the statements
siride's comment:

from https://dev.mysql.com/doc/refman/5.6/en/select.html

For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax.

is exactly the point.

LIMIT 1000,25 means LIMIT 25 OFFSET 1000

From the same Documentation

LIMIT row_count is equivalent to LIMIT 0, row_count

YOUR ACTUAL QUESTIONS

Does this actually do the same or is my understanding wrong?
Is one slower/faster in larger tables

Since both queries are the same, there is no difference

Does the result of offset change when I do WHERE column=1 (say column has >100 different values)
Does the result of offset change when I do ORDER BY column ASC (asuming it has random values)

Using LIMIT does not change any result sets. They simply navigate within the result set.
This query
SELECT id,name,description FROM tablename ORDER BY id LIMIT 1000,25

would be different from
SELECT * FROM (SELECT id,name,description FROM tablename LIMIT 1000,25) A ORDER BY id;

because the LIMIT is being applied at a different stage.
The first query returns nothing if tablename has less 1000 rows
The second query returns nothing if the subquery has less 1000 rows
CONCLUSION
You will have to sculpt the query to make sure you are sorting data at the right stage


Take one replica offline from an availability group I have 2 replicas running SQL Server 2016 Enterprise Edition in an AlwaysOn Availability Group.

One replica is primary and other replica is secondary and readable.

I have a business requirement that should allow one database replica to be taken offline and back online after a period of a few minutes by a custom developed APM monitor.

The database replica to be taken offline can be primary or secondary.

The database replica has to be take offline and online via transact SQL

When the database replica is offline it should not be possible for it to execute requests. All existing connections must be moved to the other database replica or fail, so that the client can reconnect using the IP listener to the other replica.

I can do a suspend or a planned manual failover but neither seems to support what I need.

The suspend & manual failover only works on secondary database replicas.

The suspend allows existing connections to execute requests.

The manual failover just switches the primary to secondary role and vice versa.

How do I do this ?

The database replica to be taken offline can be primary or secondary.

The database taken offline should always be the secondary.

  The database replica has to be take offline and online via transact SQL

That's possible.

  When the database replica is offline it should not be possible for it to execute requests. All existing connections must be moved to the other database replica or fail, so that the client can reconnect using the IP listener to the other replica.

Basically here's how it would go:

If the database replica that needs to be taken offline is a primary, check for the current secondary to be synchronized.
Set the database to be "taken down" as a non-readable secondary.
If the database replica was a primary, failover.
Do any other items you'd like here, such as removing it from the AG, etc.


Partitioning and the InnoDB Buffer Pool I am trying to maintain a database that contains a table with 40 billion rows (7.2 terabytes) on a server using InnoDB as a storage engine and MariaDB with MySQL 5.5.

When my database reaches about 2.5 terabytes, I can no longer insert data into the table at the rate required in production. Data in the table is rarely queried after 24 hours. The table has a primary key and one secondary index. After doing quite a bit of research, it seems like understanding the InnoDB buffer pool will be critical if I'm going to solve this problem. This is obviously too much data to fit into the buffer pool. I have a few ideas about how I can improve performance by increasing the probability data from the last 24 hours will be in the buffer pool, but it's difficult to test them all with such a large amount of data. How will the InnoDB buffer pool behave in each of the following situations? Is one idea obviously better? Or are they all bad?

Split the large table into partitions by time such that each partition's data and index fit into the buffer pool. - https://mariadb.com/kb/en/partition-maintenance/ suggests that this should improve performance, but I've seen conflicting information about how indexing works for partitioned tables. Is it one massive index? Or several smaller indexes that will fit into the buffer pool? If it's one large index, it's hard to see how this would help.
Create 2 time-partitioned tables. One table will be a large table of archived partitions, and one table will hold only one "active" partition with data that is likely to be queried (maybe a week). When I transition to the next partition in the active table (next week's data), exchange the recently active partition (last week's data) into the archive table. - This seems advantageous because the active table is guaranteed to fit into the buffer pool and queries that might execute a full table scan will not read data that will purge the active data from the buffer pool because the old data is in a different table. However, I'm assuming that when I exchange the recently active partition into the archive table, everything will come to a halt while the index for the large table is read from disk into the buffer pool and recalculated. Then there will be some time where performance suffers after that until the active data makes its way back into RAM.
Create 1 time-partitioned table that holds archived data, and one small table that is minimal in size (probably 24 hours worth of data). Then copy the data more than 24 hours old out of the small table into the partitioned archive table. - It's hard for me to imagine how this could be a good option unless copying data is somehow faster than moving an entire partition.

Any insight is greatly appreciated!

From InnoDB perspective there are no partitions. Partitioning is implemented on the server level and for InnoDB each partition looks like a separate table.

You're going right direction for optimizing writes. If you partition by time the most recent partition will be one that gets new writes. It will be cached in the buffer pool and that'll be good for performance. If the partition is small enough to fit into the buffer pool you will see no reads and there will be as many writes as commits (if innodb_flush_log_at_trx_commit=1) plus some amount for flushing dirty pages. That'll be your capacity limit.

Before you partition do a research with your read queries. The secondary index was created for some reason and I assume the read queries benefit from it.
After you partition, and if the read query doesn't change, MySQL will have to do as many index searches as number of partitions. So, the read query will be N times slower (I assume an index search time on a partition and the big table is same  which is not strictly true, but ok for rough estimation).

Use EXPLAIN PARTITIONS SELECT... to see what partitions MySQL hits to execute the query. If you can change the SELECT so it hits only one partition (by adding PK clause) that'd be ideal situation.


Possible to detach a database from SQL Server while retaining file permissions? When a user detaches a database from MS SQL Server, it sets the owner of the data and log files to that user. It also removes all non-owner permissions from the files. This is partially explained at http://msdn.microsoft.com/en-us/library/ms189128.aspx

I have tested this on the following versions of SQL Server

2005
2008
2008 R2

Is it possible to change this behavior so file permissions are retained when a database is detached?

While not a concrete answer, I find no configuration option or setting to override this behavior.  It seems to be baked in.

Everywhere Detaching and Attaching Databases is mentioned in the documentation points back to the Securing Data and Log Files page you referred to above.  The scenario on this page makes it pretty clear what is going on, and why the files should be secured as such.

Perhaps you could use a different user or service account to perform the operation?  IF the file is owned by the proper account (such as the SQL Server service account) this whole thing may be a non-issue.


Capturing transactions opened / committed during backup I gotta believe there's a way to do this, but not clear how. Don't know PostgreSQL internals well enough, however.

Let's say I start a dump of a database at 11:00, and I know said dump takes 5 minutes to complete.

I have an open transaction (T1) that was created at 10:59 and because [some reason] it doesn't COMMIT till 11:03
A new transaction (T2) is opened and committed at 11:01
Another transaction (T3) is created at 11:03 and COMMITs at 11:04

Is there a relatively straight-forward way to capture T1-T3 and include them in the same archive as all of the other committed changes that occurred between the time of my last backup and 11:00? In essence, I want to "loop back" to capture all the activity that occurred between 11-11:05 and save that, too.

No, a pg_dump captures the point in time when it started.

You seem to want point-in-time-recovery, for which you need pg_basebackup and WAL archives.


Window functions and a single subquery:

SELECT user_id, date, is_cleared
     , count(*) OVER (PARTITION BY user_id, grp ORDER BY date) AS custom_rank
FROM  (
   SELECT user_id, date, is_cleared
        , count(is_cleared = 1 OR NULL) OVER (PARTITION BY user_id ORDER BY date DESC) AS grp
   FROM   tbl
   ) t
ORDER  BY user_id, date;

The special difficulty here: each partition ends with 1. In most similar cases, each partition would start with 1. The simple solution is to count occurrences of 1 in descending order. This way each partition includes the next 1 if it exists. Voilá.

Assuming all involved columns to be NOT NULL and (user_id, date) unique.

Basics:

Select longest continuous sequence

Asides:

The first row of B gets custom_rank = 1. Not 2.
The column is_cleared should really be boolean.
A timestamp column shouldn't be called "date".


Update column with another table's value I have two tables, users (with columns email, name, id) and user_details (with columns username, address, details). Currently the username in user_details is populated with a mix of ID's and Email's. I want the table to have all ID's. Is there a way I can match the rows in user details that are currently populated with email to the row with the same email in the users table, and update it with the id of that row? I have tried the below query:

update
(select distinct
    u.sid as new_id,
    ud.username as id
from user_details ud
    inner join users u on
        lower(ud.username) = u.email) up set up.id = up.new_id;

to try and join the two tables and then update the join, but I am getting the following error:

  SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
  01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"

When trying to create a situation that resembles the one you are describing, I have used the following DDL code (notice that there are no constraints), using Oracle 12c:

Test setup

create table users (
  email varchar2(32)
, name varchar2(32)
, sid varchar2(32) -- <- assumption: data type NOT number
);

create table user_details (
  username varchar2(32)  -- <- populated with a mix of ids and emails
, address varchar2(64)
, details varchar2(64)
);

After inserting some test data, the tables contain ...

SQL> select * from users;
EMAIL                  NAME    SID
user.mail1@domain.org  name_1  1
user.mail2@domain.org  name_2  2
user.mail3@domain.org  name_3  3

-- user_details: username contains "a mix of ID's and Email's"
SQL> select * from user_details;
USERNAME               ADDRESS    DETAILS
1                      address_1  details_1
user.mail1@domain.org             more details (user 1)
2                      address_2  details_2
user.mail2@domain.org             more details (user 2)
3                      address_3  details_3
user.mail3@domain.org             more details (user 3)
3                      ---        some more details (user 3)

Problems

When using your original UPDATE, we get "ORA-01732"

update
(select distinct
    u.sid as new_id,
    ud.username as id
from user_details ud
    inner join users u on
        lower(ud.username) = u.email) up set up.id = up.new_id ;
Error at Command Line : 2 Column : 1
Error report -
SQL Error: ORA-01732: data manipulation operation not legal on this view
01732. 00000 -  "data manipulation operation not legal on this view"

When running the same UPDATE, without DISTINCT, we get "ORA-01779"

update (
  select
    u.sid as new_id,
    ud.username as id
  from user_details ud
    inner join users u on lower(ud.username) = u.email
) up
set up.id = up.new_id ;
SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"
*Cause:    An attempt was made to insert or update columns of a join view which
           map to a non-key-preserved table.
*Action:   Modify the underlying base tables directly.

Let's see if we can do this with a subquery in SET ... (still no joy)

UPDATE user_details
SET user_details.username =
(
    SELECT DISTINCT users.sid AS new_id
    FROM users, user_details
    WHERE LOWER(user_details.username) = users.email
);
Error report -
ORA-01427: single-row subquery returns more than one row

Solution

Maybe you want to consider the following solution: leave the original user_details table as it is.  Create a new table by SELECTing everything you need from user_details.

create table userdetails_new
as
select
  u.sid       as new_id -- <- sid corresponds to an email address in users
, ud.address  as address
, ud.details  as details
from user_details ud
  join users u on lower(ud.username) = u.email
union
select
  username -- <- username that is NOT an email address
, address
, details
from user_details
where username not like '%@%' ;

The new "user details" table now contains ...

SQL> select * from userdetails_new;
NEW_ID  ADDRESS    DETAILS
1                  more details (user 1)
1       address_1  details_1
2                  more details (user 2)
2       address_2  details_2
3                  more details (user 3)
3                  some more details (user 3)
3       address_3  details_3

Now we can also add some constraints, such as:

alter table users add unique(sid);

alter table userdetails_new
add constraint fkey_userdetails
foreign key (new_id) references users (sid) ;

I'm sure that there are other solutions for this.  However, the suggested procedure allows us to keep the original "user_details" data as long as we want ie until we have checked that the update/transformation was successful and correct.
Dbfiddle here.


Use one big table or multiple small tables for described scenario I have a question regarding my database scheme:

Lets assume I have 100+ measurement devices, each of them is equiped with a  voltage and current sensor. Multiple (50-100) of those devices are aggregated in a bigger device (DEVICE)
Shall I create a mysql table for each device and each quantity and store the values like:

DEVICE1_voltage:
Timestamp, sub_device1_value, sub_device2_value, sub_deviceX_value...

DEVICE1_current
Timestamp, sub_device1_value, sub_device2_value...

DEVICE2_voltage:
Timestamp, sub_device1_value, sub_device2_value, sub_deviceX_value...

DEVICE2_current
Timestamp, sub_device1_value, sub_device2_value...

...

Or is a relational scheme like:

DEVICES:
id, name

sub_devices:
id, DEVICE_id

voltage_reads:
id, timestamp, sub_device_id, value

current_reads:
id,timestamp, sub_device_id, value

more appropriate? The advantage I see with version 2 is that my scheme will never change, as with version 1 I get two new tables with each new device. These Tables then have different numbers of colums as they differ in sensor setups.

The big disadvantage of version 2 is see is when I get a lot of measurements and the table grows very big....plus that when I query Data this is already the format I need later

Which scheme is better in "theory"?

Thanks in advance!

Edit: Renamed tables and colums, to make more clear that each device can have multiple sub devices

You definitely should not have one table per device. That means you'll have to create dynamic SQL to merge the device name into the query, and create a new table every time you add a device. So version 2 is much more appropriate than version 1.

But you don't necessarily need separate tables for voltage and current. You could have a single table device_log, with columns:

id - primary key
sub_device_id - foreign key to sub_devices table
timestamp
type - 'voltage' or 'current'
value

Or if you don't need so much generality, and always read current and voltage at the same times:

id - primary key
sub_device_id - foreign key to sub_devices table
timestamp
voltage
current


Why are multiple COUNT faster than one SUM with CASE? I wanted to know which of the following two approaches is faster:

1) Three COUNT:

 SELECT Approved = (SELECT COUNT(*) FROM dbo.Claims d
                  WHERE d.Status = 'Approved'),
        Valid    = (SELECT COUNT(*) FROM dbo.Claims d
                    WHERE d.Status = 'Valid'),
        Reject   = (SELECT COUNT(*) FROM dbo.Claims d
                    WHERE d.Status = 'Reject')

2) SUM with FROM-clause:

SELECT  Approved = SUM(CASE WHEN Status = 'Approved' THEN 1 ELSE 0 END),
        Valid    = SUM(CASE WHEN Status = 'Valid'    THEN 1 ELSE 0 END),
        Reject   = SUM(CASE WHEN Status = 'Reject'   THEN 1 ELSE 0 END)
FROM dbo.Claims c;

I was suprised that the difference is so large. The first query with three subqueries returns the result immediately whereas the second SUM approach needs 18 seconds.

Claims is a view which selects from a table containing ~18 million rows. There is an index on the FK-Column to the ClaimStatus table which contains the status-name.

Why does it make such a great difference whether i use COUNT or SUM?

Execution-plans:

COUNT
SUM

There are 12 statuses in total. Those three statuses belong to 7% of all rows.

This is the actual view, i'm not sure if it's relevant:

CREATE VIEW [dbo].[Claims]
AS
SELECT
   mu.Marketunitname AS MarketUnit,
   c.Countryname     AS Country,
   gsp.Gspname       AS GSP,
   gsp.Wcmskeynumber AS GspNumber,
   sl.Slname         AS SL,
   sl.Wcmskeynumber  AS SlNumber,
   m.Modelname       AS Model,
   m.Salesname       AS [Model-Salesname],
   s.Claimstatusname AS [Status],
   d.Work_order      AS [Work Order],
   d.Ssn_number      AS IMEI,
   d.Ssn_out,
   Remarks,
   d.Claimnumber     AS [Claim-Number],
   d.Rma_number      AS [RMA-Number],
   dbo.ToShortDateString(d.Received_Date, 1) AS [Received Date],
   Iddata,
   Fisl,
   Fimodel,
   Ficlaimstatus
FROM Tabdata AS d
   INNER JOIN Locsl AS sl
           ON d.Fisl = sl.Idsl
   INNER JOIN Locgsp AS gsp
           ON sl.Figsp = gsp.Idgsp
   INNER JOIN Loccountry AS c
           ON gsp.Ficountry = c.Idcountry
   INNER JOIN Locmarketunit AS mu
           ON c.Fimarketunit = mu.Idmarketunit
   INNER JOIN Modmodel AS m
           ON d.Fimodel = m.Idmodel
   INNER JOIN Dimclaimstatus AS s
           ON d.Ficlaimstatus = s.Idclaimstatus
   INNER JOIN Tdefproducttype
           ON d.Fiproducttype = Tdefproducttype.Idproducttype
   LEFT OUTER JOIN Tdefservicelevel
                ON d.Fimaxservicelevel = Tdefservicelevel.Idservicelevel
   LEFT OUTER JOIN Tdefactioncode AS ac
                ON d.Fimaxactioncode = ac.Idactioncode

The COUNT(*) version is able to simply seek into the index you have on the status column once for each status you are selecting, whereas the SUM(...) version needs to seek the index twelve times (the total number of unique status types).

Clearly seeking an index three times is going to be faster than seeking it 12 times.

The first plan requires a memory grant of 238MB, whereas the second plan requires a memory grant of 650MB.  It may be that the larger memory grant could not be immediately filled, making the query that much slower.

Alter the second query to be:

SELECT  Approved = SUM(CASE WHEN Status = 'Approved' THEN 1 ELSE 0 END),
        Valid    = SUM(CASE WHEN Status = 'Valid'    THEN 1 ELSE 0 END),
        Reject   = SUM(CASE WHEN Status = 'Reject'   THEN 1 ELSE 0 END)
FROM dbo.Claims c
WHERE c.Status = 'Approved'
    OR c.Status = 'Valid'
    OR c.Status = 'Reject';

This will allow the query optimizer to eliminate 75% of the index seeks, and should result in both a lower required memory grant, lower I/O requirements, and faster time-to-result.

The SUM(CASE WHEN ...) construct essentially prevents the query optimizer from pushing the Status predicates down into the index seek portion of the plan.


TempDB data files don't shrink well on SQL 2008 So ideally you want to pre-size your TempDB data and log files appropriately so that this isn't a concern, but sometimes a rogue developer runs a crazy huge query on a production server during work hours, causing the TempDB data files to blow up huge.

If TempDB was literally the only thing on the drive, then I could probably just leave it like that, but on some servers I have several SQL instances that all share the same TempDB drive.

So how can I shrink these TempDB data files without restarting the instance?

I normally try:

DBCC SHRINKFILE (name = 'tempdev', size = 5000)

This worked fairly consistently in SQL 2000 and 2005 (assuming the actual tempdb activity had tapered off), but seems to not work very often in 2008 (on my current server, it only worked on 1 of 4 data files, the others continue to remain 2-3x larger).

You've got two different questions in here:

Q: Sometimes a rogue developer runs a crazy huge query on a production server during work hours, causing the TempDB data files to blow up huge.

A: When that happens, all of the TempDB data files will grow roughly equally, so you won't have to worry about shrinking specific ones. Frankly, you don't want to shrink them - if you've got drive space set aside for TempDB, just leave these files in place. Why keep re-fighting the same battle? You'll have a rogue developer run another query in a few weeks. Just leave this in place. You don't get bonused based off empty drive space.

Now, having said that, if you've got TempDB's data and log files on the same volume as your user databases (or heaven forbid, the boot drive), then that's the real root cause, and you need to fix that. Even if you don't put them on separate spindles, TempDB should be on a separate logical volume to mitigate this exact problem. When it fills up, it fills up - but it doesn't take user databases (or the entire server, in the event of a full C drive) offline.

Q: (DBCC SHRINKFILE) seems to not work very often in 2008

It's more a function of how SQL Server relies more and more on TempDB in each release. When something's active in TempDB, you can't move its data around, and each new version of SQL Server works more in TempDB. For example, when you enable Read Committed Snapshot Isolation, the version store it uses lives in TempDB. When you use AlwaysOn Availability Groups, it tracks user database statistics in TempDB too. This is just another reason why you set aside a logical volume for TempDB, size the data files to fill it up, and then walk away - your work here is done, and don't try to shrink those files.


Analyzing a query plan for a query with TWO predicates, but index only has ONE Note: This is from an introductory database systems course at my university so I'm not entirely sure if it matters, but for what it's worth we are being taught MySQL.

One homework question I'm doing right now is to analyze some query plans for a query with two predicates, specifically:

SELECT *
FROM Student
WHERE wam > 75 AND faculty = 'Arts';

however the plans provided assume an index only on one of the predicates, such as an unclustered B+ tree index on (wam).

One example part of the question is as follows:

  Compute the estimated cost of the best plan assuming that an
  unclustered B+ tree index on (wam) is the only index available.
  Suppose there are 60 index pages. Discuss and calculate alternative
  plans

How would I go about calculating the cost of a plan like this? Would I have to calculate costs for unclustered/clustered B+ tree and hash indexes for the other predicate, faculty, as well? I am aware that one alternative plan is also simply a full table scan.

"Student" has 800 pages, 64000 tuples. "faculty" can take 10 unique values. "wam" can take 100 unique values.

Thank you, if I'm not being specific enough feel free to point out anything lacking in this post.

The optimal index for that query is INDEX(faculty, wam), in that order.

MySQL does not (in most situations) use two different indexes.  However, it will use a 'composite' index to good use in some situations, such as this.

Without knowing how many rows have Arts and home many have > 75, you cannot accurately conclude which of INDEX(wam) or INDEX(faculty) would be better.  Nor can the Optimizer, since it's statistics gathering is not very good.  SHOW INDEXES and EXPLAIN SELECT provide numbers that come from the same statistics, so you won't necessarily come to the optimal conclusion.

A "Hash" index (which InnoDB does not have) is essentially no better than a BTree index.  A Hash index is useless for a range (eg, > 75).  (Hence, MySQL uses BTrees, and does not provide Hash indexes.)

Another point:  If more than a small percentage of the table has wam > 75, INDEX(wam) will be shunned; scanning the entire table is likely to be faster than bouncing between the index's BTree and the data BTree.  Again > 75 and 60 pages and 10 and 100 give no good clues of which path the Optimizer should take.  The answer to the quoted question is "it depends" or "not enough info".

You could tackle the question by presenting two answers:  One where very few rows have wam > 75, and one where most meet that predicate.

Has your instructor had any experience using databases, or only textbooks?

More discussion on devising optimal indexes:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql


sp_execute expects parameter '@handle' of type 'int' I'm trying to check in a stored procedure if a table exists in my destination database.  If it does not then I will create the table useing the information_schema tables from the source database.  However when I use sp_execute to try and bring back if the table exists I'm receiving the error
Procedure expects parameter '@handle' of type 'int'.

I'm not using an @handle parameter.  Can someone tell me what this error means and why I'm receiving it?  The relevent portion of my code is below.

DECLARE @SQL NVARCHAR(MAX),
        @Parameters NVARCHAR(4000),
        @TableNotExists INT,
        @SourceTable NVARCHAR(200),
        @DestDB NVARCHAR(200)

BEGIN

SET @SourceTable = 'table'
SET @DestDB = 'database'
SET @Parameters = N'@SourceTableIN NVARCHAR(200), @TableNotExistsOut INT OUTPUT'
SET @SQL = N'USE [' + @DestDB + '] IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'' AND TABLE_NAME = @SourceTableIN)
BEGIN SET @TableNotExistOUT = 1 END'

EXEC sp_Execute @SQL, @Parameters, @SourceTableIN = @SourceTable, @TableNotExistsOUt = @TableNotExists OUTPUT

END

I think you meant to use sp_executesql:

EXEC sp_Executesql @SQL, @Parameters, @SourceTableIN = @SourceTable, @TableNotExistsOut = @TableNotExists OUTPUT

And as JonSeigel pointed out in the comment, you have mis-spelled a parameter in your statement:

SET @SQL = N'USE [' + @DestDB + '] IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'' AND TABLE_NAME = @SourceTableIN)
BEGIN SET @TableNotExistOUT = 1 END'

That should be @TableNotExistsOUT.


Performance - Database queries for range of dates Currently we have a huge database from which we are aggregating necessary information for a project to be stored in a database specific to that project.

A new functionality now requires data to be fetched based on a time scale which can be - days, weeks, months, years. We are open to database redesign in order to optimize the queries.

The only solution I can think of is -

Have table with entries for days called day table, once it hits the 7day mark update a week table entry and once that hits a limit update the month table and so on. So when we have the query for 2010-2017 we'll fetch 2010-2016 from the year table then the months from the month table and the days from the day table.

The possible difficulty I can see at the moment with this proposed solution is that it would be difficult to calculate week ranges.

Is there a better way to solve this problem or any suggestions that can improve the solution proposed.

Any help is appreciated.I'm new to working with Database design.

Example :
From 2015-2017(till 06-Apr-17)
Then - Get values for 2015-2016 from the years table
Jan 2017 - Mar 2017 from the months table
01 Apr 2017 - 06 April 2017 from the day table

I'd recommend you make a date column to store all this data then partition and index it. You'll then be able to query it for days and months as you would usually query the date.

Here's some information on partitions (in terms of oracle DB):
https://docs.oracle.com/cd/B28359_01/server.111/b32024/partition.html

It effectively means if you partition by month and you only need to query that month you will only select data from that partition instead of the whole table.


What is the Always On Sync sync frequency? On an Always On Availability group, I understand an async secondary replica asks the primary for the blocks to be shipped. However, I would like to know how frequent is this task (to know the possible time frame of data loss) and to know if this frequency can be altered (to minimize the data the time frame of data loss).

Database replication does not occur on a specific timeframe--the primary replicates data to each secondary constantly.  If the secondary is asynchronous, the primary will replicate data to it as fast as it can.  If there are no bottlenecks, an asynchronous secondary will only be behind by what is currently in flight.

There are DMVs that provide statistics showing how much data is queued for each secondary and the last commit times.  There is a log send queue, which is basically transactions in the log on the primary that haven't yet been sent the secondary.  The other queue is the redo queue on the secondary, which is transactions that have been received and written to the log, but haven't yet been committed to the data file.

There are a number of articles, such as Measuring Availability Group synchronization lag, that have nice queries for quickly seeing how much data is in each of the queues, and any good monitoring software will also provide a view with this information.

Also, to be clear, your reference to "blocks" is incorrect as the database replication does not work on a block basis.  Database replication uses transactions.

So to answer your question about what you can do to minimize the amount of potential data loss, you simply have to make sure that the secondary has adequate storage performance to keep up, and that there is adequate network throughput to transfer data from the primary to the secondary as fast as it is generated.

One item to note is that bandwidth is not equal to trhoughput.  If you have a 100 Mbs link, you will not get 100 Mbs throughput.  If there is significant latency, your throughput will be much less. See  Why your maximum throughput is less than your bandwidth for a good explanation. WAN accelerators can be used if needed to overcome high latency on a link and provide better throughput.


Master and Slaves issue I have one master and 2 slaves,one is replicating but another slave which is read-only not replicating.

From Master :

show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |                                                                                |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------------------+
| mysql-bin.002490 | 13000479 |              |                  |
+------------------------------------------------------------------------------

Slave : Master has sent all binlog to slave; waiting for more updates

Slave status:

Slave_IO_State: Waiting for master to send event
     Master_Host: ....
              Master_User: repl
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.002490
      Read_Master_Log_Pos: 13106512
           Relay_Log_File: mysql-relay-bin.000018
            Relay_Log_Pos: 13106725
    Relay_Master_Log_File: mysql-bin.002490
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
               Last_Errno: 0
               Last_Error:
             Skip_Counter: 0
      Exec_Master_Log_Pos: 13106512
          Relay_Log_Space: 13107019
          Until_Condition: None
           Until_Log_File:
            Until_Log_Pos: 0
       Master_SSL_Allowed: No
    Seconds_Behind_Master: 0
  Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

slave processlist show below for system user

i. Waiting for master to send event
ii.Slave has read all relay log; waiting for more updates

Still not replicating.what i have to check ?

Any suggestions.

Thanks in advance.

These say that it is replicating:

     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
           Last_Errno: 0
           Last_Error:

Seconds_Behind_Master: 0

Check my.cnf on both the Master and Slave -- look for replicate/binlog_do/ignore_...  Perhaps you are not allowing some databases or tables to go from (do) the Master or be received by (ignore) the Slave.

The various %read_only variables control whether or not you can connect to the server and write; replicated writes are always allowed on the Slave.


Postgres Transaction OOM on 100k DDL statements We execute approximately 100k DDL statements in a single transaction in PostgreSQL. During execution, the respective Postgres connection gradually increases on its memory usage and once it can't acquire more memory (increasing from 10MB to 2.2GB usage on 3GB ram), OOM killer hits it with 9 which results in Postgres being gone to recovery mode.
BEGIN;

CREATE SCHEMA schema_1;
-- create table stmts - 714
-- alter table add pkey stmts - 714
-- alter table add constraint fkey stmts - 34
-- alter table add unique constraint stmts - 2
-- alter table alter column set default stmts - 9161
-- alter table alter column set not null stmts - 2405
-- alter table add check constraint stmts - 4
-- create unique index stmts - 224
-- create index stmts - 213

CREATE SCHEMA schema_2;
-- same ddl statements as schema_1 upto schema_7
-- ...
-- ...
-- ...
CREATE SCHEMA schema_7;

COMMIT

Including the create schema statement, approximately 94304 DDL statements were meant to be executed.
As per Transactional DDL in PostgreSQL

Like several of its commercial competitors, one of the more advanced features of PostgreSQL is its ability to perform transactional DDL via its Write-Ahead Log design. This design supports backing out even large changes to DDL, such as table creation. You can't recover from an add/drop on a database or tablespace, but all other catalog operations are reversible.

We have even imported approximately 35GB of data into PostgreSQL in a single transaction without any problem, but why does the Postgres connection requires huge memory when executing thousands of DDL statements in single transaction?
We can temporarily resolve it by increasing the RAM or allocating swap, but we can say that the number of schema creation in a single transaction can increase up to 50 - 60 (Approx 1M DDL statements) which would require 100+ Gigs of RAM or swap which isn't feasible right now.
PostgreSQL version: 9.6.10
Is there any reason why executing lots of DDL statements requires more memory while dml statements does not? Isn't both handles transactions by writing to underlying WAL? So why, for DLL it's different?
Reason for Single Transaction
We sync the entire database of customers from Customer Premise (SQL Server) to cloud (PostgreSQL). All customers have different no of databases. Process is, entire data will be generated as CSV from SQL Server and import into PostgreSQL using Temp Tables, COPY and ON CONFLICT DO UPDATE. During this process, we treat each customer as a single database in PG and individual DB in customer's SQL Server as schemas in customer's PG DB.
So based on the CSV data, we will create the schemas dynamically and import data into it. As per our application design, the data in PG should be strictly consistent at any point of time and there shouldn't be any partial schema / tables / data. So we had to achieve this in a single transaction. Also we incrementally sync from customer to cloud DB every 3 minutes. So the schema creation can happen either in first sync or incremental sync. But the probability of creating so many schemas in first sync itself is very high.
Update 1
Commenting the ALTER TABLE ALTER COLUMN statements greatly reduced the memory usage as it now takes only 300MB atmost. Have to merge those into the CREATE TABLE statements itself.
Will ask the core problem in PG Hackers mailing list.

A better idea entirely is to use SQL Server FDW which actually has the logic to pull in Microsoft SQL Server into PostgreSQL format (for example, Bit gets mapped to Bool). From this point

Then every three minutes,

you import the foreign schema into last_fetch_schema
if the last_fetch_schema is different from local_schema

you resync schemas

you copy all of the data over with a INSERT INTO ... SELECT ON CONFLICT DO UPDATE, and you can select only the newest data.
you drop the foreign schema last_fetch_schema

What do you gain?

On first load, you can simply use CREATE TABLE local.foo ( LIKE foreign.foo)
You can easily compare meta-data differences
CSVs loses types and leave you to infer things, FDW can read meta-data catalog.
Grabbing only the newest stuff is very simply if the rows are versioned/ you don't have to send the entire database anymore.


Ports necessary for Oracle 11g Replication across a firewall? We have two Oracle 11g Enterprise Edition servers that need to replicate data via Oracle streams.  However, the two servers are on untrusted domains and connect via a VPN.  On end of the connection is a Juniper firewall, with the other side a TMG firewall.

I know the listener runs on port 1521, but what other ports or port ranges should be opened for Oracle replication to work correctly?

I don't think any other ports than the listener ports are required.
I suspect that the ports need to be open on your source AND target database/firewall, allowing both incoming and outgoing connections. Also, the host running RMAN has to be able to reach the listener ports, but that's probably default.

If tnsping works to both servers from the host you are running RMAN from, i think you should be all set.


Plan for managing financial transactions in an easily and retrievable manner, indefinitely I've got a large (many columns) MySQL database table (InnoDB) that has a fairly high number of INSERTs (~500/day). Think of the rows as financial transactions. Clients need to be able to view these rows and/or retrieve a report containing details about the transactions through an online system (going back as far as possible).

My question is this: Is it feasible to allow these rows to accumulate and simply SELECT them from the table when preparing a listing, even accounting for significant growth in volume? The traffic would be relatively low.

We've thought of implementing a clever scheme of separating the table into years; however, that would add significantly to the complexity of the project. Assuming the table was carefully optimized and indexed adequately, would the table still be fast enough at 1,000,000+ rows per year?

I hope this is not too vague or specific to warrant an answer and would like to thank those who take the time to consider responding.

With no growth, you're looking at 1.8 million rows in 10 years. With 100% growth per year, 93 million rows in 10 years. I'd estimate your actual upper bound to be 66 million rows after 10 years.

It's practical to keep that many rows in a transactional table if you partition the table. (You don't have to do that for a while.)

  We've thought of implementing a clever scheme of separating the table into years

Millions of people have thought of this. Don't do it.

Use a single, unpartitioned table until you have a performance problem. Then partition the table. (You have a lot of time between now and then to test and practice.) Finally, after you tune the server for the new workload, consider upgrading hardware. (Banks don't run on shared web servers that cost $2.95 a month.)

Also consider switching now to PostgreSQL. It has a better query optimizer than MySQL; it has more indexing options, including function-based indexes and partial indexes; and in my experience seems to handle 100-million-row tables better than MySQL.


Full text index search returns an unexpected result When I search TaxIdentityNumber using contains an unexpected result is returned; the result contains numbers that are not matched to query. For example, I search using '"5689523657*"' and get 5681254870, 5458976235, etc. as result. I rebuilt the index, but get the same result. What should I try? What did I miss?

The full query:

SELECT *
FROM [Contractors] [contractor]
INNER JOIN [ReportDates] [reportDate]
    ON [contractor].[ReportDateId] = [reportDate].[Id]
LEFT JOIN [Persons] [tempPersons]
    ON [contractor].[PersonId] = [tempPersons].[Id]
LEFT JOIN [Organizations] [tempOrganiztions]
    ON [contractor].[OrganizationId] = [tempOrganiztions].[Id]
LEFT JOIN [Organizations] [tempHolding]
    ON [contractor].[HoldingId] = [tempHolding].[Id]
WHERE
    CONTAINS(([contractor].[TaxIdentityNumber]), '"5689523657*"')

Running on SQL Server 2008 Standard Edition. Query to create full text index:

CREATE FULLTEXT CATALOG SPFTI;
GO
CREATE FULLTEXT INDEX ON [Contractors]
(FullName, TaxIdentityNumber, RelatedOrganizationFullName)
KEY INDEX ContractorPK
ON SPFTI;

This is a known issue when using SQL Server 2008 to search for a number with more than nine digits. When using certain word-breakers, the number is stored in its scientific form e.g. nn5d68952e+009, leading to false-positive matches. One of the word-breakers affected is the neutral language (lcid 0):

-- Result includes nn5d68952e+009
SELECT display_term
FROM sys.dm_fts_parser('"5689523657*"', 0, 0, 0);

A language that does not behave this way is us-english (lcid 1033):

-- Result includes nn5689523657
SELECT *
FROM sys.dm_fts_parser('"5689523657*"', 1033, 0, 0);

There are several other workarounds and fixes mentioned in the KB article link above.


How to REVOKE ALL ON db.table FROM user after GRANT ALL db.* I cannot revoke rights form a user which I gave him before

I gave a GRANT ALL ON [database].* TO 'user'@'%', because I need some user has access to all tables unless one of them. Then I wrote:
REVOKE ALL ON [database].table1 FROM 'user'@'%', but it says that: ERROR 1147 (42000): There is no such grant defined for user 'user' on host '%' on table 'table1';

Alas, I'm pretty sure that it is not directly possible.  REVOKE seems to work only with the identical GRANT.

A messy workaround is to build GRANT statements for all the other tables with the aid if information_schema.tables, then execute them.

Suggest filing a bug at bugs.mysql.com.  (But first, check to see there are already feature requests for it.  Then click the 'me to' flag.)


How to optimize inner join query on one large table and one small table I have table A with 15M records and table B with 5k records. I need to perform an inner join on both but the query time is considerably high.

explain analyze
SELECT distinct(a.student_id), b.student_name, a.class_year
FROM table_a a
INNER JOIN table_b b on a.student_id = b.student_id;

Explain Plan

                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=3628096.85..3779293.37 rows=11780855 width=50) (actual time=35421.004..50690.702 rows=5078 loops=1)
   ->  Sort  (cost=3628096.85..3665895.98 rows=15119652 width=50) (actual time=35421.002..46385.451 rows=14264755 loops=1)
         Sort Key: a.student_id, b.student_name, a.class_year
         Sort Method: external merge  Disk: 890528kB
         ->  Hash Join  (cost=242.20..1308298.78 rows=15119652 width=50) (actual time=3.877..22332.795 rows=14264755 loops=1)
               Hash Cond: ((a.student_id)::text = (b.student_id)::text)
               ->  Seq Scan on table_a a  (cost=0.00..1268336.52 rows=15119652 width=25) (actual time=0.035..6168.042 rows=15119652 loops=1)
               ->  Hash  (cost=174.31..174.31 rows=5431 width=45) (actual time=3.822..3.822 rows=5431 loops=1)
                     Buckets: 8192  Batches: 1  Memory Usage: 483kB
                     ->  Seq Scan on table_b b  (cost=0.00..174.31 rows=5431 width=45) (actual time=0.008..1.886 rows=5431 loops=1)
 Planning time: 2.386 ms
 Execution time: 50822.593 ms
(12 rows)

I have an index on table_a for student_id

"student_id" btree (student_id)

Looks to me like the best plan is being used:

First scan the small table B to make the hash buckets,
then scan the big table A to find the matching rows from B via the hash.

You may have an I/O problem, 15 seconds for 15 million records, although if I assume that each row of table A is about 100 bytes long, then scanning it in 50 seconds is a throughput of about 30 MB/s, which isn't out of the ordinary on normal hardware, and depending on what else is going on.

And then, yes, you spend a lot of time in the DISTINCT sort. If you join and you get multiple matches, but you are not interested in the replicates at all, then it means you don't really need the columns of the joined table, except if your schema isn't normalized.

It seems that that's the clue actually.

SELECT distinct a.student_id, b.student_name, a.class_year
  FROM table_a a
  INNER JOIN table_b b on a.student_id = b.student_id;

The query doesn't seem to be logically thought through. You would get all those different class years, but nothing else from table_A. Look at your final result numbers, it says "rows=5078", which is about the size of table_B. You probably should normalize all these repeating class_years out of this massive table_A.


What to do about upgrading "main" cluster from 8.4 to 9.1 when a 9.1 "main" already exists? I have an existing 8.4 cluster (named main) that I want to upgrade to 9.1.  However, when I go to upgrade it, I get an error because 9.1 already has a cluster named main (apparently a default cluster that is created when I did a sudo apt-get install postgresql-9.1-postgis):
$ pg_upgradecluster 8.4 main
Error: target cluster 9.1/main already exists

What should I do?
Remove the default 9.1/main cluster?  How?
Rename the 9.1/main cluster?  How?
Is there anything special about the Postgresql default main cluster?
Ubuntu 11.10 Oneiric, Postgresql 8.4.8 and 9.1.4
Update: I just found this AskUbuntu question-and-answer that says to first do a pg_dropcluster --stop 9.1 main  I tried that and it worked.  So now I'm wondering if there's any data, configuration settings, etc. in the default 9.1 main cluster that I might be losing by dropping it?

If the existing main is the default cluster that was created with your installation you can just drop it. Use the tool provided by your installation:
pg_dropcluster --stop 9.1 main

More info: man pg_dropcluster
There is nothing special about the main cluster. It's just a cluster like any other which happens to be the default in your installation.
If you haven't worked with the cluster yet, there is nothing in there, that you would lose. But check with psql or pgAdmin (or a client of your choosing) to make sure.


How can I run multiple concurrent versions of my application against the same database? Background: I have multiple concurrent versions of my application running in my production environment. Depending on the user account that's used, the user will have access to a different version of the software.

Environment: Currently SQL Server 2005, imminently migrating to SQL Server 2008, ASP.Net

Problem: Each version of the software may or may not use different versions of the stored procedures that interact with the data in the database. Currently when a version is changed, whoever changes it creates a new copy and appends an incremental version number to the end. At this point we have numerous versions of some stored procs and only one version of others and nobody is sure which version of the application is pointing to which version of the stored procs - it's a mess.

I'm looking for a solution that will neatly package the stored procs up for any given version of the application to be used as the basis for a new version. This means that the new version of the application can be pointed to the new set of procs which can be rewritten or modified to the end user's content without affecting other versions of the applications that are currently available in production.

I originally thought about schemas, but one part of the issue is that the procs are heavily coupled with other procs and user defined functions, so when copying to another schema, we'd new to map and replace all these links which isn't ideal. Is there a way to write the procs so that moving them to another schema would bring the relationship between them along for the ride so that for instance if Schema1.Proc1 referenced Schema1.Proc2 and we copied them to Schema2 that Schema2.Proc1 would now reference Schema2.Proc2 without having to do any additional work?  How would this handle user defined functions where you have to reference the function by SchemaX.Func1()?  It would be handy if I could refer to the function without defining the schema the function belonged to in the same way I can with a stored procedure, but I don't think that's possible.

It seems like this is a problem that should already be solved, but I don't know what I'm looking for in order to find a viable solution.

Does anyone have any ideas?

Interesting mess. Multiple options if you're looking for model refactoring (recommended). If it's not the case/there are time constraints you can try to exploit EXEC. There's neat feature that is not very popular (maybe it's good). Core concept:

DECLARE @cmd NVARCHAR(4000)
SET @cmd = N'dbo.YourProcedure_v11'

DECLARE @retcode INT
EXEC @retcode = @cmd @Param1 = 1,
                     @Param2 = 2,
                     @ParamN = 'N'

You have to create stub procedure ('master' accessed from DAL) and call your procedures depending on user e.g.:

CREATE PROCEDURE dbo.YourProcedure
(
    @Param1 INT,
    @Param2 INT,
    @ParamN CHAR(1),
    @UserID INT
)
AS
BEGIN

    SET NOCOUNT ON
    DECLARE @cmd NVARCHAR(4000)

    IF @UserID = 1
        SET @cmd = N'dbo.YourProcedure_v11'
    ELSE
        SET @cmd = N'dbo.YourProcedure_v12'

    DECLARE @retcode INT
    EXEC @retcode = @cmd @Param1 = @Param1,
                    @Param2 = @Param2,
                    @ParamN = @ParamN
    RETURN @retcode

END

Instead of passing UserID every time/to all procedures - you can use suser_sname etc depending on your security model. Also user-version mappings should be somewhere in database - stored procedure names are hardcoded only for testing.


Oracle RAC VIP issue I have a two node RAC setup where my scan ips are registered in DNS and my public Ips are also registered in the dns. Now while connecting from my windows machine through oracle client in sql developer im getting connection time out error and network adapter error.
My question is that should I register my VIP also in dns and will that resolve this issue?

As far as I remember when I investigated RAC you need to add the VIP addresses to the DNS.


Several Foreign Key and Cascade Delete SQL Server In SQL Server 2014, I'm trying to add CASCADE DELETING (I want to set field to null actually, but is the same ) on 3 FK. If I add a Cascade Delete in one relationship, it works fine. If I add more Cascade Deletes, it doesn't work (Cycle detected error message).

In the above diagram, you can see the Users table, and a Tasks table ("Tareas" in spanish). So, what I need to acomplish is when the user is deleted, I need to set the marked field in Tasks to NULL.

This is something common in a database, so I thought there is a way to handle this.

In my case, most of my tables have a pair of fields holding the UserId of the user that Created or Modified the record. So, I need to solve this pattern to apply it several places.

You can create a trigger to update the UserID columns in related tables, such as:

USE Tempdb;

CREATE TABLE dbo.Users
(
    UserID INT NOT NULL CONSTRAINT PK_Users
        PRIMARY KEY CLUSTERED
    , ApplicationID INT NOT NULL
    , Country VARCHAR(255) NOT NULL
    , DefaultTenantID INT NOT NULL
);
GO
CREATE TABLE dbo.Tasks
(
    TaskID INT NOT NULL
    , TenantID INT NOT NULL
    , CreatedBy_UserID INT NULL
        CONSTRAINT FK_Tasks_CreatedBy
        FOREIGN KEY REFERENCES dbo.Users(UserID)
    , ModifiedBy_UserID INT NULL
        CONSTRAINT FK_Tasks_ModifiedBy
        FOREIGN KEY REFERENCES dbo.Users(UserID)
    , AssignedTo_UserID INT NULL
        CONSTRAINT FK_Tasks_AssignedTo
        FOREIGN KEY REFERENCES dbo.Users(UserID)
);
GO
INSERT INTO dbo.Users (UserID, ApplicationID, Country, DefaultTenantID)
VALUES (1, 1, 'CA', 1);

INSERT INTO dbo.Tasks (TaskID, TenantID, CreatedBy_UserID, ModifiedBy_UserID, AssignedTo_UserID)
VALUES (1, 1, 1, NULL, NULL);
GO

CREATE TRIGGER TG_Users_Trigger
ON dbo.Users
INSTEAD OF DELETE
AS
BEGIN
    UPDATE dbo.Tasks
    SET CreatedBy_UserID = NULL
    WHERE EXISTS (
        SELECT 1
        FROM deleted d
        WHERE d.UserID = CreatedBy_UserID
        );
    UPDATE dbo.Tasks
    SET ModifiedBy_UserID = NULL
    WHERE EXISTS (
        SELECT 1
        FROM deleted d
        WHERE d.UserID = ModifiedBy_UserID
        );
    UPDATE dbo.Tasks
    SET AssignedTo_UserID = NULL
    WHERE EXISTS (
        SELECT 1
        FROM deleted d
        WHERE d.UserID = AssignedTo_UserID
        );
    DELETE
    FROM dbo.Users
    WHERE EXISTS (
        SELECT 1
        FROM deleted d
        WHERE d.UserID = UserID
        );
END
GO

DELETE
FROM dbo.Users;

SELECT *
FROM dbo.Users;
SELECT *
FROM dbo.Tasks;

/* - cleanup removed for safety
DROP TRIGGER TG_Users_Trigger;
DROP TABLE dbo.Tasks;
DROP TABLE dbo.Users;
*/

However, instead of deleting rows from the Users table, I would recommend updating a bit column to indicate the User has been deleted.  This allows a history of users, along with the actions taken by those users in the Tasks table.  For instance:

CREATE TABLE dbo.Users
(
    UserID INT NOT NULL CONSTRAINT PK_Users
        PRIMARY KEY CLUSTERED
    , ApplicationID INT NOT NULL
    , Country VARCHAR(255) NOT NULL
    , DefaultTenantID INT NOT NULL
    , IsDeleted BIT NOT NULL /* Default this to false */
        CONSTRAINT DF_Users_IsDeleted DEFAULT ((0))
);

CREATE TABLE dbo.Tasks
(
    TaskID INT NOT NULL
    , TenantID INT NOT NULL
    , CreatedBy_UserID INT NULL
        CONSTRAINT FK_Tasks_CreatedBy
        FOREIGN KEY REFERENCES dbo.Users(UserID)
    , ModifiedBy_UserID INT NULL
        CONSTRAINT FK_Tasks_ModifiedBy
        FOREIGN KEY REFERENCES dbo.Users(UserID)
    , AssignedTo_UserID INT NULL
        CONSTRAINT FK_Tasks_AssignedTo
        FOREIGN KEY REFERENCES dbo.Users(UserID)
);

/* Note we are not specifying the state of the IsDeleted column here
    since it defaults to zero, which indicates they are active
*/
INSERT INTO dbo.Users (UserID, ApplicationID, Country, DefaultTenantID)
VALUES (1, 1, 'CA', 1);

INSERT INTO dbo.Tasks (TaskID, TenantID, CreatedBy_UserID, ModifiedBy_UserID, AssignedTo_UserID)
VALUES (1, 1, 1, NULL, NULL);

/*  This is how we "delete" a user */
UPDATE dbo.Users
SET IsDeleted = 1
WHERE UserID = 1;

/* Any queries that access the Tasks table now need to be cognizant
    of the state of the user (deleted or not)
*/
SELECT T.*
FROM dbo.Tasks T
    INNER JOIN dbo.Users U ON T.CreatedBy_UserID = U.UserID
WHERE U.IsDeleted = 0;


Can we have OLAP cubes with same OBJECT NAME and ID at different database? I have olap database for both production and test. Fortunately I noticed that cube under both production and test olap database has the same object name and Id. But DataSource for production and test olap database pointed towards different database connections.

My question is, having same object name and Id for the cube under different olap database is a problem or if I process the cube under test, does that overwrites the production cube?

Every object has a Name and ID property that must be unique within the scope of the parent collection. For example, two dimensions can have same name as long as each one resides in a different database.

A database is a scope. A cube is an object. Two cubes can have same name or ID as long as they belong to different databases.

Although you can specify it manually, the IDis typically auto-generated when the object is created. You should never change the ID once you have begun building a model. All object references throughout a model are based on the ID. Thus, changing an ID can easily result in model corruption.

DataSource and DataSourceView objects have notable exceptions to naming conventions. DataSource ID can be set to a single dot (.), which is not unique, as a reference to the current database. A second exception is DataSourceView, which adheres to the naming conventions defined forDataSet objects in the .NET Framework, where the Name is used as the identifier.

Naming Conventions