texts: Dynamic form builder database optimization Postgresql

texts:
Hi I have a project that create forms dynamically I use a model like the following:

forms
-----
  id (PK)
  name
  (other fields)

form_fields
-------------
  id (PK)
  form_id (FK to forms.id)
  label (nome to the field)

form_submits (form inserted by someone user)
-------------
  id (PK)
  form_id (FK to forms.id)
  user_id (FK to users.id)

form_submits_fiels
-------------
  id (PK)
  form_fields_id (FK to form_fields.id)
  form_submit_id (FK to form_submit.id)
  data

Now I have seriously problem whit the performance in the production server because the form_element table have more than 1.500.000 rows; the number continue growing and the function that return all records of a form take more that 20 seconds, the question is how optimizer the model or the query to take less time

I have some ideas but I thing that not resolve the problem:

Paginate: if i could paginate the queries the performance will better so much but I thing that is not possible because the data was not in order.
MongoDB: If use a no relational model the performance maybe will better but I not sure so mach can ber better with Mongo and is a so expensive solution


texts: Fastest way to disable indices and drop constraints and rebuild SQL Server table

texts:
Below are some T-SQL commands that we're running on a table with a few billion records.  Most of the database's size is taken up by 5 tables like this.  What is the quickest way to perform these steps without causing any issues?  It takes more than an hour just to run the 1st command.  Would it just be easier to drop the entire table and re-create it?  Or is that not possible and safe with so much data?  Can anyone think of any other ideas to speed this up?  We are just trying to truncate the data, and then rebuild the table from scratch in our ETL process.

        DROP INDEX [OF_IDX_ClusteredConcept] ON [dbo].[OBS_FACT] WITH ( ONLINE = OFF )
        ALTER TABLE OBS_FACT DROP CONSTRAINT OBS_FACT_PK
        ALTER INDEX ALL ON OBS_FACT disable;

        -- add new data to OBS_FACT table via ETL process

        ALTER TABLE [dbo].[OBS_FACT] ADD CONSTRAINT [OBS_FACT_PK] PRIMARY KEY NONCLUSTERED
        (
            [ENCOUNTER_NUM] ASC,
            [CONCEPT_CD] ASC,
            [PROVIDER_ID] ASC,
            [START_DATE] ASC,
            [MODIFIER_CD] ASC,
            [INSTANCE_NUM] ASC
        ) ON [PRIMARY]

        CREATE CLUSTERED INDEX [OF_IDX_ClusteredConcept] ON [dbo].[OBS_FACT]
        (
            [CONCEPT_CD] ASC
        );

        -- REBUILD indexes on OBSERVATION_FACT
        ALTER INDEX ALL ON OBS_FACT REBUILD

Often times, the drop index command causes this error in SQL Server Management Studio, if you try to re-launch SQL Server Management Studio in a different Window.

  Lock request time out period exceeded (Microsoft SQL Server, Error:
  1222)


texts: Delete in big table data all data, where foreign_id in other table didn't exist

texts:
Let's say i have two tables:

table_a:

    id |  article_id  |  text

article:

    id  |  text

table_a for example contain data:

1 1 ""
2 1 ""
3 1 ""
4 2 ""
5 3 ""

and table articles such data:

1 ""
3 ""

as result i must in table_a left only such data:

1 1 ""
2 1 ""
3 1 ""
5 3 ""

but if i write such sql code (pseudo):

 delete from table_a where article_id not in (article.id)

but main trouble is that table_a contain 40 billion entries, and articles 2 billion, and if for each table_a entry i will look up which id's are in articles and collect and then check them - it will be very long...
does mysql contain any variables, which could once be filled, and then used for each table_a entry?

how could i do this task more quick, than i imagine?


texts:
I have a database with 13 billion rows, per day I have around 20-30 mio rows. On top of this I have one cube, one of its dimensions is DateTime that goes down to milliseconds. To load the fact table I use the following Query as T-SQL Task within SSIS:

INSERT INTO [FACT].[DataMine]
SELECT MONTH(RDM.[DATE]) as 'PartitionID',
       DateTime_Key,
       Price,
       Amount,
FROM   [RAW].[DataMine] RDM
INNER JOIN [DIM].[DateTime] DDT
ON RDM.DateTime_Key = DDT.DateTime_Key
WHERE DM.Date BETWEEN '2011-11-28' AND '2011-11-28' AND
      DateTime_Key NOT IN (SELECT DISTINCT DateTime_Key
                           FROM [FACT].[DataMine] DM
                           INNER JOIN [DIM].[DateTime] DT
                           DM.DateTime_Key = DT.DateTime_Key
                           WHERE [DATE] BETWEEN '2011-11-28' AND '2011-11-28')

PartitionID is used because I partition the FACT table by Month. I have to be able to run the load over a certain date range and should not worry about double rows, therefore it looks first if the rows are already loaded or not.

From the performance this runs not bad, I need around 7-8 minutes for one day of data, but suddenly this goes up like a rocket and then takes > 1 hour for one day of data. What puzzles me is the fact that the load time doesn't go up gradually. Looking at the sql server i see that it is busy in the temp database and I see quiet some disk i/o (eventthough the sql server has around 140 GB RAM still free for him to grab).

Index are all up todate, no fragmentation, statistics are also looking good.

What am I missing to understand where this sudden performance drop comes from ?

Machine is:
(SQL 2008 R2 64bit / 8 cores / 192 GB RAM / SAN Disks / 10GbE)


texts:
I've an Oracle query where I need to get some data on the basis of some records (strings). I am using IN clause and query is working fine in TOAD with hardcoded data. But when I run that query through code (Dapper) and pass those strings as Parameters, query always returns NULL/ Empty result.
Here is my query:
SELECT * FROM table WHERE id IN :lstIds

that's how I'm passing my data as parameter:
using (IDbConnection conn = new DBManager().connection)
        {
            conn.Open();

            lstData = conn.Query<ViewModelData>(sqlGetData, new
            {
                lstIds = lstIdsParams

            }).ToList();

                conn.Dispose();
        }

and this is my list of Ids, that I am passing as param

Even if I run this query with hardcoded data, it still does not work..
SELECT * FROM table WHERE id IN 'PL-0417-01301006-H01-D-00013#000013#18'

But if I run same query in TOAD with hardcoded data, it works fine and returns proper data. I don't know what I am doing wrong in code that is creating issue. Can anyone help me out here??


texts:
In my database, there is a view named VAL_DATES_V which is used in many other views in the same schema.  For example, the SQL code in the view SECTOR_V uses  VAL_DATES_V.

CREATE OR REPLACE VIEW schema1.sector_v AS
SELECT s.*
FROM   schema1.sector      s,
       schema1.val_dates_v d
WHERE  s.date_id   = d.date_id
AND    d.open_flag = 1;

In some use cases, we want to allow a user to override the VAL_DATES_V view with a "with clause" of the same name:

WITH val_dates_v AS (
    SELECT 1234 AS date_id,
           1    AS open_flag
    FROM   dual
) SELECT * FROM schema1.sector_v;

In our Dev database this works perfectly fine, and SECTOR_V evaluates VAL_DATES_V as what is defined in the "with clause". However, in our QA database, the optimizer uses the schema defined view SCHEMA1.VAL_DATES_V instead of the "with clause" view of the same name.

Even more confusing, if I copy the SQL code for SECTOR_V in place of "sector_v" it works just fine in QA.

Let me know if anyone can explain why the view is taking precedent over the "with clause" view of the same name.

Real World Dev Explain Plan (partially formatted):

WITH val_dates AS
  (SELECT /*+ materialize */
          val_date,
          id,
          period_month,
          period_year,
          '1' AS open_flag,
          val_date_type
   FROM   client_rep.val_dates_table a
   WHERE  a.period_year= 2014
   AND    a.period_month = 9
  )
SELECT * FROM client_rep.xamin_sleeve_attribution_v

OPERATION    OBJECT_NAME     CARDINALITY     COST

SELECT STATEMENT         14      42
 **TEMP TABLE TRANSFORMATION
  LOAD AS SELECT    SYS_TEMP_0FD9D66E3_A916354E
   TABLE ACCESS VAL_DATES_TABLE 2   3
    INDEX   VAL_DATES_IDX2   2   1
     Access Predicates
      AND
       A.PERIOD_MONTH=9
       A.PERIOD_YEAR=2014**

 SORT   14   39
 HASH JOIN  14   38
 Access Predicates
 XSA.VARIABLE_COMP_FUND_CODE=F.VARIABLE_COMP_FUND_CODE
 VIEW
 index$_join$_005    105     2
 HASH JOIN
 Access Predicates
 ROWID=ROWID
 INDEX
 FUND_CONTROL_PK     105     1
 INDEX
 FUND_CONTROL_UK1    105     1
 HASH JOIN 14    36
 Access Predicates
 XSA.BATCH_ID=B.ID
 JOIN FILTER
 :BF0000     93      29
 NESTED LOOPS   93   29
 NESTED LOOPS   190      29
 VIEW
 VW_NSO_1    2   2
 HASH 2
 VIEW   2    2
 Filter Predicates
 AND
 TO_NUMBER(V.OPEN_FLAG)=1
 VAL_DATE_TYPE='Calendar End'
 **TABLE ACCESS
 SYS_TEMP_0FD9D66E3_A916354E     2   2**
 INDEX
 BATCHES_VAL_DATES_FK    95      1
 Access Predicates
 B.VAL_DATE_ID=ID
 TABLE ACCESS
 BATCHES     47      13
 Filter Predicates
 B.ACTIVE=1
 JOIN FILTER
 :BF0000     548     7
 TABLE ACCESS
 XAMIN_SLEEVE_ATTRIBUTION    548     7
 Access Predicates
 SYS_OP_BLOOM_FILTER(:BF0000,XSA.BATCH_ID)
 Filter Predicates
 SYS_OP_BLOOM_FILTER(:BF0000,XSA.BATCH_ID)

Create view statement:

CREATE OR REPLACE FORCE VIEW "CLIENT_REP"."XAMIN_SLEEVE_ATTRIBUTION_V" AS
SELECT XSA.*
     FROM CLIENT_REP.XAMIN_SLEEVE_ATTRIBUTION XSA,
          CLIENT_REP.BATCHES B,
          CLIENT_REP.FUND_CONTROL F
    WHERE     XSA.BATCH_ID = B.ID
          AND B.ACTIVE = 1
          AND XSA.VARIABLE_COMP_FUND_CODE = F.VARIABLE_COMP_FUND_CODE
          AND B.VAL_DATE_ID IN
                 (SELECT v.id
                    FROM client_rep.val_dates v
                   WHERE v.open_flag = 1 AND VAL_DATE_TYPE = 'Calendar End')
 ORDER BY VAL_DATE,
          FIXED_COMP_FUND_CODE,
          TIME_PERIOD,
          SORT_KEY;

Just trust me, the QA explain plan does not even materialize the "with clause". It ignores it and just uses the "val_dates_v"


texts: After bulk load, how to ensure an updated visibility map?

texts:
We have a number of tables that have an active version, an inactive version, and a view pointing at the active one.  We periodically truncate the inactive table, drop the indexes, load a bunch of data into the table, re-build the indexes and redefine the view to point to the newly loaded table.  This is reasonably standard data warehouse stuff.

