SQL Server statement takes forever while running instantly in Oracle

The Oracle optimizer uses OR Expansion to improve efficiency of the query. Quoting from the documentation:

  In OR expansion, the optimizer transforms a query with a WHERE clause
  containing OR operators into a query that uses the UNION ALL operator.

  The database can perform OR expansion for various reasons. For
  example, it may enable more efficient access paths or alternative join
  methods that avoid Cartesian products.

You can think of the new query as being written like this:

SELECT
(
    SELECT
        COUNT ( t_01.rsecondary_objectu ) AS selectExpr
    FROM
        PIMANRELATION t_01
        INNER JOIN PITEMREVISION t_03 ON t_01.rprimary_objectu = t_03.puid
        CROSS JOIN PITEM t_02
    WHERE
    t_01.rrelation_typeu = 'w8INy241VJFL2B'
    AND t_01.rsecondary_objectu = '2yLJkWqiVJFL2B'
)
+
(
    SELECT
        COUNT ( t_01.rsecondary_objectu ) AS selectExpr
    FROM
        PIMANRELATION t_01
        INNER JOIN PITEM t_02 ON t_01.rprimary_objectu = t_02.puid
        CROSS JOIN PITEMREVISION t_03
    WHERE
    t_01.rrelation_typeu = 'w8INy241VJFL2B'
    AND t_01.rsecondary_objectu = '2yLJkWqiVJFL2B'
    AND LNNVL(t_01.rprimary_objectu = t_03.puid)
)
from dual;

Now there is an equality condition for both parts of the query, so Oracle can use indexes to perform an efficient nested loop join for both. It still needs to do a cross join for both parts of the query, but the size of the intermediate result sets is significantly reduced compared to doing two cross joins in the same query. For example, if PIMANRELATION has 1 relevant row and PITEMREVISION and PITEM both have a million rows then you get a trillion rows if you cross join them together. However, if you split the queries up then you only end up with a million rows for both.

The SQL Server query optimizer has a rule that can transform OR into UNION ALL: SelToIdxStrategy. There's no documentation for this and the only reference I could find is this answer. However, that rule will not be applied in this context. Instead, you get two cross joins which can only be implemented through nested loop joins. For each relevant row in PIMANRELATION, SQL Server will cross join to all rows in PITEM, then cross join to all rows in PITEMREVISION, and will finally filter out rows after that. You can easily end up with trillions of rows getting filtered down.

I have bad news for you. If you truly cannot change any part of the query text and you need that query to perform well then SQL Server probably isn't the right platform for your application. Databases have different strengths and weaknesses and you may need to change your queries to accommodate those differences.


Is it a good approach to duplicate data in a database?

De-normalisation and certain kinds of data duplication can be a convenient way to speed-up processes. Examples are caching, datawarehousing, and materialised views.

Meanwhile the duplicated data is relied upon as a read-only snapshot (i.e. point-in-time copy of the actual data), or the system can guarantee consistency (like in the case of caches), it is safe to rely on this practice.


ORA-01830: date format picture ends before converting entire input string

SQL> alter session set nls_Date_format = 'DD-MON-YY HH24:MI:SS';

Session altered.

SQL> select trunc(sysdate, 'DD') + 13/24 from dual;

