texts: mysql perfromance schema events_statements_current timer units

texts:
I'm wondering if this is a known bug, or I'm just doing something silly. according to documentation, timer_start in events_statements_current is supposed to be in picoseconds. It is also supposed to be offset from the system start time.
(for mysql 8.X)
SET @uptime = (SELECT VARIABLE_VALUE
                 FROM performance_schema.global_status
                WHERE variable_name='UPTIME');
SELECT date_sub(now(), INTERVAL (@uptime) -`TIMER_START` * 10e-13 second) `start_time`
  FROM `performance_schema`.`events_statements_current`;

uptime is in seconds, timer_start is supposed to be in picoseconds. So I would expect to have to multiply it by 10e-12 not by 10e-13.
what am I missing?


texts: Get NULL statistics for a column in MS SQL Server

texts:
In MS SQL Server 2005, how can I extract statistical information about NULL fraction (or NULL count) for a given column?

Same information is available in Oracle and PostgreSQL, and I heard that MS SQL also has statistics, but I don't know where to find it. Google didn't help much.

I hope I will hear something like

SELECT null_frac FROM pg_stats WHERE tablename='mytab' and attname='mycol';
/* PostgreSQL, stats gathered by ANALYZE */

or

SELECT nullcnt FROM custom_stats_table WHERE tabname='MYTAB' and colname='MYCOL';
/* Oracle, stats gathered by dbms_stats.get_column_stats */

I'm not asking how to compute these values myself - I know how to do it.
I need these stats for several thousands of columns, and computing this would take very long on the database that I'm working on. I need approximations, and they should be somewhere in the server - they are needed anyway for cost-based optimizer.


texts: SQL - Optimize table population with dynamic queries

texts:
I need to optimize the following pl/sql procedure. So, fundamentally my question is: There is a way you can do a bulk insert in a pl/sql that could be more efficient than loop through an associative array?

type mydatatype is table of my_table%rowtype index by pls_integer;
type c_type is ref cursor;

dynamic_select  :=  'select <fields>, ';
dynamic_from    :=  ' from  <tables>';
dynamic_where   :=  ' where conditions and period_time >= ';

myobject    mydatatype;
cvar        c_type;

cursor p1 is
 select t.id, t.indicator
 from   <master_table> t
 where  <conditions>;

begin

