RAISERROR on a trigger. What will happen? Will we get informed? What will happen if a trigger on a table hits the part of the trigger that includes the RAISERROR code below?

RAISERROR('Attempt to modify supposedly immutable number.',16,1)

How will we know this code was executed?

The errors generated by RAISERROR operate the same as errors generated by the Database Engine code. The values specified by RAISERROR are reported by the ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, ERROR_STATE, and @@ERROR system functions. When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block. The error is returned to the caller if RAISERROR is run:

Outside the scope of any TRY block.

With a severity of 10 or lower in a TRY block.

With a severity of 20 or higher that terminates the database connection.

If you are in trigger context, all errors terminate the batch and roll back the transaction on the spot.

When it comes to error handling in SQL Server, no rule is valid without an exception. Errors you raise yourself with RAISERROR do not abort the batch, not even in trigger context

There is one more way that a trigger can terminate the batch. This happens if @@trancount is 0 when the trigger exits. A trigger always executes in the context of a transaction, since even if there is no multi-statement transaction in progress each INSERT, UPDATE and DELETE statement is its own transaction in SQL Server, and the trigger is part of that transaction.
Doc


How to retrieve a column which is not included in group by I have tried the CTE and subquery approaches in my query below but it didn't work.

I have two tables with two columns; I would like to use only one column  for the join condition, union all, and group by as per the requirement. I would like to fetch the other column without mentioning it among the group by columns. How can achieve this?

SELECT BusnPartClientSuppContractId
    ,DivBranchId
FROM (
    SELECT A.BusnPartClientSuppContractId
        ,A.DivBranchId
    FROM tblBusnPartClientSuppContractDivBranch AS A
    WHERE A.BusnPartClientSuppContractId IN (
            SELECT BusnPartClientSuppContractId
            FROM @BusnpartIdsOfOtherContracts
            )
        AND A.BusnPartClientSuppContractId NOT IN (
            SELECT TOP 1 BusnPartClientSuppContractId
            FROM @SuppClientDivBranch2
            )

    UNION ALL

    SELECT B.BusnPartClientSuppContractId
        ,B.DivBranchId
    FROM @SuppClientDivBranch2 AS B
    ) tmp
GROUP BY BusnPartClientSuppContractId
    ,DivBranchId
HAVING COUNT(*) = 1
ORDER BY DivBranchId

I need BusnPartClientSuppContractId but I dont want to include it in the group by clause.

You can use a window aggregate function in a subquery (derived table or CTE, that's not important):

SELECT BusnPartClientSuppContractId,
       DivBranchId
FROM (
    SELECT BusnPartClientSuppContractId,
           DivBranchId,
           cnt = COUNT(*) OVER (PARTITION BY DivBranchId)
    FROM (
        -- the tmp subquery as it is, no change
        ) tmp
    ) t
WHERE cnt = 1
ORDER BY DivBranchId ;

Unrelated to the question but the TOP 1 subquery is not deterministic because it doesn't have an ORDER BY. So the whole query is not deterministic and may yield different results per execution, even if the data don't change.


SQL Agent and DTSX SSIS package: How to get it to email or ftp the output file? (SQL Server 2016) I have a dtsx, and can get SQL Agent to run it as a package on the file system.

So that drops the resulting xlsx to disk.

How can I get that xlsx emailed or sftp'd somewhere?

If you're not up to modifying the SSIS package to incorporate email or SFTP, you could add an additional step to the SQL Agent job.

A Transact-SQL script step could be used to call sp_send_dbmail to email the file to the appropriate recipient.

An Operating System step could be used to run an SFTP client (e.g. WinSCP) to transfer the file (though this would probably be better done in another SSIS package--example).


DB backups for production to DR without creating jobs in prod I had two servers(SQL 2005) A and B. B is Production server and A is DR. I want to take backups for B server databases from A server. We can not create any maintenance plans or jobs in B production server. We can create any jobs in DR server to take backups for Prod server. Is it possible? both are in different domain. If the servers are in same domain is it possible? and please send the procedure.

Please help me asap.

I suggest you use Ola Hallengren's maintenance solutions stored procedures to accomplish what you are doing.

These stored procedures will allow you to backup via simple SQL scripts. You could then create a Job from the SQL Server Agent to run those backups once or on a scheduled basis.

Simple Example from the website:

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y',
@CleanupTime = 24

This process will allow you to save your backups to a remote storage or locally.

You could also run this same process using sqlcmd to connect remotely to the server you are trying to backup

sqlcmd -S <ComputerName>\<InstanceName> -i <MyScript.sql> -o <MyOutput.rpt>

And MyScript.sql would contain the backup script from above.

As long as the machine running the script is in same domain as the instance being backed up, there is no need to specify the user and password. This assumes that your domain login has the necessary server permissions to run a backup.

You must install the maintenance stored procedures before using them to backup, so please be sure to carefully read all instructions at this page.


SQL Server creates different plans when OR condition is re-arranged I was reviewing an under-performing query which looks like this:

WHERE manymany.Active = -1
  AND manymany.Check1 = -1
  AND manymany.WebsiteID = @P1
  AND CURRENT_TIMESTAMP BETWEEN ISNULL(manymany.FromDate, '1950-01-01') AND ISNULL(manymany.UptoDate, '2050-01-01')
  AND main.Active = -1
  AND main.StatusID = 1
  AND CURRENT_TIMESTAMP BETWEEN main.FromDate AND ISNULL(main.UptoDate, '2050-01-01')
  AND (main.TextCol1 IS NOT NULL OR main.TextCol2 IS NOT NULL)
ORDER BY aux.SortCode

I accidentally used SSMS query designer on this query and it re-wrote the query as follows:

WHERE manymany.Active = -1
  AND manymany.Check1 = -1
  AND manymany.WebsiteID = @P2
  AND CURRENT_TIMESTAMP BETWEEN ISNULL(manymany.FromDate, '1950-01-01') AND ISNULL(manymany.UptoDate, '2050-01-01')
  AND main.Active = -1
  AND main.StatusID = 1
  AND CURRENT_TIMESTAMP BETWEEN main.FromDate AND ISNULL(main.UptoDate, '2050-01-01')
  AND main.TextCol1 IS NOT NULL

   OR manymany.Active = -1
  AND manymany.Check1 = -1
  AND manymany.WebsiteID = @P2
  AND CURRENT_TIMESTAMP BETWEEN ISNULL(manymany.FromDate, '1950-01-01') AND ISNULL(manymany.UptoDate, '2050-01-01')
  AND main.Active = -1
  AND main.StatusID = 1
  AND CURRENT_TIMESTAMP BETWEEN main.FromDate AND ISNULL(main.UptoDate, '2050-01-01')
  AND main.TextCol2 IS NOT NULL
ORDER BY aux.SortCode

If you look closely you'll notice that it simply expanded the OR condition by repeating all conditions i.e. it changed a AND (b OR c) to (a AND b) OR (a AND c).

The resulting query was 50% smaller in terms of cost and 33% smaller in terms of execution time. I simply do not understand why re-arranging the OR condition changed the plan when both queries are identical (?). I could have expanded the OR condition myself by copy-pasting the conditions but why should I?

Paste the plan and screenshot:

Number of rows:

main     2718
manymany 188761
aux      19

Notes:

TextCol1 and TextCol2 are text datatype and cannot be indexed
There are avg. 170.20 records in manymany table per website id

But why doesn't SQL server sees both queries as one? After all, a AND
  (b OR c) = (a AND b) OR (a AND c)?

Logically it is the same, and it will get the same results.

Assumptions

My assumptions are that for the 'faster' plan, the optimizer is not considering some filter statements at the top of the OR to be the same as some filter statements at the bottom.  I might be entirely off base here.

the reasoning for getting these assumptions are based on this filter predicate:

This filter predicate uses the result of the join between the Main table and manymany table.

Notice that EXPR1021 and EXPR1022 in this filter are expressions  created from the scalar operator on the manymany table.

This filter  consists of two parts, the first one with (.. AND .. OR .. AND ..)
and the second one plain AND filtering

(getdate()>=[Expr1021]
AND getdate()<=[Expr1022]
AND getdate()>=[DB1].[dbo].[main].[FromDate]
AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000')
AND [DB1].[dbo].[main].[TextCol1] IS NOT NULL
OR getdate()>=[Expr1021]
AND getdate()<=[Expr1022]
 AND getdate()>=[DB1].[dbo].[main].[FromDate]
 AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000')
 AND [DB1].[dbo].[main].[TextCol2] IS NOT NULL)

 AND (getdate()>=[DB1].[dbo].[main].[FromDate]
 AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000')
 AND [DB1].[dbo].[main].[TextCol1] IS NOT NULL OR getdate()>=[DB1].[dbo].[main].[FromDate]
 AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000')
 AND [DB1].[dbo].[main].[TextCol2] IS NOT NULL)

As you can see, the only difference above and below the OR in the first part of this filter is

AND [DB1].[dbo].[main].[TextCol1] IS NOT NULL

VS

AND [DB1].[dbo].[main].[TextCol2] IS NOT NULL

And the second part needs to be true no matter what, as they are AND predicates without any OR's.

Resulting in extra calculations of the same functions, that in my opinion are not needed.
Again, my guess here is that the reason that sql server does these calculations is that it does not know that they are the same.

For some other parts of the where clause, it does know that these are the same, e.g. In the main table, the statusid = 1 is only evaluated once:

And in the manymany table, the same statement is evaluated twice:

In the 'slow' plan, the statements are not added together with OR clauses, and that is why the optimizer is generating a different plan, applying filter predicates on the tables separately (and no duplicate filters).

End of assumptions

Comparison of the two plans

I think that you got lucky with the performance of the 'fast' plan, but that the 'fast' plan might turn ugly when the matching data increases. It can depend on where and when you are applying your filters (and other factors).

The fast plan filtering

In the 'fast' plan: sql server applies some of the filters after the join of
  the main table with the manymany table as a result of different combinations with the two OR's  + (AND ... AND ... AND...) blocks.
The columns from the maintable are filtered after finding all the possible combinations with the manymany table.

As a result, the same predicate is executed twice on the manymany table:

For the predicates above and below the OR.

But this is not the case for some of the seek predicates on the main table

After this the join happens, and an even bigger filter predicate on the results of the join between main and manymany happens, again for all the possible combinations

Notice that EXPR1021 and EXPR1022 in this filter are expressions  created from the scalar operator on the manymany table.

This filter  consists of two parts, the first one with (.. AND .. OR .. AND ..)
and the second one plain AND filtering

(getdate()>=[Expr1021]
AND getdate()<=[Expr1022]
AND getdate()>=[DB1].[dbo].[main].[FromDate]
AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000')
AND [DB1].[dbo].[main].[TextCol1] IS NOT NULL
OR getdate()>=[Expr1021]
AND getdate()<=[Expr1022]
 AND getdate()>=[DB1].[dbo].[main].[FromDate]
 AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000')
 AND [DB1].[dbo].[main].[TextCol2] IS NOT NULL)

 AND (getdate()>=[DB1].[dbo].[main].[FromDate]
 AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000')
 AND [DB1].[dbo].[main].[TextCol1] IS NOT NULL OR getdate()>=[DB1].[dbo].[main].[FromDate]
 AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000')
 AND [DB1].[dbo].[main].[TextCol2] IS NOT NULL)

As you can see, the only difference above and below the OR in the first part of this filter is

AND [DB1].[dbo].[main].[TextCol1] IS NOT NULL

VS

AND [DB1].[dbo].[main].[TextCol2] IS NOT NULL

And the second part needs to be true no matter what, as they are AND predicates without any OR's.

Resulting in extra calculations that in my opinion are not needed.

The slow plan filtering

In the 'slow' plan: sql server applies the filter directly to the
Main table as a result of the AND (TextCol1 IS NOT NULL OR TextCol2 IS NOT NULL) part and then joins with the manymany table to filter out the rest to get to 15 rows.

Main table filters

manymany table filters

Some other, sometimes overlapping information:

The slower plan

When we look at the slower plan, the clustered index PK_main is used, into a compute scalar, filter  and nested loops operator:

When we compare this with the estimated rows to be returned, we see a difference:

It is estimating 93 rows to be returned by the predicate on the scan:

Which is actually about 20x less than what was expected, which is 1947 rows.

Afterwards, the Compute scalar or this statement:

 , CASE WHEN TextCol1 IS NOT NULL OR TextCol2 IS NOT NULL THEN -1 ELSE 0 END AS MoreFlag
 , CASE WHEN Stars BETWEEN 1 AND 5 THEN Stars END AS Rating

is evaluated on these 1947 rows.

Then the filter operator (main.TextCol1 IS NOT NULL OR main.TextCol2 IS NOT NULL) to reduce it to 1374 rows.

After this, join these 1374 rows to the dbo.manymany table to get 15 rows returned.

The faster plan

The faster plan is using the NC index: CVR_main_4 on the dbo.Main table,

It is filtering with a seek predicate, returning 27 rows to the nested loops Join operator, again joining with the dbo.manymany table.

And the actual rows returned are even lower than the estimated rows:

27 actual rows for an estimate of 152 rows

Filtering

A big difference is where the filtering happens, where on the 'slower' plan this is being done directly on the dbo.Main table:

With the predicate: TextCol1 IS NOT NULL OR TextCol2 IS NOT NULL

And is applying this filter to 1943 rows.

With the other filtering happening directly on the dbo.manymany table

(seek) predicates on dbo.manymany

While the other OR, on the 'faster' plan, is being filtered after the join from dbo.Main to dbo.manymany, and results in a much bigger filter, on the 27 rows.

Much bigger filter with multiple OR's on 27 rows.

Another difference is the Key lookup operator:

which gets 10 additional columns from the clustered index, but only has to do this for 27 rows.

Another reason that the optimizer chooses the 'slower' plan might be because the optimizer  thinks not looking up the other columns would be better.

Is the fast plan even faster, or always going to be 'faster'?

I do think that, if the data passing through the filter increases, the 'slow' plan will be better. Not only due to the key lookup, but also due to the bigger filter operator further down in the plan.

If that happens, next  to indexing. You could improve the filtering by splitting the query into multiple parts using a UNION statement.

Like so:

SELECT main.MainID, Title, Column1, Column2, Column7, Column4, Column6, Column3, Column5
     , CASE WHEN TextCol1 IS NOT NULL OR TextCol2 IS NOT NULL THEN -1 ELSE 0 END AS MoreFlag
     , CASE WHEN Stars BETWEEN 1 AND 5 THEN Stars END AS Rating
FROM manymany
INNER JOIN main ON manymany.MainID = main.MainID
LEFT JOIN aux ON manymany.AuxID = aux.AuxID
WHERE manymany.WebsiteID = @P1
  AND manymany.Check1 = -1
  AND manymany.Active = -1
  AND CURRENT_TIMESTAMP BETWEEN ISNULL(manymany.FromDate, '1950-01-01') AND ISNULL(manymany.UptoDate, '2050-01-01')
  AND main.Active = -1
  AND main.StatusID = 1
  AND CURRENT_TIMESTAMP BETWEEN main.FromDate AND ISNULL(main.UptoDate, '2050-01-01')
  AND TextCol1 IS NOT NULL

 UNION

 SELECT main.MainID, Title, Column1, Column2, Column7, Column4, Column6, Column3, Column5
     , CASE WHEN TextCol1 IS NOT NULL OR TextCol2 IS NOT NULL THEN -1 ELSE 0 END AS MoreFlag
     , CASE WHEN Stars BETWEEN 1 AND 5 THEN Stars END AS Rating
FROM manymany
INNER JOIN main ON manymany.MainID = main.MainID
LEFT JOIN aux ON manymany.AuxID = aux.AuxID
WHERE manymany.WebsiteID = @P1
  AND manymany.Check1 = -1
  AND manymany.Active = -1
  AND CURRENT_TIMESTAMP BETWEEN ISNULL(manymany.FromDate, '1950-01-01') AND ISNULL(manymany.UptoDate, '2050-01-01')
  AND main.Active = -1
  AND main.StatusID = 1
  AND CURRENT_TIMESTAMP BETWEEN main.FromDate AND ISNULL(main.UptoDate, '2050-01-01')
  AND TextCol2 IS NOT NULL
ORDER BY SortCode;


Transaction log is full due to 'CHECKPOINT' for DB in simple recovery model For one of our DB's on SQL server 2012 RTM,

We could not perform almost any troubleshooting step as the error says:

" Transaction log is full due to 'CHECKPOINT"

This being a dev database, its backup never happened, so we are out of option with that even.

Tried shrinking the log file but no success and got below error:

log is out of space - with a cascade error that the transaction log is full due to 'CHECKPOINT'

DBCC CHECKDB mostly just gives errors because the transaction log is full due to 'CHECKPOINT'

I can't change the recovery model to full - get an error because the transaction log is full due to 'CHECKPOINT'

Cannot take backup: same error:

Even tried detach and attaching back on other server (SQL server 2012 RTM) , but same error:

Tried Manually doing CHECKPOINT on the DB, but still same error:

Have already refred to this post here Simple model database transaction log full 'CHECKPOINT'

and

The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT', but no success.

Note: We do not have replication set up on the current server or any database.

Please help as i am unable to find a root cause and fix for this!

Thanks!

(since this is longer than a comment ... so putting it as answer)

  For one of our DB's on SQL server 2012 RTM.

This is your problem. There were many fixes introduced in the consecutive CUs / SPs after the RTM build.

Can you patch the sql server with the latest SP2 ? After patching, check if the issue still happens or not.

Your problem might be because your MODEL database might be in simple recovery.

If the model database is set to SIMPLE recovery model and user database is created with the simple recovery model from model database template, SQL Server does not truncate its log automatically like it suppose to (after a full backup). It appears that somehow SQL Server is treating it as if it is in full recovery model.

you can execute below log backup (even though your database is in simple recovery - as since model is in simple recovery - due to a bug in RTM, sql server treats it as being in FULL recovery)

BACKUP LOG dbName
TO DISK = 'dbName_log_backup.trn'
GO

Check this KB: Database does not follow simple recovery model behavior in SQL Server 2012 after you set the recovery model of the "model" database to "Simple"


SQL Server 2014 - Reset everything Short of reinstalling the software, is there a simple way to 'start from scratch' with a SQL instance? By that I mean:

Remove all created logons,
Remove all added databases,
Reset system databases,
Remove any new configuration,
Reset any SQL Server Agent configuration

I can't find anything online on how to do this.

You should rebuild the system databases.

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]

