texts: Performance problem caused by joining with the result of a scalar function?

texts:
In the following example the performance of the first version using only one SQL query is very bad. The problem is the last join.

When I store the result of the left side into a temporary table and join this, it runs about 50 times quicker.

It is intended to convert this to Oracle, therefore I try to avoid the use of temporary tables.

Any explanations, why the first query behaves so bad or hint to improve it?

Declare @HO int = 2866;
Declare @Dtz int = 35;

---- version 1 takes about 60 seconds
With ratings as
(
    select
        ROW_NUMBER() Over ( ORDER BY SEDate) lfd,
        SEDate,
        BBCode
    from  PPV
        join BB on BBRefnr = SEBBRefnr
    where SEHORefnr = @HORefnr
    and SEBBRefnr > 0
)
Select
    SEDATE,
    BBCODE,
    Code,
    DTRefnr
FROM (
Select
    l.SEDate,
    l.BBCode,
    dbo.GetCode(@Dtz, l.BBCode) Code
from ratings l left join ratings r on l.lfd = r.lfd + 1
where l.BBCode <> r.BBCode
or r.BBCode is null
) as t
    join DT d on code = d.DTCode and d.DTDTKRefnr = @Dtz
order by 1;

------- version 2 using a temp table takes less than 1 second

With ratings as
(
    select
        ROW_NUMBER() Over ( ORDER BY SEDate) lfd,
        SEDate,
        BBCode
    from  PPV
        join BB on BBRefnr = SEBBRefnr
    where SEHORefnr = @HORefnr
    and SEBBRefnr > 0
)
Select
    l.SEDate,
    l.BBCode,
    dbo.GetCode(@Dtz, l.BBCode) Code
into #tmp
from ratings l left join ratings r on l.lfd = r.lfd + 1
where l.BBCode <> r.BBCode
or r.BBCode is null

Select
    SEDATE,
    BBCODE,
    Code,
    DTRefnr
FROM #tmp
    join DT d on code = d.DTCode and d.DTDTKRefnr = @Dtz
order by 1;

EDIT:

Here the function:

Create function dbo.GetCode (
    @fDtz int,
    @Value varchar(10)
) returns varchar(10)
as
begin
    declare @CODE varchar(10)
    SET @Code =
        (SELECT Code FROM Ableitungen
        WHERE DTZ = @fDtz
        AND Value = @Value )

    return @CODE
end


texts: How to set/get a field of a nested composite type variable?

texts:
For example:

create type t1 as (a int2, b text, c boolean);
create type t2 as (a t1, b boolean);
create type t3 as (a t2, b int4);

Now, how can I do the following for a variable x of type t3:

x.a.a.c := true;
y := x.a.a.c;

The first gives me an error.


texts: MySQL Explain gives different result for same query, query performance changes

texts: I've been having some performance issues on my production server. The main issue, I think is the table pricelist_entries, which has approximately 6 million rows. Basically its holds all of the products available. The query below usually takes a second to execute but ever since the table pricelist_entries grew past 3 million rows the execution time has started to increase. The weird thing is that sometimes the same query will take 10 seconds, other times 1 second. First I thought that maybe its the query cache but I disabled that and I still get the same variating result. What I can see from the two explains is that when its fast the query only accesses about 5000 rows while when its slow it seem to do an almost full table scan. I can't figure out why this happens. Any help is appreciated.


texts:
How can I create a query that will find all the databases on different servers?

I have four servers,  PRDSVR, UATSVR, DEVSVR, TRNSVR; with either copies of the same database on each server or standalone databases that may only be on one or two servers.

I would like to create a query that searches for all databases, listing the server, databases on that server, and the size of each of the databases.  How can I achieve this?

Thanks in advance for any suggestions.


texts: Better way to select a matching subset of rows?

texts:
DB Fiddle link. I have a many to many table like this:

CREATE TABLE house_to_cats (
  id SERIAL PRIMARY KEY,
  house_id INTEGER,
  cat_id INTEGER
);

-- house with cats 1 and 2: too small
INSERT INTO house_to_cats (house_id, cat_id) VALUES (1, 1), (1, 2);
-- house with cats 1 2 3 4: too big
INSERT INTO house_to_cats (house_id, cat_id) VALUES (2, 1), (2, 2), (2, 3), (2, 4);
-- house with cats 1 2 3: just right
INSERT INTO house_to_cats (house_id, cat_id) VALUES (3, 1), (3, 2), (3, 3);

I need a query that takes an arbitrary list of cats and returns a matching house if exists. I came up with this:

SELECT
    house_id
FROM (
    SELECT
          house_id
        , ARRAY_AGG(cat_id) as cat_id_agg
    FROM house_to_cats
    JOIN (
        SELECT DISTINCT
              house_id
        FROM house_to_cats
        JOIN (SELECT * FROM UNNEST(ARRAY[1, 2, 3]) cat_id) inn USING (cat_id)
    ) filter USING (house_id)
    GROUP BY house_id
) agg
WHERE cat_id_agg <@ ARRAY[1, 2, 3]
  AND cat_id_agg @> ARRAY[1, 2, 3];

Is there a better way to do this?

The idea behind my query: in filter, get the house_id which have at least one of our cat in them. In agg, create cat_id_agg arrays for all of those house_ids. And in the outermost query filter out groups that don't match our set.


texts: if set global transaction, how can I commit?

texts: I found that MySQL can set a global transaction, but can I commit in another session? refer: http://dev.mysql.com/doc/refman/5.6/en/set-transaction.html


texts:
I'm trying to create a procedure that will take the values from a temporary table, and load them into another table. The second table has a composite unique constraint across its three columns (Not including the primary key) I'm using a merge statement to try and handle the upload, and keep getting an error saying
Msg 2627, Level 14, State 1, Line 13

Violation of UNIQUE KEY constraint 'IX_table'.
Cannot insert duplicate key in object 'dbo.table'. The duplicate key value is (aaa, aaa, aaa).

I'm really confused because the whole reason I am using the merge statement is to prevent exactly this from happening. Here's the relevant portion of the procedure (With names changed, and the insertion to the temporary table changed)

create table #TempTable(Id int
                        , str1 varchar(3)
                        , str2 varchar(15)
                        , str3 varchar(10))

INSERT INTO #TempTable
           (str1
           ,str2
           ,str3)
     VALUES
           ('aaa','aaa','aaa'), ('bbb', 'bbb', 'bbb'), ('aaa','aaa','aaa')

MERGE dbo.table AS t
    USING #TempTable AS s
    ON (t.str1 = s.str1
    AND t.str2 = s.str2
    AND t.str3 = s.str3)
    WHEN MATCHED THEN
        UPDATE SET t.str1 = s.str1
    WHEN NOT MATCHED THEN
        INSERT (str1, str2, str3)
        VALUES (s.str1, s.str2, s.str3);

drop table #TempTable

I'm really confused as to why this isn't detecting duplicates, any ideas?

Thanks in advance


texts: Overhead associated with DBCC CHECKTABLE?

texts: Have a logshipped VLDB and don't run DBCC CHECKDB on primary due to the long running periods of high CPU and tempdb usage.   Have read about DBCC CHECKTABLE as a possibility for a more granular data integrity checking option.  Does DBCC CHECKTABLE have overhead levels similar to DBCC CHECKDB?  Can it be run during business hours without adversely affecting server CPU and TempDB performance?


texts: Transfer AD Logins between Azure SQL Managed Instances

texts:
I am trying to transfer all logins part of an Azure SQL MI to another server. Have re-created the SQL logins with the help of 'sp_help_revlogin' script.

Learnt that AD logins needs to be created manually again, when I do so I am getting the following error

create login "abcd@xyz.com" from external provider

error:
Principal 'abcd@xyz.com' could not be resolved. Error message: ''


texts:
How to uninstall  SQL Server Management Studio 2012?

ControlPanel's "Uninstall or change programs" (in Windows 7 Prof) gives a row of entries:

while internet search results always tell: "click Microsoft SQL Server 2005, and then click Change"

