Slow query SQL Server I have a query that is taking too long to run.
I executed set statistics io on and set statistics time on, and I noticed that Scan count and logical reads are very high

Table 'TABLE_01'. Scan count 6497248, logical reads 26065220, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads
0, lob read-ahead reads 0.
SQL Server parse and compile time:     CPU time = 20 ms, elapsed time
= 20 ms.
(138 row(s) affected)
SQL Server Execution Times:    CPU time = 28969 ms,  elapsed time =
30559 ms.

Here's the query:
SELECT Object1.Column1 AS Column2,
       Object1.Column3          AS Column4,
       Object1.Column5,
       Object1.Column6,
       Object1.Column7             AS Column8,
       Object2.Column9                      AS Column10,
       Cast(Object2.Column11 AS VARCHAR)  AS Column12,
       Object2.Column13              AS Column14,
       Object2.Column15                     AS Column16,
       Object2.Column17                   AS Column18,
       Object2.Column19                     AS Column20,
       Object3.Column21                                  AS Column22,
       Object3.Column5                                   AS Column23,
       Object2.Column24              AS Column25,
       Object4.Column21                                 AS Column26,
       Object4.Column5                                  AS Column27,
       Object5.Column21                            AS Column28,
       Object5.Column29                        AS
       Column30,
       Object6.Column31                  AS Column32
FROM   Object1
       INNER JOIN (Object2
                   LEFT JOIN Object7 Object3
                          ON Object2.Column19 = Object3.Column19
                   INNER JOIN Object7 Object4
                           ON Object2.Column24 = Object4.Column19
                   INNER JOIN Object5
                           ON Object2.Column13 = Object5.Column15
                   INNER JOIN Object6
                           ON Object2.Column9 = Object6.Column9)
               ON Object1.Column3 = Object2.Column3
WHERE  Object1.Column3 IN (
   43796020, 43795933, 43795931, 43795681,
       43795672, 43793093, 43793090, 43793085,
       43790358, 43790342, 43789167, 43789155,
       43789154, 43788970, 43788928, 43788924,
       43788776, 43788774, 43788712, 43788699,
       43788674, 43788673, 43788626, 43788625,
       43788227, 43788219, 43787557, 43787530,
       43787088, 43787075, 43787063, 43787041,
       43787002, 43786368, 43786351, 43786244,
       43786243, 43786223, 43786222, 43786151,
       43417954, 43417953, 43417929, 43417294,
       43417293, 43417286, 43416500, 43416334,
       43416304, 43416288, 43416089, 43416034,
       43416016, 43416015, 43416014, 43416013,
       43416012, 43416011, 43416010, 43416009,
       43416008, 43416007, 43415753, 43401569,
       43400701, 41524028, 41316604, 41311555,
       41309174, 41308146, 41308136, 41286794,
       41281375, 41281374, 41281294, 41263751,
       41263749, 40960597, 38574969, 38133930,
       38133868, 38133516, 38132482, 37630401,
       37630400, 37630399, 37630398, 37630397,
       37630396, 37630395, 37629851, 37629846,
       37301414, 37298436, 37298433, 37298431,
       37298429, 37298427, 37298424, 37298398
       )
ORDER  BY Object1.Column1 ASC

I would like to know how I can improve this query
The estimated execution plan and the actual execution plan

Hmm  so as I mentioned in the comments, your actual execution plan reveals there is a severe cardinality estimate issue (screenshot of operation details for reference) on the Clustered Index Seek operation occuring on the atividade table. I'm not seeing much else in the execution plan or your query that jumps out at me explaining why you might be experiencing this type of issue other than the predicate in your WHERE clause being a decent size in the IN list, which essentially is syntactical sugar for a bunch of ORs. This also happens to be what the seek predicates are on for the aforementioned Clustered Index Seek operation, so it's a little suspect.
Unfortunately I think this one might require a little trial and error of query re-writing to see what helps the optimizer in generating a better plan without a cardinality estimate issue. Could you please try re-writing your query like this logical equivalent query (if possible) and re-running it to see if there's any performance improvement?
SELECT
    id_atributo_andamento AS idatributoandamento,
    id_atividade AS idatividade,
    nome,
    valor,
    id_origem AS idorigem
INTO #atributo_andamento
FROM atributo_andamento
WHERE id_atividade IN
(
   43796020, 43795933, 43795931, 43795681,
   43795672, 43793093, 43793090, 43793085,
   43790358, 43790342, 43789167, 43789155,
   43789154, 43788970, 43788928, 43788924,
   43788776, 43788774, 43788712, 43788699,
   43788674, 43788673, 43788626, 43788625,
   43788227, 43788219, 43787557, 43787530,
   43787088, 43787075, 43787063, 43787041,
   43787002, 43786368, 43786351, 43786244,
   43786243, 43786223, 43786222, 43786151,
   43417954, 43417953, 43417929, 43417294,
   43417293, 43417286, 43416500, 43416334,
   43416304, 43416288, 43416089, 43416034,
   43416016, 43416015, 43416014, 43416013,
   43416012, 43416011, 43416010, 43416009,
   43416008, 43416007, 43415753, 43401569,
   43400701, 41524028, 41316604, 41311555,
   41309174, 41308146, 41308136, 41286794,
   41281375, 41281374, 41281294, 41263751,
   41263749, 40960597, 38574969, 38133930,
   38133868, 38133516, 38132482, 37630401,
   37630400, 37630399, 37630398, 37630397,
   37630396, 37630395, 37629851, 37629846,
   37301414, 37298436, 37298433, 37298431,
   37298429, 37298427, 37298424, 37298398
)

CREATE CLUSTERED INDEX IX_Temp_atributo_andamento_idatividade ON #atributo_andamento (idatividade)
CREATE NONCLUSTERED INDEX IX_Temp_atributo_andamento_idatributoandamento ON #atributo_andamento (idatributoandamento);

SELECT
    atributo_andamento.idatributoandamento,
    atributo_andamento.idatividade,
    atributo_andamento.nome,
    atributo_andamento.valor,
    atributo_andamento.idorigem,
    atividade.id_tarefa AS idtarefaatividade,
    Cast(atividade.id_protocolo AS VARCHAR)  AS idprotocoloatividade,
    atividade.id_unidade_origem AS idunidadeorigematividade,
    atividade.id_unidade AS idunidadeatividade,
    atividade.dth_abertura AS aberturaatividade,
    atividade.id_usuario AS idusuarioatividade,
    u.sigla AS siglausuarioatividade,
    u.nome AS nomeusuarioatividade,
    atividade.id_usuario_origem AS idusuarioorigematividade,
    uo.sigla AS siglausuarioorigematividade,
    uo.nome AS nomeusuarioorigematividade,
    unidade.sigla AS siglaunidadeorigematividade,
    unidade.descricao AS descricaounidadeorigemativi001,
    tarefa.id_tarefa_modulo AS idtarefamodulotarefa
FROM #atributo_andamento AS atributo_andamento
   INNER JOIN atividade
               LEFT JOIN usuario u
                      ON atividade.id_usuario = u.id_usuario
               INNER JOIN usuario uo
                       ON atividade.id_usuario_origem = uo.id_usuario
               INNER JOIN unidade
                       ON atividade.id_unidade_origem = unidade.id_unidade
               INNER JOIN tarefa
                       ON atividade.id_tarefa = tarefa.id_tarefa
           ON atributo_andamento.idatividade = atividade.id_atividade
ORDER  BY atributo_andamento.idatributoandamento ASC

The above query breaks out the logic of the filtering on the id_atividade field for the atributo_andamento table and inserts the results into a temp table first. Then it creates an index on the idatividade field that is used in the INNER JOIN predicate to the atividade table later on. I also am testing creating an index on idatributoandamento which is what is used in the ORDER BY clause at the end of your query. Not sure if the query optimizer will use both indexes, but it's worth trying as a start.
Please run the above and let me know how it turns out. We can try adjusting a couple more things and still end up with a logically equivalent query, to see if we can fix the cardinality estimate issue, and worst case we may need to recalculate the statistics or possibly use a query hint.


SQL Server 2008 R2 SP1 - Can't install "Full-text Search" I have a "Microsoft SQL Server Express 2008 R2 SP1" installation on a machine without the "Advanced Services". Now I want to install the "Full-text Search", so I downloaded the setup "SQLEXPRADV_x64_DEU.exe".

The problem is, if I start the setup, select my existing sql-instance and go further to the feature-selection menu, I don't see the "Advanced Services". I just see the already installed features. Here's a screenshot (in german):

I did the installation of the "Full-text Search" on about 20 different machines with existing sql-instances without problems. But on this machine, I don't know what's the problem.

Hope somebody can help me! Thanks in advance!

For all people which have the same problem, I solved it this way:

I installed a completely new "SQL Server 2012 SP2" instance (with fulltext-search), transferred my databases to the new instance and uninstalled the old one.


MongoDB optimization on high data I have a mongoDB collection with 42M registers with 1M new registers every day.

We found a low performance on mongoDB querying.
showing in mongostat the fields: qr|qw ar|aw with values of 300-500.

First move was to check index and make new indexes for querys that may took so long.

Now we are getting a good performance most of the time.

But in some cases (random time) we get a lot of connections in pending status, all slow on querys.

But, the top command shows a good performance on processing speed and ram.

The question is:

How I can check more further what are the not optimized querys.
In new relic show the INSERT command in this specific collection take the 62% of "most time consuming", there is any way to speed this up?
And why can be the causes of the pending connections?
The strange thing is: when the numbers starts going up never stops until website crash.

EDIT:

We finally solved a big part of the problem doing this steps:

Move mongo to a new server withouth a openvz, seems mongo gives troubles with openvz
New filesystem and saas.
Create new indexes based on our needs.

Mongo is now running pretty well, so thanks all for your help :)

First, let me say that with the information given it will be hard to get at the root cause - it is usually an iterative process that takes multiple attempts to track down the culprit.  In the interest of answering your "what next?" portion of the question rather than identifying the root cause, read on.....

First, a couple of recommendations:

Get the host into MMS (it's free) - see http://mms.10gen.com - so you can graph stats over time and get a view of the issues without having to be sitting on the box running commands
Get munin-node installed too, so you can correlate ops etc. with IO (install docs for MMS explain this).

Next, a couple of quick checks for common causes:

What is your filesystem/kernel? - these generally need to be ext4/XFS and recent enough to have fallocate working (2.6.23 and 2.6.25 respectively) so that new file allocation is not slow
Assuming you don't get MMS and munin installed, get iostat output to match up with mongostat to determine if IO is the root cause for the bottleneck
Do you do any periodic batch updates that grow the documents significantly (i.e. that would cause moves)?  Moves are expensive and can cause IO to get backed up
Is your disk up to the data volume you are writing to it?  MongoDB fsyncs to disk every 60 seconds by default, if the volume that needs to be synced after 60 seconds is massive (say because of an insert spike) then you can also run into issues

That's not an exhaustive list, I have seen other issues cause this, but that should get you started down the right path.


Detailed optimal column order with large text and bytea I read this excellent answer on optimal column ordering and noticed the summation:

  Generally speaking, if you put the 8-byte-types first then the 4-byte-types and the 2-byte-types last you can't go wrong. Text or boolean do not have alignment restrictions like that, some other types do. In the end, you may save a couple of bytes per row at best. So, none of this is necessary for most people in most cases. But in your case it might save a couple of Gigabytes easily.

Does that mean that columns should be ordered from most space occupation to least?

If so, for bytea columns that always have constant 16-byte, 32-byte, or 64-byte lengths, do the same rules apply?  What about a text column that varies between 1kb to 5mb, heavily skewed to 1kb?

The bytea columns are the variables used in all read conditions.

The upper limit on this table's length is tens of billions of rows per year.

Does that mean that columns should be ordered from most space occupation to least?

No, not necessarily. You can play "column tetris" to minimize padding and thereby save some space. The rule of thumb I gave and you quoted is one simple strategy for basic types that require alignment.

As I mentioned in the quoted answer, you can test the actual storage size (excluding item identifier) with pg_column_size() on the whole row.

text and related varchar and char types do not require padding, so there is nothing to gain. The same is true for your bytea columns.

Concerning storage size for:

  bytea columns that always have constant 16-byte, 32-byte, or 64-byte lengths

The manual page on bytea tells us :

Storage Size
1 or 4 bytes plus the actual binary string

That means, the actual space required for a bytea column of 16-byte, 32-byte, or 64-byte
length is 17 or 20 byte, 33 or 36 byte etc. respectively.

As demonstrated in this SQL Fiddle, a bytea variable always has an overhead of 4 bytes. When stored in a column, however, it starts out with just 1 byte of overhead and switches to 4 bytes for values of 127 bytes length or more.
24 bytes of overhead are added for the row type.
Another 4 bytes are needed for the item identifier per tuple in the data page. Details in this related answer:

Configuring PostgreSQL for read performance

As for alignment requirements of bytea, per documentation:

  Values with single-byte headers aren't aligned on any particular boundary, either.

I would suggest you read that whole chapter - probably a couple of times, it's a tough read.


Postgres select using lateral I'm after running a query with a subquery (perhaps inefficient) that using a regular expression pattern it can return how many instances of a characters are included with a given record.

In doing so, I came across something like:

SELECT t."page",
            product_tree_length,
           Count(*),
  FROM     "Table" t
  ,
  LATERAL (SELECT (LENGTH((regexp_matches(t."page", '^.+\/c\/(.+?)(?=\/\?|\?|\/$).*$', 'g'))[1]) - LENGTH(REPLACE(((regexp_matches(t."page", '^.+\/c\/(.+?)(?=\/\?|\?|\/$).*$', 'g'))[1]),'/','')))) AS "product_tree_length"
  where t.page like '%\/c/%'
  GROUP BY 1, product_tree_length;

However, in selecting the product_tree_length in the main query, the returned data type is a record and not an integer as expected.
If I try to cast the value, PostgreSQL raise an exception that the operation cannot be completed.

Any idea on what I am doing wrong?

Thanks

Well product_tree_length is a table alias, not a column alias. So if you use that alias in the SELECT list, obviously you get a record.

You have two options to properly define an alias for the column

Solution one: use a column alias inside the derived table:

select t.page
       product_tree_length.len,
       count(*)
from "Table" t
  cross join lateral (
     SELECT length(....) as len
  ) AS product_tree_length
...

Solution two: define a column alias as part of the table alias:

select t.page
       product_tree_length.len,
       count(*)
from "Table" t
  cross join lateral (
     SELECT length(....)
  ) AS product_tree_length(len)
...


Can't connect to a remote server using phpMyAdmin I tried with two different servers, one had no issue but the other gave me this error:

MySQL said:
Cannot connect: invalid settings.
mysqli_real_connect(): The server requested authentication method unknown to the client [sha256_password]
mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the client
phpMyAdmin tried to connect to the MySQL server, and the server rejected the connection. You should check the host, username and password in your configuration and make sure that they correspond to the information given by the administrator of the MySQL server.

Screenshot:

Check your PHP version (and in particular, your PHP MySQL connector version), it may not have support of the newer authentication plugin. If for some reason you cannot upgrade you client PHP installation, you can change the user you are using to connect on the server to revert back to the more insecure native authentication so it works with older connectors, but try to upgrade the client first.

More info:

http://blog.ulf-wendel.de/2012/mysql-5-6-sha256-secure-password-support-for-php-mysqlnd/
https://dev.mysql.com/doc/refman/5.6/en/sha256-pluggable-authentication.html
https://stackoverflow.com/questions/30710146/php-mysqlnd-sha256-password-plugin-access-denied-user-works-from-cli-mysql


How to create an user with permission to do everything but drop databases? If it is easier, the user can be denied only deletion of one specific database instead of any database.

I assume by everything you mean db_owner rights.  Not Sysadmin rights.
If you can't trust them not to drop a database, they should not have access to most things you can do with Sysadmin.
What you want is described in the answer here https://dba.stackexchange.com/a/163437/21924

So in effect, you can create a user that has all the rights of db_owner except say, managing users and dropping database. But I don't think there's a way to directly deny such privileges, you have to instead add everything but them.
(See https://msdn.microsoft.com/en-GB/library/ms189121.aspx)

Above is what you asked for, the solution most often used is just give the user db_owner.  Take regular backups, with the database in full recovery.  Take T-log backups as frequently as needed for your DR solution.
In the end it doesn't matter if a stupid user deleted the database or if the server crashed. Something happened to the database and you need to restore it. Your DR solution defines how much data the business can afford to loose. As it is probably a business user who has db_owner and deleted the database.  You have done your part as a DBA (assuming your backup and restores work, you should test them regularly)


Concatenate into Field in Update I am trying to write a Stored Procedure which updates a quantity data column in one of my tables, and then takes a field set for random notes and concatenates the update information.

This is what I have for the Procedure thus far

CREATE PROCEDURE UpdatePartQuantity(
    @OrderNum VARCHAR(20),
    @PartNum  VARCHAR(100),
    @Quantity INT
) AS
BEGIN
    DECLARE @OrderID int, @PartID int

    IF(EXISTS(SELECT order_id FROM purchase_orders WHERE order_number = @OrderNum))
    BEGIN
        SELECT @OrderID = order_id
          FROM purchase_orders
         WHERE order_number = @OrderNum
    END
    ELSE
        RETURN 1

    IF(EXISTS(SELECT part_id FROM parts WHERE part_number = @PartNum))
    BEGIN
        SELECT @PartID = part_id
          FROM parts
         WHERE part_number = @PartNum
    END
    ELSE
        RETURN 1

    IF(EXISTS(SELECT part_id, order_id FROM order_parts WHERE order_id = @OrderID AND part_id = @PartID))
    BEGIN
        UPDATE order_parts
        SET ordered = @Quantity, notes = CONCAT(notes, 'string goes here')
        WHERE order_id = @OrderID AND part_id = @PartID
        RETURN 0
    END
    ELSE
        EXEC AddPartToOrder @OrderNum, @PartNum, @Quantity
END

So here is how the procedure is suppose to run:

Check if the passed order number exists within the database, if it does get the order id associated with it.
Check if the passed part number exists within the database, if it does get the part id associated with it.
Check if an entry has even been made for this part in this order.  If it hasn't create the entry by called the AddPartToOrder Procedure
If the entry does exists, change the ordered value to the value of @Quantity and concatenate onto the notes column: "Quantity updated: [Current Date]Old-[Old Value],New-@Quantity"
If any If statements return False, exit Procedure returning 1

My issue is I am not sure how to set up my CONCAT statement.  How would I put a GETDATE call inside of it?  How would I reference the value of @Quantity?  I am not exactly sure how to approach this.

You can concatenate text with text, so you need to CAST(). For example,

notes = CAST(GETDATE() AS varchar(max)) + 'Old-' + CAST(ISNULL(notes, '') AS varchar(max)) + 'New-' + CAST(@Quantity AS varchar(max))

Note that concatenate null with something will give you null, so to implement CONCAT() function's functionality you need ISNULL().


How to list all pending friend requests for a given user ID? I have a friendship table like this

A and B are friends if there are (A,B) and (B,A) rows. For example, uid 132 & 67 are friends indicated by (132,67) on 2nd row and (67,132) on ninth row. Rows 4 - 8 are pending requests to uid 132.

My question is how to list all pending requests to uid X? I tried this (X=132):

SELECT a.uid1, a.uid2 FROM tbl_friendship a LEFT JOIN tbl_friendship b ON a.uid1=b.uid2 AND a.uid2=b.uid2 WHERE a.uid1 IS NULL AND b.uid2 =132

Still not right because the result is empty. The expected result is something like:

83, 90, 93, 94, 97

How to fix the query?

how to list all pending requests to uid X? I tried this (X=132)

SELECT GREATEST(uid1, uid2), LEAST(uid1, uid2)
FROM tbl_friendship
WHERE 132 IN (uid1, uid2)
GROUP BY 1, 2
HAVING COUNT(*) = 1

  I'm only interested to find everyone who send friend request to 132. – anta40

SELECT t1.uid2
FROM tbl_friendship t1
WHERE t1.uid2 = 132
  AND NOT EXISTS ( SELECT NULL
                   FROM tbl_friendship t2
                   WHERE (t1.uid1, t1.uid2) = (t2.uid2, t2.uid1) );


Can two transactions share the same SCN? Do all transactions have a unique SCN?  Can two transactions share an SCN?

I'm confused by this statement in the Oracle Golden Gate manual:

  The CSN is cross-checked with the transaction ID (displayed as XID in Oracle GoldenGate informational output). The XID-CSN combination uniquely identifies a transaction even in cases where there are multiple transactions that commit at the same time, and thus have the same CSN. For example, this can happen in an Oracle RAC environment, where there is parallelism and high transaction concurrency.

https://docs.oracle.com/goldengate/1212/gg-winux/GWUAD/wu_csn.htm#GWUAD752

Table D-1 in that document identifies the GoldenGate CSN as being the Oracle DB SCN:

Oracle GoldenGate CSN Values Per Database

Oracle:  system_change_number
Where:   system_change number is the Oracle SCN value.

For single node Oracle RDBMS installations, an SCN is never duplicated.

With older versions of Oracle RAC, Oracle 9i for example, it was possible that two transactions, running on separate RAC nodes, could end up with the same SCN.  Duplicates happened only in rare situations where high transaction rates on the RAC nodes was coupled with a large value for MAX_COMMIT_PROPAGATION_DELAY.

In Oracle RAC installations of version 11gR2 and later, RAC nodes coordinate changes to the SCN via a broadcast mechanism by default, essentially eliminating the possibility of a duplicate SCN.


Is a join faster with 5 tables joined together Or 5 separate queries? I have confusion that I already have in my application 5 tables say a,b,c,d,e and have relationship with each other like a.b_id is primary key of b and chain goes on.
Now my question is that which approach will give me better performance/faster result?
5 queries
Or
A query join with 5 tables?
Thank you.

Look at it this way...  There is  a lot of overhead in each SELECT:

Round trip between client and server
Allocate a thread to work on the query
Parse the SQL
Optimize it
Perform it  (The meat!)
Send the results back

In some experiments, I have seen the "overhead" be 90% of the work.  5 queries takes nearly 5 times the overhead.


Oracle DISTINCT with NULLable columns There is a longstanding(?) problem(?) with Oracle that if you want to use a DISTINCT select on a column which has a B*-Tree Index but is nullable, that the index is not used. (As other answers suggest, this is even true if there was a check constrained added after the fact (No nulls present)).

There are various ways around this (including using a BITMAP index or adding a second NON NULL or constant column to the index). However I just noticed that if I do the SELECT DISTINCT with WHERE NOT NULL, Oracle is able to use the index (index fast full scan).

My Question: since which Oracle version this behavior is present, is it reliable (when is the two column index prefered) and why isnt it mentioned more often (for example this otherwise good answer does not mention it)?

Little reproducer

drop table SCOTT.T;
-- will not work with short rows (SELECT OWNER,SUBOBJECT_NAME ...)
create table SCOTT.T AS SELECT * FROM ALL_OBJECTS;
create index SCOTT.IDX_T_OWNER on SCOTT.T(OWNER); -- NOT NULL
-- (subobject_name,1) or (subobject_name,namespace) is NULL, NOT NULL
create index SCOTT.IDX_T_SUBOBJ on SCOTT.T(subobject_name); -- NULL
exec  dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'T', cascade=>true );
desc SCOTT.T;

set autotrace on explain
-- fast index scan:
select distinct OWNER from SCOTT.T;
-- full table scan:
select distinct SUBOBJECT_NAME from SCOTT.T;
-- fast index scan:
select distinct SUBOBJECT_NAME from SCOTT.T where SUBOBJECT_NAME IS NOT NULL;

Looks (on 18c) similiar to:

select distinct subobject_name from T;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   715 |  1430 |   436   (3)| 00:00:01 |
|   1 |  HASH UNIQUE       |      |   715 |  1430 |   436   (3)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    | 81426 |   159K|   428   (1)| 00:00:01 |
---------------------------------------------------------------------------

select distinct subobject_name from T where SUBOBJECT_NAME is not null;

------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |   624 |  1248 |     5  (20)| 00:00:01 |
|   1 |  HASH UNIQUE          |              |   624 |  1248 |     5  (20)| 00:00:01 |
|*  2 |   INDEX FAST FULL SCAN| IDX_T_SUBOBJ |  1459 |  2918 |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------

since which Oracle version this behavior is present?

I have no idea but I expect it's there for a looong time.

is it reliable?

100% reliable

when is the two column index prefered?

When you want to work with null records, too.
select distinct SUBOBJECT_NAME from SCOTT.T; can use the following index
create index SCOTT.IDX_T_SUBOBJ on SCOTT.T(1, subobject_name);

And why isnt it mentioned more often (for example this otherwise good answer does not mention it)?

The whole logic is quite clear once you know that indexes do not contain all-null records and that constraints are not used by the optimizer (as it seems to be the case).
If your index does not contain nulls and you ask for distinct on nullable column, you see, that index cannot be used (nulls are missing). If you select where col is not null, index can be used (all non-null values are there).


Get unique ids based on order by another column using group by with row_number Here is a link to the SQLfiddle. For pagination purpose, I am first executing a query to execute if's and order by's and return a list of ids, using which I query for all the data with contained lists for those ids. I am having a problem with order by in the first query, where it wont let me use order by in a nested statement(view). In short, a fiddle is provided. Using the query

SELECT req.reqId, req.RN from (
SELECT
    (ROW_NUMBER() OVER (ORDER BY A.requisitionId asc)) as RN,
    A.reqId as reqId
    FROM dbo.RequisitionToPatient B
    INNER JOIN dbo.Requisition A ON A.reqId = B.reqId
    INNER JOIN dbo.Orders C on C.reqId = A.reqId
) req
GROUP BY req.reqId, req.RN

I am getting

reqId   RN
----------
12      1
12      2
14      3
11      4
11      5
10      6
10      7
9       8
9       9
9       10
9       11

What I want is

reqId
-----
12
14
11
10
9

Try this:

select reqID FROM(
select req.reqId, req.RN from (
select
        (ROW_NUMBER() OVER (ORDER BY A.received desc)) as RN,
        A.reqId as reqId
        FROM RequisitionToPatient B
        INNER JOIN Requisition A ON A.reqId = B.reqId
        INNER JOIN Orders C on C.reqId = A.reqId
) req
group by req.reqId, req.RN
order by req.RN) AS a
  GROUP BY reqID

Output:

reqID
------
12
14
11
10
9


SSMS Timeout when browsing for sql servers? When connecting to a sql server instance one has the option of browsing the network for sql instances.  This used to work for (returning ~50 entries) us until we were migrated into a larger domain with a couple thousand machines, with the possibility of a few thousand sql instances.  It now searches for 2-3 minutes then the list is empty.

Does anyone know if there is a timeout on how long it will search?  If so, is the timeout configurable?

(I know 2-3k servers isn't useful to scroll through - but still need an answer)

The most likely reason for this is port 1434 UDP being blocked on the new network.

An in-depth analysis of how SQLBrowser messaging works can be found here: https://www.mssqltips.com/sqlservertip/2057/why-listing-all-of-your-sql-servers-does-not-always-work/
That article also lists other reasons for this issue, like the SQLBrowser service not running on the servers, but based on your description of the problem, it looks like the new network you are working on is isolated by a firewall that blocks UDP traffic on port 1434.


Find double qoutes inside a string which is in double quotes in mysql I'm storing a string that includes, by necessity, double quotes, e.g.:

ID           Data
1   "This is "my" example."
2   "This is my example."

I want to find only those row which are like ID 1. How can i do this.

"This is "my" example." (I know it's hard to tell the difference here)

Please suggest.

select * from table
where data like '"%"%"'

Or you could compare the length of data against the length of data when '"' is replaced with an empty string ('').


How to find admin user of pluggable database in Oracle 12c up I'm creating a script and I need to know what is the admin user of pluggable database. I don't find nothing about it in documentation.

Other question is, what happen if I drop a admin user of the pluggable database?  is there some problem?

CREATE PLUGGABLE DATABASE

  ADMIN USER

  Use this clause to create an administrative user who can be granted
  the privileges required to perform administrative tasks on the PDB.
  For admin_user_name, specify name of the user to be created. Use the
  IDENTIFIED BY clause to specify the password for admin_user_name.
  Oracle Database creates a local user in the PDB and grants the PDB_DBA
  local role to that user.

The admin user is just a local user with the PDB_DBA role.
Example:

SQL> CREATE PLUGGABLE DATABASE mypdb ADMIN USER balazs IDENTIFIED BY Oracle123 FILE_NAME_CONVERT=('pdbseed','mypdb');

Pluggable database created.

SQL> alter pluggable database mypdb open;

Pluggable database altered.

SQL> select grantee from cdb_role_privs where con_id = (select con_id from cdb_pdbs where pdb_name = 'MYPDB') and granted_role = 'PDB_DBA';

GRANTEE
--------------------
BALAZS
SYS


RIGHT() works in a SELECT but not in an UPDATE? I am trying to run a script which does some work including stripping the leading two characters from the string inside each row of a column. To do this I am using RIGHT() and it works fine when I use it in a SELECT..FROM but when I use that same code inside an UPDATE it fails with

  Invalid length parameter passed to the RIGHT function

why is it failing in the UPDATE but not the SELECT...FROM?

USE JobSight;

DROP TABLE #ReleaseTemp;

SELECT  A.MajRelease,
        A.MinRelease,
        A.Build,
        Split.a.value('.', 'VARCHAR(MAX)') AS ReleaseNotes
INTO #ReleaseTemp
FROM
        (
            SELECT  MajRelease,
                    MinRelease,
                    Build,
                    CAST ('<M>' + REPLACE(RelNotes, ';;', '</M><M>') + '</M>' AS XML) AS ReleaseNotes
            FROM JobsDB.dbo.ReleaseData
        ) AS A
    CROSS APPLY ReleaseNotes.nodes ('/M') AS Split(a);

UPDATE #ReleaseTemp
SET ReleaseNotes = RIGHT(ReleaseNotes, LEN(ReleaseNotes) - 2);

UPDATE #ReleaseTemp
SET ReleaseNotes = LTRIM(#ReleaseTemp.ReleaseNotes);

SELECT  MajRelease,
        MinRelease,
        Build,
        RIGHT(ReleaseNotes, LEN(ReleaseNotes) - 2) AS ReleaseNots
FROM #ReleaseTemp
WHERE #ReleaseTemp.ReleaseNotes <> '';

why is it failing in the UPDATE but not the SELECT...FROM?

Because in your SELECT you are filtering out the rows via WHERE #ReleaseTemp.ReleaseNotes <> ''. You do not have that filter on your UPDATE statement and you probably have at least one row that has a length of 0 or 1. Try making it:

UPDATE #ReleaseTemp
SET ReleaseNotes = LTRIM(RIGHT(ReleaseNotes, LEN(ReleaseNotes) - 2))
WHERE LEN(#ReleaseTemp.ReleaseNotes) > 1;

Rather than checking for <> '', I changed the filter to ensure that the length of the string is at least 2 characters. This avoids the situation in which the string has only one character which does not equate to an empty string '' but would still get that error when passing in a -1 (from length of 1 minus 2) to the RIGHT function.

Please note that I included the LTRIM() which you currently have as a second step in order to avoid that second DML operation.


Error creating index on PostgreSQL 9.1 I have a process where user A creates a table and user B tries to create an index on the new table, owned by A. Users A and B are from the same group but user B is an automated process.

Is it possible to create an index from a user that is not the owner of the table, but is in the table's owner group?

You will need to assign ownership of the table to the group the two users have in common.

Demo:

Setup:

CREATE ROLE thegroup;
CREATE USER user1 IN ROLE thegroup;
CREATE USER user2 IN ROLE thegroup;
CREATE TABLE t1 ( x integer not null );
ALTER TABLE t1 OWNER TO user1;

Without further changes, here's what happens if user2 tries to add an index on t1.x:

regress=# SET ROLE user2;
SET
regress=> CREATE INDEX t1_x_idx ON t1(x);
ERROR:  must be owner of relation t1

The solution is to grant the table ownership to the shared role. As user1 or a superuser:

ALTER TABLE t1 OWNER TO thegroup;

now:

regress=# SET ROLE user2;
SET
regress=> CREATE INDEX t1_x_idx ON t1(x);
CREATE INDEX
regress=>


How do I automatically set meta commands when starting psql? When I start a psql session, I would like certain meta commands to already be set.

For instance:

\x auto
\set ON_ERROR_STOP on

I was hoping to find an rc or config file but couldn't. The command line options don't seem comprehensive either.

You are looking for the .psqlrc file. Per documentation:

  psqlrc and ~/.psqlrc

  Unless it is passed an -X or -c option, psql attempts to read and execute commands from the system-wide startup file (psqlrc) and then the user's personal startup file (~/.psqlrc), after connecting to the database but before accepting normal commands. These files can be used to set up the client and/or the server to taste, typically with \set and SET commands.


SQL Server - How is a row updated under the hood? When SQL Server updates a row in a table, is the update always done 'in place', or is the row deleted first and then re-inserted? If the update is done 'in place', is this always the case, or are there situations where a delete is done first and then an update?

We have a developer on the team that is saying that when a table has a unique constraint on it, and it's included in replication (transactional replication), a delete is always done first and then an insert. I wanted to verify if this is ever the case.

SQL Server 2008 R2

I'm not a replication guru, but record may be marked as deleted only in case when an update is generating Page Split.

For the case of replication you can test it by looking at DBCC PAGE and look through transaction log.


Audit MariaDB Which User Account Privileges Are Being Used I'm looking to improve the security of my MariaDB installation by better auditing the user accounts and the privileges of each user account.

This database is being provided as a service out to many other users within the enterprise, and in some cases people leave the company or didn't actually need the level of privileges they requested. I'd like to audit the user accounts and see when they last logged in (to make sure users are logging in at least once every 90 days) and auditing the privileges their account has been granted.

Specifically, if possible, I'd like to look which privileges haven't been used within the past 90 days so that I can either alert our service team or (in the future) automatically revoke privileges which are not being used (with the exception of some of the disaster recovery accounts or security teams).

Any guidance on how to proceed with identifying account usage either in the general account access or in the more detailed privilege usage would be greatly appreciated.

The audit plugin, when enabled, will provide the access information associated with users accounts.

There are also authentication plugins to centrally manage authentication.


Sort Warnings, finding out the query behind them I'm troubleshooting a SQL Server 2012 that has started timing out as of a few weeks ago.

The server is filled with thousands of sort warnings, at a rate of about 15 warnings per minute.

To get the sort warnings I used the following query:

DECLARE @log NVARCHAR(1000)

SELECT
    @log = Substring(PATH, 1, Len(PATH) - Charindex('\', Reverse(PATH))) + '\log.trc'
FROM
    sys.traces
WHERE
    id = 1

SELECT
   g.TransactionID, c.name as category, e.name as event,
   g.hostname as servername, g.databasename as using,
   g.sessionloginname as login, g.starttime, g.textdata as query
FROM
   ::fn_trace_gettable(@log, 0) as g
inner join
   sys.trace_events e on g.eventclass = e.trace_event_id
inner join
   sys.trace_categories as c on e.category_id = c.category_id
where
   c.type not in ('0')
order by
   starttime desc

However the textdata column is always empty.

I'm almost certain that the sort warnings are originating from old stored procedures. However I can't find out the exact cause, as there are over 100 stored procedures being triggered every minute, how would I go about finding the query that's behind the warnings?

Update:

SELECT
    p.name, p.object_id, p.create_date, p.modify_date,
    s.last_elapsed_time, s.last_worker_time,
    (s.last_elapsed_time - s.last_worker_time) as time_difference,
    s.execution_count, last_execution_time
FROM
    SYS.dm_exec_procedure_stats as s
INNER JOIN
    sys.procedures as p on p.object_id = s.object_id
WHERE
    database_id = 11 AND s.last_worker_time < s.last_elapsed_time
ORDER BY
    time_difference desc

Shows me that a few procedures (3) have last_elapsed_time of 200'000+ and worker time much lower. One stands out as being executed 15'000+ times, while having 400'000+ longer elapsed_time than worker_time. And this query does indeed sort on a rather large table.

Could someone tell me if what I'm looking at is a possible cause of the sort warnings, or if I'm going in circles.

You can tweak your query slightly to get a count of 'SpillToTempDb' i.e. Sort warnings, which would show you if any of the queries you're looking at could be an issue.

WITH
XMLNAMESPACES (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
p.name,
p.object_id,
p.create_date,
p.modify_date,
s.last_elapsed_time,
s.last_worker_time,
(s.last_elapsed_time - s.last_worker_time) as time_difference,
s.execution_count,
last_execution_time,
Query_Plan.value('count(/ShowPlanXML/BatchSequence/Batch/Statements/*/QueryPlan/Warnings/SpillToTempDb)', 'int') TempDbSpillWarnings
FROM SYS.dm_exec_procedure_stats as s
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS deqp
INNER JOIN sys.procedures as p on p.object_id = s.object_id
where database_id = 11 and s.last_worker_time < s.last_elapsed_time
order by time_difference DESC

Otherwise, since you are on SQL Server 2012, I would recommend running an Extended Event for an hour or so. There's a good guide on doing so half way down this tutorial.


Custom CSV "report" in SQL I have two SQL tables of data extracted from a ERP (invoices)
the first contains the "head" of the invoice (invoice_number, date, customer, ...)
the second table contains the "rows" of all invoices (good_description, qty, price, ...)

To simplify:

  T1.ID  |  T1.COL_A | T1.COL_B           T2.T1_ID |  T2.COL_A | T2.COL_B
  -----------------------------           -------------------------------
   1     |    AAA    |    BBB                 1    |    eee    |    fff
   2     |    CCC    |    DDD                 1    |    ggg    |    hhh
                                              2    |    iii    |    jjj
                                              2    |    kkk    |    lll

If I make a SELECT joining two tables by ID field it would give:

           T1.COL_A  |  T1.COL_B  |  T2.COL_A | T2.COL_B
          ----------------------------------------------
   1          AAA    |    BBB     |    eee    |    fff
   1          AAA    |    BBB     |    ggg    |    hhh
   2          CCC    |    DDD     |    iii    |    jjj
   2          CCC    |    DDD     |    kkk    |    lll

Ok quite simple.

The problem is that I need a specific format to import the data in another ERP, a kind of CSV format made like this:

    ROW_TIPE    ;  T1.COL_A  ;  T1.COL_B  ;  T2.COL_A ; T2.COL_B
----------------------------------------------------------------
  INVOICE_HEAD  ;     AAA    ;    BBB     ;           ;
  INVOICE_ROW   ;            ;            ;    eee    ;    fff
  INVOICE_ROW   ;            ;            ;    ggg    ;    hhh
  INVOICE_HEAD  ;     CCC    ;    DDD     ;           ;
  INVOICE_ROW   ;            ;            ;    iii    ;    jjj
  INVOICE_ROW   ;            ;            ;    kkk    ;    lll

So the rows are "grouped" and not repeated and at the first column is inserted the type of row to be processed by the ERP importing the data (if it's INVOICE_HEAD or INVOICE_ROW)

What's the best way to achieve this result in SQL?

The data is from a Pervasive Btrieve. I managed to extract the invoice data from that in two CSV files (one for each table). Then I imported in a SQLite to make the manipulation needed to get a format that is possible to import in the new ERP software.

May I suggest to use views?  Consider the following tables (and test data):

-- T1
create table ihead (
  id number primary key
, col_a varchar2(32)
, col_b varchar2(32)
);

-- T2
create table irow (
  headid number references ihead(id)
, col1 varchar2(32)
, col2 varchar2(32)
, irowid number primary key
);

In the second table (that you have called T2), there should the rows should be numbered somehow, otherwise it will be hard to get to invoice details into the correct order - as @Bill has hinted.

-- test data
begin
  insert into ihead values (1, 'AAA', 'BBB');
  insert into ihead values (2, 'CCC', 'DDD');
  insert into irow values (1, 'eee', 'fff', 1);
  insert into irow values (1, 'ggg', 'hhh', 2);
  insert into irow values (2, 'iii', 'jjj', 3);
  insert into irow values (2, 'kkk', 'lll', 4);
end;

I'd create a join first, and store it as a VIEW, like so:

-- join, similar to the first select you have written

create or replace view alldetails
as
select
  H.id, H.col_a, H.col_b, R.col1, R.col2, R.irowid
from
  ihead H join irow R on id = headid;

Check the output:

select * from alldetails;
-- output
ID COL_A  COL_B  COL1  COL2  IROWID
1  AAA    BBB    eee   fff   1
1  AAA    BBB    ggg   hhh   2
2  CCC    DDD    iii   jjj   3
2  CCC    DDD    kkk   lll   4

Second view: get all INVOICE_HEADS (first select), then INVOICE_ROWS (second select)

create or replace view idetails (c1, c2, c3, c4, irow)
as
select 'INVOICE_HEAD', col_a, col_b, col1, col2 from alldetails
where irowid in (
  select min(irowid) from iheader group by id
)
union
select 'INVOICE_ROW', null, null, col1, col2 from alldetails
where irowid not in (
  select min(irowid) from iheader group by id
)

Now we can just:

select * from idetails order by irow
-- output
C1              C2      C3      C4      IROW
INVOICE_HEAD    AAA     BBB     eee     fff
INVOICE_ROW     -       -       ggg     hhh
INVOICE_HEAD    CCC     DDD     iii     jjj
INVOICE_ROW     -       -       kkk     lll

Get to the final output by executing:

select
  c1 ||';'|| c2 ||';'|| c3 ||';'|| c4 ||';'|| irow
from idetails
order by irow

-- final output (some padding added manually,
-- but can be done with RPAD or LPAD)

INVOICE_HEAD ; AAA ; BBB ; eee ; fff
INVOICE_ROW  ;     ;     ; ggg ; hhh
INVOICE_HEAD ; CCC ; DDD ; iii ; jjj
INVOICE_ROW  ;     ;     ; kkk ; lll

UPDATE 2017-05-17 (requirements have changed)

-- leave the first view as it is,
-- change the second view to be:
create or replace view idetails (
  c1, c2, c3, id, c4, c5, irowid )
as
select distinct
  'INVOICE_HEAD', col_a, col_b, headid, null, null, 0
from alldetails
union
select
  'INVOICE_ROW', null, null, headid, col1, col2, irowid
from alldetails;

Then, select all the columns you need (notice: 2 columns in the ORDER BY clause):

select
  c1 ||';'|| c2 ||';'|| c3 ||';'|| c4 ||';'|| c5
from idetails
order by id, irowid;

Output (padding added by hand, just for clarity):

INVOICE_HEAD;AAA;BBB;   ;
INVOICE_ROW ;   ;   ;eee;fff
INVOICE_ROW ;   ;   ;ggg;hhh
INVOICE_HEAD;CCC;DDD;   ;
INVOICE_ROW ;   ;   ;iii;jjj
INVOICE_ROW ;   ;   ;kkk;lll


Could not find prepared statement with handle -1 SQL Server Profiler So I have this issue where I'm running Sql Server Profiler and I'm getting hundreds(at least) of Could not find prepared statement with handle -1.(error 8179) per second.

I can't seem to really correlate the errors with anything specific except some cached prepared statements I think.

I'm using Linq to Sql.

If you need more info just let me know.

Well I figured it out.

Log4Net's AdoAdapter was keeping a prepared statement saved through the duration of its connection, and my plan cache was flushing them out sometimes.


SET NOCOUNT Error in handling SQL call after upgrade We are upgrading our test environment with a new server and updated version of Microsoft SQL Server and have run into an issue.

On the new server, our old code will get "operation is not allowed when the object is closed" when executing some stored procedures.  This message never appeared on the old server.  When we tracked it down, the issue can be resolved by adding SET NOCOUNT ON; to the stored procedure.

I looked at the defaults on the database and saw no settings that were different (SQL Server 2008 vs SQL Server 2014) related to defaults.

What setting should I be looking at to resolve this globally without needing to add SET NOCOUNT ON to a thousand stored procs?

SQL Server Configuration has an option called, appropriately, user options, which can be set using the sp_configure system stored procedure.  I wrote a blog post on SQL Server Science showing how to inspect and set the user options.

In brief, you can get the "config value" from the old server, using this:

EXEC sys.sp_configure 'user options';

Then, set the new server to use the same options via this:

EXEC sys.sp_configure 'user options', <config value>;
RECONFIGURE

(replace the <config value> with the value from the old server).


MySQL failures after changing innodb_flush_method to O_DIRECT and innodb_log_file_size We changed innodb_flush_method to O_DIRECT, innodb_log_file_size to a bigger value, and 3 other things (innodb_buffer_pool_size, innodb_additional_mem_pool_size, innodb_log_buffer_size) while MySQL was stopped. Unfortunately DirectAdmin restarted MySQL before the log files were deleted.

Later, when we tried to fix the situation and stopped MySQL (and stopped DA) we deleted log files it didn't help - databases were corrupted. We finally created a new instance, and recovered everything from backups.

We had errors like these:

2013-09-06 01:40:24 7ff777a0c700 InnoDB: Error: page 4 log sequence number 40425569713
2013-09-06 01:40:24 7ff777a0c700 InnoDB: Error: page 3 log sequence number 40425569736
2013-09-06 01:40:24 7ff777a0c700 InnoDB: Error: page 28 log sequence number 40425569736
2013-09-06 01:40:24 7ff777a0c700 InnoDB: Error: page 4 log sequence number 40425542325

Can someone tell me what really happened? We've seen bad things happening when you change innodb_log_file_size and don't delete log files, but we were able to recover from it then.

Was setting innodb_flush_method to O_DIRECT the cause here?

The source of the problem was removing the log files before flushing everything out of the logs
You should run this to shut it down (using innodb_fast_shutdown)
mysql ... -ANe"SET GLOBAL innodb_fast_shutdown = 0"
service mysql stop

After shutting down like this, then you can put in new logs. That way, there is nothing in terms of InnoDB Crash Recovery to worry about from the old logs.
InnoDB Architecture

What innodb_flush_method does is have the InnoDB storage engine cache any needed blocks to the double write buffer (See the chart). The error messages you have are about the  sequence numbers from the logs being misaliged because of the incomplete way you made new logs.


Effects of monolothic databases on performance and security? Please note: although I'm specifically calling out MySQL here, I would also appreciate/entertain generic answers that apply to all/most relational systems.

As a developer I don't see the design benefits of grouping tables into different databases, especially if all the tables are somehow connected/related to each other. From a performance perspective, to me (a dumb developer), why should it matter if I have 100 tables located inside a single database, or 10 databases with 10 tables in them?

So I ask: Are there performance/security/other benefits to decomposing a monolithic database into smaller ones? What are they?

Also, what if I have hundreds of tables, and they are literally all somehow linked? Meaning, there are no completely isolated tables in the entire system; all of them have foreign keys in them to some other table. Doesn't this mean I have to have only 1 big/monolithic DB?

for security, you want your data duplicated on many servers as possible, so if one falls, the others can share the load.

"classic" oltp databases performance issues raise from concurrent transactions. Due mainly to locks, time/transaction grows exponentially. Hence, a simple way to mitigate this issue is to have multiple databases, as there will be less concurrent transactions by database. Thus, this is an attractive duct tape (cheap and easy, at least compared to alternatives).

One thing to consider is that historically the bottleneck has been storage (disks), so databases tend to minimize disk access by consuming lot of cpu and memory. As well, partitioning tables helps reduce the load on disks.

As well, multiple databases allow to cache most of it in memory, seriously improving performance too.

There are other options. Some databases can use multiple machines, or you can pool machines in a virtual one, but that only goes so far too.

The current trend is to split oltp and olap database. This make sense, oltp require fast row "access", olap require high throughput column reads.

Another trick is to have two databases, one in read/write, and one in read only mode. They are in a master/slave configuration. Most reads are done one the slave, and changes on the masters are pushed to the slave. This alleviate the burden on the master database, which deal with transactions.

If you observe your database carefully, you will notice that very few operations really need acid transactions. That when it might be interesting to leave the classical model behind, and build two or three systems. By example one for tickets (which needs transactions as you don't want to sell out of stock products), and another system for the rest. If you update a product price and the old price is used for 0.1 second, it does not mater much (except for an auction system of course). Point is, with a traditional system, you can't sell tickets while you update the price, as the row will be locked, and it will be unavailable for much longer (1-10 second), as your traditional db (which is now massive) is much slower.

Typically the transactions db will be "in memory" (based on kernel locks, or multi-paxos), and the read one in a "nosql" db for 99% of the rest. The in memory logs are then merged to the nosql database. Combined with other tricks it scales linearly, and hence can grow in ways traditional databases can difficultly achieve.

This is more complicated, require analysis, learning new paradigms, and massive time to implement. To my knowledge, it is used mainly by "giants" like google or twitter mainly. This is the basis of the "big data" movements.

If your company uses a traditional sql database, you better off using all the tricks available (sharding, partitionning, read/write dbs, vm) before changing your full echosystem.


CROSS JOIN with temp table and CASE statement I'm trying to CROSS JOIN a table with a view. Here is my code:

 SELECT *
 FROM   ( SELECT    * ,
                    CASE TypeId
                      WHEN 1 THEN InGs828S
                      WHEN 2 THEN InGS828H
                      WHEN 3 THEN InGS828L
                      WHEN 4 THEN InEasyFlowWD
                    END AS InDevice
          FROM      dbo.Ports
        ) tempTable
        CROSS JOIN dbo.vwDevices
WHERE InDevice=1

I get Msg 207, Level 16, State 1, Line 4 Invalid column name 'TypeId'.  TypeId is a column in view vwDevices.

My question is why can't I use any columns from vwDevices in CASE *ColName*, while I can use columns from table Ports?

You can do it the other way around:

SELECT *
 FROM  dbo.vwDevices AS v
CROSS APPLY ( SELECT    * ,
                    CASE v.TypeId
                      WHEN 1 THEN InGs828S
                      WHEN 2 THEN InGS828H
                      WHEN 3 THEN InGS828L
                      WHEN 4 THEN InEasyFlowWD
                    END AS InDevice
          FROM      dbo.Ports
        ) AS tempTable
WHERE tempTable.InDevice=1


Is the runtime of checking for duplicate primary keys in SQL Server O(n)? I was curious and I couldn't find too much info on this. Is it also O(n) for checking for duplicates among primary keys?

Is it the same for most other SQL databases?

Is there a place that lists this info?

A primary key in SQL Server is always backed by an index (in fact, a B-tree for non-Hekaton tables). An index allows for O(log N) lookup (and duplicate checking).

In practice it's hard to measure the difference to O(1) behavior even if you aim at it. The upper index levels tend to be cached and the tree is very flat. The maximum tree level in SQL Server is 15 levels (you need to load the index pages with ~900 byte keys for that to force minimum fanout).

Each index row must be 807 bytes in size to fit at most 9 of them on a page. Then, the index size on level L is 807*9^L. L starts at 1. L == 15 => 807*9^15/10^12 = 166154 TB. Maximum database size: 524,272 TB. So the maximum level is 15.


Can this SQL cause unclosed quotation mark? I've been tracking a bug in how single quotes/apostrohpes are handled in an application, and I've found a suspect in a database trigger.

That trigger contains code like this: (shortened and anonymized)

insert into logTable ([NewRowData])
select
  (select isnull(convert(nvarchar(max), [Foo]),'NULL') as Foo
   from inserted i where  i.MyID = u.PrsID for xml raw('NewRowData'))
from @updated_row u

In production, I get errors when "Foo" contains a single quote, (For example "O'Hara")

  Incorrect syntax near 'Hara'. Unclosed quotation mark after the
  character string ',NULL)'.

However, I cannot replicate this. A manual insert/update that fires the trigger does not give an error.

Is is possible that the nested functions isnull and convert somehow messes up quotation escapes or am I looking in the wrong direction?

(Cannot find any dynamic SQL genereation anywhere, which naturally was my first suspicion.)

Database is SQL server 2012.

Addendum:
I did a similar test to what Vérace posted below before posting here with the same result i.e no exception. If there IS a bug, it must be dependant on database version, settings or query execution plan.

No - it is NOT your SQL!

To answer this, I did the following:

CREATE TABLE test
(
  num INTEGER,
  name VARCHAR (50)
)

INSERT INTO test VALUES (1, 'Paoul');
INSERT INTO test VALUES (2, 'Mary');
INSERT INTO test VALUES (3, 'O''Hara');
INSERT INTO test VALUES (4, NULL);

then,

SELECT * FROM test;

gives:

num     name
___     _____
1       Paoul
2       Mary
3       O'Hara
4       (null)

And then ran:

SELECT ISNULL(CONVERT(NVARCHAR(MAX), name), 'NULL') AS Foo FROM test;

And got the result:

Foo
____
Paoul
Mary
O'Hara
NULL        <----- String literal, not the value NULL!

Which is what you'd expect! Quite why one would want to insert the string literal NULL is beyond me - Hmm... Developers: "three-value logic, my head is melted - I'll just stick in the string and we can do an OR!". Or, was this system in Oracle at one point? Empty string and NULL string being the same thing?

So, whatever your problem is, it is not your SQL as shown above causing this problem! Check the SQLFiddle here (note: using MS SQL Server 2014!).

As for your point about it being a version bug - I find it difficult to believe that such a basic functionality would be buggy in a 2012 Server version. Such a bug would have broken loads of legacy code!

My hunch is that the problem lies somewhere with the XML part of the code and the bits we can't see. I would set up logging (of all queries) and check out the SQL text that is actually being sent to the server (on my test system).


sys.dm_sql_referenced_entities fails to provide used columns list when joining with temp table I think I need some help as I run out of ideas.

In my database I have a simple table like this:

CREATE TABLE dbo.Invoices
(
        CashDiscounts MONEY,
        Cost MONEY
)
GO

and a stored procedure that uses this table in SELECT context:

CREATE PROCEDURE Reporting.pLoadTest
AS
BEGIN
        CREATE TABLE #t ( Test INT );

        SELECT
                  Invoice.[CashDiscounts]       AS [CashDiscounts]
                , Invoice.[Cost]                        AS [Cost]
        FROM dbo.Invoices AS Invoice
        LEFT OUTER JOIN #t -- if you remove this join, dm_sql_referenced_entities should return also CashDiscounts and Cost attributes
        ON 1=1

END
GO

Now, the problem I got is following: I am using system function sys.dm_sql_referenced_entities to get list of columns used by Reporting.pLoadTest.

SELECT *
FROM sys.dm_sql_referenced_entities ('Reporting.pLoadTest', 'OBJECT')

I expect the results of this function to be:

dbo.Invoices
dbo.Invoices.CashDiscounts
dbo.Invoices.Cost

However, when I do a join to temp #t table function sys.dm_sql_referenced_entities returns only

dbo.Invoices

Everything in this case works fine if I use table variable instead of temp table. I tried to fiddle with permissions for tempdb as written in documentation

  Requires SELECT permission on sys.dm_sql_referenced_entities and VIEW
  DEFINITION permission on the referencing entity.

but with no positive result.

What should I do to get correct results of columns being used by a stored procedure that do a select and a join to temp table?

This is a bug in SQL Server and here is a Connect item to vote for if you want a change.

dm_sql_referenced_entities does not shows columnes when temporary tables are used in statement

Current status:

  We believe it is unlikely that we will address this suggestion, and so
  we are closing it as “won’t fix”.


MongoDB 3.0 - how do I check which storage engine is being used? I have MongoDB 3.0.0 running on a Digital Ocean droplet.  How can I tell which storage engine it is using?

You can find it through db.serverStatus() on section storageEngine -
http://docs.mongodb.org/manual/reference/command/serverStatus/#storageengine


SQl connection to XAMP i am trying to connect my database to XAMP . I got  this error

Warning: Use of undefined constant ID - assumed 'ID' (this will throw an Error in a future version of PHP) in C:\xampp\htdocs\Thairis\login.php on line 8

Fatal error: Uncaught Error: Call to undefined function mysql_connect() in C:\xampp\htdocs\Thairis\connectdb.php:27 Stack trace: #0 C:\xampp\htdocs\Thairis\login.php(11): include() #1 {main} thrown in C:\xampp\htdocs\Thairis\connectdb.php on line 27

mysql_* functions

officially deprecated as of PHP 5.5 (released June 2013).
removed entirely as of PHP 7.0 (released December 2015)

So switch to
mysqli or pdo and use always prepared statements with parameters because of sql injections

see api comparison


SSMS 2012 mutiple query windows using same session I wonder if it is possible to connect multiple files or query windows in SSMS to one session? The reason is that I want to use local temporary tables that are created in one file/query-window in other file/query-windows. As local temporary tables have session scope this seems to be the only way to achieve this.

We use the temp tables for separating session data in our application. I use SSMS for development and in the development context it would be useful to have the same tmp tables available in multiple SSMS windows.

What i do now is copy the creation code of the tmp tables in all query windows, then evaluate that part and then cut them out again. So they are avaible in that session, but that's a bit cumbersome. So basically it is an SSMS question and not a SQL Server question.

It is not possible to share a local temporary table between connections.

Local temporary tables are scoped to a single session, but global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

Global temporary tables have two number signs (##) as the first characters of their names, for example:

CREATE TABLE ##GlobalTemp
(
    c1 integer NULL
);


Pass a huge dataset to a query I have a huge set of employee ids (1000 to 10000) which I can't pass through IN clause. I don't want to use a Global Temporary table since I don't have write access in the DB (only read access).

What is the best way to pass the data to the query?

SELECT EMPLOYEE_NAME,AGE,SALARY
from EMPLOYEE
WHERE EMPLOYEE_ID IN (???)

After some digging around it does seem like write access can be granted but the constraint here is there should be no leftovers i.e no empty temp table lying around after the transaction is done.

Going with the temp table solution and dropping it at the end of the transactions.


Connecting to monogdb with string I'm trying to connect to MongoDB using a MONGO_URL:. This particular database has a username and password. I've created a user to allow readWrite commands and that's the one I'm using.

If I log into the database and use show dbs this is what appears:

admin         0.000GB
myspecial_db  0.000GB
local         0.000GB

I'm trying to access the myspecial_db.

MONGO_URL: mongodb://username:password@private-network-ip:27017/myspecial_db

When I try and launch my app I get this error in terminal.

MongoError: failed to connect to server [private-network-ip:27017]

What am I missing here?

As mention your error like

  MongoError: failed to connect to server [private-network-ip:27017]

This error is what you would see if the mongo shell was not able to talk to the mongod server.

As per MongoDB documentation Connection String URI Format The URI format for defining connections between applications and MongoDB instances in the official MongoDB drivers.

The following is the standard URI connection scheme:

mongodb://[username:password@]host1[:port1][,host2[:port2],...[,hostN[:portN]]][/[database][?options]]

For further your ref here


Running sp_AskBrent with @ExpertMode = 1 in PowerShell I am trying to run sp_AskBrent from PowerShell using Invoke-SQLCmd and capture its output in a variable:

$query = "EXEC SAIDBA.monitoring.sp_AskBrent @Seconds=10;"
$check = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $query
             -ErrorAction Stop -ConnectionTimeout 3

When running with ExpertMode = 0, no problem.

But when running with ExpertMode = 1, we notice three things:

It outputs every data to the shell
$check is null
It ends with the following error:

  Invoke-Sqlcmd: Duplicate column names are not permitted in SQL PowerShell. To repeat a column, use a column alias for the duplicate column in the format Column_Name AS New_Name.
  At line:1 char:18

  $check = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $query -Er ...
  CategoryInfo: SyntaxError: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
  FullyQualifiedErrorId : DuplicateColumnNameErrorMessage, Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

I think there is a workaround of providing parameters for sp_AskBrent to store the ExpertMode data into tables and select from those tables afterwards, but I want to make sure there is no way to get everything back at once in PowerShell.

Brent here. Correct, @ExpertMode = 1 turns on multiple result sets - diagnostics, wait stats, file stats, and perfmon counters.

If you only want one result set, don't turn on @ExpertMode.

If you want multiple result sets, but your application (in this case, PoSH) can't consume them, you'll need to log them to tables. That's where these parameters come in:

@OutputDatabaseName - the name of the database you want to write into. Must already exist.
@OutputSchemaName - the schema where your tables will live, like dbo. Must already exist.
@OutputTableName - the table name for the top result set (diagnostics). This parameter also requires the above two. If the table doesn't already exist, it will be created.
@OutputTableNameFileStats, @OutputTableNamePerfmonStats, @OutputTableNameWaitStats - the rest of the result sets will be written here. If each table doesn't already exist, it will be created. Each of these is totally optional - you can pass in none, some, or all of them.

If you want it to work differently, describe what you need, and I'll see if I can come up with a way to get it. Hope that helps!

From a comment:

  I was actually thinking about writing a health check script which I would run in case of incident or for monitoring and which would parse the output of sp_AskBrent.

OK, cool. Just a few things to keep in mind - the tables don't clean themselves out, and they can get big if you use the @SkipChecksQueries = 0 parameter, for example. The sp_AskBrent® licensing also prohibits distribution, so just make sure you're not bundling the script, or installing it on servers you don't own. Otherwise, go for it! If there's anything I can do to help make it easier, holler.

There's a date field in all of the output tables to help you maintain history tables.


how to update records else insert records in postgresql How to perform update and insert in same query? i need to update the record, if it is not present then it has to perform insertion i tried this

UPDATE bank SET  Address= $2,"PANCard"= $3,"IFSC" = $4  WHERE user_id = $1;
INSERT INTO bank (user_id,bank_details,"PAN",bank_acc,"UAN",tax)
       SELECT  '$2','$3','$4','$5','$6'
       WHERE NOT EXISTS (SELECT * FROM bank WHERE user_id=$1);

but am getting

{
  "name": "error",
  "length": 123,
  "severity": "ERROR",
  "code": "42601",
  "file": "postgres.c",
  "line": "1274",
  "routine": "exec_parse_message"
}

please help me here

You can use the UPSERT:

https://www.postgresql.org/docs/9.5/static/sql-insert.html

Example from documentation:

INSERT INTO distributors (did, dname)
VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;


Estimate end time for a long update I have currently running a long update (~million rows based on a postgis st_within condition) that was expected to finish in an hour or so based on similar previous queries. However, it's been three hours already; system monitoring shows that it's pegged a single CPU at 100% for that time.
Is there any way to view the progress of this update, to estimate when it is expected to end?

I know this question is old but I monitore all my updates in postgresql including updates inside transactions.. So I decided to help!
1- First create a sequence named myprogress for example.
create sequence myprogress;

2- Make your update to hit the sequence - now you are free to start a transaction:
For example if you have this update:
update mytable set c=3 where a=0 and b=1;

Just rewrite  it to (plus this and condition):
update mytable set c=3 where ( a=0 and b=1 ) and  NEXTVAL('myprogress')!=0;

**You are ok to run this update inside a transaction, as sequences are affected globally. **
Note that this will not significantly affect performance.
3- Monitore your progress. Now you can just connect in another session (or a transaction) and select your sequence (sequences are affected globally , so you will see the value in the another sessions):
select last_value from myprogress;

And you will see how many lines are affected by your query until now, with that you can estimate how many lines by second are hit by your update.
And in most of the cases how many time you will need to wait...
4- At end just restart the sequence:
alter sequence myprogress restart with 1;

So you can use it again - But carefully, to do not trust this if two users use the same sequence. If you are in doubt better if you have your own progress sequence with permissions only for you.
You can use it for slow SELECTs, DELETEs, and for some INSERTs too !


Cannot do a restore using PgAdmin4 after a new install of PostgreSQL - pg_restore.exe file not found I am new to PostgreSQL. I installed the software. Almost everything works. Then I tried to follow a tutorial for dvdrental. All instructions for using from command line worked. Then there is instruction for creating a DB using pgAmdin 4, which also worked. The next instruction is to do a restore. This fails. The error message is 'C:\Program Files\PostgreSQL\13\pgAdmin 4\runtime\pg_restore.exe' file not found. Please correct the Binary Path in the Preferences dialog.
The message is correct. pg_restore.exe is not in the runtime folder, the runtime folder does not exist. There is a bin folder and pg_restore.exe is that direction.
I tried various things such as changing "EDB Advanced Server Binary Path", I also created folder runtime and copied everything that was in bin folder to runtime, but it still will not work. This looks like an installation problem. Same message seemed to have appeared for years, but none of those solutions have worked for me. I would appreciate any help.

I was able to fix (or patch) the problem by coping files.

Searched for pg_restore.exe from folder 'C:\Program Files\PostgreSQL\13'. I found the file in 'C:\Program Files\PostgreSQL\13\bin'.
Then I copied all the files from 'C:\Program Files\PostgreSQL\13\bin' folder to 'C:\Program Files\PostgreSQL\13\pgAdmin 4\runtime' where the error message says file is missing. Note: After install, there was no runtime folder. I had to create it.
The error went away and restore seems to work. I am not sure this is the right solution, but for now, it works.


Mysql 5.7 cannot start again For the first time in my life, I have Ubuntu, so I do not know much how to manage and what should I be looking for; I'm using version 18.04.
I installed MySQL 5.7, it worked great for a couple of days and suddenly it can't even restart, doesn't start again (with service mysql start or anything)...

Using systemctl status mysql.service I get:

● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor
preset: enabled)
Active: failed (Result: exit-code) since Thu 2018-07-26 14:24:49 CEST;
10min ago
Process: 3758 ExecStart=/usr/sbin/mysqld --daemonize --pid-
file=/run/mysqld/mysqld.pid (code=exited
Process: 3749 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre
(code=exited, status=0/SUCCESS)

јул 26 14:24:49 maax systemd[1]: mysql.service: Service hold-off
time over, scheduling restart.
јул 26 14:24:49 maax systemd[1]: mysql.service: Scheduled
restart
job, restart counter is at 5.
јул 26 14:24:49 maax systemd[1]: Stopped MySQL Community Server.
јул 26 14:24:49 maax systemd[1]: mysql.service: Start request
repeated too quickly.
јул 26 14:24:49 maax systemd[1]: mysql.service: Failed with
result 'exit-code'.
јул 26 14:24:49 maax systemd[1]: Failed to start MySQL Community
Server.

and using journalctl -xe I get:

јул 26 14:24:49 maax systemd[1]: mysql.service: Scheduled
restart job, restart counter is at 5.
-- Subject: Automatic restarting of a unit has been scheduled
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Automatic restarting of the unit mysql.service has been scheduled,
as the result for
-- the configured Restart= setting for the unit.
јул 26 14:24:49 maax systemd[1]: Stopped MySQL Community Server.
-- Subject: Unit mysql.service has finished shutting down
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Unit mysql.service has finished shutting down.
јул 26 14:24:49 maax systemd[1]: mysql.service: Start request
repeated too quickly.
јул 26 14:24:49 maax systemd[1]: mysql.service: Failed with
result 'exit-code'.
јул 26 14:24:49 maax systemd[1]: Failed to start MySQL Community
Server.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Unit mysql.service has failed.
--
-- The result is RESULT.

What should I do to fix this and continue to use Mysql every day normally?
Mysql works okay when I reinstall it, but after some time, it crushes again...

The error log of MySQL records the details why MySQL can not restart, check the error log and update it in Questions would be helpful.

If MySQL is installed by apt-get or some other system's ways and uses the default configurations, error log always in /var/log/mysql.

If my.cnf changes, check the modified my.cnf in /etc/mysql/my.cnf or other paths to find the log_error param.


Will creating an index improve performance on a full scan Will adding an index on a Large data warehouse table (8Million rows, 200+ column) make any difference to the query time if all I am doing is selecting all rows from the table ?
Thanks

Currently your data is stored as either a heap or a clustered index, with all of it that structure.

If your data is in a heap and you create a clustered index, then you will reorganise all that data into a tree structure, and you will leverage some of the nicer aspects of clustered indexes, such as ordered data, and (unlike heaps) data without forwarding records (which can slow down scans). The data will be about the same size though.

If you create a non-clustered index, then this is a COPY of some of the data. This can help if you're getting only a subset of the columns or rows. If your non-clustered index doesn't include all the columns that you need for your query, it probably won't get used. But if you're only wanting three columns in your query, and your index has all three, you can save quite a bit of effort. You might get 100 times as many rows onto each page on disk, and scanning this index should therefore take just 1% of the effort.

Having indexes is just like in paper-based systems. If all you need is a list of names, then you can put all that on a single page instead of using the filing cabinet that has a dossier on each person. You'll have to maintain that list, but it might be worthwhile with the savings in other areas.


Altering Column Size On a Super Large Table I have a table with a column of type NVarChar(Max).

I didn't take good precautions before allowing users to add data so right now I have more than 2.5 billion rows and the database is becoming super large.

I'm trying to alter the column to change the size to a fixed length where records > that width should be deleted.

This is not an indexed column.

I tried LEN(Text)>width but this is a very slow function as it would scan billions of rows.

We've also experimented later with changing Int to BigInt by creating a new table and moving data there but it took a week or so.

Alter Column would freeze the system.

Can you kindly suggest any other way or suggest the method you feel is best?

The database is hosted on Azure SQL

Thank you so much :)

The best method is always specific to your requirements and situation. Some approaches might be faster than others but require more temporary space or disallow end users from querying the table. Based on what you've said in your question I'm going to assume the following:

Your primary concern is reducing space and you don't want to have to grow your database to perform this operation.
Azure SQL limits your options in moving the data to a new database or filegroup.
You want the table to remain online for end user queries during the maintenance operations.
The table has a clustered index index.

One approach to fix it:

Immediately make changes in the application to prevent end users from loading new data that's above your desired maximum length. No reason to let the problem get worse while you figure out what to do.
Verify that your desired maximum length will free up enough space. If a simple COUNT_BIG(*) is too slow you can use TABLESAMPLE and a bit of math to figure out how many rows you'll delete. I created a 100 GB table on my local machine and running a 1% sample query took less than a second with a cold cache. Example code below:

DROP TABLE IF EXISTS #BIG_TABLE;

CREATE TABLE #BIG_TABLE (
    ID BIGINT NOT NULL,
    FOR_U NVARCHAR(MAX),
    PRIMARY KEY (ID)
);

DECLARE @big_string NVARCHAR(MAX) = REPLICATE(CAST(N'Z' AS VARCHAR(MAX)), 200000);

INSERT INTO #BIG_TABLE WITH (TABLOCK)
SELECT TOP (250000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN, @big_string -- 100k is good
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

CHECKPOINT;
DBCC DROPCLEANBUFFERS;

SELECT COUNT_BIG(*) total_sampled_rows
, COUNT_BIG(CASE WHEN DATALENGTH(FOR_U) > 8000 THEN 1 ELSE NULL END) sampled_rows_too_long
FROM #BIG_TABLE TABLESAMPLE (1 PERCENT);

Notice the use of DATALENGTH instead of LEN. See Datalength optimizations for LOB data types… for the reasons you might find that performs better.
Delete rows from the table in small batches. Use the clustered key to loop over all rows exactly once. If you'd like more detail here, try Take Care When Scripting Batches. Otherwise, the core idea is to increment local variables as you process the table:

DECLARE @start_id BIGINT = 1;
DECLARE @end_id BIGINT = 500;

DELETE FROM #BIG_TABLE
WHERE ID BETWEEN @start_id AND @end_id;

Once all of the deletes are done, stop and measure your progress. Is the table small enough now? You may need to run a REORGANIZE or REBUILD to fully reclaim space. I recommend REORGANIZE because it's not an all-or-nothing operation (if you cancel it you keep your progress), it won't grow your log file, and it's an online operation.
Stop here. If the problem is that the table takes up too much space then you've solved that the problem by removing unneeded rows from the table. You probably do not need to change the data type in order to reclaim space. Having an unnecessary NVARCHAR(MAX) isn't ideal, but the downsides may not justify the effort in changing the data type. If you do need to change the data type then it will be easier to do so after you've freed up some space in your database.


Can indexing views in SQL Server 2005+ speed up SELECT count(*) from a View? My initial question was How does someone determine if it is a good idea to index a view?.

Update: My underlying assumption was that indexing views speeds them up. This is apparently not sensible, since views just execute queries, as the comments below point out.

What I'm doing right now is very similar to what this guy here is doing: I'm finding views that are selecting about 2K rows out of 2 million rows, take far too long to fetch rows, and I thought that a schema bound indexed view might help.

The operation that I was trying to optimize is:

select * from vwSample where TYPE_CODE = 'X';

TYPE_CODE is a 10-character string, there are about 2K unique TYPE_CODE values in vwSample, and there are about 2-5 million rows in vwSample, and there are between 2K and 10K rows for each unique value of TYPE_CODE. I didn't post my execution plan because I was trying to ask a "general practice" question.

Update: it looks like my question is wrong-headed, I should just write and optimize queries, and ignore the fact that there's a view, maybe even just write it as a regular query and ignore this view. And there are no "turbo buttons" available in views, that can make my view faster, by magic as the accepted answer suggests. My question appears to boil down to "I'm a SQL Server n00b and I know nothing about optimizing views, how do I speed up select * from vwSomething where KEY=X where vwSomething is a view?".

Typically you index views if you are often running aggregates, not to magically speed up joins. Also if you are not using table partitioning there is no good reason to investigate partition-aligned indexed views.

You should be focusing on optimizing the query, irrespective of the view, IMHO. An indexed view is not a magic turbo button (though a lot of people seem to think it is).

Also consider whether you should just run a query separately from the view anyway - often people run a lot of queries through the same view for convenience, even if the view actually touches more columns / rows than are necessary for the specific query.


Source for extended-event category and channel information? The SSMS GUI for Extended Events lets you filter events by category ("errors", "execution", etc) and channel ("Analytic", "Operational", etc).  How can I get the category and channel mapping for an event programmatically?

That information is available in the sys.dm_xe_* DMVs, but I had some trouble finding it.

Every event has a special, read-only column called "CHANNEL" in the sys.dm_xe_object_columns DMV.  The column is of type "etw_channel", and its value is in the column_value column of the DMV.  The column's value can be looked up in the sys.dm_xe_map_values DMV to get the channel name.

The category can be retrieved in a similar fashion, but with two wrinkles: the read-only column is called "KEYWORD", and 27 columns (in SQL Server 2014, at least) don't have that column.

The query below demonstrates how to get the information.  In particular, it returns the same information you see in the GUI when first choosing events.  The events are ordered by name, events from the "Debug" channel are filtered out, and 27 of them have a NULL category.

SELECT
        dxo.name,
        'Category' = camv.map_value,
        'Channel' = chmv.map_value
    FROM sys.dm_xe_objects AS dxo
    LEFT JOIN sys.dm_xe_object_columns AS cac ON 1=1
        AND cac.object_package_guid = dxo.package_guid
        AND cac.object_name = dxo.name
        AND cac.name = 'KEYWORD'
    LEFT JOIN sys.dm_xe_map_values AS camv ON 1=1
        AND camv.object_package_guid = cac.type_package_guid
        AND camv.name = cac.type_name
        AND camv.map_key = cac.column_value
    JOIN sys.dm_xe_object_columns AS chc ON 1=1
        AND chc.object_package_guid = dxo.package_guid
        AND chc.object_name = dxo.name
        AND chc.name = 'CHANNEL'
    JOIN sys.dm_xe_map_values AS chmv ON 1=1
        AND chmv.object_package_guid = chc.type_package_guid
        AND chmv.name = chc.type_name
        AND chmv.map_key = chc.column_value
    WHERE 1=1
        AND dxo.object_type = 'event'
        AND chmv.map_value <> 'Debug'
    ORDER BY
        dxo.name


Simple insert statements are taking so long I have a scenario where one main stored procedure calls a nested procedure and that nested procedure contains simple insert statements for multiple tables and almost every insert is taking so long

I know there are lots of possibilities for instance Disk, CPU, Memory, indexes and triggers

How do I find Clustered and NON-Clustered indexes are not the reason of this slowness?

Insert Statement:

INSERT INTO PEDetail with(rowLock) (CID,CustID,PpeID,PeID,SubSys1ID,SubSys2ID,CharacteristicID,cmbID,PETemplateID)
SELECT CID,@PID,@PpeID,PeID,SubSys1ID,SubSys2ID,CharacteristicID,cmbID,PETemplateID
FROM PEDetail with(nolock)
where PpeID = @OldPpeid

Table Structure:

 SET ANSI_NULLS ON
    GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PEDetail](
    [CID] [int] NOT NULL,
    [CustID] [int] NOT NULL,
    [PpeID] [int] NOT NULL,
    [PeID] [int] NOT NULL,
    [SubSys1ID] [int] NOT NULL,
    [SubSys2ID] [int] NOT NULL,
    [CharacteristicID] [int] NOT NULL,
    [cmbID] [int] NOT NULL,
    [PETemplateID] [int] NOT NULL,
 CONSTRAINT [PK_PEDetail] PRIMARY KEY CLUSTERED
(
    [CID] ASC,
    [CustID] ASC,
    [PpeID] ASC,
    [PeID] ASC,
    [SubSys1ID] ASC,
    [SubSys2ID] ASC,
    [CharacteristicID] ASC,
    [cmbID] ASC,
    [PETemplateID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[PEDetail]  WITH CHECK ADD  CONSTRAINT [FK_PEDetail_PEHeader] FOREIGN KEY([PpeID], [PeID])
REFERENCES [dbo].[PEHeader] ([PpeID], [PeID])
GO

ALTER TABLE [dbo].[PEDetail] CHECK CONSTRAINT [FK_PEDetail_PEHeader]
GO

ALTER TABLE [dbo].[PEDetail] ADD  CONSTRAINT [DF_PEDetail_cmbID]  DEFAULT ((0)) FOR [cmbID]
GO

NON Clustered Indexes:

CREATE NONCLUSTERED INDEX [ix_PEDetail_CID_PeID_6] ON [dbo].[PEDetail]
(
    [CID] ASC,
    [PeID] ASC,
    [SubSys1ID] ASC,
    [SubSys2ID] ASC,
    [CharacteristicID] ASC,
    [PETemplateID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_CID_CharacteristicID_CID_CharacteristicID] ON [dbo].[PEDetail]
(
    [CID] ASC,
    [CharacteristicID] ASC
)
INCLUDE ( [PpeID]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 85) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_PpeID_PeID] ON [dbo].[PEDetail]
(
    [PpeID] ASC,
    [PeID] ASC
)
INCLUDE ( [CID],
[CustID],
[SubSys1ID],
[SubSys2ID],
[CharacteristicID],
[cmbID],
[PETemplateID]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 85) ON [PRIMARY]
GO

The performance problem it's because of your clustered index. When you use clustered, and you add a new record, it needs to physically rearrange all records in the table to make space for it.

Drop the clustered index and create an unique non-clustered index instead. That should fix your performance problem.

As a general rule, I'd also recommend having a clustered index, but with only a numerical autoincremental id.


Query with conditional JOIN running slow I have a query the first part of which looks like this -

With CTE AS (
select DISTINCT
A.col1,
A.col2,
.
.
.
from tableA A
                    JOIN tableB B ON
                    (
                    B.col3= 'X' and B.col4=A.col3
                    OR
                    B.col3= 'Y' and B.col4=A.col4
                    OR
                    .
                    .
                    .
                    .
                    )

)

Now this query runs for more than 4 minutes and the execution plan showed a table spool which was data size of 600 MB and looked like this -

I changed this to conditional JOIN to UNIONs -

With CTE AS (
select
    A.col1,
    A.col2,
    .
    .
    .
    from tableA A
                    JOIN tableB B ON
                    (
                    B.col3= 'X' and B.col4=A.col3
                    )
    UNION
    select
    A.col1,
    A.col2,
    .
    .
    .
    from tableA A
                    JOIN tableB B ON
                    (
                    B.col3= 'Y' and B.col4=A.col4
                    )
     UNION
     .
     .
     .

 )

Now the spool with the large number of rows vanished and the run time reduced to 17 seconds. However when I check the IO statistics, the logical reads are higher than the previous slower query. What could the reason be ?

The reason is that AND and OR has the same precedence and the long condition x AND y OR z AND w OR v AND... without round brackets has a bit different meaning than you think.

Your query can be rewritten that way:

FROM tableA AS A
JOIN tableB AS B ON ( B.col3 = 'X' AND B.col4 = A.col3 )
                 OR ( B.col3 = 'Y' AND B.col4 = A.col4 )
                 OR (. . . . .)


Should I rely on Database Engine Tuning Advisor for creating indexes? How should I rely on SQL Server 2012 Database Engine Tuning Advisor for creating indexes and statistics? I find more often than not, it generates overly complex indexes that (I think) would be more costly in index maintenance than the benefit of having this index.

If I cannot solely rely on Database Engine Tuning Advisor, is there a straightforward method? A book an article perhaps?

The missing index DMVs are not particularly useful because I am trying to think of indexes for brand new tables.

Database Engine Tuning Advisor, like the DMVs such as sys.dm_db_missing_index_details and sys.dm_db_missing_index_groups give you a good place to start, as does the dmv sys.dm_db_index_usage_stats, to see which indexes are not being utilized and could be dropped.

However, these tools are never going to be as good as a skilled DBA making rational descisions about what indexes are really required. For example, DTA might recommend dropping an index that is hardly ever used and is expensive to maintain, but with business knowledge, you know that this index is key to running the monthly payroll.

In short - Use these tools to get a head start, but rely on your business knowledge, and performance troubleshooting skills as the final descision maker.


Database structure for tags? I posted a thread on Stack Overflow and one person pointed out that my database structure wasn't the best so now I'm here to ask how to do this in a good and proper way.

Currently I have one table called "users" and in the table I have a lot of userinfo. In "users" I have a column called "tags" were information inside the column look like "#dj #personal #coding" and things like that for every user. Is there any better way to store tags describing the user? I have one column called "tags" were the user have stored tags describing him/her and also a column called "interests" were tags are stored to remember what the user is looking for...

Basically, is there any better way to store these tags for each user?

It makes sense to store tags themselves in a separate table, and add a link table to implement many-to-many relationship between users and tags, for example

users (user_id (PK), name, [other user attributes]);
tags(tag_id(PK), tag_name, [other tag attributes]);
users_tags(user_id (FK to users) ,tag_id (FK to tags), PK(user_id,tag_id));

Depends on your needs, users_tags may have other attributes as well, for instance you might want to add datetime column to store when user added tag (surely, if it has any value to your application)


SQL Server query problem when selecting data from child table based on column in parent table I am using MS SQL Server 2008 using Management Studio. I am running a query(below) to extract dates and value based on the 'ticker' column from the parent table, the attribute column (child) , and dates (child) within a date range.

When I run the query, I get no values returned, but when I use primary key (param_id) from the parent or child table, I get the values returned that I want. However, I would like to use the column "ticker" in the parent table as the reference in the where clause.

I have 2 tables.

Parent Table
PK - param_id - int
FK - fed_id -   int    (references different fed reserve ids from other table)
description - varchar(50)
ticker -    varchar(50)
Child Table - holds the data
(PK, FK) param_id - int
(PK, FK) attrib_id -    int  (references different attributes from attribute table)
(PK) [update] - datetime
  value -   decimal(19, 6)

SQL Query below:

  SELECT [update], [value]
  FROM m_econ_fred_source
  JOIN s_econ_fred on m_econ_fred_source.param_id = s_econ_fred.param_id
  WHERE m_econ_fred_source.ticker = 'PHILINFLEXP3M' AND
        attrib_id = 7 AND
        [update] BETWEEN '2014-01-01' AND '2016-03-01'
  ORDER BY [update] ASC

If I do this, it works:

  SELECT [update], [value]
  FROM m_econ_fred_source
  JOIN s_econ_fred on m_econ_fred_source.param_id = s_econ_fred.param_id
  WHERE m_econ_fred_source.param_id = 2 AND
        attrib_id = 7 AND
        [update] BETWEEN '2014-01-01' AND '2016-03-01'
  ORDER BY [update] ASC

I have also made the ticker column unique as well to see if that would help to no avail. I also created the tables using the wizard instead of create statements.

Would anyone be able to tell me what may be wrong with my query or probable causes/solutions to my problem? Thank you. I have looked everywhere for an answer to this problem. I was unable to find a problem similar to this.

Thank you @Greg for answering the question. The problem required that the SQL statement fully qualify the columns in the where clause by referencing the tables for the columns. In this case. I needed m_econ_fred_source=attrib_id.

The complete solution is:

SELECT [update], [value]
FROM m_econ_fred_source JOIN s_econ_fred
ON m_econ_fred_source.param_id = s_econ_fred.param_id
WHERE m_econ_fred_source.param_id = 2 AND
      m_econ_fred_source.attrib_id = 7 AND
      [update] BETWEEN '2014-01-01' AND '2016-03-01'
ORDER BY [update] ASC


Update a table after insertion to another table in PostgreSQL? Let's say that my database has 2 tables (Person & Car):

Person (personID, number_of_cars)
Car (carID, owner)

where the owner attribute in Car refers to the personID in the Person table. Now, what I want is that whenever I insert a new car object into the Car table, then the number_of_cars of the owner will be incremented by one. The only solution that I have come up so far is to write the UPDATE statement right after the INSERT statement, something like this:

INSERT INTO Car (carID, owner) VALUES (1, 1);
UPDATE Person SET number_of_cars = number_of_cars + 1 WHERE personID = 1;

However, I don't feel like this is a good way of writing it because then I would have to do the update after every single insert statement. So what is a good way to achieve this in PostgreSQL?

The cleanest solution is to remove the redundant number_of_cars column completely. Your solution (as well as many related ideas floating around) are not safe against concurrent write access.

Instead, create a VIEW (or a MATERIALIZED VIEW to optimize read performance) like:

CREATE VIEW person_cars AS
SELECT *
FROM  (
   SELECT owner AS personid, count(*) AS number_of_cars
   FROM   car
   GROUP  BY 1
   ) c
JOIN person p USING (personid);

Or you could have a custom materialized view where you only update persons that had actual changes. Related example:

Refresh materalized view incrementally in PostgreSQL

If you insist on your original idea (and concurrent write access is not an issue), you could use a trigger solution. Basic example:

How do I make a trigger to update a column in another table?

You need to cover all possible changes: INSERT, UPDATE, DELETE on either table. @bgiles added more considerations.


Is there slowdown inserting into an InnoDB table that has no index set? I have an old application with lots of InnoDB tables, that have no indexes at all, not even a primary ID or such.

Those tables only contain a few thousand rows.

Would it be faster to INSERT data into these tables if I would set a primary index (that I don't need otherwise)?

On the contrary, a table with no indexes would be faster to run INSERTs on than a table with indexes.

However, in the case of InnoDB, there is already an internal index in use called the gen_clust_index. It is also better known as the Clustered Index. It exists for all InnoDB tables, even those with no PRIMARY KEY.

I have written about the gen_clust_index before

Sep 20, 2011 : Best of MyISAM and InnoDB
Mar 27, 2013 : Are there implicit indexes in InnoDB like MyISAM?
Feb 24, 2011 : what is gen_clust_index used for in mysql? (ServerFault)

Thus, adding a PRIMARY KEY on an InnoDB table with no PRIMARY KEY buys you nothing but another index.

Another headache in this regard is the fact that all lookups must eventually end up in the gen_clust_index, especially with secondary indexes. In fact, loadding data into an InnoDB table with secondary indexes will pile up changes in the Insert Buffer of the System Tablespace (See Pictorial Representation of ibdata1)