This would not remove the data and log files relating to the old databases - it just removes all references to them. You would have to manually remove the files to free up the disk space.

You must also install all service packs and hotfixes again.


mysql max_connections limit reset Last week I updated mysql max_connections to 1000 from 151 using

use mysql;
set global max_connections = 1000;

Today there was a power outage at the hosting company and the servers shut down and were then restarted.  After restart, the max_connections variable was reset to 151.

Is this default behavior?  How can I prevent max_connections global from getting reset?

Reference:

MySQL 5.7

Yes this is the default behavior. How ? The default value for max_connections is 151.

This is true for MySQL 5.7, MySQL 5.6, and MySQL 5.5

If /etc/my.cnf does not have max_connections, then 151 comes up as max_connections.

You can just run set global max_connections = 1000;.

Your hosting company must put that entry in /etc/my.cnf for you

[mysqld]
max_connections=1000

Otherwise, every restart of mysqld will reset max_connections to 151.

I have suggested this before

Jul 06, 2012 : How to resolve too many connections and fatal error in mysql running on vps


MSSQL. Tree structure fetching with conditions I need to make a tree structure
Table:

create table Dirs
(
    Id           uniqueidentifier primary key not null default (newid()),
    Name         nvarchar(30)                 not null,
    CreatedDate  datetime                     not null default getdate(),
    ModifiedDate datetime                     not null default getdate(),
    IsCollapsed  bit                                   default dbo.randbit(newid()),
    ParentId     uniqueidentifier                      default null
)

I want to fetch all child nodes from specific node (by id for example), but filter out closed directories' children keeping them itself in output?

e.g. given structure

root
   - Dir1 (v)
      -Dir2 (v)
         -Dir3(x)
            -Dir4(v)
               -Dir5(v)

where x - means that directory is collapsed, v - means that directory is expanded.

Expected output is:

Dir1
Dir2
Dir3

I have written such query:

create procedure GetNodes @parentId uniqueidentifier = null
as
begin
    with dirsrec as (
        select *
        from Dirs
        where Id = @parentId
        union all
        (
            select d.Id, d.Name, d.CreatedDate, d.ModifiedDate, d.IsCollapsed, j.id as ParentId
            from Dirs d
                     join dirsrec as j on d.ParentId = j.Id
            where d.IsCollapsed = 'false'
        )
    ),
         dirsec2 as (
             select *
             from Dirs
             where Id = @parentId
             union all
             (
                 select d.Id, d.Name, d.CreatedDate, d.ModifiedDate, d.IsCollapsed, j.id as ParentId
                 from Dirs d
                          join dirsrec as j on d.ParentId = j.Id
                 where d.IsCollapsed = 'true'
             )
         )
    select *
    from dirsrec
    union
    select *
    from dirsec2
end

It uses 2 recursions. The firstis fetching all opened nodes from given one, the second fetching all closed nodes and then output gets distincted
It works, but I dislike 2 separated queries with almost identical code. I guess it might be done much better