The issue we're running into is that we are unable to use index-only scans on the freshly-loaded tables after our script runs due to the visibility map not being updated.  I tried adding a VACUUM step to our script, using pg_background.  I've tried both VACUUM ANALYZE and VACUUM FREEZE ANALYZE, but neither will update the visibility map if there's a transaction that started before the VACUUM.

The only workaround I have right now is to fail out of the script if pg_class.relallvisible = 0 for the backing table after the VACUUM FREEZE, but this is not ideal.  Are there any other ways to force the visibility map to update?

To be clear, there will be no user activity on the inactive table until the script updates the view definition, so I'm free to do pretty much anything that I want to it, but performance is somewhat of an issue (e.g, I would not want to VACUUM FULL) and my scripts can't hold any locks that would prevent selects on other tables.


texts: Convert to CTE or Window function or a set or a merge statement

texts:
Is anyone knows how to convert the below code using a CTE or Window function:

UPDATE a
SET a.linked_pmt_id = b.linked_pmt_id, a.looptag = '1Mb-NBCBZ'
FROM tblname_KS a
    JOIN (SELECT * FROM tblname_KS with (NOLOCK) WHERE pmt_id = linked_pmt_id) b
    ON ISNULL(a.prop_name,'NULL') = ISNULL(b.prop_name,'NULL') AND
       ISNULL(a.prop_addr1,'NULL') = ISNULL(b.prop_addr1,'NULL') AND
       a.prop_city = b.prop_city
WHERE   a.linked_pmt_id IS NULL AND
        (a.status <> 'U' AND  a.addr_type NOT IN ('GIS LOCATION','ASSIGNED LOCATION') AND a.addr_type IS NOT NULL) AND
        a.prop_city IS NOT NULL AND
        a.prop_zip IS NULL
GO

Thanks


texts: Why would i get a duplicate write error of auto-generated primary key?

texts:
Hi I'm not a DBA so forgive my ignorance. I don't have access to administrator tools etc. However, I do know that we have a live server which is approaching capacity ie 80%-90% full perhaps.

What is happening is that our web-application has started not writing certain records and it appears as though SQLServer is attempting to write a duplicate primary key in certain tables. I don't understand this and as this is an auto-generated column it is completely out of our control.

Is this a possible symptom of the server getting too full? Surely a DBMS should manage this kind of thing safely?


texts:
I have a copy of SQL server Express 2012 database which may be corrupt.  When I tried to run a repair on it I was asked for the original installation media.  The Options were for an MSI or ISO image.  However, SQL sever express came as an exe file.
How do I go about repairing an express version ?


texts: MySQL performance of trigger when many rows are affected

texts:
I have a question on MySQL triggers which I hope someone might be able to help with.  As part of our platform, we have a table (accounting), which grows rapidly. We also have an archived version of the table which unneeded rows from accounting are copied into (by a trigger).  This archived table has an extra column (deleted) which states whether that particular row was deleted from accounting.

Now, I want to have a 2nd script run, which will remove the archived records from accounting (I'm thinking around a 1000 at a time, run every 30 seconds to a minute) and, need to consider two scenarios:

1 - have a delete trigger on accounting, that sets the deleted column in the archive table to true where the id matches.

2 - Don't have a trigger but, create a temporary table of ID's that need to be deleted, delete the rows from accounting where the ID's match, set the deleted column of the archive table to true where the ID's match, then drop the temporary table.

I am leaning towards the 2nd option, as I'm aware that triggers in MySQL are based on each row, so there could be up to 1000 update statements running every time the script executes.  However, I'm wondering if the performance between the two scenarios actually would be significantly different?

Hope this makes sense.

Kind Regards

Daryl


texts:
I've just installed Wamp server 2 and I get the following 4 default MySQL users:

    USER         FROM HOST
    <anonymous>  localhost
    root         localhost
    root         127.0.0.1
    root         ::1

What does the ::1 mean?


texts: How to grant privileges to use DBMS_OUTPUT in Oracle?

texts:
SET SERVEROUTPUT ON, DBMS_OUTPUT work perfect in my user, System or DBA. But when i create new user, it's not work!
How to grant privileges to use this?


texts:
We are trying to migrate a database from SQL 2014 to Azure SQL.

In SSMS, you can right click on the database and then on a task to "Deploy Database to Windows Azure SQL Database..".

This task fails because the database uses a SECONDARY filegroup. I've gone through the process to move all indexes, heaps, etc. out of the SECONDARY file group into the PRIMARY filegroup.

However, I still can't delete the file in the SECONDARY filegroup because there is 43MB of data in it. Every script I've found to list all the objects in a filegroup say there is nothing remaining in the file.

How can I determine what's still in this filegroup so I can delete the file and remove the SECONDARY filegroup?


texts:
The task: write a trigger to log all updates within tbl1 table for future audit users' actions.

The tbl1 table:

id INT(11)
y SMALLINT(6)
country SMALLINT(6)
-- really here ~20 fields but only some of them have to be monitored

I'm going to store both old & new values, one row for each field that was changed. The place for log (simplified for this example):

 CREATE TABLE z_log(
   id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
   table_name CHAR(20) NOT NULL,
   row_id INT(11) UNSIGNED NOT NULL,
   field CHAR(20) NOT NULL,
   old_val VARCHAR(255) NOT NULL DEFAULT '',
   new_val VARCHAR(255) NOT NULL DEFAULT '',
   PRIMARY KEY (id)
 )

The trigger I have created:

CREATE TRIGGER trigger1
    AFTER UPDATE
    ON tbl1
    FOR EACH ROW
BEGIN
  -- I'm interested in two fields only and if they were changed only
  IF new.y != old.y THEN
    SET @y = "('','tbl1',new.id,'y',old.y,new.y)";
  END IF;

  IF new.country != old.country THEN
    SET @country = "('','tbl1',new.id,'country',old.country,new.country)";
  END IF;

  if @y != '' or @country != '' THEN
    set @my_sql = concat('insert into z_lot values ', concat_ws(',', @y, @country), ';');
    prepare stmt1 from @my_sql;
    EXECUTE stmt1;
  end if;

END

But I got error "dynamic sql is not allowed in stored function or trigger".
I can avoid 'execute'  by using a lot separated inserts: one for field 'y', one for field 'country' and ~10 inserts more for other fields but there will be performance penalty.

Is there any other fast way to log changes only?
Thank you.


texts: The ordinal position for a column changed without my knowledge

texts:
I have a column that is out of order (ordinally, not in terms of select statement), and I just can't figure out how it got that way.

I've been researching this for quite some time and am coming up empty.
I also could not reproduce it in testing.

I have informed the developers that they are not allowed to run queries that do not explicitly name columns, for exactly this reason.

In the mean time, though, it's driving me crazy that I have no idea how we came to this state of affairs.


texts: Get top 10 records from two identical tables

texts:
Suppose I have two identical tables as bellow with different values in it as bellow. I want to get top 4 records ordered by date.
These 4 records should be union of both tables

Sample tables:

ID  userID  Date
1   1111    01/01/2018
2   1112    03/01/2018
3   1113    04/01/2018
4   1114    05/01/2018
5   1115    06/01/2018
6   1116    07/01/2018

ID  userID  Date
1   1117    02/01/2018
2   1118    03/01/2018
3   1119    05/01/2018
4   1120    06/01/2018
5   1121    07/01/2018
6   1122    08/01/2018

Expected output:

ID  userID  Date
1   1111    01/01/2018
1   1117    02/01/2018
2   1112    03/01/2018
3   1113    04/01/2018

How can I get this output. Any help will be greatly appreciated.


texts: Can I restrict user's access on a certain IP?

texts:
Like in a certain way in MySQL.
CREATE 'dbuser'@'10.1.0.1' identitifed by 'password';
GRANT USAGE on *.* to 'dbuser'@'10.1.0.1';
We are not using any mongodb tools except for Robo


texts: MongoDB Production Amazon - 3 data nodes per replica set, or 2 data nodes plus a shared arbiter

texts:
I have an Amazon EC2 deployment of mongodb (3.4). Things are going well, but the DB size is growing quickly. I am about to shard a large collection in order to begin horizontal scaling (total space needed is 2TB).

So either (ignoring config servers, etc):

1. Each shard is a replica set with 3 data nodes

total cost = $1,130 / mo

6x m4.large ($85 ea) + 4x 1TB SSDs ($116 ea) + 2x 1TB magnetic (to save some $$) ($78 ea)

2. Each shard is a replica set with 2 data nodes, each also using a shared arbiter

total cost = $809 / mo

4x m4.large ($85 ea) + 4x 1TB SSDs ($116 ea) + 1x arbiter (cheapest machine is $5)

Diff is $321/mo

I get the feeling that option 2, in Amazon's hosted environment, using exclusively SSDs, should be quite durable. As far as I can see, the only problem with Option 2 is that if a primary node dies and I failover to the secondary, for that period there is no backup. But I can't actually evaluate the severity of this scenario.

Could Option 2 be mitigated by  attaching a spinning disk to each data node, that will act as a backup volume?

If anyone could provide some advice from experience it would be super helpful, but any advice is appreciated.

Thanks,


texts: Index rebuild at partition level on a table

texts:
We have functionality in SQL Server to rebuild indexes at partition level using below command

ALTER INDEX IX_name ON table REBUILD PARTITION=68

In non-partitioned table we disable the indexes, load the data and rebuild the indexes after the data load complete. Here we disable the indexes prior to data load so that indexes will not be processed while data insertion to table is happening.

How can I do the same process for a partitioned table? If i disable indexes on a partitioned table before data load, and run the rebuild partition command after the data load is complete, it won't work, saying disabled indexes cannot be rebuilt.

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) Enterprise Edition.


texts: in what situations the dynamic port number in sql server is used?

texts:
I have the following script to find out what is the Static and dynamic port numbers:

--===============================
-- sys.dm_exec_connections
--===============================
SELECT
[user]=suser_sname()
,local_tcp_port
,session_id
,connect_time
,net_transport
,protocol_type
,encrypt_option
,auth_scheme
,last_read
,last_write

FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID
GO

--===============================
-- Execute below script if SQL Server is configured with dynamic port number
--===============================
DECLARE       @portNo   NVARCHAR(10)

EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE',
@key        =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpDynamicPorts',
@value      = @portNo OUTPUT

SELECT [PortNumber] = @portNo
GO

--===============================
-- Execute below script if SQL Server is configured with static port number
--===============================
DECLARE       @portNo   NVARCHAR(10)

EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE',
@key        =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpPort',
@value      = @portNo OUTPUT

SELECT [PortNumber] = @portNo
GO

The following link mentions netstat -ano to check usage from the cmdline.

When is a Dynamic Port “dynamic”?

when is the dynamic port used?


texts: Does Compatibility Level allow unsupported functions?

texts:
I've got a SQL Server 2016 instance with a database on it, running with a compatibility level of 2008. However, the following dummy proc creates, and executes just fine.

CREATE PROC usp_TestFormat
AS
BEGIN
    SELECT FORMAT(CAST('10/25/2018' AS DATE), 'MMM') AS Month;
END;

However FORMAT() is a 2012+ function, so when I tried to take a stored proc I'd written in above database, or the test proc above even, and deploy it to our production database, that is running on a 2008r2 box, it fails, as it doesn't recognize the function. It was my understanding that compatibility levels would prevent this type of behavior, as I'm now having to go through and re-work the code that uses the format function. Is this a bug, or am I not understanding Compatibility levels as much as I thought?


