Full text search on multiple joined tables I'm trying to set up a full text search in PostgreSQL but coming across the problem that the more tables I join to the query the fewer the results I get back.

The tables I am trying to join are:

article
keyword
author
organisation

There is a many-to-many relationship between article and all the other tables.

I am basing my query on that found here and at a loss as to why it's failing. The query so far looks like this:

SELECT title
FROM (
  SELECT article_title AS title,
  to_tsvector(article.article_title) ||
  to_tsvector(string_agg(keyword.keyword, '')) ||
  to_tsvector(string_agg(organisation.org_name, '')) ||
  to_tsvector(string_agg(author.name, ''))
  AS document
  FROM article
  JOIN article_keywords on article.id = article_keywords.article_id
  JOIN keyword on keyword.id = article_keywords.keyword_id
  JOIN article_authors on article.id = article_authors.article_id
  JOIN author on author.id = article_authors.author_id
  JOIN article_organisations on article.id = article_organisations.article_id
  JOIN organisation on organisation.id = article_organisations.organisation_id
  GROUP BY article.article_title) AS p_search
WHERE p_search.document @@ to_tsquery('David & Feeney');

In the example above, I want to search across all tables for mentions of 'David Feeney'. There are two entries in the author table for him but so far none are returned.

The blog post you refer to is wrong in using [INNER] JOIN between post and posts_tags. Nothing enforces that at least one tag exists, so posts without tags are excluded from the result.
(Also, why is there no PK for posts_tags? A dubious DB design.)

You copied that error and made it much worse by combining multiple relationships that way. Each many-to-many relationship allows for the possibility of no (zero) related rows. If you join multiple tables that way, only such rows from article remain that have relatives in each and every of the joined tables.

On the other hand, you multiply rows with multiple relatives in multiple tables, resulting in a potentially huge intermediary table with lots and lots of redundant copies in the aggregated result. Detailed explanation:

Two SQL LEFT JOINS produce incorrect result

This query should work with Postgres 9.1 or later:

SELECT a.article_title AS title
FROM   article a
LEFT   JOIN (
   SELECT x.article_id AS id, string_agg(y.keyword, ' ') AS txt
   FROM   article_keywords x
   JOIN   keyword          y ON y.id = x.keyword_id
   GROUP  BY 1
   ) k USING (id)
LEFT   JOIN (
   SELECT x.article_id AS id, string_agg(y.name, ' ') AS txt
   FROM   article_authors x
   JOIN   author          y ON y.id = x.author_id
   GROUP  BY 1
   ) a USING (id)
LEFT   JOIN (
   SELECT x.article_id AS id, string_agg(y.org_name, ' ') AS txt
   FROM   article_organisations x
   JOIN   organisation          y ON y.id = x.organisation_id
   GROUP  BY 1
   ) o USING (id)
WHERE  to_tsvector(concat_ws(' ', a.article_title, k.txt, a.txt, o.txt))
    @@ to_tsquery('David & Feeney');

Using LEFT [OUTER] JOIN we don't exclude rows without relatives in the joined tables.

By aggregating multiple rows before joining we avoid the cross-multiplication of rows.

I concatenate the string with concat_ws() (which ignores potential NULL values from any of the contributing sources) and call to_tsvector() only once, which should be a bit cheaper.

Besides correctly aggregating input from all related tables, this should also be among the fastest possible solutions. Aggregate first, join later.

That is - as long as you want to combine all words to search for David & Feeney. For instance, this way you get a match for an article associated with any organization that has the word 'Feeney' in its name and any author with 'David' in his / her name. That's what your query suggests. It's typically not what you'd want, though.

I would not concatenate everything for this purpose, but rather search in each table separately for the combination of both words ...

Also adding separators between word to avoid false positives from matching illegally combined words, like @Daniel hinted in his comment.

And I remove the subquery that's not needed.


Indexes all Exist in non Primary Filegroup - Advantages? I have come across a database where all the indexes are stored on a filegroup that is not the primary filegroup.
The database has 4 files, one of which is in the primary filegroup and the other 3 are in another filegroup, let's call it FileGroupB.
All the indexes are in FileGroupB according to this query:
SELECT  i.name,
        i.type_desc,
        o.name,
        f.name,
        o.type_desc
FROM    [sys].[indexes] i
        JOIN [sys].[filegroups] f
            ON f.[data_space_id] = i.[data_space_id]
        JOIN sys.objects o
            ON o.object_id = i.object_id
WHERE   o.type_desc = 'USER_TABLE'

If I am correct, that would mean all code definitions (EG stored procs, functions etc) are stored in the primary file but all the data is in the secondary filegroup.
What is the benefit of / thought behind this approach?

Not sure if I can list any benefits but that was fashionable about 15 to 20 years ago.
Well actually the db is accessible once the Primary Filegroup is online (if you have enterprise edition) - so you might be back online faster (albeit without any access to your data).


Select a table's current identity from another database How can I get the current identity of a table using OPENQUERY in SQL Server?
How to select the current identity on the table from another database?

If the Database is on the same Instance, then you don't need to use OPENQUERY. But even if the Database is on another Instance, then you still shouldn't need OPENQUERY (as @AndriyM reminded me of in a comment on this Answer): since the query uses JOINs to system Views instead of using the built-in meta-data functions (e.g. OBJECT_ID), fully qualifying the object names with the 4-part name (beginning with the Linked Server name) should work just fine. And in fact, using the 4-part names is probably better anyway as it would allow you to parameterize the query, whereas OPENQUERY requires a string literal for the Query (which then requires Dynamic SQL to generate the query that calls OPENQUERY).

Hence, in either case, the following query should work:

SELECT ISNULL(sic.last_value, 0) AS [LastValueReserved],
       ISNULL(sic.last_value, 0) + sic.increment_value AS [NextValue]
FROM   [ [ {linked_server_name}. ] {database_name}. ][sys].[identity_columns] sic
INNER JOIN [ [ {linked_server_name}. ] {database_name}. ][sys].[objects] so
        ON so.[object_id] = sic.[object_id]
INNER JOIN [ [ {linked_server_name}. ] {database_name}. ][sys].[schemas] ss
        ON ss.[schema_id] = so.[schema_id]
WHERE  ss.[name] = N'{schema_name}'
AND    so.[name] = N'{table_name}';

Of course, if you are getting this info for the current database, then you don't need to fully qualify those system view names with {database_name}..

Please note:

I labeled the first field with the qualifier of "Reserved" because there are reasons that Identity values can be reserved but never used (i.e. not found in the table). Examples being: if an INSERT statement fails, or if the INSERT is done within a Transaction that gets rolled-back, or if the server restarts (in which case you sometimes see a jump of approximately 1000; this behavior has been asked and documented on here and other sites).
ISNULL(sic.last_value, 0) is used because prior to the first INSERT operation, the value in the last_value column is NULL.


Find all `NaN` values in a postgresql database Numerics and floats can store NaN https://www.postgresql.org/docs/current/static/datatype-numeric.html

I have a working program with a PostgreSQL database that does not have any constraint about NaN.

I want to know which fields of which tables contains NaN (looking at some schemas, not at public schema).

Is there an automatic way to do that?

Later I add a constraint to that fields of that tables.

You may loop through the Information_schema.columns and check tables dynamically.

DO $$
DECLARE
rec RECORD;
v_found BOOLEAN ;
BEGIN

for rec IN (   SELECT column_name,table_name,table_schema
               FROM  information_schema.columns
             WHERE  data_type IN ( 'numeric', 'real', 'double precision' )  )
LOOP
   v_found := FALSE;
   EXECUTE format ( 'select TRUE  FROM %I.%I WHERE %I = %L LIMIT 1' ,rec.table_schema,rec.table_name,rec.column_name,'NaN') INTO v_found;

   IF v_found = TRUE
   THEN
        RAISE NOTICE 'Found Column %  in Table %.%' , rec.column_name,rec.table_schema,rec.table_name;
   END IF;

END LOOP;

END$$;

Testing

knayak=# create table x as select CAST( 'Nan' as NUMERIC) as n ;
SELECT 1

Result

NOTICE:  Found Column n  in Table x
DO


How do I get a linked list to display more then just the first search item in Access? I just feel like I'm missing something really stupid. I have a simple access database I'm using for tracking my projects. I have multiple individuals and I have multiple projects. For the project, I have a drop down and I can select the lead for the project from my list of employees. No problem. however, it only displays the first name of the person. How do I get it to display the first and last name?

Table 1: ID, firstName, lastName
Table 2: ID, lead, notes - "lead" is a long integer.

The Row Source is

SELECT [Table1].[ID], [Table1].[lastName], [Table1].[firstName], [Table1].[Position]
FROM Applicants
ORDER BY [lastName], [firstName];

On the Form, I get the drop down box. It shows the first, last name, and what position they have but once I select the person I want, it only shows the first name.

The database is far more complex then this but none of the other information is relevant.

One option would be to create a display column fullName that concats your first and last name values into a single column. I believe the following should work in your Row Source:

SELECT [Table1].[ID], [Table1].[lastName] & ", " & [Table1].[firstName] as fullName, [Table1].[Position]
FROM Applicants
ORDER BY [Table1].[lastName] & ", " & [Table1].[firstName];

A couple notes:

Even though we alias [Table1].[lastName] & ", " & [Table1].[firstName] as fullName, we can't order by the alias, so we repeat the expression in the ORDER BY.

Using the above query, your column count will be 3 instead of 4, so you may need to change the column count property of the ComboBox.

I'm not very Access savvy, so I'd suggest checking other properties to make sure I haven't missed anything else.


Sql Server Dynamic partition with page compressed index I created a table with partitions by following this 2012 partitioned index document.
I created partitions monthly based on Date_Id column;
CREATE PARTITION FUNCTION Fnc_Prt_Fact_Sales (INT)
AS RANGE RIGHT FOR VALUES ('20200101', '20200201', '20200301','20200401')

CREATE PARTITION SCHEME Prt_Scheme_Fact_Sales
AS PARTITION Fnc_Prt_Fact_Sales
ALL TO ([PRIMARY])

CREATE TABLE [dbo].[Fact_Sales](
   [Slip_No] [nvarchar](155) NULL,
   [Date_Id] [int] NOT NULL,
   [City_Id] [int] NOT NULL,
   [Store_Id] [int] NOT NULL,
   [Sales] FLOAT
) ON Prt_Scheme_Fact_Sales(Date_Id)
GO

 CREATE  CLUSTERED INDEX [Ix_Fact_Sales] ON [dbo].[Fact_Sales]
(
    [Date_Id] ASC,
    [City_Id] ASC,
    [Store_Id] ASC
) WITH (
   DATA_COMPRESSION = PAGE ON PARTITIONS (1,2,3,4)
)  ON Prt_Scheme_Fact_Sales(Date_Id)
GO