I tried to make a side-effect (Inserting collapsed nodes from within union all( subquery into temp table and then just union it with final result, but it looks like sql doesn't allow us to make some logic within subqueries)

WITH cte AS ( SELECT *
              FROM tree
              WHERE id = @starting_node
                  UNION ALL
              SELECT tree.*
              FROM tree, cte
              WHERE tree.parent_id = cte.id
                AND cte.IsCollapsed = 'expanded' -- if collapsed then stop
            )


DBCC CHECKDB ERROR Msg 8992 I have got the Following Error When i ran the checkDB over the database.

Msg 8992, Level 16, State 1, Line 2
Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=645577338,referenced_minor_id=7) of row (class=0,object_id=821577965,column_id=0,referenced_major_id=645577338,referenced_minor_id=7) in sys.sql_dependencies does not have a matching row (object_id=645577338,column_id=7) in sys.columns.
Msg 8992, Level 16, State 1, Line 2
Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=757577737,referenced_minor_id=7) of row (class=0,object_id=821577965,column_id=0,referenced_major_id=757577737,referenced_minor_id=7) in sys.sql_dependencies does not have a matching row (object_id=757577737,column_id=7) in sys.columns.

What are the steps can be taken to resolve the issue. Without affecting the users?

Take a look at the following KB article:  "Msg 8992" error message and inconsistent metadata in the SQL Server system catalogs

It looks like you have an inconsistency in the sys.sql_dependencies system catalog view.  As per the above KB article you can do one of two things to resolve this:

Restore a backup that doesn't have the current inconsistency
Create a new database and move all of the objects and data into the new database

Please see the above referenced KB article for further details.


implement geo / spatial / proximity search with php and mysql --- what have you done successfully For my website's users, I want to implement a worldwide geo / spatial / proximity search with php and mysql.

I want to do this as simply and minimally as possible.

What is the minimum data I should capture (Country, State, City, Postal Code, etc.)?

There are dozens out-of-the-box and open-source solutions out there, with varying reviews. What have you tried that worked or did not work?

It's quite easy to implement such a thing for the U.S. (e.g., Google has some Zip code related proximity-solutions), but apparently if I want to go global, it's an order of magnitude more difficult. Have you tried (and was it worth-it) to code it from scratch?

I think using Latitude and Longitude is the best bet. Because when you go global, then zipcode might not be very accurate search. There is a very good article about using spatial indexes and the Point type database fields. You can read here
http://vinsol.com/blog/2011/08/30/geoproximity-search-with-mysql/


MySQL join on hamming distance I have a  table t1 with 2 columns, col1 has all possible combinations of strings of length 13 with alphabets of {1,2,3} and col2 has some integer X. Thus table has 3^13 rows in total.

For each of the strings I would need to know which other strings are exactly within a hamming distance of say 3 and get the sum of col2. So each of the strings have 2288 ( (13,3)*2^3) other strings with a hamming distance of 3.

My solution to this was to create table t2 with all possible mappings for each of the strings. So this table now has 3^13*2288 rows in it with columns col1 similar to t1 and col2 has all the strings within desired distance of string in col1. Then I would index both of tables on col1 and join them together on col1 and sum col2 on tbl1 and group over col2 in t2.

SELECT t2.col2,sum(t1.col2) FROM t1 JOIN t2 ON t1.col1=t2.col1 GROUP BY t2.col2

Problem is that when I try to execute this query it runs for ages and after 10 mins I get a timeout message from server.

My server is community version of MySQL 5.7 with standard configuration.

Whats wrong with this solution and/or configurations because it takes so long process this?

The reason why your query is so slow to execute, is because MySQL has to create a temporary table to sort t2 for grouping.

However if you create an index on the grouping column, MySQL doesn't need to create a temporary table, it can just use the index to access the rows in the correct order.

In this case I would suggest to use the primary key for this, saving the overhead of an additional index on disk. The data will be saved in the correct order already.

On t2 set the primary key to (col2, col1) (order is important here). t1 would just have the primary key as (col1).

To run your query try adding an ORDER BY NULL clause as well. This will prevent MySQL from doing an implicit order by during grouping.

You will probably still hit the 10 min timeout - depending on the hardware you use. You might try to use the SELECT ... INTO OUTFILE (docs) syntax to prevent sending the 1.5 million rows to the client. This query will then still finish even with your client connection timing out.

To save some disk space you should also think about adding an integer column as the primary key to t1 and reference this key only in both columns of t2, instead of saving the whole varchar twice there as well.


Enforce 1 or many relationship In the er diagram below (not mine),

A Class can have many instructors via a standard junction table.
Is there a way of designing the Instructors/Classes tables using constraints to ensure that a Class must have at least one Instructor? I.e. 1 or many instead of 0 or many? So given Classes.class_id=1, there must be an Instructors_Classes(class_id=1, instructor_id=?)
Or is this something that you'd just use triggers to enforce?

As a secondary, can anyone suggest a good way of identifying one, and only one Instructor as the primary instructor for a class?
Currently I've added a field Instructors_Classes.IsPrimary (number) and applied a unique constraint on (class_id, IsPrimary) and a validation constraint of IsPrimary=Null OR 1, so only 1 instructor can have the value 1 per Class.
Then when I want to get a list of all Classes, I can just join on Instructors_Classes.class_id WHERE IsPrimary=1 so that I get a single value for the Instructor of that class.
This just feels a bit wrong but I'm not a db person so I don't know if there's a more conventional way of doing it. I thought of adding a Classes.instructor_id field but couldn't work out how to enforce the link between it and the Instructors_Classes table.

Really "a Class must have at least one Instructor" constraint could be checked only at transaction commit, because first we need insert a Classes and only after at least one Classes exists we can refer it in Instructors_Classes. No, currently most DBMSes do not implement features like that. Constraints are checked per statements, this check is not defered till transaction commit. Postrgesql can defer FK and some others constraints untill commit (thanks, ypercubeᵀᴹ).
You may implement a trigger on delete from Instructors_Classes which will prevent deletion of the last instructor of the class.

But your second "primary instructor for a class" requirement helps a bit. Add Classes.PrimaryInstructor  referencing Instructors_Classes nullable. After  Classes.PrimaryInstructor value  is set this FK will ensure (with proper ON DELETE/UPDATE action) at least primary instructor for a class persists.
EDIT
To be precise above FK should be composite including also Classes PK ,

create table Classes (
...
constraint FK_prim_instr foreign key (class_id, primary_instructor) references class_instructors(class_id,instructor_id)
...
);

EDIT 2
Sample script

-- MS Sql Server 2014

create table classes (
    class_id int not null primary key
    , primary_instructor int null
);

create table class_instructors(
    class_id int not null
    , instructor_id int not null
    , constraint PK_cl_instr
      primary key (class_id, instructor_id)
    , constraint FK_class_instr
      foreign key (class_id)
      references classes (class_id)
);
go
alter table classes
add constraint FK_Prim_instr
      foreign key (class_id, primary_instructor)
      references class_instructors(class_id, instructor_id)

insert classes (class_id)
values (1);

insert class_instructors (class_id,instructor_id)
values (1, 10),(1,20);

update classes
set primary_instructor = 20
where class_id = 1;

select * from classes;
select * from class_instructors ;

-- this will fail
insert class_instructors (class_id,instructor_id)
values (2,10);

-- this will fail too
update classes
set primary_instructor = 30
where class_id = 1;


Won't start: InnoDB: Corrupted page ... Tablespace was not found After a server crash (Ubuntu 16.04), when I try to start Mysql (Mysql 5.7) using innodb_force_recovery=0 it doesn't start and error.log shows:

InnoDB: Checksum mismatch in datafile: ./panel_financiero_v2/kpis_analytics.ibd, Space ID:93, Flags: 33. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
InnoDB: Corrupted page [page id: space=93, page number=0] of datafile './panel_financiero_v2/kpis_analytics.ibd' could not be found in the doublewrite buffer.
InnoDB: Tablespace 93 was not found at ./panel_financiero_v2/kpis_analytics.ibd.
InnoDB: Set innodb_force_recovery=1 to ignore this and to permanently lose all changes to the tablespace.
InnoDB: Cannot continue operation.