texts: SQL dumping all pages from buffer cache every few minutes

texts:
I have a single SQL2012 SP4 node running several databases.

The server has 20GB of memory available, 14GB allocated to SQL (nothing else running on the box).

Every few minutes SQL dumps the whole of the buffer cache. Page life expectancy hits zero, the buffer cache descriptors show that there's nothing in the cache.

I had a look at the resource monitor notifications and the notifications are bouncing around from high/steady/low every few milliseconds:

RESOURCE_MEMPHYSICAL_HIGH
RESOURCE_MEM_STEADY
RESOURCE_MEMPHYSICAL_LOW

With timestamps that are several milliseconds apart. The PLE is essentially a saw-tooth pattern.

I've seen this happen before with SQL2012 SP1 and this question:

SQL Server 2012 Free Pages in Buffer Cache Not Being Used

Seems to be a similar issue, though I've already updated to SP4.

I've tried turning on LPIM for the service account and I've tried messing with the max memory setting. Lowering max memory seems to have caused the buffer cache to empty more frequently.

Any ideas of what to check next?

Server workload is literally nothing (I'm scrolling through lists of items in an ERP system and it gets to about 40-50MB before the cache just drops again).

It's interesting because I upgraded from SP1 to try and fix this - the cache there was getting to around 500MB. Since then I dropped the max memory setting to 14GB which seems to have made it worse.

I'm wondering if Windows is panicking and throwing incorrect notifications for memory pressure at SQL - it follows that the server with max memory set to unbounded seemed to run ok-ish but never fill the cache more than a few hundred MB - but now it barely gets to 50...

More Info: for those that asked

Number of cores: 4

Database Size: 80GB

Error log shows: A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 247928, committed (KB): 495656, memory utilization: 50%.

Results of running scripts from this link: https://www.sqlskills.com/blogs/jonathan/identifying-external-memory-pressure-with-dm_os_ring_buffers-and-ring_buffer_resource_monitor/

Not sure how to interpret these - it looks like there is both internal and external memory pressure at various times..

Even more info:

This is a Hyper-V guest sitting on a host with 96GB of total ram of which about half is allocated to the guests.

The symptoms seem similar to this:

SQL Server 2012 x64 - cannot safely allocate more than 50% RAM

However, when I allocated 14GB to SQL the symptoms kicked in immediately (barely 3GB of the server memory was committed)

Last night I bumped the guest memory to 32GB and the issue went away but I see only 14GB commit of total server memory (and the business that runs the DB is busy this morning and this is when they usually have their performance issues).

About 8-9GB of data in the cache at the moment, seems to be stable.

It seems to suggest that 20GB is enough for the workload on this box. I'm happy leaving it with 32GB for now but I'd really like to get to the bottom of this so I can better configure the VMs/SQL.

I'll keep digging and update if I find the answer!

Even more more info:

I didn't restart SQL after turning on LPIM (not realising that was a requirement) but I did leave this setting on and restart to upgrade the memory so now I'm not sure if the increase in memory or LPIM has alleviated the issues.

Will jump on tonight when the server is idle and check how it looks at 20GB again.

Even Further More More Info:

Currently the server is ticking along ok with 32GB allocated and we've not seen the problem since. If this crops up again I'll come back to this question and keep digging.

Currently remains a mystery but my guess is that I'm only masking the issues at the moment.


texts: List the underlying objects used in a SQL Server Function

texts:
I use the below to get a list of underlying objects in a View:

SELECT view_name, Table_Name
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE View_Name IN
    (
      'View1', 'View2'
    )
ORDER BY view_name, table_name

I could not find any system table that provided the base objects in a function. Does anyone have any idea how to easily pull this information? I am dealing with many functions with multiple base DB objects and want to avoid manual effort to get this information. Thanks!


texts: What is the difference between an INNER  JOIN and an OUTER JOIN ?

texts:
I am new to SQL and wanted to know what is the difference between those two JOIN types?

SELECT *
FROM user u
INNER JOIN telephone t ON t.user_id = u.id

SELECT *
FROM user u
LEFT OUTER JOIN telephone t ON t.user_id = u.id

When should I use one or the other?


texts:
I will delete millions records from a table in mysql dbms, but i don't want to delete all records at one time not to force database server cpu. That's why i want to use my query many times. My whille loops doesn't work.I don't know why. How to do that? My query is below:

while 1=1 DO
delete  FROM customer.generallogs  where DateEnter < '2019-04-03 16:13:28' limit 100000;
end while;


texts: MySql: Delete all rows greater than n entries ordered by datetime

texts:
Can someone help me with a MySql query to delete all rows greater than n entries ordered by date?

I.e. say I have 1200 rows of data with a timestamp column. I need to order it by date and preserve rows only up to the first 200.

If I have only 199 rows of data, then I need to preserve them all.


texts: I managed to create a column in my MySQL database named ` (the backtick character).  How can I delete it?  Is there a way to escape the backtick character in a query?


texts: General error 1651 after a mysql migration

texts:
I finished migration from 5.1 to 5.6, and (with the same queries as before) mysql complains right away about:

  General error: 1651 Query cache is disabled; restart the server with
  query_cache_type=1 to enable it

I don't want the query cache enabled (that's an app with lots of writting), and my file my.ini looks like this:

# The MySQL server
[mysqld]
# ... other parameters...

query_cache_type = 0
query_cache_size = 0

Also, on connect, my php connection wrapper always executes

SET SESSION query_cache_type = OFF

What I don't understand is

Why the parameters apparently do not work
Why does mysql even have to complain about a parameter that is disabled when it is the user's choice to do so.

Enlightment needed - Thanks!


texts: How can lots of ram (128 GB) be used to improve MySQL 5.5 index creation?

texts:
This is similar to another post of mine, but now I have a different table structure, and it's still unclear what MySQL parameters should be changed to take advantage of the extra ram my machine has---so if it seems like a duplicate, let me know how best to rephrase what I'm asking.

I have the following table: create table mytable (id1 int, id2 int, score float) engine MyISAM, with 50 billion records.

I plan to add an index on the first column (alter table mytable add index myindex (id1)), and I am wondering what MySQL parameters could be changed to take advantage of this extra memory (e.g. buffers?)?

Note: the engine type does not need to be MyISAM if that makes a difference.


texts: Transfering a table with millions of rows in a fast efficient way without blocking

texts: Are there any solutions more efficient than simple INSERT INTO or exporting and importing files to move millions of rows from a table with 3 columns to a similar table in another database, without blocking the destination database and not losing any of the records in transactions that can be rolled back in case of failure?


texts: Is there a performance benefit to use tinyint instead of int for Primary Key?

texts:
Is there a performance benefit to use tinyint instead of int for Primary Key when I know that the number of rows are limited in this table but this key will be used as a foreign key in another huge table? (I will use a lot of joins in queries)
I'm using MySQL.
For example this is a part of my model:


texts:
I have got into slave lag gradually say every minute it lags around 5secs on slave.

So it started morning 10AM the Seconds_behind_master was 13. Now at 4:30 PM it has reached to 560.

No variable changed. Not much selects are running in there, its all regular ones. In fact we have stopped few applications thinking those could cause slowness due to selects.

This seems to be strange for me. This had never happened before.

I have below variables already set to minimize IO load on disk.

> innodb_flush_log_at_trx_commit=0
> innodb_flush_method = O_DIRECT
> sync_binlog=0

I also have 2 more slave nodes. They are performing real good. And they are used heavily by selects on app end.

Any thoughts or any one have run into similar issue before?

New Update

Something found in network.

Transferring 1 GB file to slave0
mannoj@master:~$ scp mysql-bin.001539 mannoj@12.168.4.7:/home/mannoj/
 100% 1024MB  68.3MB/s   00:15

Transferring 1 GB file to slave1                                                                                mannoj@master:~$ scp mysql-bin.001539 mannoj@12.18.11.91:/home/mannoj/
 100% 1024MB 170.7MB/s   00:06

Transferring 1 GB file to newslave000
mannoj@master:~$ scp mysql-bin.001539 mannoj@12.18.19.1:/home/mannoj/
 100% 1024MB 204.8MB/s   00:05

The slave lag persist on slave0.

Final Update (I guess)>

I disabled Network consuming mysqlbinlog puller runs via cron. Seems to be draining seconds_behind_master from 700 to 490.


texts: Good way to call multiple SQL Server Agent jobs sequentially from one main job?

texts:
I've got several SQL Server Agent jobs that should run sequentially. To keep a nice overview of the jobs that should execute I have created a main job that calls the other jobs with a call to EXEC msdb.dbo.sp_start_job N'TEST1'. The sp_start_job finishes instantly (Job Step 1), but then I want my main job to wait until job TEST1 has finished before calling the next job.

So I have written this small script that starts executing right after the job is called (Job Step 2), and forces the main job to wait until the sub job has finished:

WHILE 1 = 1
  BEGIN
    WAITFOR DELAY '00:05:00.000';

    SELECT *
    INTO   #jobs
    FROM   OPENROWSET('SQLNCLI', 'Server=TESTSERVER;Trusted_Connection=yes;',
           'EXEC msdb.dbo.sp_help_job @job_name = N''TEST1'',
           @execution_status = 0, @job_aspect = N''JOB''');

    IF NOT (EXISTS (SELECT top 1 * FROM #jobs))
      BEGIN
        BREAK
      END;

    DROP TABLE #jobs;

  END;

This works well enough. But I got the feeling smarter and/or safer (WHILE 1 = 1?) solutions should be possible.

I'm curious about the following things, hope you can provide me with some insights:

What are the problems with this approach?
Can you suggest a better way to do this?

(I posted this question at StackOverflow first, because I was focusing on improvement of the code. Still valid. But my guess is that people here will in general have smarter things to say about why I should not try to do this the way I'm doing it now, or provide good alternatives.)

EDIT (July 25)
Apparently there is not too much wrong with my script, according to the low number of answers pointing out problems with it :-) The alternative to this kind of scripting seems to be to use a tool that is designed for these tasks (like SQL Sentry Event Manager or ...) - or to write such a tool yourself. We won't be buying such a tool at my current company, so for now I'll just stick with the script.


texts:
I have increased temp tablespace temp02.dbf by the following command

alter tablespace HFDORA_TEMP add TEMPFILE '/orac/u01/oradata/hfd2/system/hfd2/temp02.dbf' size 2000M;

But after increasing it same error is coming. Kindly suggest.

  ORA-01652 : unable to extend temp segment by 128 in tablespace
  HFDORA_TEMP


texts:
I have a typical article and tag_map tables,
CREATE TABLE `articles` (
  `ID` int(11) unsigned NOT NULL,
  `Tags` tinyint(3) unsigned DEFAULT NULL,
  `Title` varchar(512) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `Tags` (`Tags`),
  FULLTEXT KEY `Title` (`Title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 `PAGE_COMPRESSED`='ON'

CREATE TABLE `tag_map` (
  `TagID` int(11) unsigned NOT NULL,
  `ID` int(11) unsigned NOT NULL,
  `Number` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`TagID`,`ID`),
  KEY `ID` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 `PAGE_COMPRESSED`='ON'

and I need to UPDATE the article table with the number of tags COUNTed from tag_map table as
UPDATE articles a JOIN (
SELECT ID,COUNT(*) AS C FROM tag_map GROUP BY ID
) b ON a.ID=b.ID
SET a.Tags=b.C WHERE a.Tags IS NULL

The problem is that WHERE a.Tags IS NULL condition is not applied to the subsequery. When updating only a few roles in the articles table, the entire tag_map table is scanned.
I run MariaDB 10.5, and this is the output of EXPLAIN UPDATE:
+------+-----------------+-------------+------+---------------+------------+---------+-----------------------+----------+-------------+
| id   | select_type     | table       | type | possible_keys | key        | key_len | ref                   | rows     | Extra       |
+------+-----------------+-------------+------+---------------+------------+---------+-----------------------+----------+-------------+
|    1 | PRIMARY         | a           | ALL  | PRIMARY,Tags  | NULL       | NULL    | NULL                  | 34543450 | Using where |
|    1 | PRIMARY         | <derived2>  | ref  | key0          | key0       | 4       | texts.a.ID            | 2        |             |
|    2 | LATERAL DERIVED | tag_map     | ref  | ID            | ID         | 4       | texts.a.ID            | 3        | Using index |
+------+-----------------+-------------+------+---------------+------------+---------+-----------------------+----------+-------------+
3 rows in set (0.001 sec)


texts:
I have a large table that frequently sends 3,000 rows of data to a client.

The longest wait state is Sending data.  I would like to minimize this if possible!

Is there a foolproof way of doing so?


texts:
I've read a lot of articles about Isolation levels, but I'm confused which one to choose for my MySQL 5.6 database.
This is my situation:

Let's say I have two group of users:

Group A (executes selects)
Group B (executes inserts and updates)

Every individual in group A is executing huge select query that takes 5 minutes and in the meanwhile a couple members from group B are inserting and updating records.
Which database isolation do I need to achieve:

The select queries that are executed by people from Group A should
NOT cause a lock to eachother. So simultaneously reads should be
allowed, they must be able to access the same data at the same time.
The select queries that are executed by people from Group A should
NOT cause a lock to people from Group B who are inserting and
updating. So simultaneously writes and reads should be allowed and
people from Group B shouldn't be waiting 5 minutes until the individual select
queries from Group A are finished.
The inserted and updated records should be included in the select
query results from Group A.


texts:
I have a table that looks like this

id |   json_column
---+------------
 1 | {"text1":"a", "text2":"b", "text3":"c", ....}
 2 | {"text1":"b", "text2":"c", ....}

and I want it to be like this

text1 | text2 | text3 | ....
a     | b     | c     | ....
b     | c     | d     | ....

one variant I found to get this result is using json_populate_record() and creating a new type x as (text1 text, text2 text, text3 text,....) so i can execute

select (json_populate_record(null::x, json_column)).* from table

Is there another way to do this without creating a type like above? All new columns can have the type text, so is there a shortcut maybe?

My problem is that I have 50 keys in one json field in the form of aaaa_bbbb_cccc_d so creating one type for this by hand would take way to much time

I am using 9.5.4


texts: SQL Server 2005 error while starting service

texts:
I am getting error while starting the service & errorlog is:

2013-04-04 14:50:49.14 Server      Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
    Oct 14 2005 00:33:37
    Copyright (c) 1988-2005 Microsoft Corporation
    Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2013-04-04 14:50:49.14 Server      (c) 2005 Microsoft Corporation.
2013-04-04 14:50:49.14 Server      All rights reserved.
2013-04-04 14:50:49.14 Server      Server process ID is 312.
2013-04-04 14:50:49.14 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2013-04-04 14:50:49.14 Server      This instance of SQL Server last reported using a process ID of 3072 at 4/4/2013 2:32:28 PM (local) 4/4/2013 9:02:28 AM (UTC). This is an informational message only; no user action is required.
2013-04-04 14:50:49.14 Server      Registry startup parameters:
2013-04-04 14:50:49.14 Server        -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2013-04-04 14:50:49.14 Server        -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2013-04-04 14:50:49.14 Server        -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2013-04-04 14:50:49.18 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2013-04-04 14:50:49.18 Server      Detected 2 CPUs. This is an informational message; no user action is required.
2013-04-04 14:50:49.84 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2013-04-04 14:50:49.85 Server      Database Mirroring Transport is disabled in the endpoint configuration.
2013-04-04 14:50:49.90 spid3s      Starting up database 'master'.
2013-04-04 14:50:50.60 spid3s      SQL Trace ID 1 was started by login "sa".
2013-04-04 14:50:50.64 spid3s      Starting up database 'mssqlsystemresource'.
2013-04-04 14:50:50.82 spid8s      Starting up database 'model'.
2013-04-04 14:50:50.82 spid3s      Server name is 'COMPAQ-FB238749\SQLEXPRESS'. This is an informational message only. No user action is required.
2013-04-04 14:50:50.82 spid3s      Starting up database 'msdb'.
2013-04-04 14:50:50.90 Server      Error: 17190, Severity: 16, State: 1.
2013-04-04 14:50:50.90 Server      FallBack certificate initialization failed with error code: 1.
2013-04-04 14:50:50.90 Server      Warning:Encryption is not available, could not find a valid certificate to load.
2013-04-04 14:50:50.93 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLEXPRESS ].
2013-04-04 14:50:50.93 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].
2013-04-04 14:50:50.98 Server      Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
2013-04-04 14:50:51.00 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.
2013-04-04 14:50:51.03 spid8s      Error: 9003, Severity: 20, State: 1.
2013-04-04 14:50:51.03 spid8s      The log scan number (100:384:1) passed to log scan in database 'model' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
2013-04-04 14:50:51.03 spid8s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.


