Application Settings in Database In the past I have stored all generic settings and configuration items such as: custom application title, home address, version, debug, etc.. all in a "settings.php" file that I would include into all of my scripts.

I would assume that it is better practice to have a database table specifically setup for application settings such as these, in that way you dont need to edit a php file to adjust or make changes. Yet I am a bit cautious about how I would set this up.

There are two options I have been considering:

I have a table with one row, many columns, making each column a unique variable value.
I have two columns with the first being the variable name, and the second being the variable's value.

Any suggestions or things I should be aware of from a database design perspective?

Under Option 1, you are guaranteed a single row (if you remember to insert the one row firsthand).

If you want to introduce a new setting, you would have to do ALTER TABLE to add the needed column.
If you wanted to remove an old setting,  you would have to do ALTER TABLE to remove the unneeded column.
You would have to assume to the presence of the column in the code. Otherwise, you may have to query information_schema.columns to make sure the column representing the setting needed exists in the table or not. Querying the table information_schema.columns is extremely bad in the presence of dozens (or even hundreds) of InnoDB tables in a heavy write environment. That could potentially lock information_schema.columns intermittently until it is free to read.
You should make sure the table count is always one(1).

Under Option 2, things are a little different

If you want to introduce a new setting, you would have to do an INSERT to add the needed setting as a row.
If you wanted to remove an old setting, you would have to do a DELETE to remove the needed setting as a row.
You have to query the table by variable name to see if the option is present.
No virtual limit to adding to or deleting a setting from a table

What do these Options have in common ?

If you leave old settings alone, this can allow for old versions of app code to use the old settings, but that just introduces clutter into the table.
Storage Engine should be InnoDB to allow multiple people to read from it.

Personally, I would choose Option 2 because creating new settings, optionally removing old settings, and checking for existing settings would all occur at one table. The method of retrieving settings would all be the same. Under Option 1, you would have to query information_schema.columns plus make certain assumptions about the settings table. The sequence of events for retrieving settings may change from code version to code version.


Postgres cannot simulate table with a view: column must appear in the GROUP BY clause or be used in an aggregate function I am trying to replace a table in our application by using a View. It works well for the most part, but I can't get past this error: "column must appear in the GROUP BY clause or be used in an aggregate function"

Steps to reproduce:

CREATE TABLE example_t (
    did serial PRIMARY KEY,
    a text,
    b text
);
INSERT INTO example_t(a, b) VALUES ('a', 'b');

CREATE VIEW example_t_v AS
    SELECT t.did as did, t.a as a, t.b as b
    FROM example_t t;

Now this query works fine:

SELECT t.a, t.b FROM example_t t GROUP BY (t.did);

But an identical query from the view fails:

SELECT t.a, t.b FROM example_t_v t GROUP BY (t.did);

ERROR:  column "t.a" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1:   SELECT t.a, t.b FROM example_t_v t GROUP BY (t.did);

It would be pretty hard to rewrite the application layer (since Django is generating the queries and it likes to use GROUP BY a lot) so is there a solution on the db layer?

Postgres is smart enough to know in the TABLE that your group-by column is UNIQUE (PRIMARY KEY) ... grouping by a column that is UNIQUE is pointless, so postgres just gives you back what you ask for (column values a and b)..

However, Postgres does NOT know that the group-by column in the VIEW is UNIQUE..  Thus, it's confused by your query (as well as most humans would be confused by your query - including myself).

FYI - your query makes no sense.  Why would you group by a column when you apparently have no interest in performing an aggregate?  What would you expect a and b to give you?


Selecting preferred row in database based on condition I'm new to SQL coding and I'm struggling to find a solution to my issue.

I have a database with contractors that have worked at our organisation on and off.

The database contains the contractors employment number and their current/past contracts.

I need to check where the contractor has more than one row, that if there is a contractor_code = '24', to also check if there is a contractor_code = '60'. If true then select the contractor_code = '60 row and data and give the CASE multiple rows count of '1'.

Sample Data:

SQL Code:

SELECT DISTINCT
       contractor_no,
       CASE WHEN contractor_code = '24' AND contractor_code = '60'
            THEN 1
            END AS multiple_roles
FROM contractor_header ch
LEFT JOIN service_history sh ON sh.contractor_no = ch.contractor_no
ORDER BY multiple_roles DESC

The table gives all contractor_no multiple_roles value of NULL.

I can't eliminate the rows based on if their active or finished as I want to count a contractor even if they're finished. Some columns don't have data in date_commenced or date_finished.

Any suggestions would be great, even if I'm way off track with a Case statement.

If there is no another contractor_code values except 24 and 60, then

SELECT /* ch.contractor_no */ sh.contractor_no ,
       CASE WHEN COUNT(DISTINCT sh.contractor_code) = 2
            THEN 1
            END AS multiple_roles
FROM contractor_header ch
LEFT JOIN service_history sh ON sh.contractor_no = ch.contractor_no
GROUP BY /* ch.contractor_no */ sh.contractor_no
ORDER BY multiple_roles DESC

If there exists another contractor_code values except 24 and 60, then

SELECT /* ch.contractor_no */ sh.contractor_no ,
       CASE WHEN COUNT(DISTINCT CASE WHEN sh.contractor_code IN (24, 60)
                                     THEN sh.contractor_code
                                     END) = 2
            THEN 1
            END AS multiple_roles
FROM contractor_header ch
LEFT JOIN service_history sh ON sh.contractor_no = ch.contractor_no
GROUP BY /* ch.contractor_no */ sh.contractor_no
ORDER BY multiple_roles DESC


Data locking question I'm learning locking basics and I have some doubts about how it works.
I'm using following query to examine transaction locks :

SELECT request_session_id AS spid ,
DB_NAME(resource_database_id) AS dbname ,
CASE WHEN resource_type = 'OBJECT'
THEN OBJECT_NAME(resource_associated_entity_id)
WHEN resource_associated_entity_id = 0 THEN 'n/a'
ELSE OBJECT_NAME(p.object_id)
END AS entity_name ,
index_id ,
resource_type AS resource ,
resource_description AS description ,
request_mode AS mode ,
request_status AS status
FROM sys.dm_tran_locks t
LEFT JOIN sys.partitions p
ON p.partition_id = t.resource_associated_entity_id
WHERE resource_database_id = DB_ID()
AND resource_type <> 'DATABASE' ;

Here i'm opening transaction and running some basic select query :

USE AdventureWorks ;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
BEGIN TRAN
SELECT *
FROM Production.Product
WHERE Name LIKE 'Racing Socks%' ;

I can see following output :

As you can see there exists key locks on clustered and non-clustered indexes

Now i'm trying to create another handmade table

CREATE TABLE [IsolationTest2Test]
(
col1 INT PRIMARY KEY ,
col2 VARCHAR(20)
) ;
GO
INSERT INTO IsolationTest
VALUES ( 10, 'The first row' ) ;
INSERT INTO IsolationTest
VALUES ( 20, 'The second row' ) ;
INSERT INTO IsolationTest
VALUES ( 30, 'The third row' ) ;
INSERT INTO IsolationTest
VALUES ( 40, 'The fourth row' ) ;
INSERT INTO IsolationTest
VALUES ( 50, 'The fifth row' ) ;
GO

So now there exists clustered index with primary key created and additioanly i added non-clustered index on column2

After all of it i'm running  following query :

USE [IsolationDB] ;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
BEGIN TRAN
SELECT *
FROM [dbo].[IsolationTest2Test]
WHERE col2 = 'The third row' ;

Then I examine locking and seeing following:

Question is why there is no key lock on clustred index in second query ? (Select from [IsolationTest2Test] )

Query plan of first statement :

Query plan of second query :

Now i can inderstand that first query that select all columns from the table   acquires S lock on non-clustered index key value and also it  acqiures S lock on clustered index key that has data on the leaf level.

Why second query do not use clustered index key col 1 to perform key lookup? Why it only use non-clustered index seek? Where it takes value for col1?

Question is why there is no key lock on clustered index in second query ?

Because the only other column in that table is the clustered index key.  And a non-clustered index always contains the clustered index key columns.

So your non-clustered index on col2 is a covering index for the SELECT * query.


Could someone help me identify my syntax error, or identify whether this is possible at all? Is it possible to do something like this in SQL with a real table? I know this is possible with a temp table, but cannot get it to work properly with a normal table.

CREATE TABLE Backups
                (
                 DBName SYSNAME,
                 LastFullBackup DATETIME NULL,
                 LastDiffBackup DATETIME NULL,
                 [State] CHAR(1) NULL
                );

        WITH    LastFullBackup
                  AS (SELECT    bs.database_name,
                                MAX(bs.backup_finish_date) AS BackupDate
                      FROM      msdb..backupset bs
                      WHERE     bs.type='D'
                      GROUP BY  bs.database_name),
                LastDiffBackup
                  AS (SELECT    bs.database_name,
                                MAX(bs.backup_finish_date) AS BackupDate
                      FROM      msdb..backupset bs
                      WHERE     bs.type='I'
                      GROUP BY  bs.database_name)

I see a syntax error on the last parenthesis, but that doesn't give me much insight into where the real problem is.

There is nothing wrong with your syntax but:

  A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE
  statement that references some or all the CTE columns. A CTE can also
  be specified in a CREATE VIEW statement as part of the defining SELECT
  statement of the view.

Reference: https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql

As a best practice I will start with a semicolon (;).

As an example following should work.

;WITH lastfullbackup
     AS (SELECT bs.database_name,
                Max(bs.backup_finish_date) AS BackupDate
         FROM   msdb..backupset bs
         WHERE  bs.type = 'D'
         GROUP  BY bs.database_name),
     lastdiffbackup
     AS (SELECT bs.database_name,
                Max(bs.backup_finish_date) AS BackupDate
         FROM   msdb..backupset bs
         WHERE  bs.type = 'I'
         GROUP  BY bs.database_name)
SELECT *
FROM   lastfullbackup
UNION ALL
SELECT *
FROM   lastdiffbackup


I cannot login to a user I just created in a PDB Just installed Oracle 12c client and server for the first time (on same machine) so please bear with me as I'm quite new to this. It's in the multitenant mode.

So I went to the server's BIN directory and ran sqlplus / as sysdba. The SID is TESTDB and looking at show pdbs; I could see that there is one PDB called BPDB1. So I switched to it with alter session set container = BPDB1 and opened it with alter database open. Now I created a user with CREATE USER testtest IDENTIFIED BY password which seemed to work.

After that, I thought I can login to the PDB from the client's BIN directory using sqlplus testtest/password. But that just gives me ORA-01017 invalid username/password; login denied.

I wonder if this is because the client doesn't know which PDB to connect to? How would I tell it this?

By the way, not sure if this is relevant but I tried setting ORACLE_HOME to the root of my client installation, but if that I got some other errors every time I try to start sqlplus. It hinted that ORACLE_HOME was set incorrectly, so I just unset that variable to continue.