I just can start Mysql using innodb_force_recovery=6 (I can't start Mysql using innodb_force_recovery=1 as the error message advises).
The .ibd and .frm files of the troubled table are in the right directory (no empty files); I even tried (just in case, with no real hope) to delete these two files (moving them to another directory) but it doesn't work neither.

Any way to repair or recover the table (bearing in mind that innodb_force_recovery=6 starts Mysql in read-only mode and I even can't create a new table)? Any way, at least, to start mysql normally (with innodb_force_recovery=0) even losing the information of that troubled table?

https://dev.mysql.com/doc/refman/5.7/en/repair-table.html says:

  REPAIR TABLE works for MyISAM, ARCHIVE, and CSV tables.

In other words, REPAIR TABLE does nothing for InnoDB. InnoDB has its own automatic crash recovery that it runs on startup.

But InnoDB crash recovery only works for lost changes that were in progress at the time of the crash. It uses the redo log and the doublewrite buffer to reconstruct lost dirty pages. This process cannot reconstruct data that was corrupted at rest.

The error message actually tells you what to do:

  InnoDB: Set innodb_force_recovery=1 to ignore this and to permanently lose all changes to the tablespace.

Read more details at https://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html:

  1 (SRV_FORCE_IGNORE_CORRUPT) Lets the server run even if it detects a corrupt page. Tries to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

Start the server with innodb_force_recovery=1 after moving your tablespace files back where they belong.

Then you can dump the table using mysqldump, and it should read the pages it can read, skipping corrupted pages. Then you can make a new table to import from the dump:

CREATE TABLE mytable_new LIKE mytable;
RENAME TABLE mytable TO mytable_bad, mytable_new TO mytable;

Then re-import your dumped data.

I assume you do not need help with dumping a single table and importing the dump file.

Re your comment:

It seems like you have more extensive corruption. I suggest the following steps:

Start the MySQL server with innodb_force_recovery=6
Use mysqldump to dump all of the data.
Shut down MySQL server.
Back up the contents of your datadir just in case you want to get professional help to see if they can recover more of the data. Try https://twindb.com, they are the best experts in MySQL database recovery.
Initialize a new datadir (I would use a new disk device, and take the old one out of service because it now has unknown damage).
Restore the data dump you created with mysqldump.


Cannot list tables (views, packages etc) from Oracle 11 database using sql explorer, but can see them using PL/SQL Developer I am connecting to oracle 11 DB.

When I connect with PL/SQL, I can explore all the objects :

However when accessing this same DB using Oracle SQL Developer, expanding the different objects results in empty list

Any ideas why this might be?

SQLDeveloper  shows tables that are owned by the logged in user. To see other users' tables, right click on connection ("dev" in your case), click "Schema Browser", and then select a user whose object you want to see.

Or directly query system views (e.g. all_tables )


SQL Server 2012 SQL Agent Job with PowerShell step fails on Out-File cmdlet with "ReportWrongProviderType" error We've recently upgraded to SQL Server 2012 (from SQL Server 2008 R2) on Windows Server 2012 (from Windows Server 2008 R2), and a SQL Server Job Agent job that was previously running without error is now failing.

The job has a Powershell job step:

$ErrorActionPreference = 'Stop'
$DateStamp = Get-Date -format "yyyyMMdd"

$BizUnit = 'SHB'

# Target File directory
$BaseDir = '\\MyFileServer\MyDirectory'

$Query = "EXEC dbo.usp_MyQuery '$BizUnit'"
$TargetFile = "MyTargetFile_" + $BizUnit + "_$DateStamp.xml"
$TargetDir = (Join-Path -Path $BaseDir -ChildPath $BizUnit)

$Query | Out-File -FilePath (Join-Path -Path $TargetDir -ChildPath $TargetFile) -Force -Encoding "utf8"

When the job is run, it fails with this error message:

  A job step received an error at line 14 in a PowerShell script. The
  corresponding line is '$Query | Out-File -FilePath (Join-Path -Path
  $TargetDir -ChildPath $TargetFile) -Force -Encoding "utf8"'. Correct
  the script and reschedule the job. The error information returned by
  PowerShell is: 'Cannot perform operation because operation
  "ReportWrongProviderType" is invalid. Remove operation
  "ReportWrongProviderType", or investigate why it is not valid.

I can't find any references to "ReportWrongProviderType" when I search the interwebz. Any ideas on what is wrong with the Out-File call?

The problem is that the PowerShell session is using the SqlServer provider by default. I added this statement into the script before the Out-File cmdlet:

Set-Location c:

This changed the provider to the FileSystem provider, and allowed the Out-File cmdlet to correctly find the file share and create the file.

This article contrasts how SQLPS loads in SQL Server 2008 R2 vs. SQL Server 2012/2014. The SQL Provider is loaded using the 'Add-PSSnapin' cmdlet in SQL Server 2008 R2,  while starting with SQL Server 2012, the 'Import-Module' cmdlet is used.

The author states: "The outcome of loading the module [with 'Import-Module'] is simply changing to the PSDrive you just loaded." Evidently, the 'Add-PSSnapin' cmdlet left the provider for its SQLPS session unchanged (as FileSystem). That change in cmdlet use, and the resulting session behavior, was what caused my problem.


Limiting Domain Admins permissions on a SQL Server I have a SQL Server 2012 machine and I want to limit the Domain Admins permissions. What can I do to make sure that the Domain Admins have read only permissions for the databases and nothing else for SQL Server?

I am interested in limiting their permissions for the SQL Server install. They can do whatever they want on the Windows Server 2008 OS.

Thank you for your help.

By default domain admins don't have access to a SQL 2012 instance, because by default builtin\administrator (which are the local admins on the machine) are not a login in SQL Server itself.

The default has changed starting 2008

  By default, the local Windows Group BUILTIN\Administrator is no longer
  included in the SQL Server sysadmin fixed server role on new SQL
  Server 2008 installations.

So unless someone added them in SQL Server the local admins/domain admins shouldn't have permissions on the instance.

That doesn't mean they can't give themselves permissions, as some case of emergency solution they can start sql in single user mode and connect but that requires a server restart.

On the other hand, on 2008 R2 this trick would still work: Think Your Windows Administrators Don’t Have Access to SQL Server 2008 by Default? Think Again


Database Design: Linking 'vague' entries to associations? Experienced developer, inexperienced DBA here.

I'm currently working on a project that lets users input the names of freight vehicles for particular packages. Eg:

Company: White Star Line
Vehicle: The Titanic
Vehicle Type: Ship

At the present time, all three of those fields are unrestricted text fields. So effectively the data is useless for anything except display. If someone enters the exact same details as above, the DB has no means of realising it's the same ship as a record with the exact same entries.

We cannot restrict any of these fields to a set of predefined values (too many potential entries, need to be open to anything), but I'd love to unify some of the more frequently used values. I'm wondering about the most elegant/efficient way of doing this.

My current line of thinking is to add an extra set of database tables with 'recognised company' & 'recognised vehicle', which work in parallel to the existing table. So if a user enters "White Star Line" as the company, and there's a pre-approved entry in the "recognised company" table, it forms an association with that company in an optional 'recognised_company_id' field.

Similarly, if the user enters White Star Line AND 'The Titanic' as ship, and there's an entry for that ship WITH that brand, it enters the association into recognised_vehicle_id.

A few things:

Autocomplete on the input fields could hopefully eliminate some spelling errors/syntax differences on identical brands/vehicles.
Reviewing a count of duplicates of the existing table could help determine which recognised companies/vehicles should be manually added.

This is far from a pretty solution, but it's the best I've got. I'm guessing this is something folks have to do quite regularly, and I'm just wondering - is there a more graceful approach to what I'm trying to achieve? Any rules of thumb for tackling this sort of issue?

Thanks in advance

One possibility would be to build a list of the values you currently have, and make users pick from that list, or enter it separately to get it on the list.

I do this all the time in various web interfaces - when I need to pay a bill through my online bank, for example, I first have to go create a payee. I curse this when I am only paying a one-time bill, but I love that I don't have to enter my account number and other details every time I make a car payment, mortgage payment or pay an electric bill - only the first time.

This will also reduce the noise from "almost" duplicates, for example if one person spells it Weber's and another spells it Webbers hopefully the second user will just use the first even if they don't think it's correct (and if they're both wrong you still only have to correct one row).

If you make users see the long-term benefit there should be no screaming about the up-front pain.


check constraint where values must be a table name Is there a way to write a check constraint such that values in a column are limited to table names currently in the schema?  It appears subqueries are not allowed (ORA-02251), but logically I am looking to do:

CONSTRAINT ck_table_name
     CHECK (src_table IN (select table_name from user_tables))

What you want is not a check constraint but an ordinary foreign key.

Normally you would do:

alter table table_name add foreign key (src_table) references user_tables(table_name);

however Oracle won't allow this as user_tables is a view, not a table.

You could try to reference the underlying Oracle data-dictionary table, but that probably won't work either -- not least because you'd need two columns at your end -- table_name and schema -- but also you'd need to be granted the privilege to reference that table.

An approach that I would consider -- as recommended above -- is to create your own table listing which tables can be referred to. You could populate this table yourself, or you could create a DDL trigger to maintain it for you!

The other thing to consider is what to do when the table is dropped: will you on delete cascade delete the record in your table, or set its contents to null using on delete set null?


Unique key constraint violation not escalated to parent stored procedure I have a stored procedure which calls 2 nested sub procedures. For Example,

MyProc
  BEGIN
    BEGIN TRY
      BEGIN TRAN
        EXEC UpdateProc
        EXEC InsertProc
      COMMIT TRAN
    END TRY
    BEGIN CATCH
      ROLLBACK TRAN
      ;THROW
    END CATCH
  END

The insert stored procedure could insert a line with duplicate values violating a unique key constraint. I'm calling the main stored procedure from a .NET application. In case of unique key violation, my application is not receiving the correct exception. The exception received is 'Specified Cast is not valid'.
I've tried adding SET XACT_ABORT ON but this did not correct the problem.

You are missing the error handling piece (i.e. TRY...CATCH). Please see the stored procedure template that I posted in the following DBA.StackExchange answer:

Are we required to handle Transaction in C# Code as well as in stored procedure


Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnDiscountPrice", or the name is ambiguous i have created fnDiscountPrice() that references the item id so that it will find the discount price for one item now I'm needing to find the total price of all the products with that ID. so i have a function that receives the ItemID and then multiplys the discount price found in fnDiscountPrice() with the same ID by the quantity. Code shown below.

CREATE FUNCTION dbo.fnItemTotal
    (@ItemID INT = 0)
    RETURNS Table

RETURN (SELECT ItemID, SUM(dbo.fnDiscountPrice(@ItemID) * Quantity) AS TimeTotal
        FROM OrderItems
        GROUP BY ItemID)

I keep getting an error of "Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnDiscountPrice", or the name is ambiguous."

Assuming that the column name returned from dbo.fnDiscountPrice is DiscountPrice you could use it like this.

CREATE FUNCTION dbo.fnItemTotal
    (@ItemID INT = 0)
    RETURNS Table

RETURN (SELECT O.ItemID, SUM(DP.DiscountPrice * O.Quantity) AS TimeTotal
        FROM dbo.fnDiscountPrice(@ItemID) as DP
          CROSS APPLY OrderItems as O
        GROUP BY O.ItemID)


Should I use a temp table or join I am creating a stored procedure in sql server 2019 that needs to use multiple select statements to get a parent row and then its related data. I have the primary key clustered value for the parent so the first query will at most return 1 row.
Should I select everything into a temp table for the first query and then join my subsequent queries to the temp or should I just keep joining to the original table?
I am not sure if the overhead of creating the temp table will overshadow the overhead of joining to the actual table repeatedly.
I have looked at the performance plans and they come out to be the same and the statistics for reads/scans and time are about the same as well.
I think what I am trying to figure out is if I use the temp table, will it relieve pressure on the original table. The original table is heavily read and written to.
I should note that these statements will be inside of a Stored Procedure so I may potentially get a boost from Temporary Object Caching.
Assume table A has > 1 million rows and has 0-10 rows per entry in TableB and 0-10 per entry in TableC

Queries without temp table
declare @taID bigint=123

select
    ta.*
from
    TableA ta
where
    ta.ID=@taID

select
    tb.*
from
    TableA ta
    inner join TableB tb on ta.ID=tb.TableAID
where
    ta.ID=@taID

select
    tc.*
from
    TableA ta
    inner join TableC tc on ta.ID=tc.TableAID
where
    ta.ID=@taID

Queries with temp table
declare @taID bigint=123

select *
into #tmpA
from
    TableA ta
where
    ta.ID=@taID

select * from #tmpA

select
    tb.*
from
    #tmpA ta
    inner join TableB tb on ta.ID=tb.TableAID

select
    tc.*
from
    #tmpA ta
    inner join TableC tc on ta.ID=tc.TableAID

as you know the PK from child tables, the joins are pointles.

even with joins, the difference between #tmpA and TableA is zero, may even be maybe in favor of TableA, depending. Your first select does the physical read if needed into memory, then its only logical reads and those would have to be done with temp table as well, except for temp table you need to crete it and insert the data


SQL Server Logon Trigger Implications - are there any side effects possible in my scenario? Some of developers on my team know passwords from SQL accounts that have extended permissions
We would like to track and be alerted whenever any of developers are using any of those SQL accounts to connect
We are about to implement logon trigger that would for each log in attempt, evaluate login's properties, and send email report if they match certain criteria. Logic is following:

if original_login() in (...SQL accounts list here...) and:
a) client IP address is 192.168.x.x  VPN subnet  (no Production apps connecting from this subnet, only developers can) or
b) client host name in (... list of Dev's host machine names...) or
c) client application name in (SSMS,az-Data etc.)
exec sp_send_dbmail (send report over email to DBA)

Trigger would have "execute as" clause and run on behalf of a SQL login whose only permission is to send db mail emails
What can be unwanted side effects of enabling this kind of logon trigger on Production ?
Can it slow login process or cause any other issues ?
p.s. I am aware about DAC and how to use it.
Tested connecting using DAC and counting on it to help me disable the trigger if any trouble begins