for p in p1 loop

    dynamic_query := dynamic_select || p.indicator || dynamic_from || dynamic_where || 'to_date(''' || initial_date || ''', ''DD/MM/YYYY HH24:MI'')' || ' and period_time < ' || 'to_date(''' || end_date || ''', ''DD/MM/YYYY HH24:MI'')';

    open cvar for dynamic_query;
        loop
            fetch cvar bulk collect into myobject limit 10000;

            forall i in 1..myobject.count
                insert /*+ append */ into my_table values myobject(i);

                commit;

            exit when cvar%notfound or cvar%notfound is null;

        end loop;

    close cvar;

end loop;

end

My ideal would be a bulk insert like (obviously don't work):

insert into my_table
values      myobject;

I could get a boost in performance when i modified the fetch limit, but is not enough. Typically that stored procedure will loop one million times to populate the table, but is required the execution time don't exceed 1 hour. Right now the average execution time is 2 hours.


texts:
I have a little problem with the performance of a mysql-query which uses a fulltext index.

The following query

SELECT Mention.id
FROM mentions AS Mention
WHERE (MATCH (`Mention`.`title_text`, `Mention`.`content_text`, `Mention`.`author_text`)
AGAINST ('"hannover 96"' IN BOOLEAN MODE))

takes about 3 seconds.

If I change the fulltext-condition to

SELECT Mention.id
FROM mentions AS Mention
WHERE (MATCH (`Mention`.`title_text`, `Mention`.`content_text`, `Mention`.`author_text`)
AGAINST ('+hannover +96' IN BOOLEAN MODE))

the query takes just about 0.001 seconds.

So is there a possibility to get a better performance of the first query? It’s important to search for the complete string “hannover 96” and not for records which contain hannover and 96.

Thanks for some tips!

Best regards,

Timo


texts: Questions regarding removing a secondary node from an availability group

texts:
I have a 2 node AG in a non-prod environment and we are looking to shut down the secondary node for cost savings, leaving this with just the primary node.  I have some questions based on this process, and my research hasn't answered all of them.  Could somehow fill in the blanks here on what is possible / best to do?

is it possible to keep just a single node AG?  if so, is there a reason to actually do it?  As I understand it, there is no benefit to keeping the AG at all if you remove all the secondary nodes, as it then becomes just a stand alone instance.  If i kept the AG, then the transaction logs of the DB's will not truncate, as its trying to find a place to send the logs to, correct?
The AG Listener - Moot point if we remove the AG altogether, but if i kept the AG as a single node, the listener would not function correct?  My app team will need to make code changes to use something else since listener name is coded to be used, and they would like to avoid that if possible.  i don't think this is possible, but wanted to check.
Secondary node down the road - we may spin up the secondary node on demand down the road, so is there an issue with leaving the cluster resources in place after we remove the AG, or does the removal delete the cluster objects?  I think they stay, but I have not found an answer on this.
SSRS question - we are in a scale-out deployment, do I need to remove the secondary from the scale out?  I would prefer to leave it since we may need to recreate the secondary down the road.  I would need to update connection strings, etc to use the primary node and not the listener.

My assumed workflow is like this based on what I found

Connect to primary replica
Delete the entire AG (this removes AG settings from all DB's, primary and secondary), and throws secondary DB's into restoring state, while leaving primary DB's accessible and completely separate.
log backups will continue on primary like normal and log truncation will happen as expected.


texts: SQL perf issue - configure server correctly first or troubleshoot issues one by one?

texts:
This is for SQL Server 2012.

I am  troubleshooting overall slow performance on a Dynamics AX environment. Everything is just slow - app itself, reports, ad-hoc, etc.  Right off the bat, I can see that none of the best practices for SQL Server have been configured - MAXDOP, tempdb, trace flags specific for Dynamics, and index/fragmentation maintenance.  I am pretty sure there are inefficient queries, missing or non-optimal indexes, etc.

I spent a little bit of time looking at wait stats, but now I'm wondering if I should first apply all the recommended best practices (industry-standard best practices for DynamicsAX), then troubleshoot what is slow, or dig into each issue one by one, and address it as it comes?  I know some will say, don't start flipping switches until you know for sure it needs to be flipped.

How would you approach this kind of scenario?


texts: Store millions of rows of denomalized data or some SQL magic?

texts:
My DBA experience doesn't go much further than simple storage + retrieval of CMS style data - so this may be a silly question, I don't know!

I have a problem whereby I need to lookup or calculate holiday prices for a certain group size and a certain number of days within a certain time period. E.g.:

How much is a hotel room for 2 people for 4 nights anytime in January?

I have pricing and availability data for, say, 5000 hotels stored like so:

Hotel ID | Date | Spaces | Price PP
-----------------------------------
     123 | Jan1 | 5      | 100
     123 | Jan2 | 7      | 100
     123 | Jan3 | 5      | 100
     123 | Jan4 | 3      | 100
     123 | Jan5 | 5      | 100
     123 | Jan6 | 7      | 110
     456 | Jan1 | 5      | 120
     456 | Jan2 | 1      | 120
     456 | Jan3 | 4      | 130
     456 | Jan4 | 3      | 110
     456 | Jan5 | 5      | 100
     456 | Jan6 | 7      |  90

With this table, I can do a query like so:

SELECT hotel_id, sum(price_pp)
FROM hotel_data
WHERE
    date >= Jan1 and date <= Jan4
    and spaces >= 2
GROUP BY hotel_id
HAVING count(*) = 4;

results

hotel_id | sum
----------------
     123 | 400

The HAVING clause here makes sure that there is an entry for every single day between my desired dates that has the spaces available. ie. Hotel 456 had 1 space available on Jan2, the HAVING clause would return 3, so we don't get a result for hotel 456.

So far so good.

However, is there a way to find out all the 4 night periods in January where there is space available? We could repeat the query 27 times - incrementing the dates each time, which does seem a little bit awkward. Or another way around could be to store all possible combinations in a lookup table like so:

Hotel ID | total price pp | num_people | num_nights | start_date
----------------------------------------------------------------
     123 |            400 | 2          | 4          | Jan1
     123 |            400 | 2          | 4          | Jan2
     123 |            400 | 2          | 4          | Jan3
     123 |            400 | 3          | 4          | Jan1
     123 |            400 | 3          | 4          | Jan2
     123 |            400 | 3          | 4          | Jan3

And so on. We'd have to limit max number of nights, and the max number of people we would search for - e.g. max nights = 28, max people = 10 (limited to the number of spaces available for that set period starting on that date).

For one hotel, this could give us 28*10*365=102000 outcomes per year. 5000 hotels = 500m outcomes!

But we'd have a very simple query to find the cheapest 4 night stay in Jan for 2 people:

SELECT
hotel_id, start_date, price
from hotel_lookup
where num_people=2
and num_nights=4
and start_date >= Jan1
and start_date <= Jan27
order by price
limit 1;

Is there a way to perform this query on the initial table without having to generate the 500m row lookup table!? e.g. generate the 27 possible outcomes in a temporary table or some other such inner query magic?

At the moment all data is held in a Postgres DB - if needs be for this purpose we can move the data out to something else more suitable? Not sure if this type of query fits the map/reduce patterns for NoSQL style DBs ...


texts:
I have a script,

UPPER(REPLACE(REPLACE(REPLACE(REGEXP_REPLACE(TRIM(mbr.mbr_first_name) || ' ' || TRIM(mbr.mbr_last_name), '[-+"=;:,<>?\/.~`!@#$%^&*()_\-|{}[]]*',''), CHR(9), ' '), CHR(10), ' '), CHR(13), ' '))

where it basically removes special characters (as much as possible) and remains only the alphabet and numeric.

But after generation, I still get this (�) black diamond with white question mark.

Is there a way to include this in the regexp_replace or to identify the CHR() value for me to remove this kind of characters


texts: Is it safe to update postgres server to 9.5 with ubuntu automatic update

texts:
Postgres 9.5 is out and Ubuntu has it in the update manager already.  I'm wondering though, is it safe to update that way?

Concerns/Questions:

Does it install 9.5 in addition to my already running 9.4 or replace it?

Does it migrate my data automatically?

Will there be downtime during the process?

Is it the same thing as running pg_upgrade

Am I missing anything else that I should be asking?


texts: How can I convert an Oracle dump file into SQL Server?

texts:
I want to ask about converting Oracle dump files (.dmp) into SQL Server files (.bak) where during conversion I don't have to be connected to any database server.

I've searched for related technologies, such as Oradump to SQL Server. Do you have another suggestion to solve this? Open source ones I mean.

Thanks for both of your response.
I see how difficult it will, but is there any possibility to use another way in converting oracle dump file? because all of solution's converter tools always provide a connection database server. I'm so thankful what if you can suggest another tool. thanks anyway


texts: What will happen in the background, when we update a record in a table in Postgres?

texts:
I have a table company like,

id  |  name  | age | address   | salary
----+--------+-----+-----------+--------
 1  | Ganesh |  32 | Chennai   |  20000
 2  | Allen  |  25 | Chennai   |  15000
 3  | Teddy  |  23 | Chennai   |  20000
 4  | Mark   |  25 | Chennai   |  65000
 5  | Paul   |  32 | Chennai   |  20000
 6  | Allen  |  25 | Chennai   |  15000
 7  | Teddy  |  23 | Chennai   |  20000
 8  | Paul   |  32 | Chennai   |  20000
 9  | Allen  |  25 | Chennai   |  15000
 10 | Teddy  |  23 | Chennai   |  20000

Now, what will happen in the background, when I update any one (or more)  record(s) in this table?

update company set address = 'California' where id = 5;

My real doubt is, whether it will delete the whole old record and insert a record newly with  given value or it just replace the given value in the specified column?

If the record is deleted for update, then the memory remain there?


texts: Adding login of a pc on the network - Microsoft SQL Server Management Studio 2014

texts:
I have a small office network and I am trying to add new logins for the other PCs on Microsoft SQL Server Management Studio 2014 (all computers are Windows 7).

I cannot seem to find the other machines when going to: Server name>Security>Logins-new>Login-name

I can only see my pc.

My pc is the also the server in the office and can share the network drives with the other computers.

How do I add the other computers as new logins?

Do I have to create a different SQL connection? How?


texts: Handling or preventing conflicts in a multi-user system

texts:
I have a web application that is accessed by multiple users from different locations worldwide. Let's say I have an "edit" form for a specific document and two (or more) users are editing the document at the same time. I know that both of the users can edit the document and submit it, but user1 will not see that user2 made changes to the document and might submit an older version of it.

I was thinking about adding some kind of a lock, but then, if user1 only opened the document for edit, but never changed anything (and kept it open) user2 will never be able to edit it. Therefore, I was thinking to add a Timeout for the editor, but then user1 might time out before he finished doing his changes to the document (let's say, went out for launch).

The question is, how would one prevent the document from being edited from one user while the other changes it?


texts: Postgresql - extremely slow basic SELECT query

texts:
I have a materialized view with about 1.6 million entries that gets refreshed every hour or so.
I have a && and ANY() as the filters in the query. I observed  the query was taking too long (over 2 minutes) to execute.
I then tried the basic SELECT * FROM components_view and it was taking about 25 seconds to execute.
explain analyze select * from components_view;
                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on components_view  (cost=0.00..350816.28 rows=1652628 width=886) (actual time=1.007..23978.237 rows=1652628 loops=1)
 Planning time: 0.103 ms
 Execution time: 25605.588 ms
(3 rows)

I then tried with one of the filters and the execution time increased by about 10 seconds
explain analyze select * from components_view where (  false = any(ignored_array) );
                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on components_view  (cost=0.00..371474.13 rows=1652628 width=886) (actual time=5.670..32654.284 rows=1652628 loops=1)
   Filter: (false = ANY (ignored_array))
 Planning time: 12.219 ms
 Execution time: 34106.971 ms
(4 rows)

Because of the slowness, the very purpose of choosing the materialized view is not being met.
There are no indexes on the columns that I"m filtering on (ignored_array and ids_array).
Any help here is appreciated. Thanks!
Edit:
Here's the explain (analyze, buffers, timing) for the queries
explain (analyze, buffers, timing) select * from components_view;
                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on components_view  (cost=0.00..350856.79 rows=1656579 width=888) (actual time=1.073..32332.136 rows=1652628 loops=1)
   Buffers: shared hit=57142 read=277149
 Planning time: 12.837 ms
 Execution time: 34038.656 ms
(4 rows)

explain (analyze, buffers, timing) select * from components_view where (  false = any(ignored_array) );;
                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on components_view  (cost=0.00..371564.03 rows=1656579 width=888) (actual time=3.516..32466.766 rows=1652628 loops=1)
   Filter: (false = ANY (ignored_array))
   Buffers: shared hit=57174 read=277117
 Planning time: 0.123 ms
 Execution time: 34089.644 ms
(5 rows)


texts:
I'm not a DBA and perhaps I can't explain myself properly. I'm looking for some fresh ideas about how, and what I need to deploy, to get a full SQL Server database available across different continents, something like building a SaaS over your private "cloud".

Imagine you have several offices in LATAM, EMEA, EAST and EEUU. Each office in every region has its own SQL Server with local databases and you are tired of managing hundreds of servers.

Easiest way to do resolve this is converging all small databases into a big one, placing it into a datacenter. As a first approach, I though that a single datacenter based in a single region will be enough to satisfy our requirements, but it won't because of latency. I mean that local users in one region can't work with a database based in a different region.

What's next? Simple thing. Deploy a datacenter in every region you want and every database will contain the same information with read/write and HA capabilities. At this point, I guess SQL Server with an AlwaysOn cluster will satisfy HA, but what about read/write? Do I have to use any kind of replica with every database service? Will it work?

And going further, imagine that a single country in a region needs to have its own database. How do I set up replication for this? What if I have to change a database schema?

Any help or clarification will be appreciated.

Our goal is to design a 3-tier application based on .net WCF published via Citrix XenApp. Due to some requirements this application won't work if the client is trying to connect to a far datacenter (i.e. LATAM to EU) because of latency (WCF works with local devices). Resuming this part, we need to build 2 DC in every continent to offer a reliable application tier with a proper fault tolerance and HA. Our main problem is to know what can we do at data tier...

My first idea was to deploy a SQL AlwaysOn cluster in the primary datacenter as a central node, with N SQL Std replicating bidirectionally from this master node. Yes, I said bidirectionally because some people at my work are fully confident with this scenario, but I'm still trying to understand how it will work in SQL Server (even if it's possible). I have more experience with MySQL and Galera and it works, but my SQL Server knowledge is limited.

I know you will ask me, why don't you connect directly from application tier to this central database? Perhaps this is the best choice because this SQL service won't be quite exhaustive (100.000 transaction per day), but we have to ensure the service and application functionality at 99,99999.


texts: postgres Poor performance on ORDER BY "id" DESC LIMIT 1

texts:
I have table items with following schema (in postgres v9.3.5):

  Column   | Type   |                         Modifiers                  | Storage
-----------+--------+----------------------------------------------------+----------
 id        | bigint | not null default nextval('items_id_seq'::regclass) | plain
 data      | text   | not null                                           | extended
 object_id | bigint | not null                                           | plain
Indexes:
    "items_pkey" PRIMARY KEY, btree (id)
    "items_object_id_idx" btree (object_id)
Has OIDs: no

When I execute query it hangs for a very long time:

SELECT * FROM "items" WHERE "object_id" = '123' ORDER BY "id" DESC LIMIT 1;

After VACUUM ANALYZE the query execution improved a lot, but still not perfect.

# EXPLAIN ANALYZE SELECT * FROM "items" WHERE "object_id" = '123' ORDER BY "id" DESC LIMIT 1;
                                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.44..1269.14 rows=1 width=63) (actual time=873796.061..873796.061 rows=0 loops=1)
   ->  Index Scan Backward using items_pkey on items  (cost=0.44..1164670.11 rows=918 width=63) (actual time=873796.059..873796.059 rows=0 loops=1)
         Filter: (object_id = 123::bigint)
         Rows Removed by Filter: 27942522
 Total runtime: 873796.113 ms
(5 rows)

The strange thing is that when I execute

SELECT * FROM "items" WHERE "object_id" = '123' LIMIT 1;

it returns 0 rows and I can do it in my code to optimize my web application's performance, but why it can be done by Postgres itself? I came to Postgres from MySQL and never seen such strange things there.

=====

I have found that it uses different query plan, different index, but why?

# EXPLAIN ANALYZE SELECT * FROM "items" WHERE "object_id" = '123' LIMIT 1;
                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..3.34 rows=1 width=63) (actual time=0.014..0.014 rows=0 loops=1)
   ->  Index Scan using items_object_id_operation_idx on items  (cost=0.56..2579.16 rows=929 width=63) (actual time=0.013..0.013 rows=0 loops=1)
         Index Cond: (object_id = 123::bigint)
 Total runtime: 0.029 ms
(4 rows)


texts: SQL Server - Must be first statement in query batch - what and why?

texts:
I cannot find a list of type of objects that while being created the command must be the first statement in query batch. So far I ve found that function/stored_procedure/view must be the first.

Does anyone know why this is required? Im sure there is a good reason, but it escapes me. The only reason for stored procedures that comes to mind is that weird interpretation of code behind END of the procedure. The SQL server still executes the code when the SP is called and had there been any more CREATE STATEMENTS the result would be mess.

Background: I m trying to create create/drop/disable/enable etc. scripts and need to know what to put into dynamic exec() for before execution. Yes I might wrap all statements in exec(), but then I would not learn anything.


texts: SQL Error 1452 : Cannot add or update a child row: a foreign key constraint fails

texts:
I am trying to install Zabbix by following this guide. I ran into the following problems when trying to run data.sql after running scheme.sql successfully

mysql -D zabbix -uzabbix -pPassword < /home/zabbix/zabbix-2.4.5/database/mysql/schema.sql

mysql -D zabbix -uzabbix -pPassword < /home/zabbix/zabbix-2.4.5/database/mysql/data.sql

  ERROR 1452 (23000) at line 2989: Cannot add or update a child row: a
  foreign key constraint fails ('zabbix'.'sysmaps_elements', CONSTRAINT
  'c_sysmaps_elements_2' FOREIGN KEY ('iconid_off') REFERENCES 'images'
  ('imageid'))

I cannot figure out why I would not be able to add or update a child row , it looks perfectly fine to me.

The scripts can be downloaded from here


texts: Full text catalog does not allow me to hit on singular forms

texts:
I have created a full text catalog for a column under database name -> "Storage" -> Full Text Catalogs. It is accent sensitive and "default catalog" is set to false. The language for the column "SearchData" is set to swedish.

I am using a stored procedure that calls the contains function like this:

CREATE PROCEDURE [dbo].[SearchPages]
    @Term varchar(200)
AS
BEGIN
    SELECT pc.SearchData from PageContent pc
    where contains(pc.SearchData, @Term)
END

When I search för "smörgås" (swedish för "sandwich"), I hit on "smörgåsbord", but not on "smörgåsar" (swedish for "sandwishes").

Apparently, it only hits on words that are compositions containing the search term, but not on plural forms.

Our customers want to search putting a singular form in the search box.

Is that possible to obtain?
It would also be nice if you could search for "goose" and find "geese".


texts: Transaction Log Backups Serial or Parallel?

texts:
We happen to be using SQL Server 2012 Standard Edition. I also happen to use Ola Hallengren's scripts to provide an easy, more flexible framework for doing backups and maintenance.

This question isn't so much about Ola's scripts as they are about a best practice. I realize the ultimate answer is "it depends on your company's requirements". But I am trying to seek the community's advice on how best to fulfill what I understand of our company's requirements.

I wish to set up transaction log backups for every 15 minutes. This way we hopefully lose no more than 15 minutes of data. Should I set up one job that uses ALL_DATABASES? or is it better to set up one job for each database and kick them all off in parallel? I ask, because I have the feeling based on how I see Ola's script functioning that the backups are kicked off in serial. The downside of serial would be that each successive backup waits until the other completes. This could potentially increase the amount of time between backups (ie, greater than 15 minutes). Plus my concern would be that a failure in one backup stops the others from happening, and I wouldn't want that to be the case. I would want the others to continue backing up.

So is it true that Ola's scripts execute in serial and also a failure stops successive backups?

And is it better to have a job for each database? or a single job that does all? My inclination is toward separate jobs, but I wish to understand what SQL Server DBA's in general tend to do.


texts: MySQL - Autoincrement does not increment sequentally if last row was deleted

texts:
I have a table which contains an auto incremented primary key id. If I delete the last row (highest id, for exampe id = 6) and insert a new row, the new id starts at 7. Which paramater I have to change that the primary key starts at 6?

CREATE TABLE animals (
 id MEDIUMINT NOT NULL AUTO_INCREMENT,
 name CHAR(30) NOT NULL,
 PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');

Result:
id  name
1   dog
2   cat
3   penguin
4   lax
5   whale
6   ostrich

DELETE FROM animals WHERE id = 6;
INSERT INTO animals (name) VALUES
('x');

Result:
id  name
1   dog
2   cat
3   penguin
4   lax
5   whale
7   x

Thanks for advice.


texts:
I would like to write a PostgreSQL stored function that essentially behaves like the stored procedures I know and love from MSSQL and MySQL where I can just wrap a query that takes no parameters and have it return that resultset without having to specify the format of the output and change that definition every time I update the query. Is this even possible in PostgreSQL ?

I've tried the following using PostgreSQL 9.2:

CREATE OR REPLACE FUNCTION test()
RETURNS SETOF record

Which gives me the following error:

  ERROR:  a column definition list is required for functions returning "record"

I've also tried:

CREATE OR REPLACE FUNCTION test()
RETURNS table ()

but apparently that's invalid syntax.


texts:
I have notification table (very large table). I need your help with the below scenario:

select all notifications for user id;
insert these notifications in notification_log table;
delete all these notifications from the notification table.

My thoughts :

create flag column in notification table and create on update trigger on it to do steps 2 and 3 in the above scenario. Draw Backs: trigger complicity o(n) and I am always select bulk of notification so it will be not nice for performance;
create simple SQL procedure to do the above scenario. Draw Backs: what if step of the scenario failed to commit the whole procedure will roll-back.

Can you help me to optimize this?


texts: Do you need external caching for a Postgres database?

texts:
I am currently building a server and have just written a database connection using JDBC, Jooq, and added a DAO layer, which also handles transaction management.  I remember that databases can cache data when it queries.

I want to know if Postgres already handles this case or would I need to build anything on top (like the DAO layer) to handle caching?

Which third party tools would you recommend if so?


texts:
I'm new to PostgreSQL triggers & functions and such.

I've got a table: keywords and each keyword should be unique. So when I try to insert a keyword that already exists, it should simply update the already existing keyword.

With the trigger below, inserting works fine, updating however takes very long - and I've always canceled it after a few minutes so I'm not sure what it would output.

Table: keywords

   Column   |            Type             |                       Modifiers
------------+-----------------------------+-------------------------------------------------------
 id         | integer                     | not null default nextval('keywords_id_seq'::regclass)
 keyword    | character varying(255)      | not null
 views      | integer                     | not null
 visits     | integer                     | not null
 created_at | timestamp without time zone | not null
 updated_at | timestamp without time zone | not null

CREATE FUNCTION update_if_exists() RETURNS trigger AS $keyword$ DECLARE result INTEGER;
BEGIN
    -- Check whether the keyword already exists.
    result = (SELECT count(keyword) FROM keywords WHERE keyword = new.keyword);

    -- If we've got a match run an update statement.
    IF result = 1 THEN
        UPDATE keywords
        SET
            views       = views + new.views,
            visits      = visits + new.visits
        WHERE
            keyword     = new.keyword;

        RETURN NULL;
    END IF;

    RETURN new;
END; $keyword$ LANGUAGE plpgsql;

CREATE TRIGGER id BEFORE INSERT OR UPDATE ON keywords
    FOR EACH ROW EXECUTE PROCEDURE update_if_exists();


texts: restrict data from select with Row-Level Security when updating

texts:
I want to create an ordered list of labeltext, so I wrote the following initial script:
Schema (PostgreSQL v10.0)

create table tbl1 (
  uid text primary key /* user id */
);
create table tbl2 (
  uid text not null default current_setting('custom.setting.uid', true)::text,
  order_id int not null,
  labeltext text,
  foreign key (uid) references tbl1 (uid) on delete cascade
);

INSERT INTO tbl1 (uid) VALUES ('boom');
INSERT INTO tbl1 (uid) VALUES ('bang');
INSERT INTO tbl1 (uid) VALUES ('bash');

alter table tbl2 enable row level security;
  create policy all_order on tbl2 for all
    using (uid = current_setting('custom.setting.uid', true)::text)
    with check (uid = current_setting('custom.setting.uid', true)::text);

create or replace function tfn(text) returns int as $$
declare
tmp int;
begin
insert into tbl2(order_id, labeltext)
  select coalesce(
      (select order_id
        from tbl2
          order by order_id desc limit 1
      ),
      0
    ) + 1, $1
    returning order_id into tmp;
return tmp;
end;
$$ language plpgsql;

set custom.setting.uid to 'boom';
show custom.setting.uid;
select tfn('foo');
select tfn('bar');
set custom.setting.uid to 'bang';
select tfn('baz');

tfn expected to get the greatest order_id for current user and insert some new labeltext with the increased order_id.
but however, the results look different:

**Query #1**

    select * from tbl2;

| uid  | order_id | labeltext |
| ---- | -------- | --------- |
| boom | 1        | foo       |
| boom | 2        | bar       |
| bang | 3        | baz       |

View on DB Fiddle

expect row created by user bang to have order_id = 1, but actually...
it's still able to select other user's order_id without restrictions

How to use RLS to limit the selection of order_id when updating?
If this isn't doable with RLS, any alternative?


texts: Select statement with dynamic columns where these columns are rows in another table

texts:
I want to write a select statement that with some fixed columns and dynamic columns.
here Dynamic Columns names are the rows in another table.

I have tried this but getting error. Please tell me where my error is

set @sql=(Select subject from tbl_subjects where C_Id=22);
SELECT
    ta.rollno,
    ta.StdNm,
    tc.C_Name,
    @sql
FROM
    tbl_cmarks tm,
    tbl_admission ta,
    tbl_classes tc
WHERE
    tm.Cid = tc.C_Id
        AND ta.rollno = tm.rollno
        AND tm.Cid = 22 and tm.Examid=9

Mysql showing:

  Error Code: 1242. Subquery returns more than 1 row

Could any one tell me how to do this in mysql?


texts: LEFT JOIN conversion to INNER JOIN, can't change FROM clause

texts:
I have 2 tables:
BASE is master and is linked with CHILD via field ID: Left outer Join / 1:N relation.
These 2 tables contain historised data, defined by 2 date-fields: start_date and end_date.
However the history in BASE is independent from the history in CHILD. E.g.:

BASE
1   1/1/2000    1/1/2010
1   1/1/2010    31/12/2012

CHILD
1   1/1/2000    31/12/2005
1   31/12/2005  1/1/2010

The goal:
For a given date, select the active record in both table and show them in 1 result-record.

Problem:
If there is no match in CHILD (e.g. 1/1/2011), I get “no rows” whereas it should show the data from BASE (and Null-values for the CHILD-fields)

This is my query:

SELECT
  BASE.*, CHILD.*
FROM
  BASE LEFT OUTER JOIN CHILD ON (BASE.ID=CHILD.ID)
WHERE
  BASE.ID  = '1'
  AND BASE.START_DATE  <=  TO_DATE('1/1/2011', 'DD/MM/YYYY')
  AND BASE.END_DATE  >  TO_DATE('1/1/2011', 'DD/MM/YYYY')
  AND CHILD.START_DATE  <=  TO_DATE('1/1/2011', 'DD/MM/YYYY')
  AND CHILD.END_DATE  >  TO_DATE('1/1/2011', 'DD/MM/YYYY')

I know that my LEFT JOIN will be converted to an INNER JOIN because of CHILD - columns in the WHERE clause. I also tried the IS NULL-trick that I found on this forum, but I’m still struggling with it.

Important remark:
Because of the technical setup, it is not possible to change the FROM clause, so I’m forced to find a solution via the WHERE clause …

Can you help?


texts: Calculate rolling sum over 7 consecutive day period on PostgreSQL

texts:
I need to get the rolling sum over a period of 7 days for each row (1 row per day).

For example:

| Date       | Count | 7-Day Rolling Sum |
------------------------------------------
| 2016-02-01 | 1     | 1
| 2016-02-02 | 1     | 2
| 2016-02-03 | 2     | 4
| 2016-02-04 | 2     | 6
| 2016-02-05 | 2     | 8
| 2016-02-06 | 2     | 10
| 2016-02-07 | 2     | 12
| 2016-02-08 | 2     | 13 --> here we start summing from 02-02
| 2016-02-09 | 2     | 14 --> here we start summing from 02-03
| 2016-02-10 | 5     | 17 --> here we start summing from 02-04

I need this in one query that returns the rows with 7-day rolling sum and the date of the last day of the range of the sum. For example, day=2016-02-10, sum 17.

So far I have this but it's not fully working:

DO
$do$
DECLARE
    curr_date date;
    num bigint;
BEGIN
FOR curr_date IN (SELECT date_trunc('day', d)::date FROM generate_series(CURRENT_DATE-31, CURRENT_DATE-1, '1 day'::interval) d)
LOOP
    SELECT curr_date, SUM(count)
    FROM generate_series (curr_date-8, curr_date-1, '1 day'::interval) d
    LEFT JOIN m.ping AS p ON p.date = d
    LEFT JOIN m.ping_type AS pt ON pt.id = p.ping_type_id
    LEFT JOIN m.ping_frequency AS pf ON pf.id = p.ping_frequency_id
    WHERE
        pt.url_slug = 'active' AND
        pf.url_slug = 'weekly';
END LOOP;
END
$do$;

I am using PostgreSQL 9.4.5. There could be multiple rows with the same date. If there is a gap (a day is missing), the 7-consecutive-day range will still be followed.


texts: Amazon RDS I/O requests - 1 query = 1 billable I/O?

texts:
I have an InnoDB database that I'd like to migrate onto Amazon RDS.

My current implementation, hosted on my own server, shows roughly 8M queries per month.

The RDS site say that
I/O Rate    $0.10 per 1 million requests

Does 1 I/O = 1 query?  ie, will I be billed $80/mo for this amount of usage + the RDS fees?


texts: SQL Server Pivot Multiple Column and Value

texts:
I have a sample table attendance look like this :

PERIOD  EMPL_NMBR   EMPL_NAME           DRPT     DAYX    [IN ]    [OUT]
201603  00001       John                QA       01      08:00    17:00
201603  00001       John                QA       02      08:00    17:00
201603  00001       John                QA       03
201603  00001       John                QA       04      08:00    17:00
201603  00001       Susan               SALES    01      08:00    17:00
201603  00001       Susan               SALES    02
201603  00001       Susan               SALES    03      08:00    17:00
201603  00001       Susan               SALES    04

I'd like to display the data from that table like this:

PERIOD     EMPL_NMBR     EMPL_NAME      1           2            3             4
                                   [IN]  [OUT]  [IN]  [OUT]  [IN]  [OUT]   [IN]  [OUT]
201603     00001         John     08:00  17:00  08:00  17:00  -      -     08:00  17:00
201603     00002         Susan    08:00  17:00   -      -    08:00  17:00    -      -

I can successfully pivot it using a single column and single value dynamically, but I get confused when I try to pivot multiple columns and values dynamically.

Can anyone help me?


texts: Benefit of having time dimension in a star schema?

texts:
What would be the benefits of having a time dimension in a star schema over having the time attributes in the fact table itself?

For example:

I have a transaction data with user information for each transaction, country where the transaction took place and dates of when it occured.

Option 1
Correct me if I am wrong, but this is probably the widely used approached and most recommended by many:

A transaction fact table containing transaction_ID (PK), user_id (FK) and country_id (FK), and date_id (FK)
User dimension containing user_id (PK) and the other user attributes, let's say name & phone_number.
Dates dimension that consists of date_id (PK), date, day, month, year, quarter.

Option 2
Something that I just thought about instead of choosing Option 1, but unsure about:

A transaction fact table containing transaction_ID (PK), user_id
(FK) and country_id (FK), date, day, month, year,
quarter.
User dimension containing user_id (PK) and the other user attributes, let's say name & phone_number.

What would be the benefits of having Option 1 over Option 2? I am not aware of the reasons why joining with another Date dimension would be a better option even though it is most widely used approach. Thanks a lot!


texts: Auto Seeding SQL Server AG Combined with Backup/Restore

texts:
Are there special conditions under which the below paragraph from MS Docs is true?  I tested this process on SQL Server 2017 Enterprise Edition and ended up with databases saying not synchronized/recovery pending and could not get them out of this state.

Combine Backup Restore with Auto Seeding

  It is possible to combine the traditional backup, copy, and restore with automatic seeding. In this case, first restore the database on a secondary replica including all available transaction logs. Next, enable automatic seeding when creating the availability group to "catch up" the secondary replica's database, as if a tail-log backup were restored (see Tail-Log Backups (SQL Server)).


texts: MySQL Replication Error, can't stop slave

texts:
When I run command SHOW SLAVE STATUS\G, I see

Slave_IO_Running : 'Yes'
Slave_SQL_Running : 'No'

What I've usually done to fix this is to run

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

It did not work this time.

When I ran STOP SLAVE;, I get SQL Error (1192): Can't execute the given command because you have active locked tables or an active transaction."

I've never seen this before. What can I do to get past it?


texts: How to read the actual value of @P0 in SQL server profiler

texts:
When I use SQL server profiler to trace the SQL statement flow using SP:StmtCompleted,
I can only read the variables as @P0, @P1, etc. like:
insert into CaseFollowup (createDate, duration) values (@P0, @P1)

Can I read the actual value of @P0, @P1?
Thanks for your help!


texts:
I'm new to partition switching and have used this basic syntax previously:

TRUNCATE TABLE TARGET

ALTER TABLE SOURCE SWITCH TO TARGET

I just came across an SSIS package where there is this syntax within a loop:

TRUNCATE TABLE TARGET

ALTER TABLE SOURCE
SWITCH PARTITION $PARTITION.MONTHFN(@MONTH) TO TARGET PARTITION $PARTITION.MONTHFN(@MONTH)

If all the partitions are being switched to the target table, is it necessary to do the switching by partition? Is this only because the source is already partitioned?


texts: Which normalization rule violates the column-per-language approach?

texts:
I've got the following table, which can store a title and content in German and English. I wonder, which normalization rule it violates?

I started going through the rules and stopped at NF2:

NF1: It's atomic.
NF2: Probably it breaks this rule, because the title and content are not directly dependent on the primary key. They depend on a key in the column name.

Is it correct, that this is against the NF2?


texts: How to resynchronize replication for a single database

texts:
I have two MySQL 5.5 servers in a master/slave replication setup. The server hosts about 10,000 databases, but the replication is messed up for only a couple of databases.

To fix the replication I could do a complete resynchronization by copying all the databases over to the slave server, but there are many databases and so much data that this would entail a long downtime for all the databases.

A better solution would be to re-synchronize just the two or three databases whose replication is broken. Can I do this in such a way that the only downtime would be for those two or three databases that have replication problems?


texts: How to Recover My Oracle 9i Database From Just The Datafiles

texts:
The state of the system when I started was that there was a single, working instance of Oracle Database 9i. I proceeded to install Oracle 11g choosing to Install Software Only. Upon completion, everything still worked fine. I created a new 11g listener on port 1522 called LISTENER, which was the same name as the 9i listener listening on port 1521. I don't know if things still worked at this point. Next, I started creating an 11g database which I gave the same SID as the pre-existing 9i database. The database creation process failed at first, and I didn't read the error messages. I ran it again, and no error messages came up. It was at this point when people started complaining about being kicked out of the database.

Upon investigation, I found that it was completely impossible to connect to the once flawless db instance. I tried a myriad of things to get connected to no avail. Finally, I decided I would be better off creating a separate instance and accessing the datafiles from there. This is what brings me here. I have tried creating an instance with the same datafile names and copying the originals into the directory, but I don't know what the original configuration is so that inevitably failed. If anyone has any advice on how I can gain access to the datafiles without knowing the original configuration, I would greatly appreciate it.

OS: Windows Server 2003 R2

Pastebin of 9i alert.log here.


texts: Configure SQL Server to associate a user login with a specific port

texts:
We have SQL Server instances that sit in our DMZ, and associated firewall rules to allow access to them from our corporate network.

The server is correctly listening on the configured ports, but now I need to configure the RDBMS to grant access to specific users on a specific port.

Apparently there is a T-SQL script that will do what I need, but I can't find it anywhere. Can anyone point me in the right direction or alternatively, show me how to do this using Management Studio's GUI?


texts: Joining tables based on time and distance: PostGIS with PostgreSQL?

texts:
I have a table with several thousand rows that contains time and position data. Rows with the same group_id have the same timestamp. Lets call this table "Small":

Small Table:
id  event_time                   group_id   item_id  position
1   '2018-06-21 18:35:01.631094+00' '123a'  1   '01010230...'
2   '2018-06-21 18:35:01.630881+00' '123a'  2   '01010044...'
3   '2018-06-18 10:35:01.630663+00' '321b'  1   '01015600...'
4   '2018-06-18 10:35:01.630305+00' '321b'  2   '01010031...'

I have another table (Table "Big") that has similar data columns (time, position, data1, data2 etc). The timestamps in this table continuous and are overlapping over the first table and there are 80+ million rows:

Big Table:
id  event_time                       Data1   position
1   '2018-06-21 18:45:01.631094+00' 'john'  '01013000...'
2   '2018-06-21 18:41:01.630881+00' 'joe'   '01016000...'
3   '2018-06-21 18:33:01.630663+00' 'john'  '01017000...'
4   '2018-06-21 18:30:01.630305+00' 'rory'  '01018000...'

I have geospatial and time indexes on the two tables.

What I would like to do is find the nearest matches between Big and Small and return data1, data2 and the differences in space and time. In short I'd like to find out that "john" is the best match to group '123a', item 2  and he was 100m and 2 minutes from it but 'rory' was closest to item 1 (5 minutes and 1 km or whatever).

I've tried a command similar to this but it is way too slow. It doesn't seem to be using the indexes.

SELECT
  big.id,
  small.id,
  st_distance(big.position, small.position) as pos_delta,
  (big.event_time, small.event_time) as time_delta,
  big.data1,
  small.item_id
FROM big, small
WHERE
  (big.event_time - small.event_time) < '2 hours'
ORDER BY login_sar_vessel.position <-> login_pos_report.position
LIMIT 1

Is there a way to possible first select data from big table for 2 hours around the timestamp associated with the group_id, find the big rows with  the smallest (big.position to small.position) distance and then repeat for each group_id? That seems a bit messy.

Oh, and the DB is postgres 9.6 with postgis 2.4.


texts: How can I copy a subset of columns from one InnoDB table to another, without locking?

texts:
I have a MySQL instance running on Amazon's RDS. This means that I cannot change any of the binlog settings.

I have a table source, created like this, which contains around 2 billion rows:

CREATE TABLE `source` (
   id INT PRIMARY KEY AUTO_INCREMENT,
   value1 VARCHAR(256),
   value2 VARCHAR(256)
);

I have another table destination, with the same columns, but the id is a BIGINT:

CREATE TABLE `destination` (
   id BIGINT PRIMARY KEY AUTO_INCREMENT,
   value1 VARCHAR(256),
   value2 VARCHAR(256)
);

The source table has gaps in the ID that I want to compact. I'd like to copy all rows in source to destination, without the id column, something like this:

INSERT INTO `destination` (value1, value2)
SELECT value1, value2 FROM `source`;

How can I accomplish this, without locking the source table? The copy itself is going to take an extremely long time, due to the size of the table, and I can't have it locked for that long.

I essentially want to run the above statement with READ UNCOMMITTED isolation, but this isn't possible due to my inability to change any binary log settings.


texts: Replacing NULL values in LEFT JOIN with MAX function

texts:
I have created a query that join two tables and returns a date of the last log in of a particular user. If a user logged in multiple times, the query returns the most recent date:

CREATE TABLE Table1 (
    USERID varchar(255),
    NAME varchar(255)
)
CREATE TABLE Table2 (
    USERID varchar(255),
    LOGIN_TIME varchar(255)
)
INSERT INTO Table1(USERID, NAME)
VALUES ('XYZ_1234', 'RAMBO'),
       ('XYZ_1235', 'ARNOLD'),
       ('XYZ_1236', 'BRUCE')
INSERT INTO Table2(USERID,LOGIN_TIME)
VALUES ('1234','17.07.17'),
       ('1234','20.07.17'),
       ('1235', '27.08.17')

SELECT T1.NAME, MAX (T2.LOGIN_TIME)
CASE WHEN T2.LOGIN_TIME IS NULL THEN 5 ELSE T2.LOGIN_TIME
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.USERID='XYZ_' + T2.USERID
GROUP BY T1.NAME

http://www.sqlfiddle.com/#!18/c119d/2

What I'm struggling with is to replace "null" values that are returned every time when a user exist in table1 and does not exist in table2.
For that I was trying to write a "CASE" function (line 2 in the link above).


texts: Audit Requirement - Capture Logins vs Database

texts:
I have a question, I hope you can give me some guidance based on your high expertise level.

There is an audit requirement where we have to provide login information per databases on a given server. Basically, I’m talking about DB users that authenticate externally to a DB (containment type is None).

How should I proceed to capture this event to answer the following question? “Give me the last login for the DB User XYZ authenticated in DB ABC?”

I couldn’t find anything in SQL Server Audit Specification (SQL 2012). It only works when the DB  has the containment type set to Partial. But I also need to capture logins for SQL 2005 and 2008 databases.

Your help is greatly appreciated, Thank you in advance.


texts: Dropping historic Partitions that are no longer required while in P2P replication

texts:
The Company I work for has a set of databases (largest just below 1Tb) on different servers - 2 in the US, 2 in Europe.

We run full peer-to-peer replication per database between the 4 nodes - so they can all take transactions (Insert/Update/Delete) and all have the data that the other nodes have gathered (within a variable latency - worst connection is on average about 30-40 secs).

The largest database carries data from early 2008 to today. All of this data is further replicated to Reporting nodes which hold ALL data.

I need to remove data at the Transactional nodes, up to 2013, to remove the Drive space deficit at the Transactional nodes, and therefore historic data will only be available at the Reporting nodes.

What is the best way to do this? Data is relatively manageable in that it is well partitioned (monthly - by partition, and then Yearly into separate Files/Filegroups). However, there is the problem of not being able to drop the Partitions while they are involved in replication and reading up on Partition switching - this is not allowed with that either. (Switching Partitions Prerequisites - Point 18)

As a full Production Environment I am trying to avoid anything that will affect replication - including resynchronisation (lot of data to resync, over large distances).

Does anyone have any good suggestions for how to perform this task?


texts:
I've written these two queries for same operation, on the basis of time complexity, i want to know which one is efficient.

select Fname, Lname, Address
from (select * from department d, employee e where d.Dnumber = e.Dno) as a
where a.Dname = 'Research';

Edit: on the basis of my first comment i assume that the query in from clause will work as temporary table/view which i name as "a", and contain all the columns of both the tables and i use it.(and i don't know it's a efficient way to do it.)

or

select Fname, Lname, Address from employee
where Dno = (select Dnumber from department where Dname = 'Research');

Or there could be more efficient way to do it.
Thanks.

Edit2:

SELECT Pnumber, Dnum, Lname, Address, Bdate
FROM employee
JOIN department d ON d.Mgr_ssn = employee.Ssn
JOIN project p ON p.Dnum = d.Dnumber
WHERE p.Plocation = 'Stafford';

i've this argument, can you tell me what's the flaw in this.,

let's there be 1000 rows in each PROJECT, DEPARTMENT, EMPLOYEE table,

then in the above query, compiler will choose in what order the join has to be applied (ABC, ACB,BCA, BAC,...) choosing the efficient one. But to select the best of all for the first, then second,... it has to perform all and compare it with each other, plus after that the order of the tuples involved would be same(not sure, maybe if it goes for EMPLOYEE, DEPARTMENT first it only have 100-150 rows, that entirely depends upon tables...right???). I never worked on live project situation, maybe there are advantages of JOINS(which there are, that why it's in advance release) which i'm not able to see. But i'm not convinced.

then in the below query let 250 comes out to be success, which then CARTESIAN PRODUCT with DEPARTMENT table and produce 2,50,000 tuples from which let's say 5,000 be RESULT, which then again CARTESIAN PRODUCT with 1000 tuples to produce 500,000.

SELECT Pnumber, Dnum, Lname, Address, Bdate
FROM (SELECT Pnumber, Dnum, Mgr_ssn
      FROM department d, (SELECT Pnumber, Dnum
                          FROM project where Plocation = 'Stafford') p
      WHERE d.Dnumber = p.Dnum) q, employee e
WHERE q.Mgr_ssn = e.Ssn;

So, my question is JOIN is to write query simply, and let compiler decides what's the efficient order. Whereas in the below one we've done the almost work for compiler.

and, i've one more question, WHERE clause applies only on one relation?, or let me rephrase, first FROM(along with all the JOINS) clause execute then where clause execute coz in that case the query with JOINS will be very expensive.

Thanks.


texts: MySQL speed up aggregation query on table

texts:
DB: Amazon RDS,
OS: Linux,
2 vCPU,
Memory: 4GB

I have a table with almost 10M rows of data. Below is the table structure:

CREATE TABLE `meterreadings` (
 `Id` bigint(20) NOT NULL AUTO_INCREMENT,
 `meterid` varchar(16) DEFAULT NULL,
 `metervalue` int(11) DEFAULT NULL,
 `date_time` timestamp NULL DEFAULT NULL,
 PRIMARY KEY (`Id`),
 KEY `meterid` (`meterid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Another table which stores device IDs (around 120 rows of data)

CREATE TABLE `devices` (
`Id` bigint(20) NOT NULL AUTO_INCREMENT,
`meterid` varchar(16) DEFAULT NULL,
`location` varchar(8) DEFAULT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `meterid_UNIQUE` (`meterid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

I need to get 15 min aggregated data  for all 'meter ids' within a time frame. I use this query -

SELECT AVG(metervalue) as value
     , DATE_FORMAT(date_time, "%d %b %Y %H:%i") as label
FROM meterreadings
WHERE meterid IN (SELECT meterid from devices)
  AND date_time BETWEEN '2018-06-23' AND '2018-06-24'
GROUP BY DATE(date_time), HOUR(date_time), MINUTE(date_time) DIV 15
ORDER BY date_time ASC;

This takes almost around 30 to 50 seconds to execute.

EXPLAIN on this query returned me this:

'1', 'SIMPLE', 'devices', 'index', 'meterid_UNIQUE', 'meterid_UNIQUE', '19', NULL, '125', 'Using where; Using index; Using temporary; Using filesort'

'1', 'SIMPLE', 'meterreadings', 'ref', 'meterid', 'meterid', '19', 'devices.meterid', '308', 'Using where'

I have 'Id' as the primary key in meterreadings table and there is an index on 'meterid' as well. How can I improve my query speed?


texts:
I see 5 Kqps on my DB server during 5 mins interval on zabbix dashboard. But sum of all max queries(Delete/select/Insert/Update/Replace) during that interval come out to be 1300.
Not sure how come max 5kqps is displaying but sum of all max is 1300 ? My objective is to find which specific DML(insert/update/select/delete) is causing high QPS here  and get some direction from here ? Another point is to understand is there anything else that can cause high QPS apart from these DML so that I can move in right direction. I am bit confused how come Max QPS can be some of individual DML QPS or I am missing anything here ?
Also IOPS during that shows 1Kops, if each query is is even hitting disk, shouldn't it be close to 5Kops ? Where can be the gap ?


texts: Designing a database for a video game business domain with multiple many-to-many relationships

texts:
I am relatively new to database design, and I decided to make my own hypothetical database for practice. However, I am having trouble modeling and normalizing it, as I esteem that there are numerous many-to-many (M:N) relationships.
General scenario description
The database is meant to retain data about various People that have worked on the Zelda series. I want to keep track of the Console(s) that a Game can be played on, Employees that have had a part in the Games development, the Jobs the Employee had (many Employees worked on different Jobs across multiple Games), etc.
Business rules

Multiple Employees can work on multiple Games.
Multiple Games can be on the same Console.
Multiple Consoles can be a platform for the same Game.
Multiple Employees can have the same Job.
An Employee can have multiple Jobs.
A Game can have multiple Employees.
A Game can have multiple types of Jobs in it's development
Multiple Games can have the same type of Job attached.
A Console can have multiple People working on it.
A Person can work on multiple Consoles.

Attribute names and sample values

Employee Name, which can be split into First and Last (for example “John” and “Doe” )
Game Title (for example “Ocarina of Time”)
Job Title (for example “Level Design”, “Director”, “Composure”, “Level Designer”, “Programmer”, “Localization”, etc.).
Console Name (for example “Game Boy Advance”)

The issue
So far, it seems no matter what I design there are data redundancies and M:N relationships between the entity types of interest everywhere. However I feel that database designers must run into this kind of problem all the time, so there must be a solution.

Note: I am well able to find the data to fill the table, the problem is organizing it into a database with tables in a normalized form.


texts: SQL Service failed to start master.mdf is in use

texts:
I've got this issue which has me baffled at the moment.

When I came in this morning there were 2 instances on a server with 4 SQL Server instances who had a backupthread process which got stuck when I killed this SQL Server process this stayed in the killed/rollback state. When trying to kill the process which was the culprit this showed to be a process which was non-existing. So after some googling and consulting with co workers we thought it best to restart the SQL Server instance.

So far so "good".

When stopping and then starting the SQL Server service again, the service refused to start. with the following error: The process cannot access a file because it used by another process.

And it was using the master.mdf file.

When checking which process was using the file it was the SQLservr.exe process which was using the file. (Using process explorer https://technet.microsoft.com/en-us/sysinternals/bb896653.aspx)

When I looked over to services, the service of the instance was successfully stopped. The Process however was still running according to task manager and process explorer although it was not using any resources.

When trying to kill this process ID it gave me an access denied error, when trying to kill it through command line with using taskkill /pid XXXX /F it still gives me access denied error, even when running as administrator with local admin and domain admin account.

The only shortterm solution was rebooting the Server, this solved the issue. Until I tried to restart the service again. When we got the same problem again.

EDIT: They rebooted again without my knowledge, now the issue cannot be recreated

SQL Server is patched until the latest version, however the Windows server has not been patched since 2013.

We are running on SQL Server 2012 and Windows server 2012

Is this a known issue with windows server? Is this a SQL Server issue? Any guidance or information would be helpful.


texts: Isolating a Distributed Replay environment

texts:
I'm a little nervous replaying a captured trace/workload for performance analysis...

I'm using an isolated service account to run the SQL Services, including the Distributed Replay controller & clients....Is that all that is needed to ensure replayed transactions won't jump across to the prod database?

IE, is it possible that procedures or CLR objects could reach out and connect to another database, even though no linked servers are defined?

TIA!


texts:
I am configuring database mirroring on SQL Server 2012.  After configuration I am getting the following error when attempting to start the mirroring session:

tcp server cannot be reached or does not exist.  Check the network address name and the ports for the local and remote are operational.
an Exception occured while executing a Transactional-SQL Statment.


texts: High WRITELOG wait on Azure SQL Database

texts:
My WaitStats analysis of our Azure SQL Database is showing WRITELOG and HADR_SYNC_COMMIT as counter the highest wait. This is a Premium 250 eDTU resource.
Script
SELECT   TOP (10) wait_type,
                  CAST (([wait_time_ms] / 1000.0) AS DECIMAL (16, 2)) AS [WaitS],
                  CAST (100.0 * [wait_time_ms] / SUM([wait_time_ms]) OVER () AS DECIMAL (16, 2)) AS [Percentage]
FROM     sys.dm_db_wait_stats
ORDER BY [Percentage] DESC;

I cannot find much information on how to address this in AZure SQL database.
Any help is appreciated.
Thanks


texts: How to connect to a remote Oracle server from Windows machine using PL/SQL Developer?

texts:
I understand that I need to configure database address in the TNSNAMES.ORA file but where should I put this file?

UPDATE

I defined the $TNS_ADMIN variable, created the TNSNAMES.ORA file, added new connection and restarted my machine. Unfortunately, the Database list in Oracle Logon remains empty.

However, the following error occurred when I was trying out various options in PL/SQL Developer:

  Initialization error
  SQL*Net not properly installed

  OracleHomeKey:
  OracleHomeDir:

What is SQL*Net?


texts: I want to backup a huge number of records and delete them, but I don't have complete access to database server so I can't run my script on the server. because it takes so long I need to submit jobs(say REINDEX for example) and then turn off my computer. Is it possible to do something like this?


texts: How to get number of active users with one SQL-Query?

texts:
I am running a site similar to craigslist where users can create classifieds and write/reply messages. In order to get an idea of activity I would like to get the amount of active users per month.

The idea is to define an active user as one who has either registered, created/edited a classified or written a message to another user within the last 30 days.

There are 2 tables:

classifieds

advertiser_id | date_created | date_edited

messages

sender_user_id | recipient_user_id | timestamp | status

I am looking for an output like this:

year; month; n_active_users; n_users_created_classified; n_user_sent_msg; n_user_read_msg

A message is marked as read by status=4

The ids are all referring to the user table. So the same sender_user_id and advertiser_id indicate the same user.

I am not sure if this is even possible with just one query but I guess it is, I just don't know how.

From another post I have learned that this could be pointing into the right direction but I am still stuck:

SELECT
   EXTRACT(YEAR FROM date_created) AS yr,   -- for each year
   EXTRACT(MONTH FROM date_created) AS mth, -- & month combination
   count(*) AS advertiser_id
FROM
   classifieds c
GROUP BY yr, mth
ORDER BY yr DESC , mth DESC

I am using MySQL Galera 5.6.27-1trusty-log - (Ubuntu), wsrep_25.12


texts: How to insert values into a new column which is added into an existing table?

texts: Please answer me if you know how to insert values into a new column added into an existing table. Your answer will be highly appreciated


texts: Child records under the parent record in SQLite

texts:
I have two tables for Billing, one Bill_Master and other is Bill_Detail. The records in both tables are as follows...

**BILL_MASTER**
id    party    bill_amount
1      abc      500
2      def      600

**BILL_DETAILS**
mstr_id    sr_no    perticular    amount
 1          1        lunch box     100
 1          2        water bag     400
 2          1        pencil boxes  300
 2          2        a4 papers     100
 2          3        staple pins   200

Now I want to make a result table as per below

**RESULT_TABLE**
mstr_id    party      billamount
 1         abc           500
           lunch box     100
           water bag     400
 2         def           600
           pencil boxes  300
           a4 papers     100
           staple pins   200

How can I do it?


texts: How to find the system impact of a query in postgres

texts:
When an query is executed in postgres - pgadmin4 .
Can i know the query to check the performance of the query using any system tables ?

example :

Time taken
Query run by
3.CPU impact
any blocks
etc..


texts:
With MS SQL Query Browser I can run multiple queries at the same time and each result set will display in it's own window.

Does PostgreSQL/pgAdmin have this functionality? Plugin perhaps?


texts: How can I catch the output of DBMS_OUTPUT.PUT_LINE when executing a block using [Oracle.DataAccess.Client]?

texts:
In my oracle Database if have the following table:

Create table Test_call_count (
    count number(10)
);
insert into Test_call_count values (0);

Now I can run the following PowerShell script

if ($ora_loaded -eq $null)
{
    $ora_loaded = [System.Reflection.Assembly]::LoadWithPartialName("Oracle.DataAccess")
}

$ConnectionString = "Data Source=YOUR_TNS;User ID=YOUR_ID;Password=YOUR_Password"

function Execute-NonQuery
{
    param (
        $sql
    )

        $conn = new-object Oracle.DataAccess.Client.OracleConnection
        $conn.ConnectionString = $ConnectionString
        $cmd = new-object Oracle.DataAccess.Client.OracleCommand($sql,$conn)
        $conn.open()
        $cmd.ExecuteNonQuery()
        $conn.close()
}

Execute-NonQuery "begin update  Test_call_count set count = count + 1;DBMS_OUTPUT.PUT_LINE('Test...'); end;"

Looking at the the database I can verify, that count increments with each call.

But is there a way to extend this, so that I can get the data send to DBMS_OUTPUT ?


texts: How to convert full-text search highlighted fragments into rows in PostgreSQL full-text search?

texts:
I am using PostgreSQL 10.  I have a "booktbl" table which has 4

book_name
paragraph_title
content
ts_content

ts_content column is a tsvector column. When Iexecute this query:

select
    book_name ,
    paragraph_title ts_headline(content,to_tsquery('english','happy'), 'HighlightAll=true MaxFragments=100 FragmentDelimiter=$')
from bookstbl
where ts_content @@ (to_tsquery('happy')) ;

I got a single row as a result which has a "ts_headline" column with all highlighted text search fragments. I want to convert all these fragments to individual rows. Like this:

book    para_title1     fragment1
book    para_title2     fragment2

How can I achieve this in PostgreSQL full text search?


texts: Oracle database link via ODBC to SQL Server does not recover automatically

texts:
I have a test environment with an Oracle 10XE database which is linked to an SQL-Server 2005 via an ODBC data source. For reference here I named the connection MSSQL_LINK. The production environment will have an Oracle 11g and an SQL-Server 2008. The connection once set up works fine.

As a test of error conditions in production we unplugged the network cable. This broke the connection. A subsequent executed statement (e.g. select * from test_table@MSSQL_LINK;) failed with an error ORA-28500, which is a wrapper for the original ODBC error:

[Generic Connectivity Using ODBC][S1000] [9013]General error in nvITrans_BeginT - rc = -1. Please refer to the log file for details.
ORA-02063: preceding 2 lines from MSSQL_LINK

This was somewhat expected. Unfortunately plugging the cable back into the machine does not lead to an automatic reconnect of the link. The problem persists until I drop the link and create it anew with the commands

drop database link MSSQL_LINK;
create database link MSSQL_LINK connect to "user" identified by "passwd" using 'MSSQL_LNK';

Also the same happens if I just disconnect the cable for a short time and reconnect it before I execute any statements over the link. The interim breakup is still detected by the ODBC driver but not fixed on its own.

As mentioned by Ste, creating a link will tell Oracle only about the connection parameters like host, user name, password, but not create a connection by itself. The link can be created without the network cable connected to the Oracle machine. If I create the link without the cable connected, then connect it to and execute a simple select on the linked server, the same Oracle error occurs.

Obviously in production mode this is an unacceptable situation which needs to be fixed, preferably by an automatic procedure.

Three questions do I have about this:

Is there a way to test a database link other than e.g. a select on a test table which resides on the linked server?
Is dropping an re-creating the database link on my oracle machine the best option I have when this problem occurs?
What is the best automatic procedure for recovering from this situation.


texts: How to force SQL developer to use imp/exp command while import/export

texts:
While importing or exporting oracle data dump using SQL Developer by default it is using impdp command for import and expdp command for export. But I want to import a dump which is exported using exp command, and for that i need to force SQL developer to use imp command instead of impdp command. And same for export.

Pleas help me if anyone knows how to in-force SQL developer to use imp for import instead of impdp and exp for export instead of expdp


texts: How to create development/test environment of TB reporting database in SQL Server

texts:
I am working on a couple large reporting databases with lots of reporting and analytical queries and many ETL jobs. When I make changes I usually do in in production, be that changes in indexing or the code.
Just had a minor accident after changing some code so I am thinking to start creating a development/test environment.
But the databases are huge with some huge tables. The databases are located on a huge server with lots of CPU cores and memory. How can I make a light test environment on my own computer in a developer edition?
Another option would be to create a test environment on the production server which is a data warehouse server (i.e. there are lots of ad hoc queries anyway). Is this a better approach? I would just have to restore a backup and name the database something else...
There are many dependencies in the databases, so scripting out all the objects manually is too much work. E.g. some stored procedures use around 100 tables...
So my question is: how can I stop making all my changes directly in production?
Thanks


texts: Why can a select statement acquire more than 1 Sch-S lock on one table?

texts:
I have a repeating deadlock in my production environment. I cannot reproduce it in staging.

Script 1 is a scheduled data import procedure.

-- Create and populate new_TableImported

if object_id('old_TableImported') is not null drop table old_TableImported
set transaction isolation level serializable;
set xact_abort on;
begin try
    begin tran
        if object_id('TableImported') is not null exec sp_rename 'TableImported', 'old_TableImported'
        exec sp_rename 'new_TableImported', 'TableImported'
    commit
end try
begin catch
    if (xact_state() <> 0) rollback tran;
    throw;
end catch
set transaction isolation level read committed;
if object_id('old_TableImported') is not null drop table old_TableImported

Script 2 is a scheduled data export procedure: just select from a view.

CREATE VIEW [export].[vSomeDataNeededOutside]
AS
select
    t.*,
    e.SomeField
from dbo.OneOfMyTables t
    left join (
        select ...
        from TableImported
        group by ...
    ) e on ...
where ...

According to data collected with SQL Server Profiler.

Select (2) starts. Sch-S lock is obtained on TableImported.
Data import (1) runs up to sp_rename block. Sch-M lock is requested, and the query waits.
(2) for some reason requests additional Sch-S lock on TableImported.
Deadlock. (1) waits (2) to release the first Sch-S lock, (2) waits (1) to acquire the second Sch-S lock.

What is going on? Why do select try to obtain the second lock of the same type on the same table?


texts: Insert Array of JSON into Postgres Table

texts:
PART 1:

I am using Postgres 9.5, and I am trying to figure out how to INSERT into a postgres table using an array of JSON. I have created a table with the following commands:

CREATE TABLE inputtable (
data_point_id SERIAL PRIMARY KEY NOT NULL,
chart_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
properties jsonb NOT NULL
);

The json data format looks like this:

[
    { col1: a, col2: 5, col3: 1, col4: one},
    { col1: b, col2: 6, col3: 2, col4: two},
    { col1: c, col2: 7, col3: 3, col4: three}
]

Due to some changes, I now must be able to insert a json object per row based on user_id. In the end, I want the output to look like this:

data_point_id  | chart_id | user_id |  properties
---------------+----------+----------+--------------
    1          |    1     |     1    | { "col1": "a", "col2": 1, "col3": 1, "col4": "one"}
    2          |    1     |     1    | { "col1": "b", "col2": 2, "col3": 2, "col4": "two"}
    3          |    1     |     1    | { "col1": "c", "col2": 3, "col3": 3, "col4": "three"}

I tried to INSERT the data into the table by using unnest, where :

INSERT INTO inputtable (user_id, chart_id, properties)
    SELECT (1, 1, unnest('{ "col1": "a", "col2": 1, "col3": 1, "col4": "one"},{ "col1": "b", "col2": 2, "col3": 2, "col4": "two"},{ "col1": "c", "col2": 3, "col3": 3, "col4": "three"}')::json)

but I get an error about type ERROR:  could not determine polymorphic type because input has type "unknown".

PART 2:

I would also like to know how a table like this can be updated. For example, I make changes to the data point, in the JSON format, and want to change the properties, and I expect an output of:

data_point_id  | chart_id | user_id  |  properties
---------------+----------+----------+--------------
    1          |    1     |     1    | { "col1": "a", "col2": 6, "col3": 7, "col4": "eight"}
    2          |    1     |     1    | { "col1": "b", "col2": 10, "col3": 11, "col4": "twelve"}
    3          |    1     |     1    | { "col1": "c", "col2": 3, "col3": 3, "col4": "new"}

Using data like so:

[
    { col1: a, col2: 6, col3: 7, col4: eight},
    { col1: b, col2: 10, col3: 11, col4: twelve},
    { col1: c, col2: 3, col3: 3, col4: new}
]

How do can this be accomplished? I am thinking the part 2 question can be solved by jsonb_populate_recordset, but I am not sure. Using Postgres for JSON is new to me, but it looks very powerful and I appreciate the assistance in helping me figure this out!


texts:
How can I perform a distinct when the system displays millisecond in my timestamps? I just need the date.

My query is like this:

select distinct orders.client_po,
                orders.order_code,
                orders.created_at,
                carts.checkout_at
            from orders
 left outer join carts on carts.code = orders.cart_id

...carts.checkout_at is the same for all my orders but orders.created_at can be different. Some orders can be created the next day, the next minute and I want to know that but when they are created during the same second I get too much duplicate information.

Format of my dates is timestamp without time zone.


texts: Which would be a recommended approach for migrating a Postgresql database from one server to another?

texts:
We are moving out of our current dataserver based on Santiago, Chile to Amazon (AWS) in North Carolina, all other servers have been successfully migrated with no problem, but our "migration consultant / provider" has been struggling migrating our Postgresql server for weeks no with no relevant progress.

Our database is about 700GB big, and they seem to have speed / bandwidth problems, but I'm guessing there's much more than that.

They are actually using a product named "Double Take" to achieve the migration, but I've just read about Slony in another question when I started writing this.

Is there any way to track down / measure daily changes in database content? Thousands of records are added and several others are expired on a daily basis.

Any suggestions on perfecting the migration process would be appreciated.


texts:
I am converting Access query to SQL server.

I want to convert below lines to SQL

1. Format (210.6, "Standard")
2. Format (210.6, "#,##0.00")

How do i convert it to SQL query.

I have tried with below, but still not able to find the solution.

For the first query, i found below solution, which is correct

    1. CONVERT(varchar, CAST(tSRO.OutputF11 AS money), 1)

Now, for second query, i do not know what i have to do.

Please help me on this.


texts: Why can't trigger be dropped from database?

texts:
One of the databases I manage is provided by my company's vendor which we restore in order to pull data out for our data warehouse. I'm writing a stored procedure to go through the database users and remove all of the users from the vendor. There is one user, we'll call it userx, which can't be removed because there's a trigger which is set to execute as this user, we'll call it trg_write_something. When I run:

DROP TRIGGER trg_write_something

I get these error messages:

  Msg 50000, Level 16, State 1, Procedure PreserveTrigger, Line 18
  Trigger [trg_write_something] cannot be dropped from the database.

  Msg 3609, Level 16, State 2, Procedure trg_write_something, Line 4
  The transaction ended in the trigger. The batch has been aborted.

If I try to alter the trigger I get the same error.


texts: Relational-JSON overlapping with association

texts:
I have a composed class called sensor that is linked to a main class Node such that:

class node is linked with another node to make a communication that produce an event
class sensor can as well detect events

So I thought of putting the sensor data inside of the table node as a JSON, but the  association that allows me to detect makes doesn't apply to any schema I have seen before.

Is it a good idea to make the change that way or will that be more tricky to get the needed data?

See the full UML below; but my point concerns only the sensor.

Do you think I can convert that in R-JSON?

If you spot any place other than position that it would be suited to use R-JSON, I would like to think of the option.


texts:
I was reading book "Database System Concepts" (6th edition by Henry F Korth Abraham Silberschatz, S Sudharshan

There is slight confusion between database system and DBMS
Are both term same or different?

A database system is a collection of interrelated data and a set of programs that allow users to access and modify these data.

A database-management system (DBMS) is a collection of interrelated data and
a set of programs to access those data.

What I understand---
A database system (DBMS) means a set of programs to access and modify data.

A database-management system (DBMS) means a set of programs to only access data.


texts: MongoDB Indexing - What happens if I don't index all necessary fields?

texts:
Let's say I have a collection with 20 fields and a query that uses 5 of them, to find the documents I need.

3/5 of those fields are always used in the query, 1 for equality comparison, 1 for range comparison (less than) and the other for sorting. The other 2 are being used conditionally, so sometimes they will and others they won't.

Those 2, should I index them? If I don't, will I still benefit from indexing the first 3?


texts:
How I can get count results with cross join?

Example cross join:

SELECT t1.firstname, t2.lastname
FROM table1 t1 CROSS JOIN table1 t2


texts: PostgreSQL: The database cluster initialization failed

texts:
Both C:\PostgreSQL and C:\PostgreSQL\data have postgres user with full access and admin rights.

I ran postgresql-9.1.2-1-windows.exe from postgres user as admin. Target C:\PostgreSQL

Every way I try, I get "The database cluster initialization failed."

Questions

Is it OK to run everything w/o being a Windows Service?
Is there a work-around to install as Windows Service?

I am trying to setup PostGIS to work with GeoDjango.

I was able to manually install PostGIS. New to PostgreSQL and I am having a crisis of confidence over all of this. Coming from MySQL to PostgreSQL for the first time.

Pertinent log output from C:\Users\Larry\AppData\Local\Temp\install-postgresql.log:

WScript.Network initialized...
Called IsVistaOrNewer()...
    'winmgmts' object initialized...
    Version:6.1
    MajorVersion:6
Ensuring we can read the path C: (using icacls) to Larry:
    Executing batch file 'radA3CF7.bat'...
    Output file does not exists...
Called IsVistaOrNewer()...
    'winmgmts' object initialized...
    Version:6.1
    MajorVersion:6
Ensuring we can read the path C:\PostgreSQL (using icacls) to Larry:
    Executing batch file 'radA3CF7.bat'...
    Output file does not exists...
Called IsVistaOrNewer()...
    'winmgmts' object initialized...
    Version:6.1
    MajorVersion:6
Ensuring we can read the path C:\PostgreSQL\data (using icacls) to Larry:
    Executing batch file 'radA3CF7.bat'...
    Output file does not exists...
Called IsVistaOrNewer()...
    'winmgmts' object initialized...
    Version:6.1
    MajorVersion:6
Ensuring we can write to the data directory (using icacls) to  Larry:
    Executing batch file 'radA3CF7.bat'...
    Output file does not exists...
Failed to ensure the data directory is accessible (C:\PostgreSQL\data)
    Executing batch file 'radA3CF7.bat'...
    Output file does not exists...
Called Die(Failed to initialise the database cluster with initdb)...
Failed to initialise the database cluster with initdb

Suggestions?


texts: Statistics not collected on Insert-Select

texts:
Based on the information here, I expected the statistics to be automatically collected during an Insert-Select operation on a new table in Oracle. However, I don't see any table-level statistics gathered after an insert-select operation.
I have set the parameter _optimizer_gather_stats_on_load to TRUE.
As per requirement (this only works for new tables), I created a new table. There are no rows prior to insert-select and 500 rows post insert-select operation.
Is there something else I am missing?


texts:
When opening the query store for the first time on any give day, it will take a VERY long time to output data.

I took a SQL script from this redgate blog article :

Top 10 most expensive queries based on average runtime

SELECT TOP 10
  qt.query_sql_text,
  CAST(query_plan AS XML) AS 'Execution Plan',
  rs.avg_duration
FROM sys.query_store_plan qp
INNER JOIN sys.query_store_query q
  ON qp.query_id = q.query_id
INNER JOIN sys.query_store_query_text qt
  ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_runtime_stats rs
  ON qp.plan_id = rs.plan_id
ORDER BY rs.avg_duration DESC;

After leaving this query running for 11 minutes I canceled it as it wasn't returning anything. Why is that so? Opening the query store through the GUI will result in the same issue.

In this screenshot you can see how cpu-intensive this query is, why is that so ? Did I improperly configure my query store, is there anything more to do than simply enable the query store and query away ?


texts: What is meant by a blind write in a schedule?

texts: I know that every conflict-serialisable schedule is view-serialisable but the converse is not true. I read in a webpage that when a schedule is view-serialisable but not conflict-serialisable then there exist some blind writes. So what is a blind write?


texts:
Is it possible to automate the managing of indexes on my database? I'm looking to see if there are any scripts or programs that can automate this process. Would it be possible to use stats generated by SQL to determine what indexes could help performance. Then use another script based on stats to determine which indexes are over kill and can be removed.

We don't have a DBA my main goal is to try to manage based on stats and not black magic.  We just want to do the best we can with our resources (time)


texts:
We are working on a project which requires import data from Excel spreadsheet on daily basis. The data will be import from pre-define template and what we're thinking, first we will upload data in temporary table and perform cleaning operation (removing unnecessary information, add new rows in parent table and get their FK, etc.).

I need to know is there any tool or utility available which can reduce our efforts.
What is the best way to INSERT bulk record from different sources (mostly from Excel spreadsheets)?


texts: How do I add composite MySQL Indexes with denormalization and JOINS?

texts:
I have the following tables:
CREATE TABLE base_event (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    created_by ... -- some columns
);

CREATE TABLE transaction_events (
    event_id BIGINT UNSIGNED NOT NULL,
    transaction_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    merchant_id BIGINT UNSIGNED NULL DEFAULT NULL,
    merchant_city VARCHAR (...) NULL DEFAULT NULL, -- Denormalize
    customer_id BIGINT UNSIGNED NULL DEFAULT NULL,
    customer_ip_address VARCHAR(...) NULL DEFAULT NULL, -- Denormalize
    ...
    FOREIGN KEY (event_id) REFERENCES base_event(id),
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (merchant_id) REFERENCES merchants(id),
);

CREATE TABLE customers (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    customer_ip_address VARCHAR(...) NULL DEFAULT NULL,
    ...
);

CREATE TABLE merchants (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ...
);

And my SELECT:
(SELECT t.*, c.name AS customer_name ...
FROM transaction_events t
JOIN customers c ON t.customer_id = c.id
JOIN merchants m ON t.merchant_id = m.id
WHERE t.customer_ip_address = 'abc' AND t.transaction_time > 'abc')
UNION DISTINCT
(SELECT t.*, c.name AS customer_name ...
FROM transaction_events t
JOIN customers c ON t.customer_id = c.id
JOIN merchants m ON t.merchant_id = m.id
WHERE t.merchant_city = 'abc' AND t.transaction_time > 'abc')

And my indexes are:
ALTER TABLE transaction_events
ADD INDEX index_1 (customer_ip_address, transaction_time),
ADD INDEX index_2 (merchant_city, transaction_time);

My query is in this form to avoid OR.
I've denormalized to a degree for the sake of the indexes.
I do not need to reference my base_event table for this query.
The relation with transaction_events to customers and merchants is not 1-to-1 but 1-to-0-or-1.

My questions:

I can get rid of the wildcard, but transaction_events has around 20 columns (Would it help creating any further indexes to speed up the query?
Do I need to put any other composite indexes (that potentially reference my FKs) to further improve this query?


texts: MySQL Partioning: Is there a performance tradeoff between number of partitions and size of each partition?

texts:
I have a large table (several 100 million rows) that I would like to efficiently partition. My question is whether there is a tradeoff between partition size and number of partitions. As far as I understand, most queries on a column used in the partition will be faster because the query will (for most queries) only have to search within the partition applicable to the query. Thus, it would make sense that, in order to maximize efficiency, you should divide a large table into the maximum number of partitions, therefore, making each partition as small as possible. In the case of MySQL, this means 1024 partitions. But is there any performance drawback to having a large number of partitions? Is so, how does one find the optimum number of partitions?

Note: There is a somewhat similar question already, but only one answer, which (from my perspective) misses the mark. So I will state the question in my own way...hopefully it is more clear


texts: DatabaseMail didn't start when an email is queued

texts:
This is about SQL Server 2005.  I have configured SQL agent jobs to send an email when it failed.  So far I have received email reliably.

However,  I have an weird problem where an email would get queued but not delivered immediately.  As an example, a job failed on April 1 at 9 AM.  When I query

SELECT * FROM dbo.sysmail_mailitems

It will show the error mail has a send_request date on 2015-04-01 09:01:30.000, however, I just received the email today, and the sent_date is shown as 2015-04-02 10:10:51.000.

I checked the database mail log, and the database mail process did NOT start on 2015-4-1.  It is only started on 2015-04-02 10:10:51.000, and at that time I open the Database Mail dialog box (I did not change anything - I just opened it to check a few settings).

The database mail log (sysmail_event_log)

2015-03-22 01:00:18.597 DatabaseMail process is started
2015-03-22 01:10:21.013 DatabaseMail process is shutting down
2015-04-02 10:10:50.393 DatabaseMail process is started
2015-04-02 10:20:53.960 DatabaseMail process is shutting down

The previous job error was on 3/22 and the email was sent properly.  The latest error on 4/1 didn't.

The server is not under stressed, and there is no error message in the server log.  It is just the Database Mail process did not start even when a mail is queued for some reason.

Summary:

Expected behaviour:

Job failed
Agent queue an email
Databasemail process starts
Email sent
Databasemail process stops

Actual behaviour:

Job failed
Agent queue an email
Databasemail process did not start immediately
Databasemail process decided to start after 2 days for unknown reason
Email sent, 2 days late
Databasemail process stops


texts:
I have a table like this:

and I want to have the output like this:

If I use case to this, I'm not getting a unique value for each pc. I used:
case
when software like '%7zip%' then software
else 'no'
end;

and I'm getting for computer_id something like this:

I don't know what function to use and if I can do this with 'case'.
Thank you!


texts: Oracle Enterprise Manager (OEM) locking SYSMAN account

texts:
Why does Oracle Enterprise Manager reset my SYSMAN account to LOCKED(TIMED) status?

I've tried rebuilding Enterprise Manager, and re-installation of Enterprise Manager, which always works, until the next reboot, and back to where we started...


texts: Changing database context before creating new database

texts:
I have noticed on many examples that before CREATE DATABASE statement, there is part that changes database context.

USE master;
GO

CREATE DATABASE Sample;
GO

I wonder why is that because with or without it, in SQL Server Management Studio 17, database creates right where it is supposed to be.


texts: Backup to Azure Storage Container: Shared Access Signature the only way?

texts:
RESTORE FILELISTONLY FROM URL = '' WITH CREDENTIAL = '' fails in SQL Server 2016.

Copied script from SQL Server 2012 environment, successfully restoring from Azure Storage Container, with create credential statement, and it failed in 2016.

Error message "Forbidden".

Only after logging into the Azure Portal, creating a Shared Access Signature for the container, could I execute the restore, and only by not specifying the credential did the restore work.

No documentation advises that a SAS (Shared Access Signature) is required in SQL Server 2016 for Azure container backup / restore.

Is there another way to use T-SQL to access blob backups on Azure storage containers in SQL Server 2016, than using SAS?

MSDN - SQL Server Backup to URL SQL Server 2016

Looking at the MSDN tutorial on how to configure the backup service SQL 2016 introduces, a SAS is used. Refer MSDN Tut on SQL 2016 backup service


texts: Correct organization of the database structure and selection

texts:
Sorry for newbie question.

For example, there is some kind of abstract device, we will call the Device class and the tasks for it: Task1, Task2, Task3.
For concreteness, I will describe everything in java codes.

class Device {
long id;
String name;
int ip;
int filed1;
String filed2;
// and so on
}

class Task1 {
long id;
long device_id; // id from Device class
int type = 1;
int property1;
char property2;
byte property3;
// Properties and their number are completely different in all Task, that is, the tasks are completely different.
}

class Task2 {
long id;
long device_id; // id from Device class
int type = 2;
String anotherProperty1;
float anotherProperty2;
long anotherProperty3;
short anotherProperty4;
double anotherProperty5;
int anotherProperty6;
// Properties and their number are completely different in all Task, that is, the tasks are completely different.
}

class Task3 {
long id;
long device_id; // id from Device class
int type = 3;
Charsequence yetAnotherProperty1;
AnotherType yetAnotherProperty2;
// Properties and their number are completely different in all Task, that is, the tasks are completely different.
}

Further, for each device a certain number of tasks are assigned, both with different classes and with the same ones. For example.

// pseudocode
Device device1 = new Device (.....);
// The list of tasks for the device 1 (Any combination, any number)
1. new Task2
2. new Task2
3. new Task1
4. new Task3

Device device2 = new Device (.....);
// The list of tasks for the device 2 (Any combination, any number)
1. new Task1
2. new Task3

Device device3 = new Device (.....);
// The list of tasks for the device 3 (Any combination, any number)
1. new Task1
2. new Task3
3. new Task1
4. new Task3
5. new Task1
6. new Task3
7. new Task1
8. new Task3
9. new Task1
10. new Task3
// and so on

I created the Device table with all the required fields, but I do not know how to save all this correctly in the database.
I will assume that:

is need to create more tables: Task1, Task2, Task3?
(when saving) to the specific tables put the all tasks, Device.id which corresponds to Task.device_id and the order of their execution?
(when load) do a query from all the tables Device, Task1, Task2, Task3 - and check from each task whether Device.id = Task1.device_id AND Device.id = Task2.device_id AND Device.id = Task3.device_id?

First, is the correct assumption, is the organization of the database structure correct or ....?

If yes, how correctly to do the optimized selection from several tables, write sql request if it is possible ..

I'm working with sqlite


texts:
I have an sql qyery.

SELECT rt.id, rt.type, rt.total_no_of_rooms
     , SUM(r.no_of_rooms) as "reserved rooms"
     , rt.total_no_of_rooms - SUM(r.no_of_rooms) as "rooms available"
FROM Roomtypes rt
LEFT OUTER JOIN Reservations r
    ON r.roomtype_id = rt.id
    AND '2018-04-11' >= r.check_in
    AND '2018-04-12' <= r.check_out
GROUP BY rt.id, rt.type, rt.total_no_of_rooms

can anyone tell e how to convert it to laravel


texts: What do I need to sync a local site office SQL Server with the Head Office SQL Server

texts:
The company has a desktop application powered by SQL Server from the head office that is dependent on an internet connection to our remote sites.  Usually when we move to a new site there is a good internet connection 5Mb+

We have a new site that is connected by a very slow satellite internet connection in the middle of nowhere.  As you can expect our application is really suffering.
Unfortunately we cannot just install a SQL Server box locally to power the application on site.

The application database has been designed (by a now non-existent 3rd party) that all separate contracts live in the same database and share common tables.

We have 3 projects running all writing to the one database, updates are about 500MB a week during active projects for all 3 projects.

Also there is a project status website powered from the head office SQL Server.  Management would not be happy if this new remote site showed no progress for the duration so we need to factor in getting data back to head office to power the website, again all from the one database.

It is outside the scope of this question to start re-designing the database. (hopefully that’s a project for later in the year)

Is it possible to install and operate a local SQL Server /
application server for the remote site clients that can update back
to the main head office SQL Server as and when it either has an
internet connection and / or enough bandwidth to do so?
How would one achieve this using SQL Server given the super slow Internet access?
What kind of timeframes would be involved to setup and test?

As usual other than money for a desktop box to act as a local server and a SQL Server 2008 Standard licence the budget is £0

EDIT: Added the tracert to show network latency from the remote site, unfortunately we are not the main contractor at this site and have little say in the provisioning of the comms.

1     1 ms     1 ms     1 ms  192.168.2.1
  2   544 ms   552 ms   564 ms  10.190.9.206
  3   543 ms   701 ms   655 ms  79.140.207.252
  4  1461 ms   542 ms   641 ms  79.140.207.254
  5   658 ms   564 ms   555 ms  ae1-143-ycr2.mcr.cw.net [213.38.254.81]
  6   601 ms   617 ms   588 ms  so-3-3-0-dcr1.lsw.cw.net [166.63.209.201]
  7   577 ms   568 ms   564 ms  xe-7-2-0-xcr1.lsw.cw.net [195.2.21.161]
  8   580 ms   564 ms   656 ms  xe-0-1-0-xcr1.lnd.cw.net [195.2.4.194]
  9   566 ms   565 ms   570 ms  ge-3-3-0.mpr1.lhr3.uk.above.net [195.66.226.76]
 10   605 ms   594 ms   569 ms  ge-5-1-0.mpr1.lhr3.uk.above.net [64.125.28.94]
 11   574 ms   569 ms   573 ms  so-0-0-0.mpr1.ams1.nl.above.net [64.125.30.130]
 12   570 ms   573 ms   570 ms  xe-0-0-0.er1.ams1.nl.above.net [64.125.26.81]
 13   635 ms   598 ms   581 ms  213.152.245.50
 14   588 ms   582 ms   582 ms  glfd-bb-1b-ae0-0.network.virginmedia.net [213.105.172.6]
 15   599 ms   618 ms   596 ms  manc-bb-1a-ae3-0.network.virginmedia.net [213.105.175.145]
 16   592 ms   609 ms   576 ms  manc-core-2a-ae0-0.network.virginmedia.net [82.15.206.246]
 17     *        *      592 ms  manc-lam-4-tenge11.network.virginmedia.net [213.104.242.26]
 18   583 ms   596 ms   636 ms  m229-mp4.cvx2-c.man.dial.ntli.net [62.252.216.229]
 19     *        *        *     Request timed out.