UPDATE (commenting Shawn Melton's answer):

Upon choosing " "Microsoft SQL Server 2012 (64-bit)"--> Uninstall/Change, I observe:

and after choosing "Remove", "Setup Support Rules" without any SSMS or continuation option:


texts: Database with replication and mirroring stuck in LOG_BACKUP after log file shrink

texts:
Need an advice from SQL Server Experts. I'm just a developer, not a DBA, so pardon my ignorance.

My environment consists of 2 equal instances (Node1 and Node2) of SQL Server 2008 R2 x64 (10.50.4339.0). I have 2 databases: DB1 and DB2 with Full recovery model. Both databases participate in replication between each other within single SQL instance and also both of them get mirrored from Node1 to Node2. These are not production databases and I did take full DB backups before doing any changes.

However, I have ended up in trouble: the log file is not growing for both DB1 and DB2 at Principal and the execution of:

select log_reuse_wait_desc, name, * from sys.databases where name='DB1' or name='DB2'

shows that both DB1 and DB2 have `log_reuse_wait_desc='LOG_BACKUP' even though I did run log backup manually.

Here is the sequence of events which caused the current state:

I needed to update DB1 and DB2 with year-worth set of changes which involved running many structure and data change scripts. Some of the data scripts were quite heavy, truncating tables and containing 70K+ INSERT calls. So, I've started applying changes at Principal (Node1) one by one in little chunks.
Node1 already had limited disk space available at partition with DB1 and DB2 data and log files.
When applying changes several times I've received log file full... message. So I've stopped and shrunk the log file of both DB1 and DB2 to 0Mb to free up some disk space to be able to finish the data/structure update task.
The log file of both DBs did get shrunk to minimal allowed however after I have continued with data changes I've got the following message:

  Msg 9002, Level 17, State 2, Line 1 The transaction log for database
  'DB2' is full. To find out why space in the log cannot be reused, see
  the log_reuse_wait_desc column in sys.databases

Sometime during running of data/structure change scripts the free disk space at relevant partition dropped to a few KBs and SQL Server sent out warning emails.

I did try to research how to get log_reuse_wait_desc column to show NOTHING. However, the suggested solutions prompted putting DB1 and DB2 into simple recovery mode or detach/re-attach them, something I cannot do because of the replication and mirroring.

I have also followed the advice from Paul S. Randal and have run log backup manually:

 BACKUP LOG DB2 TO DISK = 'z:\DB2.TRN'

which completed successfully, however the log_reuse_wait_desc still shows LOG_BACKUP and the log file is not growing.

DBCC LOGINFO Output:

DB1: 421 records like:

DB2: 381 records like:

Initial loginfo records for DB2: https://i.stack.imgur.com/TTsVu.png

Note, the Field column for DB1 contains values 2 and 4, while for DB2 Field column contains only value 2. The file size and offset fields looks scary: 54GB+? I may not have so much free space. Currently there is 26GB of free space on the drive and it's not decreasing. So seems SQL Server has hung.

Is there any way to fix the current DB1 and DB2 without disabling replication/mirroring followed by a restore from backup?

Any help is greatly appreciated.


texts: I tried creating an ERD model for hotel reservation and billing. Are the cardinalities correct, I'm a bit confused about that. And are there any attributes that are not in the right place? If someone could help me and go over this I would appreciate it. Thank you.


texts: Restore DB backup: schema node not found for object TABLE DATA

texts:
I made a backup from my db in postgresql via pgadmin. now I want to restore it in my pesonal laptop. but I get this error for each table in my DB:

schema node not found for object TABLE DATA


texts: How to normalize data based only on the repetitions

texts:
Given a non-normalized data table, is there a good a strategy to attempt to normalize based on repeated data?  Assume we have no information about the significance of the columns.

In a small example

Col1            Col2         Col3
========================================
Europe          France       Paris
Europe          France       Marseilles
Europe          England      London
Europe          England      Manchester
N.America       USA          St. Louis
N.America       USA          Washington D.C.

I can see that there are three source tables, probably called, Continents, Countries, and Cities.  What about for much larger data sets.  Say 45 columns and several hundred thousand rows.  Looking at the data is obviously not going to help much.


texts: What are charted collections in mongoDB?

texts: I'm watching an official mongo aggregation framework tutorial and it mentions that "$geoNear can be used on 'charted collections' where $near can't" (source). I have no idea what a charted collection is and googling "charted collections mongodb" gives no results.


texts: Best way to store a value which could be multiple types

texts:
I'd like to re-ask a question in a more direct and general way:

How do you create a table to store values which could be multiple different types?

In my case, the values provide diagnostics about an event. Ex: Event occurred -> Store readings from multiple PLC's which contain pertinent information about the event. The PLC's could monitor any type of data.

Some examples I can think of:

Create a column for every possible type and create another column to indicate which column to use

Ex: Cols: IntVal, StrVal, BoolVal, Type. Vals: null, null, True, "BOOL"

Store the values no matter what as a varchar


texts: Finding contiguous ranges in grouped data

texts:
I have a table that records the data of a car race with the following structure:

ID int, (id of car)
Duration timestamp, (time taken to drive)
Tour int,(no of laps made)
Pilot int,(drivers)

where Duration is in time and tour=no of laps which consists of values from 0 to 3000 and pilot has two values 2,3 (2 drivers with id 2 and 3)
The table shows the no of laps done within given time for each pilot.
The pilots drive the car within the time.Assume pilot 2 has drove the car first next pilot 3 and so on..

Now,I want total tour(difference) and total duree(difference) done by pilot having an id value 2 ?

SAMPLE DATA:

ID|DURATION|TOUR| PILOT
1 |15:00:01|216 |  2 \
2 |15:00:02|216 |  2 /   1 sec.
3 |15:00:03|217 |  7
4 |15:00:04|218 |  7
5 |15:00:05|219 |  7
6 |15:00:06|220 |  2 \
7 |15:00:07|221 |  2 /   1 sec.
8 |15:00:08|222 |  7
9 |15:00:09|222 |  7
10|15:00:10|223 |  2 \
11|15:00:11|224 |  2  |
12|15:00:12|225 |  2 /   2 sec.

Now I need the desired result:

For pilot id = 2, I need the tour difference along with duree(total)

Ans: 219 i.e 216 + (221-220) + (225-223)

First tour ID: 216 + (Dif of laps of each group) = 216 + 1 + 2 = 219

And duree(total) = Sum(Dif time of each group) = 00:00:04


texts: How to fetch the last updated time of a record?

texts:
I have a table with 5 columns in the following format:

| id | value1 | value2 | value3 | last_updated |
------------------------------------------------

The value in the columns value1, value2 and value3 can change or remain same. Those values comes from an external process, which makes a lot of entries in that table. It records the changes in the last_update column. The value of the value* columns can remain the same. I am trying to find the first event (last_update) time when any of the value1, 2, 3 is changed for first time.


texts: Postgres - Create table with foreign key constraint too slow

texts:
I have a table called "Account" that is a heavily used table, has 17 columns and has upwards of 300,000 rows. I am trying to create a new table, "NewTable", that has a foreign key constraint to "Account" table.

My CREATE TABLE statement looks like this:

CREATE TABLE "NewTable"
   (
       "column1" VARCHAR(100) NOT NULL,
       "column2" INTEGER NOT NULL,
       PRIMARY KEY("column1")
       CONSTRAINT "SomeFK" FOREIGN KEY ("column2") REFERENCES "Account" ("ID")
    )

The above statement executed for more than 25 minutes and didn't complete. We killed the transaction.

When we remove the foreign key constraint, it executes immediately.

Can someone let us know what is the problem? We initially thought it was locking the "Account" table for some reason, but when we looked at "pg_locks", we did not find any entry for "Account" - only an exclusive lock for "NewTable".


texts: Oracle AWS package errors when removing user

texts:
We are getting the folowing error when attempting to remove a user from our 11g Oracle. a dump from an Amazon DB was (failed) imported, and now we see this.

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-04045: errors during recompilation/revalidation of {USER}.RDSADMIN
ORA-04067: not executed, package body "{USER}.RDSADMIN" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"{USER}.RDSADMIN"
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called:
"{USER}.RDSADMIN"
ORA-06512: at line 2

How do we remove this {USER} and it's RDSADMIN packages?

Thanks in advance

C


texts: how to get last date when someone perform selection on table? Last accessed in terms of select statement only not last updated date


texts: Delete from GROUP BY with multiple GROUP columns

texts:
I have a simple table consisting of id, channel_id, value and timestamp:
CREATE TABLE `data` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`channel_id` int(11) DEFAULT NULL,
`timestamp` bigint(20) NOT NULL,
`value` float NOT NULL,
PRIMARY KEY (`id`),
KEY `IDX_ADF3F36372F5A1AA` (`channel_id`),
) ENGINE=InnoDB AUTO_INCREMENT=2126679551

The combination of channel_id,timestamp MUST be unique but the table has gotten corrupted. My ultimate goal is to add a unique index which fails due to the duplicates.
The duplicates are:
select channel_id,timestamp,count(*) cnt
from data
group by channel_id,timestamp having cnt>1

How can I delete the duplicates? I'm stuck since obviously I can't get the row id from the duplicates sub select?
UPDATE I could probably do
delete d1.* from data d1 join data d2 on d1.channel_id=d2.channel_id and d1.timestamp=d2.timestamp where d1.id != d2.id;

but since the table has almost 1 billion records a cross-product does not seem like a good idea?


texts: Convert datetime stored as char the YYYMMDDHHmmSS format to datetime

texts:
I'm working with a table where the date and time of an event is stored as a char in the format YYYMMDDHHmmSS or '20120606122012'. I need to compare this to the current timestamp to see if it's been more than 20 minutes since the event happened, but that seems to be more difficult than I anticipated.

Is it possible to do this, or if not: is there a different approach that works?


texts:
I try to deploy new version of existing SSIS project into Intergration Service

Catalog and getting error:

  "Failed to deploy the project. Try again later. (Microsoft SQL Server,
  Error: 27118)"

New .ispac file I have on file system (drive "D:\").
I tried:

deploy from SSMS
from command line
with Power Shall script

And I got the same error.

(SQL Server 2012)


texts:
I'm having problems with SQL Server 2012 filetable.

Filetable defines an check constraint on a path_locator field with definition as: /*ParentsAreDirectories*/ (filetable_parents_are_directories((1381579960),CONVERT([varbinary](892),[path_locator],0),[is_directory])=(1)). It seems that this check constraint calls some stored procedure or function, definition of which I was unable to found.

Then, sometimes I'm getting a deadlock on simultaneous INSERT query into filetable. I think, that deadlock is happens because check constraints of both transactions installs an S lock to filetable and then both transactions tries to install 'X' lock to insert a new row.

Basically my question is: In which order SQL Server installs locks caused by check constraints: before main query locks, or after?

And additional question - how to force installation of X lock in my scenario with filetable from very begin of transaction, if my interpretation of deadlock cause is correct?

UPD: deadlock report: https://gist.github.com/Snork2/9a4788dad1bc62736ae8


texts: Selecting from all databases from multiple tables

texts:
I've got over 2000 databases that may or may not have the same tables, but in the cases where they do, they should have the same type of information. From one table (settings) I'm trying to get the rows that have the names:

email
client_id
client_name

Then from a second table (Cluster) I'm trying to just add up the number of rows.

I'm stuck at even getting just the email for all of them. Trying to do the following with every single database listed, it tells me it can only join a certain number at once. I'm not opposed to running the command multiple times to eventually get the information, but then it tells me that "name" is too ambiguous.

SELECT *
FROM  `database1`.`settings`,
`database2`.`settings`,
`database3`.`settings`,
`database4`.`settings`
WHERE (
CONVERT(  `name`
USING utf8 ) LIKE  '%email%'
)
LIMIT 0 , 30

Ideally, I want to be able to have email, client_id, client_name, and the Cluster row totals associated so I can import it into a single database later.

Any help is appreciated!


texts: What do the values after `execute fetch from` mean in postgresql query logs?

texts:
We've turned on some slow query logging on a postgres 9.1 cluster and see
the following in the logs::

2016-08-08 13:05:51 AEST LOG:  duration: 24126.105 ms  execute fetch from S_26/C_775: SELECT ...(query continues)

What does the S_26/C_775 bit mean? Can I use this to find which client submitted the query?


texts: Filtering Escape Characters in PostgreSQL

texts:
I am trying to filter distinct serial number from a table and join two tables. Current my code looks like
SELECT DISTINCT ON (t1.serial_nbr) t1.*, q1.serl_nbr
FROM ( select
                 serial_nbr,
                 CASE
                 when substring(serial_nbr FROM 2 for 2) = '18' THEN
                 regexp_replace(serial_nbr,'(...)(\w{{5}})','\\1 \\2')
                 when substring(serial_nbr FROM 2 for 2) = '19' AND length(serial_nbr) = 9 THEN
                 regexp_replace(serial_nbr,'(...)(\w{{6}})','\\1 \\2')
                 ELSE serial_nbr END as serial
                 FROM controller_returns) as t1
LEFT JOIN dblink('{param_db}',
            'SELECT DISTINCT ctrlr_serl_nbr,insert_ts
            FROM ctrlr_basic_setng')
as q1(ctrlr_serl character varying,insert_ts timestamp without time zone)
on q1.ctrlr_serl_nbr = t1.serial
WHERE t1.serial_nbr~'^[ABCD]([AC0-9]{{1,9}})'
ORDER by t1.serial_nbr, substring(t1.serial_nbr from 2 for 2), substring(t1.serial_nbr from 1 for 1)

I am using postgreSQL and connecting through using psycopg2 in python.
This is evolving code but I been noticing that the serial # that I am getting aren't truly distinct. I would sometimes get case like this 'A1800038' and 'A1800038\n'. What I am trying to figure out is is how to filter out the serial with \n. I was trying to do it in the WHERE clause doing '^[ABCD]([AC0-9][^\\n]{{1,9}})' or '^[ABCD]([AC0-9][^[:space:]]{{1,9}})' but neither work.
Any help would be much appreciated.


texts: Bulk DELETEs in publisher breaking SQL Server Replication in subscriber

texts:
We have SQL Server replication set up between 2 databases running SQL Server 2019.  Every night, a SQL Agent job runs on the publisher that deletes all the data from a table and then rebuilds the table with new data. However, for some reason, this seems to cause our replication to break on the subscriber with errors such as this:
The row was not found at the Subscriber when applying the replicated DELETE command for Table '[dbo].TABLENAME' with Primary Key(s): [Id] = 240843 (Source: MSSQLServer, Error number: 20598)

As a result, I need to fix this by manually 'filling in' the missing rows, so they can be deleted by the subscriber.  Is there any way to fix this, or are bulk deletes like this something that isn't supported, or best practice with SQL Replication?


texts: SSIS packages running forever without completing. OLEDB Source query suspended

texts:
We have several packages that will intermittently run until they are killed.

When looking at the Activity Monitor we can see the source query for one of the data flows is in a suspended state with an ASYNC_NETWORK_IO wait. The task will remain in the state until the process is killed.

This has been happening to several different packages on several different servers. The query that becomes suspended is always a really simple query that will run in under a second if run in SSMS.

There are 2 queries we see that get suspended often. One is an inner join from one table in a database to a table in another database. It returns ~500 rows with ~10 columns in under a second usually. The second one is just Select * from a table with ~100,000 rows.

I have been reading up  on what the ASYNC_NETWORK_IO means and it looks like it is typically caused by the client application not signalling to SQL that it has consumed the data.

I have also read that it could be a resource/NIC issue but that seems unlikely to me. Some of these servers are overspec'd and the package is running on the server that holds all the data it manipulates. There should be no need for any network usage.

How can I ensure that my packages will not be suspended indefinitely?

EDIT:

The two servers that have had this issue recently have both been SQL Server 2014 Standard Edition. Version 12.0.5605.1 and Version 12.0.4213.0 .

EDIT2:
I am pretty sure the cause is back pressure caused by a whole bunch of expensive transformations. The package has around 10 Oledb commands and a few slowly changing dimensions. I think It just needs to be re-written.


texts:
I'm having a recurring issue with a MySQL database where one index in particular has been getting corrupted. Originally I saw a corruption event about every 2-3 weeks and this week I've seen it happen twice. The corruption also only happens on one index and the data itself seems fine.

The composite index in question spans two BigInt foreign key ID Columns on a table with 5M rows. I'm using InnoDB for all tables and running MySQL 5.6.23 on Amazon RDS. I looked to see if I could find anything in mysql-error but haven't found any entries in it.

To verify that the index corrupted, I run the following EXPLAIN:

mysql> explain SELECT * FROM  student INNER JOIN school ON school.id = student.school_id WHERE student.student_status_id IN (3, 4, 7) ORDER BY student.id desc LIMIT 0, 25;

+----+-------------+------------+------+-----------------------------------------------------------------+---------------------------+---------+-----------------------+------+---------------------------------+
| id | select_type | table      | type | possible_keys                                                   | key                       | key_len | ref                   | rows | Extra                           |
+----+-------------+------------+------+-----------------------------------------------------------------+---------------------------+---------+-----------------------+------+---------------------------------+
|  1 | SIMPLE      | school     | ALL  | PRIMARY                                                         | NULL                      | NULL    | NULL                  | 3690 | Using temporary; Using filesort |
|  1 | SIMPLE      | student    | ref  | student_status_id_idx,school_id_idx,schoolStatusIndex_idx       | schoolStatusIndex_idx     | 8       | school.id             |  178 | Using index condition           |
+----+-------------+------------+------+-----------------------------------------------------------------+---------------------------+---------+-----------------------+------+————————————————+

When it’s “fixed” the rows are roughly 48/1 instead of 3690/178

To fix it, it requires some combination of these 3 steps:

alter table student drop KEY schoolStatusIndex_idx; alter table student add key schoolStatusIndex_idx (school_id,student_status_id);
alter table school engine=innodb;  (rebuilds all indexes)
alter table student engine=innodb;

Still, this has me baffled. What could be causing this kind of index corruption and how can I prevent it from happening moving forward?

Here's the trimmed output of SHOW CREATE TABLE for student, student_status, and school:

CREATE TABLE `student_status` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `code` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `school_id` bigint(20) NOT NULL,
  `phone` varchar(10) NOT NULL,
  `student_status_id` bigint(20) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `student_status_id_idx` (`student_status_id`),
  KEY `school_id_idx` (`school_id`),
  KEY `schoolStatusIndex_idx` (`school_id`,`student_status_id`),
  CONSTRAINT `student_student_status_id_student_status_id` FOREIGN KEY (`student_status_id`) REFERENCES `student_status` (`id`),
  CONSTRAINT `student_school_id_school_id` FOREIGN KEY (`school_id`) REFERENCES `school` (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=2582686 DEFAULT CHARSET=latin1;

CREATE TABLE `school` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=4350 DEFAULT CHARSET=latin1;

And some additional output that might be helpful:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 5753536512 |
+-------------------------+------------+


texts: WHILE Loop for data migration/merge with multiple fields for matching

texts:
Ok so I have two identical databases in two parts of the world. I need to merge everything into the one I will call North (the other being South). North data trumps South data. I started thinking about a WHILE loop but am not sure that is the best/easiest way to go about this. My plan was to restore a copy of the South database onto the Instance where North lives and then do my work.

The table in question has ID, FirstName, LastName, Email and Login fields. And my rules are:

Any Login fields that exist in both are logged to a logging table with a status field saying it was not migrated and another for why it did not (because has identical Login).
If the Login field is NULL in South or does not match with that field in North we must check for a match on the Email field (logging similarly as above if we find a match).
If neither Login nor Email match the final attempt is FirstName + LastName (has to be both) and logged as above.
Any Login fields that don't exist in North (destination) but do in South (source) are logged in that same logging table and then inserted into the North table.

So my initial setup was something like this (which pretty sure I screwed up the Loop):

DECLARE @TempID INT
DECLARE @TempCount INT

SET @TempID = 0
SET @TempCount = 1

WHILE @TempCount = 1
BEGIN

DECLARE @SourceID nvarchar(255)
DECLARE @SourceLogin nvarchar(255)
DECLARE @SourceFirstName nvarchar(255)
DECLARE @SourceLastName nvarchar(255)
DECLARE @SourceEmail nvarchar(255)

DECLARE @TargetID nvarchar(255)
DECLARE @TargetLogin nvarchar(255)
DECLARE @TargetFirstName nvarchar(255)
DECLARE @TargetLastName nvarchar(255)
DECLARE @TargetEmail nvarchar(255)

SET @SourceID = (SELECT TOP 1 id FROM #TempExistence);
SET @SourceLogin = (SELECT TOP 1 Login FROM #TempExistence);
SET @SourceFirstName = (SELECT TOP 1 firstname FROM #TempExistence);
SET @SourceLastName = (SELECT TOP 1 lastname FROM #TempExistence);
SET @SourceEmail = (SELECT TOP 1 email FROM #TempExistence);

SET @TargetID = @SourceID (SELECT TOP 1 id FROM [North].dbo.PersonID]);
SET @TargetLogin = @SourceLogin (SELECT TOP 1 Login FROM #TempExistence);
SET @TargetFirstName = @SourceFirstName (SELECT TOP 1 firstname FROM #TempExistence);
SET @TargetLastName = @SourceLastName (SELECT TOP 1 lastname FROM #TempExistence);
SET @TargetEmail = @SourceEmail (SELECT TOP 1 Email FROM #TempExistence);

    INSERT INTO [MigratePersonLog]

    (
    [SourceWfoPersonID]
    ,[SourceFirstName]
    ,[SourceLastName]
    ,[SourceEmail]
    ,[SourceLogin]

    ,[TargetWfoPersonID]
    ,[TargetFirstName]
    ,[TargetLastName]
    ,[TargetEmail]
    ,[TargetLogin]
    ,[MatchStatus] --Matched or not
    ,[MatchArtifact] --What was matched or not matched
    )

VALUES
    (
    @SourceID
    ,@SourceFirstName
    ,@SourceLastName
    ,@SourceEmail
    ,@SourceLogin

    ,@TargetID
    ,@TargetFirstName
    ,@TargetLastName
    ,@TargetEmail
    ,@TargetLogin
    ,1
    ,'NONE'
    )

    --WHERE ID > @TempID
    --ORDER BY ID

    IF @@ROWCOUNT > 0
        SELECT @TempID = MAX(ID)
        FROM #TempExistence
    ELSE
        SET @TempCount = 0
END

Not sure this is the right path to take or if I am doing something wrong/less-optimal for the Loop stuff. Thanks in advance for any help!


texts: Getting column information from the MySQL command line

texts:
I am trying to get all the columns from MySQL using bash scripting. I want them to be stored in an array. If I write it this way:

mysql -uroot -pPassword1 "select column_name
    from information_schema.columns
    where table_schema = 'dbName'
    and table_name = 'tableName';"

...I get an error that the identifier name is too long. If I write this way:

mysql -uroot -pPassword1 <<- SMTH
    select column_name
    from information_schema.columns
    where table_schema = 'dbName'
    and table_name = 'tableName';
SMTH

everything works fine. But I can't output it to a variable. Any suggestions?


texts: Can sql server 2000 and 2012 coexist on the same server?

texts: I see that 2005/2008/2012 can all coexist but can't find anything on 2000 with 2012.


texts: Database Table Schema Circular Reference issue

texts:
Trying to fix a Circular Reference in a schema created by a developer, but having a hard time.  This is as far as I have gotten. The black line is the tables that are needed to create a file.  The red lines are a second path to the same data.  They are over rides for each of the tables.  The Developer needs a relationship to the CaseFileDefinition and the table field that is being over ridden so the processor code knows which over ride goes to which client when processing files.

My question is, because I set the Over Ride tables as many to many, am I avoiding circular logic? Is there a better way to model over rides to a table?


texts:
Let's assume that you have a table that partitions on CATEGORY and SUBCATEGORY. Also assume that every subcategory can be mapped to a category, i.e. subcategory are proper subsets of categories.

Let's also say that for my query, I know I am only interested in a subcategory of 'A', and that I know that everything with a subcategory of A has a category of A.

Which of these should I go with this:

Select blah from blahblah where subcategory = 'A'

Select blah from blahblah where subcategory = 'A' and category = 'A';

Which is to say, is the second faster?


texts:
In  SQL Server 2012 memory for backup buffer comes from buffer pool or non buffer pool (VAS)? Is this behavior changed between SQL server 2008 R2 and SQL 2012?

EDIT: Does MAX Server Memory configuration setting now controls memory for backup buffer? Historically, this was coming out of MemToLeave (or Virtual Address Space) outside SQL Server buffer pool?


texts: FDW Permissions error using MD5 encrypted password

texts:
When creating Foreign Data Wrappers in Postgres 9.6, if I use the following:

CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', dbname 'database', port '5432');

-- permissions
ALTER SERVER foreign_server OWNER TO data_owner;

-- Allow a user to access the foreign server if required
GRANT USAGE ON FOREIGN SERVER foreign_server to data_owner;

-- 4. Create foreign user mapping - using md5 encrypted password
CREATE USER MAPPING FOR data_owner SERVER foreign_server
OPTIONS ( USER 'jeff', PASSWORD 'md5de0366066f8d96ac5bb4872b1d77b0cb!');

-- 5. Create foreign table
IMPORT FOREIGN SCHEMA foo EXCEPT
(bar )
FROM SERVER foreign_server INTO foreign_tables_schema;

Why do I get an error, when I had the password as plain text everything is fine, but when I use the encrypted md5 password I cannot import the foreign tables.

Any thoughts?


texts: Why is this filtered index not only not used but errors when I try to force it

texts:
I'm trying to tune a rather annoying query where a number of the tables have something that looks like:

date = '9999-12-31 23:59:59.9999999'

At least a few of the tables are in the 300 mil row range and when I filter this I end up in the 2 mil range.  It seemed reasonable to attempt a filtered index.

CREATE TABLE test (col1 int PRIMARY KEY, col2 int, col3 varchar(50), col4 datetime2(7), col5 int);
CREATE INDEX ix_filtered ON test(col2,col3) INCLUDE (col4)
       WHERE col4 = '9999-12-31 23:59:59.9999999' ;
GO

SELECT col2,col3 FROM test
WHERE col4 = '9999-12-31 23:59:59.9999999';
GO

However when I checked the query plan it wouldn't use the index.  It just did a clustered index scan and a key lookup.  Obviously without any data this makes sense but even with all of my data it did the same thing.  The reason I haven't bothered supplying any data is this second problem that occurs regardless.

When I tried to force the index to see what the query plan would look like:

SELECT col2,col3 FROM test
WITH (index (ix_filtered))
WHERE col4 = '9999-12-31 23:59:59.9999999'

I get this error:

  Msg 8622, Level 16, State 1, Line 52 Query processor could not produce
  a query plan because of the hints defined in this query. Resubmit the
  query without specifying any hints and without using SET FORCEPLAN.

I'm trying to figure out why I'm getting the error with the query hint.  My guess is that the answer to this will also tell me why the filtered index isn't being used at all.


texts: Copy database removed source - Cannot acces mdf file

texts:
I attempted to copy a database, using Microsoft SQL Server Management Studio (SSMS), but right-clicking and following the wizard. I wanted to target server to be the same as the source, so I added "_new" to the database name.

The process failed (unfortunately I missed the error message) and now the source database is missing as well. I found another post on the same topic, which suggests locating and atttaching the .mdf file.

However, when I try to attach the mdf file, I get the following error meesage:

  Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

Luckily this was just a test database, so I didn't lose any data, that couldn't be easily reproduced. But is there a way to recover from this? If it ever happened on a production database, I would like to be prepared.


texts: Can this query be simplified? Calculate cumulative length of segments and collapse coordinates into linestring

texts:
I have a road_vertices table:

create table road_vertices
(
road_id number,
vertex_index number,
x number,
y number
);

insert into road_vertices values ('100',1,0,5);
insert into road_vertices values ('100',2,10,10);
insert into road_vertices values ('100',3,30,0);
insert into road_vertices values ('100',4,50,10);
insert into road_vertices values ('100',5,60,10);

select * from road_vertices;

   ROAD_ID VERTEX_INDEX             X          Y
---------- --------------- ---------- ----------
       100 1                        0          5
       100 2                       10         10
       100 3                       30          0
       100 4                       50         10
       100 5                       60         10

I need to:

Calculate the cumulative length of line segments (as shown in the grey text in the image above).
Collapse the coordinates and cumulative lengths into a linestring.

This is the end-goal:

ROAD_ID     LINESTRING
----------------------------------------------------------------------------
100         LINESTRING M ( 0 5 0, 10 10 11.18, 30 0 33.54, 50 10 55.9, 60 10 65.9)

I've figured out a way to do it:

--Step #3: Collapse the coordinates and cumulative lengths into a linestring
SELECT
    ROAD_ID,
    'LINESTRING M ( ' || LISTAGG(CUMULATIVE_LENGTH, ', ')
        WITHIN GROUP (ORDER BY VERTEX_INDEX) || ')'  AS LINESTRING
FROM
    (
    --Step #2: Calculate each line segment's length using the Pythagorean theorem, and add together to get cumulative length
    SELECT
        ROAD_ID,
        VERTEX_INDEX,
        X || ' '  || Y || ' ' || ROUND(SUM(NVL(SQRT(POWER((X - PREV_X),2) + POWER((Y - PREV_Y),2)),0))
            OVER (PARTITION BY ROAD_ID ORDER BY ROAD_ID,VERTEX_INDEX),2)
            AS CUMULATIVE_LENGTH
    FROM
        (
        --Step #1: Get the previous X and previous Y for Step #2's Pythagorean theorem calculation
        SELECT
            ROAD_ID,
            VERTEX_INDEX,
            ROUND(X,2) AS X,
            ROUND(Y,2) AS Y,
            LAG (X,1) OVER (PARTITION BY ROAD_ID ORDER BY VERTEX_INDEX) AS PREV_X,
            LAG (Y,1) OVER (PARTITION BY ROAD_ID ORDER BY VERTEX_INDEX) AS PREV_Y
        FROM
            INFRASTR.ROAD_VERTICES
        )
    )
GROUP BY
    ROAD_ID;

However, this solution is quite complicated. Can it simplified/improved?


texts: How to find values from one table missing in another?

texts:
I am a Junior Developer. I have been asked to create a subquery to solve this problem. If the question is not clear then please let me know so I can provide more details.

I have two tables,Article and Author. I have been tasked with doing the following:

  Select distinct Author values that don't exist in the Author table. The result should not contain any Authors that are NULL or Empty String. The purpose is to find any Author names that ARE in the Article table but NOT in the Author table.

I have Author column in both Tables. Can this problem be solved by joining these two tables?

I have tried

select distinct name
from author
where name NOT IN ( select author
                    from article
                    where author IS NOT NULL)

It doesn't give me the result that I want :(


texts: Config enable sharding collection in MongoDB cluster

texts:
I'm a new in MongoDB cluster.   I have a question about config enable shading collection in MongoDB:
I have a cluster :
Shard1: replical01/node1:27017, node2:27017
Shard2: replical02/node3:27017, node4:27017
2 config Server on other virtual machine.
Everything worked normally under manual. But I have a question as follows: how can I just shading a collection for only one shard. For example, I used mongos to create a database Test  with 3 collection T1, T2, T3.
Which T1 only located on shard1, T2 only on shard2. T3 is also located on the both shard1 and shard2.
Please help me. Thank you and regards!!


texts: How to Represent a VARCHAR Column with XML Data in a Fact Table?

texts:
I have SQL Server 2016 environment and I have decided to design a source table with holds some sort of event data as a transactional fact table. The table has about a million rows, and generates about another million per year, for now. One of the columns in the source table is VARCHAR(MAX) and almost all of the values in this column are unique. My questions here are:

Is the best approach here to store this table as fact table in the first place? Could it be modeled as a dim table?
Should I store the column as it is in the fact table?
Should I try to decompose the elements of the XML data (there are about 10 elements) and add them as attributes in a dim table? There could be a problem with this approach if more elements are added into the source table in the future.


texts: PostgreSQL 9.3 - standard_confirming_strings option in postgresql.conf not affecting remote machine

texts:
I am updating a program at work that communicates with a postgreSQL 9.3 database. I need to turn off an option in the postgresql.conf file called standard_conforming_strings, so that I can store backslashes properly in the database.

During installation, I turn off the standard_conforming_strings option in postgresql.conf automatically on the server machine. However, client installations of this program simply consist of shortcuts that point to the executable on the server machine. The client machines don't have postgres installed at all.

If data containing a backslash is stored on the server machine, the data saves correctly, meaning the standard_conforming_strings option was successfully turned off. However, when information is saved from a client machine, backslashes are stored incorrectly (as '\134' instead of '\').

Only the server machine has postgres installed, so it is the only machine with a postgresql.conf file where the standard_conforming_strings option can be turned on or off. I know this option is being disabled correctly on the server machine, but for some reason, when data is saved on the client machine, backslashes are not saved correctly, even though the data is being saved to the same database.

If both these computers are saving to the same database, they should refer to the same postgresql.conf file, shouldn't they? Data should be stored in the same manner on both machines. What is going wrong here?

Any help or suggestions would be greatly appreciated!

Edit: As requested, I've enabled logging of statements on postgres and this is what showed up in the log file when I tried to save data containing backslashes:

2014-06-02 13:44:01 PDT LOG:  statement: SELECT * FROM tblapp;
2014-06-02 13:44:38 PDT WARNING:  nonstandard use of escape in a string literal at character 135
2014-06-02 13:44:38 PDT HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
2014-06-02 13:44:38 PDT LOG:  statement: BEGIN;DELETE FROM tblapp WHERE app_key='ClientApp_ArchivePath';INSERT INTO tblapp (app_key,app_value) VALUES ('ClientApp_ArchivePath','C:\134Users\134Stephane\134Documents\134Archive');
2014-06-02 13:44:38 PDT LOG:  statement: COMMIT

The data shows up like this in pgAdmin:

ClientApp_ArchivePath | C:\Users\Stephane\Documents\Archive

Note: I did this on a machine that is saving backslashes correctly. I do not currently have access to a machine that is saving them incorrectly, but hopefully I will soon and I will go through the same process and post the results here.


texts: Where .dmp file is restored after import in oracle?

texts:
I am new to oracle and I have abc.dmp file at c:\abc.dmp location. I want to import this abc.dmp file. I am using the following command.

c:\>imp system/mypassword file=c:\abc.dmp full=y

I am using Toad for Oracle 12.1 Version. Now after import has been done I don't know where this db has been restored.

I have also created a new user and used the following command.

c:\>imp system/mypassword file=c:\abc.dmp fromuser=system touser=testuser2014.

After this commend has been executed, I don't know where the db is restored.

Please help me.

Thanks in advance


texts: When setting up an AlwaysOn Availability Group, should the master database be one of the "Availability Databases"?

texts:
I just got a Sql Server 2016 Availability Group setup (A Primary, a Syncronous Secondary and a Async Secondary).  I have five databases on the servers.

I setup an application to run queries against the server (using Entity Framework with a "failover partner" connection string.)

When I tried to test the failover by rebooting the primary server, I got the following error:

  Server MySecondaryServer, database master is not configured for database mirroring.

It seems odd to mirror a system database like this, so we did not have it setup as one of the "Availability Databases".

So, here is my question: Should I add the master database to the list of "Availability Databases" and will it cause problems if I do it?


texts: XML Network Call to Application from SQL Server 2005

texts:
We don't really have a DBA here, just a semi-experienced SQL Developer and me. I've only got about a year's experience myself and we're the only programmers for our small business.

We're trying to integrate a ticketing system in our billing software with a set of stored procedures and jobs that will help us automatically determine daily whether or not we need to change the status of an account so our Tech Support department knows to look into it.

The other developer asked me to look into SSIS as a way of exporting an XML file with calls to the billing software, but I'm not convinced we're on the right track. The API for the billing software does support XML calls, but we'd need to be able to make a network call from SQL Server to the proper port for the API. Both are on the same server and have tables which exist within the same database. We're trying to go through the API because we're concerned about allowing the primary database to make changes to the extremely convoluted billing database, as it could cause unforeseen problems and would not update the billing software's internal tracking system properly.

Is there a way to make the XML API network call from SQL Server? If not, is there a workaround that might allow us to go through the API like this?

The billing software is called Platypus by TuCows, though I doubt anyone's familiar with it and this server is running SQL Server 2005 for the moment, though we'll have 2012 implemented in another month or so.

Thanks so much for your time!


texts: postgres deadlock without explicit locking

texts: I use PostgreSQL 9.2, and I do not use explicit locking anywhere, neither LOCK statement nor SELECT ... FOR UPDATE. However, recently I got ERROR: 40P01: deadlock detected. The query where deadlock was detected is wrapped in transaction block though. Anyway, how comes it?


texts: Using partitioned table, and dealing with auto_increment PK

texts:
I have a big table (>100 million rows) that tracks user's activities. It has an ID, which is PK and has auto_increment property. This is the only unique key. Table also has userid and datetime column.

I want to partition the table by datetime, but mysql mandates that

every unique key on the table must use every column in the table's partitioning expression

So, that means I have to make PK consisting of (ID, datetime).

My concern is, ID column is no longer a unique entity. While the ID is always created automatically, there's no prevention against a bad code generating ID of its own and mess up the uniqueness of the column.

What is the common practice to rectify this situation?

Thanks for your time.


texts:
I have a schema snow within a database testdb. I'm trying to achieve column level security for my users. My users are boss, manager and intern. I set all of this table up as the superuser me. They are all in the group company.

The table I have is this: snow.company_table

 property_id |     property_address     |     city     | zip_code | state
-------------+--------------------------+--------------+----------+-------
           1 | 2564 Wescam Court        | Reno         |    89511 | NV
           2 | 1732 Rubaiyat Road       | Grand Rapids |    49603 | MI
           3 | 4094 Francis Mine        | Standish     |    96128 | CA
           4 | 3193 Ashton Lane         | Briggs       |    78608 | TX
           5 | 1519 North Street        | Alamosa      |    81102 | CO

I created the roles like this where boss, manager and intern are all within company.

CREATE ROLE company WITH LOGIN CONNECTION LIMIT 0;
CREATE ROLE boss WITH LOGIN CONNECTION LIMIT 1 PASSWORD '1234' IN ROLE company;
CREATE ROLE manager WITH LOGIN CONNECTION LIMIT 1 PASSWORD '1234' IN ROLE company;
CREATE ROLE intern WITH LOGIN CONNECTION LIMIT 1 PASSWORD '1234 IN ROLE company;

I would like for boss to have access to be able to select all columns, manager to only select property_id, zipcode and state, and the intern to only select property_id and state. I used these commands to create the roles and their grants: To grant the column privileges, I revoked all first, then granted them back.

REVOKE ALL ON snow.property_table FROM snow; --remove all privileges automatically assigned to everybody
GRANT ALL ON snow.company_table TO boss; --boss has the ability to do anything to this table
GRANT SELECT( property_id, zip_code, state) ON snow.company_table TO manager;
GRANT SELECT( property_id, state) ON snow.company_table TO intern;

for more information, running \dn+ gives the results:

  Name  |    Owner     |      Access privileges       |
--------+--------------+------------------------------
 snow   |     me       | me=UC/me                    +|
        |              | boss=U/me                   +|
        |              | company=U/me                +|
        |              | manager=U/me                +|
        |              | intern=U/me                  |

My problem is: when I connect to a user (ex. intern), I don't have permission to access the table shown above. I still want to keep the column permission, but I only want my users to see the columns I have specified without giving them full permissions on the whole company_table.

Here is the error:

STATEMENT:  select * from snow.company_table;
ERROR:  permission denied for table company_table


texts: sp_primary_keys_rowset is consuming a lot of CPU

texts:
I manage a database server that supports a C#/VB.NET application and we've had a lot of CPU usage problems.  I am using the following dmv query from Glenn Berry at SQLSkills:

SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name],
REPLACE(REPLACE(LEFT(t.[text], 255), CHAR(10),''), CHAR(13),'') AS [Short Query Text],
qs.total_worker_time AS [Total Worker Time], qs.min_worker_time AS [Min Worker Time],
qs.total_worker_time/qs.execution_count AS [Avg Worker Time],
qs.max_worker_time AS [Max Worker Time],
qs.min_elapsed_time AS [Min Elapsed Time],
qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],
qs.max_elapsed_time AS [Max Elapsed Time],
qs.min_logical_reads AS [Min Logical Reads],
qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
qs.max_logical_reads AS [Max Logical Reads],
qs.execution_count AS [Execution Count], qs.creation_time AS [Creation Time]
--,t.[text] AS [Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

and by a wide margin, the top 5 queries are all this sys.sp_primary_key_rowset procedure.  It looks like it's not documented in SQL Server's documentation, but some research tells me that it might have something to do with ADO and the application needing to find out more info about the table before running a query?  I can't seem to find any good info on this proc.

What causes this, how can I determine what queries are causing it, and most importantly, can we make it run less often?  It doesn't look like it's that expensive when it runs, only 100ms on average, but it's running 120+ times a minute.


texts:
I need to tune my postgres db to speed up delete queries. I cant't optimize my db structure yet, so I'm looking for options available on server level for postgres 9.1.

I've seen in older posts the parameter max_fsm_pages, however I can't find the parameter in my postgressql.conf, is this parameter deprecated?

Are there other parameters I can tune?


texts: Query to get reposts from people the user is following

texts:
I have a table posts where all the posts by users are stored, the structure of this table is as follows.

| post_id | post_user | post_content | post_date |

The users table is as follows.

| user_id | username | user_joined |

User relationship table is as follows.

| follower | following | rel_date |

This is the query I am using to get the posts from people that user is following to show them.

SELECT * FROM posts
WHERE post_user in (
    SELECT follower
    from user_follow
    where following=$loggedin_user)

Now I want users to share posts, for which I created a table repost_user as follows.

| repost_user | original_post_user | post_id | repost_date |


texts: How to PUSH data between a Local DB Server to Cloud DB Server?

texts:
I have an implementation doubt regarding replicating data between SQL Server 2014 database servers (Replicating data from Local Premises SQL Server to Cloud DB Server, which are not in same domain or VPN).

May I request you to please advise a solution, to achieve this data transfer push from Local DB Server to Cloud DB Server.

I will elaborate the scenario over here, what we are trying to achieve.

SCENARIO

My client has an ETL application developed in .NET which uses SQL Server as a backend.

They have their Live SQL Server hosted on a cloud provider (not Microsoft Azure)

They are opening up a new branch which does not have a leased line, since the area is on outskirts of the city. This new branch will have almost 1500 users who will make entries to a locally hosted application which will use local DB Server.

They have 2 SQL Servers:

SQL Server 2014 Web Edition Database on Cloud (not Azure)
SQL Server 2014 Standard Edition with same Application database on Local Premises

They need to push the data entries inserted in local DB Server Database named as LabProdData to the Cloud DB Server Database LabProdData, so all other branches can refer to this branch data, with a certain delay of 30 minutes or less.

What are the options to implement this scenario?

SOLUTION PROPOSED

I have proposed a solution of ETL creation to open up a SQL Port on the Cloud DB server to facilitate communication to Local DB Server and to push data to the Cloud DB Server
But, this will be quite hectic to develop multiple ETL's since the number of tables are more.
Also, the Deletion and Updation of Data needs to be tracked and Synced, which will add up complexity

My client is bit hesitant about opening the port on Cloud DB Server, since this can be vulnerable to threats.

REQUEST SOLUTION

My doubts are:

Can we use SQL Server Replication to replicate the data between Local SQL Server and Cloud DB Server, with no Active Directory or no common domain?
If yes, how do we implement this scenario using SQL Server Replication?
Is there any other way to implement this scenario, other than Replication? Can you please suggest.
Can Replication or Database Mirroring help here
Can SQL Server Replication support this with 2 DB Servers of same version but one is Web Edition and other is Standard. We need to push data from Standard Edition to Web Edition.
How can the communication be implemented between both these servers? Note: Network Domain or Active Directory do not exists.

Please do let me know if you need any further details to understand the problem statement.
Eagerly awaiting for your reply.


texts: Group by every N records but remove incomplete group that has records less than N

texts:
I need to query a table to fetch data in groups of some size say N=5 but if the size is less than 5 then the last group should be ignored
i.e every 5 records should be grouped

==> i.e first 5 records form first group
==> next 5 records form second group
==> next 5 records form third group

....
What I have tried
I have tried the below query. It works but it fetches all the data.
My Requirement
I need to strip off the last group if it does not have 5 records there.
in below query groupSize is the size of group
WITH T AS (
  SELECT RANK() OVER (ORDER BY dateReceived ) Rank,
    p.data
  FROM parameterData p where parameterID=@parameterID
        and isActive=1
)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS GroupID, (Rank - 1) / @groupSize GroupSize, AVG(data) average, MIN(data) min,MAX(data) max,MAX(data)-MIN(data) range
FROM T
GROUP BY ((Rank - 1) / @groupSize);


texts: Should I enable binlog binary logging on slave server?

texts: What are the benefits of enabling binary logging in MySQL slave server? I'm thinking that doing a switchover from master to slave and back to master would be easier in this way?


texts: Entries disappeared, auto_increment also reversed, web app security vulnerability or DB Error?

texts:
I am using Mediatemple's Grid Hosting (GS) platform to host a wordpress site. The DB tables are using the MyISAM engine.

I had noticed that a previous article had disappeared from the site. At first I thought it was a user mistake so I took a look at the DB backup that I had made soon after the article was posted and I noticed that the article had an ID of 11300 (auto_increment) and there were autosaves that had an ID all the way to 11307. When I checked the database, the ID of 11300 was occupied by a newer post which means that the value got re-set.

I did a diff of the database dump between the one taken just before the article disappeared and today. There was no content change other than the standard for some new posts and comments being added.

Mediatemple says it is a security vulnerability and that there is nothing wrong on their end. They suggested that I imported data (which I definitely didn't do) or a 3rd party doing it without my knowledge. But why would anyone go through the trouble of re-importing the database without adding any exploits or backdoors?

So my question is, is there any other possible explanation for this? Botched replication or some other oddity? I am not so much concerned about getting my data back but very concerned about future losses.


texts: how do you change directory where an exported dump file is written to in oracle 11g?

texts:
According to this site you can use the "exp" to generate a dump file (something like exp.dat) for a database.

However, I want to specify the exact location of where this dump file should be written to. Something like:

/my/preferred/directory/exp.dat

How do I tell "exp" command to use a specifc directory to write the dump file to?


texts: nvarchar(max) in Included Columns of NC index

texts:
Would adding nvarchar(max) to the included columns of an NC index that has one nchar(16) column key column cause rebuild issues with 20+ million rows?

By "rebuild issues," I mean taking a very long time to rebuild the index. I thought that the "included" columns were treated differently from key columns and would not have as big of an impact on the cluster.


texts: What happens when Core Cluster Group is failed over to another node? Will it take the whole cluster down? Single point of failure?

texts: "Cluster group" fail-over will cause cluster go offline. Really? Single point of failure? Am I missing something? What happens when Core Cluster resources (Cluster Group) are failed over from one node to another? Will it take whole cluster down? Thanks


texts: Truncation vs Deletion When `session_replication_role = replica`

texts:
When I first run

SET session_replication_role = DEFAULT;

CREATE TABLE users (
    id serial PRIMARY KEY
);

CREATE TABLE posts(
    id serial PRIMARY KEY,
    user_id INTEGER REFERENCES users(id)
);

And then run

INSERT INTO users(id) VALUES (1);
INSERT INTO posts(id, user_id) VALUES (1, 1);

SET session_replication_role = replica;

DELETE FROM users;

It successfully deletes the row in users even though this violates referential integrity because the session_replication_role = replica. So far so good.

However, when I instead run

INSERT INTO users(id) VALUES (1);
INSERT INTO posts(id, user_id) VALUES (1, 1);

SET session_replication_role = replica;

TRUNCATE users;

I get this output:

ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "posts" references "users".
HINT:  Truncate table "posts" at the same time, or use TRUNCATE ... CASCADE.

For performance reasons, I really want to be able to use TRUNCATE and not DELETE. How can I get around this issue without using TRUNCATE users CASCADE? I can not use cascading deletes because my DB may have other foreign keys that reference the users table that I do not want to delete.


texts: Join query with IN condition and Group_concat

texts:
I have an issue to get concatenated text from below two tables(product_to_category & category_description)

I need mysql query to get output as "Electronics,Electronics Component Connectors,Electronics Accessories" from category_description table's name column.That query will compare category_id in these tables with where condition as product_id = 276.

I tried this query,but it returns empty result -

 SELECT GROUP_CONCAT(description SEPARATOR ',')
  FROM `ocrk_category_description`
 WHERE `category_id` IN (SELECT GROUP_CONCAT(category_id SEPARATOR ',') as `category_id`
                           FROM `ocrk_product_to_category`
                          WHERE `product_id` = 276
                        )

Any ideas? i can provide you more information if need.


texts: Lifespan of Launchpad-Launched Exterrnal Runtime Processes

texts:
When SQL Server uses SQL Launchpad to launch an external runtime (e.g. R or Python) when sp_execute_external_script is used, what's the lifespan of the external runtime process?

Does each sp_execute_external_script script execution get its own external process instance or does the external process stay around until it's been inactive for a certain amount of time (e.g. the existing process might be reused if I execute another script written in the same language using the same SQL session)?


texts: PostgreSQL 9.6 error: invalid resource manager ID

texts:
I am encountering error messages like this in a PostgreSQL 9.6 server log file (while experimenting with pg_rewind):

LOG: invalid resource manager ID 119 at 0/910CE68

What is a (PostgreSQL) resource manager and why does it have to by identified in a WAL file?


texts: Search for X at the beginning of a line within a multi-line string

texts:
On SQL Server (2008), I have a column which is varchar(max).

I want to query for all rows where the blurb contains a lettered list:

Some sentence is here

a. foo
b. bar
c. baz

Some more text later

Can do this easily in regular expressions, but I do not have access to add regex capabilities to my instance.

So, How can I search using LIKE to find something like this? Presumably I would need to find where the first character of the line (in a multi-line blurb) is a character and 2nd is a dot.


texts:
We use Microsoft SQL Server Express Edition with Advanced Services 2008 R2 SP2

We have two locations. Each location has one sql server that serves about 20 client computers. Client computers are connected via wireless network.

Each client computer has to report its state every second by performing a single record update. A web service runs a "select * from" this table every two seconds, and serves web clients with the latest states of all client computers.
The table in question has one row per client computer, 20 rows total. There is a clustered index on the table, on the column used in the where clause of the update. So each client computer updates its own row in the table, and there should be no locking issues.

The problem - sometimes updates time out. It happens sporadically about 10 times a day in one location, and about 5 times a day in the other. The first location has 20 client computers, and the second location has 25.
I think that the problem is due to the fact that the table is so small, the update transactions are placing  page locks on the table, and have to wait for each other.

Question - will forcing row locks is the right approach to solving this problem, and if not - what should I do to run the updates without timing out?

Below is the update statement - it is very simple:

UPDATE dbo.mytable
SET
    [col1] = @col1, --more columns here
    [col10] = @col10
WHERE
    [unique_col] = @unique_value

Here is the DDL - I took out the actual col names:

CREATE TABLE [dbo].[DCA_CURRENT_DATA_T](

    [col1] [char](12) NOT NULL,
    [col2] [varchar](20) NULL,
    [col3] [char](1) NULL,
    [col4] [int] NULL,
    [col5] [int] NULL,
    [col6] [int] NULL,
    [col7] [char](1) NULL,
    [col8] [int] NULL,
    [col9] [int] NULL,
    [col10] [int] NULL,
    [col11] [int] NULL,
    [col12] [int] NULL,
    [col13] [datetime] NULL,
    [col14] [int] NULL,
    [col15] [int] NULL,
    [col16] [int] NULL,
    [col17] [int] NULL,
    [col18] [int] NULL,
    [col19] [int] NULL,
    [col20] [int] NOT NULL,

PRIMARY KEY CLUSTERED
(

    [col1] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,

ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[DCA_CURRENT_DATA_T] ADD  DEFAULT (0) FOR [col14]


texts: Looking for suggestions to model a database of Electrical Components

texts:
We have a database of electrical components and I am trying to make sure I have a good design. I am not a database designer (my comfort zone is designing with these hardware components), so I am interested in some pointers that have more application to me than name/address type examples. I have read some basic blurbs on First, Second and Third Normal Forms.
Sample tables
The tables shown bellow are just examples, there are many more points of data and categories of data, but want it to be a good, efficient design. The columns of my first table where all data is required for every entry might be:

Internal_Part_Number (Set as PRIMARY KEY [PK])
Status
Genre
Description
Manufacturer
Manufacturer_Part_Number

If the Genre values is 'Capacitor', we need additional information where the first three, but there are several optional columns that should be provided like ESR if the data is available:

Internal_Part_Number
Capacitance
Voltage
ESR (optional, not all Components have these specified)

However, if the Genre value is 'Resistor', we need different additional information with similar constraints on data as above:

Internal_Part_Number
Resistance
Power_Rating
Temperature_Coefficient (optional, but this never applies to Capacitors)

Questions

Should I be splitting up my first table?
Or, do all data columns that always exist for a given row be in a single table even though the data is not very well grouped?

For reference, our internal status of the part does not really care about the manufacturer and vice versa, But they are both necessary to describe Internal_Part_Number, so I think they both belong in the same table don't they?
Then there are more data that further define a given Part. Do I JOIN those tables to the first table on Genre when creating a view for that specific Genre?
How about when there are 50% of the attributes that are optional to populate to describe that Part, should each one be its own table that you would JOIN them all on Internal_Part_Number?
Obviously, we have a Part_Number that can be used as PK.
If I normalize this, I would have my master table as above, but would be creating a separate table called genres(cap and res, etc). Then another table called parametric list( where all parametric names live), another table to relate genres to parametrics, then finally a table that would have part Id, parametric Id, and value.
However, sometimes that value is a string, sometimes it's an enumeration, sometimes it's a number with units.
How would I link these tables to each other and how would I create a sql query that could create a table specific to the genre?


texts: Backup Restore, CPU usage and Extended Events

texts:
I'm working on measuring the performance impact of Backups on CPU. I would like to capture all the backup operations and CPU usage. I'm running a weekly full backup, daily diff, and 15 min log backup. Are there any options in Extended Events we can use to capture CPU usage?
CREATE EVENT SESSION [Backup_Events_Trace] ON SERVER ADD EVENT sqlserver.backup_restore_progress_trace (ACTION(sqlserver.client_hostname, sqlserver.database_name))
    ,ADD EVENT sqlserver.databases_backup_restore_throughput (ACTION(sqlserver.client_hostname, sqlserver.database_name)) ADD TARGET package0.event_file (SET filename = N'L:\XE\Backup_Events_Trace.xel')
    WITH (STARTUP_STATE = OFF)
GO


texts: MS SQL Server, ask password before drop specific DB

texts: I've got 2 databases which are very important for our organization. I would like to prevent an accidental drop/delete of them... Do you have any suggestion? I was thinking about asking a password before perform the drop. Is this possible in MS SQL Server?


texts:
I have created a stored procedure in mysql using the following syntax.

DROP PROCEDURE IF EXISTS `sp-set_comment_count`;

DELIMITER $$

CREATE PROCEDURE `sp_set-comment_count` (IN _id INT)
BEGIN
   -- AC   - AllCount
   DECLARE AC INT DEFAULT 0;

   SELECT COUNT(*) AS ac
     INTO AC
     FROM usergroups AS ug
LEFT JOIN usergroup_comments AS ugm ON ugm.`gid` = ug.`id`
LEFT JOIN mediagallery AS dm ON ugm.mid = dm.`id`
    WHERE dm.`status` NOT IN (200, 201, 202, 203, 204, 205)
      AND ug.`id` = _id;

   UPDATE usergroups
      SET allCount = AC,
    WHERE usergroups.`id` = _id;

END $$
DELIMITER ;

FYI I've greatly simplified the stored procedure but I do know it works without any issues.

What I'd like to be able to do is set up a trigger from usergroup_comments that works like this.

DROP TRIGGER IF EXISTS `usergroups_comments_insert`

CREATE TRIGGER `usergroups_comments_insert` AFTER INSERT ON `usergroups_comment`
    FOR EACH ROW
    BEGIN
       CALL sp-set-comment_count(NEW.`gid`);
    END;

But for some reason every time I do mysql throws an error at me that's less than helpful stating that there's a syntax error on line 4.

I've combed through the mysql documentation and found some information on restrictions of triggers but found it to be fairly convoluted.

http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html

Any ideas would be helpful.


texts: What does "copy of" mean when doing "delete archivelog"?

texts:
Disclaimer: I'm not a DBA nor did I fully read the RMAN docs cover to cover, so this may be well covered, but I couldn't find it.

The question is very simple, what is the difference between these two commands:

delete copy of archivelog all completed before 'SYSDATE-7';
delete archivelog all completed before 'SYSDATE-7';

What is deleted from where under which circumstances when running these from RMAN?

Possibly relevant infos: (?)

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5785
Next log sequence to archive   5787
Current log sequence           5787
SQL> select * from v$recovery_file_dest;

NAME             SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
---------------- ----------- ---------- ----------------- -----
e:\oracle\backup 2.1475E+11  4.7820E+10 4.6097E+10        1246

-

RMAN> show retention policy;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ****** are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;


texts:
The partition containing my Oracle 11g installation began to fill up with trace files and when I went looking for what are these I've found this disturbing post about how to disable the trace file generation.

The two related questions I have are:

Can I simply delete these trace files from my FS w/o consequence ? (I know, if they apeared one should probably look at them.. but won't happen and the database has no production use whatsoever. Going to have to happen another time.)
What should be done to prevent it from appearing again?


texts:
We have a weird situation. We have an old database and new database both different schemas (SQL Server). We are using abinitio to replicate data from old to new.

Now if for some reason the new structure does not work for us, we want a rollback plan to copy data from new to old. But this could also mean records that are updated in new and also inserts.

Also if this has to be done with no downtime to customer, how can we achieve this?


texts: Increment each row according to condition in SQL C#

texts:
I have a value of 100 from a textBox and I want to increment that value in the stock_out column but only till stock_out column is equal to stock_in column, if first row value is 40 than first row stock_out should be incremented by 10 from 100 than the remaining value from 100(90) should be incremented in the row below it and then row below it till needed (till 100 is 0). Items are ordered by Item_Name and Bill No asc.

Bill No   Item_Name   Stock_in  Stock_out  stock_in_hand
1          chicken       50        40           10
2          chicken       40         0           40
5          chicken       60        10           50
6          chicken       100       20           80
3          fish          100       30           70
4          fish          20        20            0

The result to be obtained.

Bill No   Item_Name   Stock_in  Stock_out  stock_in_hand
1          chicken       50        50           0
2          chicken       40        40           0
5          chicken       60        60           0
6          chicken       100       20          80
3          fish          100       30          70
4          fish          20        20           0

Here is the code that I have been able to do so far, but it needs a lot of work and I do not have expertise in cte or any complicated c# coding. I am using SQL Server 2014 and c#.

C# code SQL Query

select *
from stock_recieve
where Item_Name like'" + comboBoxitem.Text + "'
where Stock_out < Stock_in
order by [Bill No] asc;

update stock_recieve
     set Stock_out = Stock_out+" + qty + "
where Stock_out < Stock_in;

Any help would be greatly appreciated.

P.S I know about the sql injection and I will change my sql queries to parameterized queries.

The 100 is a example value obtained from user via textbox, how do i implement code to receive value from textbox.


texts: What adverse effects does a table have when it's not even in 1nf?

texts:
What adverse effects would a table have if it's not even in 1nf ?

I googled and read the article on Wikipedia but I couldn't find exactly why a table should be in 1nf.
Also, I couldn't find what horrible things might happen if a table is not in 1nf.

So other than being able to use simple queries  , what monsters would crawl out of the tables while I sleep happily at home if my tables weren't in 1nf ?


texts: 'Logical file name "MyDB" is already in use' Even though the database doesn't exist

texts:
I recently was given a SAN with a couple of RAID arrays to apply to my database server to increase performance. I have (and will only ever have) one user database called DDAS, in addition to the system databases that install along with SQL Server.

My intent was to have DDAS pointing to its own drive, tempdb pointing to its own drive and transaction logs pointing to their own drive. The user account running SQL Server has Full Control access to all the drives, I made sure of it before doing anything.

I successfully moved tempdb using the following SQL statements:

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', FILENAME = N'F:\tempdb.mdf')
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', FILENAME = N'F:\templog.ldf')

I restarted SQL Server, voila, tempdb is relocated (can I safely delete the old tempdb files from their original location? Maybe that's another question...)

Now, because DDAS is still in development, I was able to delete all data from all the tables before doing anything to move it to its own drive. So now the file is ~5MB in size. I perform the same statements using the DDAS files:

ALTER DATABASE [DDAS] MODIFY FILE ( NAME = N'DDAS', FILENAME = N'E:\DDAS.mdf')
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'DDAS_log', FILENAME = N'G:\DDAS_log.ldf')

I restart SQL Server and it doesn't work... I get DDAS (Recovery Pending) on the database in Management Studio indefinitely. I deleted the database completely because I have a script that will create all the objects etc... intending to just try to re-create it on the drive it is supposed to be on. I attempted to create the database with the following statement:

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'DDAS')
BEGIN
    CREATE DATABASE [DDAS]
    CONTAINMENT = NONE
    ON  PRIMARY
        ( NAME = N'DDAS', FILENAME = N'E:\DDAS.mdf' , SIZE = 1024000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024000KB  )
    LOG ON
        ( NAME = N'DDAS', FILENAME = N'G:\DDAS_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
    END
GO

but I get this error now:

I used this query to list file names being used by SQL Server:

SELECT DB_NAME(database_id) AS DatabaseName,
       name AS LogicalFileName,
       physical_name AS PhysicalFileName
FROM sys.master_files AS mf

and got this result:

How is this possible? I tried restarting SQL Server a number of times to no avail. I also checked that the original file on the C:\ drive that DDAS was using has been deleted, as well as its transaction log. There's no trace of DDAS anywhere other than this error message. I also have no open connections to DDAS that might have somehow lingered through all of those restarts.

If it matters: SQL Server Enterprise 2014 x64


texts: Is there a way to define macros/alias in psql?

texts:
It is somewhat annoying to type EXPLAIN (ANALYZE, BUFFERS, VERBOSE) in psql tool every time you tune queries.

So, is there a way to define an aliases (autocompletable ones would be very nice) in psql similar to the way it is in bash?

E.g. FULLEXPLAIN SELECT * FROM pg_stat; results into EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM pg_stat;


texts: Database fragmentation and heap SQL Server

texts:
We have critical performance issues with our production database (its around 300 gb size)
We have run the following script for a few huge tables :

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'master');
SET @object_id = OBJECT_ID(N'master.dbo.tTable');

IF @db_id IS NULL
BEGIN;
    PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
END;
GO

and get the following relults :

First table:

index_type_desc : HEAP

alloc_unit_type_desc : IN_ROW_DATA

index_depth: 1

index_level: 0

avg_fragmentation_in_percent : 81.1555698682339

fragment_count : 163818

avg_fragment_size_in_pages : 8.4652052887961

page_count : 1386753

Second table :

index_type_desc : HEAP

alloc_unit_type_desc : IN_ROW_DATA

index_depth: 1

index_level: 0

avg_fragmentation_in_percent: 82.2484358706986

fragment_count : 76826

avg_fragment_size_in_pages: 6.87469085986515

page_count : 528155

For non-clustered indexes the avg_framgentation_values value is too low.
Are we have a problem with clustered index ? And what can we do with it to improve our performance ?


texts: How do I change the config path for mysqld?

texts:
I want to change configuration file path from C:\ to E:\, but I can't find the way to do this.

Alternatively, is there a way to start a new instance with the config file I want? I've done something like mysqld --init-file="E:\db\my.ini" but it didn't work as well.

--EDITED--

I started new instance with the config files, and it worked well, and I faced another problem. Could you give me a help for this one as well?

https://dba.stackexchange.com/questions/100492/cannot-change-datadir-for-mysqld


texts: How to get user-specific settings in PostgreSQL?

texts:
I have to collect some information of PostgreSQL roles.  One piece of information is whether the role has different settings than 'usual', usual meaning here default values for only a few parameters (eg. log_statement).

So far I have only one idea: loop over all the users and issue the commands

\c postgres username

SHOW log_statement;

and then process the output.

I am wondering whether there is a similar method like querying the pg_settings view, showing per-user settings?  I don't need to know session-specific differences, only those that are persisted.


texts: Index for finding attributes in large JSONB array

texts:
Based on this original question and the follow up question from 2015.
Suppose I have a jsonb column data within a table tracks. This column holds an array with multiple keys e.g. Artist and Title in the JSON data. So we have something like:
INSERT INTO tracks (id, data)
VALUES (1, '[{"artist": "Simple Plan", "title": "Welcome to My Life"}]');

I understand that there are two Options to index the key artist:

GIN Index on data as a whole
CREATE INDEX tracks_artists_gin_idx ON tracks
USING GIN ((data));

A functional Index using for example json2arr described below
CREATE INDEX tracks_artists_gin_idx ON tracks
USING GIN (json2arr(data, 'artist'));

CREATE OR REPLACE FUNCTION json2arr(_j json, _key text)
  RETURNS text[] LANGUAGE sql IMMUTABLE AS
'SELECT ARRAY(SELECT elem->>_key FROM json_array_elements(_j) elem)';

--Index Usage:
SELECT * FROM tracks
WHERE  '{"Simple Plan"}'::text[] <@ (json2arr(data, 'artist'));

In my case, the data array is huge and has many more keys. So Option 1 is a bad idea. Is there a third option in 2021? Or is Option 2 still the way to go?
EDIT 16.02. :
This is the real world WHERE condition to be optimized:
 WHERE event @> '{"data":{"allFahrtabschnitt":[{"fahrtnummer":"62786"}]}}';

EDIT 22.02. :
Option 2 works so far and delivers great results. I would still be interested if other Options exist that can use the standard jsonb Syntax.
 CREATE OR REPLACE FUNCTION events.json2arr(_j jsonb, _key text)
     RETURNS text[] LANGUAGE sql IMMUTABLE AS
 'SELECT ARRAY(SELECT elem->>_key FROM jsonb_array_elements(_j) elem)';

 CREATE INDEX interface_dk_fahrt_v4_fahrtnummer_gin_idx ON                events.interface_dk_fahrt_v4
     USING GIN (events.json2arr(event->'data'->'allFahrtabschnitt',      'fahrtnummer'::text));
 -- completed in 5 m 33 s 209 ms

 -- Selection - must match Index-Syntax!
 SELECT metaid FROM events.interface_dk_fahrt_v4_20210219
 WHERE 1=1
 AND '{"31650"}'::text[] <@ (events.json2arr(event->'data'-     >'allFahrtabschnitt', 'fahrtnummer'::text));
 -- 127ms

 -- Selection with incorrect Syntax -> Index isnt used
 SELECT metaid FROM events.interface_dk_fahrt_v4_20210219
 WHERE 1=1
 AND event @> '{"data":{"allFahrtabschnitt":[{"fahrtnummer":"31650"}]}}';
 -- 5 Minutes

EDIT 09.04. :
Here is a new option that we learned when looking into json_path_query that needs no custom functions:
CREATE INDEX interface_dk_fahrt_v4_fahrtnummer_gin_idx ON events.interface_dk_fahrt_v4
USING gin (jsonb_path_query_array (event, '$.data.data.allFahrtabschnitt.fahrtnummer[*]));


texts: SQL 2008 R2 Datafile Too Large To Empty or Remove

texts:
Our primary database has three datafiles: 1TB, 400G and 500G.  The 1TB file is just too dang big and isn't really even needed any more, so I want to empty it and get rid of it so we can use that drive space to partition the data.  However, any attempts to empty the file will get to 16% or so and then drop to an absolute crawl: the last attempt, after 24 hours, showed the estimated time to complete (from sys.dm_db_exec_requests) was 4.5 days, and I will guarantee that would continue to increase out of proportion to the percent of work completed.

The file is currently 96% free so a lot of the data was moved out, but now we're stuck.  We can't run a shrink 'cause it's apparently too big, and it won't let us remove it 'cause it was the initial datafile for the database and holds the sys data.

Is there a faster way (faster than, say, a week or two of downtime) to shrink this thing and/or get rid of it entirely and migrate everything to another datafile in the PRIMARY filegroup?

Edit:  Found a solution!  I can't shrink the whole thing at once; I just have to do it in dribs and drabs.  Shrinking by 50G at a time is working like a champ!


texts: How to prevent high memory, CPU and time consumption by MySQL restore?

texts:
I have a local test machine with 2 GB RAM and a dual core processor.

I imported a dump on that using

mysql -uuser -psecret < script.sql

script.sql is a 700 MB file which contains a single database with more than 100 tables.

Restore took approximately 25 minutes.

Question: Why it took so long time? I have already optimized my server settings. The dump also contains extended inserts and disables keys). Is 700 MB is too large for this kind of machine? mysqld was using 100% of one of the CPU when I checked using htop and 22% of the memory.

Now what actually I want is to convert all my tables of that database to InnoDB. So for that I executed a simple shell command:

mysql -uuser -psecret -Bse "select Concat('Alter Table ' , TABLE_SCHEMA ,'.',TABLE_NAME , ' Engine= \'InnoDB\' ;') from information_schema.tables where ENGINE='myisam' and TABLE_SCHEMA='db_name';" > Innodb.sql

so Innodb.sql contains the script for converting tables to InnoDB.

Now when I run Innodb.sql one of my table takes more than 20 minutes for conversion. It contains only 1,378,397 records.

In total it takes more than 30 minutes. In the meantime memory usage by mysqld daemon was 73%. The CPU usage was OK at this time.

How can I minimize the time consumtion? Should I go for changing MySQL server settings or anything else?

If anybody wants my my.cnf setting I will share that.


texts: How to select * if sets of data in two columns matches >= 4 times

texts:
I'm trying to pull information on repeat dispatches and can't seem to come up with a simple solution to pull my data for a report.

I have the following table:

tblMaster15

 ID | Equipment
----------------
  1 |   Sink
----------------
  2 |   Grill

And another table with location info that I have to join on using ID:

tblMaster15_ABData

 ID | Unit | Region
--------------------
  1 | 600  |  6000
--------------------
  2 | 601  |  7000

And I'm trying to produce a list of the ID, Unit, and Equipment but only when the Unit and Equipment are the same more than 3 times in a given time period.

I've tried the following but it seems to be giving me everything, not just where the count is > 3.

select   m.mrid, a.unit, a.Region, m.Equipment
from     MASTER36 m
join     MASTER36_ABDATA a
on       m.mrid = a.mrid
, (select   a.unit, m.Equipment
   from     MASTER36 m
   join     MASTER36_ABDATA a
   on       m.mrid = a.mrid
   group by a.unit, m.Equipment
   having   count(*) > 3
  ) T2
where    a.unit = t2.unit
and      m.Equipment = t2.Equipment
and      mrSUBMITDATE between '2/7/17' and '3/7/17'
and      mrstatus <> 'DELETED'
and      mrstatus <> 'Canceled'
and      m.Equipment  <> 'BLDNG

I did not think you would need status or submit date. The status is closed or open. Submit date in this format: 2017-01-01 00:50:24.000

I am using SQL Server 2012.


texts:
I have an update query being fired from the Java Hibernate (for a single user), which works fine on a small dataset of rows in a table (100MB), but the same update query goes into a hung state if the number of records in the same table increases (12 GB).
I didn't find any blocking or deadlocks queries been executed.
I have performed autovacuum, reindex, analyze, increased shared buffers, set locktimeout, statement timeout values - but with no effect.
The update transaction doesn't move ahead only and never gets committed.
Output of the pg_locks table:

DDL Statement:

ANALYZE explain output:

I verified the database schemas on both databases and no difference was found. Doing the analyze, same plan is being used for both the databases but time for execution is slightly on the higher side for the database with large size. But the update gets committed when fired directly whereas through application it's waiting continuously.
Please find the output of EXPLAIN (ANALYZE, BUFFERS) update:
"QUERY PLAN"
"Update on ""CaseProductUnderwriting""  (cost=0.00..2421622.16 rows=1 width=652) (actual time=14339.234..14339.235 rows=0 loops=1)"
"  Buffers: shared read=1644192"
"  ->  Seq Scan on ""CaseProductUnderwriting""  (cost=0.00..2421622.16 rows=1 width=652) (actual time=14339.228..14339.229 rows=0 loops=1)"
"        Filter: (""CaseProductUnderwritingPK"" = '8908693'::bigint)"
"        Rows Removed by Filter: 62196975"
"        Buffers: shared read=1644192"
"Planning Time: 1.421 ms"
"Execution Time: 14339.377 ms"

Please note that the same UPDATE is not moving forward.
EXPLAIN (ANALYZE, BUFFERS) Update "QUERY PLAN"
"Update on ""CaseProductUnderwriting"" (cost=0.00..2421622.16 rows=1 width=652) (actual time=14339.234..14339.235 rows=0 loops=1)"
" Buffers: shared read=1644192" " ->
Seq Scan on ""CaseProductUnderwriting"" (cost=0.00..2421622.16 rows=1 width=652) (actual time=14339.228..14339.229 rows=0 loops=1)"
" Filter: (""CaseProductUnderwritingPK"" = '8908693'::bigint)"
" Rows Removed by Filter: 62196975"
" Buffers: shared read=1644192" "Planning Time: 1.421 ms"
"Execution Time: 14339.377 ms"


texts: MongoDB slow search in array with $all multiple conditions, even with index

texts:
I have a mongodb collection with about 100.000 documents.
Each document has an array with about 20 elements. The array looks like this:

features: [
    "0_Toyota",
    "29776_Grey",
    "101037_Hybrid",
    "240473_Iron Gray",
    "46290_Aluminium,Magnesium",
    "2787_14",
    "9350_1920 x 1080",
    "36303_Y",
    "310870_N",
    "57721_Y"
    ...

This is my query:

db.products.find({

features: {$all: ["9350_1366 x 768", "91135_IPS", "178874_Y", "36303_N"]}

})

This query is very fast (takes less than 1 second). But sometimes, in particular cases, gets very slow when I include a specific condition inside $all. I have no idea why this happens. When gets slow it takes more than 40 seconds. Always happens with the same extra condition. It is very possible that it happens with other strings.

In first place I indexed the field "features".

Also I order the $all elements for satisfy mongodb specificity. I got a little more performance, but keeps terribly slow.
Last, I test to hash strings and keep short like "8f624e" instead of "46290_Aluminium,Magnesium" for example. No performance difference.

I'm running the same mongodb in my unix laptop and on a linux server instance. Version is 4.0.2


texts: IF UPDATE in trigger returns true when no rows were affected

texts:
Why is the trigger executing the update(col1) block in the following:

USE tempdb
go
CREATE TABLE t1(id INT, col1 VARCHAR(10))
go
INSERT INTO dbo.t1( id, col1) VALUES(1,'aa'),(2,'sdf'),(3,'fg')
go
CREATE TRIGGER r_test ON t1 after UPDATE
AS
PRINT 'Trigger'
IF UPDATE(col1)
    PRINT 'col updated'
GO

UPDATE t1 SET col1='werwer' WHERE id=4

output

  col updated

I understand why the trigger executed but since id = 4 doesn't exist, shouldn't UPDATE(col1) be returning false?


texts:
I try to do a select union with order by but it seem like order by can not use union.

I receive following message

  Query must be a SELECT

This is my code

(SELECT
  `tabsales_action`.responsible as "Responsible:data:200",
  `tabsales_action`.action_type as "Action Type:data:200",
  count(*) as "Nb:Int:60"
FROM
  `tabsales_action`
WHERE
  `tabsales_action`.workflow_state = 'To do'
GROUP BY
  `tabsales_action`.responsible,
  `tabsales_action`.action_type
ORDER BY
  `tabsales_action`.responsible )
UNION (SELECT `tabmfi_loan_action`.responsible,
`tabmfi_loan_action`.action_type FROM  `tabmfi_loan_action`
WHERE
  `tabmfi_loan_action`.workflow_state = 'To do'
GROUP BY
  `tabmfi_loan_action`.responsible,
  `tabmfi_loan_action`.action_type
ORDER BY
  `tabmfi_loan_action`.responsible)
UNION (SELECT `tabin_house_credit_action`.responsible,
`tabin_house_credit_action`.action_type FROM `tabin_house_credit_action`
WHERE
  `tabin_house_credit_action`.workflow_state = 'To do'
GROUP BY
  `tabin_house_credit_action`.responsible,
  `tabin_house_credit_action`.action_type
ORDER BY
  `tabin_house_credit_action`.responsible)
UNION (SELECT `tabservices_planning`.responsible,
`tabservices_planning`.action_type FROM `tabservices_planning`
WHERE
  `tabservices_planning`.workflow_state = 'To do'
GROUP BY
  `tabservices_planning`.responsible,
  `tabservices_planning`.action_type
ORDER BY
  `tabservices_planning`.responsible)
UNION (SELECT `accounting_action`.responsible,
`accounting_action`.action_type FROM `accounting_action`
WHERE
  `accounting_action`.workflow_state = 'To do'
GROUP BY
  `accounting_action`.responsible,
  `accounting_action`.action_type
ORDER BY
  `accounting_action`.responsible)

What can I do?


texts: How to use JOIN and SUM function to Calculate the total of query returned column?

texts:
I ran bellowing query;

Query:

SELECT  j.jobId,
        j.productId,
        p.productUnitPrice
FROM    JobRequiredProducts J
JOIN    Product p
  ON    p.productId = j.productId
WHERE   j.jobId = 1
ORDER BY j.jobId

And got bellowing result;

Query result:

jobId | productId | unitPrice
_____________________________
  1   |     4     |   175.99
  1   |     5     |   100.00
  1   |     6     |   125.00

Now, I want to calculate all unitPrice to get the total. the result I expect is;

Expecting result:

jobId | productId | Total
____________________________
  1   |     4     |   175.99
  1   |     5     |   100.00
  1   |     6     |   125.00
 null |    null   |   400.99

Therefor bellowing is the query I tried;

Query I tried:

SELECT  j.jobId,
        j.productId,
        SUM(p.productUnitPrice) AS 'Total'
FROM    JobRequiredProducts J
JOIN    Product p
  ON    p.productId = j.productId
WHERE   j.jobId = 1
GROUP BY j.jobId,j.productId WITH ROLLUP

But it didn't give me the result I expected, where I went wrong and what I gotta do to get the result I expect?


texts: MongoDB replica set node failure without arbiter

texts:
I'm baffled by the fact that this isn't addressed in the official docs (or at my inability to track it down).  From the 3.4 docs:

  Only add an arbiter to sets with even numbers of voting members. If you add an arbiter to a set with an odd number of voting members, the set may suffer from tied elections.

So we have two possible scenarios:

A replica set with an odd number of data nodes, which requires no arbiter (and in fact, adding an arbiter can be detrimental per above)
A replica set with an even number of data nodes, and an arbiter

This is great so long as things are running smoothly.  But what if a data node goes down in each of these scenarios?

We now have an even number of nodes with no arbiter. As I understand it this means you cannot have a successful election.
We now have an odd number of nodes, plus an arbiter. Above docs state that this can lead to tied elections.

This is so painfully obvious, why can't I find any discussion or documentation on it? But what I really want to know is, how do I correctly handle these scenarios?