Database mail uses a Service Broker queue and an asynchronous background process to actually send emails, so performance shouldn't be a big deal.  But logon triggers can easily cause downtime, so they require a great deal of care in writing and testing.  Also you could end up with thousands of emails if a developer runs a load test or somesuch.
So it's probably overkill to use a logon trigger for this.  Instead use an Audit or even just an XEvent session.  Write the data to an event file and processes it with a scheduled job.
Here's how to create and query an Audit:
--ALTER SERVER AUDIT [sa_successful_logon] WITH (STATE = OFF);
--DROP SERVER AUDIT [sa_successful_logon]
go

declare @logFolder nvarchar(255) = ( select [path]  from sys.dm_os_server_diagnostics_log_configurations )
exec('
CREATE SERVER AUDIT [sa_successful_logon]
TO  FILE ( FILEPATH = '''+@logFolder+''' )
WHERE ([server_principal_name]=''sa'');
ALTER SERVER AUDIT [sa_successful_logon] WITH (STATE = ON);
')
GO

CREATE SERVER AUDIT SPECIFICATION [sa_successful_logon_logon_spec]
FOR SERVER AUDIT [sa_successful_logon]
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE = ON)

go

declare @log nvarchar(255) = ( select [path] + 'sa_successful_logon*.sqlaudit' from sys.dm_os_server_diagnostics_log_configurations )
SELECT event_time, client_ip, application_name
FROM sys.fn_get_audit_file (@log,default,default);


Install mysql through deb file on ubuntu 20.04 Hi I've downloaded the file mysql-server_8.0.21-1ubuntu20.04_amd64.deb-bundle.tar from mysql site.
I've extracted it to see below list of deb files
libmysqlclient21_8.0.21-1ubuntu20.04_amd64.deb
libmysqlclient-dev_8.0.21-1ubuntu20.04_amd64.deb
mysql-client_8.0.21-1ubuntu20.04_amd64.deb
mysql-common_8.0.21-1ubuntu20.04_amd64.deb
mysql-community-client_8.0.21-1ubuntu20.04_amd64.deb
mysql-community-client-core_8.0.21-1ubuntu20.04_amd64.deb
mysql-community-server_8.0.21-1ubuntu20.04_amd64.deb
mysql-community-server-core_8.0.21-1ubuntu20.04_amd64.deb
mysql-community-server-debug_8.0.21-1ubuntu20.04_amd64.deb
mysql-community-test_8.0.21-1ubuntu20.04_amd64.deb
mysql-community-test-debug_8.0.21-1ubuntu20.04_amd64.deb
mysql-server_8.0.21-1ubuntu20.04_amd64.deb
mysql-testsuite_8.0.21-1ubuntu20.04_amd64.deb

Using mysql to build a web project.
I want to understand

what each file is for
what order should be followed to install them
what files can be skipped
any disadvantage of installing through deb file (coming from windows background)
is this server edition suitable in prod env as well (short answer is ok)

I've already installed mysql workbench through a deb file
PS:
I found the dependency among most of these files that helped me install them in order as below:
i had to install libaio1 libmecab2 first
sudo dpkg-preconfigure mysql-community-server_*.deb
    mysql-community-client-core
    mysql-community-client
    mysql-client
    mysql-community-server-core
    mysql-community-server
    mysql-server

this link was helpful
https://dev.mysql.com/doc/mysql-installation-excerpt/8.0/en/linux-installation-debian.html

You can use
 mysql-server_8.0.21-1ubuntu20.04_amd64.deb

But yoz should follow the howto
Install short Howto

Download latest mysql-apt-config deb from
https://repo.mysql.com/

Install mysql-apt-config deb:
# sudo dpkg -i mysql-apt-config_*.deb

Update the package index:
# sudo apt-get update

Install mysql-server deb package:
# sudo apt-get install mysql-server

ANd if you have problems see https://dev.mysql.com/doc/mysql-installation-excerpt/8.0/en/linux-installation-debian.html


Postgres query plan has high rowcount, slow execution I've identified this snippet of a query as running at incredibly slow speeds, although it's quite simple. It aims to find all the records in Table1 which don't have a more recent entry in Table2 with the same SessionID.

SELECT t1."VisitorID", t1."Timestamp"
FROM "Table1" t1
JOIN Table2 s ON t2."Timestamp" > t1."Timestamp"
            AND t2."SessionID" = t1."SessionID"
GROUP BY sgs."VisitorID", sgs."Timestamp"
HAVING COUNT(*) = 0

Where Table1 is a temporary table with 2640 rows and Table2 is permanent with 3756955 rows.

Explain analyze gives the following output:

HashAggregate  (cost=119823801.74..119823830.53 rows=2303 width=24) (actual time=5172.891..5172.891 rows=0 loops=1)
Group Key: t1."VisitorID", t1."Timestamp"
Filter: (count(*) = 0)
Rows Removed by Filter: 2228
->  Nested Loop  (cost=0.43..97379087.60 rows=2992628552 width=24) (actual time=0.036..3986.857 rows=2909911 loops=1)
        ->  Seq Scan on "Table1" t1  (cost=0.00..80.40 rows=2640 width=61) (actual time=0.007..1.588 rows=2640 loops=1)
        ->  Index Scan using "IX_Table2_Timestamp" on "Table2" t2  (cost=0.43..25191.47 rows=1169452 width=45) (actual time=0.036..1.120 rows=1102 loops=2640)
              Index Cond: ("Timestamp" >= t1."Timestamp")
              Filter: ((t1."SessionID")::text = ("SessionID")::text)
              Rows Removed by Filter: 71
"Planning time: 0.243 ms"
"Execution time: 5172.947 ms"

I'm guessing that the slow execution time is down to the high row count, estimating nearly 3bn in the loop with actual count close to 3m, and 1.1m in the index scan and finding 1102, but I can't find the reason for this. This is just a small dataset for testing, when I used it on the full dataset (340,000 rows in Table1), it filled 45GB of temp files before I cancelled it.

What I've tried so far:

VACUUM ANALYZE on all tables involved before that part of the query runs.
Indexing all columns involved.
Replacing the greater-than with equals on Timestamp
Removing the GROUP BY and HAVING COUNT(*) statements

These gave performance increases but the snippet was still far slower than the rest of the query and the row count was still just as high.

If it's relevant, I'm running PostgreSQL 9.4.1 on an Amazon RDS instance.

EDIT: Adding the statements for creating tables and indexes. I can't see anything out of the ordinary here.

CREATE TABLE Table2
(
  "VisitorID" uuid NOT NULL,
  "Timestamp" timestamp without time zone NOT NULL,
  "SessionID" character varying(50),
  CONSTRAINT "Table2_pkey" PRIMARY KEY ("VisitorID", "Timestamp")
)
WITH (
  OIDS=FALSE
);

CREATE INDEX "IX_Table2_SessionID"
  ON Table2
  USING btree
  ("SessionID" COLLATE pg_catalog."default");

CREATE INDEX "IX_Table2_Timestamp"
  ON Table2
  USING btree
  ("Timestamp");

CREATE INDEX "IX_Table2_VisitorID"
  ON Table2
  USING btree
  ("VisitorID");

CREATE TEMP TABLE Table1 ON COMMIT DROP AS
    SELECT
        s."VisitorID",
        s."Timestamp",
        s."SessionID",
    FROM Table2 t2
  -- This date range is just to ensure a small dataset for testing
    WHERE s."Timestamp" > '2015-07-29 16:55:00' AND
             s."Timestamp" < '2015-07-29 17:00:00';

    CREATE INDEX "IX_Temp_Table1_SessionID"
    ON "Table1"
    USING BTREE ("SessionID");
    CREATE INDEX "IX_Temp_Table1_Timestamp"
    ON "Table1"
    USING BTREE ("Timestamp");

(Unsure whether this belongs as an answer, comment or edit so I'll leave it here unless I hear otherwise.)

I was unable to find the cause of the bad row count. However, after rewriting the query like this,

SELECT t1."VisitorID", t1."Timestamp"
FROM Table1 t1
WHERE NOT EXISTS (
    SELECT t2.VisitorID, t2.Timestamp
    FROM Table2 t2
    WHERE t2.SessionID = t1.SessionID
        AND t2.Timestamp > t1.Timestamp
)

the estimated row count went to a normal level and execution time decreased drastically, even on large datasets. I tested this with and without composite indexes like so:

CREATE INDEX "IX_Temp_Table1_Visitor-Session-Time"
ON "Table1"
USING BTREE ("VisitorID","SessionID","Timestamp");

CREATE INDEX "IX_Table2_Visitor-Session-Time"
ON "Table2"
USING BTREE ("VisitorID","SessionID","Timestamp");

and found no significant performance difference.


How to not replicate index creation on ignored tables in MySQL/MariaDB I have a setup with two MariaDB masters, each being the slave of the other.
Some tables are ignored, with the configuration option :

replicate-ignore-table = moodle.mdl_temp_%

My problem is when a statistic batch is run against one of the server :
a CREATE TABLE followed by a CREATE INDEX is issued, and the last statement is replicated (looking at the binlogs the CREATE TABLE is effectively not replicated), which broke the replication on the other server.

Who to avoid the CREATE INDEX being replicated ?

[EDIT]
Here is the error on the second server when the statistic batch is ran on the first server.

Error 'Table 'moodle.mdl_temp_stats_daily' doesn't exist' on query. Default database: 'moodle'. Query: 'CREATE INDEX mdl_tempstatdail_cou_ix ON mdl_temp_stats_daily (courseid)

And when I take a look at the binlogs, there's no "CREATE TABLE" but there is still the CREATE INDEX :

# mysqlbinlog /var/lib/mysql/binlog/mysql-bin.000101 | grep -i create
CREATE INDEX mdl_tempstatdail_cou_ix ON mdl_temp_stats_daily (courseid)
CREATE INDEX mdl_tempstatdail_tim_ix ON mdl_temp_stats_daily (timeend)
CREATE INDEX mdl_tempstatdail_rol_ix ON mdl_temp_stats_daily (roleid)
[...]

Plan A:  Include the index(es) in the CREATE TABLE statement.

Plan B:  Use this to add an index:

ALTER TABLE moodle.mdl_temp_stats_daily ADD INDEX(courseid);

(I have not tested either.)


Postgres 8.3 faster than 9.3? Our software product currently ships on Windows 7 with Postgres 8.3 as its database.  On a busy site, there may be 24 automated systems generating 100 rows (x100 columns) per minute, with 3-10 human clients viewing subsets of around 1000 rows – all retrieved at once, with incremental updates querying pk + timestamp every minute or so and retrieving new rows that are pertinent.  There are a few auxiliary tables, but this table has the primary activity.

As a first step to a limited multi-master system (to help with geographically separated teams), we implemented an upgrade to 9.3.  Performance wasn't the first priority, so it wasn't really profiled.  As release time has come, management has decided to drop 9.3 for now citing fear of possible performance degradation and lack of testing resources.  I was sure the performance issue was preposterous so I did some PgBench testing.

Using 9.3's pgbench, I alternated between connecting to the local 8.3 and 9.3 installations (diff't port numbers).  I've captured my results in this google drive spreadsheet, but the summary is that generally 8.3 beat 9.3.  9.3 only won in raw insert performance.

