Compare two big tables in PL/SQL I need to implement comparing 2 tables by set of keys (columns of compared tables). By this comparing i should check equal records (equal = equal by set of defined keys (columns in these input tables)) and then insert these records to report table with some calculated columns.

Also i need to get the different (by keys) records in these 2 tables and insert them to result table. This pl/sql stored procedure works correct, but completely unusable on real datasets (~ 30 million entries in each input table).

CREATE OR REPLACE procedure ASUPR_OUP."6.3.2" (
    upd_date in date
)

as

-- filtered first input table
cursor c_end is select * from mon_end inner join dep on dep.dep = mon_end.depprod and dep.gpp_op = 1 and mon_end.division != 9 and mon_end.tehind not in (7,23) and (mon_end.depprod != mon_end.depcons or (mon_end.depprod = mon_end.depcons and dep.s_s = 1)) and mon_end.cyphdir in (1,7);

-- filtered second input table
cursor c_begin is select * from mon_begin inner join dep on dep.dep = mon_begin.depprod and dep.gpp_op = 1 and mon_begin.division != 9 and mon_begin.tehind not in (7,23) and (mon_begin.depprod != mon_begin.depcons or (mon_begin.depprod = mon_begin.depcons and dep.s_s = 1)) and mon_begin.cyphdir in (1,7);

is_found boolean;

begin

    for b_row in c_begin loop
        for e_row in c_end loop
            if (b_row.depprod = e_row.depprod) and (b_row.depcons = e_row.depcons) and
            (b_row.detail = e_row.detail) and (b_row.series = e_row.series) and (b_row.stk = e_row.stk) then
            begin
                if (b_row.trs != e_row.trs) or (b_row.trn != e_row.trn) or (b_row.kolsd != e_row.kolsd) or (b_row.applic != e_row.applic) then
                begin
                    if e_row.s_s = 0 then
                        begin
                            -- insert to result table
                        end;
                    else
                        begin
                            -- insert to result table
                        end;
                    end if;
                end;
             end if;
            end;
            end if;
        end loop;
    end loop;
    for b_row in c_begin loop
        is_found := false;
        for e_row in c_end loop
            if (b_row.depprod = e_row.depprod) and (b_row.depcons = e_row.depcons) and (b_row.detail = e_row.detail)
            and (b_row.series = e_row.series) and (b_row.stk = e_row.stk) then
                begin
                    is_found := true;
                end;
            end if;
        end loop;
        if is_found = false
        then
            begin
                if b_row.s_s = 0 then
                    begin
                        -- insert to result table
                    end;
                else
                    begin
                       -- insert to result table
                    end;
                end if;
            end;
        end if;
    end loop;
    for e_row in c_end loop
        is_found := false;
        for b_row in c_begin loop
            if (b_row.depprod = e_row.depprod) and (b_row.depcons = e_row.depcons) and (b_row.detail = e_row.detail)
            and (b_row.series = e_row.series) and (b_row.stk = e_row.stk) then
                begin
                    is_found := true;
                end;
            end if;
        end loop;
        if is_found = false then
            begin
                if e_row.s_s = 0 then
                    begin
                        -- insert to result table
                    end;
                else
                   begin
                        -- insert to result table
                    end;
                end if;
            end;
        end if;
    end loop;
end;

Can anyone suggest some way to boost performance of this procedure (i am using oracle 11g2) ? All input tables have indexes on used columns.

Find a small recordset and see how many loops your code does. How many does it do for 10, 100 and 1,000 records? You will see that the number of loops increases exponentially as the number of rows increases. My rough guess is that you have asymptotically big O(2^n+2^n+2^n). Asymptotics is a way of estimating work where the amount of rows processed is not known. Can't you just write some minus selects that are union all joined together? My example is looking at indexes and I use different filters so that the data sets will not be the same. Hence you will see the difference.

SELECT 'SDO_NETWORK_METADATA_TABLE_a' table_name, index_name, column_name, column_position
  FROM dba_ind_columns
 WHERE index_owner = 'MDSYS'
   AND table_name = 'SDO_NETWORK_METADATA_TABLE'
   AND index_name
   NOT IN ( SELECT index_name
              FROM dba_ind_columns
             WHERE index_owner  = 'MDSYS'
               AND table_name   = 'SDO_NETWORK_METADATA_TABLE'
               AND column_name  = 'LRS_TABLE_NAME' )
MINUS
SELECT 'SDO_NETWORK_METADATA_TABLE_a' table_name, index_name, column_name, column_position
  FROM dba_ind_columns
 WHERE index_owner = 'MDSYS'
   AND table_name = 'SDO_NETWORK_METADATA_TABLE'
   AND index_name
   NOT IN ( SELECT index_name
              FROM dba_ind_columns
             WHERE index_owner  = 'MDSYS'
               AND table_name   = 'SDO_NETWORK_METADATA_TABLE'
               AND column_name  = 'PATH_LINK_TABLE_NAME' )
UNION ALL
SELECT 'SDO_NETWORK_METADATA_TABLE_b' table_name, index_name, column_name, column_position
  FROM dba_ind_columns
 WHERE index_owner = 'MDSYS'
   AND table_name = 'SDO_NETWORK_METADATA_TABLE'
   AND index_name
   NOT IN ( SELECT index_name
              FROM dba_ind_columns
             WHERE index_owner  = 'MDSYS'
               AND table_name   = 'SDO_NETWORK_METADATA_TABLE'
               AND column_name  = 'PATH_LINK_TABLE_NAME' )
MINUS
SELECT 'SDO_NETWORK_METADATA_TABLE_b' table_name, index_name, column_name, column_position
  FROM dba_ind_columns
 WHERE index_owner = 'MDSYS'
   AND table_name = 'SDO_NETWORK_METADATA_TABLE'
   AND index_name
   NOT IN ( SELECT index_name
              FROM dba_ind_columns
             WHERE index_owner  = 'MDSYS'
               AND table_name   = 'SDO_NETWORK_METADATA_TABLE'
               AND column_name  = 'LRS_TABLE_NAME' )
 ORDER BY 1,2,4;


Is it possible to create AWS RDS as replication from external server? I can't find any tutorial to create a new instance of AWS RDS (MySQL) to be an replication db from an external db server. Is it possible to create one?

Thanks.

yes , you can,
check below link.
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql_rds_set_external_master.html


Slow performance when using a synonym that is configured to use a linked server Situation:
I’m not sure if I should be embarrassed that we did this, or if we should be applauded. Anyways, we have a shell of a database that has synonyms pointing to another server via a linked server, and then views named identical to the tables that used to be in the database that utilize the synonyms. We did this because of server migrations and reorganization of dev teams. Anyways, the different teams had baked in code that references this particular database, and we couldn’t get them to change their code. My team was told that the effort was a huge undertaking, and they didn’t have time to do it. So, we came up with this plan for better or worse, and ended up placing this shell database on each of their SQL Servers. We can’t replicate the database because the vendor of the application doesn’t believe in primary keys (or at least that’s what we tell ourselves…. yeah ponder on that one for a second, and it will make your brain hurt even more when I tell you they are a large billion software dollar company).
Question
If we do a select against the view (select top 100 * from viewname) the data results come back in about 3-4 seconds. Remember, the view is using a synonym that uses a linked server reference.
If we do a select against the linked server (select top 100 * from ls.db.schema.object) the data results come back sub-second. The full path of the linked server reference is the same for the synonym above.
Why the difference In times? Is it because engine has to take an extra moment to build the query plan? The dev teams are starting to get restless because their apps are now running slow.

If I’ve completely confused you I’m sorry. I did the best I could to describe the environment.

Query plan using the synonym:
brentozar.com/pastetheplan/?id=Sy9b0yZJM

Query plan using the linked server:
brentozar.com/pastetheplan/?id=r17oAy-kG

Fact is both are same.

CREATE SYNONYM [Testsyn]
FOR [RemoteServer].[DBName].[dbo]
GO

SELECT *
FROM [Testsyn].EmpTable
GO

SELECT *
FROM [RemoteServer].[DBName].[dbo].EmpTable
GO

Optimizer just decode synonym to actual object,there after both will have identical query plan."[Testsyn]" will be converted to "[RemoteServer].[DBName].[dbo]"

Poor performance for synonym could be :

i) Because permission issue to synonym on remote server.You should check with DBA of both team.


Speed up INSERT procedure I am creating a stored procedure in SQL Server 2016, in which I have the following query:

DECLARE
    @FechaMovimiento DATETIME

SELECT
   @FechaMovimiento = ISNULL(MAX(FechaMovimiento), '31/12/2014')
FROM
   DB0.[dbo].[TR_TAB_OLAP_FechasCierre]
WHERE
   FechaCierre <= GETDATE() -1

INSERT INTO DB1.[dbo].[HVentas_1]
   (
   IDTienda, Id, Numero, NumeroFactura, idhora, IdLineaDocumento,
   IdDocumento, Fecha, UniqueID, Posicion, CodigoDeBarras,
   CodigoNIT_Productor, CodigoAmarreProductor,
   CodigoMarca, CodigoUnidad, CodigoUnidadDeEmpaque, CodigoImpuesto, CodigoAmarre_Articulo_Referencia,
   CodigoFamilia, CodigoLinea, CodigoCategoria, CodigoSubcategoria,
   DescripcionCortadeProducto, Descripcion, DescripcionTienda, BarraTienda, Unidades, PrecioDefecto, Precio,
   Descuento, IDImpuesto, Importe, ImporteIVA, ImporteDtoTotal,
   ImporteDtoTotalIva, ImporteDtoLinea, ImporteDtoLineaIva, Comentario1, Comentario2,
   VolumenTotal, PrecioPorPesoVolumen, Tiendas_ActivasPorBarra
   )
SELECT DISTINCT TOP 5
   v.IDTienda, v.Id, v.Numero, v.NumeroFactura, v.idhora, v.IdLineaDocumento,
   v.IdDocumento, v.Fecha, v.UniqueID, v.Posicion, v.CodigoDeBarras,
   v.CodigoNIT_Productor, v.CodigoAmarreProductor,
   v.CodigoMarca, v.CodigoUnidad, v.CodigoUnidadDeEmpaque, v.CodigoImpuesto, v.CodigoAmarre_Articulo_Referencia,
   v.CodigoFamilia, v.CodigoLinea, v.CodigoCategoria, v.CodigoSubcategoria,
   v.DescripcionCortadeProducto, v.Descripcion, v.DescripcionTienda, v.BarraTienda, v.Unidades, v.PrecioDefecto, v.Precio,
   v.Descuento, v.IDImpuesto, v.Importe, v.ImporteIVA, v.ImporteDtoTotal,
   v.ImporteDtoTotalIva, v.ImporteDtoLinea, v.ImporteDtoLineaIva, v.Comentario1, v.Comentario2,
   v.VolumenTotal, v.PrecioPorPesoVolumen, 0
FROM
   DB1.dbo.TR_TAB_OLAP_HVentas v LEFT JOIN  DB2.[dbo].[HVentas_1] v1 WITH (NOLOCK)
