How to index dynamic attributes in MongoDB I have following kind of data (simplified a bit from my real case) in MongoDB:

{
    "name":"some name",
    "attrs":[
        {"n":"subject","v":"Some subject"},
        {"n":"description","v":"Some great description"},
        {"n":"comments","v":"Comments are here!"},
        ]
}

The attrs array is a container for dynamic attributes, i.e. I don't beforehand know what kind of attributes are put there. n stands for name and v stands for value.

The MongoDB In Action book describes this as a solution for having dynamic attributes in the case where the attributes are completely upredictable. It also describes that you can index it like this:

db.mycollection.ensureIndex({"attrs.n":1, "attrs.v":1})

Queries can then be done like this:

db.mycollection.find({attrs: {$elemMatch: {n: "subject", v: "Some subject"}}})

When i test this, I get very poor performance. I tested with mycollection having 2 million documents and having no index seems to perform better.

So, the question goes, is there a way to index this kind of dynamic attribute setting so that the indexing gives good performance? In my case, it is not feasible to just have keys like "subject" and "description" and index them all...

I also asked this same question (in a bit expanded form) on the mongodb-user mailing list, where I got an answer. Read from there to get more details. The short answer is, that the strategy used in the question should work fine, but there's an issue that makes it very inefficient. Hopefully, the issue will be fixed soon.

For my case, I only need to query for exact matches for the tuple {n,v}, so I can create a multikey index:

db.mycollection.ensureIndex({"attrs":1})

and make they query like this:

db.mycollection.find({"attrs": {n: "subject", v: "Some subject"}})

which works great and uses the index very effectively.


How to recover performance_schema after restoring from partial innobackupex backup? I am taking a partial backup from a Master server, which needs to be restored on a to-be slave server. I only need one of the databases, and the mysql database.

I am following instructions on https://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/partial_backups_innobackupex.html

The command I'm using looks like this:

innobackupex --user=<the_user> --password --databases="<database_name> mysql" --slave-info /path/backup

The prepare command looks like this:

innobackupex --apply-log --export /path/backup

And then I

  Copy back the prepared backup to a “clean” datadir (in that case, make sure to include the mysql database).

MySQL starts fine, but without a performance_schema and with a lot of

  588 [ERROR] Native table 'performance_schema'.'' has the wrong structure

on its error log.

Any idea why this might happen, and how to get the performance_schema back?

The solution requires running mysql_upgrade on the server that has been restored. As per the documentation on https://dev.mysql.com/doc/refman/5.5/en/performance-schema-build-configuration.html

  If you install MySQL over a previous installation that was configured without the Performance Schema (or with an older version of the Performance Schema that may not have all the current tables), run mysql_upgrade after starting the server to ensure that the performance_schema database exists with all current tables. Then restart the server.


SSRS breadcrum just breaks into multiple lines This is a very annoying thing happening to the reports I am developing. The top breadcrumb sometimes works fine while at times breaks into multiple lines.  I have only IE 11 in my environment, Is it the browser issue? If not then please help me fix this.

Below is the screenshot.

AFter days of scratching my head and google finally fixed with a vey simple setting. This was resolved by adding the url to the Compatibility View Settings in the IE browser settings.


Informix v12 substitute for query using multiple with statements I am trying to convert some PostgreSQL into Informix and factor out the WITH statements in the query below.

I have been unable to locate a solution to factoring out multiple with statements.

The user account to do this does not have 'create table' permissions.

WITH bear AS
(SELECT
lv.vct_id
lsetup.wrk_id,
lobservation.id AS obs_id,
ladoption.factor_1 AS bear_corr,
lsurvey.dataset_series
FROM crs_vector cv
INNER JOIN crs_observation co
ON cv.id = co.vct_id
INNER JOIN crs_setup cs
ON (co.stp_id_local = cs.id AND lobservation.obt_sub_type = 'BEAR')
INNER JOIN crs_work cw
ON (cs.wrk_id = cw.id)
LEFT JOIN crs_adoption ca
ON (co.id = ca.obn_id_new)
LEFT JOIN crs_survey cs ON
ca.sur_wrk_id_orig = cs.wrk_id
WHERE cw.id = 1672933 AND co.obt_sub_type = 'BEAR'),

dist AS
(SELECT
lv.vct_id
lsetup.wrk_id,
lobservation.id AS obs_id,
ladoption.factor_1 AS bear_corr,
lsurvey.dataset_series
FROM crs_vector cv
INNER JOIN crs_observation co
ON cv.id = co.vct_id
INNER JOIN crs_setup cs
ON (co.stp_id_local = cs.id AND lobservation.obt_sub_type = 'DIST')
INNER JOIN crs_work cw
ON (cs.wrk_id = cw.id)
LEFT JOIN crs_adoption ca
ON (co.id = ca.obn_id_new)
LEFT JOIN crs_survey cs ON
ca.sur_wrk_id_orig = cs.wrk_id
WHERE cw.id = 1672933 AND co.obt_sub_type = 'DIST'),

SELECT
bear.vct_id
bear.wrk_id,
bear.obs_id,
bear.factor_1 AS bear_corr,
bear.dataset_series
dist.wrk_id,
dist.obs_id,
dist.factor_1 AS bear_corr,
dist.dataset_series
FROM   bear
FULL OUTER JOIN   dist
ON (bear.vct_id =  dist.vct_id AND bear.wrk_id = dist.wrk_id);

I might be wrong but give a shot

SELECT
bear.wrk_id,
bear.vct_id,
bear.bearing,
bear.bearing_datum,
dist.dist_equipment_type,
bear.bear_equipment_type,
dist.distance,
bear.surveyed_class AS bear_surveyed_class,
bear.geodetic_class AS bear_geodetic_class,
bear.adopt_bear_wrk_id,
bear.bear_adopt_plan,
dist.surveyed_class AS dist_surveyed_class,
dist.geodetic_class AS dist_geodetic_class,
dist.adopt_dist_wrk_id,
dist.adopt_dist_wrk_id,
bear.cos_id AS cos_id,
bear.ref_datetime,
bear.stp_id_local AS bear_stp_id_local,
bear.stp_id_remote AS bear_stp_id_remote,
dist.stp_id_local AS dist_stp_id_local,
dist.stp_id_remote AS dist_stp_id_remote
(
SELECT
lsetup.wrk_id,
lsetup.equipment_type AS bear_equipment_type,
lobservation.id AS obs_id,
lobservation.obt_sub_type,
lobservation.cos_id,
lobservation.vct_id,
lobservation.ref_datetime,
lobservation.surveyed_class,
lobservation.geodetic_class,
lobservation.value_1 AS bearing,
lobservation.value_2 AS bear1,
lobservation.value_3 AS bear2,
lobservation.value_1 as dd,
lobservation.stp_id_local,
lobservation.stp_id_remote,
ladoption.factor_1 AS bear_corr,
ladoption.sur_wrk_id_orig AS adopt_bear_wrk_id,
lcsystem.name AS bearing_datum,
concat(TRIM( TRAILING ' ' FROM CAST (lsurvey.dataset_series AS TEXT)) , ' ' , lsurvey.dataset_id) AS bear_adopt_plan
FROM crs_observation lobservation
INNER JOIN crs_setup lsetup
ON (lobservation.stp_id_local = lsetup.id AND lobservation.obt_sub_type = 'BEAR' AND lobservation.surveyed_class <> 'PSED' )
INNER JOIN crs_work lwork
ON (lsetup.wrk_id = lwork.id AND lwork.status <> 'REQU')
LEFT JOIN crs_adoption ladoption
ON (lobservation.id = ladoption.obn_id_new)
LEFT JOIN crs_coordinate_sys lcsystem
ON (lobservation.cos_id = lcsystem.id)
 LEFT JOIN crs_survey lsurvey ON
ladoption.sur_wrk_id_orig = lsurvey.wrk_id
WHERE lwork.id = 1672933 AND lobservation.obt_sub_type = 'BEAR' AND lobservation.surveyed_class <> 'PSED' bear
FULL OUTER JOIN
  (
    SELECT
lwork.id AS wrk_id,
lobservation.id AS obs_id,
lobservation.obt_sub_type,
lobservation.cos_id,
lobservation.vct_id,
lobservation.ref_datetime,
lobservation.surveyed_class,
lobservation.geodetic_class,
lobservation.value_1 AS distance,
lobservation.value_2 AS dist2,
lobservation.value_3 AS dist3,
ladoption.factor_1 AS dist_corr,
ladoption.sur_wrk_id_orig AS adopt_dist_wrk_id,
lobservation.stp_id_local,
lobservation.stp_id_remote,
lsetup.equipment_type AS dist_equipment_type,
concat(TRIM( TRAILING ' ' FROM CAST (lsurvey.dataset_series AS TEXT)) , ' ' , lsurvey.dataset_id) AS dist_adopt_plan
FROM crs_observation lobservation
INNER JOIN crs_setup lsetup
ON (lobservation.stp_id_local = lsetup.id)
INNER JOIN crs_work lwork
ON (lsetup.wrk_id = lwork.id AND lwork.status <> 'REQU')
LEFT JOIN crs_adoption ladoption
ON (lobservation.id = ladoption.obn_id_new)
LEFT JOIN crs_survey lsurvey ON
ladoption.sur_wrk_id_orig = lsurvey.wrk_id
WHERE  lwork.id = 1672933 AND lobservation.obt_sub_type = 'SLDI'  AND lobservation.surveyed_class <> 'PSED') DIST
ON (bear.vct_id =  dist.vct_id AND bear.wrk_id = dist.wrk_id AND bear.cos_id = dist.cos_id)
)


Create multiple field indexes with different types on postgres I have the following query on an 18M records table (which grows daily by 5k):

SELECT * from marketing
where start_time < '2017-02-19 08:00:00'
AND NOT name LIKE '%MyWorld%'
AND product = 'Product2'

I am using postgres 9.3.1

This is a report table that is being updated in an offline process, but I need to return the results from the report online, quickly.

I thought to use gin index, however, since my query filters 3 columns, I believe that I need a composite index

CREATE INDEX marketing_name_start_date_product_gin_trgm_idx ON tbl USING gist
(name, start_date, product gin_trgm_ops);

However this seems like a waste for me to create a gin index for columns which don't use like in queries.

What do you suggest? What is the best approach?

This is going to be impossible for us to assist in with the information provided. However, rest assured 18M is not much. Baseline questions include,

Ignoring the LIKE condition, what is the selectivity that can be provided on start_time and product?
Do you ever select on start_time, and name, without product?
When you have a btree on (start_time, product), and a gin/gist tgrm index on name, how much times does the query take?
When you create the index described, how much time does the query take?

Take a look at the plans required by both. I would do these tests on a snap shot of the table so it's fair (or two snapshots with only the indexes mentioned)

CREATE TEMP TABLE foo
AS
  SELECT * FROM marketing


Adding SPARSE made table much bigger I have a generic log table, about 5m rows.
There's a "strongly typed" field that stores event type, and a bunch of "losely typed" columns that contain data relevant to the event. That is, meaning of those "losely typed" columns depends on the type of the event.

These columns are defined as:

USER_CHAR1 nvarchar(150) null,
USER_CHAR2 nvarchar(150) null,
USER_CHAR3 nvarchar(150) null,
USER_CHAR4 nvarchar(150) null,
USER_CHAR5 nvarchar(150) null,