texts: Connection timeout errors from Postgres Update Monitor (postgres 9.6)

texts:
I am running PostgreSQL 9.6 on windows 10

It seems it has an update monitor that does not use any information about the proxy agent from Windows (we have a corporate proxy). It also does not look at the environment variables HTTP_PROXY or HTTPS_PROXY (which I have set to add credentials via CNTLM for our corporate firewall). This is useful for programs such as NPM and GIT which need to get past our outgoing firewall.

So I am getting the following message every 10 or some minutes as a pop up from a program called:

"D:\Program Files (x86)\postgresql\updatemonitor\bin\UpdManager.exe" --execute  "D:\Program Files\PostgreSQL\9.6\bin\stackbuilder.exe"

The message is very deceiving, but I tracked down the program, and it is the one listed above.


texts: SQL Server 2008 R2 Express single CPU core usage and need of upgrade to SQL Server 2014 Express

texts:
In our office we are using SQL Server 2008R2 Express which is installed in Windows server 2012. The SQL Server database is accessed by more than 60 users in network simultaneously. In recent days we are experiencing slow performance of SQL Server and our programs which makes use of SQL Server.

From last few years we were using Windows Server 2003 with SQL Server 2008 R2 Express. Few months we changed server to  Windows Server 2012.

I can analyze the performance of SQL Server using performance counters etc. But before that I wanted to know below details

Is there any limitation in SQL Server 2008 R2 Express like it uses only one core of the CPU? And does it may be a reason for slow performance?
Are there compatibility problems when using old version (SQL Server 2008 R2 Express) on Windows Server 2012?
Is there any need of upgrading to SQL Server 2014 Express? Will I get any good benefits?


texts: InnoDB row metadata size (minimum row size)

texts:
I'm trying to decide whether to normalize a thing further, and need to know what the smallest a row in InnoDB can be (I've got MySQL 5.6).

This is not exactly my case, but for simplicity, let's assume I just have a surrogate PK (bigint) and two TINYINT columns, neither of which can be null. How many bytes can I expect each row to need for storage if I'm gonna have 1 billion of these?

I'd imagine each row requires some metadata (I know it does at least for when there are NULLable columns), but can't seem to find the documentation on how to calculate it.

Then, how much can I expect to be eaten by the free space in the page, and the metadata in the page (or is that all together)?

I want to know so that I can better judge when I would have a long table that is narrow enough to avoid proper normalization.


texts: How to migrate SQL2014 to Azure SQL in short time (scripting)?

texts:
Context: The new application is ready to be used (Azure SQL). The old application is still used (MS SQL 2014). The switch from the old to the new is to be done during one night. The size of data is not extreme; however the old stored procedures have to be replaced by those that do not cause problems.
Details of the situation: I need to transfer the data from the old SQL server to the new one quickly, in night time. It means to stop the old application, backup the fresh data from old SQL, restore the data on Azure SQL, install the new SQL functionality, launch the application.
Migration during development of the new application: I have learned something about the SqlPackage.exe utility that is able to create new form of backup that can be restored at Azure SQL. However, one have to fix some SQL code in stored procedures to be allowed to create the new xxx.bacpac backup.
To fix the above mentioned SQL code I did the old form of SQL backup, restored it to an auxiliary database on SQL 2014, fixed or removed the stored procedures and views (manually), and only then used the SqlPackage.exe for the cleaned database, copied the xxx.bacpac to Azure Windows Server, and restored the bacpac to Azure SQL server. That was acceptable during development; however, it is a lot of manual work for the migration, it could take more time than what is allowed, and (mainly) it could be error prone, and one cannot test the approach in advance.
First approach to the scripted approach failed: The later idea was to use the SqlPackage.exe to export the bacpac only with named data tables. Reading the documentation, that should be possible. However, I did not find any way to exclude stored procedures, functions, and views. My batch files contains the lines like this:
call "C:\Program Files\Microsoft SQL Server\150\DAC\bin\SqlPackage.exe"^
  /Action:Export /SourceServerName:%source_server_name% /SourceDatabaseName:%source_database_name%^
  /TargetFile:%target_file% /p:TableData=dbo.my_table1 /p:TableData=dbo.my_table2 ...

How the task should be done correctly? Is there any proven and recommended way how to do the migration of fresh data?


texts: Adding 4th table to join jumps processing time from seconds to over 20 minutes

texts:
I am trying to join a total of 4 tables together. When I only join 3 of the tables together I get a result set of about 220,000 records in about 2 seconds, but as soon as I throw the 4th table in, the script will run for an extremely long time (over 20 minutes during one test) and will return million of rows.

I can't figure out why adding this last table causes this performance hit and all these extra rows to show up. As far as I can tell none of the rows are duplicates, but I have only spot checked, and never been able to run the script to completion.

SELECT  e.ID AS EmployeeID,
        NULL AS TimesheetHeaderID,
        ts.StartDateTime AS StartDateTime,
        ts.EndDateTime AS EndDateTime,
        pc.ID AS ProjectID,
        tc.ID AS TaskID,
        ts.[Description] AS WorkDescription,
        ts.UserID AS Name,
        ts.WeekEnding AS WeekEnding
FROM JobsDB.dbo.Timesheets AS ts
    INNER JOIN dbo.Employee AS e
        ON e.ADUserName = ts.UserId
    INNER JOIN dbo.ProjectCode AS pc
        ON pc.Code = ts.JobCode
    INNER JOIN dbo.TaskCode AS tc
        ON tc.Code = ts.JobSubTypeId
WHERE ts.WeekEnding >= '2014-01-01';

It is only when I include TaskCode table that I have the problem.

Link to execution plan
Table structure and indexes

There could be a task with code "Admin" a dozen times because a dozen projects have an "Admin" task.

I have no idea why Timesheets is a heap. It is the old database I am migrating info from, and was created in the manner  of "just make it work and don't care about doing it right".

I have triple confirmed the join between Code and JobSubTypeId. Looking at the schema for Timesheets the Id column is not a PK, so I could possibly make it one.

I noticed something that might work and generated this code:

SELECT  e.ID AS EmployeeID,
        NULL AS TimesheetHeaderID,
        ts.StartDateTime AS StartDateTime,
        ts.EndDateTime AS EndDateTime,
        pc.ID AS ProjectID,
        tc.ID AS TaskID,
        ts.[Description] AS WorkDescription,
        ts.UserID AS Name,
        ts.WeekEnding AS WeekEnding
FROM JobsDB.dbo.Timesheets AS ts
INNER JOIN dbo.Employee AS e
ON e.ADUserName = ts.UserId
INNER JOIN dbo.ProjectCode AS pc
ON pc.Code = ts.JobCode
INNER JOIN dbo.TaskCode AS tc
ON tc.Code = ts.JobSubTypeId
AND tc.ProjectID = pc.ID --Works but not all rows return
WHERE ts.WeekEnding >= '2014-01-01';

This code works, mostly. With it the query runs in seconds and returns 163,222 rows of data. Not as much as the 3 table join so I need to dig though the data to see if this is really working and I am just missing some task codes or if this is a false positive.


texts: Optimize table and query, aggregate by date or date-hour

texts:
I have a table storing sensor data (100M rows currently) from stations from different sites. In my case, sites may have many stations. Create table statement is as follows:

CREATE TABLE sensor_data (
site INT NOT NULL,
station INT NOT NULL,
time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
reading1 FLOAT DEFAULT NULL,
reading2 FLOAT DEFAULT NULL,
reading3 FLOAT DEFAULT NULL,
reading4 FLOAT DEFAULT NULL,
reading5 FLOAT DEFAULT NULL,
reading6 FLOAT DEFAULT NULL,
reading7 FLOAT DEFAULT NULL,
reading8 FLOAT DEFAULT NULL,
reading9 FLOAT DEFAULT NULL,
reading10 FLOAT DEFAULT NULL,
reading11 FLOAT DEFAULT NULL,
reading12 FLOAT DEFAULT NULL,
reading13 FLOAT DEFAULT NULL,
reading14 FLOAT DEFAULT NULL,
reading15 FLOAT DEFAULT NULL,
reading16 FLOAT DEFAULT NULL,
reading17 FLOAT DEFAULT NULL,
reading18 FLOAT DEFAULT NULL,
reading19 FLOAT DEFAULT NULL,
reading20 FLOAT DEFAULT NULL,
KEY Index_sst (site, station, time)
) ENGINE=InnoDB;

I need to query readings of a specific station aggregated by day or day-hour for a user defined interval. The queries are as follows:

Query 1

SELECT AVG(reading1), DATE_FORMAT(time, '%Y-%m-%d 00:00:00') AS daily
FROM sensor_data
WHERE site=1 AND station=1 AND time>='2010-00-00 00:00:00'
GROUP BY daily
ORDER BY daily;

Query 2

SELECT AVG(reading1), DATE_FORMAT(time, '%Y-%m-%d %H:00:00') AS hourly
FROM sensor_data
WHERE site=1 AND station=1 AND time>='2010-00-00 00:00:00'
GROUP BY hourly
ORDER BY hourly;

These queries don't use index, but use temporary table and filesort.

How should I get rid of long execution time of these queries? Adding a separate date column and indexing site-station-date seems to improve Query 1, but no idea about Query 2. I am wondering how the optimal way could be in this case.

SQL Fiddle


texts: Troubleshooting high disk read - buffer cache query

texts:
I have a table tbl_Table1 that is about 5 GB in size and has millions of rows.

It has these columns:

ID, ContractID, Person, Address1, Address2, and many more.

One clustered index on the PK, ID.

I then have a query like this:

SELECT * FROM tbl_Table1 WHERE ContractID = 2000

So this query isn't going to be using an index, so will do a full table scan.

Will this table then be kept in the buffer cache after this query is executed? Or will it continually read from disk?

I understand the answer is to add an index, but I want to know what happens in terms of buffer cache.

Thank you


texts: Which DB for 1 table with 100 Million rows per year?

texts:
I've a questionnaire with 70 questions and need to store the answers.

Problem: it's 100 Million records per year.

I've experience with different types of storages, but never had to deal with these huge numbers. Now I'm afraid that every wrong decision might lead to big negative impact.

Information about the data:

I was thinking about 1 table with 70 columns
The columns are already defined and might be slightly adjusted after a while (+/- 10 columns)
The data type of each column is mainly Integer and String with mostly 2 chars, max. 10 chars.
No nested (tree) structures needed
No flexible data types needed
No joins needed

Data definition (pseudo-code)

COLUMN           |   TYPE   | MAX. LENGTH
-----------------------------------------
id               | Integer  | 10
questionnaire_id | Integer  | 10
answered_at      | Datetime | -
answered_by      | Integer  | 10
answer1          | Integer  | 2
answer2          | Integer  | 2
answer3          | Integer  | 2
answer4          | Integer  | 2
...
answer35         | String   | 2
answer36         | String   | 2
...
answer70         | String   | 2

Priorities:

Storing big data
Run standard aggregate functions (avg, min, max, count, ...), filter and sort in an acceptable time

Is there any best practice or a checklist to follow which reduces my options and therefore wrong decisions?

Thank you in advance!

Edit: normalized, inspired by Dave

# questionnaire
- id (PK, AI)

# questions
- id (PK, AI)
- questionnaire_id (FK)
- label

# submits
- id (PK, AI)
- questionnaire_id (FK)
- answered_by
- answered_at

# answers
- id (PK, AI)
- submit_id (FK)
- question_id (FK)
- value      // Integers only (strings are mapped: A => 1, B => 2)


texts: Update a table with data from another table

texts:
I have a Wordpress table in which I want to take the user_email from Table2 and import it to contact_email on Table1 one based off of the user_login. user_login and user_id equal the same value. Nothing I have tried has worked. Any thoughts?

Table1

  user_id|contact_email |contact_name
 =======================================
 123     |test@test.com |deft
 124     |test3@test.com|deft3

Table2 (User table)

 user_login|user_email     |display_name
 =======================================
 123       |test@test.com  |deft
 124       |test3@test.com |deft3

I have tried:

        UPDATE Table1
        SET contact_email = (SELECT Table2.user_email
                 FROM Table2
                 WHERE Table2.user_login = user_id )
        WHERE EXISTS (SELECT Table2.user_email
                 FROM Table2
                 WHERE Table2.user_login = user_id );


texts:
I found some packages like pgTAP but I would like to get community supported tools.
I am using PostgreSQL 9.4.3 in Debian 8.1.
I have many kinds of tests which I would like to do:

INSERT by purpose wrong thing and demand false
INSERT correctly but demand not doing change to the database

How can you do simple jUnit testing by PostgreSQL?


texts: Using sp_BlitzIndex - Diagnosed as "Index Hoarder: Addicted to nulls"

texts:
Context: MS SQL Server 2012

In using Kendra Little's awesome tool, sp_BlitzIndex, a number of tables were diagnosed as "Index Hoarder: Addicted to nulls".

I'm not looking for a debate over NULLs and their place. I know that there are cases where NULLs are perfectly appropriate, and even necessary. The issue here is the feedback provided by sp_BlitzIndex, and the value of enforcing the not NULL constraint.

In most of the tables sp_BlitzIndex "diagnosed" as having numerous columns that allow NULLs, there are not any actual NULL values. We programmatically don't allow them. I just never unchecked the box that says "Allow nulls" to enforce that constraint on the database level.

What benefits does enforcing this constraint provide, and why it is one of the problems that her script identifies?

Thanks!


texts: What are the consequences of dropping or changing the user name for the postgres DB account?

texts:
I feel confident I understand what's involved at the db level:

If dropping, I'd need to create users for each sys admin, give them SUPERUSER, and then drop the postgres user.
If changing the name, I'd need to alter the postgres account.

My problem, though, is that I'm not sure what the consequences of this action would be. For example, what would it means from the point of view of the Linux postgres user? When I su - postgres, I can run psql and I'm able to connect to the database. I assume that capability goes away if the postgres DB user is gone.

Also, the database is started using the postgres linux user. Is there some issue if I remove the postgres user that would make it so the database wouldn't even be able to start?

From ps:

/usr/pgsql-9.3/bin/postgres -D /var/lib/pgsql/9.3/data PG_GRANDPARENT_PID=1 USER=postgres PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin PWD=/ PGLOCALEDIR=/usr/pgsql-9.3/share/locale LANG=en_US.UTF-8 PGSYSCONFDIR=/etc/sysconfig/pgsql SHLVL=1 HOME=/var/lib/pgsql LOGNAME=postgres PGDATA=/var/lib/pgsql/9.3/data _=/usr/pgsql-9.3/bin/postgres

I'm running PosrgreSQL 9.3 on Cent OS 7.


texts: How to join the updated result set of multiple servers

texts:
I have got 4 different servers and I want to get the free diskspace of all those in a single output.
  using xp_fixeddrives we will get the free space in each drive .

Its not possible to get into all servers and check it as above.So I am trying to execute all it a once.

for example the result set of one server is as follows

drive   free_mb
C       1816
D       2553
E       2882
F       6247
G       16936
H       7281
I       2395

As I am a starter to sql please do help me to sort out this issue .


texts:
Consider the following query:

select qq.id from (
    select id from log
    where create_date <= now() - interval '1 month'
    limit 1000
) qq
order by qq.id
for update;

Docs say:

  When a locking clause appears at the top level of a SELECT query, the
  rows that are locked are exactly those that are returned by the query;

So I suppose that this query locks 1000 rows ordered by log.id and satisfying the where condition. Is my assumption correct ? The query plan shows two LockRows. How this can be explained?

The query plan:

QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------
 LockRows  (cost=127.96..140.46 rows=1000 width=40) (actual time=7.633..7.877 rows=1000 loops=1)
   ->  Sort  (cost=127.96..130.46 rows=1000 width=40) (actual time=7.633..7.734 rows=1000 loops=1)
         Sort Key: qq.id
         Sort Method: quicksort  Memory: 103kB
         ->  Subquery Scan on qq  (cost=0.00..78.13 rows=1000 width=40) (actual time=0.108..7.174 rows=1000 loops=1)
               ->  Limit  (cost=0.00..68.13 rows=1000 width=14) (actual time=0.100..6.749 rows=1000 loops=1)
                     ->  LockRows  (cost=0.00..21475436.14 rows=315198214 width=14) (actual time=0.098..6.601 rows=1000 loops=1)
                           ->  Seq Scan on log (cost=0.00..18323454.00 rows=315198214 width=14) (actual time=0.057..2.329 rows=1000 loops=1)
                                 Filter: (create_date <= (now() - '1 mon'::interval))
 Planning time: 0.228 ms
 Execution time: 8.044 ms
(11 rows)


texts: Disconnect from network without stopping the query

texts: I have an opposite problem to Does disconnecting the network stop a query?. I would like to launch a query and disconnect without waiting for results but I don't want the query to stop. Is it possible?


texts:
Let's say that I have a VARCHAR (which contains numeric data) that I want to use for a simple computation (adding 10 to it).  According to the MySQL documentation on CAST functions, I could accomplish this with either a CAST or a CONVERT:

SELECT (CAST(field1 AS SIGNED)) + 10
FROM myTable;

or:

SELECT (CONVERT(field1,SIGNED)) + 10
FROM myTable;

What is the difference between CAST and CONVERT in this sense?  Are they both really accomplishing the same thing?