You are connecting to root container by using sqlplus testtest/password where the user doesn't exist.

Instead, you can use EZConnect or you can create a TNS name to connect to the PDB.

Example:

EZCONNECT

[oracle@host ~]$ sqlplus testtest@hostname:port/bpdb1

TNS Name

First, find out the service name of the PDB, one of the methods has been shown below.

 [oracle@host ~]$ lsnrctl status listener_name

Service "orclpdb" has 1 instance(s).
  Instance "oracdbuat", status READY, has 1 handler(s) for this service...

In my case, it's orclpdb.
Go to $ORACLE_HOME/network/admin.
Edit/Create tnsnames.ora file to add a similar entry as shown below.

ORCLPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orclpdb)
    )
  )

Now, I can connect to the PDB using the TNS name.

[oracle@host ~]$ sqlplus testtest@orclpdb


Order by varchar mysql Is there anyother alternative for order by varchar clause? At worst case consider I want to order by varchar column that is not indexed. When I tested a query with loads of data it took many times to execute. It is obvious that it will take much time. But is there any other alternative approach for this without indexing that column?

Nope. If you order by a not indexed column then the server HAS to load all the results into memory and can only then start sorting them. That is heavy - not sure how mySql does it, but on SqlServer that will also tax the tempdb for the temporary holding of the data. If it is a lot of data that may be super slow due to memory flowing over onto the hard disc.

Physics and logic can not be cheated, sadly.


Redirect Connections to a SQL Instance I have a LAN in which clients connect to the SQL Server installed on a server with the IP address of 192.168.1.10 (SERVER_A).

I have another SQL Server instance installed on another server with the IP address of 192.168.3.11 (SERVER_B).

The servers are routed to each other so I can connect to one instance by SSMS from the other server.

The problem is I need to connect from the clients in the LAN (192.168.1.0) to the instance installed on SERVER_B.

Is there a way to create an alias instance on SERVER_A which points to the instance installed on SERVER_B? So I can connect to SERVER_A and it redirects me to SERVER_B?

I have an application and I can't change how it talks to the database. So I have to create all the Tables, Views and SPs again on SERVER_A and redirect them to tables, views, and SPs on SERVER_B. I want to use something like aliasing. However the problem is I can connect to the instance locally but not remotely when I use alias instance name.

You can setup a linked server on SERVER_A to SERVER_B and access the databases using four part naming (SERVER_B.database.schema.table). See How to Create a Linked Server in the documentation.

You can also just add a secondary IP address to SERVER_B.

If Server_B is on the same VLAN as Server_A, you can add an alias to its IP address at the client level. But what you want to do is not very feasible.


Using Window Function instead of Select sub-query for counting I have to count the number of Orders a table, per Employee, for two criteria -

"Late Orders".
"Total Orders"

The query includes Orders table and Orders table. They are joined together via the EmployeeID field. The whole schema is found here.

This can be done the following way -

    Select E.EmployeeID
        ,E.LastName
        ,(Select Count(*) from Orders where Orders.EmployeeID = E.EmployeeID) as AllOrders
        ,count(OrderID) LateOrders

from Orders inner join Employees E on E.EmployeeID = Orders.EmployeeID
where CONVERT(date,RequiredDate) <= CONVERT(date,ShippedDate)
group by E.EmployeeID, E.LastName
order by EmployeeID

However I want to replace the following row with a window function -

    ,(Select Count(*) from Orders where Orders.EmployeeID = E.EmployeeID) as AllOrders

What I tried to do didn't work, with the following error -

Count(*) over(Partition By Orders.EmployeeID)

Column 'Orders.EmployeeID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I'd like to practice window functions. Can I use it here?

Here's the output -

I think that this would provide the best performance while still avoiding the sub-select.  I guessed that there was a column called OrderID in the Orders table, may need to adjust.

;WITH CTE_Orders AS
    (
    SELECT O.EmployeeID
        , COUNT(O.OrderID) AS AllOrders
        , SUM(CASE WHEN CONVERT(DATE, O.RequiredDate) <= CONVERT(DATE, O.ShippedDate) THEN 1 ELSE 0 END) AS LateOrders
    FROM dbo.Orders AS O
    GROUP BY O.EmployeeID
    )
SELECT E.EmployeeID
    , E.LastName
    , C.AllOrders
    , C.LateOrders
FROM CTE_Orders AS C
    INNER JOIN dbo.Employees AS E ON E.EmployeeID = C.EmployeeID
ORDER BY C.EmployeeID


Is database record access sequential after retrieving the indexed page from disk? This question was posted on Stack Overflow but left unanswered, I was hoping maybe here I might find an answer.

What I understand about databases that if a queried or requested record is found in the disk page, the whole page is loaded to main memory.

For example, if the content of table stored in disk page like this:

+-------+------------+----------+-----------+--------+
| rowid | EmployeeId | Lastname | Firstname | Salary |
+-------+------------+----------+-----------+--------+
|   001 |         10 | Smith    | Joe       |  40000 |
|   002 |         12 | Jones    | Mary      |  50000 |
|   003 |         11 | Johnson  | Cathy     |   4000 |
|   004 |         22 | Jones    | Bob       |  55000 |
+-------+------------+----------+-----------+--------+

Assume the indexing is done on EmployeeId.

After loading the page in main memory, how the record is searched? (sequential or some other method)

I want to know how this is carried out in Oracle and maybe some other such as Microsoft SQL Server.

In SQL Server, for a regular index (i.e. not columnstore or Hekaton) the storage engine can choose one of two methods to find the start or end point of a search. The first option is a binary search using the offset array at the end of the page. The second option is to use linear interpolation.

After the search start/end point is found, any range-scan portion of the search is essentially sequential. More specifically, the engine saves a 'cookie' to remember the search position.