i want to add partitions by dynamically (monthly). If i do that, how do i make data_compression include new added partition.
On Blog, partitions are written by manually.
--> DATA_COMPRESSION = PAGE ON PARTITIONS (1,2,3,4)
if it matters, i ue sqlserver 2017 standart edition.

The answer to your question, as asked, is to rebuild the new partition after the partition split.  The split should be to an empty partition (so long as you split the rightmost partition befor you have any data to the right of the new boundary value), so this is not expensive.
eg
declare @newBoundary int = 20200501;
declare @newPartitionId int =  $partition.Fnc_Prt_Fact_Sales(@newBoundary);

alter partition function Fnc_Prt_Fact_Sales() split range (@newBoundary);

alter index ix_fact_sales on Fact_Sales
rebuild partition = @newPartitionId
with (   data_compression = page on partitions (@newPartitionId) )

But the easier, and more common approach is to set the index's default compression to PAGE, instead of specifying that only enumerated partitions are PAGE compressed.
 CREATE  CLUSTERED INDEX [Ix_Fact_Sales] ON [dbo].[Fact_Sales]
(
    [Date_Id] ASC,
    [City_Id] ASC,
    [Store_Id] ASC
) WITH (
   DATA_COMPRESSION = PAGE --ON PARTITIONS (1,2,3,4)
)  ON Prt_Scheme_Fact_Sales(Date_Id)
GO


What's the difference between Object.bsonsize and avgObjSize returned by db.collection.stats? In my collection, my documents seem to be relatively similar. I need to know how much storage I'll need in my production server to store my documents, that's why I queried :

db.collectionName.stats()

My field avgObjSize was around 6442 bytes.
I chose a random document in my collection, I typed :

Object.bsonsize(db.collectionName.find({"_id" :         ObjectId("5508497c51a990da07b07106")}))

And I obtained 810 bytes.

Why do I have this huge difference ?

I also noticed that more I store documents, more avgObjSize decreases... Is that normal ? (I checked, my documents are not empty.)

It's a development server, so I'm on Red Hat Enterprise Linux 5.8 and it's a 32bit version.

My field avgObjSize was around 6442 bytes. I chose a random document in my collection, I typed :

  Object.bsonsize(db.collectionName.find({"_id" :         ObjectId("5508497c51a990da07b07106")}))

  And I obtained 810 bytes.

  Why do I have this huge difference ?

The db.collection.find() method returns a cursor, so what you have calculated is the size of the cursor object rather than the size of a document.

You should use db.collection.findOne() to return a single document if you want to measure the BSON size:

Object.bsonsize(db.collectionName.findOne(
    {"_id" : ObjectId("5508497c51a990da07b07106") }
))

  I also noticed that more I store documents, more avgObjSize decreases... Is that normal ? (I checked, my documents are not empty.)

The average is based on the size of the documents in the collection, so it sounds like your trend over time is to insert smaller documents. The normal behaviour depends entirely on your use case ;-)


How do I determine when scaling is necessary or helpful? I have a MySQL Server that I use for the back-end storage of an online game. The game writes to the database to persist character information such as world location, health status, ability stats, etc .. This data is written every few seconds to the database for each character and vehicle. I have been noticing that as more and more users join the game that queries I write via the database seem slower. Also, as I make the online world more complex, via content added via the database, the game tends to slow.

I am looking for the bottleneck and I am wondering how I go about determining if scaling my database (vertically or horizontally) will make a difference. Knowing the database is MySQL how do I go about seeing if my single database server is the bottleneck and if scaling it would be helpful? Thank you.

If things are slowing down as you add more data then it's most likely a lack of indexes. If you have a table being queried without indexes then the server will need to do a full table scan (eg. read the entire table).
To understand why it's so much slower read up about Big O Notation. Full table scans are O(n) and will be very slow for large data sets. Index scans would be O(log(n)). For comparison with n=1M rows the linear operation would need to read 1M rows vs about 20 for the O(log(n)) one[1].
If you know all the SQL that is being executed in your app you can jump to the EXPLAIN part of this answer.
If you don't know what SQL is causing the slowness then you need statistics to figure it out. There's no info in your question about the tech stack you're running on but if you're using an ORM framework then you may be able to enable query stats there.
You can also look at enabling the MySQL Slow Query Log. Once enabled your MySQL server will keep track of SQL commands that execute for longer than a predefined amount of time (the default is 0 seconds, eg. everything).
Once you've identified the SQL that is slow you should run an EXPLAIN on it to see the query plan. Based on that you can add additional indexes to the relevant tables that are being accessed via full table scans.
If after adding indexes things are still slow due to excessive load then you can consider scaling to a larger server or a clustered setup. Until you've optimized your SQL though it's pointless as the same slow operations would be equally slow on a clustered setup (you just may be able to support more of them concurrently).

[1]: These numbers aren't entirely correct. The real stat to look at is the number of disk I/Os involved. For a full table scan the number of disk I/Os is is the number of rows divided by the number that can fit in a disk block (usually 4KB) so it would be less than 1M I/Os though still O(n) as it would grow linearly with the number of rows. Also, most database servers use B-Trees with a high fan out (~100 vs 2 for binary trees) for indexes so the number of disk I/O's would be significantly less (4-5 levels of the tree is common). Again it's still O(log(n)) though the log is base 100 instead of 2.


How to tell how long autovacuum took to vacuum a postgres table? The system table pg_stat_all_tables records when a table was last vacuumed by the autovacuum daemon.
I've noticed a possible correlation between some performance issues and one of the larger tables in my database being vacuumed. However, since I don't know the duration of the vacuuming process, I can't be 100% sure.
How can find I out how long an autovacuum took on any given table?

Autovacuum durations are reported in the logs when log_autovacuum_min_duration is set to 0 or a duration threshold.
It produces this kind of output:

2021-05-17 03:59:40.530 CEST [25526] LOG:  automatic vacuum of table "db.public.tablename": index scans: 1
    pages: 0 removed, 1001527 remain, 0 skipped due to pins, 0 skipped frozen
    tuples: 1346603 removed, 15288094 remain, 224598 are dead but not yet removable, oldest xmin: 736
    buffer usage: 1104110 hits, 2210831 misses, 1716564 dirtied
    avg read rate: 2.329 MB/s, avg write rate: 1.808 MB/s
    system usage: CPU: user: 46.90 s, system: 60.02 s, elapsed: 7417.28 s

As can be seen in this example, it can be very long on large tables, but it does not consume so much resources due to throttling.
When the operation is still in progress, it can be monitored through
the system views pg_stat_progress_vacuum  and  pg_stat_activity (to be joined with the pid column).


Source code of View of Linked server sp_HelpText How to use sp_helptext for view on the linked server ?

There is an answer on SO: SQL using sp_HelpText to view a stored procedure on a linked server

EXEC  [ServerName].[DatabaseName].dbo.sp_HelpText 'storedProcName'


How to store multiple data types for same column (for many columns) To preface, similar questions have been asked, but I have not found any that address a table that has a large number of columns affected by this issue.  All the similar seem to address a table that has a small number of columns affected by this issue...

Proper way to store a value that could be multiple different types
Best way to store a value which could be multiple types
Best way to store different types of data in a single column, which should be query friendly - MySql
https://stackoverflow.com/questions/10455783/different-types-for-the-same-column
https://www.sqlservercentral.com/forums/topic/multiple-data-types-for-the-same-column

...but I have a MySQL database table that has many (e.g., 30) columns currently setup as the data type decimal(5,2).
Now, a new scenario has arisen where I need to enter a decimal(5,2) OR a char(2) (i.e., one or the other, but never both) for each of those 30 columns.  To give you context, I am entering the quantity of elements detected in a sample and, if the quantity of an element is not detected, then I must be able to identify that an element was tested, but not detected, such as "ND".  I also must know if the element was not yet tested.  For example, if the column is DEFAULT NULL, then it may indicate that I have not yet tested the respective element.  So, there are three possible situations related to this scenario:

A decimal(5,2) value indicates that the element was tested and detected.
A char(2) value indicates that the element was tested and not detected.
A NULL(...?) value indicates that the element was not tested.

So, my initial thought was to change the change columns' type to a varchar, in order for the columns to be able to store either a char(2) or decimal(5,2) value, but that seems be a dangerous path called Entity-Attribute_Value, which may require me to build the application layer more to accommodate the limitations caused by it.
So, I read chapter 6 of the book "SQL Antipatterns: Avoiding the Pitfalls of Database Programming" (http://rads.stackoverflow.com/amzn/click/1934356557) and several other related posts, and the two most common answers seem to be:
1. Use a design that is based on the type of query you want to perform.
In my case, I want to perform multiple types of queries that compare column values of different rows, determine averages, and possibly unforeseen more.
2. Use the Class Table Inheritance design.
I would like to implement the Class Table Inheritance design, but that would require many more tables to be created for each column.  It seems like an overly complex solution, given the many columns that need only one of two types of data.
For example, if my understanding about Class Table Inheritance is correct, then for each of the 30 columns in my base table, I must create a single table for each subtype and make their primary key also serve as a foreign key to the base table, which means that I must create 60 tables.  It seems excessive to create 60 tables in order to deal with 30 columns that might have one of two data types.  Also, it seems that I would need to figure a way to manage those 30 pairs of tables so they refer to the same column, possibly in the application layer.
One possible solution that comes to mind before I end this post is: Use the Class Table Inheritance design, but instead of creating 30 pairs of tables for my two data types, create just 2 tables for the 2 data types and add a column that stores a value that relates each pair, such as the name of the base table column that they would relate to.  But this does not seem like good practice either.
Any suggestions?

I'm the author of the book you referenced. Thanks for reading my book!
I suggest you read chapter 14, "Fear of the Unknown."
For your case:

I am entering the quantity of elements detected in a sample and, if the quantity of an element is not detected, then I must be able to identify that an element was tested, but not detected, such as "ND".

I would not store the string value "ND". I would store NULL instead of a number when the value is unknown or inapplicable.
Then when you query it, you can use:
SELECT COALESCE(myNumericColumn1, 'ND') AS myNumericColumn1
FROM Mytable
...;

The COALESCE() function in SQL returns its first argument that is not NULL. In this example, if the numeric is NULL on a given row, the result will use the default value in the second argument, 'ND'.
You can also create a VIEW with this expression for each column, so you don't have to repeat it in every query.

Re your comment:
This is starting to sound like you need to store the results in rows, not _columns. One numeric result per row. Just two three columns:

Sample ID
Element ID
Numeric value

Then you can make a distinction between three states:

Presence of a row with a non-NULL numeric value.
Presence of a row, but NULL for the numeric value, indicating the test was done but the result was Not Detected.
Absence of a row, indicating the element test was not done.

When you want your client to display the results for a sample, you would fetch 30 rows (or fewer if not all elements were tested in a given sample), then write code in your client to loop over the 30 rows, and present the data however you want.


Why doesn't time for a simple update scale linearly with the number of records? I am running some performance tests and I started with a dataset of 900.000 records the update took 10 seconds or about 90000 records per second. Then I increased the number of records to 9.000.000 and the update took 170 seconds or about 52941 records updated per second.

Why does the number of records updated per seconds drop when the total number of records increases?

The distribution in the data is the same, the larger set is the smaller set copied 10 times. The size of the data and log files did indeed increase since I copied the data 10 times. I did shrink the log file after that. There is no other workload on the database server at this moment.

The stored procedure doing the update is the following:

CREATE PROCEDURE updatePoints
    @ids NVARCHAR(MAX)
AS
    DECLARE @points1 INT
    DECLARE @points2 INT

    SET @points1 = 1
    SET @points2 = 2

    UPDATE p
    SET points = (0 + CASE WHEN p.[x] = m.[x] AND p.[y] = m.[y]
                              THEN @points1
                              ELSE 0
                      END +
                      CASE WHEN p.[u] = m.[u] AND p.[v] = m.[v]
                              THEN @points2
                              ELSE 0
                      END)
    FROM p
    JOIN  m ON m.id = p.mid
    JOIN platform.Split(@ids) i ON i.Value = m.id
    WHERE m.[status] = 1

If you post the execution plans there may be obvious differences that we can highlight. Other than plans it could be:

Different distribution of values in the dataset, leading to more frequent page splits
Data and or log file growth
A sort or hash operation spilling to tempdb
Contention with other workloads

Need more information (queries, sample data, schema, plans) for better answers.


Security setting for SQL Server services I'm currently starting to upgrade out SQL Server estate to 2012, and historical people have created two domain accounts for the database services MSSSQLServer and one for the SQL Server agent to operate under.

However I've always been concerned that in most case these have been given sysadmin rights on the SQL Server and would like to change this.  However I'm unable to find anywhere were best practice is detailed on this subject, I really want to lower this account to its minimal security requirements for starting and stopping the database but will that be enough for it to function?

Hope someone can help :-)