USER_INTEGER1 int null,
USER_INTEGER2 int null,
USER_INTEGER3 int null,
USER_INTEGER4 int null,
USER_INTEGER5 int null,

USER_FLAG1 bit null,
USER_FLAG2 bit null,
USER_FLAG3 bit null,
USER_FLAG4 bit null,
USER_FLAG5 bit null,

USER_FLOAT1 float null,
USER_FLOAT2 float null,
USER_FLOAT3 float null,
USER_FLOAT4 float null,
USER_FLOAT5 float null

Columns 1 and 2 in each type are heavily used, but starting from number 3, very few event types would provide this much of info. I therefore desided to mark columns 3-5 in each type as SPARSE.

I did some analysis first, and saw that, indeed, at least 80% of data in each of those columns is null, and in some 100% of data is null. According to the 40% savings threshold table, SPARSE would be a huge win on them.

So I went and applied SPARSE to columns 3-5 in each group.
Now my table takes about 1.8Gb in data space as reported by sp_spaceused, whereas before sparsing it was 1Gb.

I tried dbcc cleantable, but it had no effect.
Then dbcc shrinkdatabase, no effect either.

Puzzled, I removed SPARSE and repeated the dbccs. The size of the table remained at 1.8Gb.

What gives?

You need to rebuild the clustered index after making the columns sparse. The dropped columns still exist in the data page until you do this as can be seen with a query against sys.system_internals_partition_columns or using DBCC PAGE

SET NOCOUNT ON;
CREATE TABLE Thing
(
ThingId int IDENTITY CONSTRAINT PK PRIMARY KEY,
USER_CHAR1 nvarchar(150) null,
USER_CHAR2 nvarchar(150) null,
USER_CHAR3 nvarchar(150) null,
USER_CHAR4 nvarchar(150) null,
USER_CHAR5 nvarchar(150) null
)
INSERT INTO Thing
SELECT REPLICATE('A',150),
       CASE WHEN number % 5 = 1 THEN REPLICATE('A',150) END,
       CASE WHEN number % 5 = 2 THEN REPLICATE('A',150) END,
       CASE WHEN number % 5 = 3 THEN REPLICATE('A',150) END,
       CASE WHEN number % 5 = 4 THEN REPLICATE('A',150) END
FROM master..spt_values

EXEC sp_spaceused 'Thing'

ALTER TABLE dbo.Thing ALTER COLUMN USER_CHAR2 ADD SPARSE
ALTER TABLE dbo.Thing ALTER COLUMN USER_CHAR3 ADD SPARSE
ALTER TABLE dbo.Thing ALTER COLUMN USER_CHAR4 ADD SPARSE
ALTER TABLE dbo.Thing ALTER COLUMN USER_CHAR5 ADD SPARSE

DECLARE @DynSQL NVARCHAR(MAX);

SELECT @DynSQL =  'DBCC TRACEON (3604);
                   DBCC PAGE(0, ' + LEFT(file_id,10) + ', ' + LEFT(page_id,10) + ', 3);
                   DBCC TRACEOFF(3604); '
FROM Thing
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)
WHERE ThingId=76

EXEC(@DynSQL)

SELECT pc.*
FROM sys.system_internals_partition_columns pc
JOIN sys.partitions p on p.partition_id=pc.partition_id
WHERE p.object_id = object_id('Thing')
AND pc.is_dropped=1

 EXEC sp_spaceused 'Thing'

ALTER INDEX PK ON Thing REBUILD;

SELECT @DynSQL =  'DBCC TRACEON (3604);
                   DBCC PAGE(0, ' + LEFT(file_id,10) + ', ' + LEFT(page_id,10) + ', 3);
                   DBCC TRACEOFF(3604); '
FROM Thing
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)
WHERE ThingId=76

EXEC(@DynSQL)

SELECT pc.*
FROM sys.system_internals_partition_columns pc
JOIN sys.partitions p on p.partition_id=pc.partition_id
WHERE p.object_id = object_id('Thing')
AND pc.is_dropped=1

EXEC sp_spaceused 'Thing'

DROP TABLE Thing


Where to place a foreign key column? This isn't exactly related to modeling the database but rather about "standardizing" column placement.

Where should I generally put my FK column? As the last column of the table? At the start just after the primary identifier? After a relevant set of columns in the middle of the table?

Are there any industry standards I can hold on to, to make my life easier?

If it means anything, I'm using mysql, but this is a general db design question.

Although inherent column order does not matter, but since you're looking for 'industry standards', you can search publicly available databases, like Microsoft's AdventureWorks, to see where Foreign Key columns are placed.
It's usually at the beginning.

This MSSQL query lists all FKs in a database :

SELECT
    FK_Table = FK.TABLE_NAME,
    FK_Column = CU.COLUMN_NAME,
    PK_Table = PK.TABLE_NAME,
    PK_Column = PT.COLUMN_NAME,
    Constraint_Name = C.CONSTRAINT_NAME
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
    ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
            SELECT
                i1.TABLE_NAME,
                i2.COLUMN_NAME
            FROM
                INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
                ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
            WHERE
                i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
           ) PT
    ON PT.TABLE_NAME = PK.TABLE_NAME


Oracle 11gR2 can I delete datafiles to reduce tablespace? I have the following doubt, maybe a little silly, if I have a tablespace for example TBS_MYDATA of 155 GB, this tablespace is composed of 5 datafiles of 31GB each, in turn, each of these datafiles have between 3% and 4% usage ... my question is: can I delete some of these datafiles to reduce the size of the tablespace? The information that these datafiles contain would be automatically relocated in the remaining ones? Thank you very much in advance.

You can use DROP DATAFILE from ALTER TABLESPACE to drop a datafile which is empty (has no extents allocated from it).

Example:

ALTER TABLESPACE <tablespace_name> DROP DATAFILE '/path/datafile_name';

However if the datafile is not empty, you need to run something like a tablespace map to see which object extents are allocated from it.  You can use this script, but there are other searchable scripts which give pretty outputs.

SELECT
  'free space' owner, null OBJECT, null segment_type, file_id, block_id, blocks
FROM dba_free_space
WHERE tablespace_name = UPPER ('&Tblspc')
UNION
SELECT
  owner, segment_name, segment_type, file_id, block_id, blocks
FROM dba_extents
WHERE tablespace_name = UPPER ('&Tblspc')
ORDER BY 4, 5;

Once you find the objects in the datafile you want to drop, you can reorganize them into the same tablespace (which might put them into free space in other datafiles) or other tablespaces.

Here are some example commands for moving tables and indexes.

ALTER TABLE <table_name> MOVE TABLESPACE <tablespace_name>;
ALTER INDEX <index_name> REBUILD TABLESPACE <tablespace_name>;

With those examples don't forget to add your storage and statistics clauses as needed.

You could also try resizing datafiles but this will only work up to an allocated extent.  This means that if there is an extent half way through the datafile, you would only be able to resize it to 50%.  You can use the tablespace map to check these also, or do it by trial and error.

ALTER DATABASE DATAFILE '/path/datafile_name' RESIZE <new_size>;


LogWriter: Operating system error 1117 I am getting these errors on my dedicated server because of which database goes into recovery mode.

I have seen the following error in SQL server error log file:

2018-01-20 05:32:19.83 spid7s      LogWriter: Operating system error 1117(The request could not be performed because of an I/O device error.) encountered.
2018-01-20 05:32:19.83 spid7s      Write error during log flush.
2018-01-20 05:32:19.84 spid284     The client was unable to reuse a session with SPID 284, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
2018-01-20 05:32:19.84 spid335     Database newtrack was shutdown due to error 9001 in routine 'XdesRMFull::Commit'. Restart for non-snapshot databases will be attempted after all connections to the database are aborted.
2018-01-20 05:35:03.35 spid22s     Recovery completed for database aa(database ID 7) in 151 second(s) (analysis 18856 ms, redo 115386 ms, undo 2023 ms.) This is an informational message only. No user action is required.

At the same time following errors are generated in event viewer:

Application Event
EventID:17053, MSSQLSERVER
LogWriter: Operating system error 1117
    (The request could not be performed because of an I/O device error.) encountered.
Error 1/20/2018 5:32:19 AM MSSQLSERVER 18056 Server
Error 1/20/2018 5:32:19 AM MSSQLSERVER 9001 Server
Error 1/20/2018 5:32:19 AM MSSQLSERVER 9001 Server
Error 1/20/2018 5:32:19 AM MSSQLSERVER 17053 Server

System Event:
The IO operation at logical block address 4069 for Disk 1
    (PDO name: \Device\00000045) was retried.

One possible reason is that you are running in a VMWare environment [and configured Paravirtual SCSI) adapters. See this knowledge base article

  SQL Server reports "Operating system error 1117 (I/O Device Error)" on
  VMware ESX environments that are configured to use PVSCSI adapters

Unfortunately the link to the VMWare knowledgebase in that article is dead but there is an official VMWare KB article here. If this describes your situation you might want to open a support call with VMWare.


How to Store text of 15 to 20 KB file data in blob or as text? How to Store text of 15 to 20 KB file data in blob or as varchar in sql Server?

If it's the textual content of the files you are concerned with and you need to search that content, store it as [n]varvchar(max) and consider using a full-text index on the column.

If the files are XML, consider storing the content using the xml data type.

If the files are some format other than plain text and you want to preserve that format, use BLOB storage. For SQL Server Server 2012 or newer, you can use FileTables.


Join Multiple Derived Tables, Correctly in MySQL? I'm trying to achieve a full outer join in MySQL

EDIT : As per comments:

Data is

Need data with for question_id in(44,45,46,47,48,49)

Desired Output is :
Query generated using golang:

select @s:=@s+1,q1,q2,q3,q4,q5,q6

from (SELECT * FROM

(SELECT * FROM

(select ifnull(answer,' ') as 'q1',user as 'u1' from survey_answer where question_id=44) t1
   left join
 (select ifnull(answer,' ') as 'q2',user as 'u2' from survey_answer where question_id=45) t2 on t1.u1 = t2.u2

 left join
 (select ifnull(answer,' ') as 'q3',user as 'u3' from survey_answer where question_id=46) t3
on t2.u2 = t3.u3

 left join
(select ifnull(answer,' ') as 'q4',user as 'u4' from survey_answer where question_id=47) t4
 on t3.u3 = t4.u4

left join
 (select ifnull(answer,' ') as 'q5',user as 'u5' from survey_answer where question_id=48) t5
 on t4.u4 = t5.u5

  left join
 (select ifnull(answer,' ') as 'q6',user as 'u6' from survey_answer where question_id=49) t6
 on t5.u5 = t6.u6

) X

UNION

 (SELECT * FROM

 (select ifnull(answer,' ') as 'q1',user as 'u1' from survey_answer where question_id=44) t1

  right join
 (select ifnull(answer,' ') as 'q2',user as 'u2' from survey_answer where question_id=45) t2
 on t1.u1 = t2.u2

 right join
(select ifnull(answer,' ') as 'q3',user as 'u3' from survey_answer where question_id=46) t3
on t2.u2 = t3.u3

 right join (select ifnull(answer,' ') as 'q4',user as 'u4' from survey_answer where question_id=47) t4
 on t3.u3 = t4.u4

 right join (select ifnull(answer,' ') as 'q5',user as 'u5' from survey_answer where question_id=48) t5
 on t4.u4 = t5.u5

right join (select ifnull(answer,' ') as 'q6',user as 'u6' from survey_answer where question_id=49) t6
on t5.u5 = t6.u6

) ) ORDERED_ALIAS , (SELECT @s:= -1) AS s   ORDER BY u1=0 DESC