TRUNC(SYSDATE,'DD'
------------------
29-AUG-15 13:00:00


How to format a number with optional fraction


How to use temporary variable in Update query?

According to the manual, the SET clause expects a list of column assignments, meaning that the left side of each assignment must be a column reference, not a variable reference.

However, you can use variable assignment in the right hand side, where an expression goes, for instance like this:

IU.used_time = (@usedtime := unix_timestamp() - IU.start_date),

In the line above, the result of unix_timestamp() - IU.start_date is assigned to @usedtime, then the entire variable assignment expression evaluates to the same value, which is eventually stored into the IU.used_time column.

The variable assigned this way can then be used in other expressions that follow. Your update statement, therefore, could be rewritten e.g. like this:

UPDATE
    users IU
    INNER JOIN activity_stats DAS
        ON (IU.user_id=DAS.for_user_id)
SET
    IU.used_time        = (@usedtime := unix_timestamp() - IU.start_date),
    IU.time_period_left = CASE WHEN (@timeleft := IU.total_subscription_time - @usedtime) > 0 THEN @timeleft ELSE 0 END,
    IU.status           = CASE WHEN @timeleft > 0 THEN 'A' ELSE 'I' END,
    DAS.total_time_used = DAS.total_time_used + @usedtime
WHERE
    IU.time_period_left > 0
    AND IU.status='A'
;

Note that, in addition to fixing the syntax in general, I have moved the IU.used_time column assignment to the top, so that the result of unix_timestamp() - IU.start_date could be re-used in the expression for @timeleft. You can keep your order of assignments, of course. Note, however, that IU.used_time in the last assignment

DAS.total_time_used = DAS.total_time_used + IU.used_time

may still hold the old value. So, even if you decide to keep the order of assignments, I would still recommend that you use an expression rather than a column reference there if you want to use an updated value in that assignment.

See a live demo of this syntax at dbfiddle.uk.


Massive deviation of tablespace max_size compared to bytes used by segments on that tablespace

The way you have acquired the tablespace size is incorrect.

  MAX_SIZE(NUMBER) returns Default maximum size of segments (in Oracle blocks).

You can query dba_data_files and dba_free_space to get the result

Following is the exceprt of monitoring script that we have been using.

SELECT * FROM (SELECT a.tablespace_name, SUM(a.tots)/1024/1024-SUM(a.sumb)/1024/1024 Tot_Used_MB, SUM(a.tots)/1024/1024 Tot_Size_MB,
SUM(a.sumb)/1024/1024 Tot_Free_MB
FROM (SELECT tablespace_name,0 tots,SUM(bytes) sumb
FROM dba_free_space a  GROUP BY tablespace_name
UNION  SELECT tablespace_name,SUM(bytes) tots,0 FROM
dba_data_files GROUP BY tablespace_name) a GROUP BY a.tablespace_name);


Are partition numbers guaranteed to be in order of value?

I wasn't able to find a formal guarantee of this behavior, but I did find multiple examples — including a modified version of your original example — in which query optimization decisions appear to be made based on a guarantee that partition numbers are in order by value. Here's what I found:

Your example

Your example code doesn't currently compile, and also uses an index that does not cover the sample query. Here's an updated script that contains working code for your original example.

With these updates, we can see that the query plan for your ORDER BY query does not include a sort operator; query optimization appears to rely on the fact that partition numbers are ordered by value in order to omit the extra sort.

-- Create a partition-aligned index that includes all
-- columns in your SELECT statements
CREATE NONCLUSTERED INDEX [IX_DateRecorded] ON Logs(DateRecorded)
     INCLUDE (FixStatus) ON PS_Month(DateRecorded)
GO

-- Now that your index is properly defined, this query produces
-- an ordered index scan without a sort
SELECT * FROM Logs ORDER BY DateRecorded
GO

Documentation

There is strong evidence in the documentation that partition numbers are in order by value, but I agree that the documentation itself is not conclusive. Here are a few pieces of evidence that I found:

The CREATE PARTITION FUNCTION documentation states that the partitions will initially be in order:

  If the values are not in order, the Database Engine sorts them, creates the function, and returns a warning that the values are not provided in order.

The sys.partition_range_values documentation states that boundary_id, which appears to be used to define the partition number, is an

  ID (1-based ordinal) of the boundary value tuple, with left-most boundary starting at an ID of 1.

The $PARTITION documentation stats that

  $PARTITION returns an int value between 1 and the number of partitions of the partition function.

Further Testing

With a bit of testing, we can see that there are multiple cases in which SQL Server makes query plan decisions that appear to rely on the fact that  partition numbers are in order by value.

Here's the full test script, and here are a few of the most relevant queries:

-- The predicate x BETWEEN 4 AND 7 is converted to a range seek
-- on the partition number, strongly indicating that there is a
-- guarantee that partition numbers are in order by value.
-- Seek Keys[1]:
--      Start: PtnId1001 >= Scalar Operator(...[@1]...),
--      End: PtnId1001 <= Scalar Operator(...[@2]...)
SELECT COUNT(*)
FROM dbo.test_partition_ranges
WHERE x BETWEEN 4 AND 7
GO

-- If we change our predicate to use the partition number directly,
-- we see the same seek predicate.
-- Seek Keys[1]:
--      Start: PtnId1001 >= Scalar Operator(...[@1]...),
--      End: PtnId1001 <= Scalar Operator(...[@2]...)
SELECT COUNT(*)
FROM dbo.test_partition_ranges
WHERE $PARTITION.PF_INT_1to10(x) BETWEEN 4 AND 7
GO

-- If we mix conflicting predicates that use values and partition numbers,
-- SQL Server knows that no partitions are eligible.
-- "Actual Partition Count" is 0 and there are 0 logical reads
SELECT COUNT(*)
FROM dbo.test_partition_ranges
WHERE $PARTITION.PF_INT_1to10(x) > 5
    AND x <= 5
GO

-- All 10 partitions are accessed, and ordering by the partition column
-- does not produce a sort! It appears that SQL Server is once again
-- relying on the partition numbers being in order by value.
SELECT x
FROM dbo.test_partition_ranges
ORDER BY x
GO


Find all the queries executed recently on a database

This query returned some odd results (most of which were a bunch of sprocs). Moreover, all these results show queries executed since today afternoon (I need queries from morning).

That's because you are looking in the procedure cache, and the plans that were used for the morning might no longer live there (due to memory pressure, server/instance restart, manually clearing the proc cache, etc.).

The real way to get the queries executed against an instance (or more specifically a database) would be to create a SQL Trace or an Extended Events session.  Properly created, either of these will give you the information you are looking for.

If you are looking for execution statistics of this morning and only this morning (i.e. setting up the aforementioned monitoring implementations as a proactive and to-do-next-time task isn't sufficient), then unless that was already created there will be no way natively to get this information.

For future reference, start with a SQL Trace that captures the SQL:StmtCompleted event.  In XE it would be the sql_statement_completed event.

In my opinion, instead of continuing your search for remnants of query execution statistics I would start spending time on figuring out how to re-execute the workload that you are trying to measure.  But this time prior to that, set up the proper tracing/monitoring.


Why is MySQL not connecting when reading from mylogin.cnf?

There is a bug here.
You can check your .conf file permissions.
The root cause of crashes or other unexpected behavior will be in the error logs. The default path of the MySQL log file is /var/log/mysql/error.log on Ubuntu.
Also for debugging check this out.


Local Backup from Amazon RDS PostgreSQL database

You must be running in a Security Group firewall problem.

Go to your RDS Dashboard, select Instances and open the instance you want to connect to.
Look for a line like this : Security Groups rds-launch-wizard (sg-3e9axxx) ( active ). You should be able to click on rds-launch-wizard (sg-3e9axxx) which leads you to the EC2 Dashboard in the Security Groups panel.
Select your security group and open the Inbound Tab in the lower panel.
Click on Edit and Add Rule. Don't forget to set the Port Range to 5432 and select My IP in the source.
Click on Save.
You should be able to connect to your RDS Instance from your local IP.


How to skip already created table while importing dump file in MySql

--ignore-table=my_db_name.my_table_name my_db_name > dump.sql


SELECT , GROUP AND DISPLAY IN TABULAR FORM AND COUNTING INSTANCES

SELECT location,
   AVG(CASE WHEN qtr = 'Q1' THEN achievement END) `q1-average`,
   AVG(CASE WHEN qtr = 'Q2' THEN achievement END) `q2-average`,
   COUNT(CASE WHEN achievement BETWEEN 3 AND 4 THEN 1 END) `3.0-4.0`,
   COUNT(CASE WHEN achievement BETWEEN 4 AND 5 THEN 1 END) `4.0-5.0`,
   COUNT(CASE WHEN achievement BETWEEN 5 AND 6 THEN 1 END) `5.0-6.0`,
   COUNT(CASE WHEN achievement BETWEEN 6 AND 7 THEN 1 END) `6.0-7.0`
FROM sourcetable
GROUP BY location

In that form the border value (for example, 4.0) will be calculated both in lower and in upper range. To prevent this use double compare (for example COUNT(CASE WHEN achievement >= 3 AND achievement < 4 THEN 1 END)) instead of BETWEEN.


A SQL query optimzation issue with ORDER BY

The only way you are going to avoid a filesort on this one is if you maintain a materialized view of a JOIN between Game and GamePosition, so that you can create an index across (fen, white_elo).


Should the value of a select max be indexed when the where clause is already indexed

An index on (invoice) is going to typically be fairly meaningless in your case since there are many invoices with the same numbers and an invoice number by itself is almost completely meaningless in your design without knowing which customer it belongs to ... so you should have a UNIQUE index on (customer_id,invoice).

ALTER TABLE customer_orders ADD UNIQUE KEY (customer_id,invoice); -- MySQL syntax

This declaration creates both an index and a constraint -- it allows the server to quickly go to the appropriate customer_id and then quickly go to the largest value associated with that customer ID, all using the new index... and it also constrains you from inadvertently duplicating an invoice number for any reason -- the database will refuse to allow a given customer_id to have two rows with the same value for 'invoice' but will still allow the 'invoice' value to be reused on different customers, which sounds like what you need.

<digression>
If production is on MySQL, I would recommend development and testing on MySQL as well.  Supporting more DBMSs than you need to is likely to be more trouble than it's worth and you could encounter surprises in production if there are cases where SQLite and MySQL behave differently.  They are certainly unlikely to optimize queries uniformly, and if you haven't tested with the same underlying database as is used in production, then I would suggest that you really haven't "tested."
</digression>


What if a connection closes in the middle of a commit?

Short Answer

Add exception handling to ensure a rollback happens on error.

try {
    <code to execute>
} catch (SQLException ex) {
    connection.rollback();
} finally {
    connection.commit();
}

Explanation

if autocommit is set to false, and no connection.rollback() was issued before the program exits, the drivers call an automatic connection.commit() before it closes the connection.


Sql Server - Multiple errors regarding to memory in error log

You're showing some conflicting information. 20% memory utilisation on a 4 GB server would be around 820 MB, however, your screenshots indicate somewhere between 3.8 and 4 GB is consumed by SQL Server at the moment.

Those errors are clearly indicating that SQL Server has insufficient memory available to process a request. While you could, and should, set the max server memory setting to something other than 2147483647, this won't change the fact that SQL Server wants additional memory.

You need to look at optimising the server and workload, apply best practises to the instance to ensure it is running optimally, tune your SQL code to make sure it is not using excessive memory, and you will possibly need to allocate additional memory to the server.

  After restarting the mssql-service there are no problems for something
  like 2 days and then it repeats it self.

This is because SQL Server flushes all of its caches upon restart, and it takes a while to fill that up again, given the low utilisation you've indicated. Setting a max server memory setting between 2 and 3 GB should help alleviate some of these errors because SQL Server will manage memory differently than if the setting is configured to simply consume as much memory as possible, however, it is likely that you will eventually hit these errors again as 4 GB for a Web & SQL Server is quite low.

Here is a link to the SQL Server Memory Management Architecture Guide by Microsoft.
Here is a link to SQL on Linux Performance Best Practises by Microsoft.
Here is a collection of best practise tips for SQL Server that you might find useful.


How to import long strings at different column positions with SSIS?

I would not assume it will have huge consequences to performance. Handling this is what Varchar(Max) is for and it's very unlikely to have any impact.

Regarding your specific question about alternatives however, the only one is to use sparse column sets. This allows you to pack a lot of potential but usually null columns into one actual XML column. Then they only get stored inside if they are actually used. They can be selected from directly using their column name, though if you Select * then you only get the raw XML column.

Microsoft documentation is here: https://msdn.microsoft.com/en-AU/library/cc280521.aspx


Getting vacuumdb :invalid page header in block error while vacuuming

The error you pasted indicates that there has been corruption in your database.  The best approach to dealing with this kind of corruption is to shut down the database, make a complete copy of all of the files, and then restore them to another system where forensics can be performed.

If you have a complete copy of your database, such that you can retrieve any valid data from it, then you can use the "zero_damaged_pages" configuration parameter in PostgreSQL to have invalid or damaged pages wiped out entirely, allowing the database to progress even in the face of corrupt or invalid data.  Note that this option will cause you to lose valid data, as the entire page will be completely wiped out if any piece of it is deemed to be damaged or invalid.

Depending on the specific corruption which has occurred in this database, it may be possible to retrieve some of the data.  This retrieval is extremely time consuming and requires expertise knowledge of the database system and is generally only sensible when the data is extremely valuable.


Why does an sqlite3 .dump and restore result in a smaller database file size?

With the default settings, when rows (or entire tables) are deleted, SQLite does not remove empty pages from the database file. (This setting can be changed with PRAGMA auto_vacuum.)

In most cases, keeping empty pages in the database file is good idea because they will be reused by new rows that are inserted later, and adjusting the database file to remove those pages, only to reallocate them later, would be unneeded overhead.


Apply limit and conditions per grouped data

One way to solve such problems, is using window functions:

WITH cte AS
    ( SELECT date, product_name, count,
             ROW_NUMBER() OVER (PARTITION BY date
                                ORDER BY count DESC) AS rn
      FROM sales
      WHERE date >= '2015-07-01'     -- example range
        AND date < '2015-08-01'      -- 2015, July
    )
SELECT date, product_name, count
FROM cte
WHERE rn <= 2           -- max of 2 products per date
ORDER BY                     -- this does not affect the result
    date, count DESC ;       -- only its order

If there are ties, they will be resolved arbitrarily and only 2 rows per date will be returned. If you want to resolve ties with a specific preference, you can change the order inside the OVER clause. Say you want the products' names to be sorted alphabetically when they tie in top sales, use (... ORDER BY count DESC, product_name).

If you want all the (tied) products per date, just change ROW_NUMBER() to RANK().


When to use mysqldump with '--no-autocommit'? My benchmark shows slower restore

With autocommit=1, each INSERT will be committed before moving on to the next.  There is less overhead.  But, if the server crashes, the table will be half built because some INSERTs will be committed; some won't.

With

SET autocommit=0;
INSERT...
INSERT...
...
commit;

it will put all the info into a redo log for the entire table.  For large tables, this is more overhead.  Even for a tiny tables (only one INSERT), it might be a little slower due to the extra two statements.


Turns out it was phpMyAdmin - needed to show full texts.

Just above the SQL output there is an 'Options' link. Click that and you will see Partial texts/Full texts radio buttons.

Note, that when I went back to get the screenshot it was still set to Full Texts so I assume there is a cookie being set which may explain why it was different in one database to the other.


How to prevent locking and possible performance issue

Don't use MyISAM; use InnoDB.  (to avoid "table locks")
Build and maintain Summary tables.  (often 10x faster)
Let's see the slow queries; maybe they can be improved.


Oracle - Auditing for only some top-level statements

Oracle does not support negative audit rules.

The only exception is the EXCEPT clause for Unified Audit policies which excludes the given users from auditing, but not statements.


sqlplus is not in your path.

Use su - oracle instead of su oracle, as that will read the users ~/.bash* files & set the environment accordingly.


Does the SQL Server optimizer inline function calls in stored procedures?

An in-line table valued function (TVF) is simply a macro that expands into the containing query: no more, no less. Just like a standard view.

A multi-statement TVF on the other hand has to run to completion and won't be optimised for subsequent operations on the results


I stuck on the network's settings with Griddb on my ubuntu

Ubuntu generally renames the network devices. You can list them all with ip a. The first device with be the loopback, which is usually 127.0.0.1. The second device is often (but not always) the one that is used for external communications and will look something like enp0s3.


I would guess that you need to also insert the visitorcode; and your spacing looks weird in the image.

merge SalesTrainingDW.dbo.Visitor      as target
  using SalesTrainingStage.dbo.Visitor as source
     on   target.VisitorCode =  source.VisitorCode
      and target.VisitorName <> source.VisitorName
      when not matched then insert (VisitorCode, VisitorName)
        values (source.VisitorCode, source.VisitorName)
      --output $action, deleted.VisitorCode, deleted.VisitorName --changed to reflect M. Ali's point
      output $action, inserted.VisitorCode, inserted.VisitorName
        into @DimVisitorLog (ActionX, VisitorCode, VisitorName);

insert into SalesTrainingDW.dbo.VisitorLOG (VisitorCode, VisitorName, ActionX, stamp)
select VisitorCode, VisitorName, Actionx, stamp=getdate()
  from @DimVisitorLog
  where ActionX = 'insert';  --changed to reflect M. Ali's point

You can also skip the table variable like this:

insert into SalesTrainingDW.dbo.VisitorLOG (VisitorCode, VisitorName, ActionX, stamp)
  select VisitorCode, VisitorName, ActionX, stamp=getdate()
  from (
    merge SalesTrainingDW.dbo.Visitor      as target
      using SalesTrainingStage.dbo.Visitor as source
         on   target.VisitorCode =  source.VisitorCode
          and target.VisitorName <> source.VisitorName
          when not matched then insert (VisitorCode, VisitorName)
            values (source.VisitorCode, source.VisitorName)
          --output $action, deleted.VisitorCode, deleted.VisitorName --changed to reflect M. Ali's point
          output $action, inserted.VisitorCode, inserted.VisitorName
      ) as i (ActionX, VisitorCode, VisitorName)
      where ActionX = 'insert'

Since this operation is only inserting values, you can skip merge and do an insert. Unless you are going to use the extra options merge gives you on output:

insert SalesTrainingDW.dbo.Visitor (VisitorCode, VisitorName)
  output ActionX='insert', inserted.VisitorCode, inserted.VisitorName, stamp=getdate()
    into @DimVisitorLog (ActionX, VisitorCode, VisitorName, stamp)
  select  VisitorCode, VisitorName
    from  SalesTrainingStage.dbo.Visitor s
      where not exists ()
        select 1
          from SalesTrainingDW.dbo.Visitor v
          where s.visitorcode=v.visitorcode
          and s.visitorname=v.visitorname
          );

or with a left join instead of not exists:

insert SalesTrainingDW.dbo.Visitor (VisitorCode, VisitorName)
  output ActionX='insert', inserted.VisitorCode, inserted.VisitorName, stamp=getdate()
    into @DimVisitorLog (ActionX, VisitorCode, VisitorName, stamp)
  select  VisitorCode, VisitorName
    from  SalesTrainingStage.dbo.Visitor s
      left join SalesTrainingDW.dbo.Visitor v on s.visitorcode=v.visitorcode and s.visitorname=v.visitorname
    where v.visitorcode is null;

and you could also skip the table variable with either of those inserts:

insert into SalesTrainingDW.dbo.VisitorLOG (VisitorCode, VisitorName, ActionX, stamp)
  select i.VisitorCode, i.VisitorName,  ActionX = 'insert', stamp=getdate()
  from (
    insert SalesTrainingDW.dbo.Visitor (VisitorCode, VisitorName)
      output inserted.VisitorCode, inserted.VisitorName
      select  VisitorCode, VisitorName
        from  SalesTrainingStage.dbo.Visitor s
        where not exists (
          select 1
            from SalesTrainingDW.dbo.Visitor v
            where s.visitorcode=v.visitorcode
              and s.visitorname=v.visitorname
              )
  ) as i;


Stored Procedure for Vacuum in PostgreSQL

vacuum cannot run in a transaction and cannot run in a function or in a procedure.

You can try instead to generate the vacuum statements with the list of tables to be processed with psql CLI and \gexec internal command to run them.

With:

select format('vacuum %s.%s', schemaname, tablename)
from (select schemaname, tablename
     from pg_tables where tablename like 't%'
) lt;
\gexec

Execution says:

select format('vacuum %s.%s', schemaname, tablename)
from (select schemaname, tablename
     from pg_tables where tablename like 't%'
) lt;
           format
----------------------------
 vacuum public.ta
 vacuum private.t
 vacuum public.test
 vacuum s1.t
 vacuum s2.t
 vacuum public.tasks2020_04
 vacuum public.t1
 vacuum public.t2
 vacuum public.t0
 vacuum public.t3
 vacuum public.tts
 vacuum public.t0_0
 vacuum public.tasks
 vacuum public.tasks2020_05
 vacuum public.tasks2020_06
 vacuum public.tasks2020_07
 vacuum pub.t
 vacuum public.t
 vacuum public.tb
(19 rows)

select format('vacuum %s.%s', schemaname, tablename)
from (select schemaname, tablename
     from pg_tables where tablename like 't%'
) lt;
vacuum public.ta
VACUUM
vacuum private.t
VACUUM
vacuum public.test
VACUUM
vacuum s1.t
VACUUM
vacuum s2.t
VACUUM
vacuum public.tasks2020_04
VACUUM
vacuum public.t1
VACUUM
vacuum public.t2
VACUUM
vacuum public.t0
VACUUM
vacuum public.t3
VACUUM
vacuum public.tts
VACUUM
vacuum public.t0_0
VACUUM
vacuum public.tasks
VACUUM
vacuum public.tasks2020_05
VACUUM
vacuum public.tasks2020_06
VACUUM
vacuum public.tasks2020_07
VACUUM
vacuum pub.t
VACUUM
vacuum public.t
VACUUM
vacuum public.tb
VACUUM


Array column vs another table when the relationship is quite small

There is no clear answer to that. Both variants have pros and cons. Many here will advice to properly normalize data with a separate table.

You might get best of both worlds with a MATERIALIZED VIEW:
Properly normalize your data model, and offer an aggregated view on the data in the materialized view.


SQL Server 2012 - Full Text Search [Break word - Underscore]

I found it! :)

