Decrease Data IO operation on query to lower the DTU cost on Azure We have a FunctionApp in Azure that triggers on a timer and does some DB operations every 5 minutes. These operation are taking a very long time to complete. When I run the same query on my computer with the same data in the DB the query executes in about 2 seconds. On Azure it took almost almost 100 seconds.
We saw in the metrics on Azure that the Data IO limit hit the roof with 100% DTU. So we increased the DTU limit and the query now runs in about 48 seconds, but still hits the DTU limit for Data IO.
It's easy to say, just increase the DTU limit, but the customer isn't soo keen on paying more every month.
Soo, Is there something I can do with the below query to limit the amount of Data IO it does? Can I do it in another way? Split the query or something?
WITH CombinedTables AS
(SELECT SourceTable,a, b, c, d, e, f, g,
ROW_NUMBER() OVER (PARTITION BY a, b, c, d, e, f, g ORDER BY a, b, c, d, e, f, g) Row_Nbr
FROM (SELECT '2' AS SourceTable, a, b, c, d, e, f, g FROM [abc].[ExternalView]
             UNION ALL
             SELECT '1' AS SourceTable, a, b, c, d, e, f, g FROM [LocalTable] WITH (NOLOCK)
) AS CombinedTables GROUP BY a, b, c, d, e, f, g, SourceTable
) SELECT DISTINCT * FROM CombinedTables WHERE CombinedTables.Row_Nbr < 2 AND CombinedTables.SourceTable = '2'

Or is the solution to limit the number of rows we operate with, so that the amount of row that needs to be processed will be lower? there are about 84k rows in the table now, and it increases some every month.
edit: Added execution plan https://prnt.sc/uuys04

I see a table scan and no use of any indexes in the execution plan. Create indexes to support your query and consider creating one to eliminate the sort. Apply page compression to your tables and any nonclustered indexes. Make sure you are updating stats and indexes - this maintenance is still required in Azure.


Enable TRY style error handling Is there a SET command or similar I can enable to get the same error handling as a try/catch block? Specifically halting the script/procedure on the first error.

The only way I've currently found so far is to wrap the whole query in a try/catch such as

BEGIN TRY
    -- procedure body goes here
END TRY
BEGIN CATCH
    THROW;
END CATCH

While this hack does work to enable the correct error handling behaviour it just feels wrong to have a catch block that just re-throws the error.

In this particular case there's nothing more I need to do with the error other than halt the script and throw the error back out to the script runner.

In my opinion you should use TRY/CATCH every time when you have update, insert or delete statements. This has saved me a lot of headaches. Check this -- 1


join syntax / style performance consideration We recently found on one of our stored procedures that we get significant performance improvement by changing the join syntax/style of our query from this...

SELECT b.bla, c.foo, d.bar
FROM dbo.TableB b
JOIN dbo.TableC c
    JOIN dbo.TableD d  -- <-- Nested join syntax
    ON d.yyy = c.yyy
ON c.xxx = b.xxx

To this...

SELECT b.bla, c.foo, d.bar
FROM dbo.TableB b
JOIN dbo.TableC c
ON c.xxx = b.xxx
JOIN dbo.TableD d   -- <-- Regular way
ON d.yyy = c.yyy

Note: in the real query, there were 10 joined tables including inner and outer joins. The tables were not huge in terms of sql data. No aggregates. There was a DISTINCT on the output. All joins were to a primary key, but the foreign key was not necessarily indexed.

We will certainly change our ways, but I'm still curious as to the proper 'guidance' on such style. I've often used the 'indented' style to indicate a 'more readable' join for such things as lookup tables.

In a world where the query optimizer considered all possible join orders, and contained all possible logical transformations, the syntax we use for our queries would not matter at all.

As it is, the optimizer generally uses heuristics to pick an initial join order and explores a number of join order rewrites from there. It does this to avoid excessive compilation time and resource usage. It doesn't take all that many joins for the number of possible combinations to become unreasonable to explore exhaustively.

To take an extreme example, 42 joins are enough to generate more alternatives than there are atoms in the observable universe. More realistically, even 7 tables are enough to produce 665,280 alternatives. Although this is not a mind-boggling number, it would still take very significant time (and memory) to explore those alternatives completely.

Although the heuristics are largely based on the type of join (inner, outer, cross...) and cardinality estimates, the textual order of the query can also have an impact. Sometimes, this is an optimizer limitation - NOT EXISTS clauses are not reordered, and outer join reordering is very limited. Even with simple inner joins, the interaction between textual order, initial join order heuristics, and optimizer internals can be difficult to predict with certainty.

To take an example using the AdventureWorks sample database, I can write a query using the a common syntax form as:

SELECT
    P.Name,
    PS.Name,
    SUM(TH.Quantity),
    SUM(INV.Quantity)
FROM Production.Product AS P
JOIN Production.ProductSubcategory AS PS
    ON PS.ProductSubcategoryID = P.ProductSubcategoryID
JOIN Production.TransactionHistory AS TH
    ON TH.ProductID = P.ProductID
JOIN Production.ProductInventory AS INV
    ON INV.ProductID = P.ProductID
GROUP BY
    P.ProductID,
    P.Name,
    PS.ProductSubcategoryID,
    PS.Name;

Before cost-based optimization, the logical query tree looks like this (note the join order is not the same as the written order):

I can (carefully) rewrite the query to use 'nested' syntax:

SELECT
    P.Name,
    PS.Name,
    SUM(TH.Quantity),
    SUM(INV.Quantity)
FROM Production.ProductSubcategory AS PS
JOIN Production.Product AS P
JOIN Production.TransactionHistory AS TH
JOIN Production.ProductInventory AS INV
    ON INV.ProductID = TH.ProductID
    ON TH.ProductID = P.ProductID
    ON P.ProductSubcategoryID = PS.ProductSubcategoryID
GROUP BY
    P.ProductID,
    P.Name,
    PS.ProductSubcategoryID,
    PS.Name;

In which case the logical tree at the same point is:

The two different syntaxes produce a different initial join order in this case. After cost-based optimization, both produce the same output plan shape:

There are detailed differences between the two plans, with the 'nested' syntax producing a plan with a somewhat lower estimated cost:

The two inputs took a slightly different path through the optimizer, so it isn't all that surprising there are slight differences.

In general, using different syntax will sometimes (definitely not always!) produce different plan results. There is no broad correlation between one syntax and better plans. Most people write and maintain queries using something like the non-nested join syntax, so it often makes practical sense to use that.

To summarize, my advice is to write queries using whichever syntax seems most natural (and maintainable!) to you and your peers. If you get a better plan for a specific query using a particular syntax, by all means use it - but be sure to test that you still get the better plan whenever you patch or upgrade SQL Server :)


Oracle Query by Quarter I have a query that I want to get data from the past 5 quarters, but it doesn't work the way it should:

select to_char(eventdate,'YYYY') || ' Q-' || to_char(eventdate, ' Q'), ...
from ...
where eventdate between (sysdate - 458) and sysdate

It doesn't do what I want. How do I make sure I get all rows for the current quarter and the last 4, no matter what day of the quarter it is?

testbed:

create table foo as
select add_months(sysdate, -(level-1)*3) as eventdate from dual connect by level<10;

select * from foo;
/*
EVENTDATE
-------------------------
30-DEC-11 16.09.20
30-SEP-11 16.09.20
30-JUN-11 16.09.20
30-MAR-11 16.09.20
30-DEC-10 16.09.20
30-SEP-10 16.09.20
30-JUN-10 16.09.20
30-MAR-10 16.09.20
30-DEC-09 16.09.20
*/

query:

select to_char(eventdate,'YYYY "Q"- Q') -- "Q" is treated as a literal due to the quotes
from foo
where eventdate>=add_months(trunc(sysdate, 'Q'), -12); -- trunc() gets first day of the Q
                                                       -- then add_months takes us back 4 Qs
/*
TO_CHAR(EVENTDATE,'YYYY"Q"-Q')
------------------------------
2011 Q- 4
2011 Q- 3
2011 Q- 2
2011 Q- 1
2010 Q- 4
*/


Fill in missing dates with data value from previous populated date for group Picture help desk tickets that gets transfered between departments. We want to know what the department is at the end of the day for each ticket for each day that the ticket is open. The table contains the last department for each ticket for each day it is open on which there is a change in the department (including a row for the date the ticket was initially opened and the date it was closed). The data table looks like this:

CREATE TABLE TicketAssigment (
    TicketId     INT NOT NULL,
    AssignedDate DATE NOT NULL,
    DepartmentId INT NOT NULL);

What I need is to fill in any missing dates for each TicketId, using the DepartmentId from the previous TicketAssigment row ordered by Date.

If I have TicketAssigment rows like this:

1, '1/1/2016', 123 -- Opened
1, '1,4,2016', 456 -- Transferred and closed
2, '1/1/2016', 25  -- Opened
2, '1/2/2016', 52  -- Transferred
2, '1/4/2016', 25  -- Transferred and closed

I want this output:

1, '1/1/2016', 123
1, '1/2/2016', 123
1, '1/3/2016', 123
1, '1/4/2016', 456
2, '1/1/2016', 25
2, '1/2/2016', 52
2, '1/3/2016', 52
2, '1/4/2016', 25

This looks like it might be close to what I need, but I haven't had the patience to let it finish, and the estimated plan cost has 6 digits:

SELECT  l.TicketId, c.Date, MIN(l.DepartmentId)
FROM    dbo.Calendar c
        OUTER APPLY (SELECT TOP 1 TicketId, DepartmentId FROM TicketAssigment WHERE AssignedDate <= c.Date ORDER BY AssignedDate DESC) l
WHERE   c.Date <= (SELECT MAX(AssignedDate) FROM TicketAssigment)
GROUP   BY l.TicketId, c.Date
ORDER   BY l.TicketId, c.Date;

I suspect there is a way to do this using LAG and a window frame, but I haven't quite figured it out. What is a more efficient way of meeting the requirement?

Use LEAD() to get the next row within the TicketId partition. Then join to a Calendar table to get all the dates between.

WITH TAwithnext AS
(SELECT *, LEAD(AssignmentDate) OVER (PARTITION BY TicketID ORDER BY AssignmentDate) AS NextAssignmentDate
 FROM TicketAssignment
)
SELECT t.TicketID, c.Date, t.DepartmentID
FROM dbo.Calendar c
JOIN TAwithnext t
    ON c.Date BETWEEN t.AssignmentDate AND ISNULL(DATEADD(day,-1,t.NextAssignmentDate),t.AssignmentDate)
;

All kinds of ways to get a Calendar table...


Concurrency when simulating autoincrement with triggers on MySQL I am updating an old application to implement virtual tenancy, which involves adding a tenant_id column to the primary key of every single table. The application uses auto-incrementing columns heavily, which presents a problem with InnoDB, since InnoDB doesn't allow auto-incrementing columns in multi-partite primary keys.

The standard solution to this problem seems to be "use triggers": e.g., https://stackoverflow.com/questions/18120088/defining-composite-key-with-auto-increment-in-mysql. At this point, I cannot find the reference I modeled my original solution from.