The engine provisioning Setup makes them sysadmin members, as documented at Configure Windows Service Accounts and Permissions:

  SQL Server Per-service SID Login and Privileges The per-service SID of
  the SQL Server service is provisioned as a Database Engine login. The
  per-service SID login is a member of the sysadmin fixed server role.

  SQL Server Agent Login and Privileges The per-service SID of the SQL
  Server Agent service is provisioned as a Database Engine login. The
  per-service SID login is a member of the sysadmin fixed server role.

If the product Setup makes them sysadmin is better you follow the same practice.


SP is locking due to Begin Transaction Please read the following code
SET IMPLICIT_TRANSACTIONS ON

BEGIN TRANSACTION
DELETE FROM xxxx
SELECT * FROM xxxxxxxx
WHERE EXISTS (  SELECT * FROM xxxxx
                WHERE CONCAT(xxxxxxx) =  CONCAT(xxxxxxx)
            )

COMMIT

After this I run the following SP
CREATE PROCEDURE [dbo].[xxxxxx]
AS
BEGIN
---To remove any duplicates
WITH CTE AS(
   SELECT xxxxxx, xxxxxx, xxxx,
       RN = ROW_NUMBER()OVER(PARTITION BY xxxxx, xxxx ORDER BY xxxx)
   FROM xxxxxxx
)

delete FROM CTE WHERE RN > 1

---Insert and load
insert into xxxxxxxx
select xxxxxxx.*
from xxxxxxx
left join xxxxx
on
    xxxxxxxxxxx=xxxxxxxx COLLATE SQL_Latin1_General_CP1_CI_AI

where xxxxx is null

truncate table xxxxx_Staging

END

The above SP stuck and continue to run due to lock. Why my SP is stuck even I am using Being Transaction before Delete in the first staement. I dont want to use with(NOlock) option in the SP. Anyway to write SP in a better way.

You turned on IMPLICIT_TRANSACTION mode, which will start a new transaction on any statement that writes to the database.  And that transaction must be committed or rolled back explicitly, even if the transaction is started inside a stored procedure.
EG
use tempdb
go
drop table if exists t
create table t(id int)
go

create or alter procedure foo
as
begin
  insert into t(id) values (1)
end
go
SET IMPLICIT_TRANSACTIONS ON
exec foo

select @@trancount

outputs
1


Can't SHOW GRANTS after MySQL re-install I had to completely re-install MySQL on one of the slaves and then rebuild it via a sql dump (we have 1 master and 2 slaves). I tried to SHOW GRANTS for one of the users, but keep getting the following error:

  ERROR 1141 (42000): There is no such grant defined for user 'USER' on host '%'

I can SHOW GRANTS for root though. SHOW GRANTS needs a SELECT privilege for the mysql database, which root has:

mysql> show grants;
+---------------------------------------+
| Grants for root@localhost                                                             |
+---------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*dfdfdfdf' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                           |
+-----+
2 rows in set (0.01 sec)

Btw, the privileges themselves exist, as I can select them from mysql.user table:

mysql> select update_priv, delete_priv from mysql.user where user like 'USER%';
+-------------+-------------+
| update_priv | delete_priv |
+-------------+-------------+
| Y           | Y           |
+-------------+-------------+
1 row in set (0.00 sec)

What am I missing?

Edit by RolandoMySQLDBA 2013-01-15 21:06 EDT

1) Please run SELECT VERSION(); to tell me what version of MySQL you are using

2) Please run SELECT COUNT(1) from information_schema.columns WHERE table_schema='mysql' AND table_name='user';

3) Please tell me from what version of mysql did the mysqldump come from.

Entries existing in the mysql.user table and privileges being recognized by the MySQL server are two different things that are only loosely-coupled.

mysql> FLUSH PRIVILEGES;

Any time you change the content of the mysql.user table, other than by issuing a GRANT ...-type statement, including restoring from a backup, you need to issue the FLUSH PRIVILEGES command before MySQL will realize those changes have happened.  This should make the users visible.

Or, by now you may have restarted the server, which would also fix it.


Calculating daily growth using previous row value in new column - SQL Server I have the following query below that shows size of a database and when this data was collected. I am trying to create a new column that has "average daily growth" which would be determined by the size on the previous day.

If my query captures data from multiple databases/instances how can I do this for each database? As seen in here

I am trying to calculate daily growth per database using the "DB Size mb" column and would like a new column called "LastDayGrowth"

This is my query:

SELECT DISTINCT
        c.instance_name ,
        a.database_name ,
        CONVERT (DATE, SWITCHOFFSET(CAST (a.collection_time AS DATETIMEOFFSET),
                                    '+00:00')) AS 'Collection Time' ,
        'LDF mb' = ( CONVERT(DEC(15, 2), a.logsize) * 8192 / 1048576.0 ) ,
        'MDF mb' = ( CONVERT(DEC(15, 2), a.dbsize) * 8192 / 1048576.0 ) ,
        ( ( CONVERT(DEC(15, 2), a.dbsize) + CONVERT(DEC(15, 2), a.logsize) )
          * 8192 / 1048576.0 ) AS 'DB Size mb' ,
        'Reserved mb' = ( a.reservedpages * 8192 / 1048576.0 ) ,
        'Data mb' = CONVERT(DEC(15, 2), a.pages) * 8192 / 1048576.0 ,
        'Index mb' = ( a.usedpages - a.pages ) * 8192 / 1048576.0 ,
        'Unused mb' = ( ( CONVERT(DEC(15, 2), a.reservedpages)
                          - CONVERT(DEC(15, 2), a.usedpages) ) * 8192
                        / 1048576.0 ) ,
        'Unallocated mb' = ( CASE WHEN a.dbsize >= a.reservedpages
                                  THEN ( CONVERT (DEC(15, 2), a.dbsize)
                                         - CONVERT (DEC(15, 2), a.reservedpages) )
                                       * 8192 / 1048576.0
                                  ELSE 0
                             END )
FROM    MDWDB.snapshots.disk_usage a
        INNER JOIN MDWDB.core.snapshots_internal b ON a.snapshot_id = b.snapshot_id
        INNER JOIN MDWDB.core.source_info_internal c ON b.source_id = c.source_id
ORDER BY c.instance_name ,
        a.database_name ASC;

I have tried this using the "LAG" function but did not succeed. I tried it like this:

LastDayGrowth = DATEDIFF(DAY,
                         LAG(( CONVERT(DEC(15, 2), a.dbsize)
                               + CONVERT(DEC(15, 2), a.logsize) )
                             * 8192 / 1048576.0, 1, NULL) OVER ( ORDER BY a.database_name, ( ( CONVERT(DEC(15,
                                                      2), a.dbsize)
                                                      + CONVERT(DEC(15,
                                                      2), a.logsize) )
                                                      * 8192
                                                      / 1048576.0 ) ),
                         ( ( CONVERT(DEC(15, 2), a.dbsize)
                             + CONVERT(DEC(15, 2), a.logsize) ) * 8192
                           / 1048576.0 ))

Any tips, or advice would be greatly appreciated!

I just read this article adressing this topic and I suppose you might be interested, because it uses the lag function to solve exactly your Task:

https://www.mssqltips.com/sqlservertip/3690/identify-sql-server-database-growth-rates/

The Author created the following Script using the LAG Window function:

SELECT DISTINCT
    A.[database_name]
,   AVG( A.[Backup Size (MB)] - A.[Previous Backup Size (MB)] ) OVER ( PARTITION BY A.[database_name] ) AS [Avg Size Diff From Previous (MB)]
,   MAX( A.[Backup Size (MB)] - A.[Previous Backup Size (MB)] ) OVER ( PARTITION BY A.[database_name] ) AS [Max Size Diff From Previous (MB)]
,   MIN( A.[Backup Size (MB)] - A.[Previous Backup Size (MB)] ) OVER ( PARTITION BY A.[database_name] ) AS [Min Size Diff From Previous (MB)]
,   A.[Sample Size]
FROM
(
    SELECT
        s.[database_name]
    --, s.[backup_start_date]
    ,   COUNT(*) OVER ( PARTITION BY s.[database_name] ) AS [Sample Size]
    ,   CAST ( ( s.[backup_size] / 1024 / 1024 ) AS INT ) AS [Backup Size (MB)]
    ,   CAST ( ( LAG(s.[backup_size] )
            OVER ( PARTITION BY s.[database_name] ORDER BY s.[backup_start_date] ) / 1024 / 1024 ) AS INT ) AS [Previous Backup Size (MB)]
    FROM
        [msdb]..[backupset] s
    WHERE
        s.[type] = 'D' --full backup
    --ORDER BY
    --  s.[database_name]
    --, s.[backup_start_date]
) AS A
ORDER BY
    [Avg Size Diff From Previous (MB)] DESC;
GO


Use of MySQL trigger to insert row's week number I have a table of orders in a MariaDB database that currently has a datetime column. For the dashboard I am building for this database, I have a statistic I would like to display which is "Orders This Week."

My question is this: Can a trigger be used to take the datetime from a just-inserted order and insert the week number of that date into another column? If so, can I also use SQLAlchemy to match the order's week number to Python's strftime('%U') to see orders in a given week?