Regarding your FULL OUTER JOIN question (not working in MySQL):  you can probably achieve a result similar to that of a FULL OUTER join by coding a UNION of a LEFT and RIGHT join.  However, looking at your raw data and your "Desired Output", it may be better to use pivoting.

Consider the following - just for generating a test table that resembles your "data" table (using MySQL version 5.1):

create table user( userid int );
create table question( qid int, answer varchar(128));

insert into user values (0),(4),(5),(6),(7),(8),(9),(10);
insert into question (qid) values (44),(45),(46),(47),(48),(49);

create table qa
as select * from user, question;

-- Add some dummy answers to the table, leave some gaps.
update qa
set answer = concat(concat('answer', userid), qid)
where mod( (userid*qid), 2 ) = 0 ;

We get a table that looks like this: (middle section not displayed)

mysql> select * from qa;
+--------+------+------------+
| userid | qid  | answer     |
+--------+------+------------+
|      0 |   44 | answer044  |
|      0 |   45 | answer045  |
|      0 |   46 | answer046  |
|      0 |   47 | answer047  |
|      0 |   48 | answer048  |
|      0 |   49 | answer049  |
|      4 |   44 | answer444  |
|      4 |   45 | answer445  |
|      4 |   46 | answer446  |
 ...
|      8 |   49 | answer849  |
|      9 |   44 | answer944  |
|      9 |   45 | NULL       |
|      9 |   46 | answer946  |
|      9 |   47 | NULL       |
|      9 |   48 | answer948  |
|      9 |   49 | NULL       |
|     10 |   44 | answer1044 |
|     10 |   45 | answer1045 |
|     10 |   46 | answer1046 |
|     10 |   47 | answer1047 |
|     10 |   48 | answer1048 |
|     10 |   49 | answer1049 |
+--------+------+------------+
48 rows in set (0.00 sec)