I know only enough about databases to know that I am in huge danger of shooting myself in the foot here. In particular, I am very concerned about concurrency, both in terms of making sure we don't start generating duplicate primary keys by accident (correctness), but also in terms of making sure that we don't start generating all sorts of locks that would slow down the application.

So my question has two parts:

Given the scheme below, how can I be sure that the code that selects the next value in a sequence for a table and tenant_id is atomic? Is it possible for two instances of the trigger to search for the same row at the same time?
How will this sequencing scheme behave during transactions? Will the sequence be rolled back if the transaction is rolled back? (We don't necessarily want that.) Will the row containing the sequence be locked for the entirety of the transaction? If so, is there some way to exclude the sequence table from the transaction?

Note that I am entirely willing to accept that this scheme is a terrible idea. We have even thought about doing the sequence generation at the application level. But before we commit to that kind of work, I wanted to figure out whether our current approach is useable.

The sequence table is defined as possible:

CREATE TABLE IF NOT EXISTS `MyApplicationSequence` (
    `table_name` VARCHAR(50) DEFAULT NULL,
    `tenant_id` SMALLINT NOT NULL,
    `sequence` INT NOT NULL,
    PRIMARY KEY (`tenant_id`, `table_name`)
);

And for each table that requires it, we create a trigger like the following:

CREATE TRIGGER `SEQ_MyApplicationTable1`
BEFORE INSERT ON `MyApplicationTable1` FOR EACH ROW
BEGIN
    UPDATE
        `MyApplicationSequence`
    SET
        `sequence` = `sequence` + 1,
        NEW.`original_primary_key` = `sequence`
    WHERE
      `table_name` = 'MyApplicationTable1' AND
       `tenant_id` = NEW.`tenant_id`
    LIMIT 1;

    -- If we there was no sequence already, start a new one at 1
    IF ROW_COUNT() = 0 THEN
        INSERT INTO `MyApplicationSequence` (`table_name`, `tenant_id`, `sequence`)
        VALUES ('MyApplicationTable1', NEW.`tenant_id`, 1);
        SET NEW.`original_primary_key` = 1;
    END IF;
END;

You can avoid the whole problem by using this pattern.  Old tables had

id MEDIUMINT UNSIGNED AUTO_INCREMENT,
PRIMARY KEY(id)

New table has

tenant_id SMALINT UNSIGNED,
id INT UNSIGNED AUTO_INCREMENT,
PRIMARY KEY(tenant_id, id),
INDEX(id)

Notes:

INDEX(id) is sufficient to handle AUTO_INCREMENT.
The ids for a given tenant will have a lot of gaps and will grow faster than before.  So you may need a larger datatype.
Nothing prevents you from inserting a duplicate id, but that would not happen in ordinary programming.


PostgreSQL schemas and search_path I'm reading Oreilly's PostgreSQL: Up and Running, and in the section Organizing Your Database Using Schemas it says the search_path may be set at the database level:

  For example, if we wanted all objects in contrib to be accessible without schema qualification, we would change our database as follows:

  ALTER DATABASE mydb SET search_path="$user",public,contrib;

However, after typing the command above, I still can't refer to tables in schemas other then public without qualifying them:

ALTER DATABASE auth SET search_path="$user",public,staging;`
\d users

Gives:

Did not find any relation named "users".

But if I use \d staging.users, it works.

SHOW search_path;

Gives:

   search_path
----------------
 "$user",public

Does the default search_path from postgresql.conf override the per-database value set with ALTER DATABASE ... SET search_path ? If that's the case, what's the use of the per-database value?

The citation from the SET related paragraph of the ALTER DATABASE documentation section is "Whenever a new session is subsequently started in that database, the specified value becomes the session default value". So the changes will take effect for new sessions only. You just need to reconnect.


Cannot connect to SQL Server database regardless of connection string I'm having a tough time to connect to a local SQL Server instance within a C# app.  I can connect to external databases on other servers, but I need to be able to use my own local database.

The exception I get is:

  {"Login failed for user 'ASTROAdmin'."}

There isn't really any more info within the exception, so I don't know if the account doesn't exist, if the password is bad, if the database doesn't exist, etc.  The connection string I'm using is:

  connectionString="Server=MIKE-LAPTOP;Initial
  Catalog=ASTRO_PROD;Integrated Security=False;User
  ID=ASTROAdmin;Password=password;Persist Security Info=True;Type System
  Version=SQL Server 2008;Application Name=OpsMgr;"

I've also ran the following on the server:

ALTER LOGIN [ASTROAdmin] WITH PASSWORD=N'password'

So the password should be fine.

I've been trying different values (such as . and localhost) for Server and also using Data Source= instead, but I get the same exception no matter what.  My theory is that there are multiple instances of SQL Server on this machine, like perhaps SQL Server Express.

Also, if I run the SQL Profiler tool, I don't see anything come up as it tries to connect.  No data what-so-ever.

What's the best way to continue to diagnose this problem?  Thanks!

If you have access to SQL Server Management Studio, then attempt a logon to MIKE-LAPTOP with your provided credentials.  Can you connect?  Any errors thrown?  That will eliminate the possibility of password/permissions.

You mentioned that there could be multiple SQL instances.  Depending on configurations, you may need to specify the instance name and/or port number.

Such as:

MIKE-LAPTOP\sql2008
MIKE-LAPTOP\sql2008,1433


Statistical Database Optimization My friends and I are building a database for statistical research.
So we have a user that answers 10 questions in a row.
The questions are face-images, one is mirrored and the other one is the original, and the user should answer what the original is. From the images we know, where it lies (URL) the id and the sex.

He can participate as many times as he wants, so we need to save all his answered questions.

We want to save how many things he got right, how many he got wrong, and see if it is easier for the user to differentiate between women or men faces.

We want to store as many information as possible, without a lot being redundant.

Here is our current ER -Modell and we are not really sure if it is correct.

I would appreciate any help

Consider having a row in the userstat table for each attempt that the user makes at the test. This means, then, that you would have to make the composite primary key of the table to be users_userid and attemptid (which can be an identity column).

I would maybe think that the questions table should be a many-to-many relationship between userstat and questions rather than between users and questions, assuming that each time a user attempts the test they may get different questions. Either way the primary key on this table needs to include more than the single foreign key, it should include both foreign keys.


Is it possible to have a loop in SQL without creating a stored procedure? I would like to loop through the vertices in a SHAPE/GEOMETRY column in a user-defined data type (ESRI's version of ST_GEOMETRY). I've posted the details in a GIS SE post here: https://gis.stackexchange.com/questions/209894/update-sde-st-geometry-m-values-to-cumulative-distance-using-oracle-sql

I believe this would normally be done in a stored procedure (probably in conjunction with the sde.ST_GEOMETRY functions ST_NumPoints and ST_PointN) . However, I'm merely a data analyst, not a DBA, and do not have CREATE PROCEDURE permissions, so I can't make a stored procedure. Additionally, in my company/organization, a request for a procedure to be made would either be rejected or take years to be implemented.

So I'm wondering, as an alternative to creating a stored procedure, if I could loop through the vertices within an SQL update statement instead?

Oracle 12c (12.1.0.2.0)

You can put a pl/sql in an anonymous block. Something like this might help:

declare
  v_vertices  your_user_defined_data_type;

  cursor your_cursor_c
  is
    select vertices
    from   your_table
    where  your_column = 'A VALUE';

begin
    open your_cursor_c;
    loop
      fetch your_cursor_c
      into  v_vertices;
      exit when your_cursor_c%notfound;

      ST_NumPoints(v_vertices);
      ST_PointN(v_vertices);

      commit;
    end loop;
end;

Also with 12c there is some inline procedure/function stuff that you can do. Not sure if it is necessary but here is a link: https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1.


Add a DL as a login in azure sql managed instace I am able to add individual AD logins as a login in azure sql managed instance, when i try to add a DL is the same way i am getting the following error.

error: Principal '' could not be found or this principal type is not supported.

Query user: CREATE LOGIN [] FROM EXTERNAL PROVIDER
GO

Want to add a DL as login so that all individuals withing the DL can access the instance, isn't  this not possible?
What am I doing wrong here?

To create a DL (assuming it is a group in AAD), you need to create without the @domain.. on the database that you want to connect.. example:

CREATE USER [GroupName] FROM EXTERNAL PROVIDER

Here you have a link

https://techcommunity.microsoft.com/t5/azure-database-support-blog/lesson-learned-23-how-to-add-aad-security-groups-to-azure-sql/ba-p/368866


Is there any time where we should avoid cascading delete? In SQL Server 2008, there is a Primary table which is linked to three other child tables by 1 to many relationship. So, we are thinking of using Cascading delete in the primary table, so that all the records on the child table will be removed when record from primary table is deleted.

So, is cascading delete a correct choice here?
When cascading detele should not be used?

I am generally wary of cascaded deletes (and other automatic actions that could drop/damage data), either via triggers or ON <something> CASCADE. Such facilities are very powerful, but also potentially dangerous.

  So, is cascading delete a correct choice here?

It would certainly do what you are looking for it to do: remove related records when a parent record is removed, without you needing to implement any other logic to ensure that children get removed first therefore making your code more concise. All the actions will be wrapped in an implicit transaction so if something blocks the child deletes the whole operation is blocked, maintaining referential integrity with little or no extra coding effort.

Make sure that your use of cascaded deletes and other "behind the scenes" actions are well documented so future maintainers of the system are fully aware of it.

  When cascading detele should not be used?

It should not be used if you are paranoid like me! One key point to consider is the other developers who currently, or may in future, work on your code/database (hence the comment above about documenting any "hidden" behaviours).

It is quite common in my experience for inexperienced people to use DELETE then re-INSERT in order to update rows, especially when what they really want is a MERGE/UPSERT operation (update existing rows and create new ones where a row with a given key does not exist) and the DBMS doesn't support merge/upsert (or they are unaware of its support). Without cascaded actions this is perfectly safe (or will error when it threatens data integrity) but if someone does this for rows in a parent table where referring FKs have ON DELETE CASCADE set then related data will be deleted as a result of the initial delete and not replaced - so data is lost (not that even if the delete and subsequent insert are wrapped in explicit transactions, the cascade happens with the delete operation - it won't wait to see if the transaction replaces rows in the parent table in subsequent statements) and the cascade could continue through other relation ships (for instance: delete a senior supervisor, his team get deleted by cascade, his teams' teams get deleted by cascade, all the tracked records for all those people get deleted by cascade, ...). Without cascading enabled, you would just get an error here instead of the data being silently lost.


Create A Cursor with loop conditon I am try to make a cursor with loop condition.
purpose of cursor:- I want to execute a procedure 17times .

Procedure definition:

dbo.ALLAARTI_MIS_SUMMARY @IN_SYSDIFF = @IN_DIFF

DECLARE @IN_DIFF int
DECLARE vendor_cursor CURSOR FOR
SELECT date_time FROM dumy_mis
OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor
INTO @IN_DIFF

WHILE @@FETCH_STATUS = 0
BEGIN
exec dbo.ALLAARTI_MIS_SUMMARY @IN_SYSDIFF = @IN_DIFF
END
CLOSE vendor_cursor
DEALLOCATE vendor_cursor

I'm not sure what are you trying to achieve with this script, but you should fetch next records inside WHILE block just to be sure that @@FETCH_STATUS=0

dbo.ALLAARTI_MIS_SUMMARY @IN_SYSDIFF = @IN_DIFF

DECLARE @IN_DIFF int
DECLARE vendor_cursor CURSOR FOR
SELECT date_time FROM dumy_mis
OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor
INTO @IN_DIFF

WHILE @@FETCH_STATUS = 0
BEGIN
    exec dbo.ALLAARTI_MIS_SUMMARY @IN_SYSDIFF = @IN_DIFF

    FETCH NEXT FROM vendor_cursor
    INTO @IN_DIFF
END
CLOSE vendor_cursor
DEALLOCATE vendor_cursor


Convert Oracle query to MySQL I've just tried to convert this update command, which is Oracle sql, to MySQL with SQLWays and it failed with the following error:

  You can't specify target table 'animal_table' for update in FROM clause

source command (Oracle):

UPDATE animal_table
     SET (animal_info1,
          animal_info2,
          animal_info3) = (SELECT animal_nbr, animal_amount, '00'
                           FROM animal_table t2
                           WHERE t2.category_id = animal_table.category_id
                             AND t2.sumary_id = animal_table.summary_id
                             AND t2.animal_type = 'special'
                          )
  WHERE animal_type = 'cats'
    AND category_id = 'foo';

suggested target command (MySQL):

UPDATE animal_table
SET
    animal_info1 = (SELECT animal_nbr FROM animal_table t2
                    WHERE t2.category_id = animal_table.category_id
                    AND t2.sumary_id = animal_table.summary_id
                    AND t2.animal_type = 'special'),
    animal_info2 = (SELECT animal_amount FROM animal_table t2
                    WHERE t2.category_id = animal_table.category_id
                    AND t2.sumary_id = animal_table.summary_id
                    AND t2.animal_type = 'special'),
    animal_info3 = (SELECT '00' FROM animal_table t2
                    WHERE t2.category_id = animal_table.category_id
                    AND t2.sumary_id = animal_table.summary_id
                    AND t2.animal_type = 'special')
    WHERE animal_type = 'cats'
    AND category_id = 'foo';

can anyone help me out here?

Try using an UPDATE JOIN of the table against itself

UPDATE
    animal_table A
INNER JOIN
(
    SELECT
        category_id,summary_id,
        animal_nbr,animal_amount,'00' info3
    FROM animal_table
    WHERE animal_type = 'special'
) B USING (category_id,summary_id)
SET
    A.animal_info1 = B.animal_nbr,
    A.animal_info2 = B.animal_amount,
    A.animal_info3 = B.info3
WHERE
    A.animal_type = 'cats' AND
    A.category_id = 'foo';

Make sure that animal_table has a compound index on category_id and summary_id. If you do not have such an index, please run this:

ALTER TABLE animal_table ADD INDEX (category_id,summary_id);

UPDATE 2011-09-27 13:10 EDT

I just noticed that categrory_id 'foo' limits the dataset. Here is an updated refactoring of my answer ( I moved category_id = foo into the subquery )

UPDATE
    animal_table A
INNER JOIN
(
    SELECT
        category_id,summary_id,
        animal_nbr,animal_amount,'00' info3
    FROM animal_table
    WHERE animal_type = 'special'
    AND category_id = 'foo'
) B USING (category_id,summary_id)
SET
    A.animal_info1 = B.animal_nbr,
    A.animal_info2 = B.animal_amount,
    A.animal_info3 = B.info3
WHERE
    A.animal_type = 'cats';

I also recommend adding this additional index to accommodate the subquery

ALTER TABLE animal_table ADD INDEX (animal_type,category_id);


Error running Powershell Script in SQL Server Agent I have a SQL job that runs via SQL Server Agent (Running SQL Server 2012 Enterprise). The last step of the job is to run an application located on a network share. Unfortunately I don't know the name of the folder that the application is in (the folder is the version number), so I'm using PowerShell to find it:

set-location "\\server\companydocuments\MyApp\Application Files\"
$name = Get-ChildItem | sort name -desc | select -f 1 | select name
cd $name.name
& ".\Application.exe"

If I open a PowerShell window on the SQL Server then it works fine. When I run this in SQL Server Agent I get the following error:

A job step received an error at line 1 in a PowerShell script.
The corresponding line is 'set-location "\\server\companydocuments\MyApp\Application Files\'"

I thought it might be a permissions issue so I've tried SQL Server Agent running under my credentials (it's a test server, not production) but I still get the same error. I've also tried mapping a network drive to the shared folder instead of UNC paths, but the same error.

Can anyone suggest how I can connect to this folder?

I believe your issue is going to be the SQLPS provider. Since PowerShell steps in SQL Server Agent automatically put you into the context of that provider some commands that work in your normal console will not function the same way. A write up was done here with Set-Location. You basically have to tell SQLPS the provider you want to use.

Your code would look something like below:

set-location -Path Microsoft.PowerShell.Core\FileSystem::"\\server\companydocuments\MyApp\Application Files\"

You could also wrap the remainder into two lines if you wanted to:

cd (Get-ChildItem | Sort-Object name -Descending | Select name -First 1).name
& ".\Application.exe"


How to speed up MongoDB chunk moving between shards On a MongoDB 3.6 in a replica set and WiredTiger engine we have a large collection with 7.7TB of data. We started to see severe performance issues (extreme CPU usage and slowdown of data replication) which we somehow solved by temporary using a new empty database. The data is basically usage logs and DB pattern is that mostly there are inserts (of new usage data) and occasionally data reads for certain users or reports.

To scale the system and solve performance issues in the long run, we now created two shards where the idea is to have a fast shard (with data of recent 2 months) and a slow shard (with all historical data). Fast shard run on n1-standard-8 (8 vCPUs, 30 GB memory) machines with SSD disks, while slow shard is on n1-highmem-2 (2 vCPUs, 13 GB memory) machines with rotational disks.

The database is not under any load (there are no new usage log inserts) and basically does nothing. The sharding process has started, but the speed of moving chunks is slow and will take around 40 days to move all the chunks. There are 200000 chunks left to be moved from fast to slow shard with average chunk size 32MB and it takes about 18 seconds to move one chunk.

What can be done to speed up this chunk migration process?

UPDATE

I have learned that if source shard is on SSD disks, then the chunk moving has almost double speed.

Things like how many shards we have, which database are sharded, and the configuration setting of your shard cluster. But one of the most important pieces of information that the config servers hold, is the mapping of the chunks to shards.

By default MongoDB takes ChunkSize=64 MB

By default , MongoDB takes 64 megabytes as the default chunk size. That means that if a chunk is about 64 megabytes, or within 64 megatons range , it will be split.

1MB<=chunkSize<=1024MB

We can define a chunk size between the values of one megabyte and 1024 and one gigabyte. The chunk size is configurable during run time. So if we decide to change a chunk size, we can easily do so. But before we go in changing your chunk size.

for further your ref here and here


Remove expired certificate SQL Server I am trying to delete an expired certificate from my database server.  The certificate was used to encrypt connections to sql server 2014 r2.  We no longer have a need to encrypt connections and so we want to delete the certificate instead of renewing, however when we delete it, sql server fails to start because it is unable to establish an ssl connection because it can't find the certificate.

I have done the following:

SSCM -> Protocols (right click) -> Selected Properties and set force encryption to NO.
On the certificate tab the drop down is blank

But SQL will fail to start with the error above.  No databases are encrypted, either.

I checked by running this query

SELECT
    db.name,
    db.is_encrypted,
    dm.encryption_state,
    dm.percent_complete,
    dm.key_algorithm,
    dm.key_length
FROM
    sys.databases db
    LEFT OUTER JOIN sys.dm_database_encryption_keys dm
        ON db.database_id = dm.database_id;
GO

encrypted state returns a null for all.

I did "export" and password protect the service master key but I don't know if that has anything to do with it or not and not sure how to "undo" that if it does.

Sounds like you may have deleted the certificate from the certificate store prior to unbinding it from SQL Server.  I haven't tested it yet, but I'm guessing that if you did that, no certificate would be shown in the SSCM dialog because it would be trying to find the certificate that matches the thumbprint stored in the registry, but wouldn't be able to find it.

You may be able to fix this by clicking on the "Clear" button in SSCM where the certificate is configured.  IF this will clear the thumbprint that is stored in the registry, then SQL Server will start, but it's possible that the "Clear" button won't work if it can't find the certificate.

If that doesn't work, check the Certificate value in the registry at:

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.12.MSSQLSERVER\MSSQLSERVER\SuperSocketNetLib

The registry path will be different if you're using a named instance.

If it's not blank, then SQL Server will try to find the certificate that matches the thumbprint that is stored there.  If it doesn't find the certificate, then it fails to start.  If the certificate has been deleted and the Certificate value in the registry still contains a thumbprint, then just save what's there and then delete the thumbprint so that it's blank.  Once that is done, SQL Server should start.

Lastly, if you already have the new certificate, you should be able to install it in the certificate store, bind the service to it in SSCM, and SQL Server should start.


How to speed up query containing concat() in WHERE clause? In my company we use a commercial software running with mysql 5.5 (MyISAM) that often executes a query of this form:

SELECT table1.attrib1
  FROM table2, table1
 WHERE table2.status = 'Active'
   AND table2.table1 = CONCAT( 'constant prefix', table1.attrib1 )

The output of EXAMINE tells me that the index on table2.table1 is used, but the index on table1.attrib1 isn't used to execute the query. Instead all existing rows of table1 are accessed (full table scan). The expected result contains only a few rows.

It's a commercial software, so I can't change the query. But I could add indexes or change database parameters.

Question: What can I do to speed up this query?

Since your situation is

product is commercial
you cannot change the query
you cannot change table layout

Your best shot (really your only shot) would be

ALTER TABLE table2 ADD INDEX status_table1_ndx (status,table1);

You may or may not see a change since the

AND table2.table1 = CONCAT( 'constant prefix', table1.attrib1 )

is really a JOIN clause due to having the two tables on opposite sides of the = sign. The CONCAT on the right side would also trigger a full table scan. Adding the compound index on status and table1 seems to be the only option left.


How to limit databases number that a MySQL user can create? Is there any way to limit the number of databases, that a specific MySQL user can create? (as like as in any web hosting), they give each user the ability of creating specific number of databases.

A Limit to the number of MySQL Databases a User Account can Create

At the MySQL level as per the Limits on Number of Databases and Tables there is no limit on the number of databases MySQL can contain at this level.

If you give a user account the global CREATE permission to create new databases, then you give them just that and you cannot restrict the number of databases it can create at this level.

Limiting the number of Database a MySQL User Account can Create

To control the number of database you allow a user account to create you could just create the databases per an "approved" request and not grant them permission to CREATE databases themselves. You'd grant the user account Database Privileges at this level once created.

Additionally, as some third party hosting services utilize, if you only allow access to manage MySQL instances and databases via an application, it is possible to have rules at this level keeping track of the number of databases an account creates, and enforcing rules to set such restrictions.

Related Posts

Hosting: why does the number of MySQL databases matter?
Global Privileges
Database Privileges


Why would I need to update stats manually? I have often found that when I am having performance problems on my MSSQL database, I can resolve them by running sp_UpdateStats.  I learned this because that's always the first line of defense from tech support, right after, "Is your computer turned on?"

My understanding is that SQL Server is supposed to keep stats automatically; I shouldn't need to nanny it.  So I'd like to understand: what circumstances might lead to the stats falling out of date so badly that I need to update them manually?

As the data in a table changes the statistics objects that represent the distribution of data become outdated. This is a problem because it's the statistics objects that the optimiser uses to make it's estimates when building execution plans. If these estimates are inaccurate your performance will suffer.

SQL Server does automatically update statistics objects when the amount of data in the table that has been changed hits certain thresholds, but if you have data that is changing frequently or tables with millions of rows you will better off performing regular index maintenance.

This can be done with maintenance plans or scripts. Ola Hallengren's suite of scripts is very good for this and something that I use in all of my environments. There is also the Minionware suite that I have not used personally but I hear good things.


Persisted computed column causing scan Converting a regular column to a persisted computed column is causing this query to not be able to do index seeks. Why?
Tested on several SQL Server versions, including 2016 SP1 CU1.
Repros

With a computed column
With a regular column

The trouble is with table1, col7.
The tables and query are a partial (and simplified) version of the originals. I'm aware the query could be rewritten differently, and for some reason avoid the problem, but we need to avoid touching the code, and the question of why table1 cannot be seeked still stands.
As Paul White showed (thanks!), the seek is available if forced, so the question is: Why the seek is not chosen by the optimizer, and whether we can do something differently to make the seek happen as it should, without changing the code?
To clarify the problematic part, here's the relevant scan in the bad execution plan:

Why the seek is not chosen by the optimizer

TL:DR The expanded computed column definition interferes with the optimizer's ability to reorder joins initially. With a different starting point, cost-based optimization takes a different path through the optimizer, and ends up with a different final plan choice.

Details
For all but the very simplest of queries, the optimizer does not attempt to explore anything like the whole space of possible plans. Instead, it picks a reasonable-looking starting point, then spends a budgeted amount of effort exploring logical and physical variations, in one or more search phases, until it finds a reasonable plan.
The main reason you get different plans (with different final cost estimates) for the two cases is that there are different starting points. Starting from a different place, optimization ends up at a different place (after its limited number of exploration and implementation iterations). I hope this is reasonably intuitive.
The starting point I mentioned, is somewhat based on the textual representation of the query, but changes are made to the internal tree representation as it passes through the parsing, binding, normalization, and simplification stages of query compilation.
Importantly, the exact starting point depends heavily on the initial join order selected by the optimizer. This choice is made before statistics are loaded, and before any cardinality estimations have been derived. The total cardinality (number of rows) in each table is however known, having been obtained from system metadata.
The initial join ordering is therefore based on heuristics. For example, the optimizer tries to rewrite the tree such that smaller tables are joined before larger ones, and inner joins come before outer joins (and cross joins).
The presence of the computed column interferes with this process, most specifically with the optimizer's ability to push outer joins down the query tree. This is because the computed column is expanded into its underlying expression before join reordering occurs, and moving a join past a complex expression is much more difficult than moving it past a simple column reference.
The trees involved are quite large, but to illustrate, the non-computed column initial query tree begins with: (note the two outer joins at the top)

LogOp_Select
    LogOp_Apply (x_jtLeftOuter)
        LogOp_LeftOuterJoin
            LogOp_NAryJoin
                LogOp_LeftAntiSemiJoin
                    LogOp_NAryJoin
                        LogOp_Get TBL: dbo.table1(alias TBL: a4)
                        LogOp_Select
                            LogOp_Get TBL: dbo.table6(alias TBL: a3)
                            ScaOp_Comp x_cmpEq
                                ScaOp_Identifier QCOL: [a3].col18
                                ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=16)
                        LogOp_Select
                            LogOp_Get TBL: dbo.table1(alias TBL: a1)
                            ScaOp_Comp x_cmpEq
                                ScaOp_Identifier QCOL: [a1].col2
                                ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=16)
                        LogOp_Select
                            LogOp_Get TBL: dbo.table5(alias TBL: a2)
                            ScaOp_Comp x_cmpEq
                                ScaOp_Identifier QCOL: [a2].col2
                                ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=16)
                        ScaOp_Comp x_cmpEq
                            ScaOp_Identifier QCOL: [a4].col2
                            ScaOp_Identifier QCOL: [a3].col19
                    LogOp_Select
                        LogOp_Get TBL: dbo.table7(alias TBL: a7)
                        ScaOp_Comp x_cmpEq
                            ScaOp_Identifier QCOL: [a7].col22
                            ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=16)
                    ScaOp_Comp x_cmpEq
                        ScaOp_Identifier QCOL: [a4].col2
                        ScaOp_Identifier QCOL: [a7].col23
                LogOp_Select
                    LogOp_Get TBL: table1(alias TBL: cdc)
                    ScaOp_Comp x_cmpEq
                        ScaOp_Identifier QCOL: [cdc].col6
                        ScaOp_Const TI(smallint,ML=2) XVAR(smallint,Not Owned,Value=4)
                LogOp_Get TBL: dbo.table5(alias TBL: a5)
                LogOp_Get TBL: table2(alias TBL: cdt)
                ScaOp_Logical x_lopAnd
                    ScaOp_Comp x_cmpEq
                        ScaOp_Identifier QCOL: [a5].col2
                        ScaOp_Identifier QCOL: [cdc].col2
                    ScaOp_Comp x_cmpEq
                        ScaOp_Identifier QCOL: [a4].col2
                        ScaOp_Identifier QCOL: [cdc].col2
                    ScaOp_Comp x_cmpEq
                        ScaOp_Identifier QCOL: [cdt].col1
                        ScaOp_Identifier QCOL: [cdc].col1
            LogOp_Get TBL: table3(alias TBL: ahcr)
            ScaOp_Comp x_cmpEq
                ScaOp_Identifier QCOL: [ahcr].col9
                ScaOp_Identifier QCOL: [cdt].col1

The same fragment of the computed column query is: (note the outer join much lower down, the expanded computed column definition, and some other subtle differences in (inner) join ordering)

LogOp_Select
    LogOp_Apply (x_jtLeftOuter)
        LogOp_NAryJoin
            LogOp_LeftAntiSemiJoin
                LogOp_NAryJoin
                    LogOp_Get TBL: dbo.table1(alias TBL: a4)
                    LogOp_Select
                        LogOp_Get TBL: dbo.table6(alias TBL: a3)
                        ScaOp_Comp x_cmpEq
                            ScaOp_Identifier QCOL: [a3].col18
                            ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=16)
                    LogOp_Select
                        LogOp_Get TBL: dbo.table1(alias TBL: a1
                        ScaOp_Comp x_cmpEq
                            ScaOp_Identifier QCOL: [a1].col2
                            ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=16)
                    LogOp_Select
                        LogOp_Get TBL: dbo.table5(alias TBL: a2)
                        ScaOp_Comp x_cmpEq
                            ScaOp_Identifier QCOL: [a2].col2
                            ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=16)
                    ScaOp_Comp x_cmpEq
                        ScaOp_Identifier QCOL: [a4].col2
                        ScaOp_Identifier QCOL: [a3].col19
                LogOp_Select
                    LogOp_Get TBL: dbo.table7(alias TBL: a7)
                    ScaOp_Comp x_cmpEq
                        ScaOp_Identifier QCOL: [a7].col22
                        ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=16)
                ScaOp_Comp x_cmpEq
                    ScaOp_Identifier QCOL: [a4].col2
                    ScaOp_Identifier QCOL: [a7].col23
            LogOp_Project
                LogOp_LeftOuterJoin
                    LogOp_Join
                        LogOp_Select
                            LogOp_Get TBL: table1(alias TBL: cdc)
                            ScaOp_Comp x_cmpEq
                                ScaOp_Identifier QCOL: [cdc].col6
                                ScaOp_Const TI(smallint,ML=2) XVAR(smallint,Not Owned,Value=4)
                        LogOp_Get TBL: table2(alias TBL: cdt)
                        ScaOp_Comp x_cmpEq
                            ScaOp_Identifier QCOL: [cdc].col1
                            ScaOp_Identifier QCOL: [cdt].col1
                    LogOp_Get TBL: table3(alias TBL: ahcr)
                    ScaOp_Comp x_cmpEq
                        ScaOp_Identifier QCOL: [ahcr].col9
                        ScaOp_Identifier QCOL: [cdt].col1
                AncOp_PrjList
                    AncOp_PrjEl QCOL: [cdc].col7
                        ScaOp_Convert char collate 53256,Null,Trim,ML=6
                            ScaOp_IIF varchar collate 53256,Null,Var,Trim,ML=6
                                ScaOp_Comp x_cmpEq
                                    ScaOp_Intrinsic isnumeric
                                        ScaOp_Intrinsic right
                                            ScaOp_Identifier QCOL: [cdc].col4
                                            ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=4)
                                    ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=0)
                                ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=1) XVAR(varchar,Owned,Value=Len,Data = (0,))
                                ScaOp_Intrinsic substring
                                    ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=6)
                                    ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=1)
                                    ScaOp_Identifier QCOL: [cdc].col4
            LogOp_Get TBL: dbo.table5(alias TBL: a5)
            ScaOp_Logical x_lopAnd
                ScaOp_Comp x_cmpEq
                    ScaOp_Identifier QCOL: [a5].col2
                    ScaOp_Identifier QCOL: [cdc].col2
                ScaOp_Comp x_cmpEq
                    ScaOp_Identifier QCOL: [a4].col2
                    ScaOp_Identifier QCOL: [cdc].col2

Statistics are loaded and an initial cardinality estimation is performed on the tree just after the initial join order is set. Having the joins in different orders also affects these estimates, and so has a knock-on effect during later cost-based optimization.
Finally for this section, having an outer join stuck in the middle of the tree can prevent some further join reordering rules matching during cost-based optimization.

Using a plan guide (or, equivalently a USE PLAN hint - example for your query) changes the search strategy to a more goal-oriented approach, guided by the general shape and features of the supplied template. This explains why the optimizer can find the same table1 seek plan against both computed and non-computed column schemas, when a plan guide or hint is used.
Whether we can do something differently to make the seek happen
This is something you only need to worry about if the optimizer does not find a plan with acceptable performance characteristics on its own.
All the normal tuning tools are potentially applicable. You can, for example, break the query up into simpler parts, review and improve the available indexing, update or create new statistics...and so on.
All these things can affect cardinality estimates, the code path taken through the optimizer, and influence cost-based decisions in subtle ways.
You may ultimately resort to using hints (or a plan guide), but that's not usually the ideal solution.

Additional questions from comments

I agree that it's best to simplify the query etc., but is there a way (trace flag) to make the optimizer continue with the optimization and reach the same result?

No, there's no trace flag to perform an exhaustive search, and you don't want one. The possible search space is vast, and compilation times that exceed the age of the universe would not be well-received. Also, the optimizer doesn't know every possible logical transform (no one does).

Also, why is the complex expansion needed, as the column is persisted? Why can't the optimizer avoid expanding it, treat it like a regular column, and reach the same starting point?

Computed columns are expanded (like views are) to enable additional optimization opportunities. The expansion may be matched back to e.g. a persisted column or index later in the process, but this happens after the initial join order is fixed.


Oracle - Delete dmp files from ASM diskgroup I want to delete my export files daily from ASM diskgroup with a crontab job. I want to prepare a script for it.
ASMCMD> ls

exp1.dmp

exp2.dmp

exp3.dmp

exp4.dmp

ASMCMD> pwd

+DATA/EXP

How can I prepare this script? I prepared a template for it, but I couldn't script it.
set_gridenv
asmcmd
cd +DATA/EXP
rm -rf exp*.dmp
exit
Best regards,

Just did a small test.
# find some ASM files
asmcmd find DATA/T101N/AUTOBACKUP/2020_09_20/ \*
+DATA/T101N/AUTOBACKUP/2020_09_20/s_1051641713.313.1051641713
+DATA/T101N/AUTOBACKUP/2020_09_20/s_1051658719.331.1051658719

# remove them one-by-one
asmcmd rm DATA/T101N/AUTOBACKUP/2020_09_20/s_1051641713.313.1051641713
asmcmd rm DATA/T101N/AUTOBACKUP/2020_09_20/s_1051658719.331.1051658719

# they are gone
asmcmd find DATA/T101N/AUTOBACKUP/2020_09_20/ \*
ASMCMD-8002: entry 'AUTOBACKUP' does not exist in directory '+DATA/T101N/'

Your scenario should be similar:
#!/bin/bash

# set grid home
. oraenv <<< +ASM

# delete dump-files
asmcmd rm -rf DATA/EXP exp\*.dmp

Best of luck!


why SELECT is still using CPU & DiskIO? I have a large database, about 30GB. In order to delete some data from a table (most data is in this table, simply because the # of rows), I have to do delete in batch. However, if I delete in too many batches, the system becomes very slow. So, what we do is: query the # of data that will be deleted, then use 1/10 of the # as batch size.

When I test this, I found a weird thing.

since it has lots of data to be deleted, the delete batch loop takes a long time to finish. during the delete, when I use sp_who2 to check, I can see the CPU time and DiskIO of 2 SPID keep changing. One is for DELETE and one is for SELECT. the delete is for bacth delete. but the SELECT is for getting the # of data that will be deleted.

I'm using the sqlcommand in c# to do this. The SELECT was done first, once get the #, then we do DELETE. The log actually shows the result of SELECT. My question is: why sp_who2 still shows the CPU time and DiskIO changing? Should it be done already? what can cause this?

and at the same time, the CPU Time and DiskIO for delete is changing too.

anyone has an explaination of this?

thanks

If there's a where clause in the DELETE statement, then the server still needs to perform a SELECT internally to the DELETE statement in order to delete only the specified records.

You may be only issuing one DELETE statement, but that's a convenience of SQL. The Database software has to process that statement and perform the necessary logic to do the job under the hood.  This is one of those things it does that you don't normally think about until you happen to notice it, as you did now.


Read uncommited does not work in UPDATE WHERE NOT EXISTS I'm not able to force SQL Server (2016) to respect values updated in the same statement.

What I need to do is to update old values (column a in table #t in demo code below) with mapping table (#u) to new ones (column new_a in table #u)

After the update rows containing old values would be deleted.

Here is the demo code:

DROP TABLE IF EXISTS #t
DROP TABLE IF EXISTS #u

CREATE TABLE #t (a int, b int)

CREATE UNIQUE CLUSTERED INDEX t_idx ON #t
(
    a ASC,
    b ASC
)

CREATE TABLE #u (old_a int, new_a int)

INSERT INTO #t (a, b)
VALUES
(1,1),
(1,2),
(2,1),
(2,2)

INSERT INTO #u (old_a, new_a)
VALUES
(1, 3),
(2, 3)

UPDATE t
SET t.a = u.new_a
FROM #t AS t WITH (READUNCOMMITTED)
JOIN #u AS u
ON u.old_a = t.a

WHERE NOT EXISTS (
    SELECT 1
    FROM #t AS t_top WITH (READUNCOMMITTED)
    WHERE 1=1
    AND t_top.a = u.new_a
    AND t_top.b = t.b)

which results in

Cannot insert duplicate key row in object 'dbo.#t' with unique index 't_idx'. The duplicate key value is (3, 1).

And while I can do it other ways (eg. disable unique index, then delete duplicates) I would like to know why SQL Server does not want to read freshly updated values.

Also, is it possible to somehow update with commits after each row? Preferably without using stored procedures.

SQL Server version is 2016

EDIT:
SQL Server sees the values when checking for FK violations, but not in WHERE EXISTS clause

EDIT 2:

There is no "first update", "second update" here, there is one statement, one atomic update. And this update produces 2 rows that are the same.

If you want "the second" update to not succeed you should write 2 explicit updates, not one, inside 1 transaction. But in this case nolock would have no sense because the same transaction sees its own uncommitted values without any hint at all.


Unable to execute the Teradata stored procedure due to syntax errors ERROR: CALL Failed. 3707:  SP_PARTICIPANTS_BY_AGE:Syntax error, expected something like a name or a Unicode delimited identifier between '(' and ')'.

Below is the query used

REPLACE  PROCEDURE  SANDBOX.SP_PARTICIPANTS_BY_AGE ()

DYNAMIC RESULT SETS 1
SQL SECURITY INVOKER

BEGIN

    DECLARE c  CURSOR WITH RETURN ONLY FOR
        WITH
            AgeData as (
                SELECT
                usr.USER_ID
                ,usr.BIRTH_DATE
                ,floor(months_between(CURRENT_DATE , usr.BIRTH_DATE )/12) AS AgeCalc
                FROM DV1HDSV.SPIRE_USERS usr
            )

        SELECT
            MA.AGE_CATEGORY
            ,COUNT (DISTINCT fit.USER_ID) AS PARTICIPANT_COUNT
            ,CASE AGE_CATEGORY
            WHEN 'Under 30' THEN 1
            ELSE ROW_NUMBER() OVER (ORDER BY AGE_CATEGORY)
            END AS DATAORDER
        FROM
            (
                SELECT
                USER_ID
                ,AgeCalc
                ,CASE
                WHEN AgeCalc <=30 THEN 'Under 30'
                WHEN AgeCalc BETWEEN 31 AND 40 THEN '31-40'
                WHEN AgeCalc BETWEEN 41 AND 50 THEN '41-50'
                WHEN AgeCalc BETWEEN 51 AND 60 THEN '51-60'
                WHEN AgeCalc > 60 THEN 'Above 60'
                END AS AGE_CATEGORY
                FROM AgeData
            ) MA
            LEFT JOIN DV1HDSV.SPIRE_FITNESS_RECORDS fit
            ON fit.USER_ID = MA.USER_ID
        WHERE AGE_CATEGORY IS NOT NULL
        GROUP BY MA.AGE_CATEGORY
        ORDER BY DATAORDER;

    OPEN c;

END;

Teradata doesn't support CTE in stored procedures (as of 15.00), see SQL Data Manipulation Language > The SELECT Statement > WITH and WITH RECURSIVE Statement Modifiers. Surprisingly, in some cases WITH works, but in more complex cases you get a syntax error like that.


Copy table structure excluding nullability into local temporary table How can I copy the structure of a table into a temp table (or table variable) excluding the nullability of each column? Mainly I need to copy the column names and data types, but not nullability, keys, indexes, etc. I'll also note that this must be done programmatically within a procedure, so I can't just generate a create table script and modify it. The best I've come up with has been:

select top 0 *
into #MyTempTable
from dbo.MyTable

which copies the nullability of each field, thus defeating the purpose for me.

I've also played around with dynamic SQL and extracting the column data from table INFORMATION_SCHEMA.COLUMNS to build the create table statement, but the issue there is that the temp table goes out of scope after the dynamic SQL statement executes and control is returned to the "main" procedure. (And I'd rather not jam the rest of the procedure into the dynamic SQL statement.) If there were a way to return the temp table from the exec (@Sql) statement or keep it in scope somehow, it might work, but I don't know that there's a way to do that.

Related questions:

Copy complete structure of a table
Create table structure from existing table

For table variables there is not much you can do. However, for temporary tables (even local ones -- i.e. those with names starting with a single #) there is quite a bit that can be done.

With regards to the behavior of local temporary tables not surviving the end of the process in which they were created, the place where people seem to get stuck is in assuming that they can only be created. That might be true for table variables (which is why they won't help here), but not so true for temporary tables (local or global): they can also be altered ! And, because local temporary tables are available to sub-processes, and because any changes made to them in a sub-process survive the ending of that sub-process, they can be created in the "current" scope and then altered in a sub-process. The end result will be the desired structure in the current scope :-) !

So you don't need to approach this problem in terms of needing to create the local temporary table with the perfect structure, just so long as there is a programmatic means of determining what changes should be made. And for this particular request that is (thankfully) not terribly difficult. Once the temporary table is created, we just look at the columns of the source table to get their names, datatypes, and optional collation (the three pieces needed for the ALTER TABLE statement). Then we just construct a Dynamic SQL string with the ALTER TABLE statements that adjust the nullability as desired. Execute that and all will be right with the world.

Set up the test:

For this test we will use a local temporary table as the table to copy, but that is just to make for an easier test, and a permanent table will behave the same way.

SET NOCOUNT ON;

IF (OBJECT_ID(N'tempdb..#SourceTable') IS NOT NULL)
BEGIN
  PRINT 'Dropping #SourceTable...';
  DROP TABLE #SourceTable;
END;

CREATE TABLE #SourceTable
(
  Col1 INT,
  Col2 NVARCHAR(37) COLLATE Hungarian_100_CI_AI_SC NOT NULL,
  Col3 DATE,
  Col4 TIME(3) NOT NULL,
  Col5 FLOAT(20) NOT NULL,
  Col6 DECIMAL(10,3) NOT NULL,
  Col7 NUMERIC(14)
);

IF (OBJECT_ID(N'tempdb..#TargetTable') IS NOT NULL)
BEGIN
  PRINT 'Dropping #TargetTable...';
  DROP TABLE #TargetTable;
END;

Run the test:

Finding the nullability is rather easy. But you can't issue an ALTER COLUMN statement without specifying the datatype. The datatype is provided in proper format by the sys.dm_exec_describe_first_result_set() DMF, but that was introduced in SQL Server 2012, hence it won't help on SQL Server 2008 R2. So, we have to rebuild the actual datatypes, which means you need to: divide max_length by 2 for NCHAR and NVARCHAR; use scale by itself for time, datetime2, and datetimeoffset; and use both scale and precision for decimal and numeric ( <sarcasm> so much fun </sarcasm> ).

NOTE: Please remember to remove the two references to tempdb. in the code below when using this outside of a testing scenario. Those references -- noted in inline comments in the code -- are only there due to the source table (the table to be copied) being a temporary table and existing in tempdb.

SELECT *
INTO   #TargetTable
FROM   #SourceTable
WHERE  1 = 0;

DECLARE @AlterQuery NVARCHAR(MAX);
SET @AlterQuery = N'';

SELECT  @AlterQuery = @AlterQuery
        + N'ALTER TABLE #TargetTable ALTER COLUMN [' + col.[name] + N'] '
        + CASE
            WHEN col.user_type_id IN (34, 35, 36, 40, 48, 52, 56, 58, 59, 60, 61, 62, 98,
                            99, 104, 122, 127, 128, 129, 130, 189, 241, 256) THEN tp.name
            WHEN col.system_type_id = 240 THEN tp.name -- User-Defined Types (UDTs)
            WHEN col.user_type_id IN (231, 239) THEN tp.name + N'(' +
                       CONVERT(NVARCHAR(5), col.max_length / 2) + N')' -- nvarchar, nchar
            WHEN col.user_type_id IN (165, 167, 173, 175) THEN tp.name + N'(' +
                    CONVERT(NVARCHAR(5), col.max_length) + N')' -- (var)binary, (var)char
            WHEN col.user_type_id IN (41, 42, 43) THEN tp.name + N'(' +
                CONVERT(NVARCHAR(5), col.scale) + N')' -- time, datetime2, datetimeoffset
            WHEN col.user_type_id IN (106, 108) THEN tp.name + N'(' +
                            CONVERT(NVARCHAR(5), col.[precision]) + N', ' +
                            CONVERT(NVARCHAR(5), col.scale) + N')' -- decimal, numeric
          END
        + ISNULL(N' COLLATE ' + col.collation_name, N'')
        + N' NULL;' + NCHAR(0x0D) + NCHAR(0x0A) -- CR + LF
FROM     tempdb.sys.columns col -- remove "tempdb."
INNER JOIN sys.types tp
        ON tp.user_type_id = col.user_type_id
WHERE    col.[object_id] = OBJECT_ID(N'tempdb..#TargetTable') -- remove "tempdb..#"
AND      col.is_identity <> 1 -- exclude the IDENTITY field
AND      col.is_nullable = 0 -- only ALTER the columns that actually need it
ORDER BY col.column_id ASC;

PRINT @AlterQuery; -- debug

EXEC (@AlterQuery);

Check the results:

The following query will display the columns and their datatypes and nullability. You will see that all fields now have is_nullable showing as 1 :-).

SELECT col.column_id,
       col.[name],
       tp.[name],
       col.max_length,
       col.[precision],
       col.scale,
       col.is_nullable
FROM   tempdb.sys.columns col
INNER JOIN sys.types tp
        ON tp.user_type_id = col.user_type_id
WHERE  col.[object_id] = OBJECT_ID(N'tempdb..#TargetTable')
ORDER BY col.column_id ASC;


Is there any way to replace sql server file_guid with new GUID? I have two databases with same file_guid. Getting following error when trying to restore second database in Amazon RDS.

  Aborted the task because of a task failure or a concurrent RESTORE_DB
  request. Task ID 8 (RESTORE_DB) exception: Database DATABASENAME
  cannot be restored because there is already an existing database with
  the same file_guids on the instance.

This is because I already restored another database with same file_guid but different physical_name), which is an Amazon RDS limitation. If interested find details in here and here.

How can I update sys.database_files record?

We had to do the same - as we wanted to copy a database to a new database, changing the name and file IDs at the same time.

We found the original process posted here to not to be repeatable, I guess it depends on how the original file ids were created.  In any event, I have updated the following procedure, which is repeatable.

Backup database from RDS to S3 using standard RDS database backup.

exec msdb.dbo.rds_backup_database
@source_db_name = '<database-name>',
@s3_arn_to_backup_to = 'arn:aws:s3:::<bucket-name>/<backup-filename>'

Restore .bak file into SQL Server on a local machine.  We have MSDN account, so we just downloaded SQL 2016 and installed, giving us full access to SQL on a local machine.  But, I believe you can download SQL Express and do the same, some functionality may be lost depending on the source version of the SQL Engine.
Perform a Export Data Tier Application for the database.  This will create a .bacpac file which includes the schema, data and code (stored procedures, etc.).
Delete the original data imported into SQL Server in step 3.
Perform an Import Data Tier Application and point to the .bacpac file created in step 3.  This step will set the file guids = _Data and _Logs, making them unique.
Do a full backup to get a new .bak file.
Upload new .bak file to S3 and do standard RDS database import.

exec msdb.dbo.rds_restore_database
@restore_db_name = '<database-name>',
@s3_arn_to_restore_from = 'arn:aws:s3:::<bucket-name>/<backup-filename>'


What's the data flow between Pgpool, Postgresql and client I'm new in Pgpool.

I want to ask the question, what is the dataflow between Pgpool, Postgresql when client querying data

1/

Client query data to Pgpool,
PgPool ask Postgresql
Postgresql calculate result, return data to PgPool
PgPool transfer data to Client

2/

Client query data to PgPool
PgPool ask Postgresql
Client and Postgresql make a connection
Postgresql calculate result,
Postgresql directly transfer data to Client

3/ Other schema ?

Could you please show me the right answer

Many thanks

Pgpool is a kind of a layer between client and postgresql. Pgpool receive a query from client and ask Postgresql for data. Postgresql answers to Pgpool and pgpool send data to client like on the picture below. There is not direct connection between client and Postgres when you use Pgpool to connect to database. By the way Pgpool provides connection pooling, replication, load balance, connection limits and paralell query so it's quite nice to use with PGSQL

Manual: http://www.pgpool.net/docs/latest/pgpool-en.html


Case style of tables and field (Naming Conventions) What is best practice for naming dbs, tables (collections) and fields among DBMSs?

I mean casing style, for example AaaBaa,aaa-bbb, aaa_bbb, AAA-BBB, aaaBbb, ...

I think if one exists it should be applicable to all DBMSs  (MySQL, MongoDB, Oracle, ...), shouldn't it?

Currently I thing following is most appropriate:

Columns/Fields/Tables/Collections/Databases:  aaaBbbCcc.

Could distinct case style for these object be useful at all?

It all boils down to personal preference and consistency within your team (or just yourself). Nothing worse than seeing different styles mixed.

As for SQL Server, be mindful of sp_yourProcName, as "sp_" prefix for a stored procedure can do funny things (read more about this from Aaron Bertrand).

Consistency is king. Make your choice and stick with it.


AlwaysOn Synchronous Mode Possibilty We have 2 SQL Servers at Production site and 1 SQL Server at DR site.

Please correct below things as I am not getting clear idea about these sync mode in AlwaysOn:

If I install SQL Server FCIs in Production environment (2 node) can I set one secondary to synchronous mode automatic failover and DR (Stand-Alone) server to Async mode?
Total 3 SQL Servers (Primary Sync, Secondary Sync and Secondary Async (Stand-Alone)) is it possible?
If SQL Server Cluster (2 Instances) is configured in Windows 2-node cluster, is synchronous mode possible?
Should all the SQL Servers be stand-alone to configure AlwaysOn synchronous mode and automatic failover?
Two SQL Servers in Production site, can I set secondary as Synchronous mode with automatic failover?

If I install SQL Server FCIs in Production environment(2 node) can i
  set one secondary to synchronous mode automatic failover and
  DR(Stand-Alone) server to Async mode?

If you use a SQL Server FCI with Availability Groups, you cannot have automatic failover of the AG but you still get automatic failover of the FCI. The FCI will be one instance and the DR secondary will be another (as FCI are logically a single instance).

  Total 3 SQL Servers (Primary Sync,Secondary Sync and Secondary
  Async(Stand-Alone)) is it possible ?

This is possible, but you're going to use the FCI from your first question then there will only ever be 2 instances, so you'd have Primary Sync and secondary async.

  If SQL Server Cluster(2 Instances) is configured in windows 2 node
  cluster,is synchronous mode possible ?

I'm not sure what you mean by SQL Server cluster and windows cluster. If you are talking FCIs, you wouldn't be able to AG between the FCIs because they'd share the same set of nodes.

  Should all the SQL Servers should be stand-alone to configure
  Always-ON synchronous mode and automatic failover?

They don't have to be. It really depends on what you're trying to solve for. Remember that each secondary will have a copy of the database and will need to have the log blocks be transported to it. This may or may not be doable with the infrastructure available.

  Two SQL Servers in Production site,can i set secondary as Synchronous
  mode with automatic failover ?

Assuming neither are in a SQL Server FCI, yes and you can do it on the fly (though it may take time to get in sync).


Cartesian Product SQL / Inserts with multiple Values Clauses I want to create cartesian product sql query.But,I'm getting the error.Where I wrong?

alter table #Personel
(
Ad varchar(100)
)

alter table #Masa
(
 MasaAdi varchar(100)
)

insert into #Personel values('deneme1'),('deneme2'),('deneme3')
insert into #Masa values('Masa1'),('masa2'),('masa3')
select Ad,MasaAdi from
(
   select ROW_NUMBER() over(partition by MasaAdi order by ID,Ad ) as Sira
      ,ID,Ad,MasaAdi
   from (
   select newid() as ID,Ad,MasaAdi from #Personel
   left outer join #Masa on 1=1
   ) t
) t2 where Sira = 1 order by Ad

On SQL Server you can write

select Ad,MasaAdi from
(
 select ROW_NUMBER() over(partition by MasaAdi order by ID,Ad ) as Sira,ID,Ad,MasaAdi
 from (
 select newid() as ID,Ad,MasaAdi
 from #Personel
 cross join #Masa
 )t
 ) t2 where Sira = 1 order by Ad

This is the best syntax to emphasize that it is a cross join.

But on SQL Server 2008 your query gives the same result .

EDIT:

Your problem is not the select statement, but the inserts. Inserts with multiple value clauses is a new feature of SQL Server 2008. For 2005 you have to write:

insert into #Personel values('deneme1')
insert into #Personel values('deneme2')
insert into #Personel values('deneme3')

insert into #Masa values('Masa1')
insert into #Masa values('masa2')
insert into #Masa values('masa3')


Query runs a long time in some newer MySQL versions I have created a database on MySQL 5.0.15. I have a query and when I run this query on this MySQL version, I get 0.9 s run time. When I import this database to another MySQL server with same hardware and run the same query I get over 120s and sometimes MySQL hangs.

What is the difference between 5.0 and 5.1 or 5.5? I have tested 5.1 and 5.5 versions.

Is it possible a query takes longer in a newer version (something like mysql structure change)?

Sorry but I can't put this query here, but the query is like:

SELECT fl_passenger_ticket. *,
       fl_aganc.name                             AS agancname,
       fl_pnr.remark                             AS remark,
       fl_pnr.reservetime                        AS reservetime,
       fl_pnr.cancelpnr,
       fl_flight_date.fromcity                   AS fromcity,
       fl_flight_date.tocity                     AS tocity,
       fl_flight_date.flightdate                 AS flightdate,
       fl_flightdate_capacity.adultper           AS adultper,
       fl_flightdate_capacity.childper           AS childper,
       fl_flightdate_capacity.infantper          AS infantper,
       fl_flightdate_capacity.cancel             AS cancelsegment,
       fl_flightdate_capacity.tax1adultpric,
       fl_flightdate_capacity.tax1childpric,
       fl_flightdate_capacity.tax1infantpric,
       fl_flightdate_capacity.tax2adultpric,
       fl_flightdate_capacity.tax2childpric,
       fl_flightdate_capacity.tax2infantpric,
       ( fl_flightdate_capacity.tax3adultpric +
         fl_flightdate_capacity.tax4adultpric +
         fl_flightdate_capacity.tax5adultpric )  AS taxxtadultpric,
       ( fl_flightdate_capacity.tax3childpric +
         fl_flightdate_capacity.tax4childpric +
         fl_flightdate_capacity.tax5childpric )  AS taxxtchildpric,
       ( fl_flightdate_capacity.tax3infantpric +
         fl_flightdate_capacity.tax4infantpric
         +
         fl_flightdate_capacity.tax5infantpric ) AS taxxtinfantpric
FROM   fl_passenger_ticket
       INNER JOIN fl_pnr
         ON ( fl_passenger_ticket.pnrid = fl_pnr.pnrid )
       INNER JOIN fl_aganc
         ON ( fl_pnr.agancid = fl_aganc.agancid )
       LEFT JOIN fl_flightdate_capacity
         ON ( fl_pnr.pnrid = fl_flightdate_capacity.pnrid )
       LEFT JOIN fl_flight_date
         ON ( fl_flightdate_capacity.flightdateid = fl_flight_date.flightdateid
            )
WHERE  fl_passenger_ticket.ticketnumber <> ''
       AND fl_passenger_ticket.pnrid <> 0
       AND fl_pnr.agancid = 60
       AND fl_flightdate_capacity.aganccharterid = 0
       AND fl_flightdate_capacity.cancel IN ( 0, 1 )
       AND fl_pnr.reservetime >= '2011/09/01 00:00:00'
       AND fl_pnr.reservetime <= '2011/09/19 23:59:00'
ORDER  BY fl_passenger_ticket.rowid,
          fl_pnr.reservetime

I have 4 joins.
The table is innodb.
There are 100000 records

The result is 100 rows and 50 columns.

EXPLAIN result is

show variables like 'innodb%' result

Just off the bat, newer versions of MySQL actually improve innodb performance (especially 5.5). I would highly recommend updating to this version if you're going to run InnoDB.

One method you could use to hunt down why it's taking so much longer is using MySQL Profiles

mysql> SET PROFILING=1;
mysql> SHOW TABLES;
mysql> SELECT * FROM foo;
mysql> SET PROFILING=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW PROFILES;
+----------+------------+-------------------+
| Query_ID | Duration   | Query             |
+----------+------------+-------------------+
|        1 | 0.09270400 | SHOW TABLES       |
|        2 | 0.00026400 | SELECT * FROM foo |
+----------+------------+-------------------+
2 rows in set (0.05 sec)

mysql> SHOW PROFILE FOR QUERY 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000053 |
| checking permissions | 0.000009 |
| Opening tables       | 0.000032 |
| System lock          | 0.000010 |
| init                 | 0.000028 |
| optimizing           | 0.000003 |
| statistics           | 0.000012 |
| preparing            | 0.000008 |
| executing            | 0.000003 |
| Sending data         | 0.000068 |
| end                  | 0.000004 |
| query end            | 0.000007 |
| closing tables       | 0.000008 |
| freeing items        | 0.000013 |
| logging slow query   | 0.000003 |
| cleaning up          | 0.000003 |
+----------------------+----------+
16 rows in set (0.04 sec)

This should give you an indication of where it's hanging. From your explain output, you should try to get some indexing on the second and third tables instead of doing full table scans. But without DDL or the actual join columns, I can't suggest anything better than to research indexing strategies.


Recovering InnoDB database from a full physical backup? I had a server running Ubuntu 12.04 crash, and had to fully reinstall. I have a full physical backup of all files stored on the server, including the ibdata1, .frm, and .idb files associated with my databases, which were largely using InnoDB in MariaDB 5.5. However, since this is just a little side project, and an opportunity for me to get my feet wet dealing with server and database administration, I am at a loss for where to proceed from here. I can find numerous references for how to proceed if you are missing any of these files, mostly covering that it is extremely difficult to do so, but possible, however, I cannot find any instructions for how to successfully restore the database from a backup containing all of these files. Clearly I'm missing something.

Moving forward I'll be sure to add making a logical backup to the list of priorities, but it's a bit late for than now. And while I'd very much prefer to not write the data I have off, if that's what it comes to, I'll chalk it up as a learning experience.

Put all the files back where they were.
Start mysql/mariadb.

Caveat:  If MySQL/MariaDB was running when you took the physical dump, there may be issues.


To give a user all privileges except the 'Create user' privilege My task is simple i want to create a user in MySQL server that has all the privileges but excluding the ability to create other users.

I tried to create a user through the root account. then i gave him all the privileges, then i revoked the 'create user' privilege from this new user, then i 'flushed privileges'. i thought this is would be enough but when i logged in with the new user, i was able to create another user.

these are the codes that i used in my root account to create a new user and give him privileges except for the 'create user' privilege:

     create user 'user91'@'localhost' identified by 'google';
     grant all privileges on *.* to 'user91'@'localhost';
     revoke create user on *.* from 'user91'@'localhost';
     flush privileges;

After this i logged out of the root account and signed in with the 'user91' account and tried to create another user with this code:

     create user 'slim2000'@'localhost' identified by 'stealth';

and this code worked, what should i add in the first block of code in order to prevent 'user91' from creating other users.

This works:

As root:

mysql> CREATE USER 'myuser'@'%' IDENTIFIED BY 'billy';
Query OK, 0 rows affected (0.04 sec)

Then, on the sport schema, grant ALL to myuser

mysql> GRANT ALL ON sport.* TO 'myuser'@'%';
Query OK, 0 rows affected (0.02 sec)

Exit and then log on as the new user myuser

mysql> exit;
Bye
[pol@localhost inst]$ ./bin/mysql -S ./mysql.sock -u myuser -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.22-log Source distribution

Show all the schemas myuser can see

mysql> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sport              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

There are many more schemas on this server - it's just that myuser can't see them.

mysql> use sport;
Database changed

Then, try to create a user as myuser.

mysql> create user 'fred'@'%' IDENTIFIED BY 'mary';
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

Fails.

But, I can do this.

mysql> create table test (a int, b varchar(10));
Query OK, 0 rows affected (0.53 sec)

mysql> insert into test values(3, 'testvalue');
Query OK, 1 row affected (0.04 sec)

Take a look at the three answers here, here and here for a better idea of what's going on. Always test your users after creation - it's very easy to give users too much power.

You can also do it this way (a more elegant solution perhaps)

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,
    INDEX, ALTER, CREATE TEMPORARY TABLES
ON sport.* TO 'username'@'localhost' IDENTIFIED BY 'password';

Finally, you could just remove the CREATE USER privilege (and, of course, the WITH GRANT OPTION) from this answer to have a "super user" who can do everything except create new users.

I think @sqlbot may have figured out the root of your problem.


Does `Seconds_Behind_Master` show exact Slave Lag from Master? Does Seconds_Behind_Master always show the correct number of seconds the slave is behind the master?

I have also Implemented the mk-heartbeat replication monitoring.

It was showing the other result in comparison with Seconds_Behind_Master.

Which one is more accurate?

Seconds_Behind_Master is based on the difference between UNIX_TIMESTAMP() and the timestamp logged for the query within the binary log of the master, or the relay log on the slave. Seconds_Behind_Master actually gets you lost in context of realtime if replication processes a series of long running queries. The lag could grow astronomically until all relay log entries are processed and then Seconds_Behind_Master will suddenly drop to zero. In terms of realtime, you have no way to knowing or anticipating when the lag will eventaully dissipate until it hits zero.

Strictly using mysql, you can monitor replication lag in terms of realtime in the following manner:

From SHOW SLAVE STATUS\G, get two values

Relay_Master_Log_File represents log file containing the last successfully execute SQL statement on the Master that was executed on the Slave.
Exec_Master_Log_Pos represents the position within Relay_Master_Log_File of the last successfully execute SQL statement on the Master that was executed on the Slave.

You could perform this against that binary log:

TMSTMP=`mysqlbinlog ---start-position=EMLP RMFL | head -50 | grep "^SET TIMESTAMP=" | head -1 | sed 's/=/ /g' | sed 's/\// /g' | awk '{print $3}'`
RIGHTNOW=`date +%s`
(( REPLAG = RIGHTNOW - TMSTMP ))

where EMLP is Exec_Master_Log_Pos RMFL and is the Relay_Master_Log_File

While this is realistically the correct way to get replication lag using only the log file and postition, you must

communicate with the slave to get the needed log file and position
communicate with the master to retrieve the dump of the binlog
dump the binlog each time you check (mysqlbinlog works safely when it it not the currently open log, this may require performing FLUSH LOGS on the master before dumping the master log file)

Getting replication lag from the binary logs yourself takes a lot more leg work.

mk-heartbeat checks for a heartbeat table and record only. Using a live table on a master and comparing the slave's copy of the heartbeat table to UNIX_TIMESTAMP() on the slave is a much more concise realtime lag measurement.

You should go with mk-heartbeat.


Storing data in PostgreSQL: One table or two? I've just started using PostgreSQL 9.2 and my data consists of product prices at various points in time, usually a different price every month.

Question: Because every product can have different prices over time, I am thinking of storing all the data in a single table, each row having full details of the product, its price at that time, and the time.

id  | name           | color  | manufacturer | date      | price
123   Socks 123        black    nikki          12-12-2012   123.70
124   Socks 123        black    nikki          12-01-2013   145.67
125   Graphic Tee xXx  red      departed       13-01-2013   250.80

My reason for storing everything in one table and have duplicate data in certain columns like name, color, manufacturer is to facilitate doing queries like fetching all products' prices in a particular month (can return 10k rows) will avoid costly table joins if I have a table with the product details and a foreign key in another table with just the time-cost data.

Is this a good reason for doing what I've described? Will using hstore make it easier to do such queries? Or did I get it all wrong?

Thanks!!

If you mix the history data in with the current like that in order to speed up queries over a time period, you do so at the expense of slowing down queries for current data. You can add an extra column to explicitly mark the relevant rows as the current prices (and have id+flag as the PK) but that adds extra work to your business logic to both keep it maintained and respect it in all reports.

Of course if you move the price completely out of the main table you have a similar problem in finding the latest price becoming more expensive unless you have a "latest" flag or do-normalise slightly and keep a copy of the current price in the main table as well as the price history table. Personally I would do the latter, and use a trigger on that product table to automatically update the price history table when a new product is added or the price updated (I'm assuming the products tables does no see massive write activity most of the time so the performance impact of using a trigger here should be minimal) in order to remove that auditing task from your other logic to avoid bugs caused by new code turning up that forgets to update the history/audit.

Caveat: I'm answering this from a general PoV as I've not used postgres much in anger, so do some benchmarks before taking anything I've said regarding performance as fact in that system!


Can't connect to/from SQL Server 2008 from my Windows 2008 server I have a server with:

Windows 2008 Server
SQL Server 2008 enterprise

From my server using SQL Server Management Studio, I can't logon to any SQL Server and vice verse, I can't connect to my SQL Server from any pc/laptop.

I checked and see that, the port 1433 closed, even if I turned of firewall, I still can't connect to it.

My server name is: S143343. I have 2 sql server instances is MSSQLSERVER and S143343 (the same name with server name for replication). But I can't start S143343.

One more thing, from my server, I just connect to sql server by server name is localhost or dot (.). No way to access by computer name or IP.

Can you show me the way to solve this problems?

I'd check Services to ensure both are running - the name in Services for both SQL Servers will also confirm the instance name. On my laptop, I have MSSQLSERVER, so I can connect to that with either the machine name (LAPTOP), or with .    My other instance is called SQLTEST2, so I can connect to LAPTOP\SQLTEST2.

If that fails, go looking in both sets of binaries in Program Files. Each instance will have ERRORLOGs in the the respective log folders - that might have a clue in there, such as a login error.

Incidentally, since they are both on the same machine, ensure Shared Memory is enabled for both.


(Beginner) MYSQL: What can I do about this where statement? Suppose I have some table named tableA and column1 in tableA has some null values and strings.
Also suppose I had created some temporary table named tempTable that has only 1 column. And that 1 column contains null values and strings as well.
I have a statement similar to the following:
select *
from tableA
where tableA.column1 in (select * from tempTable);

However, I want it to also select the contents when tableA.column1 is null. The in operator ignores that case.
Is there a different operator that considers null?
Or is there anyway I can apply certain constraints in the where clause under true or false conditions.
Like for example in the incorrect statement below:
select *
from tableA
where (if condition1, then tableA.column1 =.... elseif condition2 then tableA.column1 =....);

SELECT tableA.*
FROM tableA
JOIN ( SELECT DISTINCT column1
       FROM tempTable ) temp ON tableA.column1 <=> temp.column1

<=> is null-safe compare operator. It assumes that NULL is equal to NULL whereas regular comparing operator = considers them unequal.
Subquery is used for to collapse equal values in tempTable - if not then each value from tableA may be selected a lot of times.


Auto created Statistics vs Manually created Statistics Is there any difference between the Statistics that got created automatically by Query optimizer and the one that created with CREATE STATISTICS statement.

Will the manually created statistics get auto updated when AUTO_UPDATE_STATISTICS is ON. Is there any advantage one over another. I went through the Google and MSDN didn't get much about the difference.

Why am asking here is we are maintaining a script that we used to create statistics manually after each create table script. But in few scripts we missed to keep the statistics script so few statistics got generated automatically. Will this have an impact

References

STATISTICS
UPDATE STATISTICS

The only major difference is that SQL Server will only ever create single column statistics. You can create multi-column and filtered statistics. If any of the statistics you created were those types, they may be important. If not, SQL Server is likely to have created system statistics on those single columns, when deemed useful to a query.

Unfortunately you have to do a bit of digging to figure out which statistics your queries are using.

See here for checking the plan cache.

See here for checking queries as you execute them.  Note in the comments that on 2014+ you require a different trace flag.


postgres full text search modifies ts_query I have following query:

select
  vec.vec,
  vec @@ to_tsquery($$'fabr':*A$$),   -- true
  to_tsquery($$'fabr':*A$$),          -- fabr
  vec @@ to_tsquery($$'fabry':*A$$),  -- false
  to_tsquery($$'fabry':*A$$),         -- fabri <-- "i" instead of "y"
  vec @@ to_tsquery($$'fabryc':*A$$), -- true
  to_tsquery($$'fabryc':*A$$)         -- fabryc
from (
  select setweight(to_tsvector('english', coalesce('fabryczna')), 'A') as vec
) vec

It more details this query converts "fabry" to "fabri":

select to_tsquery($$'fabry':*A$$) -- will return "fabri" instead of "fabry"

Is it happening because I am using "english" dict while querying with "polish" word?
Is it possible to avoid this situation without installing polish dict? Is there any option to just force pg to match EXACT characters? (I don't want to use like/ilike)

If you don't want the polish library you can use the simple dictionary which will keep the y also:

karoly> select to_tsquery('simple', $$'fabry':*A$$);
+--------------+
| to_tsquery   |
|--------------|
| 'fabry':*A   |
+--------------+


MySQL 8.0.17 on Windows - I forgot my root password, dont need to keep any previous DBs I have previously used MySQL for a class about a year ago and not since. I've forgotten the password and I need to use it again.  I don't need to be able to access any previous databases, so I tried stopping the service in command prompt and uninstalling then re-installing MySQL but this still resulted in me getting stuck at the "Accounts and Roles" section of the MySQL Installer where it asks for my root password.

Is there a way I can reset it or start a new root account all together? I've seen tutorials for Ubuntu but I'm not knowledgeable enough yet to translate this to windows.

It's possible re-initialize the data directory to produce an empty MySQL instance. Here is the documentation describing the process: https://dev.mysql.com/doc/refman/8.0/en/data-directory-initialization.html .

It tells the command to perform is:

bin\mysqld --initialize --console

or the following if you want to connect as root without password:

bin\mysqld --initialize-insecure --console

The data currently in the data dir would be overwritten. If there still is a chance it might be needed in future, you may find datadir param in my.cnf and backup the directory before proceeding to re-initialization.

Another possible option is to uninstall MySQL, remove or rename datadir as found in my.cnf and install again. It shouldn't ask for the old root password then.


postgres query performance: view vs function I have a scenario where I need to run a payroll report. The report calculates the payroll amount, grouped by staff member, for a specific date range.

For example, when running the report for 2016-11-01 to 2016-11-30, I would see the following result:

Staff Id    Total
------------------
1           123.00
2           439.22

I'm using the following query for the above report:

select
    user_id as staff_id,
    sum(amount) as total
from transaction
where
    business_id = <business_id> and
    type = 'staff' and
    kind = 'commission' and
    created_at between <start_date> and <end_date>
group by
    user_id;

I'm trying to determine the best way to optimize the performance of this query given the following requirements:

Results will vary based on the business_id, start_date and end_date
Data should always be fresh

It appears both views and functions would do the job, but I'm not 100% on which is the best approach given the requirements.

Sidenote: it would be great to cache the data based on the parameters mentioned above, but it seems like there isn't a great solution on the database side. Correct me if I'm wrong!

Additional information:

I'm running Postgres 9.6
I have indexes on the business_id, type, kind, user_id and created_at columns in the transaction table. These are all single column, btree indexes.

A view cannot help you producing an aggregate based on unknown parameters (business_id, start_date and end_date).  It is nothing else than a given query, stored permanently in the database for later reuse.  (Well, the implementation is more complicated, but that does not affect their usage.)

Considering your query, the furthest you can get with a view is

CREATE VIEW staff_commission AS
SELECT
    user_id AS staff_id,
    business_id,
    amount,
    start_date,
    end_date
FROM transaction
WHERE
    type = 'staff' AND
    kind = 'commission';

Everything that is known beforehand is there, plus the columns you need for producing the desired output.

For obtaining the latter, you have to create a function in any case (optionally, working from the view).  Building on the view makes sense when you have several queries that filter the date the same way.

The performance of all these will be the very same.  If the indexes makes sense or not depends very much on the actual data.  (I am pretty sure you don't need all of them, though.)  Without knowing anything about those, it is very hard to guess which improvements are needed.  Try what you have, check the EXPLAIN ANALYZE output and see if there is something missing.

Finally about caching: PostgreSQL is very smart about this.  Going into details would fill a chapter or two in a book, but I would not worry about this until I see too many disk reads (which can be seen from EXPLAIN (ANALYZE, BUFFERS)).


In SQL, is it composite or compound keys? About SQL (Computing/Databases):

When we have two or more fields in a table that, together, identify its records uniquely, what's the proper way of calling them? Composite or compound keys?

I've seen on the web both uses so I'm not really sure.

Both composite key and compound key describe a candidate key with more than one attribute. According to the Relational Database Dictionary (C.J.Date) they mean the same thing.

In ER modelling the term "compound key" also has a more specific meaning. It means a key whose constituent attributes are references to keys in other entities - i.e. a compound key forms an identifying relationship. For most purposes, this isn't an especially useful or important concept so the terms composite/compound are often treated as interchangeable. It's probably best to stick to "composite key" unless you are referring specifically to the ER modelling concept of a compound key.


Can a 2008 SSIS package Connect to a Newer SQL Instance? I am working on a project to upgrade a SQL Server instance from 2008 R2 to 2016.  This server is also running SSIS 2008.  Based on the MS documentation, it appears that different versions of SSIS can co-exist on the same server.  I would like to take advantage of this by leaving SSIS 2008 in place, yet also installing SSIS 2016 side-by-side and slowly migrating the packages to the new version.

What I am unclear on is this:

Can an SSIS 2008 instance/package connect to a SQL 2016 database (or 2012 or 2014)?  I’ve seen a lot about backwards compatibility, but not forwards.  Is anyone else doing this type of thing?

Any help would be greatly appreciated.  Thanks!

SSIS can connect to many different servers based on the connection type used in the SSIS package. Since even SQL Server 2016 SSIS still uses Native Client 11 (and has since 2012), you should not have any issues connecting from SSIS 2008 R2 to SQL Server 2016. SQL Server 2008 R2 uses Native Client 10 or 10.1 but has no issues connecting to SQL Server 2016 either.

In the connection manager in SSIS, you choose the way to connect to SQL Server. It could be ODBC, OLE DB, or any number of connections types. I say this to say SSIS itself does not care what version of SQL Server you are connecting to but the task you use will determine the connection type. I would assume you are using OLE DB and as such you may choose the provider in the connection string.


Compare two sets with a quantity field Imagine I have a basket from an online shop, with id of item and the quantity.
I have a list of items of quantity and I would like to find a basket with the exact same items and quantity for each.

I have found solutions to find matching sets, but not with duplicates or quantity field.

The basket could contain multiple times the same item id, or I could also choose to have a quantity field, depending on what's better

thanks

two solutions I see for database format :

id_basket id_product quantity
1           1           2
1           2           1
2           1           2
2           2           1
3           1           1
3           2           1

or

id_basket id_product
1           1
1           1
1           2
2           1
2           1
2           2
3           1
3           2

basket 1 and 2 contain the same thing, but not the third, and I would like to be able to find a basket that contains a list of product using "raw" data, so the idea is not to find duplicates on the database, but find one that matches a static list (like find a basket that contain 2x item 1 and 1x item 2)

You could create a query that first lists or groups elements as in your first example. Then select on that (as a subquery) grouping by basket and crating a string per basket through GROUP_CONCAT(). Then you can use those results (perhaps as another subquery) grouping by the group_concat string and having count(*) > 1 to find baskets with equal contents.