You tagged MySQL, so here is what should work for a MySQL trigger on your Before Insert trigger.

 DELIMITER $$
 CREATE DEFINER=`USERNAMEHERE`@`%` TRIGGER `SCHEMANAMEHERE`.`TABLENAMEHERE_BEFORE_INSERT` AFTER INSERT
 ON `TABLENAMEHERE`
 FOR EACH ROW
 BEGIN
    SET NEW.WeekNumber = WEEKOFYEAR(NEW.InsertedDateTime);
 END$$
 DELIMITER ;


select based on sum of rows compared to another field value I have a table of invoice items. A single transaction may result in multiple debit items and multiple credit items sharing the same invoice_set_id, I need to compare the sum of the debit items against the sum of the credit items and add the invoice_set_id to the result set if sum(debits) > sum(credits). It should also add to the result if there is no row with a credit amount. Using mySQL. Thanks for any help. Example table and result follows:

invoice_item_id  invoice_set_id  credit_debit  amount
62                a22             debit         15.00
63                a22             debit          8.00
64                a22             credit        23.00
65                b23             debit         44.00
66                c55             debit         15.00
67                c55             debit          2.00
67                c55             credit         8.00

Given the above, the result set should be:

invoice_set_id
b23
c55

Explanation: a22 is not returned because the debits and credits are equal, b23 is returned because it has a debit but no credit, and c55 is returned because the sum of the debits is greater than the single credit.

I appreciate any help with this. The actual query is more involved, but I think this particular problem is all I need help with.

This is an UNPIVOT problem.To get the sum of credit items and the sum of debit items you can use a SUM of a CASE statement. To show that, we need a table first:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE invoice_items
    (`invoice_item_id` int, `invoice_set_id` varchar(3), `credit_debit` varchar(6), `amount` int)
;

INSERT INTO invoice_items
    (`invoice_item_id`, `invoice_set_id`, `credit_debit`, `amount`)
VALUES
    (62, 'a22', 'debit', 15.00),
    (63, 'a22', 'debit', 8.00),
    (64, 'a22', 'credit', 23.00),
    (65, 'b23', 'debit', 44.00),
    (66, 'c55', 'debit', 15.00),
    (67, 'c55', 'debit', 2.00),
    (67, 'c55', 'credit', 8.00)
;

Now you can combine SUM and CASE like this:
Query 1:

SELECT invoice_set_id,
       SUM(CASE WHEN credit_debit = 'debit' THEN amount END) AS debit_amount,
       SUM(CASE WHEN credit_debit = 'credit' THEN amount END) AS credit_amount
  FROM invoice_items
 GROUP BY invoice_set_id;

Results:

| INVOICE_SET_ID | DEBIT_AMOUNT | CREDIT_AMOUNT |
|----------------|--------------|---------------|
|            a22 |           23 |            23 |
|            b23 |           44 |        (null) |
|            c55 |           17 |             8 |

From here it is fairly simple to filter out the unwanted rows. We just need to wrap the two sums in COALESCE statements to turn NULL into 0:

Query 2:

SELECT invoice_set_id
  FROM(
    SELECT invoice_set_id,
           SUM(CASE WHEN credit_debit = 'debit' THEN amount END) AS debit_amount,
           SUM(CASE WHEN credit_debit = 'credit' THEN amount END) AS credit_amount
      FROM invoice_items
     GROUP BY invoice_set_id
  )X
WHERE COALESCE(debit_amount,0) > COALESCE(credit_amount,0);

Results:

| INVOICE_SET_ID |
|----------------|
|            b23 |
|            c55 |

If your actual problem is as simple as your example and you do not need the intermediate values for anything else, you can get the result also by just changing the sign of the amount based on the credit_debit value before calculating the sum. That would look like this:

Query 3:

SELECT invoice_set_id
  FROM invoice_items
 GROUP BY invoice_set_id
HAVING SUM(CASE WHEN credit_debit = 'debit' THEN amount ELSE -amount END)>0;

Results:

| INVOICE_SET_ID |
|----------------|
|            b23 |
|            c55 |


Db2 LUW tens of millions of rows We have a table with tens of millions of time series rows. We index by three foreign keys to relations. Let's hypothetically say medical observation row to doctor, acl, and person. Main thing being a 3 part key where acl is based on the user and the other two based on entity relationship.

What's a good physical architecture in db2 to make this run fast? (sub second) It currently takes 10+ seconds to query.