If we now "extend" and "pivot" (see http://stratosprovatopoulos.com/web-development/mysql/pivot-a-table-in-mysql/ ) and use MySQL's group_concat() function ( see https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html ), we get a result that may be useful for you.

create view qa_extended as
select
  userid
, case when qid = 44 then answer end as q44
, case when qid = 45 then answer end as q45
, case when qid = 46 then answer end as q46
, case when qid = 47 then answer end as q47
, case when qid = 48 then answer end as q48
, case when qid = 49 then answer end as q49
from qa
;

create view qa_pivot as
select
  userid
, group_concat(q44) as q44
, group_concat(q45) as q45
, group_concat(q46) as q46
, group_concat(q47) as q47
, group_concat(q48) as q48
, group_concat(q49) as q49
from qa_extended
group by userid
;

Output of   select * from qa_pivot;

mysql> select * from qa_pivot;
+--------+------------+------------+------------+------------+------------+------------+
| userid | q44        | q45        | q46        | q47        | q48        | q49        |
+--------+------------+------------+------------+------------+------------+------------+
|      0 | answer044  | answer045  | answer046  | answer047  | answer048  | answer049  |
|      4 | answer444  | answer445  | answer446  | answer447  | answer448  | answer449  |
|      5 | answer544  | NULL       | answer546  | NULL       | answer548  | NULL       |
|      6 | answer644  | answer645  | answer646  | answer647  | answer648  | answer649  |
|      7 | answer744  | NULL       | answer746  | NULL       | answer748  | NULL       |
|      8 | answer844  | answer845  | answer846  | answer847  | answer848  | answer849  |
|      9 | answer944  | NULL       | answer946  | NULL       | answer948  | NULL       |
|     10 | answer1044 | answer1045 | answer1046 | answer1047 | answer1048 | answer1049 |
+--------+------------+------------+------------+------------+------------+------------+
8 rows in set (0.00 sec)


Changing the target schema on export or import? I normally do the following for SQL dumps:
pg_dump -U username -cOx database > dump.sql

And then restore it into a new database with:
psql -U username new_database < dump.sql

Currently the database tables are not in the public schema, but a schema named for a user. No reason for it. I think it happened when there was a migration from MySQL to PostgreSQL a few years back using a utility whose name escapes me.
I'd like to move everything into public. I know I can do this by editing the dump.sql, which is a slow process given the size of the file (which is pretty small in database terms).
Is there a way to do this when exporting or importing a SQL dump?
This is PostgreSQL 13, btw.

There is no direct way to do this.
You could for example restore the dump into a temporary database, drop the public schema there, rename your schema to public and then dump again.


Is it possible to restrict SQL Server DB_Owner when connecting using SQL Server Mgt Studio? Here is the back story. A developer created an EXE program. The exe program has the ability to access a SQL Servre database. In order to do that we create an Active Directory group. Let's call this group AppAdmin. The EXE program use windows integrated security to connect to the SQL Server database.

In order to use this EXE program, the user must belong to the APPAdmin group. The AppAdmin group then now set the membership as DB_Owner for the database. This setup has been like this for a few years. and it is running fine. The user is only limited to what the EXE program can do.

Now here is the complication. One user one day decided to install SQL Server Management Studio Express. Let's call this guy JOE. and JOE is a member of APPAdmin group.

Now I am assigned to find away to protect the database from JOE breaking it using SQL Server Management Studio.

I am wondering if there is a way to restrict JOE when he is connecting using SQL Server Management Studio?

What are you suggestion ??

Changing this EXE program seems to be out of the question because it is big and I might break it if I start changing how the EXE program connect to the SQL Server database

Thank you

Changing this EXE program seems to be out of the question

That makes using application roles not an option. However, here's the link anyway since it's good to know: Application roles

Your second option would be using logon triggers. Link for more info
What you do is, once somebody logs in, you check which application name they are using and if it's not your EXE, you deny access by aborting the logon..

However, this is not bullet proof since the "application" string can be spoofed. It might however be "good enough" for stopping the curious people.

Security should be designed in such a way that people that are able to log in to a database can only do what they are allowed to do. So your best option (but the most complicated to implement at this stage) Would be to revoke the DBO permission an give more granular permissions. If you need business logic to be forced your only option is to implement that in stored procedures and only give execute permissions to those stored procedures.. However, that might again mean chaning the EXE.


What happens when a CTE query is interrupted I have the following query.

WITH d_rows AS (DELETE FROM t_a RETURNING * )  INSERT INTO t_b SELECT * FROM d_rows;

What will happen if i cancel this query (Ctrl+c in psql)? Will the delete run? Does it run already? Does it run in batches? I see the t_b is growing while the query is running. This is important to know since this is about a lot of data.

A CTE isn't any different to any other statement in this regard - it runs within a transaction. If no transaction is already open then one is opened for the statement and committed only at the successful completion of the whole statement.

Thus, if you interrupt the CTE, all effects will roll back and nothing will happen1. If you interrupt it just at the moment it is committing then it might commit and all take effect, but either all or none of the CTE will take effect. Individual CTE sub-terms cannot take effect by themselves.

So ... the delete might run, but if so, its effects will not be committed and will be rolled back when the query is cancelled. Exactly how it's executed does not matter. PostgreSQL could choose to batch it, execute it row-by-row, or execute the whole DELETE and feed all its results into the INSERT. It doesn't matter, because the effects will be the same.

1 Well, strictly a whole lot of table bloat could be created, but there are no effects logically visible to other transactions.


Is connection string's default database used if queries are run against other databases? Let's say I have an app that uses this connection string to connect to a SQL Server using "DB1" as the default database (a.k.a. initial catalog):

Server=myServerAddress; Database=DB1; User Id=myUsername; Password=myPassword;

But all queries executed through this connection target other databases, never DB1. (Example: SELECT * FROM DB2.dbo.Table1)

Can/will DB1 possibly be used for anything, even though it is not referenced in any queries? I'd like to hear about all possibilities no matter how obscure. (For example, could there be differences in options, cursors, table locking, query performance...?)

I would assume not, except that today I saw strange performance issues with DB1 that are making me second guess my assumption. It could be a red herring but regardless I'd like to make sure I fully understand the role of the default database / initial catalog.

Different performance of the same query typically indicates different execution plans.  The database context generally won't yield different plans (and performance) but there are some cases where it could.

The default collation of the current database context determines the collation of literals and parameters.  Below is an example that shows a slightly different execution plan when the query differs only by database context:

CREATE DATABASE TestDatabase
    COLLATE Latin1_General_CI_AS;
GO
CREATE DATABASE TestDatabase_SC
    COLLATE Latin1_General_100_CI_AS_SC;
GO

USE TestDatabase;
GO

CREATE TABLE dbo.TestTable(
    Col1 varchar(10) CONSTRAINT PK_TestTable PRIMARY KEY
    );
INSERT INTO dbo.TestTable VALUES('A');
GO

--query plan in TestDatabase context shows seek predicate: Seek Keys[1]: Prefix: [TestDatabase].[dbo].[TestTable].Col1 = Scalar Operator([@1])
SELECT Col1 FROM TestDatabase.dbo.TestTable WHERE col1 = 'A';
GO

USE TestDatabase_SC;
GO

--query plan in TestDatabase_SC context shows seek predicate: Seek Keys[1]: Prefix: [TestDatabase].[dbo].[TestTable].Col1 = Scalar Operator(CONVERT_IMPLICIT(varchar(8000),[@1],0))
SELECT Col1 FROM TestDatabase.dbo.TestTable WHERE col1 = 'A';
GO

I wouldn't expect a significant performance difference here because SQL Server coerces a sargable expression even though the literal and column data types differ.

The current database context also determines SET options used when not explicitly set by the client API or SET statement.  This may result in different execution plans, especially for legacy APIs.


Calculating tsrange based on parent/child created dates Given the table:

CREATE TABLE node (
    id serial PRIMARY KEY,
    created timestamp without time zone NOT null,
    path ltree NOT null UNIQUE
);

And the records:

INSERT INTO node (id, path, created)
VALUES
  (1, '1', '2010-01-01 12:00'),
  (2, '1.2', '2010-01-01 13:00'),
  (3, '1.2.3', '2010-01-01 14:00');

How do I get the valid range for each node based on the node's created date and its' parent's created date?

For instance, the ranges for the given path 1.2.3 would be:

id | path  | valid
------------------------------------------------------------------
 1 |     1 | tsrange('2010-01-01 12:00', '2010-01-01 13:00', '[)')
 2 |   1.2 | tsrange('2010-01-01 13:00', '2010-01-01 14:00', '[)')
 3 | 1.2.3 | tsrange('2010-01-01 14:00', 'infinity', '[)')


Connect DBeaver to PostgreSQL 13 I've been using PostgreSQL 10 and 12 on Windows successfully with DBeaver in the past. Now I've set up a new development machine and installed the current PostgreSQL 13.0.1 and the current DBeaver 7.2.4 on Windows 10.2004. When connecting to the local Postgres server, I got the message that a connection wasn't possible in the Postgres 10 style and I should check the hb_pga.conf entries.
I went to my hb_pga.conf file and changed all of the shiny new scram-sha-256 entries back to the old md5 and restarted the database server.
But DBeaver won't connect. Is this a problem with the latest DBeaver or Postgres or what? Has anybody managed to get these two to talk to each other with the latest versions?
Unfortunately DBeaver seems to be based on Java and is limited to JDBC which doesn't seem to be maintained anymore so it can't use the current Postgres authentication scheme. I've looked for multi-DBMS alternatives to DBeaver but couldn't find any that looks convincing. Same old problem, good GUI database clients are very expensive, the free ones are often of poor quality. I hoped DBeaver was a good solution.

Changing authentication to md5 in pg_hba.conf won't help you if the password hash is already in the scram format.  You need to set password_encryption TO md5 and then change your password (possibly back to the same thing it already is) to get the password stored in the md5 format.

JDBC which doesn't seem to be maintained anymore

JDBC is maintained.  Perhaps you can't figure out how to upgrade it, but that is a different problem than it not being maintained.
SCRAM does work with JDBC starting in version 42.2.1 (current version is 42.2.18)


Deleting large amount of data in ORACLE I have to delete larges amount of data by user request, ie, when a user press a button some PL/SQL will be executed to delete a certain set of data. Usually we can say that this set has approximately 10 million of rows. The table will have about 300 million of rows monthly and in this moment I don't know how the housekeeping will work on historic data.
What do you suggest in order to make this the most efficient possible and also to be able to not blow the transaction log files and put the database down?

The delete is something so simple like:

delete from <table> where <field> = <value>

Where this field is not a key in the table.

****** EDIT ******

This is a operation that a user triggers. And has to run after the moment that the user press the button.
The table is partitioned by date (interval of months).
I made an edit in the original description. There will be an estimated 300M of rows monthly, 10M per day. So you can look at this as 30 loadings of 10M of rows per day that will be in the same partition (partition for that month), and I want to delete one (or more) specific loadings for that month (partition).
We are just beginning so what I can say is just estimated values.

Thanks in advance.

If you want to be able to delete this data with the maximum efficiency, and you do not need to be able to roll the transaction back, then change the partitioning scheme to be based on days.

If you want to be able to optionally recover that data, then you could still partition it, but could remove it from the table using a partition exchange with a newly created table. You can then drop that table at a later date.

Re-partitioning aside, deleting 10 million rows is always going to require extensive undo logging, because a rollback operation has to recreate those rows. The redo would not be so extensive.

But it is never going to be a lightweight operation.

p.s. There's no such thing as blowing a transaction log, or putting a database down -- if you use informal, slang terminology to describe a problem then you're not describing it accurately, and you're inviting imprecise responses.


Continuous replication between Oracle Database and Azure SQL Database Background info: Our core ERP has its data on Oracle. Our own applications are Microsoft SQL Server based.

Almost every application reads data from this Oracle database and usually there is some sort of synchronisation where we copy the entire database.

When looking around there are numerous solutions. Most of these seem deprecated. (e.g. Oracle GoldenGate).

Our Oracle database is on-premise and we intend to use Azure SQL Database for our future applications.

What is currently the best way to set-up this synchronisation?

Master: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
Slave: Azure SQL Database

I must have assumed that Oracle GoldenGate was deprecated since I've seen lots of old posts but hardly any recent ones. But this is another 'tool' to keep our data in sync. Are there other/better solutions?

Perhaps a linked server is an option in my case? Is that possible with Oracle on-prem and Azure SQL Database?

Is GoldenGate the best option if you want replication?

Oracle Golden Gate has always supported replication between Oracle and SQL Server. For a project I recently confirmed that Golden Gate for Oracle 10 is still available if you push a bit.  If you are using an Oracle version that is out of support then you need to update for a number of reasons.

Synchronization with Azure is detailed here:

Configuring Oracle GoldenGate for Azure


SSAS -> MDX -> Create a price comparision against resellers I want to write an MDX query that will do a price comparison for a product against each re-seller and return a count which a 'greater', 'lesser' or 'equal' for a given month.

Measure:
fact_reseller_price

Dimension:
dim_date
dim_resellers
dim_products

Data in my fact table:

fact_reseller_price

product      reseller price date_purchase
SSD          AMZ      10.00    01/5/2012
SSD          BB       15.00    01/5/2012
SSD          COSTCO   15.00    01/5/2012
SSD          COSTCO   15.00    01/5/2012
SSD          FRYS     20.00    01/5/2012
SSD          WALMART  10.00    01/5/2012
SSD          EN       5.00     01/5/2012
SSD          EN       8.00     01/5/2012
SSD          EN       4.00     01/5/2012

My resultant table from the MDX query (Price Comparison)

product reseller comparision_reseller count type    date_purchase
SSD     AMZ      BB                   1     GREATER 01/5/2012
SSD     AMZ      COSTCO               2     GREATER 01/5/2012
SSD     AMZ      FRYS                 1     GREATER 01/5/2012
SSD     AMZ      WALMART              1     EQUAL   01/5/2012
SSD     AMZ      EN                   3     LESSER  01/5/2012
SSD     BB       AMZ                  1     LESSER  01/5/2012
SSD     BB       COSTCO               2     EQUAL   01/5/2012
SSD     BB       FRYS                 1     GREATER 01/5/2012
SSD     BB       WALMART              1     LESSER  01/5/2012
SSD     BB        EN                  3     LESSER  01/5/2012

If you have the following database schema with Date, Product and Reseller dimensions and a price fact table:

create table DimProduct (
       ProductID    int not null
      ,Product      varchar (50)
)
go

Create table DimDate (
       DateID    datetime not null
      ,MonthID   int not null
)
go

create table DimReseller (
       ResellerID   int not null
      ,Reseller     varchar (50)
)
go

create table FactPrices (
       ProductID    int not null
      ,ResellerID   int not null
      ,DateID       datetime not null
      ,Price        money
)
go

insert DimProduct (ProductID, Product) values (1, 'Widget')
insert DimProduct (ProductID, Product) values (2, 'Dohickey')
insert DimProduct (ProductID, Product) values (3, 'Whoflungdung')
go

insert DimDate (DateID, MonthID) values ('2012-01-01', 201201)
insert DimDate (DateID, MonthID) values ('2012-02-01', 201202)
insert DimDate (DateID, MonthID) values ('2012-03-01', 201203)
go

insert DimReseller (ResellerID, Reseller) values (1, 'Braise and co')
insert DimReseller (ResellerID, Reseller) values (2, 'Cocost')
insert DimReseller (ResellerID, Reseller) values (3, 'Walls Mart')
go

insert FactPrices (ResellerID, ProductID, DateID, Price) values (1, 1, '2012-01-01', 10)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (1, 1, '2012-02-01', 9)

insert FactPrices (ResellerID, ProductID, DateID, Price) values (1, 2, '2012-01-01', 10)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (1, 2, '2012-02-01', 10)

insert FactPrices (ResellerID, ProductID, DateID, Price) values (1, 3, '2012-01-01', 10)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (1, 3, '2012-02-01', 11)

insert FactPrices (ResellerID, ProductID, DateID, Price) values (2, 1, '2012-01-01', 10)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (2, 1, '2012-02-01', 10.5)

insert FactPrices (ResellerID, ProductID, DateID, Price) values (2, 2, '2012-01-01', 10)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (2, 2, '2012-02-01', 9.90)

insert FactPrices (ResellerID, ProductID, DateID, Price) values (2, 3, '2012-01-01', 10)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (2, 3, '2012-02-01', 11)

insert FactPrices (ResellerID, ProductID, DateID, Price) values (3, 1, '2012-01-01', 12)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (3, 1, '2012-02-01', 10.5)

insert FactPrices (ResellerID, ProductID, DateID, Price) values (3, 2, '2012-01-01', 10)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (3, 2, '2012-02-01', 11)

insert FactPrices (ResellerID, ProductID, DateID, Price) values (3, 3, '2012-01-01', 10)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (3, 3, '2012-02-01', 10.5)

go

Create a cube with Date, Product and Reseller dimensions and a SalesTrends fact table with a single metric 'Price'.  The MDX query below will produce counts of products for which the price is increasing, static or decreasing in a given period.

with
member [Measures].[Delta]
    as [Measures].[Price] -
       (parallelperiod ([Date].[Calendar].[Month]
                       ,1
                       ,[Date].[Calendar].currentmember)
       ,[Measures].[Price])

member [Measures].[Rising]
    as count (filter ([Reseller].[Reseller].Children
                      ,[Measures].[Delta] > 0))
      ,solve_order = 20

member [Measures].[Static]
    as count (filter ([Reseller].[Reseller].Children
                      ,[Measures].[Delta] = 0))
      ,solve_order = 20

member [Measures].[Falling]
    as count (filter ([Reseller].[Reseller].Children
                      ,[Measures].[Delta] < 0))
      ,solve_order = 20

select {[Measures].[Rising]
       ,[Measures].[Static]
       ,[Measures].[Falling]}
    on columns
      ,[Product].[Product].Children
    on rows
  from [SalesTrends]
 where ([Date].[Calendar].[Month].[201202])

The query calculates the delta from the previous period, and calculates three metrics for rising, static or falling based on counts of resellers where the delta is positive, zero or negative.  Note that the prices will aggregate in strange ways if you to calculate this at anything but the lowest grain of the underlying fact table.


What does Error: 17828, Severity: 20, State: 4 mean? I've found this guy in my SQL log:

"The prelogin packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library. [CLIENT: xx.x.xx.xxx]

Error: 17828, Severity: 20, State: 4."

but I can't find any information on it.

We are running SQL 2005 Enterprise (x86) - SP2 and the error is coming from the AD server. The users are getting errors on the application side - "Connection failure". It's random too and I can't reproduce it.

Does anyone know how to fix this (besides 'contact the vendor')?

It's a timeout error, basically, and it's fixed (i.e. error suppressed) in SS 2008.

Here's the relevant connect item.


xtrabackup prepare - after dump or before restore? i have doubts about xtrabackup --apply-log.
is there any difference when use it? should it be done right after dump or before restore (even few months later)? seems stupid, but maybe i missing something.

documentation says this:

  Prepare a backup in BACKUP-DIR by applying the transaction log file named xtrabackup_logfile located in the same directory. Also, create new transaction logs. The InnoDB configuration is read from the file backup-my.cnf created by innobackupex when the backup was made.

thanks for help :)

The chief reason to use --apply-log until the time of restore is to support incremental backups.

If you make incremental backups, you would use --apply-log --redo-only each incremental backup, and then just before you restore, you do a final prepare with --apply-log.

Otherwise if you don't make incremental backups, there's no reason not to do --apply-log right after you make the back up.  Then it's ready to restore on a moment's notice.


How to upgrade SQL Server 2008 to SQL Server 2014 servers with mirroring configured We have two SQL Server 2008 R2 instances with mirroring configured. We want to upgrade them to SQL server 2014.

How will this upgrade effect the mirroring between the servers, and also how should I proceed with this upgrade without causing an outage?

As per my research I can upgrade the mirrored server first to 2014 and then do a failover on 2008 server to make it mirror and the 2014 as principal and follow the same procedure to upgrade 2008 as well. Could some one please shed more light to it. What are some intrinsic points that I need to take care of?

Your research is correct you have to first apply service pack on mirror instance and then failover the mirroring so as to make this mirror instance as primary. Now you have to apply patch on instance which is nor mirror(which was previously primary). I have done it many times and I do not see much Gotchas.

  and also how should I proceed with this upgrade without causing an outage?