ON
   v.fecha = v1.fecha AND v.[UniqueID] = v1.[UniqueID] AND v.Posicion = v1.Posicion
WHERE
   v1.fecha IS NULL
AND
   v1.Uniqueid IS NULL
AND
   v1.Posicion IS NULL
AND
   v.fecha > @FechaMovimiento

This is the execution plan:

https://www.brentozar.com/pastetheplan/?id=Sy4qks9SW

It is taking too much time (around 10 minutes), even though I am taking a small quantity of records. I would like to know If there's any way to speed up this procedure.

Query timings:

@FechaMovimiento assignment : less than 1 second.
Select distinct: less than 1 second
Insert From Select Distinct: almost 10 minutes.

I have a non-clustered index on HVentas on Fecha, UniqueId and Posicion.
And a columnstore index in HVentas_1.

First let's do a quick summary of the query plan for that insert query. SQL Server scans all rows from HVentas_1 to create the hash build and finds 0 relevant rows. It then scans through TR_TAB_OLAP_HVentas and stops if/when it finds 5 distinct rows such that v.fecha > @FechaMovimiento and that don't match a row in HVentas_1. You have an index defined on the table but SQL Server doesn't use it in the query plan that you uploaded. The worst case for performance should be when @FechaMovimiento is a value that doesn't match any rows in the table. In that case SQL Server will scan all 70 million rows from the table only to insert 0 rows. The number of rows inserted isn't relevant here. What's important is the amount of work that SQL Server did to find the rows to insert.

For the actual plan that you uploaded, SQL Server had to scan 70037142 rows from the heap before it found five distinct rows. It seems as if you had a very selective value for the variable, but you're using a local variable in a stored procedure without a RECOMPILE hint. You could be running into an issue with parameter sniffing. In any case, try it again with a RECOMPILE hint. That will allow SQL Server to build a query plan that accounts for the value of the local variable. This should be fine unless you're running this stored procedure hundreds of times per second. With better information, the query optimizer can make more informed decisions about when to use an index. Using an index here is likely to lead to better performance.

As a side node, you have a CCI that only has ten rows. Will that table continue to be that small? If so that may not be a good candidate for columnstore.


Outer Apply vs Left Join Performance I am Using SQL SERVER 2008 R2

I just came across APPLY in SQL and loved how it solves query problems for so many cases,

Many of the tables I was using 2 left join to get the result, I was able to get in 1 outer apply.

I have small amount of data in my local DB tables and after deployment the code is supposed to run on data atleast 20 times big.

I am concerned that outer apply might take longer than the 2 left join  conditions for large amount of data,

Can anyone tell how exactly apply works and how will it effect the performance in very large data,
If possible some proportional relations with size of each table like proportional to n1^1 or n1^2 ... where n1 is number of rows in table 1.

Here is the query with 2 left join

select EC.*,DPD.* from Table1 eC left join
  (
   select member_id,parent_gid,child_gid,LOB,group_gid,MAX(table2_sid) mdsid from Table2
   group by member_id,parent_gid,child_gid,LOB,group_gid

  ) DPD2 on DPD2.parent_gid = Ec.parent_gid
        AND DPD2.child_gid = EC.child_gid
        AND DPD2.member_id = EC.member_id
        AND DPD2.LOB = EC.default_lob
        AND DPD2.group_gid = EC.group_gid
  left join
  Table2 dpd on dpd.parent_gid = dpd2.parent_gid
            and dpd.child_gid = dpd2.child_gid
            and dpd.member_id = dpd2.member_id
            and dpd.group_gid = dpd2.group_gid
            and dpd.LOB = dpd2.LOB
            and dpd.table2_sid = dpd2.mdsid

Here is the query with outer apply

select * from Table1 ec
OUTER APPLY (
      select top 1 grace_begin_date,retroactive_begin_date,Isretroactive
                    from Table2 DPD
                    where DPD.parent_gid = Ec.parent_gid
                    AND DPD.child_gid = EC.child_gid
                    AND DPD.member_id = EC.member_id
                    AND DPD.LOB = EC.default_lob
                    AND DPD.group_gid = EC.group_gid
                    order by DPD.table2_sid desc
     ) DPD

Can anyone tell how exactly apply works and how will it effect the performance in very large data

APPLY is a correlated join (called a LATERAL JOIN in some products and newer versions of the SQL Standard). Like any logical construction, it has no direct impact on performance. In principle, we should be able to write a query using any logically equivalent syntax, and the optimizer would transform our input into exactly the same physical execution plan.

Of course, this would require the optimizer to know every possible transformation, and to have the time to consider each one. This process might well take longer than the current age of the universe, so most commercial products do not take this approach. Therefore, query syntax can, and often does, have an impact on final performance, though it is difficult to make general statements about which is better and why.

The specific form of OUTER APPLY ( SELECT TOP ... ) is most likely to result in a correlated nested loops join in current versions of SQL Server, because the optimizer does not contain logic to transform this pattern to an equivalent JOIN. Correlated nested loops join may not perform well if the outer input is large, and the inner input is unindexed, or the pages needed are not already in memory. In addition, specific elements of the optimizer's cost model mean a correlated nested loops join is less likely than a semantically-identical JOIN to produce a parallel execution plan.

  I was able to make same query with single left join and row_number()

This may or may not be better in the general case. You will need to performance test both alternatives with representative data. The LEFT JOIN and ROW_NUMBER certainly has potential to be more efficient, but it depends on the precise query plan shape chosen. The primary factors that affect the efficiency of this approach is the availability of an index to cover the columns needed, and to supply the order needed by the PARTITION BY and ORDER BY clauses. A second factor is the size of the table. An efficient and well-indexed APPLY can out-perform a ROW_NUMBER with optimal indexing if the query touches a relatively small portion of the table concerned. Testing is needed.


SQL Server 2012 Install error "The instance selected for installation is already installed and clustered on computer..." I am trying to setup a new environment for SQL Server 2012 AlwaysOn. I inadvertently used some SQL Server 2008 config files initially to do the installs but ran into too many problems. I un-installed SQL Server using program features but apparently that did not remove everything.

Now when I try to use the install wizard, I get to the "Installation Configuration Rules" screen and am presented with a failed status for rule "Existing clustered or cluster-prepared instance". If I click on the "failed" link, the dialog box says

  Rule 'Existing clustered or cluster-prepared instance' failed. The instance selected for installation is already installed and clustered on computer SQLNode1. To continue, select a different instance to cluster."

The problem is I absolutely need to use the same instance that I had tried to previously remove. How can I ensure the previous instance has been removed so I can attempt this install again?

Installing Windows Server from scratch would remove any issues from the failed install and ensure that there are no problems going forward when patching or changing the SQL Server instance.

If this is going to be a production server removing any chance for downtime in the future should be the biggest concern.


Is there a better alternative to lookup tables to deduplicate text columns in PostgreSQL? My database has VARCHAR columns each containing 20k-60k distinct short texts. It is not feasbile (due to space constraints) to store these values in each row, they need to be externalized somehow. These values are never updated.

The classic approach would be to replace each of these columns with a separate lookup table and corresponding id - this however requires a lot of joins and a lot of similar tables.

Enums don't seem to be feasible for this problem, although they would remove the need for explicit joins: They cant be longer than 63 bytes and it is impossible to add new values in transactions until version 10.

I have looked in the documentation for CREATE TABLE - there seems to be no built-in support for this kind of deduplication. TOAST-Tables don't seem to contain unique values either.

Is there another approach I have missed?

Lots of joins is exactly the usual approach.

You can, however, write a non-STRICT LANGUAGE sql function like lookup_mytext(id) that does a SELECT from the lookup table by id. PostgreSQL will inline it as a subquery, then flatten the subquery to a join. So the effect is the same, but it's notationally simpler to write

SELECT
  x,
  lookup_mytext(y)
FROM t;

than

SELECT
  t.x, tt.val
FROM
  t INNER JOIN tt ON (t.y = tt.id);

Most of the time I prefer to avoid obfuscation and keep the explicit join. But it can become beneficial if this is a real pattern across the app.

See the documentation for rules on function inlining. Check with EXPLAIN.


Waiting for table level lock on wordpress site We are experiencing issues with mySQL loading 100% of the server's CPU. While checking the running processes we found many of the following:

Waiting for table level lock | SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'

The table is configured to MyISAM.

Any suggestion of how to optimize mysql?

I had this problem and added an index to wp_option.option_name


calling scalar function to assign value to local variable In a SQL Server stored procedure, if we declare a local variable and assign the value by calling a scalar function
DECLARE @variable_name INT= dbo.scalarfuntion_name(@p1, @p2).
Would it have any impact on overall performance of SP as in general using scalar function directly into script has performance downgrade most of the times?
Thanks!

Two big problems with using functions in a query are that cardinalities are not accurate and that the function code is called once per row. When setting a variable neither of these are a concern. So I would think, generally speaking, there would be no measurable performance impact.


Synchronise two databases (but not really) I'm trying to design the following system.

An arbitrary number of of embedded systems will each record data constantly and indefinitely. It is required that these devices upload their data to a server which maintains a complete history of all captured data (and records the source of the data by an ID number of something). The embedded systems are required to keep a small local history of about a day.

Is it possible to use MySQL's replication functionality to do this? How would it work, since the local DB will be deleted periodically, while the server copy must never be deleted.

Another way is possibly just periodically running a program to upload the day worth of data and clear the table, but this seems messier especially on an intermittent internet connection.

What do you think would be a good design for this situation?

Thanks

Yes, but by using tricks. MySQL has statement based replication, which causes the same SQL to be executed on the target as on the source. So the question is, can you write a SQL statement that does what you want? The answer is yes if you can exclude the rows in the target somehow. One way to do this would be a column that is 0 on all the sources, but which you update to 1 on the target by some mechanism (batch job, trigger, etc). Then when you execute the DELETE on the source WHERE ... AND is_target=0 the statement will do nothing when it is replicated.

There is no way that I am aware of to simply have the target system filter out deletes (this can be done with with Shareplex so I assume GoldenGate too, but that costs money).


SQL server compare a field with another that is composed of a list separated by commas of the 1st field I need to execute the below query but I have a problem.

I cannot use
tTerritorio.terrNome = tContactoTerritorio.territorios because territorios is a list of several terrNome separated by ,

How can I make this match so that I can retrieve the information I need?

select terrNome As Territorio, espDescricao AS Especialidade, COUNT (distinct contNome) AS 'Nº Médicos'
from tContacto, tContactoTerritorio, tTerritorio,tEspecialidade
where tContacto.contID=tContactoTerritorio.contID and tContacto.tpcontID=120
and tTerritorio.terrNome = tContactoTerritorio.territorios
and (tEspecialidade.espID=tContacto.esp1ID or tEspecialidade.espID=tContacto.esp2ID or
tEspecialidade.espID=tContacto.esp3ID or tEspecialidade.espID=tContacto.esp4ID or
tEspecialidade.espID=tContacto.esp5ID)
group by terrnome, espdescricao