RAM usage is around 25-30%
Read hits to buffer pool are around -1300%
Log hits to buffer pool are 100% (are we doing too many transactions when we don't need to - i.e. change to read uncommitted)

I wonder about having a separate table space for this with a separate buffer pool for example.

Also would you consider partitioning?

I'm curious about your statement: read hits to bufferpool are around -1300%. Is your bufferpool hit ratio -1300% ? Can you check the hit-ratio with?

  WITH BPMETRICS AS (
    SELECT bp_name,
           pool_data_l_reads + pool_temp_data_l_reads +
           pool_index_l_reads + pool_temp_index_l_reads +
           pool_xda_l_reads + pool_temp_xda_l_reads as logical_reads,
           pool_data_p_reads + pool_temp_data_p_reads +
           pool_index_p_reads + pool_temp_index_p_reads +
           pool_xda_p_reads + pool_temp_xda_p_reads as physical_reads,
           member
    FROM TABLE(MON_GET_BUFFERPOOL('',-2)) AS METRICS)
   SELECT
    VARCHAR(bp_name,20) AS bp_name,
    logical_reads,
    physical_reads,
    CASE WHEN logical_reads > 0
     THEN DEC((1 - (FLOAT(physical_reads) / FLOAT(logical_reads))) * 100,7,2)
     ELSE NULL
    END AS HIT_RATIO,
    member
   FROM BPMETRICS;

If it is indeed negative, there are more physical reads than logical reads. Several physical reads per logical read is weird, one possible cause is that the bufferpool is so small that it cant hold the EXTENTSIZE for the tablespace in question.

One other thing that comes to mind is if Db2 failed to start the normal bufferpool and instead uses the default bufferpool - which is very small - for that pagesize.


encrypt data without performance issues? I've googled around for the last 45 minutes or so and it seems like that I can't figure out what kind of options I got when it comes up to encryption in databases.

Problem:

I got a SaaS product and the data is kinda sensitive.
I want to give my customers 100% security on the fact that I can't read their data.

Is this even possible?
What kind of possibilities do I got?

I got a SaaS product and the data is kinda sensitive. I want to give my customers 100% security on the fact that I can't read their data.

If it's software as a service, and you do more than simply store blobs of data then give those blobs back when the customer asks, this is pretty much impossible.

You're dreaming. You can't realistically combine SaaS and the inability to see the customers' data.

(About the only outfit I know that does so is SpiderOak, and it does so by supporting almost no features - it stores stuff, and then it retrieves that stuff. That's it.)

  Is this even possible? What kind of possibilities do I got?

Few.

You can't index encrypted data unless you know the key - and the index will contain decrypted values.

You can't write a WHERE clause that filters based on the encrypted values unless you know the key. You could get the client to supply a pre-encrypted key to compare if it's a simple equality test, but you can't do b-tree index scans (requires > and < operators), or much else of interest.

You can't aggregate values unless you know the key.

You really can't do anything useful with them unless you know the key.

In theory you can use homomorphic encryption to allow computations on encrypted values. In practice it's incredibly limited, slow, and impractical, as well as weaker than other crypto. In practice most real-world homomorphic systems are useful for sums and that's about it.


Table compression in InnoDB? I have read that table index compression happens just in MyISAM, but I came across
an article in dev.mysql that said: InnoDB supports table and index compression!
So now what is the pros of MyISAM?
Is there any reason left to use MyISAM? (except full-text search)

Recovering Disk Space

As far as recovering InnoDB diskspace goes, you only have to migrate data out of ibdata1 and import it back one time if you enable innodb_file_per_table.

Once you do that, any InnoDB table that gets bloated can be shrunk. For example, if you have an InnoDB called mydb.mytable, you can shrink it with:

ALTER TABLE mydb.mytable ENGINE=InnoDB;

or

OPTIMIZE TABLE mydb.mytable;

Restoring Databases

@Craig is totally right on MyISAM.

As for InnoDB, restoring tables individually can be a nightmare. This is the case because tables are either inside ibdata1 (innodb_file_per_table=0) or outside (innodb_file_per_table=1).

For tables outside ibdata1, this is a 24/7 nightmare because the tablespace id of the .ibd file may change if a table is dropped and recreated throughout the course of a business day. If you backup the .ibd with its tablespace id that day, you cannot restore it to it DB Folder by dropping it in. You also cannot do any tricks ALTER TABLE tblname DISCARD TABLESPACE and ALTER TABLE tblname IMPORT TABLESPACE because the tablespace id is hardcoded. You have to write some custom adhoc scripts to pull off such a miracle in conjunction with ALTER TABLE tblname DISCARD TABLESPACE and ALTER TABLE tblname IMPORT TABLESPACE.
For tables inside ibdata1, mysqldump is your friend. 'NUFF SAID...


How to calculate total ON hours and Total OFF hours in day of a motor using PHP and MySQL In my application I would need to calculate total ON hrs and OFF hrs in a day of motors in one tank. Every time he/she turn ON/OFF motors, data will go and store in motor_status_log table of my database.

While I searched for same thing in google I got following URL: calculating total login-logout time of a particular user in mysql

My rearranged query is:

"SELECT ilv.motor_id,SUM(UNIX_TIMESTAMP(offtime)-UNIX_TIMESTAMP(ontime)) FROM (SELECT a.motor_id, a.timestamp AS offtime,(SELECT MAX(b.timestamp) FROM motor_status_log b WHERE b.motor_id=a.motor_id AND b.timestamp<a.timestamp AND b.runningstatus='on') as ontime FROM motor_status_log a WHERE a.runningstatus='off' AND a.timestamp BETWEEN '2013-06-26 00:00:00' AND '2013-06-26 23:59:59' AND motor_id='$motor_id') ilv GROUP BY ilv.motor_id"

and by using the given SQL Query in the above link it is not working.

And table in my database is like:

+--------+-------+---------+--------------------+-----------------+
|user_id |Tank_id|motor_id |  timestamp         | runningstatus   |
+--------+-------+---------+--------------------+-----------------+
|    1   |    1  |    1    |2013-06-26 09:53:29 | off             |
|    1   |    1  |    1    |2013-06-26 10:45:42 | on              |
|    1   |    1  |    1    |2013-06-26 10:55:29 | off             |
|    1   |    1  |    1    |2013-06-26 17:55:39 | on              |
|    1   |    2  |    2    |2013-06-26 17:56:39 | off             |
|    1   |    2  |    2    |2013-06-26 08:53:29 | off             |
|    1   |    2  |    2    |2013-06-26 10:45:42 | on              |
|    1   |    2  |    2    |2013-06-26 12:55:29 | off             |
|    1   |    2  |    2    |2013-06-26 14:55:39 | on              |
|    1   |    3  |    3    |2013-06-26 17:56:39 | off             |
+----------------+---------+--------------------+-----------------+

Please help me. Thanks in advance

A table and some data. I ignored your primary key here, but I wouldn't do that in production.

CREATE TABLE motor_status_log (
  user_id integer not null,
  tank_id integer not null,
  motor_id integer not null,
  timestamp datetime not null,
  runningstatus varchar(3))
;

INSERT INTO motor_status_log VALUES
    (1, 1, 1, '2013-06-26 09:53:29', 'off'),
    (1, 1, 1, '2013-06-26 10:45:42', 'on'),
    (1, 1, 1, '2013-06-26 10:55:29', 'off'),
    (1, 1, 1, '2013-06-26 17:55:39', 'on'),
    (1, 2, 2, '2013-06-26 17:56:39', 'off'),
    (1, 2, 2, '2013-06-26 08:53:29', 'off'),
    (1, 2, 2, '2013-06-26 10:45:42', 'on'),
    (1, 2, 2, '2013-06-26 12:55:29', 'off'),
    (1, 2, 2, '2013-06-26 14:55:39', 'on'),
    (1, 3, 3, '2013-06-26 17:56:39', 'off');

You can get the basic data you need with this query. You probably want to take a hard look at the subquery that starts on the second line, and returns the column "run_end".

select t1.tank_id, t1.motor_id, t1.timestamp as run_start
  , (select min(timestamp)
     from motor_status_log t2
     where t2.tank_id = t1.tank_id
       and t2.motor_id = t1.motor_id
       and t2.timestamp > t1.timestamp
       and t2.runningstatus = 'off') as run_end
from motor_status_log t1
where t1.runningstatus = 'on'
order by t1.tank_id, t1.motor_id, t1.timestamp;

You can use that in a FROM clause, and calculate the minutes of run time with timestampdiff().

select *, timestampdiff(minute, run_start, run_end) elapsed_min
from
  (select t1.tank_id, t1.motor_id, t1.timestamp as run_start
    , (select min(timestamp)
       from motor_status_log t2
       where t2.tank_id = t1.tank_id
         and t2.motor_id = t1.motor_id
         and t2.timestamp > t1.timestamp
         and t2.runningstatus = 'off') as run_end
  from motor_status_log t1
  where t1.runningstatus = 'on') t
order by tank_id, motor_id, run_start

TANK_ID  MOTOR_ID  RUN_START                   RUN_END                     ELAPSED_MIN
1        1         June 26 2013 10:45:42+0000  June 26 2013 10:55:29+0000  9
1        1         June 26 2013 17:55:39+0000  (null)                      (null)
2        2         June 26 2013 10:45:42+0000  June 26 2013 12:55:29+0000  129
2        2         June 26 2013 14:55:39+0000  June 26 2013 17:56:39+0000  181

I'd presume that the 'off' time for each day would be 24 hours minus the run time. I could be wrong.

You need to watch for 'on's that don't have corresponding 'off's, and vice-versa. (Motor 3 has an 'off' with no corresponding 'on'.) It's not clear what you might want to do about that kind of situation.  You could

ignore it,
treat it as a maximal run period (which would probably guarantee that preventive maintenance would get done earlier rather than later),
treat it as an average run period, or
something else I haven't imagined yet.

I ignored it.

Summing elapsed time

To sum elapsed time the simplest way, wrap the previous query in another select. But this "simplest way" can give you misleading numbers if you have motors that are on over a period of days. (You can solve that problem with an auxiliary table, though.)

select motor_id
       , date(run_start) as start_date
       , sum(elapsed_min)/60.0 as hrs_on
       , 24 - sum(elapsed_min)/60.0 as hrs_off
from
  (select *, timestampdiff(minute, run_start, run_end) elapsed_min
   from
     (select t1.tank_id, t1.motor_id, t1.timestamp as run_start
       , (select min(timestamp)
          from motor_status_log t2
          where t2.tank_id = t1.tank_id
             and t2.motor_id = t1.motor_id
             and t2.timestamp > t1.timestamp
             and t2.runningstatus = 'off') as run_end
    from motor_status_log t1
    where t1.runningstatus = 'on') t
  ) tt
group by motor_id, start_date
order by motor_id, start_date

motor_id  start_date  hrs_on  hrs_off
--
1         2013-06-26  0.15    23.85
2         2013-06-26  5.17    18.83


Need to maintain transactional consistency of the data across both servers, need to achieve these goals without manual intervention I got this question about MS SQL Server 2012 administration, two production servers in the same data center. You need to ensure that database remains available if a catastrophic server failure or a disk failure occurs.

You need to maintain transactional consistency of the data across both servers. You need to achieve these goals without manual intervention.

The correct answer for the above question is

>>
Two servers configured on the same subnet
SQL Server Availability Group configured in Synchronous-Commit Availability Mode
<<

But I think the correct answer should be

    Two servers configured in Windows Failover Cluster in the same data center SQL Server configured as a clustered instance<<

The tricky part is in this requirement:

  You need to ensure that database remains available if a catastrophic
  server failure or a disk failure occurs. You need to maintain
  transactional consistency of the data across both servers. You need to
  achieve these goals without manual intervention.

The disk failure part means a failover cluster alone won't work because the storage is shared with both nodes. If the storage where the data files live fails, then both nodes will be affected.

However, a 2-node synchronous Availability Group isn't the answer either, because as Microsoft's own documentation points out:

  If primary's session-timeout period is exceeded by a secondary replica, the primary replica temporarily shifts into asynchronous-commit mode for that secondary replica. When the secondary replica reconnects with the primary replica, they resume synchronous-commit mode.

Read further in that link in the "Factors That Disrupt Data Synchronization" section, and Microsoft elaborates on the reasons why you can't guarantee that a 2-node AG will not lose data on failover.

So what's the right answer for SQL Server 2012?

There isn't one. You can't guarantee zero data loss with 2 independent SQL Server 2012s without third party tools (like SAN replication, and even then, there's a ton of work involved.) I'm guessing the question came from a test or certification written by somebody without real-world experience. That wouldn't be the first time, and it won't be the last.

Is there a right answer for later versions?

Yes, SQL Server 2017 introduced a new REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT setting at the Availability Group level. The default is 0, which means as long as the primary receives the transaction, it's committed. You can change that to 1 (or more), which means that if at least that number of secondaries don't also commit the transaction, then the transaction fails.


How to avoid redundant case/when in Postgres? Based on this simple example table:

create table some_data (x integer, y integer);

insert into some_data (x,y) values
(1,1),
(1,2),
(1,1000),
(1,2),
(2,1),
(2,1),
(2,2);

I query it trying to sum the values based on particular conditions, like:

select
  case when x = 1 then
        sum(case when y = 1 then 1 else 0 end) as counter_x,
        sum(case when y = 2 then 2 else 0 end) as counter_y,
        sum(case when y > 2 then 7 else 0 end) as counter_z,
      end,

  sum(case when x = 1 then 1 else 0 end) as counter_a,
  sum(case when x = 2 and y = 1 then 10 else 0 end) as counter_b,
  sum(case when x = 3 and y = 30 then 400 else 0 end) as counter_c
  from some_data;

What I am expecting here is:

counter_x | counter_y | counter_z | counter_a | counter_b | counter_c
----------+-----------+-----------+-----------+-----------+----------
1         | 4         | 7         | 4         | 20        | 0

However my query doesn't work as the wrapping case when x = 1 is not allowed.
How can I fix it?

You should add a condition for every counter:

select
    sum(case when x = 1 and y = 1 then 1 else 0 end) as counter_x,
    sum(case when x = 1 and y = 2 then 2 else 0 end) as counter_y,
    sum(case when x = 1 and y > 2 then 7 else 0 end) as counter_z,
    sum(case when x = 1 then 1 else 0 end) as counter_a,
    sum(case when x = 2 and y = 1 then 10 else 0 end) as counter_b,
    sum(case when x = 3 and y = 30 then 400 else 0 end) as counter_c
from
    some_data;

counter_x | counter_y | counter_z | counter_a | counter_b | counter_c
--------: | --------: | --------: | --------: | --------: | --------:
        1 |         4 |         7 |         4 |        20 |         0

dbfiddle here


Where Clause Index Scan - Index Seek I have below table:

CREATE TABLE Test
(
    Id int IDENTITY(1,1) NOT NULL,
    col1 varchar(37) NULL,
    testDate datetime NULL
)

GO

insert Test
select null
go 700000

insert Test
select cast(NEWID() as varchar(37))
go 300000

And below indexes:

create clustered index CIX on Test(ID)
create nonclustered index IX_RegularIndex on Test(col1)
create nonclustered index IX_RegularDateIndex on Test(testDate)

When I query on my table:

SET STATISTICS IO ON
select * from Test  where col1=NEWID()
select * from Test  where TestDate=GETDATE()

First is making index scan whereas the second index seek. I expect that both of them must make index seek. Why does the first make index scan?

The problem here is an implicit conversion to UNIQUEIDENTIFIER type. There is a warning about it in the execution plan, and it is connected with Data Type Precedence.

Your table is using the wrong data type to store GUIDs. If col1 were correctly typed as uniqueidentifier, your problem would never have arisen. The string representation of a GUID has a maximum length of 36 (not 37!) characters, which is much less efficient than using uniqueidentifier (16 bytes). Note also that NEWID() returns a uniqueidentifier, not any sort of string.

I added some hints to my queries below to get an identical execution plan.

You can avoid this if you assign NEWID() to a variable but you still have to declare VARCHAR(37) variable or just cast NEWID() to VARCHAR(37) in the where clause.

Here are a few queries that show us how it works:

-- Query 1
SELECT * FROM Test WITH(INDEX=[IX_RegularIndex]) WHERE col1=NEWID()
GO

-- Query 2
DECLARE @id UNIQUEIDENTIFIER = NEWID()

SELECT * FROM Test WITH(INDEX=[IX_RegularIndex]) WHERE col1=@id
GO

-- Query 3
DECLARE @id VARCHAR(37) = NEWID()

SELECT * FROM Test WITH(INDEX=[IX_RegularIndex]) WHERE col1=@id
GO

-- Query 4
SELECT * FROM Test WITH(INDEX=[IX_RegularIndex]) WHERE col1=cast(NEWID() as varchar(37))
GO

Related Q & A concerning the number of times the NEWID function is evaluated:

NEWID() In Joined Virtual Table Causes Unintended Cross Apply Behavior


Unsupported redo log format (0). The redo log was created before MySQL 5.7.9 I have not started MySQL on my Mac Mojave in while. When today I ran mysql.server start I got the following error:

2019-10-03T21:42:11.410763Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2019-10-03T21:42:11.464825Z 1 [ERROR] [MY-013090] [InnoDB] Unsupported redo log format (0). The redo log was created before MySQL 5.7.9
2019-10-03T21:42:11.464850Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2019-10-03T21:42:11.465724Z 1 [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
2019-10-03T21:42:11.466028Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2019-10-03T21:42:11.466203Z 0 [ERROR] [MY-010119] [Server] Aborting

My online research suggested that I need to rename ib_logfile1 and ib_logfile1 in my /usr/local/var/mysql directory.

I did that and then I got the following error:

2019-10-03T21:27:39.506301Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2019-10-03T21:27:40.301335Z 1 [ERROR] [MY-013168] [InnoDB] Cannot upgrade server earlier than 5.7 to 8.0
2019-10-03T21:27:45.304727Z 1 [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
2019-10-03T21:27:45.305119Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2019-10-03T21:27:45.305571Z 0 [ERROR] [MY-010119] [Server] Aborting

What is going on here? (BTW, I thought I already had mysql8 installed but I am not sure anymore)

You must upgrade to 5.7 as a first step.  Then you can upgrade to 8.0.

If Mac is trying to make a bigger leap, then they are doing it wrong.

You can probably see what version is installed (without hitting that problem) by doing

mysqld --version


drastically different runtimes in BIDS and the SSRS Web Portal I have a report in a BIDS project that has been deployed to our SSRS Web Portal.  On the web, it renders within seconds.  In BIDS, it takes upwards of seven minutes.  I can see that the query runs against the database quickly - what in the world is BIDS doing that is taking so long?

I have seen this happen in a number of different cases...

If your query is returning a lot of rows across a slow WAN link then you might be seeing a slowdown due to network traffic.  I have that class of problem where I'm working now.

If you report server is on the same fast LAN as the database server it would be quicker under these circumstances.  This might explain the difference in performance.

Also, if you have virus scanning software running on your PC it could interfere with the local processing in a variety of ways - scanning the .data file etc.  Often real-time scanning also hooks into memory allocation and scans data segments of applications.  This really slows down managed applications.

Those are a couple of possibilities that come to mind.  There may be other causes as well.


Which is the most performant way to copy a number of SQL Server tables to a readonly SQLite database? I have a database with about 50 tables with use together about 1 GB space.
The SQLite database will be readonly.
This copying will be done once each month.

EDIT:

I see one possible solution using linked server via SQLite 3 ODBC Driver which results in rather simple t-sql scripts. I don't know about its performance.

I guess that other solution require more work, but obtain better performance.

I have successfully used SQL Server Integration Services (SSIS) to setup a job to "archive" data to SQLite databases. A quick way to build a SSIS job, is to do a Task->Export Data task. At the very end you will see a checkbox on the top left asking you if you want to save this as a SSIS job.

Make sure that you have installed the SQLite provider from http://sqlite.phxsoftware.com/

You can schedule this SSIS package once it is saved.


Multiple one to many relationships between tables I'm creating a simple dabase design with three entities and have a question about the relationships between my Employee and Task tables.

The thing is:

A task has an owner, which is represented by the "createdBy" attribute, which is a FK of empID;
A task also has an assignee, which is represented by the "assignedTo" attribute, which is also a FK of empID;

An employee can have multiple tasks, even if there was only one field using empID as a FK. But, you could say a task can have multiple employees, eventhough it can only have two. This would make it a many to many relationship, thus require an additional table.

However, because the number of employees that a task can have is fixed, because it has two designated fields, I feel like this is not the proper solution. Still, it feels strange to have multiple one to many relationships.

Could someone tell me whether or not it is possible to have the relationships the way they are now? Or would I need to treat it as a many to many relationship anyway?

If a task can have multiple assignees, then you may want a many-to-many table between employee and tasks.  However, the current model works well if the task can only be assigned to one employee at a time and you don't want to record assignment history.

It is not uncommon to have multiple one-to-many relationships between two tables.  You have correctly modeled the case you have described.   However, I generally separate created-by and owners relations.  This permits someone other than the owner to enter proposals and tasks.  A created-by fields is typically used as an audit field which should not be modified, while the owner field usually would be modifiable.

You do have a many-to-many relationship between employees and proposals. This resolved by the tasks table.


Allow multiple users to see procedures and functions I have GRANT ALL PRIVILEGES ON `test1`.* TO 'admin'@'%', GRANT ALL PRIVILEGES ON `test1`.* TO 'devel'@'%' (from SHOW GRANTS FOR some_user) and I have created simple procedure as admin in test1.

And here is what I see:

as admin

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| admin@%        |
+----------------+
1 row in set (0,03 sec)

mysql> SELECT ROUTINE_DEFINITION FROM information_schema.ROUTINES WHERE SPECIFIC_NAME='test' AND ROUTINE_SCHEMA='test1';
+--------------------+
| ROUTINE_DEFINITION |
+--------------------+
| begin
end          |
+--------------------+
1 row in set (0,04 sec)

mysql> call test();
Query OK, 0 rows affected (0,03 sec)

as devel:

mysql> select current_user();                                                   +----------------+
| current_user() |
+----------------+
| devel@%        |
+----------------+
1 row in set (0,03 sec)

mysql> SELECT ROUTINE_DEFINITION FROM information_schema.ROUTINES WHERE SPECIFIC_NAME='test' AND ROUTINE_SCHEMA='test1';
+--------------------+
| ROUTINE_DEFINITION |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0,03 sec)

mysql> call test();
Query OK, 0 rows affected (0,03 sec)

How can I allow multiple users to see code of procedure or function?

Try granting EXECUTE permissions on user devel.

GRANT EXECUTE ON PROCEDURE test1.yourprocedure TO 'devel'@'%'


Backup encryption questions We're going to be implementing encrypted backups in SQL 2014 soon and I'd like to know the gotchas or lessons encountered by anyone else who has done the same.   I've read up at MSDN and elsewhere, created my master key and cert for backups in master db (and backed them up!).  We do prod-to-test refreshes occasionally so I'll create the prod cert there from the backup file.  None of our databases are TDE.

What do you recommend maintaining the cert & key backup files and keeping them secure?  What kind of problems have you had long term with administering the backups or restoring them during DR?

Just looking for tips to avoid the arrows that inevitably come with something new & different.  Paranoia level is high where recoverability is concerned.

Assuming you have the process down tight (as in scripted, tested, re-tested, and tested with the newest and most junior persons on the team) the only other area that I seen people slip on is accessibility of the key. Most people get that they should not store it in the same place as the backup files or even transport them together.

What is sometimes missed is whether the keys/certs are as well protected and accessible in a disaster. I've encountered users asking for some built-in backdoor from Microsoft (doesn't exist) or seek help to hack their own DBs because the key was lost along with a major incident at their primary data center (key/cert backups were in a secure administrative server that was not replicated to the DR site).

As for keeping them secure and fresh, the former is no different from storing other valuable data. Place in a secure host, highly restrict access (JIT if possible), all access logged in a secure tamper evident location (non-repudiation). Rotation process and mechanism must be automated to include the backup and DR sites. Everything validated by a different party from the one that executed on the tasks. Really no rocket science here. Things fail when humans think the tasks are trivial and they can do it in their sleep. Automation that has logs, monitors and alerts built-in will significantly reduce your risks.


User mapping to a standby database I have a standby database that is being restored in standby mode using log-shipped transaction files downloaded from a vendor's sFTP site on a daily basis.

Now, I am required to give a read-only access on this database to someone else, outside our domain. Since it is on standby, I can not create a user and grant read access. I also asked the vendor if they can do this for me on primary database but they said no.

So I am asking here if there is any way round to solve this problem?

If I change the database to online status and created a mapped user to the database, would that be okay to bring it back to a standby again and continue the restoring process without hassle?

If I change the database to online status and created a mapped user to
  the database, would that be okay to bring it back to a standby again
  and continue the restoring process without hassle?

No, that will break log shipping.

Unfortunately, until SQL Server 2014 you don't have much for other options.

  Grant access via stored procedures or views in another database. This
  will require enabling cross database ownership chaining, which can be
  a treacherous security road. And a lot of things could go wrong over
  time as tables are added, modified, and dropped.

  Create the login on the log shipping primary instance with the
  associated database user, and disable the login on the log shipping
  primary instance. If you’re using SQL authentication, you may have to
  use a special script to transfer the SID to the log shipping secondary
  to get it all to work.

In 2014+, you can use CONNECT ANY DATABASE and SELECT ALL USER SECURABLES IN SQL SERVER to grant read only access.


giving users select-only permissions of database objects I'm running SQL Server 2017 with a production database called Biz. I'd like to

Give three users the ability to run SQL queries against this database without altering the data, or maybe preferably,
Create a separate database (or schema) called BizView with some commonly-used queries, tools, etc. Then I could give BizView the appropriate read-only permissions on Biz. Then my users could just log into BizView and run their queries from there. . . right?

All of my users are on a domain. What combination of logins/roles/permissions would allow this basic read-only reporting on my production database? It's so simple, but I feel like I'm mis-applying Oracle concepts (I'm newly responsible for this single MS-SQL database) to this situation.

It sounds like you simply want to give those users db_datareader and nothing else. This is pretty common.

  Members of the db_datareader fixed database role can read all data
  from all user tables.

See the role explained in the MS DOCS


return continuous set of IDs based on two other columns Given a table such as the one below, how can I ask PostgreSQL to return the first set of n rows in which one column has a certain value (code=0) and the values of the second column (named segment) are all the same, and the sequence of IDs is not broken.

For n=4, the result should be id's 12, 13, 14, 15 (i.e. the first four id's where code=0 and the values of segment are the same - in this case 2).

CREATE TABLE foo
AS
  SELECT *
  FROM ( VALUES
    (  1, 0, 0 ),
    (  2, 0, 0 ),
    (  3, 0, 0 ),
    (  4, 0, 1 ),
    (  5, 0, 1 ),
    (  6, 0, 1 ),
    (  7, 1, 1 ),
    (  8, 0, 1 ),
    (  9, 1, 2 ),
    ( 10, 0, 2 ),
    ( 11, 1, 2 ),
    ( 12, 0, 2 ), -- HERE
    ( 13, 0, 2 ), -- HERE
    ( 14, 0, 2 ), -- HERE
    ( 15, 0, 2 ), -- HERE
    ( 16, 0, 3 )
  ) AS t(id,code,segment);

You can do something like this..

SELECT
  array_agg(id),
  code,
  segment,
  grp,
  count(*)
FROM (
  SELECT
    id,
    code,
    segment,
    count(is_reset) OVER (ORDER BY id) AS grp
  FROM (
    SELECT
      id,
      code,
      segment,
      CASE
        WHEN
          code <> lag(code) OVER (ORDER BY id)
          OR segment <> lag(segment) OVER (ORDER BY id)
        THEN 1
      END AS is_reset
    FROM foo
  ) AS t
) AS g
GROUP BY code, segment, grp;

   array_agg   | code | segment | grp | count
---------------+------+---------+-----+-------
 {11}          |    1 |       2 |   6 |     1
 {10}          |    0 |       2 |   5 |     1
 {1,2,3}       |    0 |       0 |   0 |     3
 {7}           |    1 |       1 |   2 |     1
 {4,5,6}       |    0 |       1 |   1 |     3
 {16}          |    0 |       3 |   8 |     1
 {8}           |    0 |       1 |   3 |     1
 {12,13,14,15} |    0 |       2 |   7 |     4
 {9}           |    1 |       2 |   4 |     1
(9 rows)

To query this, just search for code = 0 AND count >= 4.

Explanation

First we generate a reset

SELECT
  id,
  code,
  segment,
  CASE
    WHEN
      code <> lag(code) OVER (ORDER BY id)
      OR segment <> lag(segment) OVER (ORDER BY id)
    THEN 1
  END AS is_reset
FROM foo

 id | code | segment | is_reset
----+------+---------+----------
  1 |    0 |       0 |
  2 |    0 |       0 |
  3 |    0 |       0 |
  4 |    0 |       1 |        1
  5 |    0 |       1 |
  6 |    0 |       1 |
  7 |    1 |       1 |        1
  8 |    0 |       1 |        1
  9 |    1 |       2 |        1
 10 |    0 |       2 |        1
 11 |    1 |       2 |        1
 12 |    0 |       2 |        1
 13 |    0 |       2 |
 14 |    0 |       2 |
 15 |    0 |       2 |
 16 |    0 |       3 |        1
(16 rows)

Then we create groups,

  SELECT
    id,
    code,
    segment,
    count(is_reset) OVER (ORDER BY id) AS grp
  FROM (
    SELECT
      id,
      code,
      segment,
      CASE
        WHEN
          code <> lag(code) OVER (ORDER BY id)
          OR segment <> lag(segment) OVER (ORDER BY id)
        THEN 1
      END AS is_reset
    FROM foo
  ) AS t;
 id | code | segment | grp
----+------+---------+-----
  1 |    0 |       0 |   0
  2 |    0 |       0 |   0
  3 |    0 |       0 |   0
  4 |    0 |       1 |   1
  5 |    0 |       1 |   1
  6 |    0 |       1 |   1
  7 |    1 |       1 |   2
  8 |    0 |       1 |   3
  9 |    1 |       2 |   4
 10 |    0 |       2 |   5
 11 |    1 |       2 |   6
 12 |    0 |       2 |   7
 13 |    0 |       2 |   7
 14 |    0 |       2 |   7
 15 |    0 |       2 |   7
 16 |    0 |       3 |   8
(16 rows)

Now you'll notice that the groups a fn(code,segment), reseting when either changes. Now we group by the grp, and do a count(*), but because you want the ids we run them through array_agg and store them as an array.

Note, you say and the sequence of IDs is not broken. I'm working off the assumption that this is not what you meant. PostgreSQL doesn't have a concept of gapless sequences, so that's not a good idea anyway. I assume you wanted what I've done.


Production (20GB+) Database running slowly and timing out I know the title thread is a bit ambiguous (and my first post here!) but I am having a very severe production database problem. I am not a DBA, though I know how to work with queries and SQL Manager. The database is constantly under load, as there's a constant input and output. The problems started about a week ago and I've done everything I know to get it working... from Rebuilding Indexes to Updating Statistics to Shrinking DB. As an example of the recent problem I used to run a daily backup at 5PM and it took 20-25 minutes, now its taking over an hour. Some days the thing will work just fine, for example yesterday we didn't have an issue and today its all over the place. Its a 20+GB Database running in a VM with Windwos Server 2008R2, dual Xeon @ 2.4GHZ and 12GB of RAM plus a partition for OS, one for DB and one for backups. As one of the measures I am taking I created a separate Log partition to move the log from the DB HardDisk to its own separate HDD. Haven't yet done it as I need to detach and take everything offline. I checked the fragmentation for the main table (5+ million entries) and it was over 99%... this was AFTER rebuilding the index.

At this point, I am just stuck. The application users use is timing out, transactions not going in or out (financial institution) sometimes. I am constantly monitoring the performance, and heck even the SQL Server Activity Monitor timed out for me a few moments ago. Any suggestions and what else should I do (aside from separating the log and DB) would be greatly appreciated.

EDIT: Server RAM Usage just in case:
[1]

EDIT2: Code used for rebuilding Index

  ALTER INDEX ALL ON DailyTransactions
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
          STATISTICS_NORECOMPUTE = On);

First you need to figure out what the actual problem is. The sys.dm_os_wait_stats DMV can help with that. Most values in there are accumulative, so you need to capture it a few times  over an extended period to see what is actually going on.

One of the things that this DMV can tell you is if you have RAM pressure. The graphic you posted is basically useless as SQL Server uses all memory it can get (if it needs to). So this is only showing that that mechanism is working.

Once you figured out what your biggest problem is, come back here to get more help.

There are a few wait_types that can be considered noise. You can filter many of those out with this query:

SELECT wait_type,
       wait_time_ms / 1000.0 AS wait_in_sec,
       (wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_wait_sec,
       signal_wait_time_ms / 1000.0 AS signal_sec,
       waiting_tasks_count AS wait_count,
       100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS percentage
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN (
        'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
        'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
        'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
        'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
        'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
        'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
     AND waiting_tasks_count >0
     AND wait_time_ms >0
  ORDER BY wait_time_ms DESC;

(based on http://www.sqlskills.com/blogs/paul/survey-what-is-the-highest-wait-on-your-system/)


oracle-mysql fast refresh materialized view Is is possible to Create Fast refresh enabled Materialized view from MySQL to Oracle .  i know steps for oracle to oracle Fast refresh enabled Materialized view. But now the scenario is need to transfer number of records from MySQL to oracle via db-link with use of materialized view. But every time i need to make Complete Refresh which is time consuming.
so is it possible to create fast refresh materialized view for MySQL to Oracle ?

A fast refresh requires (among many other things) materialized view logs on source tables.

Refresh types

  You can perform fast refreshes of materialized views only when the
  master table or master materialized view has a materialized view log.

That functionality is specific to and implemented in Oracle, and not available in MySQL. It is not like no other database can provide the logically similar functionality, but this is the Oracle implementation, that works with Oracle databases.


Select tree-based structure I have a table in my SQL Server database with data like this:

ID  ParentID
--- ---------
1   NULL
2   1
3   1
5   1
5   4
5   6
8   6
7   9
9   10

I need a select statement that will return all the rows that have some relation between them (no matter parent or child). For example for id = 1 I need to get all the rows except last two. The same result must be for id = 2, 3, 4, 5, 6 ,8. And  for id = 7 or 9 the statement must return only the last two rows. I tried to solve this issue with recursive CTE but I failed.

I'm not great with recursion either, so don't feel bad.  I've had success with structures similar to the following. It's not elegant, but you can see exactly what's going on.  Basically you need to gather all the possible values to evaluate against both ID and ParentID for a given ParentID value. This solution assumes integer values.  It will get a little more tricky with alphanumric values. The nice thing about this is you can run it as is or modify it to display a hierarchical ordering.

    --create the table for the example
    IF OBJECT_ID('tbl_RecursiveExample','U') IS NOT NULL
        DROP TABLE tbl_RecursiveExample

    CREATE TABLE tbl_RecursiveExample
    (
          ID INT
        , ParentID INT
    )

    INSERT tbl_RecursiveExample
    VALUES   (1,NULL)
           , (2,1)
           , (3,1)
           , (5,1)
           , (5,4)
           , (5,6)
           , (8,6)
           , (7,9)
           , (9,10);

    --change the value to the parentID you are interested in
    DECLARE @ParentIDVal INT=10;

    WITH
    cteFirstRelationship --Top level relationships
    AS
    (
        SELECT ID RelatedIDs
        FROM tbl_RecursiveExample
        WHERE ParentID = @ParentIDVal
        UNION
        SELECT ParentID
        FROM tbl_RecursiveExample
        WHERE ID = @ParentIDVal
    ),
    cteSecondRelationship --secondary relationships
    AS
    (
        SELECT ID RelatedIDs
        FROM tbl_RecursiveExample t
            JOIN cteFirstRelationship cf ON cf.RelatedIDs=t.ParentID
        UNION
        SELECT ParentID
        FROM tbl_RecursiveExample t
            JOIN cteFirstRelationship cf ON cf.RelatedIDs=t.ID
    )
    --possible add another level if needed
    SELECT DISTINCT t.* --> Needed due to the match of both values
    FROM tbl_RecursiveExample t
        JOIN
        (
            SELECT RelatedIDs from cteFirstRelationship
            UNION
            SELECT RelatedIDs from cteSecondRelationship
        )r ON r.RelatedIDs = t.ID OR r.RelatedIDs = t.ParentID


Is there a reason to use extremely abbreviated table names? We're using a database setup from a vendor's application that has horrifically hard to read database table names, and no documentation on what is stored where. I can see why one might want to obfuscate their table structure in a proprietary app, but one of the selling points of this application (Enterprise Resource Planning) was it's customizability.

Table names are like aptrx (Accounts Payable Transactions) and apmaster_all (curiously, this is the vendors table). It's an extremely complex database, so I was wondering if there was any logic to the convention or if it was simply being obfuscated intentionally or otherwise.

To the best of my knowledge the length of the table name won't affect performance noticeably, correct? The database is very complex (hundreds of tables) so sorting makes sense, but I can't imagine why AccountsPayableTransactions isn't preferable to aptrx....

Oracle has had a long standing limit on table names of 30 characters.  I suspect this is a legacy issue based off an original 16 bit environment.
The length of a table name could have some minuscule effect on performance as all the names have to be stored in a data dictionary and also parsed for queries but I don't think you could measure the hit.

A more important effect of short table names is that it's hard to work with.  I too have to maintain an enterprise database schema with short names.  There is no good reason to have short table names.  Ease of maintenance trumps obfuscation or old DOS habits every time.


Query Optimizing Request I am very sorry if this question is structured in not a very helpful manner or the question itself is not a very good one!

I need to update a MSSQL table call component every 10 minutes based on information from another table call materials_progress

I have nearly 60000 records in component and more than 10000 records in materials_progress

I wrote an update query to do the job, but it takes longer than 4 minutes to complete execution!
Here is the query :

UPDATE component
SET stage_id =
CASE
    WHEN t.required_quantity <= t.total_received THEN 27
    WHEN t.total_ordered < t.total_received THEN 18
    ELSE 18
END
FROM
    (
    SELECT
        mp.job_id,
        mp.line_no,
        mp.component,
        l.quantity AS line_quantity,
        CASE WHEN mp.component_name_id = 2 THEN l.quantity*2
            ELSE l.quantity
        END AS required_quantity,
        SUM(ordered) AS total_ordered, SUM(received) AS total_received ,
        c.component_id
    FROM line l
    LEFT JOIN component c ON c.line_id = l.line_id
    LEFT JOIN materials_progress mp ON l.job_id = mp.job_id AND l.line_no = mp.line_no AND c.component_name_id = mp.component_name_id
    WHERE
        mp.job_id IS NOT NULL
        AND (mp.cancelled IS NULL OR mp.cancelled = 0)
        AND (mp.manual_override IS NULL OR mp.manual_override = 0)
        AND c.stage_id = 18
    GROUP BY  mp.job_id,  mp.line_no,  mp.component, l.quantity, mp.component_name_id, component_id
    ) AS t

WHERE
    component.component_id = t.component_id

I am not going to explain the scenario as it too complex.. could somebody please please tell me what makes this query this much expensive and a way to get around it?

Thank you very very much in advance!!!

Edit 1:
Here is the execution plan;

I am not sure your logic behind those joins but some part of the query can be optimised as; (for ex; WHEN t.total_ordered < t.total_received THEN 18 of your first CASE does not make sense at all as ELSE part is also equal to18`)

EDIT: Sub query brought back in with some optimisations. Syntax looks alright now.

UPDATE c2 SET stage_id =
         CASE
           WHEN t.required_quantity  <= t.total_received THEN 27
         --WHEN t.total_ordered < t.total_received THEN 18 (NOT NEED THIS BIT)
           ELSE 18
         END

FROM component c2 INNER JOIN
     (SELECT c.component_id,
            CASE mp.component_name_id WHEN 2 THEN l.quantity*2
                 ELSE l.quantity
            END AS required_quantity,
            SUM(received) AS total_received,
                    --(NOT NEED THIS BIT AND OTHER LIST OF COLUMNS)
                    --SUM(ordered) AS total_ordered
     FROM line l
        LEFT JOIN component c ON c.line_id = l.line_id
        LEFT JOIN materials_progress mp ON l.job_id = mp.job_id AND
              l.line_no = mp.line_no AND c.component_name_id =
                                                   mp.component_name_id
     WHERE
            mp.job_id IS NOT NULL
            AND (ISNULL(mp.cancelled,0) = 0) --(OR CONDITION REMOVED)
            AND (ISNULL(mp.manual_override,0) = 0) --(OR CONDITION REMOVED)
            AND c.stage_id = 18
     GROUP BY  mp.job_id,  mp.line_no,  mp.component,
                l.quantity, mp.component_name_id, component_id) AS t

ON c2.component_id = t.component_id --(INNER JOIN)


How can I create a MongoDB based on MySQL schema? How can I create a MongoDB based on MySQL schema?

This is my schema:

productCode VARCHAR(15)
productName VARCHAR(70)
productLine VARCHAR(50)
productScale VARCHAR(10)
productVendor VARCHAR(50)
productDescription TEXT
quantityInStock SMALLINT(6)
buyPrice DOUBLE
MSRP DOUBLE

MongoDB uses flexible schema and you do not have to predeclare your field types in the database. MongoDB (as at 3.0) does not provide any server-side schema validation, so it's perfectly allowable to have a field with different types within the same collection or to have fields vary by document within the same collection.

Schema declaration & validation is optional, but if you want to enforce types you would have to do so via your application logic. There are analogous MongoDB BSON field types for most RDBMS column types, but also obvious differences. For example, MongoDB has a String field which stores variable length text (up to ~16MB) rather than distinct VARCHAR(..) and TEXT types.

In general you will not want to use identical schema when exporting data from a relational database to MongoDB: you would instead consider how best to model your data to support common use cases. For a more complete overview including concepts and examples please refer to the Data Modeling section in the MongoDB manual.

You have many options to migrate data, for example:

using client drivers to read/write from your source & target databases
exporting to a CSV or JSON format which can be read by the mongoimport command line tool
using an ETL tool


SQL Server space in MDF file with multiple files and one Primary file We have space issues in our F: and our H: drive. We have added one more drive - I: drive with 500 GB capacity. Can we add the new database file in the I: drive and set the autogrowth option in the F: drive and the H: drive so that the new data will use the I: drive?
The current database files are as follows:
F:\MSSQL11.MSSQLSERVER\MSSQL\DATA\test.mdf
G:\MSSQL11.MSSQLSERVER\MSSQL\Log\test1_log.ldf
H:\MSSQL11.MSSQLSERVER\MSSQL\Data\test123_1.ndf
H:\MSSQL11.MSSQLSERVER\MSSQL\Data\test_2.ndf

Yes, you can go ahead with that.

Limit the other secondary files (Disable Autogrowth) and create new ndf file in the new drive. This will resolve your space issue.


Oracle custom week of the year numbering based on first Monday of the year I have following sql for generating dates, I wanted to change week of the year numbering based on 'First Monday of the Year'.

Hence numbering would be,
01-05-15 ---> 01
01-12-15 ---> 02
01-19-15 ---> 03
...
...
...

SELECT TO_NUMBER (TO_CHAR (CurrDate, 'YYYYMMDD')) AS date_key,
   CurrDate AS alt_full_date_key,
   TO_NUMBER (TO_CHAR (CurrDate + 1, 'WW')) AS week_num_of_year,
   TO_NUMBER (TO_CHAR (CurrDate + 1, 'IW')) AS week_num_of_year_iso,
   NULL AS week_num_of_year_custom
FROM (    SELECT LEVEL n, TO_DATE ('28/12/2014', 'DD/MM/YYYY') + NUMTODSINTERVAL (LEVEL, 'day') CurrDate
          FROM DUAL
    CONNECT BY LEVEL <= 18000);

ISO Definition is here, You need to check if Jan-01 is Monday, if it is, then you can use ISO week format model 'IW'. If it isn't you can still use that but subtract 1 from that value.

WITH x AS (SELECT to_char(trunc(SYSDATE,'YYYY'),'DY') y_b FROM dual)
,y AS (SELECT to_date('01-05-15','mm-dd-rr') AS d FROM dual UNION ALL
       SELECT to_date('01-12-15','mm-dd-rr') AS d FROM dual UNION ALL
       SELECT to_date('01-19-15','mm-dd-rr') AS d FROM dual)
SELECT y.d your_date
      ,CASE WHEN x.y_b = 'MON'
            THEN to_number(to_char(y.d,'IW'))
            ELSE   to_number(to_char(y.d,'IW'))-1
        END week_num
FROM x,y;

I tested briefly and it looks ok to me, but you should test as well.


Varchar to date in SQL Server 2008 I have varchar data in database table which stores date in DD-MM-YYYY format.

I want to select that varchar in MM-DD-YYYY format.

I'm using

select convert (varchar(30), (select CONVERT(datetime, '16-11-2011', 103)), 101)

It gives 11/16/2011

Any simple query is possible for this?

This is string manipulation: nothing to do with datetimes

SELECT
   SUBSTRING(Whatever, 4,2) + '-' + LEFT(Whatever, 2) + '-' + RIGHT(Whatever, 4)
FROM
   SomeTable;

If you stored it as datetime, then you'd format it into whatever locale you wanted on the client. Then it'd be sortable and comparable too.


sql server stored procedure - collection of data for fields as input I have a stored procedure to add a collection of values to a field in table.
How I implement it now:
from my .net app, I create a string of comma separated values from an array( eg: resource1, recource2, resoource3...). This string is then send to stored procedure, where it is split and each value is inserted into a table.
my code below
ALTER PROCEDURE  [dbo].[resourcetrackerupdate]
@resources varchar(max)

as begin

SET NOCOUNT ON;

DECLARE @MyCursor CURSOR;
DECLARE @ReourceName varchar(max);
BEGIN
    SET @MyCursor = CURSOR FOR
    select [name]  from dbo.splitstring(@resources)

    OPEN @MyCursor
    FETCH NEXT FROM @MyCursor
    INTO @ResourceName

    WHILE @@FETCH_STATUS = 0
    BEGIN

            BEGIN TRY
                INSERT INTO resourcetracker ([ResourceName]) VALUES (@ResourceName)
             END TRY
            BEGIN CATCH

             END CATCH
      FETCH NEXT FROM @MyCursor
      INTO @ResourceName
    END;

    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END;

Now, my requirement is that I need to send multiple field data (as opposed to just one - resourcename - now). So , I will need to send resourcename, resourcesize, assignedto etc to stored procedure to insert into the table as new records.
FYI, these arrays are size of few thousands records.
The long comma separated string approach might not work anymore. How can I change my stored procedure to process all that data?

One of the drive is the source, because that could define the best way.
Now, i will assume there is some internal logic to the procedure.
Possibilities:

send it as json/xml and parse it using MSSQL functions
send it as a table valuable
place it into a temporar table and process it in procedure


How do we handle timestamp formatting errors on large data imports? We have a large database with millions of rows of data from ship AIS pings.

We were recently formatting and importing the data from our holder, ping_raw relation into a ping relation.

During our last go at this our query was stopped: ERROR: invalid value "2.01" for "YYYY", SQL state: 22007. We are trying to import a text string (ping_raw.datetime_pos) into a timestamp with timezone field in ping.dt_pos using the to_datetime() function (in PostgreSQL 9.3).

Last thing - we searched for any values SELECT * FROM ping_raw WHERE datetime_pos LIKE '%2.0%', which returned 0 rows. It seems like all the text values in ping_raw are formatted YYYY-MM-DD HH24:MI:SS.

Why is this error coming up? How can we successfully convert text value ping_raw.datetime_pos into a timestamp with timezone datatype in ping.dt_pos?

We figured it out.

Some of our timestamp text data was imported in scientific notation (i.e. 2.0160924 E+13. This threw an error, as we didn't include the . in the template format in the to_datetime() function.

Our solution was to pull the datetime from another column that contained more or less duplicate data.

Point is - the to_datetime() function in PostgreSQL is very particular and requires data going in to match the format you define. If you're having a similar issue run a few tests to figure out where your data isn't matching up to what the function expects.

We also saw that Postgres MAY support a conversion from scientific notation. We couldn't find anything in 9.3 that did, but it seems like it is on their list of things to implement.


MySQL query duplicates from 2 columns I need to query duplicates in a table from 2 columns (agents_name) and (estates_name) based off the (display_name) column.

I tried the following:

SELECT id, display_name, author_table.agents_name, author_table.estates_name, agents_name, estates_name
FROM author_table

INNER JOIN(
SELECT agents_name and estates_name
FROM author_table
GROUP BY agents_name and estates_name
HAVING COUNT(agents_name) >1 and COUNT(estates_name)>1
)temp ON author_table.agents_name and author_table.estates_name = temp.estates_name;

The last 'sentence' should create a temporary table but I'm getting Error Code: 1054, unknown column temp.estates_name

use aliases for your table especially when you self join

SELECT a1.id, a1.display_name, a1.agents_name, a1.estates_name, t.agents_name, t.estates_name
FROM author_table a1
INNER JOIN(
SELECT agents_name.lestates_name
FROM author_table
GROUP BY agents_name and estates_name
HAVING COUNT(agents_name) >1 AND COUNT(estates_name)>1
)temp t ON a1.agents_name = t.agents_name AND a1.estates_name = t.estates_name;