We have some customization of our postgresql.conf files which I generally kept from 8.3 to 9.3, I'll list the non-default settings

max_connections = 1000
shared_buffers = 320MB
temp_buffers = 80MB
max_prepared_transactions = 50    #8.3 only, 9.3 left at 0 (not sure why)
max_fsm_pages = 204800            #8.3 only, 9.3 doesn't have setting
autovacuum_max_workers = 30

So, is this just the price of progress, or is there something I should be doing in 9.3 to make it excel?

Usually PostgreSQL 9.3 is generally faster then 8.3 - but hard to say what is wrong. Possible sources: problems with IO, wrong PostgreSQL configuration - max_connections = 1000 is probably terribly wrong value, default work_mem is usually too small, hitting hw limits (9.1 and higher should to better use more CPU), wrong testing ...

Other problems can be changes in pgBench. Result of pgBench 8.3 should not be exactly compared with results pgBench 9.3. If you would to have a good numbers, then you have to use pgBench 9.3 for PosgreSQL 9.3 and PostgreSQL 8.3. Don't forget - test should run minimally about 20 munutes (better hours).

Second factor - probably pgBench is not important for your production. pgBench is synthetic tests with disputable value. It is good for initial hw and sw testing, but is bad for configuration precision and database tuning. Much more important is speed of your application - you should to test a speed of slow queries or most often queries. 9.3 has lot of new functionality - better monitoring, more comfortable tools, better optimization for some kind of queries, built-in replication, online comfortable physical backup, much smarted vacuum (faster on large datasets), ...

Increasing of default_statistics_target had overhead in query planning - for extremely fast and simple queries it can be significant. On second hand, higher value decrease a probability of wrong estimation and wrong optimization - that is important for usual non trivial queries - planning is little bit slower (it can be visible in synthetic tests) but usage in production is much more robust. Depends on your application (if it use simple or not simple queries) this configuration variable can be important for you or not.


MySQL process stuck in commit state We recently noticed an issue on our MyCluster DB which is comprised of 2 instances hosted on AWS.

Our website grinds to halt in terms of performance and we noticed that there 2 processes that are stuck in commit state as shown below.

Id  User    Host    db  Command Time    State   Info
49  rdsadmin    localhost:56378 mysql   Sleep   1       \n
938 dbadmin 10.0.11.242:57130   REDACTED    Query   1   init    COMMIT
942 dbadmin 10.0.1.114:49236    REDACTED    Query   1   init    COMMIT

We are in the process of getting performance_schema enabled. How should we go about find the root cause of the performance bottleneck?

We noticed that there were large number of inserts into the error log table coupled with info logging. This put a lot of stress on the database and resulted in significant performance issues. We disabled info logging at the application level which eliminated the performance bottleneck.


Surrogate vs composite key in hierarchical data structure I'm currently evaluating a schema for a hierarchical data structure. My main problem is how I should design the schema to prevent inconsistent data (reference of foreign key in another hierarchy). The two variants I discovered are either to use a composite or a surrogate key.

Requirements

Strict consistency checking per hierarchy, e.g. a Part in Hierarchy A cannot have a foreign key on a Type in Hierarchy B (see example schemas below).
The constraints have to be checked in the database and not with application code, as the database is used from different applications.
Scalable for performance and extendable with more hierarchy levels.

Variant 1: Composite Key

In this example, the foreign keys are part of the composite primary key. With this, it is automatically checked that the Type of the Part are assigned with the same Item (same hierarchy), because they are part of the composite primary key.

This approach seems troublesome to me, as it is not very easy to extend the model. For example, if I decide to create a PartMetadata table, which holds metadata for a single Part, I have to include the whole composite key, even though the metadata has no connection to the hierarchy.

Item
  ItemId (PK)
  Name

Type
  TypeId (PK)
  ItemId (PK, FK)
  Name

SubItem
  SubItemId (PK)
  ItemId (PK, FK)
  Name

Part
  PartId (PK)
  ItemId (PK, FK)
  SubItemId (PK, FK)
  TypeId (PK, FK)
  Name

Variant 2: Surrogate Key

In this case I would have to define additional constraints or triggers to ensure that it is not possible to insert a Part with a Type that belongs to a different Item.

Item
  ItemId (PK)
  Name

Type
  TypeId (PK)
  ItemId (FK)
  Name

SubItem
  SubItemId (PK)
  ItemId (FK)
  Name

Part
  PartId (PK)
  SubItemId (FK)
  TypeId (FK)
  Name

Questions

Should the surrogate solution always be favored? In what cases is it useful to use the composite key solution?
Which solution is better maintainable in the long term, especially if the hierarchy gets bigger?
Which solution offers better performance (reporting, CRUD)? Does the surrogate solution have a huge impact because of check constraints or triggers?
Are there other options (in my opinion, both my variants seem like non-ideal solutions)?
Has someone experience with both variants and can share his wisdom?

With the 1st design, you can enforce that a Part is related (through SubItem and Type) to the same Item. With the second, you can't (through DDL alone, you need triggers as you already mentioned to enforce that or make sure all CRUD operations are done through stored procedures that take care of the restriction). Otherwise you may have a Part that is related to a SubItem and a Type that refer to different Items.

Some notes on your (1st) design:

It's not clear that you have the correct FOREIGN KEY constraints. Your requirement cannot be enforced by 3 simple FKs in Part but needs 2 composite ones:

Part
  PartId    (PK)
  ItemId    (PK, FK1, FK2)
  SubItemId (PK, FK1)
  TypeId    (PK,      FK2)
  Name

You may not need to have PartId in the PK. Is it possible to have 2 or more parts that belong to the same SubItem and same Type? If not, the unique (or primary key) constraint should be on (ItemId, SubItemId, TypeId). If yes, then it should be on (ItemId, SubItemId, TypeId, PartId), as you have it.

Regarding PartMetadata or any other table that can reference Part table, you don't have to include the whole composite key of Part. You can always add a new surrogate UNIQUE key in Part and use that instead. This is common practice I think in complex and high hierarchies of entities. We can use composite PKs and FKs up to a point. When they become too wide (4-6 columns), we can add surrogate unique keys in a few important tables and use them for referencing from lower tables in the model.


Indexes involved in intervals collision detection Let's assume an hotel booking application wants to display all customers being present between two dates.

Starting with a venue table :

CREATE TABLE `venue` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `startdate` DATE NOT NULL,
  `enddate`   DATE NOT NULL
)

I wish to perform a query finding venues for a one month interval, e.g. last december 2013:

SELECT `venue`.id
WHERE `startdate` <= '2013-12-31'
AND   `enddate`   >= '2013-12-01'

In order to have great performance among millions of rows (big hotel), I immediately add two indexes, one for each date:

ALTER TABLE `venue`
ADD INDEX (`startdate`),
ADD INDEX (`enddate`  )

I leave to the SQL engine the responsibility to choose the right index. If dates intervals are distributed homogeneously, from say 2000-01-01 to 2014-01-01, the previous query will immediately use the enddate index, only scanning the last month. If the interval is closer to the beginning, e.g. last week of 2000, the startdate index will be used, scanning only the whole 2000 year.

Now, what if I want to get the venues for a single day, right in the middle of the venues distribution, e.g. first january of 2007. Both indexes will more or less offer the same filtering, that is half the table. And scanning half the table is very, very long.

Adding paired index like (startdate, enddate) or (enddate, startdate) won't be of any used, as far as I can tell.

Is there any other index-based strategy to make things faster ?

This is exactly the kind of query that can benefit from a composite, or multiple-column index: when a single statement filters by multiple columns in the same table. The order of columns in the index will depend on your use cases, but in general the order in which the columns appear in the DML statement is fine, in your case (assuming MySQL but the concept is the same for most RDBMSs):

ALTER TABLE 'venue'
ADD INDEX ('startdate','enddate')

See the documentation for other alternatives, such as adding a column containing the hash of multiple columns.


Is it possible to log ship from on-premise SQL server to Azure SQL Managed Instance? From the docs, it would seem that restoring a full database backup file is possible, but can one also restore transaction logs?

(the idea is to minimize downtime in a migration from on-prem to MI).

Thanks.

No, log shipping to Managed Instance is not possible; it only supports the restore of full backups. See this thread:

Lesson Learned #82: Azure SQL Database Managed Instance supports only COPY_ONLY restoring a database backup (was here before)

And the limited syntax diagram for RESTORE in the documentation for Managed Instance:

RESTORE Statements (Transact-SQL)

The docs imply that RESTORE ... FROM URL will implicitly add WITH RECOVERY and so there will be no opportunity for logs to be applied after the initial restore. So your downtime will be >= the time it takes to take a COPY_ONLY full backup, put it somewhere MI can access, restore it, and re-point your app(s). You might also want to read through this tutorial, for a different approach.


Can a un-used NONCLUSTERED INDEX still enhance query speed? This is a strange situation, but I'm hoping someone has an answer.
During some performance troubleshooting, we added a NONCLUSTERED INDEX to a table, as was requested by sp_BlitzIndex.  We checked its usage the next day, and it showed 0 reads (0 scans/seeks, 0 singleton lookups), so we disabled it.
The very next minute, we get a complaint of the same app-slowness (performance trouble) that we were trying to check out & resolve in the first place when we added the INDEX.
Now, I know in theory, this sounds purely coincidental.  The INDEX was provably, measurably, NOT USED.  Disabling it SHOULD NOT have caused query performance degradation.  But it's almost TOO coincidental.
Question
So my question, simply enough, is thus:
Is it at all possible, that a NONCLUSTERED INDEX, whose usage-stats (from the DMVs / sp_BlitzIndex) show NO usage, still have been helping query performance somehow on the affected table?

Yes it could, when SQL Server decides that the statistics from that index is more accurate/useful and uses that stats to do the estimates and come up with a plan.

I have come across situations when SQL Server has decided to use stats from one index and scan/seek another index.

Edit - This might not be applicable cause I just realized that you have disabled the index.  I have not tested that scenario.


How can I visualize the entire delete cascade of an existing database? I have a PostgreSQL 9.6 database with 68 tables. Many of them have foreign key constraints with ON DELETE CASCADE clauses.

I would like to see the complete graph of the entire cascade. Is there a tool for that?

Dbeaver has Diagram tool. Just drug and drop your tables and you'll see relationships


Should I create two endpoints for two availability groups on the same SQL Instance? I have a two node cluster running SQL Server 2012. I want to create two Availability groups within the same instance of SQL Server.

Should I add one endpoint for each AG, or shall I allow both AG's to use the same endpoint?

I have read the documentation here but the only part pertaining to multiple endpoints relates to multiple SQL instances

  Note that if more than one server instance on a given computer requires a database mirroring endpoint, specify a different port
  number for each endpoint.

If you read Prerequisites, Restrictions, and Recommendations for Always On availability groups it says

Each server instance requires a database mirroring endpoint. Note that this endpoint is shared by all the availability replicas and database mirroring partners and witnesses on the server instance.