texts: Need separate columns that calculates percentage

texts:
I have the following table:

NAME
------
Alex
Bob
Bob
Tim
Alex
Roger

I need to produce the following:

 Name    Count Percentage
 -------------------------
 Alex    2     33%
 Bob     2     33%
 Roger   1     16.6%
 Tim     1     16.6%

I have no idea where to start. I can do the initial query for the count which is simple.


texts: Many-to-many tables tracking participation at multiple events

texts:
I am working on a side project to track observations of different birds, and the time spent during each observation. Right now I have a table called Observations:

 obs_id   |   start_time  | end_time  |   date      |  location
--------------------------------------------------------------------
    1            3            36         27/09/2016    acadia park
    2            4            00         27/09/2016    zion park
    3            12           50         27/09/2016    acadia park
    4            16           32         27/09/2016    yellowstone
    5            9            20         28/09/2016    zion park
    6            21           50         28/09/2016    yellowstone

However, multiple people are observing these birds, and I would like to be able to query the people associated with these birds. I would also like to keep a running total of each person's observation time.

So I have a table of Observers, with a column for their total time spent observing (in hours, calculated from the start_time and end_time columns in the Observations table), but I'm not sure of the best way to relate these two tables. I want to relate these two tables so that I can see the observations that each person was involved with.

 person_id   |   total_time  | name
-----------------------------------------
    1            1.5          Jane Doe
    2            4            John Doe
    3            5            James Doe
    4            6            Jenny Doe
    5            3            Jeremy Doe

Unfortunately, there can be multiple people at an observation, and the same person can have multiple observations, too. That makes this (if I understand correctly) a many-to-many relationship. I'm wondering how to go about fixing that. Should I make a separate associative entity? Or should I just add a column into Observations that contains a foreign key for Observers, and create new rows for each observer – something like this?

 obs_id   |   person_id  | start_time  |  end_time |   date      |  location
--------------------------------------------------------------------
    1           1_1            3          36          27/09/2016    acadia park
    1           1_2            3          36          27/09/2016    acadia park
    2           2_1            4          00          27/09/2016    zion park
    2           2_2            4          00          27/09/2016    zion park
    2           2_3            4          00          27/09/2016    zion park
    2           2_4            4          00          27/09/2016    zion park
    3           3_1            12         50          28/09/2016    yellowstone

This will be implemented using PHP, allowing users to submit their observations on a website and updating a SQL database (MySQL). I've looked at some other problems, like tracking events associated with an item, or tracking student attendance, but none of them have quite the same problem that I do.

Thanks in advance for any help.


texts: SQL Server - How to compress XML inside Trigger?

texts:
I am using SQL Server Standard Edition 2012.

When doing Audit using Trigger, the fields of the audited row(inserted or deleted) are being inserted into the table in XML Format. However, as observed, the space occupied is 10 times the original row (checked using sp_spaceused command, suspected due to the tags).

I am wondering, is it possible to compress XML inside the trigger (means using SQL)?

EDIT:
Here's an example of the XML (there are around 50 columns in total). As observed, this really occupies A LOT of spaces.

<t>
  <MyKey>1</MyKey>
  <ClientKey>10</ClientKey>
  <DocNum>doc number</DocNum>
  <Source></Source>
  <DocType>doc</DocType>
  <GLDate>1900-01-01T00:00:00</GLDate>
  <GLDesc></GLDesc>
  <FY>2119</FY>
  <Period>4</Period>
  <CurrKey>-1</CurrKey>
  <InvoiceDate>1900-01-01T00:00:00</InvoiceDate>
  <InvNum></InvNum>
  <TaxInvNum></TaxInvNum>
  <CreatedBy>-1</CreatedBy>
  <CreatedDate>1900-01-01T00:00:00</CreatedDate>
  <UpdatedBy>23</UpdatedBy>
  <UpdatedDate>1900-01-01T00:00:00</UpdatedDate>
  <VoidBy>-1</VoidBy>
  <VoidDate>1900-01-01T00:00:00</VoidDate>
  <RejectBy>-1</RejectBy>
  <RejectDate>1900-01-01T00:00:00</RejectDate>
  ...
  ...
  ...
</t>


texts: What's the benefit of CROSS JOIN vs INNER JOIN in recursive CTE queries?

texts:
In docs, the join of non-recursive part to recursive is done via CROSS JOIN. Is there some benefit to using it instead of INNER JOIN? Is it just a subjective choice?

CREATE TEMPORARY TABLE folders (id INT, parent INT) ON COMMIT DROP;
INSERT INTO folders
    (id, parent)
    VALUES
    (1, null),
    (2, 1),
    (3, 2);

using CROSS JOIN

WITH RECURSIVE tree (id, parent) AS (
    SELECT id, parent
    FROM folders
    WHERE id = 3
    UNION ALL
    SELECT p.id, p.parent
    FROM folders p, tree
    WHERE tree.parent = p.id
)

using INNER JOIN

WITH RECURSIVE tree (id, parent) AS (
    SELECT id, parent
    FROM folders
    WHERE id = 3
    UNION ALL
    SELECT p.id, p.parent
    FROM folders p
        INNER JOIN tree
        ON tree.parent = p.id
)


texts: How to determine effective permissions and how granted for a local or domain account

texts:
With SQL Server - if you have a local or domain user (including accounts like LOCAL SYSTEM) how can you determine

What effective permissions they have on a database
How those permissions are granted (e.g. which roles, any direct etc)


texts: PostgreSQL function to insert to table from another table has different structure

texts:
I have the following table with five columns representing locations and their status each quarter in a year.

-- id, location_id, status, year, quarter
CREATE TABLE foo AS
SELECT * FROM ( VALUES
(1, 12, 1, 2014, 3),
(2, 12, 1, 2014, 4),
(3, 13, 0, 2015, 1),
(4, 13, 0, 2015, 2),
(5, 13, 1, 2015, 3),
(6, 13, 0, 2015, 4),
(7, 12, 1, 2015, 1),
(8, 12, 1, 2015, 2)
) AS t(id,location_id,status,year,quarter);

I want to create a function that will extract data from the table based on passed parameters and store them in a new table that has different structure.

I created this function that will receive 5 parameters,

the selected year
quarter 1 (1 to select, 0 to ignore)
quarter 2 (1 to select, 0 to ignore)
quarter 3 (1 to select, 0 to ignore)
quarter 4 (1 to select, 0 to ignore)

For example:

select get_info(2015,1,0,0,1)

This option will select id_ location and status where quarter = 1 and 4 only. This is the function

CREATE or REPLACE FUNCTION  get_info(
get_year int,
q1 int,
q2 int,
q3 int,
q4 int)
RETURNS VOID
AS $$
DECLARE
BEGIN
   CREATE TABLE IF NOT EXISTS tbl_temp
   ( parcelnum text,
    quart1  int,
    quart2  int,
    quart3  int,
    quart4  int);
[ I have prolem with this part:
INSERT INTO  tbl_calc_temp( parcelnum text,quart1  int,
quart2  int,quart3      int, quart4  int  )
Valuse (select some_thing from foo where quarter = 1 or 4)
]
END; $$
LANGUAGE PLPGSQL;

This function will create a table tbl_temp,
then it should select rows from table foo based on based parameter then insert them to the table tbl_temp. For the quarter was not select, it values will be null .

Please help me writing the part I have problem with.

Note:
The new table tbl_temp should have the following structure:

 CREATE TABLE tbl_temp
   ( parcelnum text,
     year   int,
    quart1  int,
    quart2  int,
    quart3  int,
    quart4  int);

I want to use:

INSERT INTO table(parcelnum,year, quart1 ,quart2, quart3,quart4)
VALUES
(values should be selected from foo and in case a quarter was not select or there is no record for a location in any quarter the value will be null);


texts: SQL SERVER 2014 - View value changes depending on the date

texts:
In a Data Mart I have a table with information about Players (Dimension Player). And It have the following attributes:

Name (nvarchar(250))
Dt_Contract (date)
New_Player (int)
DT_Execution (datetime)

The New_Player is a “binary code” and it show us the new team players. It is calculated as follows:
MONTH(DT_Contract) = MONTH(CAST(@DT_Execution AS DATE)) THEN 1 ELSE 0 END
So,

1 – Is a new player

0 – Is a old player
The Dt_Execution is  the Date on which the process runs

I liked to be able to see what new players on a specific date e not only when Month(DT_Conttract) = Month(Dt_Execution)

Basically,
Players who have been entered today are with the correct value in New_Player but if I want to observe what were the new players last month I will not be able to watch what they were. I think I need something more dynamic paw achieve this flexibility.

Does anyone have any idea how can I get it? What type of modification I have to implement in the table?

PS: The code is inserted SP that runs every day.


texts: I can't find time zone tables in sys database of MySQL

texts:
For my Django application, I had to set the time zone to UTC, however I am not able to set it as I couldn't find any tables related to time zone in the system database.

How do I get those? the scripts available in here https://dev.mysql.com/downloads/timezones.html only relates to loading of data in the tables and not creating them.

MySQL Version: 5.7
OS: Windows


texts: Can we WITH x AS ( ... ) INSERT INTO y(k) SELECT * FROM x in MySQL?

texts: We can do INSERT INTO SELECT, but what if I have a bunch of WITH AS statements (about 27ish) for the SELECT part? My current solution is do the query then scan using programming language (Golang for my case) then insert it using normal INSERT INTO statement one by one. Was there one step solution to do this?


texts:
I am reading the SAP guide administrator of BO, and I am a little confused about some term they use regardless how to perform online or hot backups.

Using the same words

  For hot backups, use the database vendor's backup tools in online atomic mode

It is the first time ever that I heard about online atomic backups.

And what is the difference between these kind of backups and normal backups?? While the guide is talking about making offline backups they do not use the term online atomic mode so I think administrator should perform a normal backup (Normal for me)

Could anyone make some light please??

Thanks in advance


texts: MySQL stops in windows VM and shows innodb os error in logs(error no. 5)

texts:
2020-08-23 17:22:36 5688 [Note] Plugin 'FEDERATED' is disabled.
2020-08-23 17:22:36 5688 [Note] InnoDB: Using atomics to ref count buffer pool pages
2020-08-23 17:22:36 5688 [Note] InnoDB: The InnoDB memory heap is disabled
2020-08-23 17:22:36 5688 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2020-08-23 17:22:36 5688 [Note] InnoDB: Memory barrier is not used
2020-08-23 17:22:36 5688 [Note] InnoDB: Compressed tables use zlib 1.2.3
2020-08-23 17:22:36 5688 [Note] InnoDB: Not using CPU crc32 instructions
2020-08-23 17:22:36 5688 [Note] InnoDB: Initializing buffer pool, size = 32.0M
2020-08-23 17:22:36 5688 [Note] InnoDB: Completed initialization of buffer pool
2020-08-23 17:22:37 5688 [Note] InnoDB: Highest supported file format is Barracuda.
2020-08-23 17:22:37 5688 [Note] InnoDB: The log sequence numbers 1771332 and 1771332 in ibdata files do not match the log sequence number 24084333 in the ib_logfiles!
2020-08-23 17:22:37 5688 [Note] InnoDB: Database was not shutdown normally!
2020-08-23 17:22:37 5688 [Note] InnoDB: Starting crash recovery.
2020-08-23 17:22:37 5688 [Note] InnoDB: Reading tablespace information from the .ibd files...
2020-08-23 17:22:38 1640  InnoDB: Operating system error number 5 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory. It may also be you have created a subdirectory
InnoDB: of the same name as a data file.
InnoDB: Error: could not open single-table tablespace file table_a.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.