I changed the Language for Word Breaker in the Full-Text Catalog to Traditional Chinese.

I used the sys.dm_fts_parser in order to find what language could use the underscore as a break word.

1033 - English

1028 - Traditional Chinese

select * from sys.dm_fts_parser('Oblivion_PS3', 1028, 5, 0)

return Oblivion AND PS3

select * from sys.dm_fts_parser('Oblivion_PS3', 1033, 5, 0)

return Oblivion_PS3


MySQL Master 5.5 - Slave 5.6 - unknown system variable 'binlog_checksum', Error_code: 1193

If your run SHOW SLAVE STATUS\G on the Slave, and you see Slave_IO_Running: Yes, then the IO Thread is fine.
Notwithstanding, please note the Warnings

2016-11-18 22:48:03 12808 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0

Error Code 0 means it's not an error, just informational.

2016-11-18 22:48:03 12808 [Warning] Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Error_code: 1193

You mentioned the error involves the variable binlog_checksum.
According to the MySQL Documentation for binlog_checksum

Enabling this option causes the master to write checksums for events written to the binary log. Set to NONE to disable, or the name of the algorithm to be used for generating checksums; currently, only CRC32 checksums are supported. As of MySQL 5.6.6, CRC32 is the default.
This option was added in MySQL 5.6.2.

Since your Master is 5.5.42, there is no binlog_checksum available to compare the Master to the Slave. The warning is valid.
Perhaps setting binlog_checksum to 'NONE' on the Slave might disable this check.

2016-11-18 22:48:03 12808 [Warning] Slave I/O: Unknown system variable 'SERVER_UUID' on master. A probable cause is that the variable is not supported on the master (version: 5.5.42-log), even though it is on the slave (version: 5.6.31-log), Error_code: 1193

This is the same problem. The option server_uuid does not exist in MySQL 5.5.42 (the Master) as the message itself says.
EPILOGUE
As long as replication is running you are OK for the moment. It strongly recommend get your Master to MySQL 5.6.31 ASAP. Why ?
MySQL 5.6 introduced microseconds to DATETIME. If the Master (5.5.42) sends a DATETIME to the Slave (5.6.31), the IO Thread will break immediately. Please read my post from 2015-01-02 where I mentioned this.
You could work around this DATETIME mess by switching to STATEMENT replication (setting binlog_format to STATEMENT) on both Master (5.5) and Slave (5.6).


Size of a MyISAM table doubles after OPTIMIZE

It's a 4 month ago filed bug and it is fixed in MySQL 8.0.22.


If I understand correctly you are asking:
"Which would be better and faster, to run everything on 1 machine and have clients directly connect to the server to remove networking as a potential bottleneck, or having clients access the server from another machine by moving the web services and log in to that other machine?"

In I.T. we usually try to squeeze out all the performance we can for every dollar thus I could see why you would ask this, but this is a dangerous path that is being entertained.

Firstly, what you're really talking about is resource utilization and where does the bottleneck lie.  If your bottleneck is in fact networkIO, then this would help performance but you run A LOT of risks which are outlined below.  However, if NetworkIO REALLY is the bottleneck, you have to fix that.  Use NOCOUNT ON in your code, verify the network is stable.  Rarely is network_io the bottleneck compared to diskIO or RAM.

What DB Engine are you using?  If you're using MS SQL Server you could do a simple

SELECT * FROM SYS.DM_OS_WAIT_STATS ORDER BY 3 DESC

How high does network IO show up on the list?  Depending on your version you should get 450-500 rows or so returned.  If NetworkIO isn't even in the top 50 then you probably shouldn't entertain this idea.

Having Everyone Directly Connect On 1 Machine And Run DB/Web Services Locally:

In a world where I.T. best practices trump all else, then you would without a doubt separate client access from the main machine.  There is just way too many things that could go wrong.  For example, if you're using SQL Server and SSMS, SSMS itself will EASILY eat up 500MB per instance.  That means every client who connects and opens up SSMS will eat up RAM, so if you have 20 people connecting, you just ate up 10GB ram.

Also while devs might be great at development, but unless you have a strong DevOps environment they cannot be trusted in prod.  They are under the gun to get things to run fast and will just hack around.  When you remove devs from prod all kinds of mystery issues immediately leave.

Security would be another risk.  In many I.T. environments I've consulted at, security was an afterthought.  I wouldn't be surprised if devs and users would have access to install files, create shares, change settings, etc. on your prod server.  This is horrible.

How about if they start saving large files on your drives and you run out of disk space?

Having everyone connect from a web server:

In this scenario, users are still connecting to the web server directly which means they could stop web services, however those could typically be recovered fast and it won't affect the overall health of the DB server.  You isolate the DB from everyone and this is a much safer alternative.


Hyphen in my computer's name causing T-SQL error

When you have special characters in a name put []'s around it to let SQL know that it's an identifier.  This is also how you manage special characters.

So in your case

Drop Login [John-PC\John]


MYSQL workbench & other IDEs not working on fresh OS after reinstall?