There will always be an outage you just cannot avoid it, when you failover SQL Server have to be stopped on current instance and failed over and started on other so you can see outage is just unavoidable.

For your reference

Minimizing downtime when patching instance which is mirrored
Upgrade Mirrored instance
Steps to Apply a Service Pack or Patch to Mirrored SQL Server Databases

Start reading the first 2 articles and then the third one has step by step method with screenshots.

The high level steps would be

Remove the witness server from mirroring(if you hjave configured witness)
Apply the service pack to the mirror server
Failover the mirrored databases over to make it principal
Apply the service pack to the former principle server (which is now a mirror)
Apply the service pack to the witness server
Add the witness server back to the mirroring configuration


Error: Incorrect usage of UNION and ORDER BY I have this query:

SELECT DISTINCT CONCAT (MONTH(`Date`),'-',YEAR(`Date`))
from wp_result_plugin
WHERE Bet=2
AND month(`Date`) = EXTRACT(month FROM (NOW()))
ORDER BY MONTH(`Date`) ASC

UNION

SELECT DISTINCT CONCAT (MONTH(`Date`),'-',YEAR(`Date`))
from wp_result_plugin
WHERE Bet=2
AND month(`Date`)
ORDER BY MONTH(`Date`) ASC

I am seeing this error:

Incorrect usage of UNION and ORDER BY

How to make this work?

Wrap the selects into parentheses

(SELECT DISTINCT CONCAT (MONTH(`Date`),'-',YEAR(`Date`)) from wp_result_plugin WHERE Bet=2 AND month(`Date`) = EXTRACT(month FROM (NOW())) ORDER BY MONTH(`Date`) ASC )
UNION
(SELECT DISTINCT CONCAT (MONTH(`Date`),'-',YEAR(`Date`)) from wp_result_plugin WHERE Bet=2 AND month(`Date`) ORDER BY MONTH(`Date`) ASC)


Comparing id s of same column and assign value of one to another I have table with

id name
1  aa
1  bb
2  ff
3  df

I want to check if value is repeated in column "id" then assign the "name" value to next matching ids row
Expected:

id name
1   aa
1   aa
2   ff
3   df

Can any one suggest how this can be achieved?

WITH cte AS (SELECT *,
                    MIN(name) OVER (PARTITION BY id
                                    ORDER BY name ASC) rn
             FROM test)
UPDATE cte
SET name=rn

fiddle

This will work on the basis of the "lower" letters (IE 'A') will overwrite "higher" letters (IE 'B'), as you have not indicated any preference for how to handle this ordering.


Transactional replication subscriber vs AG secondary replica for reporting I am trying to recommend my client a SQL server upgrade (from 2005-> 2014) which will allow the users to create record snapshot (via SSRS) from a read-only data source.  Data latency should be kept to a minimum.

My understanding for transactional replication is a subscriber database schema can be a subset of the publisher, and can have different indexes; but I don't think an AG secondary replica can have a different schema from primary, even though a secondary replica can have different indexes?

I am also thinking about having the SSRS Report DB/ReportTempDB and operate independently rather than get replicated/synchronized.  I think it is OK to have the secondary replica/subscriber to communicate to the SSRS Report DB/ReportTempDB via SSRS, right?

Note that for my client the DR is NOT a main concern.

The secondary replica can't be changed inside; and no you can't add indexes.

You can do subsets and add indexes on the transactional subscriber of something but you will likely encounter issues if you ever need to modify your source tables (such as upgrading the application) or reload data for any other reason; it's good practice to remove it before any change then reapply after and then apply your own changes.

Also replication often fails for any numbers of reasons. Basically just a warning that it's likely ongoing work and monitoring rather than set and forget.

For the latter part of your question, you don't have to add it to the AG if you don't want to. I'm not sure what you're getting at.


Cannot connect to my local instance When I connect to my local server, I get this message

A network-related or instance-specific error occurred while establishing a connection to
SQL Server. The server was not found or was not accessible. Verify that the instance name
is correct and that SQL Server is configured to allow remote connections. (provider:
Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft
SQL Server, Error: 2)

This is my login information:

Server type :       Database Engine
Server name :       .
Authentication:     SQL Server Authentication
Login:              sa
Password:           123

How can I fix it?

Go to SQL Server Configuration Manager

Start->all program->SQL Server 2008 R2->Configuration Tools->SQL Server Configuration Manager

Explore "SQL Server Network Configuration" and then select "Protocols For MSSQLSERVER".

Check your TCP/IP Protocols in right side pane. If it is disabled then enabled it and Restart the "Sql Server(MSSQLSERVER) service" that available in  "SQL Server 2008 R2 services pane".

You can follow the same steps to make sure for other protocols and services.


Why does metadata have to stored in table cells according to Codd's 12 rules Among Codd's 12 rules for Relational Database Management Systems is Rule 1, which states the following:

Rule 1: Information rule
All information(including metadata) is to be
represented as stored data in cells of tables. The rows and columns
have to be strictly unordered.

Why is it important that metadata be stored in tables?

Metadata isn't uniquely more important than non-metadata in this context. Rather, his rule just simply wants the reader to realize that "All Information" is inclusive of even the metadata regarding the database itself. This is useful for understanding the structure of the database and the relational model behind it. This is why the Information Schema is part of the ANSI SQL standards.


MongoDB: Server sockets closed after a few minutes I am working with multiple AWS intances connected to the same MongoDB database (inside Compose.io Elastic deployment). But i am getting the error server <url>:<port> sockets closed after a few minutes. Can anyone give me any hint about what may be wrong with the connection code?

CONNECTION CODE

var url = "mongodb://<user>:<password>@<url1>:<port1>,<url2>:<port2>/<dbName>?replicaSet=<replicaSetName>";
var options = {
    server : {"socketOptions.keepAlive": 1},
    replSet : { "replicaSet": <replicaSetName>, "socketOptions.keepAlive": 1 }
};
MongoClient.connect(url, options, function(err, db) { ... });

ERROR MESSAGE

Potentially unhandled rejection [2] MongoError: server <url>:<port> sockets closed
at null. (/var/app/current/node_modules/mongodb/node_modules/mongodb-core/lib/topologies/server.js:328:47)
at g (events.js:199:16)
at emit (events.js:110:17)
at null. (/var/app/current/node_modules/mongodb/node_modules/mongodb-core/lib/connection/pool.js:101:12)
at g (events.js:199:16)
at emit (events.js:110:17)
at Socket. (/var/app/current/node_modules/mongodb/node_modules/mongodb-core/lib/connection/connection.js:142:12)
at Socket.g (events.js:199:16)
at Socket.emit (events.js:107:17)
at TCP.close (net.js:485:12)

First a probably silly question, are you replacing all the , , etc placeholders with the actual values from your compose.io database?

If you are already doing that, maybe you can debug the connection using the mongo shell to connect to your DB instances.

If you have local install of mongodb you can use:

  mongo host:port/database-name -u user -ppassword

This is also what compose.io docs recommend for connection debugging https://docs.compose.io/common-questions/how-to-connect-to-mongodb.html


MySQL failed connection attempts hitting max_connections I run a couple of websites but I believe one of them is causing too many Connections Failed Attempts that causes max_connections variable to hit.

Currently, max_connections=2500 (already large) but total connections per hour due to failed attempts have become greater than 3000. How do I debug this? The httpd error log for the website does show a couple of PHP warnings/notices though. For example, using mysqli instead in the database config.

I don't think the failed connections are causing the max connections issue, but can you set log_warnings=2 and after a short time, view the error log to see if there is more information there? The error log would record something like "access denied for user 'user'@'host'..."

The command to set log_warnings=2 is:

set global log_warnings=2;

You can find the error log like this:

show global variables like 'log_error';

What version of mysql are you running?

Can you also show the output of:

show global variables like 'skip_name_resolve';

and:

show global status like 'max_used_connections';


Clustered Primary Key that is never used vs. Non-Clustered Primary Key on Multiple Columns I am working on a table design for Customer Totals and trying to make a decision about the primary key. I was going to go with a surrogate identity column with a clustered index, but this column would NEVER be used. The candidate primary key columns are CustomerNumber + AccountNumber, because these are the unique identifiers for each row, but these will NOT be sequentially inserted.

Basically, on a daily basis a report will be run which will update each CustomerNumber + AccountNumber record with the most recent purchase total and total date.

Does it make sense to remove CustomerTotalID completely and have CustomerNumber + AccountNumber be a PK with a NON-clustered index?

    CREATE TABLE CustomerTotals (
    CustomerTotalID INT IDENTITY(1,1),
        CustomerNumber INT,
        AccountNumber INT,
        PurchaseTotal DECIMAL(10,2)
        TotalDate DATE,
      CONSTRAINT [PK_CustomerTotals] PRIMARY CLUSTERED (
    CustomerTotalID ASC
)
    )

I'm working on a similar problem right now except more columns and millions of rows. We have a PK that isn't used in any queries. We ran a preliminary test where we changed it to a non-clustered PK and I found two columns that are used in where clauses and created a clustered index on those.

Many queries ran faster and out of more than two dozen indexes on the table we think we will be able to delete 25 of them.

In theory what were are looking to do is not best practices because the columns aren't unique that we're looking to use for the clustered index. But in practice it allows us to save a lot of space by making many non-clustered indexes unneeded and improve I/O by deleting these indexes.


How do I reset the SQL Server password on Linux? The instructions on technet say that I either have to reset the SQL Server configuration with mssql-conf setup or run a proc which I can not run because I do not have access.

What the simplest way to way to reset the password on Linux?

While not mentioned on technet, there is an argument to mssql-conf that works here,

sudo /opt/mssql/bin/mssql-conf set-sa-password

You'll have to pick a password, note for reasons unexplained The password must be at least 8 characters long and contain characters from three of the following four sets: Uppercase letters, Lowercase letters, Base 10 digits, and Symbols.. I suggest everyone use MICROSOFTsucksH4RD, as it satisfies that.

Also, while you're there you may as well use mssql-conf to disable the spyware

I'm just going to put there here so when I'm looking for SQL Server Password on Google because I can't remember it, I can find it.

  The specified password does not meet SQL Server password policy requirements because it is too short. The password must be at least 8 characters


How do you skip databases with Ola.Hallengren's scripts? I have a few databases in which the frontend application manages certain maintenance tasks.  In this particular instance, I'm running the INDEX OPTIMIZE job every night and every so often it clashes with the application and fails.

The following is the error that I receive:

Msg 50000, Level 16, State 1, Server MyTestServer01, Procedure CommandExecute, Line 152
Msg 2550, The index "clidx_StatisticalInterface_dPollTime" (partition 1) on table "StatisticalInterface" cannot be reorganized because it is being reorganized by another process.

I know that there are times where the vendor would rather their builtin solution manage the maintenance.  I'm sure I could find that feature in the application and turn it off, but this question is in case that I can not.

How would I configure or edit this job to ignore a specific database or a list of databases in the same instance?

Sure can.  In the job, you'll see @Databases='USER_DATABASES'.  Just switch it to @Databases='USER_DATABASES,-Db1' any that you don't need to be optimized, place a '-' in front of.

Take a look at the parameters list listed on his website:

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


Database restructure - beneficial? I have a table for email messages. Then, I have a table that has the message parts.