I'm using VM and OS is Windows Server 2016 and Uniform Server.
If I set innodb_force_recovery > 0, nothing gets updated in db.
I have started mysql and apache as admin, also I have  tried deleting ib_logfile* and still nothing works.
In the other case, even if both mysql and apache are running, the web page shows like request can't be handled and these errors will be in mysql error log.
How can this be resolved?


texts: Is it good to save tempdb and log files in one drive?

texts:
My server has a very 40GB drive for OS, two 2TB drives and one 450GB drive. The server will have about 10 databases; the total data size of the databases is about 1.4TB. Most of the databases are using simple recovery mode. The users love to create big temp tables and sometimes they create large transaction logs.

Is it ok to put all the tempdb files and log files (include tempdb log file) in the 450GB drive?


texts:
I'm looking for a tools that allows migration of DB2/UDB databases to SQL Server.
It's quite easy migrate schema and data using  linked server or import export with SISS.

But what about stored procedures? We have a lot complex stored procedures.
There are some tools like the ispirer one -- which is too expensive and not so mature --
other one the swissql one is no more maintained.

What about other tools? Anyone have experience about? My focus is on stored procedure migration.


texts: How to increase mysql_connect_error on mysqlrouter?

texts:
I have an problem with mysqlrouter.
I try to increase the max_connect_errors variable on mysqlrouter but is still value 100 in the file log.
2021-03-02 14:30:59 routing INFO [7f31a73f3700] [routing:neonCluster_rw] fd=99 Pre-auth socket failure xx.xx.xx.xx:xxxxx: client auth timed out
2021-03-02 14:30:59 routing INFO [7f31a73f3700] [routing:neonCluster_rw] 1 connection errors for xx.xx.xx.xx:xxxxx (max 100)
2021-03-02 14:31:03 routing INFO [7f31a6cec700] [routing:neonCluster_rw] fd=79 Pre-auth socket failure xx.xx.xx.xx:xxxxx: client auth timed out
2021-03-02 14:31:03 routing INFO [7f31a6cec700] [routing:neonCluster_rw] 2 connection errors for xx.xx.xx.xx:xxxxx (max 100)

I've add the max_connect_errors variable with the value 512 in the config file but nothing change.
I have restart the mysqlrouter service after the change.
The configuration file:
[DEFAULT]
logging_folder=/var/log/neonCluster
runtime_folder=/etc/mysqlrouter/neonCluster/run
data_folder=/etc/mysqlrouter/neonCluster/data
keyring_path=/etc/mysqlrouter/neonCluster/data/keyring
master_key_path=/etc/mysqlrouter/neonCluster/mysqlrouter.key
connect_timeout=15
read_timeout=30
dynamic_state=/etc/mysqlrouter/neonCluster/data/state.json

[logger]
level = INFO

[metadata_cache:neonCluster]
cluster_type=gr
router_id=1
user=mysql_router1_p1apqt5eo8fl
metadata_cluster=neonCluster
ttl=0.5
use_gr_notifications=0

[routing:neonCluster_rw]
bind_address=0.0.0.0
bind_port=3306
destinations=metadata-cache://neonCluster/?role=PRIMARY
routing_strategy=first-available
protocol=classic
max_connections=1024
max_connect_errors=512

[routing:neonCluster_ro]
bind_address=0.0.0.0
bind_port=3307
destinations=metadata-cache://neonCluster/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic
max_connections=1024
max_connect_errors=512

[routing:neonCluster_x_rw]
bind_address=0.0.0.0
bind_port=3308
destinations=metadata-cache://neonCluster/?role=PRIMARY
routing_strategy=first-available
protocol=x
max_connections=1024
max_connect_errors=512

[routing:neonCluster_x_ro]
bind_address=0.0.0.0
bind_port=3309
destinations=metadata-cache://neonCluster/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=x
max_connections=1024
max_connect_errors=512

How can I do to increase this variable.
I do an error somewhere ?
Thanks for your help.


texts: How to store very very big Array in MySQL (separator / json_encode / serialize)?

texts:
I have project with saving timestamps from SCADA system to localhost MySQL-DB. All works fine but thinking about storing datas (read many threads and array storing isnt most efficiency method). Every produced piece is capture by sensor and current timestamp send to mysql database. My question is about most efficiency method for saving datas.
Current solution is using "/" separator like:
16484300/16484311/16484360/16484390/16484400

Every single piece is update statment {timestamp}/
Please what is your solution to store a lot of datas in database (something about 3000 values per day). PS: I'm thinking about serialize/json_encode to TEXT datatype (serialize wins for performance)
Thanks!


texts: Comparing dates in a BEFORE INSERT trigger

texts:
I need help making a TRIGGER that compares two dates being entered into a table, but I'm not 100% on the syntax I should use.

Something like:

CREATE TRIGGER chk_dates
   BEFORE INSERT ON `job_history`
   FOR EACH ROW
    BEGIN
      IF( NEW.end_date < OLD.start_date ) .../// error, don't insert data
      ELSE .../// allow the data to be entered

My table job_history will have a start_date, and when end_date is entered, I want it to check to ensure that it isn't prior to start_date.

How do I accomplish this?


texts: Passing each value from a temp table to a scalar function and sum values

texts:
I am relativity newbie in SQL so please do not  judge strictly. I have a table valued function.Within that function I have a this code:

 DECLARE @totalPayment DECIMAL(18,4),
         @totalPaymentConverted DECIMAL(18,4),
         @fromCurrencyIdForPaymentSystemsId CHAR(3);

 DECLARE @GetTotalPaymentAmount AS TABLE
 (
   Amount     DECIMAL(18,4),
   CurrencyId CHAR(3)
 );

INSERT INTO @GetTotalPaymentAmount
  SELECT    SUM(Amount),CurrencyId
  FROM      [dbo].[fn_DepositWithdrawReport]()
  WHERE     OperationTypeId = 2
  GROUP BY  CurrencyId;

This insert return met table like this

 A      C
-----|------
550  | USD
650  | EU
     |

I want to pass each of this values Amount and CurrencyId to the Scalar valued Function as Input parameter like this:

[dbo].[function_Valued](@totalPayment,@fromCurencyId,@toCurrencyId)

And sum that values for instance if we consider the table above:

[dbo].[functin_scalar](550, USD, EU) = 450
[dbo].[function_scalar](650, EU, EU) = 650

Get total 1100

Put simply I want to calculate sum amount and I need to convert it in one currency. I have been told to use APPLY but I cannot understand how to use it in my code. Maybe someone could show in code example?


texts:
We are migrating an application from Oracle to SQL Server. The vendor is responsible for the majority of the migration efforts, but there is one piece that was developed in-house that I'm having trouble with.

The old application on Oracle has a procedure that collects specific data and then another procedure inserts that data into a staging table in our revenue system (a different Oracle server). This is an Oracle procedure via an Oracle Public Database Link.

In SQL Server, I'm trying to rewrite this process using SQL Server's Linked Servers. I've created the Linked Server connection to the revenue system and it can connect via that link.

From SQL*Plus, I can issue this statement as that user:

select * from rs.rs_myapp;

And I get results. (rs is the revenue system user, not the user I connect as. rs_MyApp table is the staging table for our data in their system.)

But if I attempt to do this in SQL Server:

select * from [rstest-Link]..rs.rs_myapp

I get this message:

  Msg 7314, Level 16, State 1, Line 50 The OLE DB provider
  "OraOLEDB.Oracle" for linked server "rstest-Link" does not contain
  the table ""rs"."rs_myapp"". The table either does not exist or
  the current user does not have permissions on that table.

I can do this, however:

select * from openquery([rstest-Link], 'select count(*) from rs.rs_myapp')

And that gets results back, showing that the link does work.

However, I cannot complete the SQL Server procedure, because of that 7314 error. The procedure is really basic, it has exactly one line of code in it:

Insert Into [rstest-Link]..rs.rs_myapp (ACCOUNT_NUMBER, FROM_DATE, TO_DATE,
        Data1, Data2, SAMPLE_DATE, Content1, Content2)
    select ACCOUNT_NUMBER, FROM_DATE, TO_DATE, MyData1, MyData2, SAMPLE_DATE, MyContent1, MyContent2
    FROM dbo.RS_INTERFACE;

But without resolving the 7314 error, there's no way to create the procedure.

So how do I do this? Am I going to have to use a cursor and issue some sort of code for each row, like:

INSERT OPENQUERY ([rstest-Link], 'SELECT ... FROM ...')
VALUES ('...');


texts: Alternatives to joins using Linked Server and OPENQUERY

texts:
In my environment, we have a server that stores sales data and another server that stores our HR data. For some reports, we need to combine sales data with employee data. Our sales server contains various tables with millions of rows. The employee table on the HR server contains about 12,000 rows.

On the sales server we have the HR server setup as a Linked Server. For employee sales reporting, we run the main queries on the sales server. Typically this involves joining multiple tables on this server and a single join across to the HR server to the employee table.

As you probably suspect, we are having performance issues. I've tried using both four dot nation  and OPENQUERY.

I haven't seen a major difference in performance between the two. Both are bad. If the query optimizer picks a plan where the remote table is queried once, performance is acceptable, even if all 12,000 rows are returned. Unfortunately, I have seen execution plans where the remote table was hit thousands of times in a single query.

I've considered simply creating a local table and running a SQL Agent job to refresh it from the HR employee table every hour or so. Although not ideal, this would be acceptable because the HR employee table doesn't change all that often.

Are there any other approaches I should be considering to solve my performance issue?


texts: MySQL - Where can I find metrics on the performance of Blob vs file system?

texts:
I know and understand that there are performance hits in storing blob data in the database, but the blob portion of the data is going to be rarely retrieved/viewed, it is for smaller data (the vast majority under 256k with a max of 10mb), it is not going to be used by most customers, and the total rows is expected to be relatively low, very likely under half a million, if not less. Also some of the data is dynamic and can change for some users, as in it's not static images. In other words we're at the edge of whether or not it's worth it.

I keep reading that it's better to store in the file system but I can't find actual metrics that show the performance difference, just people repeating each other without any concrete proof or metrics. For us it may be worth the performance cost in exchange for being fully ACID as well as guaranteeing that all our backups are completely synched.

That being said does anyone know or have any real world metrics to show the performance difference between storing items as blobs vs in the file system. I'm trying to understand if the performance penalty is worth it or not rather than blindly following the general rule of thumb and after spending at least 2-3 hours and I've yet to be able to see anyone show any actual numbers. It's all just words with nothing concrete.

By the way this is a MySQL InnoDB table. The actual data table has a link to the blob data table, so the blob is not in the main able and is only retrieved when need be to avoid any I/O issues. In other words instead of the path to the data on the filesystem, it's an ID to another table with only blobs. How does that compare in terms of performance? Is it 25% worse? Is it 100%? Is it 200-500%? Is it 1000%?

If the cost is only 100%-200% it is probably worth it for us because again the data is rarely retrieved. So even if we had say 10,000 concurrent users, maybe only 50 users would be retrieving their blob data concurrently at best. Yes the data is specific to each user, it isn't images.