An endpoint is for instance of SQL Server and all Availability Groups configured within that instance can use this endpoint.


What are the advantages and disadvantages of Third normal form and Boyce-Codd normal form? I know that 3NF makes a database more efficient and stores data according to relevance, which is more logical, but are there other advantages? and are there any disadvantages?

Third normal form(3NF) includes the rules of the lower level normal form, such as second (2NF) and first normal form (1NF). Similarly, Boyce/Codd normal form (BCNF) includes the rules of the lower level such as 3NF, 2NF and so on (see here). Normalization provides both advantages and disadvantages, some of them are mentioned below.

Advantages of  normalization:

Reduced data redundancy:

In each level of normalization a type of data redundancy (same data present in more than once) removed from the model. Once the redundancy is removed, it is easy to change the data since data is present in only one place.

Increased data quality:

By applying business rules, there is a smaller chance in storing unwanted data in the table. Also there is less need for storing null values.

Capturing complete business requirements:

In normalization we have to ask a lot of questions to business people, so our understanding of the business becomes stronger and requirements of the business are easy to capture.

Data modification anomalies (Insert, delete and update)  are reduced.

Normalization splits a entity into a smaller number of entities, so you will have more smaller tables, so it is easier for sorting, indexing and searching.

Since there are many smaller tables, you will have many clustered indexes, it will help you in query tuning.

A normalized data model can be easier to modify and maintain.

Disadvantages

With many number tables and joins in between those tables slow down the performance of the database.

Having a large number of tables consumes more development time for implementation.

A completely normalized database needs clear and broad understanding of the business, it takes more time to analyse and understand the business. I hope this will help you. Thank you.


Connecting to Oracle with encrypted pwd I am reading my userid , pwd from a file with 400 permissions to connect to the external oracle application.

Now my requirement is that i should not use the plain text pwd present in the file . I should use some encrypted pwd to connect. How i can do this ?

My guess is that you would want to use an Oracle wallet so that you don't need to embed the username and password in your application at all.  Depending on the architecture of your application, there may be other options as well-- if you're building a Java application that is deployed to an application server, for example, most application servers have a way to configure connection pools such that only the encrypted password is stored in a configuration file.


MySQL: memory requirements for django app with 1 big table (460M rows) I have created a django app that is running on virtual machine. All db tables are rather small (up to 1M rows) but recently I was asked to add a big table (460M rows).