Maybe a simple like will work for you:

','+tContactoTerritorio.territorios+',' like '%,'+tTerritorio.terrNome+',%'

I'm adding commas at the beginning and end of territorios assuming the first and last occurrences don't have them. Then you look for '%,terrnome,%'.
(Obviously this is a terrible way to search for a string but it's also a terrible way to store data)

SELECT terrNome             AS Territorio,
  espDescricao              AS Especialidade,
  COUNT (DISTINCT contNome) AS 'Nº Médicos'
FROM tContacto,
  tContactoTerritorio,
  tTerritorio,
  tEspecialidade
WHERE tContacto.contID   =tContactoTerritorio.contID
AND tContacto.tpcontID   =120
AND ','+tContactoTerritorio.territorios+',' like '%,'+tTerritorio.terrNome+',%'
AND (tEspecialidade.espID=tContacto.esp1ID
OR tEspecialidade.espID  =tContacto.esp2ID
OR tEspecialidade.espID  =tContacto.esp3ID
OR tEspecialidade.espID  =tContacto.esp4ID
OR tEspecialidade.espID  =tContacto.esp5ID)
GROUP BY terrnome,
  espdescricao


Longitude and Latitude order with ST_X, and ST_Y on SRID 4326 I have a MySQL 8.0.15 and I am experimenting with the geo-spatial location functions. I have applied SRID 4326 to the "loc" column.

Nothing in the docs other than the primitive Point() suggests the input as "x,y" or "long, lat" but if I run the following query:

INSERT INTO organization
(loc, spots, name, phone, website, account_id, category_id)
VALUES
(
  ST_PointFromText("POINT(-113.532000 54.733000)", 4326),
  10,
  "Baptiste",
  " ",
  null,
  null,
  null
);

  SQL Error (3617): Latitude -113.532000 is out of range in function st_pointfromtext. [It must be within -90 > +90]

Which makes perfect sense as there are no 113 degrees of latitude - but when I swap the lat/lon parameters - everything works! However when I query the lat/lon the results are backwards.

SELECT
   *,

    ST_Y(loc) as lat,
    ST_X(loc) as lng

FROM
  organization;

What am I not understanding about how these functions and coordinates work?

Seems the POINT() actually accepts LAT, LON on input - but when querying one must remember that Y is latitude, and X is longitude???

TIA

Which makes perfect sense as there are no 113 degrees of latitude - but when I swap the lat/lon parameters - everything works! However when I query the lat/lon the results are backwards.

That's because MySQL and Microsoft do the absolutely insane thing of swapping the order in geographic representations because that's what the EPSG says. Oracle and PostGIS do not. There is no X/Y in MySQL Geographic Representation. With MySQL v8, they tried to fix this while maintaining total and utter insanity by introducing

ST_Longitude()
ST_Latitude()

For more information check out Does MySQL store point datatypes as LAT LNG or LNG LAT?


Can I change database compatibility level from 80 to 90 or higher with log shipping? I have a database on SQL 2008 R2 with compatibility level 80 and I want to change it to 90 or 100. I assumed that after changing, I may find a regression in our application's performance.
Many backups (like full, differential, and log) will be taken while using the new compatibility level 90 or 100. This database is also configured with log shipping.
My question is can I change compatibility level back to 80 safely without threats or must I take any considerations?

It's quite easy to change it back and forth. But you will have to test the performance. Here's a post on how to change it in SSMS.


UPDATE Trigger with condition I'm trying to trigger an update on a specific user in my user table.
Basically is someone tries to change that user then I want to rollback whatever query was executed and disable the user(using a SP), but if if is not that user, than proceed with the update commit.

This is the code I created:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Users_Trigger_UPDATE]
ON [dbo].[Users]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @user_id as nvarchar(30);
DECLARE @action as nvarchar(30) = 'UPDATE';
DECLARE @source as nvarchar(30) = (SELECT client_net_address FROM
sys.dm_exec_connections WHERE session_id = @@SPID);
    SELECT
      @user_id = inserted.UserID
    FROM
      inserted
   IF @user_id = 'Admin'
    BEGIN
        ROLLBACK TRAN;
        EXEC [dbo].[DisableUser] @user_id, @action, @source;
        PRINT 'Admin User Triggered';
    END
ELSE
    BEGIN
        PRINT 'Other User';
        COMMIT;
    END
END

But I'm receiving the error "The transaction ended in the trigger. The batch has been aborted."

I tried to use the TRY...CATCH but the error is the same. I didn't create a nested transaction so I'm assuming that when I use the ROLLBACK or COMMIT that will apply to the original Query that triggered the UPDATE.

I tried an INSTEAD OF trigger, but the result is the same.

The result of the trigger is correct, it's doing what I want on the DB side, the problem is that there is software using this table that probably has a Try...Catch and as the trigger returns an error, even if the result is correct, the software gives a Login error because the trigger at the end has an error.

While I am not advocating the approach being attempted here (i.e. manipulating the Transaction within the Trigger), I at least wanted to explain what was going on so that there was a clearer understanding of how Triggers work.

Here are some basic behaviors of Triggers:

When a Trigger is executed, it is always within a Transaction. Individual DML statements will execute within a system-initiated Transaction (known as auto-commit) if a Transaction was not already active at the time that the DML statement was executed. Meaning:

If @@TRANCOUNT == 0 before the DML statement, then @@TRANCOUNT will be 1 in the Trigger
If @@TRANCOUNT > 0 before the DML statement, then @@TRANCOUNT will be the same value in the Trigger

Unlike Stored Procedures where @@TRANCOUNT needs to be the same before and after execution, Triggers allow for @@TRANCOUNT being higher or lower after their execution, with one exception (see next item).
If the Trigger ends with @@TRANCOUNT being 0 (before the Trigger exits), then

you will get an error stating that the Transaction ended in the Trigger and that the batch is being aborted
the batch being aborted cannot undo / revert whatever ended the Transaction:

If the Transaction was committed, the data will still be committed.
If the Transaction was rolled back, those changes are gone.

Once the Transaction ends, either by COMMIT or ROLLBACK, the inserted and deleted tables no longer have any rows in them. If you need anything from them after the Transaction ends, then you need to capture it into variables and/or a temp table (or table variable) before the Transaction ends.
Triggers execute with an implied XACT_ABORT ON. This might complicate things, even if you have a TRY...CATCH construct. In some cases you might need to explicitly set XACT_ABORT OFF

That said, you certainly can COMMIT / ROLLBACK the Transaction within a Trigger. Simplistically, if you do either of those actions, you just need to issue a BEGIN TRAN; before the Trigger ends so that you don't get the error about it aborting the batch. This assumes, in the case of ROLLBACK, that you want to mask the fact that this happened instead of allowing ROLLBACK to cancel the entire operation, which is how it is normally used within Triggers.

However, reality is not so simple:

If there are multiple Triggers, the order in which they fire could result in different behavior. Assuming there were no more than 3 Triggers, you can control the order by setting the "first" and "last" Triggers. More than 3 Triggers will likely not be able to control the ordering of all of the Triggers.
If there are nested Triggers, then ending the Transaction in the Trigger will could lead to unexptected behavior.
If there is an active Transaction before the Trigger executes, then ending the Transaction in the Trigger will take control away from whatever is happening after the Trigger.

To account for the possibility of an active Transaction already existing prior to the Trigger firing, you would need to capture @@TRANCOUNT at the top and then issue the correct number of BEGIN TRAN;, and possibly also COMMIT TRAN;, statements:

DECLARE @TranCount INT = @@TRANCOUNT,
        @Index INT = 0;

...

-- For an actual COMMIT, do the following:
WHILE (@Index < @TranCount)
BEGIN;
   COMMIT TRAN;
   SET @Index += 1;
END;

....

-- If either COMMIT or ROLLBACK occurs, do the following:
SET @Index = 0; -- reset in case COMMIT was processed above
WHILE (@Index < @TranCount)
BEGIN;
   BEGIN TRAN;
   SET @Index += 1;
END;

Again, this is not recommended as it changes the nature of any existing Transaction to behave in an unexpected, non-intuitive way. The flow would end up being:

Begin Transaction A
Do something
Insert into this Table

Transaction A might be committed or rolled-back, in which case:

Actions performed as "Do something" above are either committed or rolled-back
Transaction B will be started

Transaction A might be left alone, in which case there is no Transaction B

After the Trigger exists

If Transaction A was committed or rolled-back, it won't be possible to change that action. Transaction B will now exist, and changes made at this point will be completely disconnected from changes made previously in Transaction A.
If Transaction A was left as active:

Actions taken previously are still available to COMMIT or ROLLBACK
Actions taken after the Trigger ends are grouped along with actions taken before the Trigger fired as an atomic unit (this is how things usually work and hence the expected behavior).

The definitive reference on all this is Error and Transaction Handling in SQL Server by Erland Sommarskog.


replicate-wild-ignore-table does not stop already logged queries on slave We have a master DB where where multiple queries where fired on a specific table, these queries were taking too long to execute on slave and it was lagging behind and as a result of which queries were queued and executing one by one.

Later we identified that this table was not required to be replicated and we added this table to replicate-wild-ignore-table on the slave's my.cnf and restarted the slave. But the slave again picked up the remaining queries for that table. Ideally it should have skipped the queries for that table.
We are not able to figure out why this is happening.

Can somebody explain?

Without seeing the exact my.cnf entries on the slave, or knowing where the update came from (normal UPDATE command, or a stored procedure for example), or knowing what type of binary log format (row vs statement) you are using, all I can do is give you a link to how MySQL handles the replicate table options here.

A couple of things to note on this to explain why the above points are needed:

If you are using statement based logging and the statements are executed from a stored procedure, the slave will run it because --replicate-table rules do not apply to stored objects.
If the table name you are trying to ignore contains an underscore, you might need to escape it like "foo_bar". The same applies to the database name.


MySQL LDAP authentication Is LDAP authentication and authorization in MySQL possible? What are the requirements (MySQL version, third-party software etc.)? How it's organized?

Thanks.

use OpenDS with MySQL cluster to implement LDAP

I think this will help a bit...


Trigger on indexed view I created an indexed view with a INSTEAD OF trigger on it, but it's not firing when inserts happen at the base table level.

This is the plan that a normal insert generates (note the clustered index insert on VW_X.VW_CI which is the clustered index representing the view)

Is there any way I can track inserts on a view despite the fact that inserts don't really happen there, but at the base tables that create the view?

The base table insert plan incorporates operations necessary to keep the indexed view synchronized with the base table as defined by the view definition. This part of the plan is automatically generated and cannot be disabled, or made to call the instead of trigger logic on the view instead. The contract of an indexed view is that it will always materialize the query stored in the view.

The main purpose of instead of triggers on views (indexed or otherwise) is to make them updatable when they wouldn't be otherwise. The question doesn't explain what you need to achieve, but it seems an indexed view is not the right mechanism. You might need a separate table instead of a view. Please ask a follow-up question if you want help solving the underlying problem, and ideally provide a self-contained reproduction script.