When the next row is needed by the execution engine, the storage engine validates that the cookie is still valid (i.e. a concurrent change hasn't affected the physical structure) and proceeds to find the next matching record from that point, using the offset table and previous/next page links as necessary, searching either forward or backward. If the cookie is invalid, the engine effectively re-seeks to find the correct current position.

A read-ahead mechanism is used to help ensure pages needed by a range scan are present in memory before the page is needed by the execution engine.


Does blocking always mean open transaction? This may seem a bit dull question but does blocking always mean that there is open transaction and that may cause transaction log grow to infinity because open transaction prevents log truncation (database in SIMPLE mode)?

No.

If you try the following from two different connections then the second one will be blocked by the first (visible in sys.dm_os_waiting_tasks) but neither will result in any transaction log activity and running DBCC OPENTRAN will report "No active open transactions" (assuming no other activity).

SELECT COUNT_BIG(*)
FROM master..spt_values v1 WITH (TABLOCKX),
     master..spt_values v2 WITH (TABLOCKX),
     master..spt_values v3 WITH (TABLOCKX),
     master..spt_values v4 WITH (TABLOCKX)


Get all the data between Characters I am trying to extract all the variable from the following string;
string = 'factor1|factor2|factor3'
This is just a test string. In real string, I do not know the number of factors. What I want is following;

ID  |  Var1 |  Var2 |  Var3 |  VarN  1  Factor1  Factor2 factor3 FactorN

What I have tried is as following but I have to provide everything manually in order to get all the N factors. I want something generic. I have googled a lot and I have searched a lot but couldn't find anything useful.

 select SUBSTRING('factor1|factor2|factor3'
            ,CHARINDEX('|','factor1|factor2|factor3') + 1
                    ,CHARINDEX('|','factor1|factor2|factor3',CHARINDEX('|','factor1|factor2|factor3')

                                        )
                            -1)

Assuming that this question pertains to Microsoft SQL Server:

Identifying the most appropriate solution requires knowing more about:

What is the source of this string
What is the goal of splitting it and returning each value as a separate column?

That being said, the way to accomplish this in T-SQL is to use Dynamic SQL to SELECT a series of literal values that are the result of the input string being split. For example:

DECLARE @String NVARCHAR(MAX) = N'factor1|factor2|factor3';

DECLARE @SQL NVARCHAR(MAX) = 'SELECT ';

SELECT @SQL += N''''
             + factors.SplitVal
             + N''' AS [Var'
             + CONVERT(NVARCHAR(50), factors.SplitNum)
             + N'], '
FROM   SQL#.String_Split(@String, N'|', 1) factors;

SET @SQL = STUFF(@SQL, LEN(@SQL), 1, N';');

--PRINT @SQL;

EXEC (@SQL);

You can use whatever string splitter you prefer, though SQLCLR-based splitters are the best choice. The one I used is from the SQL# library of SQLCLR functions (which I wrote, but String_Split is in the Free version).

This can easily be wrapped in a stored procedure as they can dynamically create their result set structure at runtime. On the other hand, it cannot be used in a TVF (T-SQL or SQLCLR) as those are required to have static result set structures.

Also, this code just returns a single row. The sample result set in the Question has an ID field which implies multiple rows. It is not clear, at this point, how that would even be passed in. But, if there are multiple rows then this gets a little more complicated:

The code presented here can be adapted to return additional rows of static values, combined into a single result set via UNION ALL between them
The code presented here can be adapted to instead dynamically create a local temp table containing the appropriate number of fields, then for each row, create an INSERT statement instead of the current SELECT to populate the temp table, and finally do a SELECT * FROM #tmp;. All of this would be done within the Dynamic SQL, even the final SELECT, so that the temp table can be kept as a local temp table, thereby allowing multiple processes to run at the same time (which would not work if using a global temp table).
Depending on where the input data is coming from and how it is being used, it is possible that a Table-Valued Parameter (TVP) would be best.
If this data already exists, as shown, in a table, then SQLCLR is an option as a stored procedure can be created to split a string into any number of fields and return each row as it is read from the table (i.e. streaming the data back). This is more efficient than T-SQL which needs to place all rows either in memory (in the Dynamic SQL local variable) or on disk (in a temp table). There is a stored procedure in the SQL# library (noted above) called String_SplitResultIntoFields that handles this particular scenario quite nicely and efficiently. This proc, however, is only available in the Full version (i.e. not free), but the benefit is that it already exists.


How to satisfy 2NF and what are superkeys? - Understanding the algorithm for database normalization in general Given the table:

Student_subject(studentid, subjectid, username, grade)

Primary key consisting of a combination of:

studentid, subjectid

Candidate key consisting of a combination of:

subjectcode, username

Now it can be saide the following functional dependencies exist:

studentid, subjectid -> grade (BCNF)
username, subjectid -> grade (BCNF)
studentid -> username (3NF)
username -> studentid (3NF)

BCNF - Boyce–Codd normal form (3.5NF)

So the NF of the table will equal the lowest NF found among all FDs, right?
There seems to be some different explanations around for how to find the NF levels.

As I've seen from one explanation on how to satisfy 2NF, it is so that all data/columns need to depend on the whole/entire primary key, but I can't see that is the case (since the primary key is defined as the combination of studentid, subjectid) with

studentid -> username (3NF)
username -> studentid (3NF)

And if it doesn't fulfill 2NF, it can't satisfy a higher level 3NF. isn't it so that you start to see if 1NF is satisfied, and if it is you check if 2NF is as well and so on...

Also superkeys are anything that identifies a row uniquely, incl. candidate keys, primary key and all possible combinations of these with the rest of the attributes?

to satisfy 2NF, it is so that all data/columns need to depend on the
  whole/entire primary key

That's incorrect. A relation R is in second normal if every non-prime attribute of R is fully dependent on each candidate key of R. In the case of studentid -> username and username -> studentid both username and studentid are prime attributes (attributes that are included in a candidate key). So those dependencies are not a violation of 2NF and Student_subject does satisfy 2NF with respect to those dependencies. As already pointed out, Normal Forms apply to relations, not to FDs.


Updating a WHERE clause to check if a value is NOT in a separate table I have a query which uses a WHERE clause, and I happen to use the exact same WHERE clause in many queries on this table (et al).

The query is:

SELECT
    DATENAME(DW, [AtDateTime]) AS [Day of Week]
    ,COUNT(*) AS [Number of Searches]
    ,CAST(CAST(COUNT(*) AS DECIMAL(10, 2))
         / COUNT(DISTINCT CONVERT(DATE, [AtDateTime])) AS DECIMAL(10, 2))
       AS [Average Searches per Day]
    ,SUM(CASE WHEN [NumFound] = 0 THEN 1 ELSE 0 END)
       AS [Number of Searches with no Results]
    ,CAST(CAST(SUM(CASE WHEN [NumFound] = 0 THEN 1 ELSE 0 END)
         AS DECIMAL(10, 2)) / COUNT(*) AS DECIMAL(10, 4))
       AS [Percent of Searches with no Results]
FROM [DB].[dbo].[SearchHistory]
WHERE
    [CustomerNumber] <> '1234' AND [CustomerNumber] <> '5678'
GROUP BY DATENAME(DW, [AtDateTime]), DATEPART(DW, [AtDateTime])
ORDER BY DATEPART(DW, [AtDateTime])

The part I wish to change is the WHERE clause, to instead allow me to use a table so that if I have to add a customer number to be ignored, I don't have to update all my queries. (And there are quite a few queries that have this same WHERE clause.)

CREATE TABLE dbo.CustomerExclusions
(
  CustomerNumber VARCHAR(32) PRIMARY KEY -- Is CustomerNumber *really* a string?
);

INSERT dbo.CustomerExclusions(CustomerNumber) VALUES('1234'),('5678');

Now your WHERE clause across all queries becomes:

WHERE NOT EXISTS
(
  SELECT 1 FROM dbo.CustomerExclusions AS c
  WHERE c.CustomerNumber = SearchHistory.CustomerNumber
)


How to benchmark Switchable Optimizations in MySQL I want to benchmark the performance of Switchable Optimizations in MySQL, i.e., comparing the query latency and the result of EXPLAIN with/without using these optimization switches.
I have already tried to use sysbench and MySQL official test-suite. But I fond that they are too general and mainly for functional testing, i.e., runing these tests with/without the optimization switches, the performance are almost identical. What I want is sth like a performance test suite.
Could you please suggest me some good tools/suites to fulfill my demands?
Thanks!

(I realize that this does not "answer" the Question.  But it is too long for a Comment.)
Good luck.  re "Almost identical" -- Probably because they were "identical" except for random noise in the timing.  The switchable optimizations are, in general, "binary" and "optional".
Consider an obvious situation:  You have an index that could be used in a query.  Still, the Optimizer will deliberately consider whether to ignore the index and simply scan the table.  It may be more costly to bounce between the index and the table, hence using the index would slow down execution.  This is a "binary" decision -- use vs ignore index.  The Optimizer cannot know the exact cutoff between the choices.  It is influenced by HDD vs SDD, buffer_pool_size, other activity, statistics, etc, etc.
That is, a benchmark would be complicated by having more than 2 choices (an index exists or not):

FORCE INDEX vs IGNORE INDEX vs 'allowed' the Optimizer to choose
for "allowed" the Optimizer picks the better choice vs fails to

Any of these could change whether the Optimizer would pick a particular optimization.  Also, the timings would change by a relatively unpredictable amount in cases such as these:

Adding more rows to a table
Changing the size of a 'range'
ANALYZE TABLE will sometimes lead to changing things significantly.
OPTIMIZE TABLE -- ditto.

Over the years, I occasionally see "index merge intersect".  (INDEX(a), INDEX(b) with WHERE a=1 AND b=2)  In every case, a composite index would be better (INDEX(a,b)).
Change that AND to OR and no index is useful -- except when "index merge union" is used.  This almost never happens.  It takes two index range scans, a merge, and then something like a "join".
EXPLAIN does not show much info.  EXPLAIN FORMAT=JSON is better.  The Optimizer Trace shows a different set of info.
I don't know of a test set like what you seek.  I see the writing of even one test to be quite a challenge (see above).  Furthermore, interpreting the results would be challenging.
If you do succeed with your goal, please blog about it.


GROUP BY performance I have a query like this:

SELECT HOUR(datetime) as Hour, COUNT(*) as hits, SUM(clicks)
FROM visitors_log_table
WHERE datetime >= CURDATE()
GROUP BY Hour
WITH ROLLUP

It works fine. But I don't need  GROUP BY for the clicks column.
I only need the WITH ROLLUP result for it.

This query is just an example. In my query I have many more columns like the clicks column where I don't need a GROUP BY.

My table is very, very large, so performance is important. The datetime column is indexed.
So should I do a seperate query, or should I leave it like that, or...?

Take a look at my answer here - you could take a similar query and use MySQL events (see here) to add rows to an aggregation table on an hourly basis - say at 5 minutes past the hour, perform a query for the previous hour's clicks and hits - that way you'd only be querying an hour at a time and I presume that the performance would be OK for a small subset of your results. Since it's logging you're doing, any logged data isn't going to change, so querying 1 hour at a time makes sense - i.e. (pseudocode)

SELECT HOUR<my_time_field> AS Hour, <my_aggregates>
FROM <my_table>
WHERE MyTime
BETWEEN (Now - 65mins) AND (Now-5mins);
GROUP BY Hour

Something like that might be a solution to your issue?


Reporting on a SQL DB Server with many write operations and few read operations I know this is a generic question, but I have to clarify some basic questions.
I know that when a write is happening to a SQL Server database table, a read to that table will have to wait.
My question here is: the SQL database server I am working on is being exposed to many writes and a few reads. And a few SSRS reports are also being run on the same server.

What is the ideal or recommended way to accommodate this?

I know that it is always best to perform reporting from a duplicated instance of the same database. But for a few reports, is it worth to set up a duplicate instance for reporting?

I get complaints sometimes that some reports are running slow.

Can it be the read wait while a write happens?

I know that when a write is happening to a SQL Server database table, a read to that table will have to wait.

This isn't necessarily true. Proper indexing can help mitigate blocking waits by giving queries adequate paths to data, and helping modification queries find the data they're going to be modifying.

  I know that it is always best to perform reporting from a duplicated instance of the same database. But for a few reports, is it worth to set up a duplicate instance for reporting?

Potentially, because:

  I get complaints sometimes that some reports are running slow.

Finally:

  Can it be the read wait while a write happens?

Take a look at SNAPSHOT ISOLATION. If the reports are slow because of blocking, this can help. If the reports are slow for other reasons, at least you'll know pretty quickly. Heh.


MADlib apply `cosine_similarity` on term frequency I am working on a small topic modelling project and I have stumbled upon the idea of developing it in MADlib and PostgreSQL.

MADlib provides this term frequency function that converts my documents into, well term frequencies.

Are there any other utility functions that can help me to perform madlib.cosine_similarity or madlib.dot_product
 on the generated term frequencies which is essentially stored as sparse matrices (which is not a supported format for the cosine_similarity function that I needed)?

I may be wrong, but I don't think that Postgres has built-in TF-IDF functionality. I'd be super happy to learn that I'm wrong. Obviously, you can parse and roll your own counts.

In case you don't already know, there's a package that may be of interest to you called pg_similarity, here's one link:

https://salsa.debian.org/postgresql/pg-similarity

It's got a lot of measures. Many, including cosine similarity, can read data off of an index. The pg_similarity extension doesn't help you with storing or processing your corpus, but at least you get the math in a C function.

In a rare win for RDS, they've got this extension compiled and available. Otherwise, you need your own compile.

Here's hoping that you get a better informed answer...I'm just poking at the Postgres implementations of some of these tools myself.


Backup and restore of TDE Databases to different server There seems to be a lot of information on backing up and restoring TDE databases, but it's generally all the same information, and there seems to be some contradiction about certain points.  I have some specific questions I hope someone can answer.

Do I have to backup the Database Master Key and restore it?  Or is it enough to recreate it with the same password?
Similarly to #1, do I need to backup and restore the Service Master Key?
If I've backed up the TDE encryption certificate and cert key, can I restore it to a different database that has a different DMK (and SMK) without losing data?
If #3 is yes, then what is the purpose of backing up the DMK and SMK if you can just generate new ones when restoring the Cert?

You need to refer to architecture of TDE encryption

  Do I have to backup the Database Master Key and restore it? Or is it enough to recreate it with the same password?

Yes and NO. Always backup your DMK !

Yes

  The database master key is used to encrypt other keys and certificates inside a database. If it is deleted or corrupted, SQL Server may be unable to decrypt those keys, and the data encrypted using them will be effectively lost. For this reason, you should back up the database master key and store the backup in a secure off-site location.

Yes - when you have to restore the database during a DR scenario (rebuild the server).

No - when you dont have the cert Private key password and want to restore the cert to different server, you can simply backup the cert with a new password and restore on the different server.

Remember that - A DMK does not encrypt the database. It is used to encrypt the certs and other keys to keep them protected.

  Similarly to #1, do I need to backup and restore the Service Master Key?

A rare scenario, but its good to have it backed up as part of best practice. A SMK (##MS_ServiceMasterKey##) is the root of encryption and is generated automatically the first time it is needed to encrypt another key. If SMK is corrupted, then you might need to restore it.

For 3 and 4 ..

You can, but why would you do it ?

You have to turn off encryption, create a new cert with new Private key and then turn on encryption. Depending on your database size, this can take some time.

Remember that a DEK is the one that does the encryption and decryption of the database.

As a side note, refer to this script for info on the databases, encryption status along with other useful information.


Create table if not exist each time API called: bad practice? I'm writing a simple API in PHP with MySQL as database for a web application. I intend to put CREATE TABLE IF NOT EXIST for all tables in each API call.

I expect that I will have 5-6 tables.

I'm trying to do so for a simple reason: I will not need any extra script to run for initialization of the application - it will do it automatically on the first run.

As a drawback I expect that this can slow down API operation. However I tried to log API calls and it is still 2-3 milliseconds (in my case I did not notice any difference if there is CREATE TABLE or not).

Any thoughts?

So let's think forward a little. The application goes live, the tables are created and everything's lovely. Then, you have a new requirement and the tables must change. How do you code that? There's the CREATEs for environments where the code's never run, plus a bunch of ALTERs for existing environments. Now another requirement comes along. This time you have to migrate data for some reason or other. Now there are the CREATEs (for new environments), the ALTERs (for existing environments) and a whole bunch of DDL and migration logic. But wait - there's more. For really good business reasons you want the column holding the migrated data to have the same name as an existing column. How do you now tell if the database has been migrated or not? No longer do you have a metadata-only lookup. No, now you need a flag or version table to show what DDL has to run at each and every execution.

Yeah! You're a great success. Zillions of people want to use your service. Venture capitalists wheeling barrows of money to your door. Third-party organisations are begging to partner with you. The dev team's doubled (nay, trebled!) in size to handle the business opportunities and customisations. Oops. These all have to be coded in the API start-up code. Each. And. Every. One. You no longer have an API. You have the mother of all migration scripts with a fragment of business code tacked on. :sad-face:


How "LIKE" by LONG VARCHAR? I had DB Vertica 8.0.
Create schema WAREHOUSE with table include field by type LONG VARCHAR.
Now I tried to execude SELECT for example

SELECT * FROM WAREHOUSE.ALL_EVENTS a
WHERE
a.original_data like '%d963%'

returned error

SQL Error [4286] [42883]: [Vertica][VJDBC](4286) ERROR: Operator does not exist: long varchar ~~ unknown
  [Vertica][VJDBC](4286) ERROR: Operator does not exist: long varchar ~~ unknown
    com.vertica.util.ServerException: [Vertica][VJDBC](4286) ERROR: Operator does not exist: long varchar ~~ unknown

In Oracle I used dbms_lob package for CLOB fields.

Vertica have simular package for LONG VARCHAR types?

How "LIKE" by LONG VARCHAR?

Vertica documentation about LONG datatypes, in either version 7.1 or 8.0 states:

Notes
For optimal performance of LONG data types, HP Vertica recommends that you:

Use the LONG data types as storage only containers; HP Vertica does not support operations on their content.
Use the VARBINARY and VARCHAR data types, instead of their LONG counterparts, whenever possible. The VARBINARY and VARCHAR data types are more flexible and have a wider range of operations.

VARCHAR datatype supports up to 65K bytes long strings. You could use that instead, if the data are not longer.


ibm_db on HADR primary database? Yesterday I discovered a peculiar thing, it looks like a bug but I'll post it here in case someone seen it before. Testscript:

#!/usr/bin/python

import sys
import getopt
import re
import os
import traceback

import ibm_db

"""
Test of ibm_db in a HADR env
"""

def f1(dbname, username, pwd):
    print "start"
    cfg = (dbname, username, pwd)
    conn_str = "DATABASE=%s;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=%s;PWD=%s" % cfg
    conn1 = ibm_db.connect(conn_str, "", "")
    print "connect done"
    ibm_db.close(conn1)
    conn1 = None
    print "end"

def main():
    dbname = None
    username = None
    pwd = None
    try:
        opts, args = getopt.getopt(sys.argv[1:], "d:u:p:")
    except getopt.GetoptError:
        usage()
    for o, a in opts:
            if (o == "-d"):
                dbname = a
            if (o == "-u"):
                username = a
            if (o == "-p"):
                pwd = a

    f1(dbname, username, pwd)
    f1(dbname, username, pwd)

if __name__ == "__main__":
    try:
        main()
        print('')
    except Exception, e:
        print e
        sys.exit(1)
    sys.exit(0)

Running the script on a standalone database works as expected:

./test_hadr.py -d db1 -u user -p pwd
start
connect done
end
start
connect done
end

Running the script against a HADR PRIMARY hangs on second connection

./test_hadr.py -d db1 -u user -p pwd
start
connect done
end
start
# waits for a couple of minutes

I straced it and it appears to connect to failover on the second attempt, and after a couple of minutes:

write(1, "[IBM][CLI Driver] SQL1776N  The "..., 111[IBM][CLI Driver] SQL1776N  The command cannot be issued on an HADR database. Reason code = "1". SQLCODE=-1776
) = 111

Any clues why this happens, and how it can be avoided? Using the same connection several times appears to be working, but using two different connection hangs.

Looks like, when the alternate server is configured, the CLI driver prefers its proper hostname over "localhost". If you specify the machine hostname or IP address in the connection string instead of "localhost" the script works as expected.

The test script can be modified as:

[...]
import traceback
import socket
[...]

def f1(dbname, username, pwd):
    print "start"
    ip = socket.gethostbyname(socket.gethostname())
    cfg = (dbname, ip, username, pwd)
    conn_str = "DATABASE=%s;HOSTNAME=%s;PORT=50000;PROTOCOL=TCPIP;UID=%s;PWD=%s" % cfg
    [...]

It is recommended to explicitly bind Db2 to an IP address via nicbinding.cfg, in which case "localhost" wouldn't work anyway.


Who or what initiated the failover We have our clusters set to High Availability and manual failover. I have an alert to email DBAs whenever a failover occurs. What we would like to also see in the email is WHY and/or WHO. We have other teams in there that restart, test and delete things all the time and we would like to know if this is someone working on something vs something wrong. Right now the email is "The availability group database "TESTIN2" is changing roles from "PRIMARY" to "RESOLVING" because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required."
Thanks

What we would like to also see in the email is WHY and/or WHO.

Then you have quite a good bit of development work ahead of you. Seriously.

Everyone asks this same question, but it is not an easy one to answer with any more resolution than "It failed over at this time." Things such as virtualization make this even harder as the tools and the hypervisor itself may do things that are outside the purview of your sandbox.

However, to get you started, places to scrub for data:

SQL Server Errorlog
Application Event Log
System Event Log
Cluster Log/Clustering Event Log
Hypervisor Logs/Database
Internal Lists of Downtimes/Upgrades/Patches/Etc.

Best of luck.

  We have other teams in there that restart, test and delete things all the time and we would like to know if this is someone working on something vs something wrong.

That's an organizational information sharing/Administration issue and not one you're going to have much help in dealing with through a simple SQL Server Alert. In fact, to do this properly, you'll want to take SQL Server out of the picture!

Most likely you're going to need to write a service that constantly consumes event logs, traces, error logs, etc., and then takes action when issues occur.


Oracle installation requires old rpm versions, what to do? I am installing Oracle 11g R2 on OEL6.

I run the following command, for to check only prerequisites

./runInstaller -silent -responseFile /0/grid/response/crs_install_mk.rsp -executePrereqs

It generated a log that contains the following info:

INFO: Package: libaio-0.3.105: This is a prerequisite condition to test whether the package "libaio-0.3.105" is available on the system.
INFO: Severity:IGNORABLE
INFO: OverallStatus:VERIFICATION_FAILED
INFO: -----------------------------------------------
INFO: Verification Result for Node:testr1n1
INFO: Expected Value:libaio-0.3.105 (i386)
INFO: Actual Value:missing
INFO: Error Message:PRVF-7532 : Package "libaio-0.3.105 (i386)" is missing on node "testr1n1"
INFO: Cause: A required package is either not installed or, if the package is a kernel module, is not loaded on the specified node.
INFO: Action: Ensure that the required package is installed and available.

Whan I check this package:

rpm -qa|grep libaio

libaio-0.3.107-10.el6.x86_64
libaio-devel-0.3.107-10.el6.x86_64
libaio-devel-0.3.107-10.el6.i686
libaio-0.3.107-10.el6.i686

And when I try to install

rpm -Uvh libaio-0.3.105-2.i386.rpm

warning: libaio-0.3.105-2.i386.rpm: Header V3 DSA/SHA1 Signature, key ID 73307de6: NOKEY
Preparing...                ########################################### [100%]
        package libaio-0.3.107-10.el6.x86_64 (which is newer than libaio-0.3.105-2.i386) is already installed
        package libaio-0.3.107-10.el6.i686 (which is newer than libaio-0.3.105-2.i386) is already installed

What should I do now? I have newer version than Oracle requires. Ignore it?


How to validate DB Users/logins across Availability Group by script How to validate DB users in AG Groups across nodes by script.
Please help me, any one.

Thanks in advance

You can use this addin (AG Addin) for SMSS, it copies:

User-defined Server Roles
Server Logins and Permissions
Linked Servers
SQL Server Agent Operators
SQL Server Agent Alerts
SQL Server Agent Jobs


How to Correctly Estimate the Average Number of I/O Operations my RAID 10 disk can offer? The average Input/Output Operations per Second a disk can sustain is a very important factor in the tuning of MySQL / InnoDB databases, as it drives the speed with which Dirty InnoDB Buffer Pool pages are flushed back to the disk.

Is there a method or a formula I can use to estimate the average I/O Operations per Second my RAID 10 disk array can run on?

tl;dr Yes you can do a rough and ready calculation but benchmarking is advisable if you need accuracy. There are many factors beyond raw disk performance that can influence IOPs, in particular the RAID controller.

If you have the technical specifications for the disk, the quick calculation to determine IOPs is: 1 / (average latency + average seek time) = random IOPs.

The contributory factors:

Average latency is the time taken for the disk sector requested to rotate under the drive head.
Average seek time is how long it takes the drive head to locate itself over the disk track requested.
Rotational speed (quoted in revolutions per minute) isn't used directly in the calculation but affects the above. It is also the headline figure quoted in any drive specification.

While there is variation across drive models, you can apply a rough and ready figure to each of the common rotational speeds.

7200 RPM ~75 IOPs
10K RPM ~125 IOPs
15K RPM ~175 IOPs

With the majority of enterprise class RAID controllers, your read performance for common RAID levels (1/10/5/6) will broadly be in line with IOPs * number of disks. Cheaper controllers may impose a penalty. Write performance however is significantly effected by the chosen level:

RAID 1 & 10 = IOPs / 2
RAID 5 = IOPs / 4
RAID 6 = IOPs / 6

For example, a RAID 10 array of 10 15k disks equates to:

(175 * 10) = 1750 IOPs read
(175 * 10) / 2 = 875 IOPs write

Of course, most workloads are a mix of read and write so its necessary to consider the r/w ratio to determine the capacity needed.


How to Pivot Multiple Columns in SQL Server I've read many posts about using pivot to get the data in the format I want, but the more I read the more confused I get.

I have this data:

That I'm trying to get into a format similar to this:

For the most part, everything I try results in an SQL error, and the only successful attempt I've had didn't return the data in the format I'm looking for.

Any help would be appreciated.

Something like:

select hour_of_day,
       avg( case when day_of_week = 2 then item_count else null end ) Mondays,
       avg( case when day_of_week = 3 then item_count else null end ) Tuesdays,
       avg( case when day_of_week = 4 then item_count else null end ) Wednesdays,
       avg( case when day_of_week = 5 then item_count else null end ) Thursdays,
       avg( case when day_of_week = 6 then item_count else null end ) Fridays,
       avg( case when day_of_week = 7 then item_count else null end ) Saturdays,
       avg( case when day_of_week = 1 then item_count else null end ) Sundays
where ...
group by hour_of_day


Exporting/Importing data from SQL Server 2014 to PostgreSQL 9.5 with tab delimited files Not sure if anyone has run into this problem before and I am going a little bit crazy.

I am exporting a table from SQL Server 2014 using BCP to a tab delimited text file and then uploading the file to PostgreSQL 9.5 using COPY.

exec master..xp_cmdshell 'bcp tempdb.dbo.##MyTable out "C:\BCPFiles\MyTableFile.txt" -c -t -T'

This works fine.

create temp table mytable (col1 int, col2 varchar(100), col3 varchar(100));
COPY mytable FROM '/tmp/MyTableFile.txt' WITH (FORMAT text);

ERROR:  invalid input syntax for integer: "1    text1      text2"

It looks to me like COPY doesn't recognize the tab, even though when I view the file it is tab delimited. There are no NULL values in this file btw.

CSV is not a good option for me because of the text has commas in it and from what I am reading PG doesn't like double quotes

(CSV is not a good option for me because of the text has commas in it and from what I am reading PG doesn't like double quotes)

Sure Pg accepts double quotes. See COPY

  QUOTE Specifies the quoting character to be used when a data value is quoted. The default is double-quote. This must be a single one-byte character. This option is allowed only when using CSV format.

You don't even have to do anything..

COPY foo FROM 'asdf.csv' WITH (FORMAT CSV);

  It looks to me like COPY doesn't recognize the tab, even though when I view the file it is tab delimited. There are no NULL values in this file btw.

If you want to use tabs, just set that up.

COPY foo FROM 'asdf.csv' WITH (FORMAT CSV, DELIMITER E'\t');


What changes SQL Server Compatibility Level changes? We had a database and lots of functionality that worked fine in SQL Server 2008 R2.  We recently upgraded both our development and production servers to SQL Server 2014.  Unbeknownst to us, our development server stayed in compatibility mode for 2008 R2, but the Prod box is in Compatibility Mode for 2014.

My question, is where can I find what changed between SQL/Compatibility versions so we will know what needs fixing to maintain functionality.  I know I can set (or have set) the Prod server to compatibility with 2008 but since I don't administer the prod box, I cannot make that call.  My google-fu seems to keep turning up tutorials on how to change the compatibility level, but nothing on what actually changed so I can't tune our queries/procs/functions/etc to work well with the new version.

A compatibility upgrade changes the way the SQL Engine works and has the database level improvements/changes from the other versions.  This doesn't mean you can't use features outside of that though.  For example having a DB in a older compatibilty mode doesn't mean you can't use mirroring or AGs.

It does mean the query optimizer, newer system provided functions, and other features will not work the same way or be available in some cases.  You will want to ensure your code operates the same way but your DR/HA and operational solutions shouldn't have changed.

Run your code through the Upgrade Advisor and also read up on the breaking changes.  Read up on best practices for upgrades as well, Thomas LaRock has a good article on it as an example.


How do I by pass startup code in access I am struggling to by pass my startup code in MS Access using the SHIFT + ENTER keys. How else can I accomplish this?

Hold down the SHIFT key while opening your database.

-> Depending on the macro security settings for your database, you might see one or more security messages on startup. You must hold down the SHIFT key until you close the security messages, or the startup options will not be bypassed.


Storing historical data in DB vs on disk I've been pondering on alternative solutions to speed up data retrieval from my DB.
I currently have a relatively large database, about 1 billion rows and growing. We expect the DB to grow linearly with about 1.5 billion rows (300GB) per years. My DB is PostGreSQL on FreeBSD.

The database contains history sensor data which simply consists of a timestamp, a numeric value and 1 foreign key to a sensor. The data will never be updated, only written and is not accessed very often.

With this amount of data is it's not possible to keep everything in memory, but I still want fast data retrieval if possible. We're currently lookup at almost 6ms per table row lookup time because it's so far spread out over the harddisk that every lookup has maximum seek time.

I've been doing some small scale tests with storing data on disk and this seem a LOT faster. We're looking at 1.8s for a day from the DB versus 0.2ms retrieving it from disk (including value conversions). This is almost 10.000x faster.

Which leads to my question: Is there any particular reason not to store the history data as plain files on disk if I don't care about foreign key consistency?

I think you've answered your own question.

If there's no need for foreign key consistency and you are only ever reading back this data then the difference in search times makes a compelling argument for storing the history data as plain text rather than in a database.


Advanced column in MS-BIDS Because we are having the same problems as in http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/e51b775a-8601-491b-8ad4-a71b0f0d59ce we have the following question:
That is when inserting from a flat file source we wmiis the last row from the file.

Can you show us where we can find the advanvced column view to set the text qualification to false? Thanks in advance.

That is an option of the "flat file connection" editor. Its on "Advanced->TextQualified". Here is an image of it:


SQL Server experiencing THREADPOOL waits while still having plenty of available workers I recently took a look at my production SQL Server's cumulative threadpool waits, and it's literally days worth of waits. So I started doing some digging.
I'm on a 32 core, 64 bit server (running on EC2). Max workers is correctly set to 0 in config, and the following DMV reports the correct, expected number:
SELECT max_workers_count FROM sys.dm_os_sys_info

Output: 960

Looking at my total number of workers, I'm rarely seeing any over 300-400:
SELECT count(*) FROM sys.dm_os_workers

Output: 372

And yet, if I run the following DMV, I always have threads listed:
SELECT * FROM sys.dm_os_waiting_tasks
WHERE wait_type = 'THREADPOOL'

SELECT dow.state , dow.is_preemptive , dow.is_sick , dow.is_in_polling_io_completion_routine , [Num Workers] = COUNT(1)
FROM sys.dm_os_workers dow
GROUP BY dow.state , dow.is_preemptive , dow.is_sick , dow.is_in_polling_io_completion_routine;

The waits in milliseconds are usually pretty short, 20-200 ms generally, and they disappear quickly as well, but they are adding to the overall cumulative threadpool figure. They also never have blocking sessions.
I am stumped as to why anything is encountering a threadpool wait when I have so many available workers. Shouldn't the hundreds of available workers be handling these requests instantly without any threads going to threadpool?
I would appreciate any input or direction here.
SQL Server Version: SQL Server 2016 (SP2-CU8) (KB4505830) - 13.0.5426.0 (X64) Enterprise Edition: (64-bit) on Windows Server 2016 Datacenter 10.0  (Build 14393: ) (Hypervisor)
Cost Threshold for Parallelism is set to 1400.
Hyperthreading is not enabled.
Maximum Degree of Parallelism is set to 8.

If you have the max degree of parallelism set to zero, which is the default, then queries that might benefit from parallelism will consume 32 threads.  With the number of workers set to 960, again the default for your setup, you could only run 30 simultaneous parallel queries.

I'd recommend setting max degree of parallelism to something sane, like 8.


How to reclaim database space on Azure SQL Server after setting nvarchar values to null There is db space challenge at hand on Azure SQL Server database. 490 GB out of 500 GB is used. That's what I read from nice pie chart on Azure portal.
Notice that some of the techniques used on-prem won't work for Azure SQL Server Database.

Biggest table is DidbStudySerumLevelDeviation.

exec sp_spaceused 'RulesEngine.DidbStudySerumLevelDeviation '

returns

+------------+------------------------------+
| name       | DidbStudySerumLevelDeviation |
| rows       | 20 805 409                   |
| reserved   | 133 332 840 KB               |
| data       | 123 079 792 KB               |
| index_size | 9 772 192 KB                 |
| unused     | 480 856 KB                   |
+------------+------------------------------+

Table definition:

CREATE TABLE [RulesEngine].[DidbStudySerumLevelDeviation](
    [Id] [uniqueidentifier] NOT NULL,
    [PatientTherapyId] [uniqueidentifier] NOT NULL,
    ...
    [Abstract] [nvarchar](max) NULL,
    ...
 CONSTRAINT [PK_RulesEngine.DidbStudySerumLevelDeviation] PRIMARY KEY CLUSTERED
(
    [Id] ASC,
    [PatientTherapyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

Clustered index exists on primary key. There are also some nonclustered indexes.
Abstract column data takes around 90% of space of a table.

Procedure is run to set unnecessary Abstract column data to null. That should reduce problematic column "weight" to 10%. However this doesn't reduce database size. How to reclaim that database space?

Maybe I should wait and some Azure SLQ maintenance magic occurs? It sounds like wishfull thinking though.

Regarding possible duplicate

Answer to this question might me specific to Azure deployment of SQL Server. Drop in database size reported in Azure portal or read from exec sp_spaceused didn't change.

Please defrag all indexes. As mentioned on this article, fragmentation can claim a lot space.

Run sp_helpfile to verify the log is not consuming space also. If the log is big run the following statement to recover space.

DBCC SHRINKFILE (log, 0)

On this thread I provided some queries that may be useful about database size.

Hope this helps.


xp_sendmail: failed with mail error 0x80004005 when I run xp_sendmail

exec master.dbo.xp_sendmail @recipients='me@hotmail.com',
@subject='terrrible thing happened',
@message='test'`

I am getting an error

  Msg 18025, Level 16, State 1, Line 0
  xp_sendmail: failed with mail error 0x80004005

It is SQL Server 2000. Outlook is set on this box. I can send emails from the Outlook.
It worked before. We just moved the server to DMZ.

We disabled Trendmicro antivirus's firewall

Have you logged into the server with the account which the SQL service is running under, and configured outlook under that account?


DBMS_Resource_Manager.Calibrate_IO Usage DBMS_Resource_Manager has a procedure called Calibrate_IO with a Physical Disks input parameter.  The documentation says this value should be set to the...

  Approximate number of physical disks in the database storage

With a logical drive striped across an unknown number of disks in a SAN disk group containing 30 drives, I'm not sure what this means.  I ran the procedure for values of 1 and 30, yet the IOPS, MBPS and latency were nearly identical.

Is this number just for informational purposes or is it actually used?  If the the later, then what should it be set to when there is not a one to one relationship between logical disks and physical disks?

Block to run procedure:

set serveroutput on;

DECLARE
   vDisks         Binary_Integer := 30;
   vMaxLatency    Binary_Integer := null;
   vMaxIOPS       Binary_Integer;
   vMaxMBPS       Binary_Integer;
   vActualLatency Binary_Integer;
BEGIN
   DBMS_RESOURCE_MANAGER.Calibrate_IO(Num_Physical_Disks => vDisks
      , Max_IOPS => vMaxIOPs, Max_MBPS => vMaxMBPS
      , Actual_Latency => vActualLatency);
   DBMS_OUTPUT.PUT_LINE('      Max IOPS:' || vMaxIOPS);
   DBMS_OUTPUT.PUT_LINE('      Max MBPS:' || vMaxMBPS);
   DBMS_OUTPUT.PUT_LINE('Actual Latency:' || vActualLatency);
END;
/

select * from dba_rsrc_io_calibrate;

It really is to be the number of physical disks, in the old fashioned style, not using a SAN. The best article about this I found here http://blog.bensmann.com/testing-oracle-io. Most others blogs/docs I found were more like copies from manuals than original contents that explains something new. When using this on a SAN, the cache is going to play a big role, as is the inner working of those beasts. For example, they write in a buffer and give an acknowledgement to the rdbms that the write is complete, even when the data did not even come close to the intended disk. When continuously other blocks are written, that cache could be choked and then we get a better view of the real io performance. Reading and updating blocks from a regular sized datafile won't be sufficient to reach that.


Migrating VLDB to AlwaysON AG I have been using AlwaysON AG for a long time now and currently have about 10TB of data across 120 databases and 3 AG groups for any application that is on SQL 2012 with great success. Each AG group is running on patch level 11.0.5058.0 with 2 synchronous replica(on different SANS) in Primary Data center and 1 ASYNC replica in DR. Migration has been a non-issue because none of the databases were substantial enough that I could not fit into my maintenance window which is 12-4AM on SAT morning.

My issue is that my last application to migrate to 2012 includes a 4TB TDE encrypted databases database which is about 10x larger than any of the previous ones I have migrated. The database takes 4 hours to backup after tuning extensively(I hate TDE!!)

The restore to the primary replica is instant because of seeding incremental but the issue comes from having to backup the database before adding to the availability group. 4 hours is my exact outage window and I can't get any more.

My plan to migrate application is to -

First Outage Window

Restore Database from 2008 to 2012 Primary Replica
Change application ARECORD(or cname not sure which) to Primary replica
Run database on single node until next outage window

Week Later :

Add database to availability group
Change ARECORD/CNAME to listener

What I don't like about this is I am going an entire week with 1 node instead of 3 which is worrisome. If anyone else has any better ideas on how to accomplish this I would love to hear from you or any type of comment from people who have worked with VLDB in availability groups and what you like/hate/loved about doing it. I am trying to go all in on this software and have loved it so far but getting worried when it comes to the VLDB migration.

The requirement that a database have a full backup is only enforced by the Wizard. If you just Add the database after the Availability group is already created you can restore the same backup on both the Primary and Secondary and then add the database to the Availability Group. To save time during the maintenance window we usually restore Full backups ahead of time and take a differential backup during the maintenance window.

Take Full backup of the database being moved.
Restore the full backup on the New primary WITH NORECOVERY this ensures the database isn't used until the secondary is ready.
Restore the same full backup on the new secondary WITH NORECOVERY this is needed to add them to the AG.
Restore the primary database WITH RECOVERY. RESTORE DATABASE [MyBigDB] WITH RECOVERY;
On the Primary instance navigate to AlwaysOn High Availability > Availability Groups > MyAG > Right-Click on Availability Databases > Add Database
Select your database
Select Join only
Connect to the secondary instance
All the validation should be skipped
Verify everything and click finish or Save the script.
Verify syncronization is happening by viewing the Availability Group Dashboard


Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_turkish_ci,IMPLICIT) for operation '=' and why? Hello I got error in mariadb. In my command  two columns have different collate. one is utf8_unicode_ci (a) other is utf8_turkish_ci (b). Why I got error when I compare these columns ( where a = b )  both of them utf8.
Error:
    Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_turkish_ci,IMPLICIT) for operation '='

It would be useful if you can provide your tables definition. However, your error suggests that your are trying to check equality between two columns having different collation.

One solution is to change collation of one of the columns to match the other collation. Check here - Column Level.

Other solution is to use COLLATE clause (I do not know if this works in mariadb, but it should be there).

Personally, I prefer to avoid COLLATE and harmonize string columns as collation mismatch tend to force lots of COLLATEs (maintenance problems).


Can a MS SQL Server be named different than host computer? I have a virtual machine that was built by our IT department to host various processes. They named the computer something incomprehensible like "XYSVC105" and I am not allowed to change it. I have also installed MS SQL Server 2014 on this machine and everything is working great.

However, I have been searching for MS best practices but cannot seem to find if you are able to change the name of the SQL Server to be different than the host computer or if it's recommended not to do it. I would like to change the SQL Server name to something like "OPSRES".

Most of the reasons for this are internal. One of them being that this will be used by a multitude of users and developers. From experience they will remember a simple name like OPSRES and not XYZ145PPQ.

I have been searching for MS best practices but cannot seem to find if you are able to change the name of the SQL Server to be different than the host computer or if it's recommended not to do it.

If you installed default instance then your sql server name will be hostname XYSVC105.

If you installed named instance e.g. sql01 then your sql server name will be XYSVC105\sql01.

Below are few ways that you can use to have your application connect to the name you wish e.g. OPSRES.

  I would like to change the SQL Server name to something like "OPSRES".

You can create an alias and have your client applications use that in connection string.

Also, since you are using sql server 2014 - if you are using AlwaysOn Availablity group then you can just use listener (its a virtual network name (VNN) or Virutal IP (VIP))  and have your application use listener instead of host names.

You can even ask your DNS Admin to create Hostname Alias for a SQL Server Instance


Inner join only for not empty values i can get right result using the where join , with next sql:

WITH fktable1 AS (
SELECT 2 AS id
)
,fktable2 AS (
SELECT 3 AS id
)
SELECT main.*
FROM
-- main data
    (
    VALUES
    (1, NULL, NULL),
    (2, 2, NULL),
    (3, NULL, 3),
    (4, 2, 3),
    (5, 4, NULL), -- wrong, not exist fkcol1 in the fktable1
    (6, NULL, 5) -- wrong, not exist fkcol2 in the fktable2
    ) AS main (col1, fkcol1, fkcol2)
    , fktable1, fktable2
WHERE
    (
        fktable1.id = main.fkcol1
        OR main.fkcol1 is NULL
    )
    AND (
        fktable2.id = main.fkcol2
        OR main.fkcol2 is NULL
    );

it give me the output:

 col1 | fkcol1 | fkcol2
------+--------+--------
    1 |        |
    2 |      2 |
    3 |        |      3
    4 |      2 |      3
(4 rows)

is there some way to get the same by table join statement?
i tried to find solution and read many exists question, but unsuccess.

You have an inner join with a condition there:

WITH fktable1 (id) AS (
  values (2)
)
,fktable2 (id) AS (
  values (3)
)
SELECT main.*
FROM (
  VALUES
    (1, NULL, NULL),
    (2, 2, NULL),
    (3, NULL, 3),
    (4, 2, 3),
    (5, 4, NULL),
    (6, NULL, 5)
) AS main (col1, fkcol1, fkcol2)
  join fktable1 f1 on f1.id = main.fkcol1 or main.fkcol1 is null
  join fktable2 f2 on f2.id = main.fkcol2 or main.fkcol2 is null


What does it mean by '4M blocks' or '4G blocks' in Oracle? The following 2 sentences are excerpt from Oracle documentation. You can easily fine them by google them directly. My question is, 4G and 4M are the unite of digital information storage. What's the meaning in the context of the following sentences?

Bigfile tablespaces can contain only one file, but that file can have up to 4G blocks.
A smallfile tablespace can contain up to 1022 files with up to 4M blocks each.

Thanks.

In this case 4G and 4M aren't quite referring to what I think you mean by the unit of digital storage; it isn't referring to 4 gigabytes and 4 megabytes. It's just a number, not a unit.

Oracle has to keep track of the blocks in the tablespace, and appears to be limited to a 32-bit 'address' space (2^32 = 4,294,967,296 = 4G). So it can internally reference up to 4G blocks of data. The distinction between bigfile and smallfile is that bigfile can reference one huge file with all 4G blocks in it, which smallfile can reference up to 1022 files with up to 4M blocks in each. (There seems to be some overhead somewhere, since it's 1022 instead of 1024). The total number of blocks ends up being (almost) the same, they're just organised differently.

So far this has little to do with how much actual data that is. This is where the database block size comes in. If you have a 4KB (4096 byte) block size, a bigfile can be up to 4G x 4096 bytes, or 16TB; while with smallfiles each file could be up to 4M x 4096 bytes, or 16GB. As you can have 1022 files, the total available storage is (almost) the same. If your database blocksize is twice as big, 8KB, the bigfile can also be twice as big, up to 32TB; and each smallfile can similarly be up to 32GB.

The bigfile documentation may help explain it better than I can. (Though confusingly that uses '32K' to mean 32 kilobytes, which is normally clear but when also talking about 4G blocks it might have been better to use '32KB'... I'm not opining on which is 'correct'!)


Are these tools still valid? I was watching a 7 years old webinar done by Brent Ozar (https://youtu.be/U_Kle3gKaHc), and heard of several items being recommended at that time.

SQLDiag utility.
SQLNexus.
PAL tool.
Database Tuning Advisor/wizard.
BPA (Best Practices Analyzer).
SQL Server Policy Based Management.

Are all of them still to be used/considered or there is something newer that replaced them?

Brent here - the guy in the hairy chest wig in the video.

Much like that hairy chest wig, I don't use those tools much anymore. The video is still up just for comic relief sake.

Over time, I wanted better tools, so with the help of my coworkers, I built 'em and open sourced 'em. Here's the Github repo for the First Responder Kit, which contains:

sp_Blitz - health check, replaces the BPA for my needs
sp_BlitzFirst - performance check, replaces Diag/Nexus for most of my needs, especially with the @SinceStartup = 1 parameter
sp_BlitzCache - performance check that focuses on queries
sp_BlitzIndex - index design check that replaces the DTA for me


MySQL - Turkey/Istanbul Daylight Saving Time Change Until this year Turkey was using daylight saving time, staying UTC+2 on winter period, UTC+3 on summer. This year it is decided to stay only in UTC+3.

Our DateTime's are persisted on MySQL (5.26.34) db (on AWS) was using UTC timezone.

When i try to use CONVERT_TZ(DateTime, UTC, EUROPE/ISTANBUL) for recent datetime -lets say- 2016-11-21 00:00 it tries to convert according to UTC+2, whereas it suppose to be UTC+3.

When i would tried to convert 2015-11-21 00:00 -last year- it suppose to convert according to UTC+2, since Turkey was in UTC+2 at that time.

I Found an announcement on AWS forum[1], advicing to use Moscow timezone, which is not an option for us since Turkey's timezone situation is unique and our DateTime's are saved in the form of UTC0.

https://forums.aws.amazon.com/ann.jspa?annID=4115

DATETIME is like a picture of a clock.  There is no going back to fix it.  A DATETIME column reflects what the user would see on a clock at the time he/she does the INSERT.

TIMESTAMP is stored internally as UTC, but converted going in and going out.  So, if the timezone is set correctly, and if the tz table is correct, TIMESTAMP always reflects a point of time in the cosmos.

So, in addition to getting the tz code 'right', and setting the timezone correctly, use the 'right' datatype.


Error when only deploying a dimension on SSDT I'm trying to follow a tutorial on SQL Server Analysis Services (SSAS) but I'm having trouble on deployment.

Summarized, what I'm doing is:

Open SQL Server Data Tools (SSDT)
Creating a new Multidimensional project
Creating a Data Source from a SQL Server database (succesfully connects)
Creating a Data Source View (can succesfully explore data)

If I try to deploy now, it works.

Creating a Dimension

The deployment doesn't work.

It gives me multiple errors such as:

Error in OLE DB or ODBC
Error in OLAP processing engine: An error ocurred when dimension, with ID "Dim Location", Name "Dim Location" was being processed.
Error in OLAP processing engine: An error ocurrer when attribute "Name" from dimension "Dim Location" from database "MultidimensionalProject02" was being processed.

If instead of creating a Dimension, I create a cube (with its dimensions), it gives the same errors (errors are related with the dimensions?).

The dimension I'm using is simple. Its name is DimLocation and has two columns, LocationKey (bigint) and Name (varchar).

The fact table when creating a cube is also simple. Its name is FactConsumption and has the columns, ConsumptionKey (bigint), LocationKey (bigint) and Value (int).

How can I create these elements and sucessfully deploy?

I'm using SQL Server 2016 Developer (I also have Express installed).

I don't speak Portuguese very well  but it looks as if it's saying that it can't connect to the datasource.

Please verify if your Analysis services service account has access to the data source.

Keep in mind that, unless you specify the username and password the processing is done using the Analysis Services service account while when you browse data from within visual studio you are using your own account.

Please read through Set Impersonation Options which explains in detail how the different data source options work.


Logging and Replaying database updates/inserts SQL Server 2008 I am in the process of migrating our client's database to the Azure Cloud SQL Server and setting up data sync etc. All of this is ok, but I need to reduce the amount of time the client's system needs to be down for to complete this process.

I was hoping to be able to set up a process that records all database inserts/updates from the time I create the backup which will be used in to migrate the data to azure, and then replay them on the database once it's in the cloud.

Is there a tool for this? Is SQL Profiler good enough for this?

Just to close this off, I found that Red gate Data Compare seems to be a great tool that will allow me to simply compare the two databases and in essence replicate the differences.


Let's start by dissecting the original query, based on the provided example data:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE Stocks
    (`id` int, `ST_StockCode` int, `SM_StockCode` int, `ST_ItemSize` int)
;

INSERT INTO Stocks
    (`id`, `ST_StockCode`, `SM_StockCode`, `ST_ItemSize`)
VALUES
    (1, 679783, 678649, 7.00),
    (2, 679789, 688622, 7.00),
    (3, 679792, 679793, 8.00),
    (4, 679792, 686376, 8.00),
    (5, 679793, 679792, 7.00),
    (6, 679793, 686376, 8.00),
    (7, 679795, 679796, 8.00),
    (8, 679796, 679795, 7.00),
    (9, 679797, 617114, 7.00),
    (10, 679797, 627339, 7.00)
;

Query 1:

SELECT
        t1.`ST_StockCode`, t2.`SM_StockCode`, t2.`ST_ItemSize`
    FROM
        `stocks` AS t1,
        `stocks` AS t2
    WHERE
        t1.`ST_StockCode` = t2.`SM_StockCode`
    GROUP BY t1.`ST_StockCode`
    ORDER BY t1.`id` ASC

Results:

| ST_STOCKCODE | SM_STOCKCODE | ST_ITEMSIZE |
|--------------|--------------|-------------|
|       679792 |       679792 |           7 |
|       679793 |       679793 |           8 |
|       679795 |       679795 |           7 |
|       679796 |       679796 |           8 |

First thing to note is that, this being an inner join, the two values  ST_StockCode and SM_StockCode are always identical. In the following examples I therefore mention only one of them as it is easy to add the missing one back in.

Second, this query is using a non SQL Standard extension to the GROUP BY clause. The query groups by ST_StockCode and then references two other columns without an aggregate. MySQL will return for those columns the first value it comes across. It does not check if the values are distinct and you might get different results each execution. If we assume that the original query writer was aware of this behavior we can do this instead:

Query 2:

SELECT SM_StockCode, MIN(ST_ItemSize) ST_ItemSize
  FROM stocks AS t1
 GROUP BY SM_StockCode;

Results:

| SM_STOCKCODE | ST_ITEMSIZE |
|--------------|-------------|
|       617114 |           7 |
|       627339 |           7 |
|       678649 |           7 |
|       679792 |           7 |
|       679793 |           8 |
|       679795 |           7 |
|       679796 |           8 |
|       686376 |           8 |
|       688622 |           7 |

This returns for each SM_StockCode the minimum ST_ItemSize (instead of a random one). However, it returns potentially more rows then the original query because there might be SM_StockCodes that do not have a matching ST_StockCode (as in the provided example data). However, that is easily fixed:

Query 3:

SELECT SM_StockCode, MIN(ST_ItemSize) ST_ItemSize
  FROM stocks AS t1
 WHERE EXISTS(SELECT 1 FROM stocks AS t2 WHERE t2.ST_StockCode = t1.SM_StockCode)
 GROUP BY SM_StockCode;

Results:

| SM_STOCKCODE | ST_ITEMSIZE |
|--------------|-------------|
|       679792 |           7 |
|       679793 |           8 |
|       679795 |           7 |
|       679796 |           8 |

Now only SM_StockCodes that have a matching ST_StockCodes are returned. (You have to duplicate the SM_StockCode column if you really need that value twice.)

With the query written this way, the indexing strategy becomes fairly obvious:

Create an index on ST_StockCode to support the EXISTS lookup and create a second index on SM_StockCode, ST_ItemSize to support the GROUP BY.

If you are sure that every SM_StockCode has a matching ST_StockCode (for example because a foreign key has been declared between the two, or because two of the analysts swear that this is always the case) you can shorten the query even more:

Query 4:

SELECT DISTINCT SM_StockCode, ST_ItemSize
  FROM stocks;

Results:

| SM_STOCKCODE | ST_ITEMSIZE |
|--------------|-------------|
|       678649 |           7 |
|       688622 |           7 |
|       679793 |           8 |
|       686376 |           8 |
|       679792 |           7 |
|       679796 |           8 |
|       679795 |           7 |
|       617114 |           7 |
|       627339 |           7 |

However, as in the example data excerpt of 10 random rows this condition is not given, the above result does not match in this case.


How to properly debug and inspect SQL Queries in PHP File? My website AdminPanel has a PHP file which takes almost 30-40 seconds in loading.  I doubt the queries which are making the mess, but may be it is the code but the code structure seems perfect. I want to find and check the all the queries execution and running time i.e how much time it takes to execute? But i can't.

I tried the slow query logs option but it is not logging anything. And from the general log i just see queries but no execution time and details.

Thanks

SET GLOBAL long_query_time = 1;
to log slow queries taking 1 second or more.

EXPLAIN EXTENDED SELECT ..... to see valuable information about how the query is getting to the result.

Follow immediately in the same session,
SHOW WARNINGS;
to see how the Optimizer rearranged the sequence of events.
(Rick James, thanks for the reminder.)


How to get average of lat/long from cities by county that they're in I found this script but I'd like to get the average of the latitudes/longitudes so I can just have the latitude/longitude of the counties. I tried

SELECT id, CITY, COUNTY, LONGITUDE, LATITUDE, AVG(LONGITUDE) AS val_1, AVG(LATITUDE) AS val_2
FROM directory_platform_cities
GROUP BY id, COUNTY;

But it's just attaching the lat/long of the first city to the county, is there a query that can do this? My intention is to get the latitude/longitude of the counties using the coordinates of their respective cities.

My intention is to get the latitude/longitude of the counties using the coordinates of their respective cities.

This is insane and you should never do this. Counties aren't geographic and this isn't how GIS works. Moreover the cities inside the counties are totally useless for determining where the county is. Cities are near rivers, costs, railroads, and roads. Counties are just political hacks to disempower minorities, and make sure rich ranches in unincorporated territories have enough police to keep out ex-slaves.

So if you have to use counties, what you want you to do is use them as they are and not try to make sense of them.

Download the Cenus Shape Files for your state. They're distributed in the TIGER data set by state, and they have one or more polygons that represents each state's counties!
Run a query using ST_Within to determine what county your points are in.

There is no need to find any specific point in that polygon because it makes no sense.


Slow query with multiple group by I have a query to get all the comments from threads where a given user has commented and not yet seen the comment. So if user 1 commented to a topic, then user 2 commented after his comment, then user 1 gets a notification and the notification is gone when he saw the new comment (p1.date>p2.last_seen) checks this.

It all works fine, but multiple group by is slow. It's better if I have separate queries for each type (tooic,news,pictures), as seen below.

If I have only 1 group by, the query is quick (0,02 sec or so).

    SELECT SQL_NO_CACHE p1.* FROM comment p1 INNER JOIN
      (SELECT max(seen) last_seen, max(id) MaxId,
      user_id, topic_id, picture_id, news_id, date
      FROM comment WHERE user_id='1' and deleted=0
      GROUP BY topic_id) p2
   ON p1.topic_id = p2.topic_id
   WHERE p1.date > p2.last_seen and p1.id > p2.MaxId and deleted=0
   GROUP BY p1.topic_id ORDER BY p1.date DESC

But it's very slow (~1,5 sec) with multiple group by (creating temp table takes ~1,2 sec).

    SELECT SQL_NO_CACHE p1.* FROM comment p1 INNER JOIN
      (SELECT max(seen) last_seen, max(id) MaxId,
      user_id, topic_id, picture_id, news_id, date
      FROM comment WHERE user_id='1' and deleted=0
      GROUP BY topic_id, picture_id, news_id) p2
   ON p1.topic_id = p2.topic_id and p1.picture_id= p2.picture_id
   and p1.news_id= p2.news_id
   WHERE p1.date > p2.last_seen and p1.id > p2.MaxId and deleted=0
   GROUP BY p1.topic_id, p1.picture_id, p1.news_id ORDER BY p1.date DESC

I'm using no cache for testing purposes.

Schema

Id is primary, other indexes are user_id, topic_id, picture_id, news_id, deleted, date and seen

CREATE TABLE `comment` (
`id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`topic_id` int(11) NOT NULL,
`picture_id` int(11) NOT NULL,
`news_id` int(11) NOT NULL,
`text` text COLLATE utf8_hungarian_ci NOT NULL,
`date` datetime NOT NULL,
`reply_id` int(11) NOT NULL,
`comment_vote` int(11) NOT NULL,
`comment_hit` int(11) NOT NULL,
`moderated` int(11) NOT NULL,
`seen` datetime NOT NULL,
`deleted` tinyint(4) NOT NULL,
`ip` text COLLATE utf8_hungarian_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
COLLATE=utf8_hungarian_ci;
ALTER TABLE `comment`
ADD PRIMARY KEY (`id`),
ADD KEY `topic_id` (`topic_id`),
ADD KEY `picture_id` (`picture_id`),
ADD KEY `news_id` (`news_id`),
ADD KEY `deleted` (`deleted`),
ADD KEY `user_id` (`user_id`),
ADD KEY `date` (`date`),
ADD KEY `seen` (`seen`);

Explain

1(id)   PRIMARY(select_type)    <derived2>(table) ALL(type) NULL(possible keys) NULL(key)   NULL(key_len)   NULL(ref)   105(rows)   Using temporary; Using filesort(extra)
1(id)   PRIMARY(select_type)    p1(table)   ref(type)   PRIMARY,topic_id,deleted,date,picture_id,news_id(possible keys)  topic_id(key)  4(key_len)  p2.topic_id(ref)    5(rows) Using where(extra)
2(id)   DERIVED(select_type)    p2(table)   index_merge(type)   deleted,user_id(possible_keys)  user_id(key)    4,1(key_len)    NULL(ref)   169(rows)   Using intersect(user_id,deleted); Using where; Using filesort(extra)

Using intersect(user_id,deleted) --> Provide INDEX(user_id, deleted) (in either order).

See the tag [greatest-n-per-group]; there are more efficient ways to do this type of query.  Also, see [groupwise-max] in stackoverflow.com

Also INDEX(topic_id, picture_id, news_id) (in any order)

(There may be more improvements to make; see how far those tips get you.)


Postgres performance difference on insert I was tried to partition one large table, and come with such Postgres behavior that I could not explain. Maybe you will have any suggestions?

I went quite straight forward way (i know that it is not most efficient, but still i had some time):

create table target_table  as select * from parent_table limit 0;
insert into target_table select * from parent_table where date_field >= '20120101' and date_field <='20120131' and tariff_id <> -1;
insert into target_table select * from parent_table where date_field >= '20120201' and date_field <='20120228' and tariff_id <> -1;
insert into target_table select * from parent_table where date_field >= '20120301' and date_field <='20120331' and tariff_id <> -1;
insert into target_table select * from parent_table where date_field >= '20120401' and date_field <='20120430' and tariff_id <> -1;
insert into target_table select * from parent_table where date_field >= '20120501' and date_field <='20120531' and tariff_id <> -1;
.....

And there is results how much time each command took to execute:

08:44:42  [INSERT - 8981055 row(s), 119.962 secs]  Command processed
08:46:36  [INSERT - 8222656 row(s), 114.057 secs]  Command processed
08:48:43  [INSERT - 8981454 row(s), 126.789 secs]  Command processed
08:51:02  [INSERT - 8929325 row(s), 139.815 secs]  Command processed
08:53:13  [INSERT - 9465383 row(s), 130.752 secs]  Command processed
08:55:25  [INSERT - 9302664 row(s), 131.911 secs]  Command processed
08:57:36  [INSERT - 9581798 row(s), 130.853 secs]  Command processed
08:59:58  [INSERT - 10080875 row(s), 142.071 secs]  Command processed
09:15:03  [INSERT - 9717698 row(s), 905.030 secs]  Command processed
10:24:22  [INSERT - 10169181 row(s), 4159.346 secs]  Command processed
11:33:11  [INSERT - 10000601 row(s), 4128.590 secs]  Command processed
12:38:36  [INSERT - 10576846 row(s), 3924.579 secs]  Command processed

I can't find the explanation why sometime inserts from the same table took 2 minutes and sometimes it took 1 hour! What could be reasons for such behavior?

Some info:

PG server running on amazon cloud xlarge EC2 instance.
I am the only one user and it was not overloaded with other tasks.
work_mem = '1GB'
parent_table is pretty large - 800 million of rows.

Very likely your problem is that you have an index on the source table which is making the first hits easy.  However at some point you are hitting rows that are not in the cache and so this is requiring a sudden increase in disk I/O accounting for the performance drop.  In essence suddenly you start missing the cache and so you end up having to read a bunch of things from disk again.


Meaning of "Dependency" in Relation to Elementary DB Normalization In trying to figure out why the following table definitions from a textbook exercise about nurses in hospital wards is not in 2NF:
Ward (WName, Location, WType)
Ward-Nurse (WName(fk), NurseID, NurseName, TeamCode, TeamSkill,
Shift)
what is the meaning of "depends on" when describing how some attributes don't "depend" on the whole key? Is it something like "you don't need to know the ward name in order to know what shift a nurse is working" for example?
What is the thought process by which it becomes obvious that for this DB to be in 2NF I need to change the definition to
Ward ( WName, Location, WType)
Ward-Nurse ( WName(fk), NurseID(fk), Shift)
Nurse ( NurseID, NurseName, TeamCode, TeamSkill)
Likewise for 3NF, what interpretation of the word "depends" and its application to the table definitions above allows me to know that the solution for 3NF is
Ward (WName, Location, WType)
Ward-Nurse ( WName(fk), NurseID(fk), Shift)
Nurse ( NurseID, NurseName, TeamCode(fk))
Team (TeamCode, TeamSkill)
All the above being from a textbook exercise where no explanation is given beyond the proposed table definitions.

In this context "depends on" means "determined by" or "dictated by". Knowing a person's ID we can look up their date of birth. Given a second ID we will, in general, get a different date of birth. (Of course they may incidentally be the same date but that is not dictated by the table definition, just an accident since there are only a finite number of dates to use.) So to know a person's date of birth we must know the ID number. We say date of birth depends on the ID.
To determine if a table is 2NF I would think "Given a row, if I changed part of the key only, would I still be certain what the non-key columns' values are?" For the proposed table Ward-Nurse (WName(fk), NurseID, NurseName, ..), if I changed WName but kept NurseID constant, would I still be certain what NurseName would be? The answer is yes, because NurseName depends on (is determined by / dictated by) NurseID only and is not in any way determined by WName. Similarly with TeamCode and TeamSkill.
The difference between 2NF and 3NF is 2NF talks about keys and 3NF talks about non-keys. Given a row from the 2NF table Nurse (NurseID, NurseName, TeamCode, TeamSkill), if I changed any non-key column will everything else still be necessarily the same? If I change the value of NurseName will TeamCode or TeamSkill remain constant? Yes, they will. If I change TeamCode will TeamSkill and NurseName remain as they were? No, TeamSkill must change; TeamSkill must change when TeamCode changes so its value depends on the value of TeamCode. Hence (TeamCode, TeamSkill) becomes its own table in 3NF.


Simple query not returning blank records Very simple example here to a very frustrating treasure hunt for the mystery character.

I have a table with a varchar(1) NOT NULL column - everything default - ANSI_PADDING & NULLS are ON for the table as well. We'll call this column CodeNum

A simple query would be

SELECT *
FROM tableA
WHERE CodeNum = ' '

If I run this query against DatabaseA  it returns the all the rows that have a blank or "nothing" in the column as expected.

If I run the same query against DatabaseB for the same table (used compare scripts and even tools to verify they are the same) it returns no results.

I tried looking for hidden carriage returns and other characters by looking for CHAR(1) through CHAR(255). Still no results.

As a test I tried setting the column in DatabaseB equal to '' with a blanket update statement.

UPDATE tableA
SET CodeNum = ''

Now my query returns the results as expected. So to me that says that there is some character in that column but I'd really like to figure out what that character is so I can track down its origin. Any thoughts on what else I could be missing?

Try:

SELECT ASCII(CodeNum)
FROM tableA

To see the ascii number of the character in that field.


SQL - Update table for a unique ID with multiple entries if one entry has variable in question I'm trying to update a table to fill in a variable to each row in the table if one row for the unique ID shows that variable.

I was trying to do the following, but it's giving me an error.  Appreciate the help!

update patient_encounter
Set user_defined4 = 'DTC'
where person_id = (SELECT person_id from patient_encounter where  mrkt_plan_id = '489681B4-9D94-413B-95A0-C50D5964F340')

I want to fill in "DTC" into the user_defined4 field for any person_id that shows the particular mrkt_plan_id listed (which correlates to DTC) for any of their entries.  Each person_id has multiple encounters and I want them all to share the same market plan once it is listed as DTC once.

The error says Subquery returned more than 1 value.
Thanks!

There multiple entry in table patient_encounter for a person_id, so you need to get the person_id with top 1

Try this

update patient_encounter
Set user_defined4 = 'DTC'
where person_id = (SELECT top 1 person_id from patient_encounter
                     where  mrkt_plan_id = '489681B4-9D94-413B-95A0-C50D5964F340')

EDIT as per comment, if you are looking to update all the record, use IN as below

update patient_encounter
    Set user_defined4 = 'DTC'
    where person_id IN (SELECT person_id from patient_encounter
                         where  mrkt_plan_id = '489681B4-9D94-413B-95A0-C50D5964F340')


stored procedure examples I am wondering if there are examples of open source apps that are written in stored procedures? I read a lot about them in textbooks and would like to see how they are used in practice beyond implementing simple functionalities. One example that I found is the DBT benchmark implementations (http://sourceforge.net/apps/mediawiki/osdldbt) using stored procedures. If there are other similar applications I'd love to learn about them.

@a_horse_with_no_name: thanks for your pointer. Apps like LedgerSMB that makes heavy uses of stored procedures are exactly those that I am looking for. If there are other similar ones please let me know.

I doubt there are many applications that are completely written in stored procedures. Doing a (sophisticated) user interface based on a procedural language in a DBMS is pretty hard to do (depending on the DBMS maybe even impossible).

Oracle's APEX (APlication EXpress) is geared toward that, but the UI is still HTML - although it is generated generated through stored procedures if I'm not mistaken.

One application I know of that makes heavy use of stored procedures as part of the business logic is LedgerSMB: http://ledgersmb.org/


Backup on an empty SSAS database hangs I created an SSIS package to iterate through all databases on an SSAS instance. It grabs the DatabaseId using AMO in a vbscript, then generates the XMLA to backup each database.

Most databases backup with no problems, but when the ForEach gets to one particular database, the Execute Analysis Services DDL Task seems to hang. The .abf file is created, but the SSIS package doesn't seem to get the response that the command is completed. It's like the backup process is stuck on the file.

When I backup the database using SSMS dialogs, the same problem occurs. I verified that other databases backup successfully using this method.

I should note that the database in question is 0.0MB in "estimated" size according to the db properties in SSMS. Is the database truly empty, or could this be a sign of corruption?

Why doesn't the backup process complete? I get no error messages, just a long-running process that does not end. Is there anything I can do to find the root cause?

The backup hangs because the SSAS database is corrupt, which was the result of a forced reboot of the server.

Some questions this raises:

If an SSAS database has an estimated size of 0, does that always indicate a corrupted database?
Are there any other ways to detect SSAS db corruption by using SSMS dialogs or AMO?