texts: What is better - having 10 databases for 10 organisations or 1 table for 10 organisation with org_id?

texts:
I am making a web service (using Laravel in case that is relevant) that will have many separate organisations  generated by the service.
Considerations

I can manage all organisation data in one database by assigning an org_id column in each table, but when a row is fetched it will have to be inquired from all organisation rows (which I think will take more time).
Another solution is to make separate database for every organisation, but then I think I have to switch databases frequently and for each request causing more database connections and in turn causing more server processing involved.

Question
So I want to ask what approach will best suit my needs.


texts: Rewriting query to move subquery out of FROM clause

texts:
Suppose I have an entries table that looks something like this:
| id | category | text | user_id |
|----|----------|------|---------|
| 1  | A        | z    | 22      |
| 2  | B        | y    | 23      |
| 3  | B        | x    | 24      |
| 4  | C        | w    | 25      |

id is unique; none of the other columns are
I want to get the row for specified category(ies) that have the highest ID.  So in this example if I'm search for category B I'd expect it to return 3,B,x,24
There are several more columns in the table, but only those three are shown for brevity.  Other columns exist which have to be joined, e.g. to get user info.
I have the following query, which works:
SELECT *
FROM (
         SELECT MAX(id) as id
         FROM entries
         WHERE category = 'B'
         GROUP BY category
     ) as tmp
         LEFT JOIN entries e ON e.id = tmp.id
         LEFT JOIN users u ON (e.user_id = u.id)

An OR condition can be added to the WHERE clause when I need to select multiple categories, and that works.
However my ORM doesn't support subqueries in the FROM clause.  I managed to get it to work with WHERE ANY, but the query is 4.5 times slower:
SELECT *
FROM entries
WHERE id = ANY
    (
        SELECT MAX(id) as id
        FROM entries
        WHERE category = 'B'
        GROUP BY category
    );

Replacing ANY with ALL yields the same data but ALL takes at least 5 times longer than ANY.
Is there another syntax that runs as fast as the first but with some other syntax?


texts:
Very similar to https://serverfault.com/questions/163442/large-insert-update-in-table-causes-timeout-while-sql-server-re-arrange-indexes only I am not using SSIS with only occassional large (predominently annual) inserts from another staging table in a stored procedure and the target table is partitioned with data being written to an existing partition. All indexes are aligned.

I've tried setting AUTO_UPDATE_STATISTICS_ASYNC ON but that doesn't seem to help. Queries are still blocked for about 10 minutes while the indexing catches up even when they should use partition elimination and not hit the inserted data.

I'd rather have a fast but stale result than a time-out.

Even after

ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS_ASYNC ON
GO

sp_helpdb MyDB.status

still reads

  Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
  Recovery=SIMPLE, Version=655, Collation=Latin1_General_CI_AS,
  SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics,
  IsFullTextEnabled

Shouldn't it have something to indicate that async is on?

Is there anything else I can do to make sure queries aren't slowed down?


texts:
I have inherited a MySQL instance currently being used by an enterprise software suite. I have no idea what the credentials are for any account, but have full admin rights to the server that the instance is installed on.

I am not very knowledgeable about MySQL. Is there any way I can either add a new admin user or find out the details of an existing user of some kind?

I have looked in what seems to be the my.conf file I think is being used, but there are only various directories and server ports being set there.


texts: Will a VM backup of a SQL Server work for restoring the SQL database

texts: I am wondering if a VM backup will restore a SQL database with out integrity issues....  Ie…    we backup SQL nightly and keep 3 copies, we backup with VM and keep 14 copies....  if we had to restore day 10 from VM would we have any integrity issues, I know we could not do log recovery, but that point in time would be better than nothing.  We have completed a test restore and it seemed to work, but I would like the experts answers also.   Thanks


texts: Mysterious inequality behavior when comparing date strings

texts:
Can someone explain this mysterious Postgres behavior? I'm performing the following queries at the psql command line.

mydb=> select '20150526' > '2015-05-25';
-[ RECORD 1 ]
?column? | t

mydb=> select '20150526' > '2015-05-26';
-[ RECORD 1 ]
?column? | f

mydb=> select '20150526' > '2015-05-27';
-[ RECORD 1 ]
?column? | f

If Postgres was actually comparing the strings directly, either all three would be true or false, depending on how character ordering is defined. This seems to establish that Postgres is trying to convert the strings to dates before comparison, presumably based on some heuristic about the formats. However:

mydb=> select '20150526' < '2015-05-27';
-[ RECORD 1 ]
?column? | t

mydb=> select '20150526' < '2015-05-26';
-[ RECORD 1 ]
?column? | t

mydb=> select '20150526' < '2015-05-25';
-[ RECORD 1 ]
?column? | f

Note that the result of the middle query is incorrect, if interpreted heuristically as dates! The inverse issue occurs with the >= operator. This caused an extremely subtle bug in a trigger function I'm using.

So, two mysteries:

Postgres seems to be heuristically interpreting these strings as dates in order to do comparisons. This is a little surprising, so I would like confirmation.
The results are wrong for the < and >= operators when the dates are the same but in different formats. This seems like a bug.


texts: How to select all results tied for first position

texts:
My current output is a list of caretakers and how many budgies they take care of. I get it via the following SELECT statement:

SELECT caretakers.name, COUNT(species.name)
FROM caretakers
JOIN cares_for ON cares_for.caretaker=caretakers.id
JOIN animals ON cares_for.animal=animals.id
JOIN species ON animals.species=species.id
WHERE species.name LIKE 'budgie'
GROUP BY caretakers.name
ORDER BY COUNT(species.name) DESC

Animals is a table containing specific distinct animals and their names.
Species is a table containing species with id tags to which 'animals can link to (via animals.species)'.
Caretakers is a table containing specific distinct people and their names
Cares_for is a table containing ids of specific animals that are being cared for and the id of the specific caretaker that cares for them.

My current output is 33 rows, all but 2 of which only take care of 1 budgie. The two others each take care of 2 budgies.

How would I make it so that only the caretakers tied for the first place get displayed? I've tried using MAX(Count(...)) in various places, even restructuring WHERE to HAVING, but everything returns an error.

Help would be welcome.


texts: SQL, list if has at least one value for each property

texts:
I have the following query:

SELECT
    m.MilgaId,
    opt.PropertyId,
    sd.PropOptionId As StudentOptId

FROM
     Milga AS m
        INNER JOIN
        PropertyOptionInMilga AS poInMil
            ON m.MilgaId = poInMil.MilgaId
        INNER JOIN
        PropertyOption AS opt
            ON poInMil.PropertyOptionId = opt.PropertyOptionId
        LEFT OUTER JOIN
        StudentData AS sd
            ON opt.PropertyOptionId = sd.PropOptionId AND sd.UserId = 'A8F60C49-960F-4A6D-A759-925077607C5A'
WHERE
    (m.IsEnable = 1) AND
    (m.IsSpecial = 0)

    GROUP BY
        m.MilgaId,
        opt.PropertyId,
        sd.PropOptionId,
        m.IsEnable,
        m.IsSpecial
    HAVING
        (
            m.IsEnable = 1 AND
            m.IsSpecial = 0
         )
    ORDER BY
        m.MilgaId

Query results:

MilgaId PropertyId  PropOptionId
937       90            2291
937       132           null       <-----  132 with null
937       132           2434       <-----  132 has one without null so it's in result
938       25            38
938       91            NULL        <-----
938       132           2434
939       201           2600
939       202           null

I would like to get all the rows for MilgaId when all its PropertyId has at least one value

The is the results I need:

MilgaId PropertyId  PropOptionId
937       90            2291
937       132           2434

** MilgaId=937 is in the results because it has two propertyId: 90 & 132, while 132 is null but also 2434.

Milga=938 is out because for PropertyId=91 there is no value.


texts: What is the safe+fast way to move all Postgresql data to another machine?

texts:
I need to copy all data from one Postgresql 9.2.4 server to another (both on similar hardware, Ubuntu 12.04). Is it safe to shut down the source Postgres server, rsync the data directory to the new server and start up both servers? Do I need to copy the pg_xlog directory as well? Or is it ok to leave that out since the source server will be shut down cleanly (service postgresql stop)?

I have approx 275G of data so don't want to backup and restore as that will take ages.


texts: Which of MultiStatements is "out of sync"

texts:
How do I find out which statement is out of sync when I get error 2014 with Multi-Statements?

I am calling libmysql.mysql_query with a SQL Connection with SupportsMultiStatements, passing a string of commands separated by ;.
This code runs all day without errors.  But every few days (with no regularity) my application throws:

  2014 - Commands out of sync; you can't run this command now.

Retry always works.

The error message does not specify which of the multiple commands is "out of sync".  Is there any way to know?

This question is not how to fix my specific problem, so I haven't included the list of commands. (I suspect the intermittent problem is caused by a timeout waiting for table lock.)  This question is about how error 2014 applies to multi-statements.


texts: UPDATE with LIMIT / OFFSET in PL/pgSQL function

texts:
In my Postgres 9.2 database I've got the following SELECT, which will be used by a PL/pgSQL function:

SELECT
      id
FROM
      tablea
WHERE
      migrated = false;

I want to get 2000 records each time and perform something like the following:

CREATE or REPLACE FUNCTION migrate_data()
RETURNS integer AS
$$
declare
        row record;

BEGIN

FOR row IN EXECUTE '
        SELECT
              id
        FROM
              tablea
        WHERE
              migrated = false
'
LOOP

INSERT INTO tableb (id)
VALUES (row.id);

UPDATE tablea a SET migrated = yes WHERE a.id = row.id;

END LOOP;

RETURN num_rows; -- I want it to return the number of processed rows

END

$$ language 'plpgsql';

How can I tell the function to process 2000 records with every call? And it must start from where it left when on the next call.

Example:

select migrate_data(); -- id 0 to id 2000

select migrate_data(); -- id 2001 to id 4000

select migrate_data(); -- id 4001 to id 6000

select migrate_data(); -- id 6001 to id 8000

Etc.


texts: varchar(MAX) text cuts off while going more than 8000 characters inside a procedure

texts:
I have a procedure in which i have declared a variable with datatype varchar(MAX),

declare @str varchar(MAX);

set @str='select * from Table1...'

print (@str);
exec (@str);

but when the text written inside @str goes above 8000 characters (i.e. 8193 characters) it cuts off the string and prints only 8000 characters and executing it gives error off course.

I have tried 2 solutions after some search, but it doesn't work.

1) I have tried using 2 variables of same type "varchar(MAX)", and concat it at the time of execution, but doesn't works.

2) I have tried to cast "into varchar(MAX) again" both the variables before concatinating it, and then execute it, but that also doesn't works.


texts:
I was in an SQL class where the instructor said "DBAs don't like it when users create views, because they will clobber tablespace."

Obviously, I understand that any schema object will consume some tablespace, but isn't it a huge exaggeration to say that views will eat up tablespace, since all you need to store is just a query (and privileges and some other metadata), and as such they won't take significantly more space than an ordinary row in an ordinary table?


texts: Why SSAS tries to connect to the default instance?

texts:
I've installed SSDT and while tring to browse the cube I get this error

Here it tries to connect to port 2383 which is for the default instance, but in my case SSAS is installed for a named server under port 2382. How to correct this error, so the system could understand that this is a named server?
everything is installed locally, and port 2382 is open, firewall is turned off.
Should I change the port number in Visual Studio?
Thanks