How do I fix Replication event checksum verification failed while reading from network. Error_code: 1743 Our server had disk space problems. As a result, replication's IO Threads fails to start.

Here are the errors in mysql error log:

  [Note] Slave I/O thread: connected to master
  'user@host:3307',replication started in log 'mysql-bin.000030' at
  position 196820914

  [ERROR] Slave I/O: Replication event checksum verification failed
  while reading from network. Error_code: 1743

  [ERROR] Slave I/O: Relay log write failure: could not queue event from
  master, Error_code: 1595

How do I fix the problem?

Try resetting the relay logs as follows:
STOP SLAVE;
CHANGE MASTER TO master_log_file='mysql-bin.000030',master_log_pos=196820914;
START SLAVE;

This will erase all relay logs in the Slave, starting with a fresh one. It should start retrieving binlog events from the Master. If it fails again, then mysql-bin.000030 on the Master may be corrupt. In that event, you would have to set up replication from scratch by doing this:

Stop the DB Application
On the Master, RESET MASTER;
mysqldump the data from the Master
Load the mysqldump on the Slave
On the Slave, run STOP SLAVE;
On the Slave, run CHANGE MASTER TO master_log_file='mysql-bin.000001',master_log_pos=4;
On the Slave, run START SLAVE;

CAVEAT
Make sure the network connection is clear (with no dropped packets) between Master and Slave.
Give it a Try !!!


How to store YMD, HM but not S Seconds in mariaDB/MySQL? I was trying to make a column that stores the showtime of movies.

Since it is more meaningful to have showtimes in the unit of minutes, can I have a date type that has only years to minutes (eg. 2017-12-26 15:30), but no seconds? Thanks!

There is no special datatype for this :) Instead, you can use DATE_FORMAT

Example:

SELECT DATE_FORMAT(now(), '%Y-%m-%d %H:%i');

So you can create a view to converting your datetime column to the specific date format.

Create table with Datetime

create table test (date datetime);
insert into test values (now());
select * from test;
2017-12-28 10:30:24

Create view to convert date format

create view v_test as (select DATE_FORMAT(date, '%Y-%m-%d %H:%i'));

select * from v_test;
2017-12-28 10:30


Moving data from staging table to production table uses up to 200GB of Log space I have multiple staging tables containing data that needs to be moved to production tables. The process of moving this data is done within a transaction to make it possible to rollback everything in case the process fails.
The most log consuming operations are:

Deleting old data from production tables
Inserting new data into production tables

The query used to delete the old data is:
DELETE FROM PROD_TABLE WHERE CONDITION

A simple truncate is not possible since not all the data can be deleted.
The data to be deleted and the data to be moved is about 200 Million records.
Is there anyway to improve the process to reduce the log consomption.

You might like to look at partition switching - the staging table could be switched in to the production table, and the production table switched out to a history table. https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver15#c-switching-partitions-between-tables gives an example of a switch out. There are quite a few blogs about the technique, look up a few and see if they match what you are trying to do.
A system I used to work on had a monthly data load. The data was staged to a table, which was then switched in to the live table. The live table had a partition per month. It was very quick as I think it's a metadata operation. HTH


How to properly filter columns after applying aggregate function I find that I need to apply an aggregate a lot, but I don't want to show the aggregated value as well. So the best way to do this was to just nest the query to filter it out (like below) but there has to be a better way, how?

select state
from(
    select state, max(cnt)
    from (
        select state, count(*) as cnt
        from customer
        group by state) as t
) as r;

Here I just want to show state without the max value.

I think you can achieve this result without so many subqueries:

Select State
  From Customer
  Group By State
  Order By Count(*) Desc
  Limit 1;


Database "frozen" on ALTER TABLE Our production environment just froze* this morning for a while when altering a table, adding a column actually.

Offending SQL:ALTER TABLE cliente ADD COLUMN topicos character varying(20)[];

* Login into our system requires a select from that very same table, so no one could login during the alter table. We actually had to kill the process to allow the system resume normal operations.

Table Structure:

CREATE TABLE cliente
(
  rut character varying(30) NOT NULL,
  nombre character varying(150) NOT NULL,
  razon_social character varying(150) NOT NULL,
  direccion character varying(200) NOT NULL,
  comuna character varying(100) NOT NULL,
  ciudad character varying(100) NOT NULL,
  codigo_pais character varying(3) NOT NULL,
  activo boolean DEFAULT true,
  id serial NOT NULL,
  stock boolean DEFAULT false,
  vigente boolean DEFAULT true,
  clase integer DEFAULT 1,
  plan integer DEFAULT 1,
  plantilla character varying(15) DEFAULT 'WAYPOINT'::character varying,
  facturable integer DEFAULT 1,
  toolkit integer DEFAULT 0,
  propietario integer DEFAULT 0,
  creacion timestamp without time zone DEFAULT now(),
  codelco boolean NOT NULL DEFAULT false,
  familia integer DEFAULT 0,
  enabled_machines boolean DEFAULT false,
  enabled_canbus boolean DEFAULT false,
  enabled_horometro boolean DEFAULT false,
  enabled_comap boolean DEFAULT false,
  enabled_frio boolean DEFAULT false,
  enabled_panico boolean DEFAULT false,
  enabled_puerta boolean DEFAULT false,
  enabled_rpm boolean DEFAULT false,
  enabled_supervisor integer DEFAULT 0,
  demo boolean,
  interno boolean,
  mqtt_enable boolean NOT NULL DEFAULT false,
  topicos character varying(20)[],
  CONSTRAINT pk_cliente PRIMARY KEY (rut),
  CONSTRAINT fk_cliente_familiaid FOREIGN KEY (familia)
      REFERENCES cliente_familia (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT pk_pais FOREIGN KEY (codigo_pais)
      REFERENCES pais (codigo) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT unique_id_cliente UNIQUE (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE cliente
  OWNER TO waypoint;
GRANT ALL ON TABLE cliente TO waypoint;
GRANT ALL ON TABLE cliente TO waypointtx;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE cliente TO waypointtomcat;
GRANT SELECT ON TABLE cliente TO waypointphp;
GRANT SELECT ON TABLE cliente TO waypointpphppublic;
GRANT ALL ON TABLE cliente TO waypointsoporte;
GRANT SELECT, INSERT ON TABLE cliente TO waypointsalesforce;
GRANT SELECT ON TABLE cliente TO waypointadminuser;
GRANT SELECT ON TABLE cliente TO waypointagenda;
GRANT SELECT ON TABLE cliente TO waypointmachines;
GRANT SELECT ON TABLE cliente TO waypointreports;
GRANT SELECT ON TABLE cliente TO readonly;

CREATE INDEX index_cliente
  ON cliente
  USING btree
  (rut COLLATE pg_catalog."default");

CREATE INDEX index_cliente_activo
  ON cliente
  USING btree
  (activo);

CREATE INDEX index_cliente_id_activo
  ON cliente
  USING btree
  (id, activo);

CREATE INDEX index_cliente_rut_activo
  ON cliente
  USING btree
  (rut COLLATE pg_catalog."default", activo);

CREATE TRIGGER trigger_default_admin
  AFTER INSERT
  ON cliente
  FOR EACH ROW
  EXECUTE PROCEDURE crea_default_admin();

CREATE TRIGGER trigger_default_grupo
  AFTER INSERT
  ON cliente
  FOR EACH ROW
  EXECUTE PROCEDURE crea_default_clientegrupo();

Should I disable CONSTRAINTS, TRIGGERS, or something else?

Perhaps any DB Tuning?

What else should I provide for further analysis?

Version: PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

The command you wish to run does take an ACCESS EXCLUSIVE lock on the table, preventing all other access to that table.  But the duration of this lock should be just a few milliseconds, as adding a column like the one you want to add does not require the table to be re-written, it just requires metadata to be updated.

Where the problem can come in, and I bet you dollars to donuts that it is the problem you are seeing, is in lock priorities.  Someone has a weak lock, like ACCESS SHARE lock, on that table, and they are camping on it indefinitely (maybe an idle-in-transaction connection which has been leaked?  Someone who opened psql, started a query in a repeatable read mode, and then went on vacation?).

The ADD COLUMN tries to take the ACCESS EXCLUSIVE it needs, and it queues up behind the first lock.

Now all future lock requests queue up behind the waiting ACCESS EXCLUSIVE request.

Conceptually, incoming lock requests which are compatible with the already-granted lock could jump over the waiting ACCESS EXCLUSIVE and be granted out of turn, but that is not how PostgreSQL does it.

You need to find the process which is holding the long-lived weak lock.

You can do this by querying the pg_locks table.

select * from pg_locks where
    granted and relation = 'cliente'::regclass \x\g\x

If you do this while everything is locked up, you should get only one answer (unless there are multiple long-lived culprits).  If you do this after you already killed the ADD COLUMN, then you might see lots of granted locks, but if you repeat it a few times there should one or a few which are staying around each time.

You can then take the PID that you got from pg_lock, and query with that into pg_stat_activity to see what the offender is doing:

select * from pg_stat_activity where pid=28731 \x\g\x

...

backend_start    | 2016-03-22 13:08:30.849405-07
xact_start       | 2016-03-22 13:08:36.797703-07
query_start      | 2016-03-22 13:08:36.799021-07
state_change     | 2016-03-22 13:08:36.824369-07
waiting          | f
state            | idle in transaction
backend_xid      |
backend_xmin     |
query            | select * from cliente limit 4;

So, it ran a query, inside a transaction, and then went idle without ever closing the transaction.  It is now 13:13, so they have been idle for 5 minutes.


Sqlite, DB clients & BLOB's: Practical DB Use I'm looking for an SQLite client that can help me to make practical use of the DB. The plan is to insert mp3's as BLOBs (or perhaps just as references to external files).  Each record will also include transcripts/notes/tags.

I'd like to be able to access this DB in such a way that I can click on the mp3 and listen to it, then add text data in the other fields, and finally search on text in the DB. LibreOffice Base is a nice client that allows me to define forms, but I'm not clear if simple access to the mp3 is possible. Are there other options?

I should say up front that I can do bash scripting and C, but I don't have python.

Following up on the comments, one option to simplify this requirement without resorting to developing a user interface to link sound files and a database, and considering your skills in shell programming, is using command line tools to edit/retrieve tags on MP3 files (Here's a QA with plenty of resources and alternatives for reading and manipulating mp3 tags from the command line), and using XMMS2 or similar to build and maintain the library of files, and see or manually edit individual file's MP3 tags.

The benefit of this approach is that XMMS2 is not only very good at handling a library of sounds/music (including creating playlists and so forth) but also in editing MP3 tags, which can be manipulated from command line scripts through a variety of open source well documented and popular tools.


Excessive page locking There is a query:

SELECT entry_id FROM table_name
WHERE (path_id = 7 AND value_string =  N'00020003-0004-0005-0006-000700080009' )

The table in this query contains some millions of records. Both path_id and value_string have their own non-clustered indexes.

When I use SQL Server Profiler to trace this query with Lock:Acquired event, I see that there are three IS page locks taken: one on a page that contains this value_string as '00020003-0004-0005-0006-000700080009', one on a page that contains path_id as 6 (which is staggering), and one on a page that contains path_id as 7 (which is expected).

The way I get information about pages: in Lock:Acquired there is a field TextData containing text like 1:87032; I take this and execute

DBCC PAGE ('mc33', 1, 87032, 3)

Then I get output like this:

The question is: why engine puts an IS lock on a page containing only sixes, if the query is only interested in sevens? This "excessive" locking causes us deadlocks when this query is executed in parallel with certain inserts, and I would like to know how I can get rid of such locking.

Locating a value implies traversal from the upper level point anchor (which explains the 6es). IS locks are required for traversal under read-commited isolation. This is neither "excessive" nor unexpected. If I'd venture a guess I would speculate that path_id is a low selectivity value and the index is not helping much.

You complain about deadlocks, I suggest you investigate them accordingly, starting with the deadlock graph.

As a side note, excessive locking (when truly a concern) can be alleviated using row versioning based isolation levels. If you're interested in low level details on how B-Tree seek actually works, start with Transaction Processing: Concepts and Techniques

To detail a bit: an inner page will contain a key and a pointer to a page/slot. This means that 'the page starting at the pointer contains values smaller than this key value'. Seek will search the upper page until it finds the first key bigger than the value seek-ed. It jumps to the pointer and continues from there, recursively. If the upper page says 'from page 148 all keys are smaller than 8' and the page 148 contains 6es, seek will  lock page with 6es. There is no magic pixie dust here. Locking lots of 6es is a clear hint that the key has very low selectivity: there are many rows that repeated a small range of key values.


Finding and storing groups of rows PK My problem is for Postgresql 10 but it probably is also relevant for other DB systems.

I have several tables in which I need to identify groups based on several criteria (some of them are PostGIS-geospatial criteria, some other are shared values in certain columns, ... that don't matter).

I am going to take a simple example. Let's say I have I have first table with people:

CREATE TABLE employees
(
id serial PRIMARY KEY,
name varchar(255),
desk_number varchar(20)
)

and that I want to group all employees that share the same desk (field desk_number).
Illustration :

employees
--------------
id | name | desk_number
1  | Bill | 314
2  | Joe  | 200
3  | Bob  | 314
4  | Matt | 189
5  | Sam  | 314
6  | Anne | 150

In this example, what I want to have is the list of employees sharing desk number 314 : Bill, Bob and Sam (id: 1, 3, 5).

My current query for finding this is :

WITH findpairs AS
(   -- part 1 of the query : selecting pairs
    SELECT s1.id AS id1, s2.id AS id2
    FROM employees AS s1
    JOIN employees AS s2
    ON s1.desk_number = s2.desk_number
    WHERE s1.id < s2.id
)
-- part2 of the query : filtering
SELECT s1.id1
FROM findpairs AS s1
LEFT JOIN findpairs AS s2
ON s1.id1 = s2.id2
WHERE s2.id2 IS NULL
;

Explanations :
In the first part of the query, I find all pairs of rows that for employees that share the same desk.

Without the WHERE clause, I would obtain as a result:

id1 | id2
 1  | 3
 3  | 1
 1  | 5
 5  | 1
 1  | 1
 2  | 2
 3  | 3
 4  | 4
 5  | 5
 6  | 6

The WHERE clause "s1.id < s2.id" prevents returning matches for a row with itself and makes sure that for two rows matching each other, only one row will be returned.

With this WHERE clause, what the part 1 of the query returns is :

id1 | id2
 1  | 3
 1  | 5
 3  | 5

I only two pairs of rows (1-3 and 1-5) to define the group I need ; the row 3-5 is unnecessary. That is why the second part of the query also does some filtering : because of the previous "s1.id < s2.id" clause, I know the lowest of the id values is only present in s1.id and never in s2.id. By using a LEFT JOIN, I can identify these rows from the set returned by part 1 and discard the other ones.

The final result returned is :

id1 | id2
 1  | 3
 1  | 5

And I can store this as such in a specific table :

CREATE TABLE matched_employees
(  id1 integer, id2 integer )

Question 1 : is there a less tedious way to do this ?
This takes a hell of a time to run on my server with my dataset.

Question 2 : instead to store the result in a table with pairs ( matched_employees ). Is there a better way to store this information (and easily access it later) ?

If you don't need real "pairs" you can get the list of employees sharing a desk using this:

select e1.*
from employees e1
where exists (select *
              from employees e2
              where e1.desk_number = e2.desk_number
                and e1.id <> e2.id);

If you want that as a single "list of IDs" you can use string_agg()

select string_agg(e1.id::text, ',') as id_list
from employees e1
where exists (select *
              from employees e2
              where e1.desk_number = e2.desk_number
                and e1.id <> e2.id);

Or use array_agg() to get those IDs as an array

Online example: http://rextester.com/AXVGA22292


Big size of database logs SQL Server 2008 I have a database which is running under Microsoft SQL Server 2008. Now, I have seen that the log of the database (the .ldf file) is growing to big size. The database file (.mdf) has a size of 630MB and the log file has a size of 12GB. I wonder what the reason for this can be. Is there a tool which let me seeing into the log where I can see, what is the reason for this big size?

What can I do to prevent that the log is growing to this big size?

You need to do log backups at least daily (we do them every 15 mintues). Read in books online about how to backup the log (not the daatbase, the log, these are two separate types of backups) and you should find directions for how to truncate the log without backing it up which you will likely need since you have let it get this size. The make sure you have a current backup of the database before you start. Then truncate the log and immediately set of regularly schedueld backups so this doesn't happen again.


SQL statement slower on as400 console than from remote odbc I am having a strange problem on one of my i systems running 7.1

I am using jdedwards 8.0 as erp but the issue is not related as i just found out.

After some research it came down to this. I run the same simple select in a specific table. First time i run it from one fat client which connects through odbc and the second time i run it through STRSQL.

first time it takes 1 sec maximum and second time it takes 4-5 seconds.

Debug (STRDBG) shows that when running on server it has an index suggestion that already exists! and odbc machine uses it.

I tried droping and adding the index from both erp and from navigator but results are the same.

if i run the same sql from STRSQL for the same table but on our production library it runs fast like odbc case.

sorry for my ignorance but iam not even close to being a guy that knows as400...

P.S. some more info:

Its a simple SQL that i am running:

SELECT  *  FROM testDTA/F74G0904  WHERE  ( WFDCT = 'JE' AND WFDOC =
166823.000000 AND WFKCO = '00003' AND WFDGJ = 114090 AND WFJELN =
1.000000 AND WFLT = 'AA' AND WFEXTL = ' ')
The table has around 10.000.000 in both libraries.

  GREEN SCREEN - LIB1 - 4 SECS

  GREEN SCREEN - LIB2 - 1 SEC

  ODBC CLIENT - LIB1 - 1 SEC

  ODBC CLIENT LIB2 - 1 SEC

It seems to be library specific and connection specific at the same time.

DSPJOBLOG in the case of "GREEN SCREEN - LIB1 - 4 SECS" suggests an index that already exists. Also as i said tried to drop and create the index.

Take a look on the sam eSQL in different libraries, optimizer and execution plan:

Comparison images

Thanks

The problem was something simple yet hard to find.

The indexes' sort sequence was wrong.

The clients were wrongly configured to use language preference for sort sequence.

So when the indexes were created they had language specification for sorting.

The server when a job was submitted, was looking for indexes with *HEX specification so he was ignoring the existing indexes.


sql job cancel triggers failure notification When I cancel a sql agent job running an ssis package, I get a failure email because I set up the notifications to email when the Job Fails.

Does anyone know why this is considered a 'failure'?

Because SSIS package are executed as step of type CmdExec. When you cancel this step the process being run is forced to end prematurely.  Consequently premature termination of a process is considered a failure. Behind the scene cancelling a job calls for sp_stop_job

For other type of job steps in a SQL agent job for e.g. T-SQL command performing a backup, when you cancel this job it finishes the currently executing step so it is not considered a failure.


STARTUP command fails : Why is that? I just installed Oracle Database Express Edition 11g Release 2.This is the first time I am using Oracle. Earlier I have used MySql and MSSql and I bet this one is a lot different !

These are the things I see in the start menu :

To start the oracle database, I click start database. Then in the prompt I do :

C:\Windows\system32>SQLPLUS / AS SYSDBA

SQL*Plus: Release 11.2.0.2.0 Production on Sat Mar 2 20:37:14 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

SQL> STARTUP
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL>

I did exactly the same as written in DOCS but then why does I see that message as I run the command STARTUP.

Also certain commands like :

show databases;
use database db_name;

don't work in it ! Why is that ?

And isn't there a graphical query browser that comes packed with this RDMS ?

To restart Oracle, connect via SQL-PLUS as SYSDBA as you did previously, and type in the commands

shutdown normal;
startup mount;
alter database open;
exit;

Oracle SQL Developer can help you graphically administer the Oracle Database.

Most Oracle metadata are stored in special tables. For example,

To show all tables: select * from cat;
To show all users/schemas: select * from dab_users;
To change schemas: alter session set current_schema=NEW_SCHEMA_NAME;

Are there any other specific Oracle commands you were looking for?


How to store likes to posts in Parse.com database? In my parse database I have a classed called Post and Like. Should I create a field likes in Post class with Relation<Like> type or there is another better option of storing likes in Parse DB?

For those purpose, I only use relations in the USER table with users ids stored as pointers inside.

Using a cloud code function, I provide a  parameter to add , del or whatever instructions to apply to a single or an array of users. Cloud gives the opportunity to create collateral events such as "a user like this"

As a reading step, a query is applied to the relation to retrieve parts or all users.

The column is named with the purpose such as I will add 2 columns to mention  respectively "like" , "dontLike"

However, if one need more than a single information , a second table needs to be created and linked also with a relation.
For example, a new table with a first column which is a regular pointer to users, other  columns to stored the additional attributes.
Which means that this new table must be first saved, to be able to stored its id in the relation.


Oracle 11g and 18c in one machine I have a 64-bit Windows 7 machine with an Oracle 11g version installed. I want to install the Oracle 18c version on the same machine.

Is it possible? If yes, what are the configurations I need to do for them (11g and 18c) to coexist?

Thanks

Can you run 11g and 18c on the same machine for SE2/EE editions?

yes.

I'd place them in different ORACLE_HOMEs.  BUT, you only need to run 1 Listener.

Can you run 11g and 18c on the same OS if both are XE?

no. (But - 18c XE can run in a VM)

11g XE License limits you to one XE instance per physical machine.

  Any use of the Oracle Database Express Edition is subject to the
  following limitations;

  Express Edition is limited to a single instance on any server;
  Express Edition may be installed on a multiple CPU server, but may only be executed on one processor in any server;
  Express Edition may only be used to support up to 11GB of user data (not including Express Edition system data);
  Express Edition may use up to 1 GB RAM of available memory.

18c XE License does not have Condition 1 from 11g XE.  As I understand, this was done so that you can run 18c XE inside a VM:

  Oracle Database 18c Express Edition automatically constrains itself to
  the following resource restrictions;

  2 CPU threads;
  2 GB of RAM; and
  12 GB of user data.

The code also prevents you from running multiple XE instances on the same host.  ( source )

  4.2 Oracle Database XE Installation and Execution Restrictions

  Oracle Database XE restricts itself to only one installation per logical environment. The logical environment can either be a virtual
  host such as a VM or container, or a physical host. If more than one
  Oracle Database XE installation is attempted to be started in such a
  logical environment, an "ORA-00442: Oracle Database Express Edition
  (XE) single instance violation error" is raised and the database will
  not start.

But, certain VM technologies are supported.  That is:  You can run multiple instance of XE as long as each one is in a different Virtual Host.

  Oracle certifies the following virtualization technologies with Oracle
  Database on Windows:

  Oracle VM Server
  Microsoft Hyper-V


Calculating Running Total After Reset I have a SQL Server 2012 table which has customer id, amount, and a reset columns. I’m trying to calculate the running total and also calculate the running total after reset. Once the reset "flag" is set then I want to calculate the running total after the running total.

I have the following code and not sure if there's a way to calculate running total after a break point.

WITH a
AS
(SELECT
        *
        ,SUM(amount) OVER (ORDER BY id) AS RunningTotal
    FROM Table1)
SELECT
    *
    ,CASE
        WHEN resetYN = 1 THEN 0
        ELSE Amount + LAG(RunningTotal, 1) OVER (ORDER BY id)
    END AS ResetRunningTotal
FROM a

Here's one method of getting running totals after reset. There are probably others out there. Here is your test data:

CREATE TABLE user20907 (
ID INTEGER NULL,
CUSTOMERID INTEGER NULL,
AMOUNT INTEGER NULL,
RESETYN INTEGER NULL
);

BEGIN TRANSACTION;
INSERT INTO user20907 VALUES (1,111,5,0);
INSERT INTO user20907 VALUES (2,111,6,0);
INSERT INTO user20907 VALUES (3,111,7,1);
INSERT INTO user20907 VALUES (4,111,8,0);
INSERT INTO user20907 VALUES (5,111,9,0);
INSERT INTO user20907 VALUES (6,111,4,1);
INSERT INTO user20907 VALUES (7,111,7,0);
INSERT INTO user20907 VALUES (8,111,9,0);
COMMIT TRANSACTION;

First get a running total of the ResetYN column. I'm going to call it SUM_OF_RESETYN. In an outer query you can partition by both CustomerID and SUM_OF_RESETYN. For the reset running total, instead of taking Amount directly you can do a case statement similar to what you already have:

SELECT
      t.ID
    , CUSTOMERID
    , AMOUNT
    , RESETYN
    , RUNNINGTOTAL
    , SUM(CASE WHEN RESETYN = 1 THEN 0 ELSE AMOUNT END) OVER (PARTITION BY CUSTOMERID, SUM_OF_RESETYN ORDER BY ID) RESETRUNNINGTOTAL
FROM
(
    SELECT
          ID
        , CUSTOMERID
        , AMOUNT
        , RESETYN
        , SUM(AMOUNT) OVER (PARTITION BY CUSTOMERID ORDER BY ID) RUNNINGTOTAL
        , SUM(RESETYN) OVER (PARTITION BY CUSTOMERID ORDER BY ID) SUM_OF_RESETYN
    FROM user20907
) t;

I ran that query and the results matched yours, except that I did not have a NULL value for RESETRUNNINGTOTAL for ID 1. Was that intentional? If so it should be easy to integrate that bit of logic into my query.


Mirroring vs. Log Shipping The software company I work for is planning to migrating our infrastructure into the cloud. Our current focus is to find the best database replication method.

Current Servers:

SQL Server 2008 SE - Located in NY (In house - Master)

SQL Server 2012 SE - Located in Chicago (Rackspace - Slave)

We have been considering log shipping, but we are also aware of mirroring and replication.
 Can someone please provide some insight on what our best approach for this task may be? We simply want to keep both databases in sync with each other. Real time does not matter yet, but it will in the near future.

Note: Our long term goal is to make Rackspace our primary, and NY our secondary.

Log Shipping essentially involves three jobs. The first job backs up the log on the primary server and stores those backups in a local folder. The second job copies those files across the network to the secondary server. The third jobs restores those backups using the WITH STANDBY option. Log Shipping is set up on an entire database. The database on the secondary server is accessible but is read-only.

Requires: A shared folder on the primary server. Firewall configuration to allow the secondary server to access the file share. Security set up so the proxy on the second server has permissions to access the file share. This may be an issue for you in a hosted situation.

Caveat: Log shipping is not real-time. The common interval for the first job is 15 minutes. An unplanned failover will lose data.

Caveat: Log Shipping uses transaction log backups, so its design must be done in conjunction with your backup design.

Database Mirroring involves the primary server sending individual transactions to the secondary server, in either a synchronous fashion or an asynchronous fashion (asynchronous is enterprise edition only). Like Log Shipping, Mirroring It is set up on an entire database. Unlike Log Shipping, the secondary database is inaccessible.

Requires: An endpoint to be created on each sever for the mirroring traffic. Firewall allowing traffic from and to that one port.

In a synchronous mirroring setup, an unplanned failover will not lose data.

Both Mirroring and Log Shipping are creating a copy of an entire database so there is no object requirements. Heaps, Clustered Tables, tables with and without keys - all get copied.

Note: Given the requirement for file sharing, I disagree with Szymon's comment about log shipping being easier to set up and maintain and requiring less resource. Installing the File Server role on a Windows Server is increasing resource requirements as well as increasing the surface area of attack. Additionally, in a log shipping unplanned failover, bringing the secondary online is a pain. Lots of steps, most of which involve running stored procedures in a query window.


How to verify galera is synced I have been using mysql master:master replication and was always able to check the status of replication using this command:
mysql > show slave status\G
I was able to see this in the show slave status command:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
The seconds_behind_master always let me know if I was behind on one of my servers.

I am not using galera and am not sure how to verify if everything is up to date.
I have come across this article "Monitoring Cluster Status"
I realize I can use this command to see how many nodes are in the cluster:
SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size';

But I am not sure if this command if good enough to tell whether or not they are exactly up to date:
SHOW GLOBAL STATUS LIKE 'wsrep_local_state_comment';
This command currently show synced but I cannot tell if this is the best way to tell if I am good to go.

If wsrep_local_state_comment, wsrep_ready, and wsrep_connected show the correct values, then that particular node is good.

There's no Seconds_Behind_Master equivalent, because Galera is synchronous -- a healthy node can't be lagging, by definition and by design... a slow node slows down all the others, it doesn't lag behind them.


Possible reasons for irregularly high query execution time? The above is a graph showing the average response times per second (including error bars) for an experiment where I simulate 30 clients which are all invoking the same query multiple times. I ran this experiment over and over and over again using also different queries but the result is always that around 20 seconds there is this one peak.

I'm not that familiar with postgres. The only thing that I thought of which could be responsible for this behavior is the vacuum daemon. However I enabled logs for the deamon and it turns out that it's not active at all in the period of the experiment. Is there anything else running at arbitrary points which could cause a high query time for a short period?

The function:

CREATE OR REPLACE FUNCTION insert_message(_queue_id BIGINT, _source_client_id BIGINT, _receiver_client_id BIGINT, _content TEXT, OUT status SMALLINT) AS $$
    BEGIN
                INSERT INTO message VALUES(DEFAULT, _queue_id, _source_client_id, _receiver_client_id, _content, DEFAULT);
                status = 0;
    END;
$$ LANGUAGE plpgsql;

The respective table:

CREATE TABLE message(
    id BIGINT DEFAULT nextval('message_id_seq'),
    queue_id BIGINT NOT NULL,
    sender_client_id BIGINT NOT NULL,
    receiver_client_id BIGINT NOT NULL,
    content TEXT NOT NULL,
    insertion_time TIMESTAMP NOT NULL DEFAULT current_timestamp,
    PRIMARY KEY(id),
    FOREIGN KEY(queue_id) REFERENCES queue(id) ON DELETE CASCADE,
    FOREIGN KEY(sender_client_id) REFERENCES client(id) ON DELETE CASCADE,
    FOREIGN KEY(receiver_client_id) REFERENCES client(id) ON DELETE CASCADE
);

Index on the table:

CREATE INDEX message_index ON message(queue_id, receiver_client_id, sender_client_id);

EDIT: Ran it on AWS. Same pattern. Thus it's no hardware thing.

That spike is probably caused by dirty data pages being flushed to disk. Raise the checkpoint_segments parameter in the postgresql.conf file.

http://www.postgresql.org/docs/current/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-CHECKPOINTS

http://www.postgresql.org/docs/current/static/wal-configuration.html


Complex Delete Cascade Including Self-Referencing Table Above is a diagram of my data structure. It represents a hierarchy which can contain three different types of "elements": "A"s, "B"s and "C"s. The relationships show the delete cascade behavior I would like to use if it were possible.

All types have properties in common, including columns that show position in the hierarchy (Parent and Index) and type of element. Those common columns are stored in the ElementBase table.

Each type of element also has unique properties which are stored on the the corresponding tables based on element type.

Each of the rows in AData, BData and CData reference a unique main row in ElementBase. "A" and "C" elements also each reference a "B" element. "B" elements can have 0 or more "S"s.

My problem is: how can I maintain reference integrity and support something like cascade deletion?

I want to be able to delete a row from ElementBase and have the corresponding row in either AData, BData or CData also be deleted. If, for example, a "B"-type element gets deleted from ElementBase, firstly the corresponding row from BData should be delete, then every "C"-type element that references it needs to be deleted in both the ElementBase and CData tables, and all "A"-type elements need to have their references set to NULL in AData.

And the cherry on top: if the element I delete has children of any type, I want this same logic to run recursively down the hierarchy.

Since ElementBase is self-referencing, I can't use the simple ON DELETE CASCADE feature in that table. I also can't use that on AData or CData because they both reference BData which could then result in "multiple cascade paths" which are apparently evil in SQL Server.

One alternative I've found is INSTEAD OF triggers. The problem with that is this behavior has to be recursive and I can't quite figure out how to enable them to be recursive and also eventually do the original delete at the end.

I think I have captured what you need in this basic design:
db<>fiddle
ElementBase
Self-fk for the hierarchy:
CREATE TABLE dbo.ElementBase
(
    id integer NOT NULL,
    parent_id integer NOT NULL,
    element_type char(1) NOT NULL,

    -- id key
    CONSTRAINT [PK dbo.ElementBase id]
        PRIMARY KEY CLUSTERED (id),

    -- fk target
    CONSTRAINT [UQ dbo.ElementBase id, element_type]
        UNIQUE NONCLUSTERED (id, element_type),

    -- self fk
    CONSTRAINT [FK dbo.ElementBase parent_id id]
        FOREIGN KEY (parent_id)
        REFERENCES dbo.ElementBase (id),

    -- valid element types
    CONSTRAINT [CK dbo.ElementBase element_type]
        CHECK (element_type IN ('a', 'b', 'c')),

    -- for maintenance
    INDEX [IX dbo.ElementBase parent_id]
        NONCLUSTERED (parent_id)
);

BData
Cascade delete from ElementBase:
CREATE TABLE dbo.BData
(
    id integer NOT NULL,
    element_type AS CONVERT(char(1), 'b') PERSISTED,

    -- id key
    CONSTRAINT [PK dbo.BData id]
        PRIMARY KEY CLUSTERED (id),

    -- fk to ElementBase
    CONSTRAINT [FK Bdata ElementBase id, element_type]
        FOREIGN KEY (id, element_type)
        REFERENCES dbo.ElementBase (id, element_type)
        ON DELETE CASCADE
);

AData
No cascading delete from ElementBase; SET NULL cascading delete from BData:
CREATE TABLE dbo.AData
(
    id integer NOT NULL,
    element_type AS CONVERT(char(1), 'a') PERSISTED,
    b_element integer NULL,

    -- id key
    CONSTRAINT [PK dbo.AData id]
        PRIMARY KEY CLUSTERED (id),

    -- fk to ElementBase
    CONSTRAINT [FK Adata ElementBase id, element_type]
        FOREIGN KEY (id, element_type)
        REFERENCES dbo.ElementBase (id, element_type)
        ON DELETE NO ACTION,

    -- fk to BData
    CONSTRAINT [FK dbo.AData dbo.BData id b_element]
        FOREIGN KEY (b_element)
        REFERENCES dbo.BData (id)
        ON DELETE SET NULL,

    -- fk lookup
    INDEX [IDX dbo.AData b_element]
        NONCLUSTERED (b_element),
);

CData
No cascading delete from ElementBase; SET NULL cascading delete from BData:
CREATE TABLE dbo.CData
(
    id integer NOT NULL,
    element_type AS CONVERT(char(1), 'c') PERSISTED,
    b_element integer NOT NULL,

    -- id key
    CONSTRAINT [PK dbo.CData id]
        PRIMARY KEY CLUSTERED (id),

    -- fk to ElementBase
    CONSTRAINT [FK Cdata ElementBase]
        FOREIGN KEY (id, element_type)
        REFERENCES dbo.ElementBase (id, element_type)
        ON DELETE NO ACTION,

    -- fk to BData
    CONSTRAINT [FK dbo.CData dbo.BData b_element id]
        FOREIGN KEY (b_element)
        REFERENCES dbo.BData (id)
        ON DELETE CASCADE,

    -- fk lookup
    INDEX [IDX dbo.CData b_element]
        NONCLUSTERED (b_element),
);

S
Cascading delete from BData:
CREATE TABLE dbo.S
(
    s_id integer NOT NULL,
    b_element integer NOT NULL,

    -- id key
    CONSTRAINT [PK dbo.S s_id]
        PRIMARY KEY CLUSTERED (s_id),

    -- fk to BData
    CONSTRAINT [FK dbo.S dbo.BData b_element id]
        FOREIGN KEY (b_element)
        REFERENCES dbo.BData (id)
        ON DELETE CASCADE,

    -- fk lookup
    INDEX [IDX dbo.S b_element]
        NONCLUSTERED (b_element),
);

ElementBase Instead of Delete Trigger
This handles deleting related items within ElementBase, then cascading deletes to AData and CData. Cascade deletes to BData and S are handled by RI:
CREATE OR ALTER TRIGGER [dbo.ElementBase IOD Cascade]
ON dbo.ElementBase
INSTEAD OF DELETE AS
BEGIN
    SET ROWCOUNT 0;
    SET NOCOUNT ON;

    -- Exit if no work to do
    IF NOT EXISTS (SELECT * FROM Deleted) RETURN;

    -- Holds ElementBase rows identified for deletion
    CREATE TABLE #ToDelete
    (
        id integer PRIMARY KEY,
        element_type char(1) NOT NULL
    );

    -- Find all related ElementBase records
    WITH R AS
    (
        -- Anchor: parent ElementBase rows
        SELECT D.id, D.element_type
        FROM Deleted AS D

        UNION ALL

        -- Recursive: children
        SELECT EB.id, EB.element_type
        FROM R
        JOIN dbo.ElementBase AS EB
            ON EB.parent_id = R.id
            AND EB.id <> R.id
    )
    INSERT #ToDelete
        (id, element_type)
    SELECT DISTINCT
        R.id,
        R.element_type
    FROM R
    OPTION (MAXRECURSION 0);

    -- Delete related CData records (manual cascade)
    DELETE CD
    FROM #ToDelete AS TD
    JOIN dbo.CData AS CD
        ON CD.id = TD.id
    WHERE
        TD.element_type = 'c';

    -- Delete related AData records (manual cascade)
    DELETE AD
    FROM #ToDelete AS TD
    JOIN dbo.AData AS AD
        ON AD.id = TD.id
    WHERE
        TD.element_type = 'a';

    -- Delete ElementBase (BData, S records via cascade)
    DELETE EB
    FROM #ToDelete AS TD
    JOIN dbo.ElementBase AS EB
        ON EB.id = TD.id;
END;


Using indexed views for aggregates - too good to be true? We have a data warehouse with a fairly large record count (10-20 million rows) and often run queries that count records between certain dates, or count records with certain flags, e.g.

SELECT
    f.IsFoo,
    COUNT(*) AS WidgetCount
FROM Widgets AS w
JOIN Flags AS f
    ON f.FlagId = w.FlagId
WHERE w.Date >= @startDate
GROUP BY f.IsFoo

The performance isn't awful, but can be relatively sluggish (perhaps 10 seconds on a cold cache).

Recently I discovered that I can use GROUP BY in indexed views and so tried out something similar to the following

CREATE VIEW TestView
WITH SCHEMABINDING
AS
    SELECT
        Date,
        FlagId,
        COUNT_BIG(*) AS WidgetCount
    FROM Widgets
    GROUP BY Date, FlagId;
GO

CREATE UNIQUE CLUSTERED INDEX PK_TestView ON TestView
(
    Date,
    FlagId
);

As a result the performance of my first query is now < 100ms, and the resulting view & index is < 100k (although our row count is large, the range of dates and flag IDs means that this view only contains 1000-2000 rows).

I thought that perhaps this would criple the performance of writes to the Widget table, but no - the performance of inserts and updates into this table is pretty much unaffected as far as I could tell (plus, being a data warehouse this table is updated infrequently anyway)

To me, this seems way too good to be true - is it? What do I need to be careful with when using indexed views in this way?

As you've noted, the view itself only materializes a small number of rows - so even if you update the whole table, the additional I/O involved with updating the view is negligible. You probably already felt the biggest pain you're going to feel when you created the view. The next closest will be if you add a gazillion rows to the base table with a bunch of new IDs that require new rows in the view.

This isn't too good to be true. You're using indexed views exactly how they were meant to be used - or at least one of the most effective ways: to pay for future query aggregations at write time. This works best when the result is much smaller than the source and of course when the aggregations are requested more often than the underlying data is updated (more common in DW than OLTP, generally).

Unfortunately many people think indexing a view is magic - an index won't make all views more efficient, especially views that simply join tables and/or produce the same number of rows as the source (or even multiply). In these cases the I/O from the view is the same or even worse than the original query, not only because there are the same or more rows, but often they are storing and materializing more columns, too. So materializing those in advance doesn't provide any gains, since - even with SSDs - I/O, network, and client processing/rendering still remain the primary bottlenecks in returning large resultsets to the client. The savings you get in avoiding the join at runtime just aren't measurable in comparison to all the other resources you're still using.

Like non-clustered indexes, just be careful to not over-do it. If you add 10 different indexed views to one table, you're going to see more impact to the write portion of your workload, especially if the grouping column(s) are not (in) the clustering key.

Gosh, I've been meaning to blog about this topic.


Consults and counts in multiple databases I'm not very familiar with mysql advanced queries, because I'm more of a designer as a developer.
My problem - I have 3 tables:

Clients

------------------------------------------------------
| id |        name        |          email           |
------------------------------------------------------
| 1  | Client 1           | client1@somecompany.com  |
| 2  | Client 2           | client2@somecompany.com  |
| 3  | Client 3           | client3@somecompany.com  |
| 4  | Client 4           | client4@somecompany.com  |
| 5  | Client 5           | client5@somecompany.com  |
| 6  | Client 6           | client6@somecompany.com  |
| 7  | Client 7           | client7@somecompany.com  |
| 8  | Client 8           | client8@somecompany.com  |
| 9  | Client 9           | client9@somecompany.com  |
| 10 | Client 10          | client10@somecompany.com |
------------------------------------------------------

Products
--------------------------------
| id | client_id |  item_name  |
--------------------------------
| 1  | 6         |  something  |
| 2  | 4         |  something  |
| 3  | 6         |  something  |
| 4  | 2         |  something  |
| 5  | 5         |  something  |
| 6  | 7         |  something  |
| 7  | 9         |  something  |
| 8  | 10        |  something  |
| 9  | 1         |  something  |
| 10 | 5         |  something  |
| 11 | 8         |  something  |
--------------------------------

product_status

------------------------------
| id | product_id |  status  |
------------------------------
| 1  | 1          | new      |
| 2  | 2          | used     |
| 3  | 3          | new      |
| 4  | 4          | used     |
| 5  | 5          | used     |
| 6  | 6          | new      |
| 7  | 7          | used     |
| 8  | 8          | used     |
| 9  | 9          | new      |
| 10 | 10         | used     |
| 11 | 11         | new      |
------------------------------

I need know the following:

Id and name of client
How many items has the client
How many items "new" and "used"

Like this

expected results
-----------------------------------------------------------------
| client_id | client_name | total_items | Items_new | Item_used |
-----------------------------------------------------------------
| 1         | Client 1    | 1           | 1         | 0         |
| 2         | Client 2    | 1           | 0         | 1         |
| 3         | Client 3    | 1           | 0         | 1         |
| 4         | Client 4    | 1           | 0         | 1         |
| 5         | Client 5    | 1           | 0         | 1         |
| 6         | Client 6    | 2           | 2         | 0         |
| 7         | Client 7    | 1           | 0         | 1         |
| 8         | Client 8    | 1           | 1         | 0         |
| 9         | Client 9    | 1           | 0         | 1         |
| 10        | Client 10   | 1           | 0         | 1         |
----------------------------------------------------------------

How can I achieve this?
Thanks for your help!

You can join the tables and then use (conditional) aggregation.

SELECT c.id client_id,
       c.name client_name,
       count(p.id) total_items,
       count(CASE
               WHEN ps.status = 'new'
                 1
             END) items_new,
       count(CASE
               WHEN ps.status = 'used'
                 1
             END) items_used
       FROM clients c
            LEFT JOIN products p
                      ON p.client_id = c.id
            LEFT JOIN product_status ps
                      ON ps.product_id = p.id
       GROUP BY c.id,
                c.name;

This shows data for all clients. If you only want them actually having a product, you need to change the first LEFT JOIN to INNER JOIN.

It also counts products which have no status in items. If you don't want to include such products, change the second LEFT JOIN to INNER JOIN as well.


PowerBi Line Graph: Tooltip has different decimal places I have a line graph showing water level data.

The graph has a tool tip which displays the values when you hover over with the mouse. See below.

My problem is that the number of decimal places displayed for Recorded Flow is different. I want them all to display with 3 decimal places.

All three values are stored in an SQL using a float datatype. The Warning and cutoff level values are a single value (which could be 1, 2, or 3 decimal places but displayed to 3 decimals), where as Recorded flow is a variable time series stored to 8-10 decimal places, but only ever displays as 2 decimal places. (and I want 3.)

I can't see anywhere that the datatype is altered or that the values have been rounded. I have removed and re-added the time-series, and changed the order of appearance but it makes no difference. I've even rounded the data in SQL to see if PowerBi was sniffing the data to determine formatting.

All web search results I've found talk about the lack of formatting control, but not that they're inconsistent or appear differently.

What am I missing?

The answer is embarrasingly simple. in PBIRS:

Click on the datasource field
Click modelling in the tool bar
Look for the formatting options

In PowerBI

Click on the datasource field
Click Columns tools in the tool bar
Look for the formatting options.


Why does database recovery take so long? On my SQL Server 2012 x64 Std instance I had a large transaction fail due to the transaction log filling up yesterday, it couldn't be rolled back and SQL Server has restarted the specific DB to perform recovery.

The DB is around 750GB with the tlog hitting 170GB (very large ETL job was running). The job was only running for a few hours but the recovery so far has taken >24 hours (70% complete, on phase 3 of 3).

What I can't understand it why it's taking so long? There doesn't seem to be any pressure on the disks, sys.dm_exec_requests shows it waiting on PAGEIOLATCH_EX/SH, which I'd expect. I could've restored the entire DB in this time...

If anyone can shed any light, it would be greatly appreciated.

EDIT: As requested, getting the output of the error log:

Recovery of database 'MyDB' (6) is 77% complete (approximately 28100 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDB' (6) is 77% complete (approximately 28095 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDB' (6) is 77% complete (approximately 28099 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDB' (6) is 77% complete (approximately 28102 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDB' (6) is 77% complete (approximately 28097 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.

Also, the sys.dm_exec_requestsinfo you're after:

session_id  command status  percent_complete
35  DB STARTUP  background  86.06061

IFI and "Perform volume maintenance tasks" enabled/granted.

I had a large transaction fail due to the transaction log filling up yesterday, it couldn't be rolled back and SQL Server has restarted the specific DB to perform recovery.

This is not totally correct when a transaction starts in SQL Server it reserves space in transaction log in case the transaction has to rollback. From Transaction Log architecture BOL doc

  Rollback operations are also logged. Each transaction reserves space
  on the transaction log to make sure that enough log space exists to
  support a rollback that is caused by either an explicit rollback
  statement or if an error is encountered. The amount of space reserved
  depends on the operations performed in the transaction, but generally
  it is equal to the amount of space used to log each operation. This
  reserved space is freed when the transaction is completed

So I guess you can understand now.

  What I can't understand it why it's taking so long?

Query rollback operation is mostly single threaded while when same query executes it can use parallelism/multiple threads to perform same operation making it very fast as compared to the rollback. I suggest you read What Bob Dorr has to say about rollback taking long time. To get more insight about what occurs when rollback is in progress please read This article. Restarting SQL Server service is not going to help much, after restart the rollbak process had to start again. You have to wait and let the rollback process complete.

You can also use sys.dm_exec_requests to track your rollback.

select
session_id,
command,
status,
percent_complete
from sys.dm_exec_requests
where command IN ('killed/rollback','rollback','db_startup')

The other reason which Remus mentioned is too many VLF's. If you have to many VLF's recovery can take lot of time. Also other factor that can delay recovery is if SQL Server service account does not have Perform Volume maintenacce task right or instant file initialization. You can use this link to check if IFI is present or not.

EDIT:

From The output you posted

session_id  command status  percent_complete
35  DB STARTUP  background  86.06061

The startup process is 86 % complete. Just keep monitoring it, eventually it will complete. Also as stated rollback process can get blocked and can incur waits so keep monitoring that as well.

  2012 x64 Standard SP2

You have standard edition which cannot take advantage of fast recovery which is present in enterprise edition. With fast recovery database can come online after second phase of recovery(the redo phase) however it is alwasy not the case so please read the article. As an additional reading you can read When is fast recovery used. Standard edition limitation could be one more reason why it is taking database time to come online.

  One other thing, the rollback session (35) is itself blocking another system process from performing CHECKPOINT

Yes it can and you cannot do anything as you have no control over system process in terms you cannot kill it.


Differentiating tables and views in INFORMATION_SCHEMA.COLUMNS I have a view which selects information from INFORMATION_SCHEMA.COLUMNS, which seems to give me both columns and views. Is there an easy way to tell which rows are columns and which are views? Should I do a comparison with INFORMATION_SCHEMA.VIEWS? I need both columns and views, I just want to be able to tell which is which.

You will need to do a left outer join to INFORMATION_SCHEMA.VIEWS to see if they are views or not.

select CASE WHEN b.TABLE_NAME is not null then 'view' else 'table' end OBJECT_TYPE,
    a.*
from INFORMATION_SCHEMA.COLUMNS a
LEFT OUTER JOIN INFORMATION_SCHEMA.VIEWS b ON a.TABLE_CATALOG = b.TABLE_CATALOG
    AND a.TABLE_SCHEMA = b.TABLE_SCHEMA
    AND a.TABLE_NAME = b.TABLE_NAME


Why SQL Server Express 2012 take automatically backup in "Virtual Device" without virtual device configuration? I have noticed that after migration of SQL Server 2012 Enterprise to SQL Server 2012 Express, every day it shows "spurious" virtual backup after taking manual backup of database.

When I execute this T-SQL to show backup details

select * from msdb.dbo.backupset;

select * from msdb.dbo.backupmediafamily;

It shows that details of backup with manual backup details & virtual device backup details. I am also attaching the screenshot of backupset & backupmediafamily :

* After Edited*

I have check out from windows PowerShell , The SQLWriter service is running in window services. And my Windows Server 2012 R2 has installed in Domain environment. So, The virtual backup is running from SQLWriter service.

Why does it show most of the automatically backup taken in virtual device with NT AUTHORITY\SYSTEM user name & by default it shows the backup with virtual device & their some virtual location. But my SQL Server database is not attached with any SAN device.

The database is on SQL Server 2012 Express. So, there is no any option of SQL Server Agent Automatic Backup. How it shows automatic backup in log files. Automatically it take the "Device_Type=7" which signifies virtual device location, as per MSDN  https://msdn.microsoft.com/en-us/library/ms178018(v=sql.110).aspx & https://technet.microsoft.com/en-us/library/ms190284(v=sql.110).aspx  if "Device_Type=7" i.e, Virtual device.

But here I would like to say that before migration of database, the database was on SQL Server 2012 Enterprise & it was setup with SQL Server Agent automatic backups. But this time using SQL Server 2012 Express.

Even automatically it is taking backup of "System databases" as well as "User Databases".

My question is that if any virtual device has not setup then why my SQL Server Express database is taking virtual backups. Even this database is my audit database. If this backup will going on continuation manner.

Is there any impact of my audit data?

Any suggestion would be appreciated?

why it show most of the automatically backup taken in virtual device with
  "NT AUTHORITY\SYSTEM" user name & by default it shows the backup with
  virtual device & their some virtual location.

This is because some tool or windows server native backup feature is taking a Snapshot backup of SQL Server. Such backups normally use NT AUTHORITY\SYSTEM and SQL Server VSS writer service to connect to perform Snapshot backup.

Please note since you said there is no third party backup scheduled I have guessed here that it might be windows native backup. If you want to find more about such backup start the profiler and capture the events when backup is running. Or you can schedule the profiler. I must say profiler can create load so be aware about that  so be selective in events when configuring the profiler.

Workaround:

If you are very much certain that you don't need this backup, which IMO is useless as compared to native TSQL backup, you can go ahead and disable SQL Server VSS writer service. You can go to Services.msc and you would find this service there and then disable it.

  If this backup will going on continuation manner. Is there any impact of my Audit Data?

No, such backup is not going to have any affect on your data but yes it would be utilizing system resource when running.


Configuration required for a big mysql database around 20GB I need to host a single machine database (constraints), wherein the database size is roughly 20gb. Most of the tables are in MyISAM. Any way I can actually run it with decent speed ? Alternatives I can look into are :
TokuDB, Percona, MariaDB.

A few of the tables have around 50-60 million rows.
So what I want to know is, how much of RAM and possibly what my.cnf settings might be required to host such a database.

Thanks

The main configuration settings are in my memory blog.  Most important is 20% of RAM for key_buffer_size.  (However, it does not need to be more than the sum total of Index_length of all the SHOW TABLE STATUS.  This will also do the arithmetic:

SELECT  ENGINE,
    ROUND(SUM(data_length) /1024/1024, 1) AS "Data MB",
    ROUND(SUM(index_length)/1024/1024, 1) AS "Index MB",
    ROUND(SUM(data_length + index_length)/1024/1024, 1) AS "Total MB",
    COUNT(*) "Num Tables"
FROM  INFORMATION_SCHEMA.TABLES
WHERE  table_schema not in ("information_schema", "performance_schema")
GROUP BY  ENGINE;

Beware of a large WP installation -- it uses one database, hence one directory, for each 'user'.  After thousands of users, the OS will bog down because of this.

TokuDB's niche is elsewhere; I would not expect it to be useful for this task.


Last record by date given a foreign table Consider this 2 tables:

CREATE TABLE massifs (
id uuid primary key,
name text unique);

CREATE TABLE records (
id uuid primary key,
massif_id uuid  not null references massif(id)
record_date timestamptz not null
some_usefulldata text)

I'm trying to get the last result for each massif. Something with a subquery ?

For example a result could be

massifs.name | record_date::date | some_usefulldata
mont-blanc   | 2020-02-10        | 10
aravis       | 2019-03-10        | 12

I think that the question is the same if I add this 2 tables : https://www.postgresql.org/docs/current/tutorial-fk.html and if I ask this question "give me the last weather records for each cities".

Ok, I finally got my Sh** together. Was a bit rusty :

SELECT result.* FROM massifs m
JOIN LATERAL (SELECT m.id, m.name, r.record_date, r.some_usefulldata
    FROM records WHERE (m.id=r.massif_id)
    ORDER BY r_record_date DESC LIMIT 1
) result ON true
ORDER BY m.id, result.record_date DESC

To be honest I found the answer on the timescaledb docs. I thought this was a common problem in TSDB, it was !