CREATE TABLE `genescorrelation` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `correlation` double NOT NULL,
 `gene1_id` int(10) unsigned NOT NULL,
 `gene2_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `c_gene1_id_6b1d81605661118_fk_genes_gene_entrez`   (`gene1_id`),
KEY `gene2_gene1_idx1` (`gene2_id`,`gene1_id`))
ENGINE=InnoDB AUTO_INCREMENT=92345005 DEFAULT CHARSET=latin1

The query that user will run (up to 10 genes):

 SELECT gene1_id, AVG(correlation) AS avg FROM genescorrelation
 WHERE gene2_id IN (176829, 176519, 176230)
 GROUP BY gene1_id ORDER BY NULL

Currently I have imported around 89M rows and the query is slow. It would take around 30-40 seconds for 3 or 4 genes.

I think my VM is too weak because it was a default set up (for smaller project/database). Currently it has just 2GB RAM and 36GB space.

I need to ask to set up prod machine.

What are the memory/space suggestion for this kind of set up?
Is there anything that could be done in MySQL config?
Is there anything else I can do to speed up a query?

Any hints appreciated, thanks.

FYI, I asked my initial question on so: https://stackoverflow.com/questions/31969964/mysql-slow-avg-query-for-411m-rows. After that I was trying to add the suggested index (gene2, gene1) but didn't have enough space, so dropped the table and reimported 90M rows.

Looking at the query

SELECT gene1_id, AVG(correlation) AS avg FROM genescorrelation
WHERE gene2_id IN (176829, 176519, 176230)
GROUP BY gene1_id ORDER BY NULL

I have four(4) suggestions

SUGGESTION #1

I see three(3) columns. I recommend making a covering index

ALTER TABLE genescorrelation
ADD INDEX gene2_gene1_correlation_idx1 (gene2_id,gene_id,correlation);

The StackOverflow accepted answer already mentioned making this index. That way, the aggregation never touches the table. All the needed data will come from the index.

Since you already made gene2_gene1_idx1 here is the fix

ALTER TABLE genescorrelation
DROP INDEX gene2_gene1_idx1,
ADD INDEX gene2_gene1_correlation_idx1 (gene2_id,gene_id,correlation);

SUGGESTION #2

You probably need more read threads

Add this to my.cnf

[mysqld]
innodb_read_io_threads = 8

Restart of mysql is required

SUGGESTION #3

If the server is just a DB Server, you should reserve up to 75% of RAM for InnoDB Buffer Pool or the sum total of all InnoDB Data and Indexes (SELECT SUM(data_length+index_length) InnoDBDataAndIndex FROM information_schema.tables WHERE engine='InnoDB';), whichever is smaller.

SUGGESTION #4 (OPTIONAL)

Change the Query to see break down by gene2_id

SELECT
    IFNULL(gene2_id,'All Gene2 IDs') gene2,
    IFNULL(gene1_id,'All Gene1 IDs') gene1,
    AVG(correlation) AS avg FROM genescorrelation
WHERE gene2_id IN (176829, 176519, 176230)
GROUP BY gene2_id,gene1_id WITH ROLLUP;


SQL date and time query not importing all I have the below table and when i run this query:

SELECT *
FROM table1
WHERE Date <= "11/15/2018 12:00:00 AM"

it only retrieves the dates 15,14,13,12,11,10 and 1 of Nov 2018 .
How can i solve this? Thanks!

I tried changing the format of the date in the WHERE part of the query to "11/15/18 12:00:00 AM", it doesn't work.

This looks like a case where the date column isn't a date at all, but rather a string.

You aren't getting Nov 2 through Nov 9 because the sorting on the string and calculating if it's less than 11/1.... in alphanumeric order.

Ideally, dates will be stored as dates, and then your existing query will work. This will also ensure that dates are valid dates, and you don't end up with data for 32 February, or even random strings.

If you can't change the type of the column, you'll have to cast the database values to a date to do the comparison (which will hurt performance, but get you the results you expect).


Designing MySQL Database For Two Different User Types (Staff and Client)? I'm creating a web-app that will connect to a MySQL database.  This app will have 2 users:  a Staff and Client that can both log in from the same login page.

Both Staff and Client share the following common attributes:

staffID/clientID
status
timestamp update
username
password
email
image profile
first/middle/last name
gender
date of birth
address
phone

But the Staff has the unique attributes:

position
license
badge
date hired

And the Client has the unique attribute:

company name

1.) Would it be advisable to put all the common attributes in one single Users table with a column user_type to determine whether it's a Staff or a Client that logs in or make two separate tables for Staff and Client that have the exact same attributes?

2.) Would it be advisable to further separate the following attributes:

gender
date of birth
address
phone

into another table since they will not be queried as much (these attributes are only queried when viewing users' profiles)?  Will this help speed up query time for, say, log ins?

3.) Would it be ideal to separate the image profile attribute into one table Image_Files or keep it within the same table with all the other attributes?

As of now, I'm leaning on making the following tables with columns:

USERS table:

userID [PK] (formerly staffID/clientID)
status
timestamp update
username
password
email

USER_DETAIL table:

user_detailID [PK]
userID [FK]
gender
date of birth
address
phone

STAFF_DETAIL table:

staff_detailID [PK]
userID [FK]
position
license
badge
date hired

CLIENT_DETAIL table:

client_detailID [PK]
userID [FK]
company name

IMAGE_FILES table:

imageID [PK]
userID [FK]
image pathfile pointer

Is this database design unnecessarily complicated?  If so, how may I streamline it to make it more simpler, faster to query, and yet still satisfy the unique attributes of each entities?

I think your problem is that you are focusing to much on performance. From the description it seems like the user table will be rather small (say, less than a million users).

Instead you should focus on the functionality you want from the system. There are questions you should ask and design for such as:

Can a user be both client and staff?
Can a user be user for several companies?
Can a user have several adresses, say a business adress and a home adress?
Do you need to have "time based changes", say that a client should be made active (or inactive) at a future date?
Do you need to store change information (who and when information was changed)?
Will you need to support several different login schemas (say through facebook or using some kind of one-time-password)?

My take on this is that I would have one table describing a login user and a second table describing a users role. When a user has successfylly logen in I would check if the users has several roles, and the user would be required to select role: staff or user for one company. This would allow the login to be for one physical person, over time it could evolve to include OTP or challenge response or what is deemed necessary. The user would at any time have zero, one or more selectable roles.

Edit (added): If the user has only one role, there is no need to show any selection. Even if a user has no role right now the system might allow changing adress, mail adress and so on. Role based might allow a user to be administrator for the users at one company. End edit.

In the role table I would have start and stop dates. If necessary from a performance view it would be possible to later add an "active" field but that would be an optimization which I would not add until necessary.


Is it bad practice to use a data-warehouse for providing reorganized non-BI data? The Primary goal is the case-optimized data representation. The idea is to gather data from several databases, save the data in a data-warehouse and restructure/connect the data in data marts(similar to a view across multiple databases). Basically the intention is to make it easier to access the data that is spread across databases.

As you can see in the picture the plan is to have dependent data marts(shown in the upper half of the picture) to avoid having multiple Clean and Load processes like independet data marts have (shown in the bottom of the picture). The BI aspect of the data-warehouse will also be used but is not relevant for my question. Only the data in the DB will be changed. The data represented in the DW is just for reading purposes. So the basic concept is not hurt. But I still feel like the DW is misused here.

To anwser my question please include following points:

Is the DW misused?
Are there any DW's with dataconnectors that fit (near) real-time
capabilities?
What alternatives fit these requirements to connect    data from
multiple DB's and represent them in a clean and structured    way in
real-time without having tons of Clean and Load processes?

The first (top) example is essentially the most common ways to design the ETL process for a BI datawarehouse and datamarts. You don't want the lower example, as you'll need to build, deploy/version and troubleshoot multiple instances of the same code.

As for "misuse" or not, it all comes down to how you design the datamart. Typically, star schemas are designed for analytics, but there's nothing to say it won't work for OLTP-type work. I would consider the amount of work required to remodel the data when loading - is it worth the extra complexity, or would you be better off querying the source database directly from your app?
Maybe a database view in the source database(s) could do the same, with a lot less complexity?

Be really careful with "real-time" BI implementations: The more "real-time" you build the solution, the more complex it inevitably gets.

On the one hand, a process that does a truncate-and-load every night is relatively simple to build and run.

And incremental loads that run every hour, and you need to start identifying new, changed and perhaps even deleted (!) rows in the source data. This may be hard or even impossible, depending on how the source data is structured or shipped to you.

A full-on "real-time" solution (within quotes, because unless you have just views on the source database, it's not really realtime) will probably need some kind of change capture, trigger or similar complex logic for every dependent table. All of those things can potentially affect the source databases adversely with regards to performance and stability.

It gets even more complex and costly when those tables interact, say for example, if you need to join snowflake schemas into a star schema.

To answer your third question, pretty much any ETL tool (SSIS, Pentaho, etc) will support basic datawarehouse ETL. Once you add near-realtime logic, the choice of platform ETL tool becomes less relevant (you'll probably need to script most of this yourself anyway), and the solution becomes very dependent on the database platforms of the source data as well the infrastructure and platform of your datawarehouse.


Paging in SQL Server 2012 vs. previous versions I have used lot of paging code in SQL Server 2008. The format is

With CTE(
 -- SQL With RowNumber
)

-- SELECT ALL Page N

This is working fine. But I have heard that SQL Server 2012 has better paging support. So, if I change all my stored procedures with paging to the new method, will my queries be more efficient?

Actually, the OFFSET/FETCH extension is more syntactic sugar than performance enhancement. I've heard of a few cases where performance can be improved, but in all of my tests thus far, the performance is equivalent to the CTE with ROW_NUMBER() that you're already using - or at least not "better enough" to justify a code change. IMHO. You'll want to actually test your queries in your environment against your data to see if the performance benefit is worth the refactor.

Some very early performance comparisons here:

https://sqlblog.org/2010/11/10/sql-server-v-next-denali-using-the-offset-clause-paging

And from an updated article in 2015:

https://sqlperformance.com/2015/01/t-sql-queries/pagination-with-offset-fetch

And the documentation:

http://msdn.microsoft.com/en-us/library/ms188385%28v=SQL.110%29.aspx


Will my SQL query use stale data? How can I prevent it? I have two tables (SJob & SJobDependent) that I need to join for some logic in a stored procedure. They both have a column (job) that connects them in a one-to-many relationship - one SJob record for zero or more SJobDependent records.

Here is my SQL query:

-- Return any records that are active and have no unsatisfied dependencies.
SELECT * FROM SJob
LEFT JOIN SJobDependent
    ON SJob.job = SJobDependent.job
    AND SJobDependent.satisfied = 0
WHERE SJobDependent.jobDependentID IS NULL
AND SJob.state = 'active'

Here is the Actual Execution Plan from SQL Server Studio:

Due to the way the code is written:

// Pseudo-code:

// SJob record is added with SJob.state = 'ready'.

// Related SJobDependent record(s) are added.

// SJob record is updated to SJob.state = 'active'.

I fear that this may happen when the SQL query runs:

Scan SJobDependent.
SJobDependent record(s) inserted.
Start scan of SJob. SJob.state is 'ready'.
SJob is updated. This blocks reading of SJob?
End scan of SJob. SJob.state is 'active'.

The problem I fear is that my SQL query returns SJob records found in the "active" state (SJob.state = 'active'), but fails to see the related SJobDependent records.

Is this problem capable of happening, or am I over-analyzing the SQL query?

If this is a legitimate problem to worry about, what can I do to solve it? I'm open to solutions.

One idea I've had is to force the scan of SJobDependent to occur after the scan of SJob. Is this even possible? What are the implications/consequences of doing this?

Do the scans shown in the Actual Execution Plan occur in a particular order or is it always random from call-to-call?

NOTE: As noted in AMtwo's answer, Repeatable Read isolation level will probably not solve my problem, due to the fact that it only takes effect when the read starts.

If you're using the default isolation level in SQL Server (Read Committed), then you certainly can run into all sorts of issues around inconsistent reads. Paul White describes the problems here.

If you want your read queries to read data which is fully consistent to how it looked at a given point in time, I'd recommend that you consider Read Committed Snapshot Isolation (RCSI). With RCSI, your query will return data that is consistent to a single point in time (the start of your query).  If User A starts a SELECT query while User B is concurrently performing updates, User A will read the "old" value because it will read a snapshot of the data, which is consistent to the start of the query.

The catch with RCSI is that it's a database-level setting. Unlike Read Uncommitted, you can't set it as a session-scoped setting. You'll have to consider this change more globally before making the change. However generally speaking, if you require consistent reads for this query, you probably want consistent reads for the entire application.

While the Repeatable Read isolation level may look appealing to solve your problem, but note this detail from the linked post:

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

This means that the data can still be changed prior to being accessed, but during the time your query is running. It is also subject to some of the same inconsistent reads as the Read Committed isolation level--notably phantoms.


ORDER BY too slow in Foreign Table using postgres_fdw PostgreSQL v9.6, postgres_fdw

Foreign table

CREATE FOREIGN TABLE user_info (
  id bigint ,
  info jsonb
)
SERVER server_test_fdw OPTIONS(SCHEMA_NAME 'public', TABLE_NAME 'user_info_raw' );
-- user_info_raw is a large table (100 million records, 200 GB)

Sample data of info  column

{"key1": 1, "key2": 0.678}
{"key1": 1, "key2": 1.0}
{"key1": 1, "key2": 0.986}
{"key1": 2, "key2": 0.75}
{"key1": 2, "key2": 0.639}

Query on foreign table  (updated)

SELECT id, info
FROM user_info
WHERE info ->> 'key1'= '1' -- OR using jsonb_extract_path_text(info, 'key1')  = '1'
ORDER BY id
LIMIT 10;

Limit  (cost=10750829.63..10750829.65 rows=10 width=40) (actual time=550059.320..550059.326 rows=10 loops=1)
   ->  Sort  (cost=10750829.63..10751772.77 rows=377257 width=40) (actual time=550059.318..550059.321 rows=10 loops=1)
         Sort Key: id
         Sort Method: top-N heapsort  Memory: 26kB
         ->  Foreign Scan on user_info (cost=100.00..10742677.24 rows=377257 width=40) (actual time=1.413..536718.366 rows=68281020 loops=1)
               Filter: ((info ->> 'key1'::text) = '1'::text)
               Rows Removed by Filter: 7170443
 Planning time: 4.097 ms
 Execution time: 550059.597 ms

Query on user_info_raw

EXPLAIN ANALYSE
SELECT id, info
FROM user_info_raw
WHERE info ->> 'key1'= '1'
ORDER BY id
LIMIT 10;

 Limit  (cost=0.57..1296.95 rows=10 width=59) (actual time=0.043..0.073 rows=10 loops=1)
   ->  Index Scan using idx_user_info_raw_info on user_info_raw  (cost=0.57..68882850.88 rows=531346 width=59) (actual time=0.042..0.070 rows=10 loops=1)
         Filter: ((info ->> 'key1'::text) = '1'::text)
 Planning time: 0.192 ms
 Execution time: 0.102 ms

select pg_size_pretty(pg_table_size('user_info_raw'));
 pg_size_pretty
----------------
 223 GB

It takes 10 milliseconds to execute the query on the user_info_raw (remote server).

But, It takes a lot of time when using theuser_infoforeign table. When I remove ORDER BY id, the query executes very fast.

I think that my query on foreign table should send to the remote server for executing, but it's not, I don't know why, may be due to of this excerpt from postgres_fdw document

  By default, only WHERE clauses using built-in operators and functions
  will be considered for execution on the remote server. Clauses
  involving non-built-in functions are checked locally after rows are
  fetched. If such functions are available on the remote server and can
  be relied on to produce the same results as they do locally,
  performance can be improved by sending such WHERE clauses for remote
  execution

How can I fix the issue related to ORDER BY in foreign table  ?

UPDATED

Adding use_remote_estimate on server and foreign table doesn't help.

Sort operation is performed on local postgres server, not on remote. If your code always sort the results, you
  can just create a view on remote postgres with order by clause and
  then create a foreign table pointing to the view.

On remote server, adding a view

CREATE VIEW vw_user_info_raw
SELECT id, info
FROM user_info_raw
ORDER BY id

On local server, create foreign table references to that view

CREATE FOREIGN TABLE user_info (
  id bigint ,
  info jsonb
)
SERVER server_test_fdw OPTIONS(SCHEMA_NAME 'public', TABLE_NAME 'vw_user_info_raw' );

Refer to Postgres Admin Forum


Check for table lock in conditional statement How can I make this work?

IF( EXISTS (SHOW OPEN TABLES WHERE In_use < 2 and `Database` like 'MYDB' ) ) THEN
 -- DO SOMETHING
END IF

Context

I want to run some sql statements inside a procedure, only if the table x is not locked ( because of updates or any other reason ).

I want to do this on the MySQL server side as I have clients that trigger this procedure from more than 12 machines from bash, c and  c#.

So is there any way to get the table lock information in a conditional statement on the server side ?

The simplest solution to this seems like it would be to use a named lock.

http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_get-lock

IF GET_LOCK('my_arbitrary_lock_name',0) THEN
  ...
  DO RELEASE_LOCK('my_arbitrary_lock_name');
ELSE
  ...
END IF;

This pool of locks is independent of table locks, metadata locks, row locks, or any other kind of "actual" database object locks, and obtaining, releasing, or checking these locks is also independent of transactions.

The only thing that's actually getting "locked" here is the name you passed as the first argument, which is an arbitrary string.  All this does is ensure that no more than one thread can hold a lock of the same name at the same time.  The others have to wait for up to the number of seconds specified in the 2nd argument to GET_LOCK() otherwise they don't get the lock (and GET_LOCK() returns false).  After the timeout (which is 0, in the example, to wait no time at all) if the lock isn't free, the function returns false; if the lock is free then the lock is obtained by that connection and the function returns true.  Other connections can discover which connection holds a lock with the IS_USED_LOCK() function, but they can't interfere (other than kill the connection, given sufficient privileges).

If you "forget" to release a lock, it's automatically released if you disconnect.

Asking for a different lock will also release the current lock you hold in MySQL, and asking for a lock you hold will release it and then try to re-obtain it, potentially handing it over to another thread.  These behaviors vary between MySQL and some versions of MariaDB, it's best to be explicit when using them rather than relying on such side-effects for intentional behavior.


Oracle 11g r2 standard edition installation - set environment variable PATH and create tnsnames.ora I have installed Oracle 11g R2 standard edition (server software, 2GB download from Oracle).

I found that the 'Environment Variable PATH' has not been set. Which of these directory should I use for the PATH value:-

Oracle base
Software location
Database file location

Also do I need to create 'TNS_ADMIN' environment variable and 'TNSNAMES.ora' file

Kindly advice. Thank you

Normally you use the oraenv script to set you environment correctly for a given ORACLE_SID. The ORACLE_SID and ORACLE_HOME combinations are maintained in /etc/oratab or /var/opt/oracle/oratab which is read by the oraenv script that should be sourced (called with a .)

What oraenv by default sets does is make sure that PATH contains at least $ORACLE_HOME/bin

If you want to have some control over where your SQL*net files are located it is smart to create a TNS_ADMIN environment variable that points to the directory where amongst others tnsnames.ora is located. The tnsnames.ora file is most important to be able to point to a - local or remote - database using SQL*net.

If you only access your database from the same machine as where the database is running and you do not access an other database, a tnsnames.ora is not needed, just convenient. Normally we have a tnsnames.ora that contains lots of database on the site.