The issue was resolved by installing MySQL server. Recommend all to use MySQL installer web version which automatically lets you choose what packages you wish to install (this might even include something you wouldn't even install on your own), and it will download, install, and configure the latest versions of the packages on its own.

Thanks


Getting Postgres CREATE TABLE statements

I don't know of any Postgres-specific tool that can do that, maybe you should approach this from the Django perspective: https://docs.djangoproject.com/en/1.8/ref/django-admin/#django-admin-sql


What can become a reason of database failure?

The number one reason is operator error. The 'whops, I dropped the wrong table' moment.

Next are catastrophic hardware failures (lost, unrecoverable, disk). Non-disk failures are actually less problematic because the data can usually be retrieved by attaching the (still working) disks to a different host.

Disk I/O errors (corruption of media at rest) used to be a big issue, but nowadays all DBs have page checksum enabled by default and can catch media corruption.

Memory and processor errors can cause corruption in-memory, before writing to disk, and thus defeat checksums, but such errors manifest all over the host process(es) and are usually detected as 'the server is crashing randomly'. Unfortunately, often times by the time action is taken the data is already randomly corrupted.

Application logical corruptions are often a big deal ("the app has written all fields with 'foo' when it should 'bar' ") but those are seldom seen as 'corruption' and can be corrected.

And then there are engine code defects. You can't really defend against them, other than keep an eye on the community pulse and be aware when issues are discovered and publicized, keep your servers patched up to date.


When migrating all the way down, should I just run VACUUM (FULL) as the last down migration?

Other than taking time and resources to run vacuum full will do no harm.

The autovacuum process will eventually trim empty tables. but tables that are merely sparse will continue to consume disk space until a vacuum full.


Does local.oplog.rs hold all data in mongodb replication?

The MongoDB oplog is a special capped collection including all data used for replication. The oplog alone only includes an idempotent log of data changes. You can use the oplog for incremental backup and point-in-time restoration for a replica set, but note that an oplog backup is only valid to apply against an existing full data backup of the same replica set with a common oplog entry.

If you want to backup the oplog.rs collection independent of the rest of the data files you will have to use mongodump, which can have a significant effect on a production deployment as all of the oplog data needs to be read into memory by mongodump.  The oplog.rs collection also has a special caveat on indexing: it is designed to be scanned in $natural order and does not support adding any indexes.

A typical incremental backup approach for a production system is using filesystem snapshots assuming your deployment (and filesystem) meet the requirements for a consistent backup. Filesystem snapshots capture the changes since the last snapshot, so are incremental after the first snapshot. Ongoing snapshots are less impactful for an active deployment than mongodump.

Another common approach is using agent-based backup (for example, MongoDB Cloud Manager or MongoDB Ops Manager). Agent-based approaches generally also use the oplog for real-time backup. See Cloud Manager Backup Preparations for some background.

The MongoDB manual is the best reference for supported backup methods for your version of MongoDB server. Backing up using mongodump for the oplog alone is not a recommended (or documented) backup method.


Find which permissions are being used for a given user on a object

Have a look at sys.user_token (http://technet.microsoft.com/en-us/library/ms188421.aspx) and sys.login_token (http://technet.microsoft.com/en-us/library/ms186740.aspx). They tell you which roles/logins/users SQL Server is involving when figuring out the permissions for the current user.

Then you can use sys.database_permissions (http://msdn.microsoft.com/en-us/library/ms188367.aspx) and sys.server_permissions (http://msdn.microsoft.com/en-us/library/ms186260.aspx) to get the permissions associated with each token. (They even tell you who granted the permission.)

Finally, sys.database_principals (http://msdn.microsoft.com/en-us/library/ms187328.aspx) and sys.server_principals (http://msdn.microsoft.com/en-us/library/ms188786.aspx) can be used to translate the principal_id into a name.

I took a stab at combining the system views above. It might not be perfect, but it should get you started:

SELECT
        T.name token_name,
        T.type token_type,
        T.usage,
        T.is_login_token,
        UDP.name user_name,
        UDP.type_desc user_type_desc,
        UDP.default_schema_name,
        UDP.create_date,
        UDP.is_fixed_role,
        --UDP.authentication_type_desc,
        SP.name login_name,
        SP.type_desc login_type,
        SP.create_date,
        --SP.is_fixed_role,
        DP.class_desc,
        DP.permission_name,
        DP.state_desc,
        QUOTENAME(OBJECT_SCHEMA_NAME(O.object_id))+'.'+QUOTENAME(O.name) object_name,
        O.type_desc object_type_desc,
        C.name column_name
  FROM (SELECT 0 is_login_token,* FROM sys.user_token
        UNION ALL
        SELECT 1 is_login_token,* FROM sys.login_token
       )AS T
  LEFT JOIN sys.database_principals AS UDP
    ON T.principal_id = UDP.principal_id
   AND T.is_login_token = 0
  LEFT JOIN sys.server_principals AS SP
    ON (UDP.sid = SP.sid AND T.is_login_token = 0)
    OR (T.principal_id = SP.principal_id AND T.is_login_token = 1)
  LEFT JOIN sys.database_permissions AS DP
    ON (DP.grantee_principal_id = UDP.principal_id
        AND T.is_login_token = 0)
    OR (DP.grantee_principal_id = SP.principal_id
        AND T.is_login_token = 1)
  LEFT JOIN sys.all_objects AS O
    ON DP.major_id = O.object_id
  LEFT JOIN sys.all_columns AS C
    ON DP.major_id = C.object_id
   AND DP.minor_id = C.column_id;


Yes you really need to run checkdb on ALL the secondary replicas. If you read MS document on Automatic Page repair it says

Automatic page repair cannot repair the following control page types:
File header page (page ID 0).
Page 9 (the database boot page).
Allocation pages: Global Allocation Map (GAM) pages, Shared Global Allocation Map (SGAM) pages, and Page Free Space (PFS) pages.

So you can see not all types of repairs are fixed by automatic page repair. What if you encounter corruption not coming under error fixed by automatic repair.
If you are using availability groups on WSFC without FCI the storage would be local storage so you need to separately run checkdb and analyze storage for each replica.


UPDATE Query replacing string primary key taking days to resolve

For the first query it can run fairy fast, but you'd need some temporary functional index for a join:

create index bigtable_old_id_2 on bigtable( split_part(bigtable.old_id,'.',2) );
create index foreigntable_old_id on foreigntable(old_id);

This will allow Potgres to quickly find matching row in bigtable.

You can drop these indexes afterwards if you don't need them anymore.


Difference between init.ora db_name and query in sqlplus

During startup , Oracle searches for initialization file :

  In the platform-specific default location, Oracle Database locates
  your initialization parameter file by examining filenames in the
  following order:

  spfile$ORACLE_SID.ora
  spfile.ora
  init$ORACLE_SID.ora
  The first two
  filenames represent SPFILEs and the third represents a text
  initialization parameter file.

In your screenshot you have spfileBO1.ora, and it seems your $ORACLE_SID environment variable is set to BO1.  If you want to start the server using init.ora, you should do it manually  :

SQLPLUS command:

STARTUP PFILE = /path/init.ora


Using CROSS APPLY OPENJSON causes Azure to hang

The fact that you cannot obtain even an estimated execution plan for the query against the large table suggests that compilation is waiting on statistics creation (or update) for the nvarchar(max) column.

When running against the smaller sample table, statistics creation completes quickly, so an execution plan is quick to create.

I can reproduce your situation on a low-powered (S0) Azure SQL Database. On a slightly less anaemic S3 instance, the compilation still takes around 30 seconds due to the large amount of I/O involved in reading the nvarchar(max) column to generate statistics. The premium tier is needed to get good I/O performance - even a cold P1 can get these statistics created in 3 seconds or so.

You can confirm the cause by temporarily disabling automatic statistics creation:

ALTER DATABASE CURRENT SET AUTO_CREATE_STATISTICS OFF;

The SQL server will then produce a plan quickly, albeit with warnings about missing statistics:

Be sure to re-enable this feature after testing because good statistics are vital to general execution plan quality. Or commit to creating necessary statistics manually.

If the issue is instead automatic refresh of stale statistics, you could choose to have these updated asynchronously:

ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS_ASYNC ON;

Alternatively, scale your Azure SQL Database up to a performance level that suits your workload and budget.


Update all rows but with limited number at a time

I would set up a WHILE loop similar to the following example:

set nocount on
IF OBJECT_ID('tempdb..#TableToUpdate') IS NOT NULL
    drop table #TableToUpdate
create table #TableToUpdate (KeyColumn int IDENTITY(1,1) NOT NULL,OriginalColumn int, NewColumn int)
go
insert into #TableToUpdate(OriginalColumn,NewColumn) values(1,1)
go 105

declare @RowsInTable int
declare @BeginRange int = 0
Declare @EndRange int = 0
Declare @Increment int = 10
set @RowsInTable=(select count(*) from #TableToUpdate)

while @EndRange < @RowsInTable
begin

set @EndRange += @Increment

print 'updating ranges from ' + convert(varchar(100),@BeginRange) + ' to ' + convert(varchar(100),@EndRange)

update #TableToUpdate
 set NewColumn = OriginalColumn
 where KeyColumn between @BeginRange and @EndRange

set @BeginRange = @EndRange

end
GO


Simply put, nodetool cleanup will remove data from a node that it is no longer responsible for.  This can happen when adding new nodes to a cluster, as they assume token ranges once held by other nodes.  Once the data is streamed to the new node(s), it isn't automatically removed from the pre-existing nodes.  This is intentional, in that if a new node fails to bootstrap no data is lost.

Running a cleanup will indeed remove potentially orphaned replicas.  Normally, it is a good idea to ensure that repairs have been successfully completing prior to running nodetool cleanup.

In this case, as repair has never been run, I can't imagine that the application users really care about data consistency at this point.  But one thing to try, might be to run cleanup on one node to get some space back, followed by repair on the same node.  Work your way through the cluster running repair/cleanup (in that order) on each.  That should minimize the risk of data loss, while allowing for restoration of some semblance of consistency.


SQL query: insert values into polygon where features of the polygon intersects another polygon

You're not allowed to reference table_A in the insert inside the select. The query needs to work stand-alone.
The following should work (untested):
insert into schema.table_A(columnKJ1)
select "columnPO4"
from schema.table_B
join schema.table_A on st_intersects(table_a.geom, table_B.geom);


Is there a plugin to add a map behind the spatial results?

There is but as you have found out it is rather limited. I think you will have to try another tool / software Power BI has quite good geographical support or you could try real GIS software such as ArcGIS or MapInfo or one of the many opensource GIS projects.
Can't give a proper answer re BingMaps or Google maps, pretty sure you need to write some code and also purchase some cloud credits for the relevant service.


SQL Server 2000 Backup fails with Status = 33

Further investigation and "Googling" solved the issue, The issue was with drive, It was compressed and SQL Server did not like that.

Solution

Removed the compression from Drive and this time backup completed without any errors and much faster.


Package deployed in SSIS 2012 catalog fails with timeout error

delete Unused Connection String(specially those have invalid connection string) search for child Package reference that are disable but has invalid references.


In [Percona] MySQL, is the Adaptive Hash Index included in the Total memory allocated count?

Just going by the MySQL Documentation, I would say yes.

According to the MySQL 5.6 Documentation on innodb_adaptive_hash_index

  Disabling the adaptive hash index empties the hash table immediately. Normal operations can continue while the hash table is emptied, and executing queries that were using the hash table access the index B-trees directly instead. When the adaptive hash index is re-enabled, the hash table is populated again during normal operation.

If you really want to know for sure, login as root@localhost, run this

SET GLOBAL innodb_adaptive_hash_index = 'OFF';

and look at the memory stats again. They should be zero and memory counts should reflect that.


This happens because SET STATISTICS TIME ON will give you summarized time results for a stored procedure in addition to time results for each query that's part of the stored procedure. Let's run a simple test to try to reproduce what you saw. The following code isn't important except that it runs for a few seconds on my machine:

set statistics time on;

WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
SELECT AVG(CAST(N AS BIGINT))
FROM cteTally
WHERE N <= 10000000;

WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
SELECT AVG(CAST(N AS BIGINT))
FROM cteTally
WHERE N <= 1000000;

Abbreviated output from SET STATISTICS TIME ON:

  SQL Server Execution Times:    CPU time = 3407 ms,  elapsed time =
  3450 ms.

  SQL Server Execution Times:    CPU time = 343 ms,  elapsed time = 388
  ms.

As expected, there are two separate measurements for the execution time with the second query running in about one tenth of the time of the first. Now I'll create and run a stored procedure that just calls the above code. Abbreviated output from SET STATISTICS TIME ON:

  SQL Server Execution Times:    CPU time = 1187 ms,  elapsed time =
  1201 ms.

  SQL Server Execution Times:    CPU time = 110 ms,  elapsed time = 117
  ms.

  SQL Server Execution Times:    CPU time = 1297 ms,  elapsed time =
  1319 ms.

The math works out for my simple example: 1187 + 110 = 1297 CPU time and 1201 + 117 = 1319 elapsed time.


MYSQL: Values of a column where two other columns have same value

You mean something like

SELECT col2, col3 FROM table WHERE col2 = col3 GROUP BY col2, col3
HAVING COUNT(DISTINCT col1) > 1;

Would show you the col2/col3 combinations where col2 = col3 but col1 differs between those rows.


Foreign Key (references) constraint on PostgreSQL Foreign Data Wrapper?

The PostgREST team gave a potential solution: faking the foreign key constraints.

They read the pg_catalog.pg_constraint table to get this information.  The idea was that I could manipulate the search_path and create my own pg_constraint table where I could make the fake foreign keys:

CREATE TABLE pg_constraint (LIKE pg_catalog.pg_constraint);

ALTER ROLE postgrest IN DATABASE p2k SET search_path = public, remote, pg_catalog;

I was able to make such a table, and the query they use gave something reasonable back.  However it seems to fail for some other reason.

It's also hackish and quite a bother to create the information in pg_constraint correctly.  So I probably won't accept this answer, just wanted to put it here because it might work for someone else / some similar use case.


You need to create synthetic table containing all the intervals in Your timeline range. Or the numbers table when range limits are easily calculated from the range number.

Example:

CREATE TABLE test (val INT);
INSERT INTO test VALUES (0),(55),(150),(420),(490),(550);

SELECT (nums.num-1)*200 mini, nums.num*200 maxi, COUNT(test.val) cnt
FROM (SELECT 1 num UNION ALL SELECT 2 UNION ALL SELECT 3) nums
LEFT JOIN test ON (nums.num-1)*200 <= test.val AND test.val < nums.num*200
GROUP BY nums.num;

The numbers count is to be equal (or more, not less) to the ranges count.

If You want to change the amount of ranges dynamically influenced by data values range (with some reasonable limit) You may use the next method to create synthetic table nums (for example if ranges count guaranteed less than 1000):

SELECT nums1.num*100+nums2.num*10+nums3.num num
FROM (SELECT 0 num UNION SELECT 1 UNION ... UNION SELECT 9) nums1,
     (SELECT 0 num UNION SELECT 1 UNION ... UNION SELECT 9) nums2,
     (SELECT 0 num UNION SELECT 1 UNION ... UNION SELECT 9) nums3

To cut off the upper empty ranges use additional condition like

WHERE ... AND nums.num * @step <= (SELECT MAX(val) FROM datatable)


Should I put followers_no & following_no & product_no & last_login and etc in the user table? Won't updates cause fragmentation on the table?

It most certainly will cause fragmentation, but you must compare it with normalization.

FRAGMENTATION

Fragmentation is introduced in a heavy-write environment.

DELETEs automatically create empty space by at least the size of the row at the time of deletion.
UPDATEs can also cause fragmentation, mostly notably on variable-length data.

Further Links on InnoDB Fragmentation and how to Eliminate it from InnoDB data and system tablespace

http://www.bluegecko.net/mysql/innodb-tablespace-fragmentation-find-it-and-fix-it/
How do you remove fragmentation from InnoDB tables? (April 11, 2012)
Howto: Clean a mysql InnoDB storage engine? (My StackOverflow Post From Oct 29, 2010)

NORMALIZATION

Any columns you have in a user table that are immutable (i.e., will never experience changes) should act as the main table. Things such as

Address Change
Spelling Correction
Last Name Change (in case a lady get married)

will produce very little fragmentation since changes of those kinds are rare.

Any information that logs frequent changes to user information should go into a userinfo table. This will separate fragmentation issues from the immutable user data. You can easily defrag an InnoDB userinfo table with one of the following

ALTER TABLE userinfo ENGINE=InnoDB;
OPTIMIZE TABLE userinfo;

CONCLUSION

You should split up the user data into user and userinfo tables. A simple INNER JOIN will combine them as needed.


Snapshot Azure database for fast revertion to snapshot

SQL Server native backups are not available on Azure SQL. Here you have automated backups with a retention period of up to 35 days or you have long-term backups that you can restore.

If you do not want to use long term backups, you can export your databases as bacpac to Azure BLOB storage and import that bacpac at any time you want. You can also export your database to a local on-premises storage.

Additionally you can create a copy of your databases, remove existing databases when you want, and rename the copy database as you wish.


You can use the information available in sys.dm_db_index_usage_stats for that purpose. The following query returns the number of reads and the number of writes against each database since the last server restart. That information is stored in memory only and therefore not preserved past a server restart.

SELECT D.name AS database_name, D.state_desc,D.is_read_only,X.read_cnt, X.write_cnt
  FROM sys.databases AS D
  LEFT JOIN(
       SELECT database_id,
              SUM(DDIUS.user_seeks+DDIUS.user_scans) AS read_cnt,
              SUM(DDIUS.user_updates) AS write_cnt
         FROM sys.dm_db_index_usage_stats AS DDIUS
        GROUP BY DDIUS.database_id
      )X
    ON D.database_id = X.database_id
 ORDER BY database_name;

The numbers represent the count of "actions" against the tables in a database. You cannot judge the amount of data read or written based on these counts. The DMV also includes a user_lookups column. I did not include those, as a lookup usually implies another read action. For example a single index scan can cause thousands of lookups. But it still is only a single read action.


With ENGINE=InnoDB, the entire table must be scanned to perform the count.  Actually, any index can be used, and the Optimizer picks the smallest index (or the PRIMARY KEY if no secondary indexes exist.

If the table (or index) is not currently cached in the buffer_pool, then the query will take longer because of all the I/O necessary.

The reason for having to touch all the rows is that there could be other connections adding/deleting rows during the count, and InnoDB is obligated to provide the answer that is compatible with various transactional integrity requirements.  (Your Question has been asked many times; other Answers may provide more details.)


AD group cannot connect to SQL from excel

Ah group permissions in SQL Server have never worked out for me right off the bat. I would check this question and make sure you are setting it up right on the SQL side https://stackoverflow.com/questions/5029014/how-to-add-active-directory-user-group-as-login-in-sql-server

Also I agree with John, if you add a member to a group they need to log off and back on again before AD will update with the new group.


Can I do a backup without extension functions?

The best solution is to restore into a database that does not have PostGIS installed yet.

If PostGIS is installed in the template1 database, use template0 to create the new database.


MongoDB - Should I separate the journal and the data to different drives?

The only time that moving the journal is an absolute recommendation is if you have to use a direct NFS mount - NFS is not recommended for MongoDB in general but in particular it does not play well with the journal.

In general, the journal will have quite a different access pattern to the rest of your data (sequential versus random access).  Hence it is often a good idea to separate the journal and the data from a performance perspective.  Note that this is a very broad generalization and will vary depending on your usage of MongoDB, but will generally be true.

Since your questions are largely about EC2 and EBS then NFS will not play a part here, but I thought it worth mentioning in a broader context, onto specifics.

For EC2/EBS, you will generally only need to separate the journal out if you are seeing write IO contention (or overall IO contention given the nature of EBS) - it will move IO to a different disk and free up some capacity on the data disk.  Of course, with EBS your IO is also dependent on the network IO available on the instances and that is dependent on your instance size (and whether you have opted for P-IOPS), hence a lot of variables.

If you are seeing high IOWait times and your disk looks write bound (see IOStat), this is something you should consider, but only as a temporary measure, because it will only be a small tweak compared to increasing available IO.  There are plenty of options available there depending on your starting point, like adding more EBS drives to the RAID or by taking advantage of P-IOPS provisioning.  You can also occasionally get better performance by changing instance type so that you have less network contention.  Each of these may be more effective than moving the journal and have less headaches.

To explain, there are other considerations here - snapshots for one.  Thanks to the journal you no longer have to fsync and lock the database to get a consistent snapshot (be it EBS or LVM or other).  However the journal has to be included in the snapshot for that to be the case.  Hence whatever node you use for backing up, if you intend to snapshot without taking downtime for that node, then you need to make sure the journal is included.

Finally, one of the uses of the journal is to facilitate recovery from an unclean shutdown, such as an OS level crash/reboot.  If the journal is on the ephemeral disk in EC2, it will be blown away by such a reboot and hence not be useful in that context.  Any such crash/reboot with such a configuration would therefore require a resync from scratch or a restore from backup/snapshot should it occur.

Overall, like most configuration decisions, you have to weigh the pros and cons and pick the solution most relevant for your use case.  Hopefully this will give you enough information to make an informed choice.


Postgres doesn't use expression index for group by

There is this code in check_index_only in src/backend/optimizer/path/indxpath.c:

/*
 * check_index_only
 *      Determine whether an index-only scan is possible for this index.
 */
static bool
check_index_only(RelOptInfo *rel, IndexOptInfo *index)
{

[...]

    /*
     * Construct a bitmapset of columns that the index can return back in an
     * index-only scan.  If there are multiple index columns containing the
     * same attribute, all of them must be capable of returning the value,
     * since we might recheck operators on any of them.  (Potentially we could
     * be smarter about that, but it's such a weird situation that it doesn't
     * seem worth spending a lot of sweat on.)
     */
    for (i = 0; i < index->ncolumns; i++)
    {
        int         attno = index->indexkeys[i];

        /*
         * For the moment, we just ignore index expressions.  It might be nice
         * to do something with them, later.
         */
        if (attno == 0)
            continue;

So if I read that right, expressions are ignored when an index only scan is considered.

I don't think that's a matter of principle, it is just not implemented.

From PostgreSQL v12 on, you could use a case-insensitive ICU collation to get what you want:

CREATE COLLATION und_ci (
   LOCALE = 'und-u-ks-level2',
   PROVIDER = icu,
   DETERMINISTIC = FALSE
);

Then you'd have to change the column to use that collation:

ALTER TABLE contacts ALTER home_city TYPE text COLLATE und_ci;

This index can be used for case insensitive searches, and you can use it for an index only scan too.


Unfortunately, your understanding is a misunderstanding, and REORGANIZE requires locks for its operation. It just doesn't have as severe locking as an offline REBUILD.

Since REORGANIZE takes X locks, it has the potential to block readers.

Here's a quick demo to prove that it takes locks.

DROP TABLE IF EXISTS dbo.whyreorg

CREATE TABLE dbo.whyreorg
(ID INT IDENTITY PRIMARY KEY, junk INT)

INSERT dbo.whyreorg
SELECT TOP 1000 FLOOR(RAND(CONVERT(VARBINARY,NEWID()))*1000)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b

CREATE INDEX IX_whyreorg_this ON dbo.whyreorg (junk)

BEGIN TRAN
ALTER INDEX IX_whyreorg_this ON dbo.whyreorg REORGANIZE

SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

COMMIT

Sample output (trimmed for space):

REORGANIZE shouldn't take locks on other tables, so I would hypothesize something in the blocked transactions in dependent on the table undergoing reorganization. Without details, it's impossible to confirm this.

Thankfully, the solution here is simple: stop reorganizing.


How do you generate the DDL that created a DB2 LUW database?

The minimum requirement is to have DB2 10.1.4 or DB2 10.5 installed.

  -createdb: Generate the CREATE DATABASE command that was used to create the source database

db2look -d sample -createdb


Super slow MariaDB 5.5 count(*) on large simple table

It's reading the entire 12M rows in the index.  Yes, it is doing an index scan, not a table scan, but that is really the table (only 2 columns), then the index is as big as the data.

Do not use CHAR(...) unless the data is constant length.  I suspect the average length for a "domain" is somewhere around 20.  Changing to VARHAR(120) will shrink the data and the index by a factor of maybe 5.  This shrinkage will probably speed up the full scan by a factor of 5, and help the other queries some.

What was the setting of innodb_buffer_pool_size?  It should not have been more than about 500M.  The data is about 2GB, as is the index.  So you had to read most or all of the index from disk.  That is, the buffer_pool could not have cached it all, so you were I/O-bound.

Switching to 8GB of RAM and increasing innodb_buffer_pool_size to, say, 5500M, will perhaps let it cache the entire index, thereby running the full query in 6 seconds instead of 60.  Add to that, my suggestion about CHAR, and it might go below 2 seconds.


With SQL transactional replication, does changing user permissions to a table trigger a new snapshot?

If you change a user's permissions to a particular table BEFORE a Subscriber has been initialized or reinitialized, if the article property Copy permissions is set to true, the permissions will be copied to the Subscriber when the snapshot is applied.

If you change a user's permissions to a particular table AFTER a Subscriber has been initialized, the permissions will not be replicated on the fly, unless you generate a new snapshot and mark the Subscriber for reinitialization.  Note that the article property Copy permissions must be set to true.

What is typically done is if you need to change a user's permissions to a particular table AFTER a Subscriber has been initialized, and you need the permissions to be present at the Subscriber without reinitializing the Subscriber, the script to grant permissions are posted to the Subscriber using sp_addscriptexec.

I have a post detailing sp_addscriptexec located here at Executing scripts with sp_addscriptexec.


Postgres insert query max length or number of VALUES parameters

The answer to your question is that it depends on your system.  Eventually your client or server will run out of memory trying to process a huge statement. typical unimpressive hardware can handle a million (very skinny) rows without trouble.

The answer to your problem is that most widely used libraries have a way to use COPY...FROM STDIN which can be run from the client.  If you use psql, that mechanism is \copy.  In Perl's DBD::Pg, it is pg_putcopydata.


Why isn't INFORMATION_SCHEMA not displayed in the MySQL Workbench UI by default?

Click on

Edit -> Preferences -> SQL Editor and then check the box "Show Metadata and Internal Schemas"

Click on Refresh icon on the right side of Schemas in the left side bar.

I am using 6.3CE. Older versions may have something similar.


In SQL Server Replication, at what point is it recommended that one separate the distributor?

It totally depends on the workload and the hardware in question.  Having the distributor on another server means that part (possibly all) of the buffer pool will be getting used by the distribution database and not the actual workload that the server should be doing.  Then there's the CPU and memory needed by all the agents that you are running.


How to write this JSON-preparing, non-scalar query as scalar?

In your scenario, because you are using variables, it is sure that your query returns only one row (scalar), so you can ignore this warning.

To suppress the warning, you could add a TOP 1 to your query, but it is not necessary.

SET @json = (SELECT TOP 1
  @varchar1 as MyVarchar1,
  @varchar2 as MyVarchar2,
  GetUtcDate() as MyTime FOR JSON PATH);

You can find more on https://documentation.red-gate.com/codeanalysis/execution-rules/ei003


This is what you could do

for each table in each database in each environment add a column called LAST_MODIFIED_DATE TIMESTAMP(6)
add a before insert or update trigger to set LAST_MODIFIED_DATE=CURRENT_TIMESTAMP for each table
be sure the timezone of each database is correctly set in the NLS Parameters or you will be getting odd answers.  I assume each database is in the same timezone
create a table in a logging schema to record the list of changed values, something along the lines of

CREATE TABLE delta_changes
( P_KEY NUMBER(20)
table_name VARCHAR2 (250),
id NUMBER(10),
change_date TIMESTAMP(6),
logging_date TIMESTAMP(6) DEFAULT CURRENT TIMESTAMP(6));

and create a sequence for the primary key and a pre insert trigger on delta_changes
create a database link between the two databases
create a packaged procedure along these lines
create a job to run the packaged procedure on a schedule of your choice

PROCEDURE  get_changes(table_name_in := NULL)    is

CURSOR all_changes is
SELECT table_name
FROM dba_tables
WHERE owner =  your_schema;
v_execute  VARCHAR2(1000);
BEGIN

IF table_name_in IS NULL THEN

FOR EACH t_name in all_changes LOOP
v_execute := 'INSERT INTO  delta_changes (table_name,id,change_date,logging_date)
VALUES ('||t_name.table_name||',(SELECT ID, LAST_MODIFIED_DATE, CURRENT_TIMESTAMP FROM '||t_name.table_name||', '||t_name.table_name||'@TheOtherDatabase WHERE '||t_name.table_name||'.primary_key = '||t_name.table_name||'.primary_key||'@TheOtherDatabase  and t_name.table_name.LAST_MODIFIED_DATE > t_name.table_name.LAST_MODIFIED_DATE@TheOtherDatabase;';
EXECUTE IMMEDIATE v_execute;
END LOOP;
ELSE
v_execute :='INSERT INTO  delta_changes (table_name,id,change_date,logging_date)
VALUES ('||table_name_in||',(SELECT ID, LAST_MODIFIED_DATE, CURRENT_TIMESTAMP FROM '||table_name_in||', '||table_name_in||'@TheOtherDatabase WHERE '||table_name_in||'.primary_key = '||t_name.table_name||'.primary_key'||'@TheOtherDatabase  and t_name.table_name.LAST_MODIFIED_DATE > t_name.table_name.LAST_MODIFIED_DATE@TheOtherDatabase;';
END IF;
--add exception handling here-log all errors to another table and RAISE error again
END GET_CHANGES;

This solution has a number of ways it could be improved such as:

use bind variables
which users should have execute on this packaged procedure?
or use Oracle advanced queues to log the changes on one database and then extract them to compare on the other
please excuse my syntax as I'm doing this by memory


How does MS SQL Server authenticate MSDTC connections when using SSPI?

There are two Distributed Transaction Standards Supported by the DTC. XA Transactions and OLE Transactions and recovery is implemented differently.

XA Transaction Recovery Process use xa_recover_entry of GetXaSwitch to get a list of xid from the transaction resource manager (SQL Server is a resource manager) that it then uses to rollback or commit the prepared transactions. I assume that running as NETWORKSERVICE is enough to do that based on what is documented in Disabling TIP, LU and XA Transactions

  In Windows XP , Windows Server 2003, and subsequent versions you can
  disable XA transactions to help prevent the security risk that arises
  when a user-specified DLL, used by the DTC to communicate with the XA
  partner's transaction manager, is loaded directly into the DTC
  process. This situation exposes a resource manager's databases to
  serious data corruption and can cause Denial of Service (DOS) attacks.
  The disabling of XA transactions helps to protect the DTC from this
  DLL attack.

The recovery process for OLE is implemented a bit differently. The resource manager has a list of prepared transactions and is responsible for reenlisting them in the DTC. Have a look at the Performing Recovery section of Implementing DTC Interfaces.

Not sure I actually provided a definitive answer for you here but I thought this was too much for a comment.


Create foreign key constraint on indirect data

-- Logger LOG exists.
--
logger {LOG}
    PK {LOG}

-- Sensor number SNS# of logger LOG exists.
--
sensor {LOG, SNS#}
    PK {LOG, SNS#}

FK1 {LOG} REFERENCES logger {LOG}

-- Measurement batch MEA was
-- received from logger LOG at time TIM.
--
measurement {MEA, LOG, TIM}
         PK {MEA}
         AK {LOG, TIM}
         SK {MEA, LOG}

FK1 {LOG} REFERENCES logger {LOG}

-- Value VAL was received from sensor number SNS#
-- of logger LOG in measurement batch MEA.
--
sensor_meas {MEA, LOG, SNS#, VAL}
         PK {MEA, LOG, SNS#}

FK1 {LOG, SNS#} REFERENCES sensor {LOG, SNS#}

FK2 {MEA, LOG}  REFERENCES measurement {MEA, LOG}

Note:
All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key

Using suffix # to save on screen space.
OK for SQL Server and Oracle, for others use _NO.
For example, rename SNS# to SNS_NO.


That's not how it works.

When you click a link in an existing report it basically opens the linked report with the parameters in the URL.

The target report has no clue about the rendering date of the source report.

If you want to accomplish anything like this you would have to pass the rendering datetime of the main report as a parameter to the subreport and have the subreport check that (every single one of them).

As far as I know there isn't a general setting and you would have to modify every drill-through action to send a parameter based on the datetime of rendering and modify each target report to check on it and render an error message if the parameter exceeds a certain threshold.


Add an alias for the derived table before the ; for example VendLargestUnpaidInv:

Select Sum(LargestUnpaid) from
 (Select   Max(InvoiceTotal) AS LargestUnpaid from Invoices
  where InvoiceTotal-(PaymentTotal+CreditTotal)<0 group by vendorID ) VendLargestUnpaidInv;


SQL Server TDE Vs SQL Server Database Backups

Based on the comments so far -

SQL server has no knowledge of how EFS does encryption. Turning off EFS - the timing is reduced almost similar to pre-TDE timing (confirmed by OP).

This means that EFS is slowing down the backups.

Remember that both EFS and TDE will protect data at rest. So, for sql servers, you should have a global policy to exclude sql server filesystem from EFS.


How to Create Indexes Or Manage Performance For Complex Queries Before a Release?

It's always a tough thing to performance test production systems, companies typically have far lesser systems/hardware for QA/Staging/Dev environments. One method I use is to baseline performance of our QA system before implementing SQL changes, and then check performance after. I am not concerned with the actual times, but the delta of before and after. Unless you have exact hardware/systems between environments, you will be somewhat limited.


In SSIS 2012, what is the purpose of flagging a package as an "Entry Point" package

There's one behaviour I can think of:

Only packages with "Entry Point Package" set to 1 can be executed by catalog.create_execution

(see http://msdn.microsoft.com/en-us/library/ff878034.aspx)


How do you filter SQL error logs to display specific error messages?

Here is one I wrote myself years ago.  This is a section of a larger stored procedure, but it works fine on its own.  You can modify the filters to remove unwanted entires as well as highlight others.

print ' '
print ' '
print '==================='
print '== SQL Errorlogs =='
print '==================='
print ' '

declare
  @FirstLog      smallint,
  @LastLog       smallint,
  @BufferRecords smallint,
  @SQL           varchar(2000),
  @Output        varchar(2000)

select
  @FirstLog      = 0, --> Defaults to current logfile
  @LastLog       = 1, --> Defaults to logfile immediately preceding current
  @BufferRecords = 4  --> Used to give a frame of reference to the error message

---------------------------------------------------------------------------------------------------
-- Date Created: July 11, 2005
-- Author:       Bill McEvoy
-- Description:  This procedure combines and parses the SQL Error Logs.  Entries of special
--               interest are marked which allows the operator to quickly scan the output for
--               errors.  Entries of interest are removed from the output.  Entries before and
--               after the errors are included to give a frame of reference.
--
---------------------------------------------------------------------------------------------------
-- Date Revised: December 7, 2006
-- Author:       Bill McEvoy
-- Reason:       I converted this procedure to support SQL Server 2005
---------------------------------------------------------------------------------------------------
set nocount on
declare @count  smallint,
        @alert  char(5)

select  @count  = 0,
        @SQL    = '',
        @output = '',
        @alert  = '---->'

---------------------------------------------------------------------
-- Validate input parameters                                       --
---------------------------------------------------------------------

IF (@FirstLog > @LastLog)
BEGIN
  select @lastLog = @FirstLog + 1
END

---------------------------------------------------------------------
-- M A I N   P R O C E S S I N G                                   --
---------------------------------------------------------------------
--                                                                 --
-- Create work tables                                              --
--                                                                 --
-- Import SQL Error Logs                                           --
--                                                                 --
-- Remove unwanted entries                                         --
--                                                                 --
-- Mark items of interest                                          --
--                                                                 --
-- Generate report                                                 --
--                                                                 --
---------------------------------------------------------------------

---------------------------------------------------------------------
-- Create work tables                                              --
---------------------------------------------------------------------

IF (object_id('tempdb..#ERRORLOG') IS NOT NULL)
  drop table #ERRORLOG

CREATE TABLE #ERRORLOG
(
  LogID       int identity primary key clustered,
  LogDate     datetime NULL,
  ProcessInfo varchar(12) NULL,
  Alert       char(5) default ' ',
  LogEntry    varchar(900) NULL,
  Row         smallint NULL

)

-- Add indexes
create index [IX_ERRORLOG_Alert]    on dbo.[#ERRORLOG](Alert)    with fillfactor = 98 on [PRIMARY]
create index [IX_ERRORLOG_LogEntry] on dbo.[#ERRORLOG](LogEntry) with fillfactor = 98 on [PRIMARY]

---------------------------------------------------------------------
-- Import SQL Error Logs                                           --
---------------------------------------------------------------------

select @count = @FirstLog

  WHILE (@Count <= @LastLog)
  BEGIN
    insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','',' ')
    insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','',' ')
    insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','',' ')
    insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','','------------------------------')
    select @output = '-- Processing: ERRORLOG' + case(@count) when 0 then '    ' else '.' + convert(char(3), @count) end + ' --'
    insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','',@output)
    insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','','------------------------------')
    insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','',' ')
    select @SQL = 'exec master..sp_readerrorlog ' + convert(varchar(3), @count)
    insert into #errorlog (LogDate, ProcessInfo, LogEntry)
    execute (@SQL)
    select @count = @count + 1
  END

---------------------------------------------------------------------
-- Remove unwanted entries                                         --
---------------------------------------------------------------------

delete
  from #ERRORLOG
 where LogEntry like '%Bypassing recovery for database%'
    or LogEntry like '%Setting database option ANSI_WARNINGS%'
    or LogEntry like '%Log backed up with following information:%'
    or LogEntry like '%Login succeeded for user%'
    or LogEntry like '%found 0 errors and repaired 0 errors%'
    or LogEntry like '%Database differential changes%'
    or LogEntry like '%The certificate was successfully loaded%'
    or LogEntry like '%this is an informational message only%'
    or LogEntry like '%logging sql server messages in file%'
    or LogEntry like '%Recovery complete.%'
    or LogEntry like '%Starting up database ''ERROR_LOGGING''%'
    or LogEntry like '%Database backed up: Database: %'
    or LogEntry like '%Log backed up: Database: %'
    or LogEntry like '%allocate%'

---------------------------------------------------------------------
-- Mark items of interest                                          --
---------------------------------------------------------------------

update #ERRORLOG
   set Alert = @alert
 where (LogEntry like '%err%'
    or  LogEntry like '%exception%'
    or  LogEntry like '%violation%'
    or  LogEntry like '%warn%'
    or  LogEntry like '%kill%'
    or  LogEntry like '%dead%'
    or  LogEntry like '%encounter%'
    or  LogEntry like '%cannot%'
    or  LogEntry like '%could%'
    or  LogEntry like '%fail%'
    or  LogEntry like '%full%'
    or  LogEntry like '%not%'
    or  LogEntry like '%terminate%'
    or  LogEntry like '%bypass%'
    or  LogEntry like '%recover%'
    or  LogEntry like '%roll%'
    or  LogEntry like '%upgrade%'
    or  LogEntry like '%victim%'
    or  LogEntry like '%stop%'
    or  LogEntry like '%shut%'
    or  LogEntry like '%timed out%'
    or  LogEntry like '%truncate%'
    or  LogEntry like '%terminat%')
   and  (ProcessInfo <> ' ' or  ProcessInfo IS NULL)

---------------------------------------------------------------------
-- Generate report                                                 --
---------------------------------------------------------------------

BEGIN

  -- Show entries of interest + buffer records
  select A.LogID,
         'Date'       = isnull(convert(varchar(19), A.LogDate, 120), ''),
         A.ProcessInfo,
         A.Alert,
         'Descripton' = left(A.logEntry, 250)
    from #ERRORLOG A
   join (select LogID from #ERRORLOG where Alert = @alert) B  on (A.LogID >= (B.LogID - @BufferRecords))
                                                           and (A.LogID <= (B.LogID + @BufferRecords))

  -- Show informational records
  union
  select A.LogID,
         'Date'       = isnull(convert(varchar(19), A.LogDate, 120), ''),
         A.ProcessInfo,
         A.Alert,
         'Descripton' = left(A.logEntry, 250)
    from #ERRORLOG A
   where ProcessInfo = ''
   order by A.LogID
END


How to instruct Oracle 11g Standby database MRP to bring down as soon as applying the given archive log file?

archive_xyz.log contains the sequence number Smax

This is where you are wrong. You have 3 instances, you have 3 redo threads, so you have 3 archivelogs containing the change at Smax.

Because of this, you need to recover the database until SCN/change, not "archive_xyz.log".

alter database recover managed standby database until change Smax;

Note that, this does not apply the change at Smax.

managed_standby_recovery::=

  UNTIL CHANGE Clause

  Use this clause to instruct Redo Apply to recover redo data up to, but
  not including, the specified system change number.


How to query data between two specific months for all years?

You can use EXTRACT (MONTH FROM DATE) function.

CREATE TABLE TABLE1(ID INT, CREATED_DATE DATE);
INSERT INTO TABLE1 VALUES (1, TO_DATE('20180101', 'YYYYMMDD'));
INSERT INTO TABLE1 VALUES (1, TO_DATE('20180201', 'YYYYMMDD'));
INSERT INTO TABLE1 VALUES (1, TO_DATE('20180301', 'YYYYMMDD'));
INSERT INTO TABLE1 VALUES (1, TO_DATE('20180401', 'YYYYMMDD'));
INSERT INTO TABLE1 VALUES (1, TO_DATE('20170401', 'YYYYMMDD'));
INSERT INTO TABLE1 VALUES (1, TO_DATE('20180501', 'YYYYMMDD'));
INSERT INTO TABLE1 VALUES (1, TO_DATE('20180601', 'YYYYMMDD'));

SELECT *
FROM   TABLE1
WHERE  EXTRACT (MONTH FROM CREATED_DATE) >= 4
AND    EXTRACT (MONTH FROM CREATED_DATE) <= 5;

ID | CREATED_DATE
-: | :-----------
 1 | 01-APR-18
 1 | 01-APR-17
 1 | 01-MAY-18

dbfiddle here


Conditional Join - Specific Column Select

Regarding your comment about the scan, that happens because using ISNULL() in the join manipulates the field, so it's no longer SARGable. It may be faster to split this up, rather than try to do all the work in a single query. For example:

DECLARE @Recipient_ID INT, @Submitter_ID INT

SELECT
@Recipient_ID = Order.Recipient_Id,
@Submitter_ID = Order.Submitter_Id
FROM Order
-- where clauses

IF @Recipient_ID IS NULL

SELECT Emp.Full_Name
FROM Employee Emp
WHERE Emp.Emp_Id = @Submitter_ID

ELSE

SELECT Emp.Full_Name
FROM Employee Emp
WHERE Emp.Emp_Id = @Recipient_ID

EDIT based on comment:
Since there can be many orders, what about this? It is SARGable so should index well, and you're not querying the table multiple times.

with cte as (
SELECT
COALESCE(Order.Recipient_Id, Order.Submitter_Id) AS EmployeeID
FROM Order
-- where clauses
)

SELECT Emp.Full_Name
    FROM Employee Emp
INNER JOIN cte on cte.EmployeID = Emp.Emp_Id


MySQL/MariaDB stored procedure compiles with typos

The explanation here is related to the same situation found in the other question... the expression here is at least potentially ambiguous, so it can't reasonably be validated at define time.  It could refer to something that's not yet defined.

MySQL validates the syntax of stored procedures at define-time, but it doesn't validate anything that's potentially ambiguous or things that could become valid by the time the program is actually running.

In a stored procedure:

-- unambiguous

SET foo      -- definition rejected if 'foo' is not a valid local or system variable
    = bar;   -- definition rejected if 'bar' is not a valid local variable

-- potentially ambiguous, probably intended to be a column name if 'foo' isn't a variable
-- could be a reference to a not-yet-created or altered base or temp table

UPDATE buzzfizz SET foo = bar; -- checked for syntax only, server assumes you know best

The tables referenced in a stored procedure don't even have to exist when the procedure is declared, or could have a different structure when the procedure is run... after all, you can create tables (base tables and temporary tables) within stored procedures.  If a procedure could only be declared against already-existing objects, this would be a significant lack of flexibility.  You can also "mask" a base table by creating a temporary table with the same name, which would have messy implications here.

Stored functions and events abide by the same general rules, although things are a little different there since these programs do have differences from procedures... for example, you can't create a base table within a stored function, because stored functions aren't allowed to implicitly or explicitly cause a COMMIT.

Triggers are somewhat different, because they are parsed and cached as soon as they are declared, so if their definition doesn't match the table they're defined against (i.e., referencing a nonexistent column in the table via the NEW or OLD alias), the trigger is rejected... but references to other tables are not validated, since the other table might not yet exist and things that could be "column names" could indeed refer to columns or could refer to be other objects.

Views are somewhat different.  They're validated at define time, and the referenced objects have to exist.  That's why mysqldump generates SQL statements to create a dummy table with the same name and approximately the same column definitions as each view, only to later cause those tables to be dropped and replaced with the view itself.  If not for this, views couldn't be restored from a backup if they referenced other views that did not yet exist.  You can change the underlying objects out from under a view, which will cause an error, later when you try to access the view... but you can repair a view that you've broken in this way by putting the table back into its original condition.  Views, of course, have no local variables and can't create or alter tables or manipulate transactions.


Attempting to repair the database isn't going to help.  Page 1:9 is a system object and system objects can't be repaired.  You'll want to script out the tables and create a new database.  Then create the tables in the new database, and copy over the data into the new database.

If the database won't open you can try setting it to emergency mode using ALTER DATABASE MyDB SET EMERGENCY which may bring the database online.  If you does you can script the data out. If not you're basically screwed.

If you had a backup...
Another option would be to restore the database to a point in time before the page became corrupt.  If you have a backup from that far back you can restore that backup, then roll the transactions logs forward and you should be able to get back to a state where you have all the current data without the corruption.


try this,

declare @t table( id int, parent_id int,child_id int)
insert into @t values
(1 , 9 ,  8 ),(2 , 8 , 17 )
,(3 , 8 , 33 ),(4 , 8 , 18 )
,(5 , 9 , 22 ),(6 ,22 , 11 )
,(7 ,22 ,  4 ),(8 , 3 ,  5 )

declare @parentinput int=9

select t.* from @t t
left join @t t1
on t.child_id=t1.parent_id
where t.parent_id=@parentinput

union
select t1.* from @t t
left join @t t1
on t.child_id=t1.parent_id
where t.parent_id=@parentinput


How to list all users that have a specific Temporary Tablespace like as default

You can use a select on dba_users

Example

select a.USERNAME
from dba_users a
Where
  TEMPORARY_TABLESPACE = '<NAME TEMP TABLESPACE>';


How to undo every change made to a database in a controlled environment?

If this is just for development/testing, you can get a license for Developer Edition (free for 2014+ and ~$50 for 2012 and lower).  and use snapshots, since Developer Edition supports all of the features supported in Enterprise Edition. The only difference is licensing - you cannot use Developer Edition in production.

You're not going to be able to cook something up that's much simpler or faster than reverting from a database snapshot. But do some research - you want to be aware of things like this.


Should I used varchar(max) or varchar(4000) SPARSE?

There is no advantage for the non-NULL cases when using SPARSE, and in fact, there are two stated disadvantages:

an extra 4 bytes per each non-NULL value
slightly longer access time

As you pretty much already gathered, the SPARSE option only makes sense for fixed-length datatypes; I can't think of a single reason to use it on variable-length types.

I am not sure that the extra 4 bytes implies anything about it being stored off-row, and the MAX types aren't entirely off-row when they exceed 8000 bytes as there is then the 16-byte pointer in the row to that off-row location.

Stick with VARCHAR(4000), no SPARSE, and I would even consider making it NOT NULL DEFAULT('') (an empty string is still 0 bytes, but now you don't need to mess with the NULL indicator, and can a comment really be "unknown" as opposed to "no comment"?).


MYSQL count based on conditions in same column

As far as you don't show us your desired result let me show some approaches to your question.

Your first example:

SELECT COUNT(student_id) count
FROM   tbl_result
WHERE  subject_id IN ('7112',  '6118');

give a result of 5, it doesn't matter if you use COUNT(*) or COUNT(student_id).

| count |
|:-----:|
|   5   |

If you want to know which students then you can use GROUP BY student_id, but then you should add student_id to the SELECT statement:

SELECT subject_id, COUNT(id) count
FROM   tbl_result
WHERE  subject_id IN ('7112',  '6118')
GROUP BY subject_id;

Result:

| subject_id | count |
|:----------:|:-----:|
|    6118    |   3   |
|    7112    |   2   |

For the second one use the same syntax adding more conditions:

SELECT subject_id, COUNT(id) count
FROM   tbl_result
WHERE  subject_id IN ('7112',  '6118')
AND    score > 49
GROUP BY subject_id;

Result:

| subject_id | count |
|:----------:|:-----:|
|    6118    |   3   |
|    7112    |   1   |

SELECT COUNT(*) count
FROM   tbl_result
WHERE  subject_id IN ('7112',  '6118')
AND    score > 49;

| count |
|:-----:|
|   4   |

Rextester here


Why is parallelism not good in this query?

It doesn't, generally. It's there because XML queries are misery made by the devil hisself, and Jonathan is attempting to not cause observer overhead on your server.

As an alternative, sp_BlitzCache will warn you about expensive key lookups only.


Choice of connection pooling library for VM deploys

Any pool in an environment in which time can flow backwards or leap forwards, which it can in a virtual machine, is going to be subject to spurious failure when it does so.

Moreover, it is not just connection pools that are affected.

Any...

Timed waits
Timed polls from a concurrent collection
Object.wait() with a timeout
Thread.sleep() call
...
Anything in Java that requires the measurment of time is affected.

If you cannot nail down your clock drift, you will encounter unexplained application failures, occurring in your code and third-party libraries.

HikariCP (i.e.me) got sick of spending hours trouble-shooting a user's issue, reading logs and thread dumps, only to discover their clock had jumped backwards or forwards more than a minute.

User: "Why did I get a bunch of connection timeouts?!"

(several days of back-and-forth questions and examining logs later...)

HikariCP: "Well, you asked the pool to try for 30 seconds to give you a connection ... then your clock jumped forward 42 seconds and Java (and the OS) unparked all the threads as if time had really passed."

It is really simple to fix, just configure NTP inside your VM.


What will happen when a forum uses 24 char length as an URL ID?

But notice: it's not 24 bytes (256-bit characters), it appears to be a number only. And a 24-digit decimal number can be stored in just 10 bytes (12 digits, 999999999999, uses 5 bytes, E8D4A50FFF, so squaring that will use 10). A bigint is 8 bytes, and it can go up to 9.2 * 10^18. For all you know, they actually do use bigints and ignore the first 5 digits of the number. Even an int can store 500 million, but just 9 times that size will exceed the positive range. Perhaps they simply thought big when they started!

Even if they do use 10 byte keys, sure, it's a little unpleasant, but it's really not that big a deal. It all depends on the structure of the tables being used and their indexes, plus access patterns to those tables.

Anyone who has a database with five hundred million posts has some serious hardware and some developers/DBAs who are paid to keep it in good order.

Perhaps the post ID is sparse and can be converted by a lookup into something much shorter, and the database is built around the shorter key. By sparse, I mean that each next post ID is not sequential with the one before it, similar to credit card numbers, where some internal relationship between the digits proves it's valid. It's all pure speculation until we hear facts from someone who knows the architecture.


Is it possible to define a foreign key with duplication and cascading delete only if no values exist?

Is this possible to do with FOREIGN KEY constraints, or should I look for another way?

Look another way. I'm not 100% I understand what's happening from that description but ON DELETE CASCADE quite explicitly means "allow the deletion to cascade". There are no buts about it.

Or, just make sure in the app, you check -- in a transaction -- that there are no remaining survey_type's then you can delete and allow it to cascade naturally.


Does column size increase break SELECT *?

"Maximum row size for mysql" is the terminology you are probably looking to search for - that finds a useful stack overflow page at https://stackoverflow.com/questions/10349361/mysql-how-to-workaround-the-row-size-limit-of-66-kbytes

That suggests a maximum row size (excluding blob types which are stored off-page) of 64Kb, and has a couple of useful looking links with more detail.


Functional Dependencies and Normal Forms

1NF satisfied, as Y and Z depend on the key WX;
2NF satisfied as Y and Z depend on the whole key WX, and not on any subset of the key;
3NF not satisfied as Z depends on Y, which is not part of the key.

To put this into 3NF one would restructure as (W X Y) and (Y Z).


The AND (a,b) IN (SELECT x,y FROM ... WHERE) is standard SQL but not yet implemented in SQL Server. You can replace it with a correlated EXISTS subquery:

AND EXISTS (SELECT * FROM ... WHERE ... AND x=a AND y=b)

Your EXISTS subquery is not correct because it is not correlated.

In your specific query, it should be written:

AND EXISTS
    (SELECT *
    FROM FactPerson FPi
    WHERE FPi.PersonType = 'A'
    AND FPi.Age BETWEEN 18 AND 21
    AND FPi.Age = FP.Age AND FPi.PersonType = FP.PersonType)

But all the subquery is really unnecessary. The simple conditions would be equivalent:

AND FP.PersonType = 'A'
AND FP.Age BETWEEN 18 AND 21


General rules for deployment would have kinks worked out when deploying to a TEST, QA or DEV system so there are no issues with the PROD deployment.  If you are already running a cluster you can add an instance to the cluster at no licensing cost and use that as your TEST environment to iron out the deployment process.  If the deployment to TEST goes south then you simply restore the database from the PROD backup, change the scripts and try again.


Reset MySQL table data after each session is completed in php

Setup:

Take a mysqldump of the 'default' data.
Put the output into a Stored Procedure.
Modify the stored procedure to also CREATE DATABASE and USE that database.  The database name would be dynamic -- probably the user id, with something prepended to avoid naming conflicts.  Also surround with backtics.

User comes in:

perform that stored procedure.

User leaves.  This could be tricky -- what if he stays logged in?  what if some server dies without getting to this code?  Etc.  There may need to be something that runs around periodically to clean up orphaned databases.

Drop his database.

Note:  Your question implied that everyone is using the same database.  I switched to having everyone use a different database.


How this simple Stored Procedure ended up in Deadlock

Instead of checking to see if the data already exists and then doing an INSERT, try doing an INSERT IGNORE and seeing if the LAST_INSERT_ID() came back with the same value or not on two calls.

CREATE PROCEDURE sp_CreateCtrMsgTagIfNotExist
(
    siteId INT, logTimestamp DATETIME, IntervalId INT,
    digestMatched TINYINT(1), OUT returnpk INT
)
BEGIN

   DECLARE rpk INT;

   SELECT LAST_INSERT_ID() INTO rpk;
   START TRANSACTION;
   INSERT IGNORE INTO Site_Msg_Tag
       (Site_Msg_Tag_Id,Unit_Id,Log_Timestamp,Interval_Id,Digest_Matched)
       VALUES (NULL,siteId,logTimestamp,IntervalId,digestMatched);
   SELECT LAST_INSERT_ID() INTO returnpk;
   IF returnpk = rpk THEN /*1*/
       SELECT Site_Msg_Tag_Id INTO rpk FROM Site_Msg_Tag
       WHERE Unit_Id = siteId AND Log_Timestamp = logTimestamp
       AND Interval_Id = IntervalId ;
       SET returnpk = 0 - rpk;
   END IF;/*1*/
   COMMIT;

END;

Checking LAST_INSERT_ID()

If two calls produced different values, you should make the stored procedure set returnpk as is to indicate that the row was newly inserted.
If two calls produced the same value, then go query for the Site_Msg_Tag_Id. You should make the stored procedure set returnpk as negative to let you know the Site_Msg_Tag_Id was already there.

Give it a Try !!!


Datetime Clustered Index Keeps getting fragmented

Fragmentation can still occur on a table with an ever increasing key even if that key is never itself subject to updates.

If the file group is shared with other objects allocations can be interleaved causing fragmentation.
Updates that increase the size of rows can cause page splits.
Deletes can leave pages nearly empty and cause internal fragmentation.

However you say deletes and updates are rare or non existent. So to reach 70% fragmentation within a week I would assume that the cause must be a shrink database task. See Why you should not shrink your data files