The Parts table contains a

   field_id,  message_id, and data

At the time I used Parts to name the table and yet used field_id for the column. Just an FYI

So for example, a part_id of 2 would be the subject of the message.
I have parts for subject, date, htmlbody, and textbody. Due to this structure, I have approximately 2 more queries per email (one for the parts, and another for the email addresses associated to the email) than if I were to push all the data into the messages tables. I found this structure to be best, but I'm beginning to think it might be wrong and not best for performance.

My question is, will it be in my best interests to restructure the database? Id rather not.

I was thinking about moving the htmlbody and textbody and subject and date to the messages table. Another solution would be to grab all the emails and their data from the Parts table in one query. I could grab all the ids in one query and then do an IN(ids) for the second query.

CREATE TABLE IF NOT EXISTS `messages` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) NOT NULL,
  `account_folder_id` int(10) NOT NULL,
  `hash` varchar(255) NOT NULL,
  `uid` int(10) NOT NULL,
  `seen` tinyint(1) NOT NULL,
  `flagged` tinyint(1) NOT NULL,
  `date_created` int(11) NOT NULL DEFAULT '0',
  `last_modified` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `hash` (`hash`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `message_parts_data` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `message_id` int(10) NOT NULL,
  `field_id` int(10) NOT NULL,
  `data` text NOT NULL,
  `date_created` int(11) NOT NULL DEFAULT '0',
  `last_modified` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `fields` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

INSERT INTO `fields` (`id`, `name`) VALUES
(1, 'To'),
(2, 'Subject'),
(3, 'Date'),
(4, 'From'),
(5, 'CC'),
(7, 'ReplyTo'),
(8, 'textHtml'),
(9, 'textPlain'),
(11, 'Forward');

Thanks

I can't tell you the best structure for your data because I don't know all your needs, however, I can tell you how I structured my tables for a similar project that had to store a scalable amount of email messages in a searchable form that may or may not be geographically distributed. This is what we did:

There was a messages table consisting of a unique message_id as primary key, as well as some fields to store information that was not contained in the message itself (data from the SMTP envelope) such as dates and times, sender, sending server, a few performance indicators, etc. Additionally, there was a storage_id, to indicate which storage structure contained the parts/attachments (usually a folder path, but could be a schema-based address, such as sftp://user@host/path/to/folder).

Next was the headers table which stored one message header line per row. This contained a unique header_id as primary key, as well as a message_id (indexed) that linked back to a record in the messages table. There was a header_name field (indexed), which contained the header name (eg: "Subject", "From", etc), and a header_text field, which contained the text of the header. There was also a sequence number (indexed) that was used to indicate the order of the headers. Due to our need to search these headers, we used fulltext indexing on header_text.

Then there was a parts_text table which stored only the text parts of a message. This table had a unique part_id as primary key, a message_id, a sequence and some fields for storing mime types and part-markers, and the part_body field, which stored the entire text. This field was fulltext indexed, however in the long term we planned to run the message's text parts through a lucene indexing process and just store the results in the db, with the actual text part stored as a file in the area designated by the message's storage_id.

Then there was a parts_binary table which stored the same things as the parts_text table but without the part_body. That was stored as a file in the area designated by the message's storage_id. Note that the sequence in this table would be interspersed with the sequence from the parts_text table.

I know this is not a direct answer to your question, but this structure worked for us so maybe it can be of some help to you.


Does tables under schema uses multiple tablespaces other than default tablespace I have schema A in a database and have assigned the default tablespace as myspace.
There are 20 tables that reside under this single schema and all the tables are using different tablespaces.
My question is:
Why are all tables using different tablespaces instead of the default tablespace?

A default tablespace is just that, a default.  If you specify a different tablespace when you create a table, the table will be created in the tablespace you specified.  Though it would generally not make sense to do so, you could put each table in a different tablespace so that the user owned 20 tables in 20 different tablespaces none of which were the default tablespace.

Alternately, a user's default tablespace can be changed so it is possible that the 20 tables were created without a specified tablespace but when the user's default tablespace was different.  Then the user's default tablespace was changed after all the tables were created.


Partition switching vs schema transfer I'm designing a database whose data will be generated daily by an ETL.
After some research, I found that one can use partition switching or schema transfer to maximize the uptime of the "live" data. Just for completeness of information, this database will be deployed on Azure SQL Database and developed on SQL Server 2019 Developer Edition on Docker. It consists in ~ 20 tables, each one having less than 10M rows (expected).
So now in my design there are two schemas:

[App]: containing multiple tables (w/o foreign keys) with the live data used by the application.
[AppShadow]: containing the exact same tables as the [App] schema (but w/ foreign keys), usually empty, used as destination for the ETL.

My flow looks like this:

Perform the ETL using the tables in the [AppShadow] schema as a destination. The foreign keys ensure that my data is consistent.
set transaction isolation level serializable; begin transaction;
Disable all the constraints on the tables in the [AppShadow] schema using alter table [AppShadow].[<table_name>] nockeck constraint all;
Truncate all the tables in the [App] schema
Switch the partition of all the tables from [AppShadow] to [App]
Re-enable all the constraints on the tables in the [AppShadow] schema using alter table [AppShadow].[<table_name>] with check check constraint all;
commit transaction;

This solutions seems to work fine and the downtime is minimal, but I can't figure out any advantage or disadvantage over transferring the entire tables between schemas using a [Swap] schema and the alter schema <schema_name> transfer <table_name> statement.
SQL Server being SQL Server, I'm pretty sure there are some hidden caveats that I'm unaware of that should let me prefer a solution over the other.
Do you have any advice or suggestion?

Do you have any advice or suggestion?

All the operations you described are metadata-only, so I wouldn't expect any significant difference in performance.
It is strange that you don't have Foreign Keys on your [App] schema.  You've already paid the cost of checking the DRI, and if you have validated foreign keys in your database the Query Optimizer can take advantage of them.
Schema transfer would allow you to leave all the foreign keys in-place.


counting the number of a specific word in one SQL column I'm trying to make a survey like a referundum to know if my neighboors in my small building are for or against a colectif compost to reduce the organics wastes.

For this tool vote I'm using PHP and SQL, my database is build like this:

DBNAME=collectifCompost
Table=voteCompost

column1=id PRIMARY AI

column2=user

column3=vote(yes/no/blank)

To build a chart with statistics vote results (% of YES, NO, BLANK..) I want to make a query on my database to count the number of YES NO AND BLANK but I don't know how. Can you help me please?

If you need more information about my code ask me everything.

Use:

select   vote, count(*)
from     voteCompost
group by vote;

Now you get the number of rows per vote value.


Is fragmentation at non-leaf levels a problem? SQL version: 2017 Standard x64
OS: Windows Server 2016 Datacenter (10.0)

I've just created some minimal indexes on ETL tables which were previously heaps with no indexes.  Both R and W performance have improved.  Thought I'd keep on eye on index fragmentation, so I'm looking at sys.dm_db_index_physical_stats: in DETAILED mode simply to learn more about this area.  I'm an
experienced DWH developer rather than a DBA, so my questions may be very beginner to DBAs (there is no DBA here, or they'd be taking care of this).

The index I'm looking at is a clustered unique index.  This table has 1.3m rows.  At the leaf level (0) I see .89% fragmentation: good.  At higher levels though there is fragmentation.  Index has levels 0-3.  At level 2 fragmentation is 52%, fragment_count=23, record_count=2781.

This is confusing me, especially as I only created the index yesterday.

Is fragmentation at non-leaf levels a performance concern?
How can there be so much fragmentation at level 2 already?
In a clustered index, what do non-leaf (>0) levels in the B-tree actually store?  It can't be row data as in level 0: is it a pointer to a set of lower index entries?
If I'm right about 3, it would seem a trivial job to reorganise/rebuild an upper level of a B-tree, e.g. one with only 2781 records, without going near the heavy-duty work of reorganising the leaf level.  But I can't see any mention of a way to do this.
What does fragment_count actually mean, especially at higher levels?  The documentation only says that logical fragmentation is "the percentage of out-of-order pages in the leaf pages of an index".  Is fragment_count a count of contiguous areas of the index, which are in-order internally?

No. Non-leaf index pages are particularly likely to stay in cache. In principle, it might slow down read-ahead a little (upper levels are used to drive this) the first time an index scan with read-ahead occurs, but meh. Some people regard fragmentation at any level a normal thing and not something to worry about if the data is usually already in memory or if the storage subsystem has low latency and good throughput.
Because SQL Server doesn't go out of its way to avoid it. Non-leaf pages can experience page splits too. Padding the index above the leaf (PAD_INDEX) is possible but rarely worthwhile.
The clustered index key(s) and any uniquifier.
This isn't implemented.
I believe it is a count of the number of contiguous areas at that level of the index, yes.


How to optimise T-SQL query using Execution Plan I have a SQL query that I have spent the past two days trying to optimise using trial-and-error and the execution plan, but to no avail. Please forgive me for doing this but I will post the entire execution plan here. I have made the effort to make the table and column names in the query and execution plan generic both for brevity and to protect my company's IP. The execution plan can be opened with SQL Sentry Plan Explorer.

I have done a fair amount of T-SQL, but using execution plans to optimise my query is a new area for me and I have really tried to understand how to do it. So, if anyone could help me with this and explain how this execution plan can be deciphered to find ways in the query to optimise it, I would be eternally grateful. I have many more queries to optimise - I just need a springboard to help me with this first one.

This is the query:

DECLARE @Param0 DATETIME     = '2013-07-29';
DECLARE @Param1 INT          = CONVERT(INT, CONVERT(VARCHAR, @Param0, 112))
DECLARE @Param2 VARCHAR(50)  = 'ABC';
DECLARE @Param3 VARCHAR(100) = 'DEF';
DECLARE @Param4 VARCHAR(50)  = 'XYZ';
DECLARE @Param5 VARCHAR(100) = NULL;
DECLARE @Param6 VARCHAR(50)  = 'Text3';

SET NOCOUNT ON

DECLARE @MyTableVar TABLE
(
    B_Var1_PK int,
    Job_Var1 varchar(512),
    Job_Var2 varchar(50)
)

INSERT INTO @MyTableVar (B_Var1_PK, Job_Var1, Job_Var2)
SELECT B_Var1_PK, Job_Var1, Job_Var2 FROM [fn_GetJobs] (@Param1, @Param2, @Param3, @Param4, @Param6);

CREATE TABLE #TempTable
(
    TTVar1_PK INT PRIMARY KEY,
    TTVar2_LK VARCHAR(100),
    TTVar3_LK VARCHAR(50),
    TTVar4_LK INT,
    TTVar5 VARCHAR(20)
);

INSERT INTO #TempTable
SELECT DISTINCT
    T.T1_PK,
    T.T1_Var1_LK,
    T.T1_Var2_LK,
    MAX(T.T1_Var3_LK),
    T.T1_Var4_LK
FROM
    MyTable1 T
    INNER JOIN feeds.MyTable2 A ON A.T2_Var1 = T.T1_Var4_LK
    INNER JOIN @MyTableVar B ON B.Job_Var2 = A.T2_Var2 AND B.Job_Var1 = A.T2_Var3
GROUP BY T.T1_PK, T.T1_Var1_LK, T.T1_Var2_LK, T.T1_Var4_LK

-- This is the slow statement...
SELECT
    CASE E.E_Var1_LK
        WHEN 'Text1' THEN T.TTVar2_LK + '_' + F.F_Var1
        WHEN 'Text2' THEN T.TTVar2_LK + '_' + F.F_Var2
        WHEN 'Text3' THEN T.TTVar2_LK
    END,
    T.TTVar4_LK,
    T.TTVar3_LK,
    CASE E.E_Var1_LK
        WHEN 'Text1' THEN F.F_Var1
        WHEN 'Text2' THEN F.F_Var2
        WHEN 'Text3' THEN T.TTVar5
    END,
    A.A_Var3_FK_LK,
    C.C_Var1_PK,
    SUM(CONVERT(DECIMAL(18,4), A.A_Var1) + CONVERT(DECIMAL(18,4), A.A_Var2))
FROM #TempTable T
    INNER JOIN TableA (NOLOCK) A ON A.A_Var4_FK_LK  = T.TTVar1_PK
    INNER JOIN @MyTableVar     B ON B.B_Var1_PK     = A.Job
    INNER JOIN TableC (NOLOCK) C ON C.C_Var2_PK     = A.A_Var5_FK_LK
    INNER JOIN TableD (NOLOCK) D ON D.D_Var1_PK     = A.A_Var6_FK_LK
    INNER JOIN TableE (NOLOCK) E ON E.E_Var1_PK     = A.A_Var7_FK_LK
    LEFT OUTER JOIN feeds.TableF (NOLOCK) F ON F.F_Var1 = T.TTVar5
WHERE A.A_Var8_FK_LK = @Param1
GROUP BY
    CASE E.E_Var1_LK
        WHEN 'Text1' THEN T.TTVar2_LK + '_' + F.F_Var1
        WHEN 'Text2' THEN T.TTVar2_LK + '_' + F.F_Var2
        WHEN 'Text3' THEN T.TTVar2_LK
    END,
    T.TTVar4_LK,
    T.TTVar3_LK,
    CASE E.E_Var1_LK
        WHEN 'Text1' THEN F.F_Var1
        WHEN 'Text2' THEN F.F_Var2
        WHEN 'Text3' THEN T.TTVar5
    END,
    A.A_Var3_FK_LK,
    C.C_Var1_PK

IF OBJECT_ID(N'tempdb..#TempTable') IS NOT NULL
BEGIN
    DROP TABLE #TempTable
END
IF OBJECT_ID(N'tempdb..#TempTable') IS NOT NULL
BEGIN
    DROP TABLE #TempTable
END

What I have found is that the third statement (commented as being slow) is the part that is taking the most time. The two statements before return almost instantly.

The execution plan is available as XML at this link.

Better to right-click and save and then open in SQL Sentry Plan Explorer or some other viewing software rather than opening in your browser.

If you need any more information from me about the tables or data, please don't hesitate to ask.

Before getting to the main answer, there are two pieces of software you need to update.
Required Software Updates
The first is SQL Server. You are running SQL Server 2008 Service Pack 1 (build 2531). You ought to be patched up to at least the current Service Pack (SQL Server 2008 Service Pack 3 - build 5500). The most recent build of SQL Server 2008 at the time of writing is Service Pack 3, Cumulative Update 12 (build 5844).
The second piece of software is SQL Sentry Plan Explorer. The latest versions have  significant new features and fixes, including the ability to directly upload a query plan for expert analysis (no need to paste XML anywhere!)
Query Plan Analysis
The cardinality estimate for the table variable is exactly right, thanks to a statement-level recompilation:

Unfortunately, table variables do not maintain distribution statistics, so all the optimizer knows is that there are six rows; it knows nothing of the values that might be in those six rows. This information is crucial given that the next operation is a join to another table. The cardinality estimate from that join is based on a wild guess by the optimizer:

From that point on, the plan chosen by the optimizer is based on incorrect information, so it's no wonder really that performance is so poor. In particular, the memory set aside for sorts and hash tables for hash joins will be far too small. At execution time, the overflowing sorts and hashing operations will be spilled out to physical tempdb disk.
SQL Server 2008 does not highlight this in execution plans; you can monitor the spills using  Extended Events or Profiler Sort Warnings and Hash Warnings. Memory is reserved for sorts and hashes based on cardinality estimates before execution starts, and cannot be increased during execution regardless of how much spare memory your SQL Server may have. Accurate row count estimates are therefore crucial for any execution plan that involves workspace memory consuming operations.
Your query is also parameterized. You should consider adding OPTION (RECOMPILE) to the query if different parameter values affect the query plan. You should probably consider using it anyway, so the optimizer can see the value of @Param1 at compilation time. If nothing else, this may help the optimizer produce a more reasonable estimate for the index seek shown above, given that the table is very large, and partitioned. It may also enable static partition elimination.
Try the query again with a temporary table instead of the table variable and OPTION (RECOMPILE). You should also try materializing the result of the first join into another temporary table, and run the rest of the query against that. The number of rows is not all that large (3,285,620) so this should be reasonably quick. The optimizer will then have an exact cardinality estimate and distribution statistics for the result of the join. With luck, the rest of the plan will fall into place nicely.
Working from the properties shown in the plan, the materializing query would be:
SELECT
    A.A_Var7_FK_LK,
    A.A_Var4_FK_LK,
    A.A_Var6_FK_LK,
    A.A_Var5_FK_LK,
    A.A_Var1,
    A.A_Var2,
    A.A_Var3_FK_LK
INTO #AnotherTempTable
FROM @MyTableVar AS B
JOIN TableA AS A
    ON A.Job = B.B_Var1_PK
WHERE
    A_Var8_FK_LK = @Param1;

You could also INSERT into a predefined temporary table (the correct data types are not shown in the plan, so I cannot do that part). The new temporary table may or may not benefit from clustered and nonclustered indexes.


can I put HAVING with select query I have  this SQL code

SELECT *,(SELECT COUNT(e.clients_idclients) FROM experienced e JOIN designation d WHERE e.designation_iddesignation = d.iddesignation AND e.clients_idclients = clients.idclients AND d.iddesignation IN('268'))as de_count
FROM `clients`
LEFT JOIN `experienced` ON `experienced`.`clients_idclients` = `clients`.`idclients`
LEFT JOIN `designation` ON `designation`.`iddesignation` = `experienced`.`designation_iddesignation`
LEFT JOIN `industry` ON `industry`.`idindustry` = `experienced`.`industry_idindustry`
LEFT JOIN `company` ON `company`.`idcompany` = `experienced`.`company_idcompany`
LEFT JOIN `academic_qualifications` ON `academic_qualifications`.`clients_idclients` = `clients`.`idclients`
LEFT JOIN `qulification` ON `qulification`.`idqulification` = `academic_qualifications`.`qulification_idqulification`
LEFT JOIN `institute` ON `institute`.`idinstitute` = `academic_qualifications`.`institute_idinstitute`
LEFT JOIN `job_category` ON `experienced`.`job_category_idjobCategory` = `job_category`.`idjobCategory`
LEFT JOIN `client_tag` ON `client_tag`.`clients_idclients` = `clients`.`idclients`
LEFT JOIN `cv` ON `cv`.`clients_idclients` = `clients`.`idclients`
LEFT JOIN `tag` ON `tag`.`idtag` = `client_tag`.`tag_idtag`
WHERE `clients`.`status_idstatus` != '10'
AND `clients`.`clientsAge` >= '0'
AND `clients`.`clientsAge` <= '100'
AND `clients`.`clientsOtherQualifications` = '1'
AND (`clients`.`fullName` LIKE '%custorme%'  OR `clients`.`clientsEmail` LIKE '%custorme%' OR `designation`.`designationName` LIKE '%custorme%' OR `cv`.`cvName` LIKE '%custorme%' OR `industry`.`industryName` LIKE '%custorme%' OR `company`.`companyName` LIKE '%custorme%' OR `qulification`.`qulificationName` LIKE '%custorme%' OR `institute`.`instituteName` LIKE '%custorme%' OR `job_category`.`jobCategoryName` LIKE '%custorme%' OR `tag`.`tagName` LIKE '%custorme%')
GROUP BY `clients`.`idclients`
HAVING de_count > 0
ORDER BY `clients`.`idclients` DESC
LIMIT 30

Can I write this  SQL code as

HAVING > (SELECT COUNT(e.clients_idclients) FROM experienced e JOIN designation d WHERE e.designation_iddesignation = d.iddesignation AND e.clients_idclients = clients.idclients AND d.iddesignation IN('268'))

without defining it next to the top select?

I would guess "yes".  But I would not ask anyone, I would simply try it.  It is non-destructive, etc; trying is much faster and easier than waiting for an answer.


Optimizing read query in PostgreSQL I'm having a tough time optimizing a read query from PostgreSQL 10. The query is as follows

SELECT mp.id_person, (
    COUNT(CASE
          WHEN mp.id_g_type = mc.id_g_type
               (and mc.g1 = mp.g1 or mc.g1 = mp.g2 or mc.g2 = mp.g1 or mc.g2 = mp.g2)
          THEN 1
          END
         ) / CAST(COUNT(mp.id_g_type) AS decimal)) as probability
FROM significant_mg mp
INNER JOIN significant_mg mc on mp.id_g_type= mc.id_g_type
WHERE mp.id_person!= 28076 AND mc.id_person= 28076
GROUP BY mp.id_person
HAVING COUNT(CASE
          WHEN mp.id_g_type = mc.id_g_type
               and (mc.g1 = mp.g1 or mc.g1 = mp.g2 or mc.g2 = mp.g1 or mc.g2 = mp.g2)
          THEN 1
          END
         ) / CAST(COUNT(mp.id_g_type) AS decimal) > 0.97

I've created an indices, one on the id_person column, the other one on id_g_type column. The significant_mg is not a table but a materialized view that contains columns id_person, id_g_type (both bigints) and columns g1 and g2 (both smallints).

The id 28076 in the where clause won't be fixed as I only use this id for testing purposes, it will be replaced by a variable when I'm done optimizing the select.

The result of explain analyze is as follows

"Finalize HashAggregate  (cost=140784.56..141226.84 rows=6552 width=40) (actual time=2869.754..2891.226 rows=14 loops=1)"
"  Group Key: mp.id_person"
"  Filter: (((count(CASE WHEN ((mp.id_g_type = mc.id_g_type) AND ((mc.g1 = mp.g1) OR (mc.g1 = mp.g2) OR (mc.g2 = mp.g1) OR (mc.g2 = mp.g1))) THEN 1 ELSE NULL::integer END))::numeric / (count(mp.id_g_type))::numeric) > 0.97)"
"  Rows Removed by Filter: 19818"
"  ->  Gather  (cost=136165.16..140293.13 rows=39314 width=24) (actual time=2796.706..2823.029 rows=59496 loops=1)"
"        Workers Planned: 2"
"        Workers Launched: 2"
"        ->  Partial HashAggregate  (cost=135165.16..135361.73 rows=19657 width=24) (actual time=2744.110..2753.896 rows=19832 loops=3)"
"              Group Key: mp.id_person"
"              ->  Nested Loop  (cost=0.43..103303.58 rows=1593079 width=32) (actual time=11.259..1589.526 rows=1283988 loops=3)"
"                    ->  Parallel Seq Scan on significant_mg mc  (cost=0.00..44870.04 rows=82 width=12) (actual time=11.189..373.041 rows=66 loops=3)"
"                          Filter: (id_person = 28076)"
"                          Rows Removed by Filter: 1291777"
"                    ->  Index Scan using i_g_type on significant_mg mp  (cost=0.43..518.83 rows=19377 width=20) (actual time=0.041..11.846 rows=19454 loops=198)"
"                          Index Cond: (id_g_type = mc.id_g_type)"
"                          Filter: (id_person <> 28076)"
"                          Rows Removed by Filter: 1"
"Planning Time: 0.498 ms"
"Execution Time: 2893.309 ms"

Is there any way I could improve the query performance?

UPDATED (simplified) query:

SELECT mp.id_person
FROM significant_mg mp
INNER JOIN significant_mg mc on mp.id_g_type = mc.id_g_type
WHERE mp.id_person != 28076 AND mc.id_person = 28076
GROUP BY mp.id_person
HAVING SUM(CASE
          WHEN mc.g1 = mp.g1 or mc.g1 = mp.g2 or mc.g2 = mp.g1 or mc.g2 = mp.g1
          THEN 1
          END
         ) / CAST(COUNT(mp.id_g_type) AS decimal) > 0.97

You are fundamentally asking it to do a lot of work, there is no way around that.  You can perhaps shave some time off of this, but you won't get orders of magnitude improvement.  If you explain some of the motivation behind this, perhaps there is a way to materialize the view in a different way which will lead to large improvements.

For just optimizing the query as given:

You could create an index on all four columns, leading with "id_g_type", so that the index scan can be turned into an index-only scan.  You probably need to manually vacuum the materialized view to make this effective.

The reason it is doing a seq scan on "mc", rather than using the index on "id_person", is that the seq scan can make use of parallelism while the index scan can not.  You can play around with "max_parallel_workers_per_gather" (or with alter table significant_mg set (parallel_workers = ...); to see how much you actually benefit from parallelism.

There is no point in including mp.id_g_type = mc.id_g_type in the WHENs, because the join condition will ensure all rows pass that criterion.


Calculating percentage of a row over total sum Apologies for the bad title, I wasn't sure what would be a good title for this.

This is currently (simplified view of the) data I'm working with

Agent    |  Commission
---------|------------
Smith    |    100
Neo      |    200
Morpheus |    300

I need to calculate the percentage of the total commission, each agent is responsible for.

So, for Agent Smith, the Percentage would be calculated as (Agent Smith's commission / Sum(commission)*100

So, my expected data would be

Agent    |  Commission   |  % Commission
---------|---------------|---------------
Smith    |    100        |     17
Neo      |    200        |     33
Morpheus |    300        |     50

I have a function returning the commission for each agent. I have another function returning the percentage as (Commission/Sum(Commission))*100. The problem is that Sum(commission) gets calculated for each and every row, and given that this query would be run on a Data Warehouse, the data set would be rather large ( currently, it's just under 2000 records) and quite honestly, a bad approach (IMO).

Is there a way of having the Sum(Commission) not calculate for every row being fetched ?

I was thinking something on the lines of a 2 part query, the first part would fetch the sum(commission) into a package variable/type and the second part would refer to this pre-calculated value, but I'm not sure how I can accomplish this.

I am limited to using SQL, and I'm running on Oracle 10g R2.

You're looking for the analytical function ratio_to_report

select
  agent,
  round(ratio_to_report(commission) over ()*100) "% Comm."
from
  commissions;


What is the consensus on using NOT NULL constraints on all text-based fields in a table? Some of the tables I have come across don't have any NOT NULL constraints, which has yielded to some gotchas when querying.

I'm curious what the feelings are with using something like NOT NULL DEFAULT '', which would replace the need of the coalesce() function when retrieving/filtering records.  On the other hand instead of coalesce, you'd have to use CASE statements or, if you still wanted to use coalesce, you'd do something like coalesce(nullif(field_foo,''), field_bar)

Semantically that data IS NULL, setting it to something else is awkward at best. We can always assume NULL is NULL, we know what that is. if you use '' then all of a sudden you have to keep track of what you're setting as your new NULL. Just use the construct of the system where possible.

Using a value for a null has problems as well; a query will never match a null to anything; it's not a value so no range of options will ever match it; the exception is the IS NULL condition, which has special semantic meaning as NULL has special semantic meaning.

In addition you're dubiously exploiting the database's NOT NULL constraint. That constraint is meant to mean that column always has data. Your data technically fulfills that constraint, but it does not semantically.

The only reason I can see for setting NOT NULL and using a homebrewed "NULL" value is when an application is inserting it's own data and it has it's own value for NULL. For instance PHP's NULL when stored to a database column will appear as a value, not a SQL NULL. This can be avoided with proper programming in the application however, and I would only consider the "fake NULL" solution if I had no control over the application that was inserting "fake nulls"-- at that point the battle is lost, and your solution becomes the most applicable.


Two logs files for a single database Is there any reason or performance benefit in having two log files for a single database and on different drives?

Logs are written to sequentially, so only one would ever be used at a time.  So, no, there is no benefit from having two on the same database.

EDIT: I looked over the part about different drives.  I suppose there could be a need in having two log files if you are working with limited drive space and your original log file would outgrow it's drive and you cannot move it to a larger drive for whatever reason.


How to prevent Deadlock on SELECT queries? I have a multi-session DB.
Few sessions inserting many rows to the DB. and one session selects from this table.

Sometimes I'm getting a deadlock on the SELECT query and I don't understand why.

For example: Assume this table

Create Table t1([id] int identity(1,1), [column] varchar(max))

Insertions go like this: (I'm doing it from an 'after' trigger)

Begin Transaction
Insert into t1 WITH (TABLOCKX) ([column]) values ('value 1'), ('value 2') ... ('value N')
Commit Transaction

The selection goes like this:

Select TOP 10 [id],[column] from t1 order by id

The lock on the insertions meant to keep the sequence of session insertions in the DB.

Each time I lock only one table in a transaction. I've tried to recover the deadlock with one session inserting and second one selecting but I've failed.
Before I've added the TABLOCKX I've never got the deadlock.

So my questions are:

How come a deadlock possible? (Maybe in a nested transaction?)
Is there's an option in MSSQL to do a write lock in a manner to not block the selects?
Any idea to prevent deadlock? (but keep session insertion sequence in the DB without changes...)

I can't say much about your deadlocks without the queries or a deadlock graph.
Is there any reason why you specify an exclusive table lock?
SQL Server is very good at escalating locks from row or page to table itself.

  Is there's an option in MSSQL to do a write lock in a manner to not block the selects?

Yes there is, you can enable READ COMMITTED SNAPSHOT ISOLATION LEVEL.
With this isolation level you can always read what's committed. If another query is writing at that moment you will see the latest snapshot of what was written.
Please read through the MSDN page and see if RCSIL will work for your app.


Are multiple databases sharding for read on docker as a good idea? So I have a server with multiple CPU cores and it installed a database. Do you think that if we install multiple databases (data sharding) with docker, then each request will go to different databases is a good idea? The data is different in each database and based on the request from a client it will query on the different database.

I don't know this can be worse than a dedicated database with all data or not?

The gotcha with sharding is that the application has to know which shard to query. Generally, this is done by sharding on something like client. I'll adapt one of my old blog posts to use as my answer.

When you’re building an application for lots of clients, there’s two common ways to design the database(s):

Option A: Put all clients in the same database
Option 2: Build one database per client

Putting All the Clients in the Same Database

It’s simple: just add a Client table at the top of the schema, add a ClientUsers table to make sure people only see their own data, and away we go.

Benefits of this approach:

Easier schema management. When developers deploy a new version of the application, they only have to make schema changes in one database.  There’s no worries about different customers being out of sync or on the wrong version.

Easier performance tuning. We can check index usage and statistics in just one place, implement improvements easily, and see the effects immediately across all our clients.  With hundreds or thousands of databases, even the smallest change can be difficult to coordinate.  We can check our procedure cache contents and know for certain which queries or stored procedures are the most intensive across our entire application, whereas if we’re using separate databases per client, we may have a tougher time aggregating query use across different execution plans.

Easier to build an external API. If we need to grant access to our entire database for outsiders to build products, we can do that easier if all of the data is in a single database.  If the API has to deal with grouping data from multiple databases on multiple servers, it adds development and testing time.  (On the other hand, that “multiple servers” thing starts to hint at a restriction for the one-database-to-rule-them-all scenario: one database usually means all our load impacts just one database server.)

Easier high availability & disaster recovery. It’s really, really simple to manage database mirroring, log shipping, replication, and clustering if all we have to worry about is just one database.  We can build a heck of an infrastructure quickly.

Putting Each Client in its Own Database or Shard

You still need a client listing, but now it becomes a directory - for each client, you also track the shard it lives in. On startup, your app queries this table, and caches it in RAM. When it needs data for a client, it connects directly to that shard (database & server).

Benefits of this approach:

Easier single-client restores. Clients are unreliable meatbags.  (Except mine – they’re reliable meatbags.)  They have all kinds of “oops” moments where they want to retrieve all of their data back to a point in time, and that’s a huge pain in the rear if their data is intermingled with other client data in the same tables.  Restores in a single-client-database scenario are brain-dead easy: just restore the client’s database.  No one else is affected.

Easier data exports. Clients love getting their hands on their data.  They want the security of knowing they can get their data out anytime they want, avoiding the dreaded vendor lock-in scenario, and they want to do their own reporting.  With each client’s data isolated into their own database, we can simply give them a copy of their own database backup.  We don’t have to build data export APIs.

Easier multi-server scalability. When our application needs more power than we can get from a single server, we can divide up the databases between multiple servers.  We can also spread out the load geographically, putting servers in Asia or Europe to be closer to clients.

Easier per-client performance tuning. If some clients use different features or reports, we can build a specialized set of indexes or indexed views just for those clients without growing everyone’s data size.  Granted, there’s some risk here – by allowing schema differences between clients, we’ve just made our code deployments a little riskier and our performance management more difficult.

Easier security management. As long as we’ve properly locked down security with one user per database, we don’t have to worry about Client X accessing Client Y’s data.  However, if we just use a single login for everyone, then we haven’t really addressed this concern.

Easier maintenance windows.  In a global environment where customers are scattered around the globe, it’s easier to take customers offline for maintenance if we can do it in groups or zones.

Which one is right for you?

There’s no one right choice: you have to know your own company’s strengths and weaknesses.  Let’s take two of my clients as examples.

Company A excels at hardware performance tuning.  They’re really, really good at wringing the very last bit of performance out of hardware, and they don’t mind replacing their SQL Server hardware on a 12-18 month cycle.  (They refresh web servers every 4-6 months!)  Their Achilles’ heel is extreme compliance and security requirements.  They have incredible auditing needs, and it’s just easier for them to implement bulletproof controls on a single server, single database than it is to manage those requirements across thousands of databases on dozens of servers.  They chose one database, one server, many clients.

Company 2 excels at development practices.  Managing schema changes and code deployments across thousands of databases just isn’t an issue for them.  They have clients around the world, and they’re processing credit card transactions for those clients around the clock.  They need the ability to spread load geographically, and they don’t want to replace servers around the world every 12-18 months.  They chose one database for each client, and it’s paying off as they start to put SQL Servers in Asia and Europe for their offshore clients.