texts: Best practice for working with materialized views in Postgres

texts:
As background, I work with many materialized views which are based on PostGIS spatial queries, some of which take days to refresh (these views are infrequently refreshed whenever the underlying spatial data (ie road networks) are updated). I then have many other views which are dependent on these materialized views.

While using materialized views in general works well, it becomes a nightmare when I need to alter the definition of one of the views (for instance, changing a join condition or adding new columns). Since there's no equivalent of CREATE OR REPLACE ... for materialized views, I end up having to delete and then recreate all the dependent objects.

Recently I've started "wrapping" all my materialized views in standard views as a way around this. So basically every materialized view has a corresponding standard view which selects all columns direct from the materialized view. Then no dependents directly reference the materialized view, they only ever reference the wrapper view. This allows me to temporarily replace the definition of the wrapper to point at a different data source (not the materialized view), so I can then alter the definition of the materialized view and then lastly re-direct the wrapper back to the materialized view. Phew! It works, and avoids the need for the cascading drop/recreate object process, but still seems very clunky.

Is there a best practice process for working with materialized views to overcome these limitations? What's the usual approach to take here to avoid the cascading delete/recreate steps?


texts:
I am running SQL Server 2012 and and using the March 2019 release of sp_blitzCache to analyze some slow running queries on the system.

I have executed the stored procedure with parameter @SortOrder = 'Reads' and found some queries reading tons of pages (in the millions) that need to be reduced.

The odd thing is that none of these queries are returning any information for the memory grant columns, they are all null.

Why is that we can have a query returned from sp_BlitzCache doing many reads and writes, but contain nulls for all memory grant columns?

Thanks,
Kevin


texts: "Table or view does not exist" on synonym

texts:
I have a database and a USER administrating it. Then I have an additional USER_APP to whom I granted CRUD access to all tables in my oracle data modeler (and then applied these via generated script )

I created a synonym for every table from USER_APP account, with the following syntax:

create synonym USER_APP.CLIENTS for USER.CLIENTS;

And for every table generation is successful and commit made.
But when I try to use it anyhow on that account, for example:

select * from CLIENTS;

I get an error that CLIENTS do not exist, namely

ORA-00942 : table or view does not exist.

What can I do to fix that or where should I look to determine the cause/solution?


texts: Query result in a daily date from a date range selection Postgres 9.3

texts:
I need to make a report that will output a result in a daily row result based on the datarange selected.

Example:

Table: sales
->id
->type
->gross
->created_at
->updated_at

For example my query is

SELECT COUNT(case when type = 'Completed Sale' then id end) as complete,
       COUNT(case when disposition = 'Partial Sale' then id end) as partial,
       SUM(gross)as revenue
FROM sales
WHERE created_at >= '2015-09-20 00:43:32' AND created_at <= '2015-09-24 18:43:32';

The range is Sept 20 to Sept 24

So basically the output will be like:

complete | partial | revenue
1          1         2.3

What I want the output to be like this

start date | end date    | complete | partial | revenue
___________|_____________|__________|_________|____________
2015-09-20 | 2015-09-21  | 1        | 1       | 2.3
2015-09-21 | 2015-09-22  | 3        | 4       | 4.3
2015-09-22 | 2015-09-23  | 3        | 4       | 4.3
2015-09-23 | 2015-09-24  | 4        | 4       | 3.3

So as you can see, the output is the daily basis of the start date and end date. If choose 1 month range then there will be 30 or 31. Is that possible?


texts:
I'm new in "databaase administration"
the problem I am facing is that I want to have an exact copy of a table, from MySQL database (website database) to oracle database (a local one).

What I've done till now

Searched for two days.

I installed MySQL connector/odbc on the 'oracle machine' and successfully connected to the remote MySQL database

Also I can execute queries from the oracle machine like:

select * from remote_table;

and it shows the expected results,

The problem here is:
Is there a way to sync these two databases (mySQL to oracle), or at least update the local one every midnight..

About the two machines

Both machines are running Windows OS

local one :
Running Windows XP
Oracle 8i

remote one :
Running Windows 8.1
MySql 5.6.17


texts: How to handle too many instances of multiple tables?

texts:
If there is a table accessed by multiple DB threads ( for example
100) at a time then how many instances of that table will be opened?
If I run show open tables and see that in_use column value of a
table is 100. Does it mean 100 instances of that table is opened in
memory at a time?
Which system variables I should optimized to reduce the number of too many instances of table and Which status variables can be used to monitor these system variables?

Please note that there is a lot of open tables with multiple instances...


texts: Is TEXT to VARCHAR(MAX) an implicit conversion?

texts:
I am copying TEXT columns to another table where the destination column is VARCHAR(MAX).

It appears that this is an implicit conversion and that I do not need to write an explicit CAST for it.

Is this correct?


texts:
I recently crossed over from being a Java developer to an actual DBA in our company. I am learning the ropes, so to speak, about being a DBA (which is actually somewhat of a new position for our company).

I have seen several scripts where we run the command DB2 BIND bind_file other_parameters.

I am puzzled by what these do. I have asked our other DBAs, but they were not able to explain it to me in a way that made sense. I have looked at IBM's Information Center for the BIND command, but it wasn't clear to me either.

I know binding is somehow important, because we are supposed to run REORGS, run STATS, and re BIND on our databases regularly to assist with performance.

Since I am still a n00b DBA, I was wondering if anyone can provide a "What is BINDing for Dummies?" explanation?

  EDIT: In edition to the answer below, I recently came across the following developerworks article: "DB2 packages: Concepts, examples,
  and common problems: Understanding DB2 system and user application
  packages". Very helpful. Especially for the system packages, which
  is what we were mostly running into.

  20130905 EDIT: This blog entry by DB2 DBA Ember Crooks is stellar with regards to binding and what it means. She also wrote a
  previous entry on packages not being found and when to up the
  CLIPKG number for the binds and what that means. These articles are
  very well explained. Basically like reading "DB2 Binding and Packages
  for Dummies" if such a thing existed.


texts: MySQL 5.6 DateTime Incorrect datetime value: '2013-08-25T17:00:00+00:00' with Error Code 1292

texts:
I'm using MySQL 5.6 and I have a program that runs the following SQL statement against my database:

UPDATE `m_table` SET `s_time` = '2013-08-25T17:00:00+00:00' WHERE id = '123'

Unforutnately, I get the following error:
Incorrect datetime value: '2013-08-25T17:00:00+00:00' for column 's_time' at row 1

The datatype for s_time is DateTime.

I have already attempted to set the allow_invalid_dates property using the workbench.

Can anyone understand and please explain this error to me?  I know that if I manually change the statement to UPDATE m_table SET s_time = '2013-08-25 17:00:00' WHERE id = '123', the statement works.

Unfortunately, I cannot modify the program that supplies the SQL statement (which I'm told is valid by the creator of the program) and I also cannot understand what the +00:00 symbolises.

Thanks


texts:
I am using MYSQL

SELECT  m.proposalId, m.title, n.stageNumber, n.committeeId, n.modifiedDate,
        o.msNumber , o.description,o.ics,o.edition
    FROM  mystands_Proposal m
    INNER join  mystands_ProjectLifecycle n on m.proposalId = n.proposalId
    INNER join  mystands_Project o          on m.proposalId = o.proposalId
    WHERE  n.newState=0
      AND  n.committeeId=79827
      AND  (n.assignedTo=29913 OR  n.actionBy=29913 )
      AND  n.proposalId LIKE '%sdas%'
      AND  o.projectNumber LIKE '%sdass%'
      AND  n.stageNumber=40.92
      AND  o.category=1
      AND  o.degreeofCorrespondence=1
      AND  o.msNumber LIKE '%sdas%'
      AND  (n.modifiedDate <='2015-05-15' AND  n.stageNumber=40.2 )
      AND  (n.modifiedDate <='2015-05-07' AND  n.stageNumber=30.99 )
      AND  (n.modifiedDate <='2015-05-27' AND  n.stageNumber=55.99 )

I am doing inner joins for three tables for search functionality and in java if the user enters the values then I am appending the value to the sql query on the fly. The above code is working fine for AND operations between fields.

How do I perform "OR" operation for the fields entered by the user and display the result. I have tried this:

SELECT m.proposalId, m.title, n.stageNumber, n.committeeId, n.modifiedDate, o.msNumber , o.description,o.ics,o.edition FROM mystands_Proposal m
INNER join
    mystands_ProjectLifecycle n on m.proposalId = n.proposalId
INNER join
    mystands_Project o on m.proposalId = o.proposalId
WHERE n.newState=0
      OR  n.committeeId=80246
      OR  (n.assignedTo=79977
              OR  n.actionBy=79977
          )
      OR  n.proposalId LIKE '%ads%'
      OR  o.projectNumber LIKE '%sds%'
      OR  n.stageNumber=30.99
      OR  o.category=1
      OR  o.degreeofCorrespondence=1
      OR  o.msNumber LIKE '%sadsa%'
      OR  (n.modifiedDate <='2015-05-22'
              AND  n.stageNumber=40.2
          )
      OR  (n.modifiedDate <='2015-05-22'
              AND  n.stageNumber=30.99
          )
      OR  (n.modifiedDate <='2015-05-29'
              AND  n.stageNumber=55.99
          )

Now what is happening is it is giving me the results of the inner joins because newState=0 is true and entire where condition is getting true and I am getting the results of inner joins on three table and the result is not being filtered as desired.

Can you please help where am going wrong? Thanks.


texts: Is there an upper limit to the SqlDbType.Structured ADO.NET type?

texts:
Is there an upper limit to the number of rows\total size of the SqlDbType.Structured type when sending data to a Table parameter in a sproc?

I can't find anything to suggest there is so I'm assuming that there'd just be a time-out if a giant data set was sent.

I'm looking at 2000-ish rows, so nothing frightening.  I just want to preempt any potential explosions.


texts: How to design a Table in which its fields varies on its categories?

texts:
I am trying to design a database to post free ads. Eg http://www.gumtree.com.au/p-post-ad.html

Scenario: Every ad will have different categories and some categories will have further sub categories. SO the table AD will have different fields depending on its category. For example, an ad for used car will have MAKE, MANUFACTURER etc but an ad for books will have Book title field, Author field etc.

SO how do I design the table for ads?

I can possibly think of two solutions. One, to create subtypes for categories in table ad. Two, to create individual table for each categories of ad (this does not sound like a good design).


texts: MS SQL Server Copy Database Wizard vs Backup / Restore Script

texts:
I am working on a live SQL Server database. It was just recently migrated from another server. In the old server the backup was done using a job with Copy Database Wizard. When the database was migrated to the new server, the job wasn't restored along with the database, so the new database doesn't automatically run a backup job.

I'm not familiar with the copy database wizard, so my question would be is there any disadvantage of running a job with sql script using BACKUP and RESTORE as opposed to the Copy Database Wizard ? Is there any risk if I run the backup using script as opposed to the CDW ?


texts: SQL Server total memory less than target memory, lot of extra memory unallocated

texts:
The situation is the following:
We have a SQL2008r2 server (64bit on 64bit Windows) with SSRS installed on it. The Max Memory is set to 6GB, the server has 12GB total memory.

The target server memory is always more than the total server memory (for instance total: 2758MB, target: 6144MB = 6GB, which is the max memory).

My problem is that the server has 6GB free memory, but the SQL Server is not using the available space, nor SSRS, or other process. Can anyone tell me is this normal? I've found nothing regarding this topic online.


texts: csv file with fields having commas in them

texts:
I have a CSV file with fields given as follows.

number1, string1, "string2, string3", number2, string4, "string5, string6"

where number's are numbers and string's are strings.  I am trying to read this as follows, I am ignoring first header line

LOAD DATA LOCAL
  INFILE 'filename.csv'
  INTO TABLE table_name
  FIELDS TERMINATED BY ','
  IGNORE 1 LINES;

But due to embedded commas in some fields, this command is giving causing to
overlap the fields. How would I correctly do this in MySQL?


texts:
My database have been up and running for a while with query cache enabled, but there are 0 hits. I have tried to check the settings but I am not sure what the problem is. Using InnoDB.

mysql> SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+------------+
| Variable_name                | Value      |
+------------------------------+------------+
| query_cache_limit            | 268435456  |
| query_cache_min_res_unit     | 4096       |
| query_cache_size             | 4294967296 |
| query_cache_strip_comments   | OFF        |
| query_cache_type             | ON         |
| query_cache_wlock_invalidate | OFF        |
+------------------------------+------------+

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| Qcache_free_blocks      | 622        |
| Qcache_free_memory      | 4291758416 |
| Qcache_hits             | 0          |
| Qcache_inserts          | 1471501    |
| Qcache_lowmem_prunes    | 0          |
| Qcache_not_cached       | 8995948    |
| Qcache_queries_in_cache | 2269       |
| Qcache_total_blocks     | 5199       |
+-------------------------+------------+

Any ideas ? I saw some other similar questions like this - the answer just suggests turning it off though without any good explanation except "the InnoDB Storage Engine likes to play traffic cop with the query cache".


texts: query to concatenate columns if record duplicate

texts:
My table has two column that is id and name. I want to do concatenate the columns if the if more than two names are same like:

id                                                                    name
1                                                                     John
2                                                                     David
3                                                                     Zed
4                                                                     John
5                                                                     David
6                                                                     John
7                                                                     David
8                                                                     John
9                                                                     John

Now I want my output that if name is repeated than the first record will remain as it is and other will be concatenated by id like

Id                                                                     Name
1                                                                      John
2                                                                      David
3                                                                       Zed
4                                                                      4 John
5                                                                     5 David
6                                                                     6 John
7                                                                      7 David
8                                                                      8 John
9                                                                      9 John

Can anyone please help...Thank you


texts: Combining log shipping and regular log file backups

texts:
Following on from my earlier question, I'm trying to set up a combination of Log Shipping and database backup. What I have in place is:

Log Shipping set up between my two servers, to run every 5 minutes
A (bespoke) process that takes a full backup every night at midnight, and log backups every hour

Log Shipping appears to be working OK; however, I cannot get my backups to run. The "full" backup is fine, but if I try to restore to a "point in time" after a few backups, I'm getting lots of errors similar to:

  The log in this backup set beings at LSN xxxxxxx, which is too recent to apply to the database. An earlier log backup that includes LSN yyyyyyy can be restored

Having dug around the logs and history tables, I can see that the "backup" performed by Log Shipping is chipping away at the transaction logs, and presumably "stealing" a chunk of the log, meaning there's a break in the chain of my backup logs.

I've tried changing my backup process to use COPY_ONLY when backing up the logs, but this hasn't made any difference.

Is it simply impossible to combine Log Shipping with regular full/log file backups, or am I missing something?


texts: MySQL: columns with mixed character encodings, and finding columns which have multibyte data

texts:
I have some rather large CSV files that I am loading into my MySQL 5.7 database.  The files are several gigabytes in size, several million lines long, and have large column widths that must be used in joins (sometimes as long as ~500 characters).

The data is all standard English characters, and most of the columns can fit into a single byte character set like latin1.  However, several of the columns require unicode for things like trademark/registered/copyright symbols, measurement symbols (inches, feet, radius, etc), and therefore I've been using utf8mb4 on all tables.

The problem with doing this is twofold.  It blows up our index sizes, so in some cases, we can't create an index on a column(s) because the width becomes greater than 3072.  Additionally, it seems to be having a significant performance impact, presumably because the data size is 4x.

What I'd like to do is use latin1 on all columns in the table, and only utf8mb4 on columns that need it.  This leads to my questions -

What's the best way to identify for sure which columns are actually storing multibyte characters?  Can I detect that somehow, either within my CSV prior to loading (using python/pandas maybe?), or from within the database?  The files are stored as utf8.  They are currently loaded into a utf8mb4 table.  If I could easily scan the table and say "this column contains no multibyte data", I could change it to latin1.

Second, will I run into problems if I try to create composite indexes with columns using different encodings?  Say column A is utf8mb4 and column B is latin1.  Is there anything wrong with creating an index on these two columns?  ie: CREATE INDEX my_index
ON my_table(A, B);.  I'm assuming there's no issue doing that.


texts: Sync a local MySQL database and a live server MySQL database

texts:
I want to store data in my local MySQL database and after a time interval or when system connected to internet it will update my live MySQL database remotely. Is it possible?

I have an inventory management script in PHP MySQL. The problem is in our country internet connection drops suddenly. So, I want to first write to the local MySQL database and then it will update the real server. In any situation like internet connection dropped, the script will work locally to gather data and retrieve data. When the internet connection comes back it will update the data to the real server.

Other user will use the same system at the same time. That time, the real server will also be updated. So, when two database will merge, it will consider time and rearrange its invoice number and other things.


texts:
This is my table structure and a sample query.  However, my query returns these results:

ID   event1  Exists
1    cycle   No
1    cycle   Yes
2    run     Yes
3    walk    No

As you can see ID 1 is returned twice, a yes and a no value.  I only want each item from event1 returned 1 time in my query.  Desired output is:

ID   event1  Exists
1    cycle   Yes
2    run     Yes
3    walk    No

Here is sample table structure and my query (which please feel free to completely re-write), what must be altered in order to return my desired result set?

create table #robschneider
(
  ID int IDENTITY(1,1) PRIMARY KEY
  ,event1 varchar(100)
  ,uuid int
)
create table #bigponyhorses
(
  ID int IDENTITY(1,1) PRIMARY KEY
  ,uuid int
  ,empid varchar(10)
)
Insert Into #robschneider Values ('cycle', '1'), ('run', '2'), ('walk', '3')
Insert Into #bigponyhorses VALUES ('1','mn12'), ('2','mn12'), ('3', 'cr23'), ('1', 'bb11')

Declare @empid varchar(10)
Set @empid = 'mn12'

select
DISTINCT
[rs].[ID],
[rs].[event1],
case
  when [bph].[empid] = @empid AND [bph].[uuid] is not null then 'Yes' else 'No'
end as [Exists]
from #robschneider rs
LEFT join #bigponyhorses bph
on [rs].[uuid]=[bph].[uuid]

If it exists, I want a yes returned, if it does not exist I want a no returned.


texts: Log Shipping or Always on as DR for SQL Failover CLuster

texts:
Which is easier to maintain as Disaster Recovery on a remote site for a 2-node SQL Server Cluster

Log Shipping
Single Instance of Always on Availability Group Instance.

Any suggestion will be highly appreciated.


texts: How mysql (innodb) handles auto increment column

texts:
I am working on a OLTP environment with multiple parallel write operation. I found this in documentation about auto increment locking but still i am interested to know much about it in details. Can any one tell me how mysql handles auto_increment column in below cases by using AUTO-INC locking.

1.When there are multiple mysql users performing inserts in parallel ?

2.When a user updating maximum value of the auto increment column and the same time another user is inserting a new row ?

3.how mysql handles before insert trigger when there is an update column inside the trigger changes auto increment value to be inserted ?

4.If any extended insert is in progress and let assume it run for 2 seconds then any other inserts on the same table executed at the same time will wait to acquire auto-inc lock ?

Also tell me is a there any work around to get rid of this bug related to auto increment and triggers.


texts: Is it ok to install a SSAS 2016 instance next to a SQL Server 2014 instance?

texts:
Should I expect any problems installing a SQL Server Analysis Services 2016 tabular instance next to a SQL Server 2014 database engine? (e.g. due to an update of some shared components)

I'm mostly worried about any issues related to the running database engine.

Thanks.


texts: Cost / Capacity Planning: Quantify Data Ingress / Egress

texts:
To evaluate the migration of an on-prem SQL Server to a cloud-hosted VM, I want two fundamental pieces of information:

The list of clients that access the on-prem DB Server. I'd like to know: client app name, client hostname, target database name and username.
The amount of data trafficked for each such client for a given unit of time.  For example, I'd like to know that client "SQLCMD" projected 2 MB of data from the DB in the last hour, or that it wrote 4k of data in the last hour.

I'm successfully gathering the data I want for the first item.  I'm simply using the following event session:

CREATE EVENT SESSION [Connections] ON SERVER
ADD EVENT sqlserver.connectivity_ring_buffer_recorded(
    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.username)),
ADD EVENT sqlserver.login(SET collect_options_text=(1)
    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.username)),
ADD EVENT sqlserver.logout(
    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'c:\xevents\connections')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

However I have not found a way to gather information for item #2 - total data trafficked per unit time.  Since many cloud providers will bill on the basis of data ingress or egress, I'd like to gather this information to make cost estimates for running the migrated database in the cloud.

I spotted an article about SQL Server Performance Dashboard metrics.  It claims that the dashboard can provide:

Network InterfaceBytes Sent/sec
Network InterfaceBytes Received/sec

If this is not directly what I need, it certainly is the foundation.  The article claims that the dashboard uses Perfmon, DMV's, and Extended Events to attain such metrics.  Unfortunately, the article neither shows examples of the above metrics, nor explains which approach (DMV's? XEvents?) offers it.

Does anyone know how I can obtain this information, preferably using just DMV's or XEvents?

There are multiple versions of SQL Server i'm targeting for this effort: 2012, 2016 and 2017.

The Performance Dashboard I already mentioned is now built-in to SSMS.  That dashboard gives me total logical and physical reads and writes for the top queries (since the DB server came online?).  That's cool, but not what I'm looking for; it is not clear how "logical" or "physical" IO can be converted into raw kilobytes of data sent/received.  Also, I need this information aggregated preferably for all queries, not just the top 10.

I also notice that the SSMS standard report for "Activity - Top Connections"   seems closer to my goal, because it indicates how many "packets" were sent or received per connection.  I might be able to convert "packets" into an actual data-size metric per connection.

Any advice on how to proceed is appreciated.


texts: How to run pg_dump so that it doesn't slow down other connections?

texts:
I am running a daily pg_dump on PostgreSQL 9.3.14 database of approx. 50 GB of size:

pg_dump -Fc dbname > file

The problem I am having is that while pg_dump runs, which takes several hours, all the other database connections are extremely slow.

Is there any way to make the pg_dump consume less resources or is there a more proper way of creating periodic backups that doesn't slow down the db server so much?

E.g. would running pg_dump from a different server help the database I/O on its working server?


texts: How does the slave database server back up the logs of the master database server?

texts:
Now that the master-slave replication architecture for MySQL 8.0.24 has been built, how does the slave database server back up the logs of the master database server?
I'm looking for transaction logs and operation logs.


texts: Is there a reason to update statistics manually?

texts: In SQL Server,  statistics are updated automatically when Auto Update Statistics in True (which is the default). Is there a reason to update statistics manually and in what circumstances?


texts:
I have an Access back-end database with tables, queries, reports...and I have an Access front-end database with a data-entry form that will be used by 3 users. Both back- and front-ends are in same folder on a file server accessible to all 3 users.

What is the proper method of sharing my Access front-end database? Is it simply to have each user make a copy of it and put it on their local drive? This is my first time sharing a database, so any input would be greatly appreciated.


texts: Can I connect to the database as the user "dbo"?

texts:
If not, why?

During security audit we are being asked if the dbo login is interactive. My understanding is that the account is automatically created and managed by SQL server itself and is not directly interactive.

I have tried to the demonstrate that a sysadmin users would appear in SESSION_USER as dbo when authenticating but they're not fully convinced. Their question is now if it is possible to directly authenticate to dbo by logging in with its password.

Is this possible? I don't even know where I would find dbo's password if it has one.


texts: Rerunning query in the same connection takes significantly longer

texts:
I am having a weird issue when a running a query that joins some semi-large tables (one table is about 2 million rows and the other about 21 million) together.  What happens is the query does not run within the same time frame consistently.  If I run the query on a new connection in SSMS it takes about 45 seconds to complete.  After it completes if I hit the execute button again it takes almost 4 minutes to complete.  If I then open a new query (connection) and execute the query it takes about 45 seconds again.  These queries are being run from the server and I can consistently reproduce this.

Any help on what is going on and how to fix is helpful.


texts: I have setup a few buckets and now I’d like to create a view but I’m not sure where to start.  I have logged in to the web console but the views I have created to not return any results.  Also, is it possible to create a view that spans buckets?


texts: What is the difference between ssisdb.catalog.executions.status values of 7 and 9?

texts:
7 means package succeeded.

9 means package completed.

What is the difference and under what circumstances does 9 occur?


texts:
I want to Grant a user only INSERT permission.

When I try to insert data, it throws the error "Access Denied".

What permission do I give a user to use/select database in mysql and how?

I have given only GRANT INSERT ON database.* To user@'localhost';


texts: Schema/Structure to handle secondary computed data

texts:
I'm trying to design a LAMP-based website for my pinball league, which plays competitive pinball every week in match play.

One of the features of our league is that we convert raw scores to matchpoints.  For example, suppose four players play a match and their scores are:

14,325,320
35,332,110
34,003,990
11,345,920

then their matchpoints would be 2, 4, 3, 1 respectively.

Sometimes we might want a more complex matchpoint algorithm, which might be based on properties of the raw scores (e.g., if the highest score is better than all other scores combined, award an extra matchpoint).

Obviously whatever database schema I use, I would like it to store not just the raw score, but also easily display the matchpoints associated with that score.

What I'm confused about is the best place to compute those matchpoints.  Should I:

Set a SQL trigger each time a raw score is entered into the database, then have some complex logic to determine if matchpoints should be computed and then modify rows as necessary?  I'm not sure if SQL can necessarily handle our matchpoint algorithms easily.
Don't store matchpoints in the database at all, but compute them on the fly each time they are requested?  This seems like a waste of computation power.
Having a scheduled event in SQL, or a cron job on the server, that goes through the database and computes matchpoints, then adding that data to the database as necessary?  That seems to have the drawback of needing to wait around for that computation to happen.

Right now the best approach I can think of is a hybrid of 2 and 3, where during the competition matchpoints aren't stored, but computed on the fly, and then having a human-thrown trigger at the end of each week's competition that computes all the matchpoints and puts them into the database.  Possible bugs might be that I have to be careful to reuse the same code or else the database scores might differ from the temporary scores.  Also in case of human error the trigger might not be thrown.

It seems like I can't be the only person who needs a bit of automated business logic to parse database entries as they come in and then display them.  In some sense the ideal result would be something like a SQL View, but where I can add my own logic to it.  I have no idea if something like that exists.

Are there any better ideas?


texts: sp_whoisactive keeps returning create procedure statements

texts:
When I run sp_whoisactive on my server it often shows create statements being run for stored procedures but this doesn't make any sense since the procedure already exists.

See the following example:

dd hh:mm:ss.mss session_id  sql_text
00 00:00:00.120 103 CREATE PROCEDURE [dbo].[TempGetStateItemExclusive3]
00 00:00:00.140 274 CREATE PROCEDURE [dbo].[TempGetStateItemExclusive3]
00 00:00:00.133 324 CREATE PROCEDURE [dbo].[TempGetStateItemExclusive3]

This is from one run of sp_whoisactive it looks like there are three sessions creating the procedure. Is this something I am misinterpreting or if not how can I find out where this is coming from? These create statements are blocking actual inserts.

I am executing

EXEC sp_WhoIsActive
     @find_block_leaders = 1,
     @sort_order = '[blocked_session_count] DESC',
     @get_full_inner_text = 1

The batch being executed is (which is a stored procedure from asp.net SQL Server based session state but I am seeing the exact same thing for other stored procedures as well)

CREATE PROCEDURE [dbo].[TempGetStateItemExclusive3]
        @id         tSessionId,
        @itemShort  tSessionItemShort OUTPUT,
        @locked     bit OUTPUT,
        @lockAge    int OUTPUT,
        @lockCookie int OUTPUT,
        @actionFlags int OUTPUT
AS
    DECLARE @textptr AS tTextPtr
    DECLARE @length AS int
    DECLARE @now AS datetime
    DECLARE @nowLocal AS datetime

    SET @now = GETUTCDATE()
    SET @nowLocal = GETDATE()

    UPDATE dbo.ASPStateTempSessions
    SET Expires = DATEADD(n, Timeout, @now),
        LockDate = CASE Locked
                      WHEN 0 THEN @now
                      ELSE LockDate
                   END,
        LockDateLocal = CASE Locked
                           WHEN 0 THEN @nowLocal
                           ELSE LockDateLocal
                        END,
            @lockAge = CASE Locked
                WHEN 0 THEN 0
                ELSE DATEDIFF(second, LockDate, @now)
                END,
            @lockCookie = LockCookie = CASE Locked
                WHEN 0 THEN LockCookie + 1
                ELSE LockCookie
                END,
            @itemShort = CASE Locked
                WHEN 0 THEN SessionItemShort
                ELSE NULL
                END,
            @textptr = CASE Locked
                WHEN 0 THEN TEXTPTR(SessionItemLong)
                ELSE NULL
                END,
            @length = CASE Locked
                WHEN 0 THEN DATALENGTH(SessionItemLong)
                ELSE NULL
                END,
            @locked = Locked,
            Locked = 1,

            /* If the Uninitialized flag (0x1) if it is set,
               remove it and return InitializeItem (0x1) in actionFlags */
            Flags = CASE
                WHEN (Flags & 1) <> 0 THEN (Flags & ~1)
                ELSE Flags
                END,
            @actionFlags = CASE
                WHEN (Flags & 1) <> 0 THEN 1
                ELSE 0
                END
        WHERE SessionId = @id
        IF @length IS NOT NULL BEGIN
            READTEXT dbo.ASPStateTempSessions.SessionItemLong @textptr 0 @length
        END

        RETURN 0


texts:
When I run $ mysqld --verbose --help | less I got information like

Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf

As per my understanding, At startup, mysql first look for my.cnf within /etc/mysql/my.cnf then within /etc/my.cnf and then finally ~/.my.cnf.

Am I correct?
Which directory does this mean - ~/.my.cnf?
If a variable x has value 100 in /etc/mysql/my.cnf and again the same variable x has value 200 in etc/my.cnf, What value will really apply?
 -


texts: Open source tools to manage Data Quality

texts: Are there open source (or commercial) tools that can report data quality issues in a data warehouse using the Kimball star schema model?  It doesn't have to be the silver bullet.  I'm just looking for something to start with that can give basic details of data quality.


texts: How do you throttle (put a limit) your database mail?

texts:
I discovered today that we have a really ridiculously powerful SMTP server, when I forgot my where clause and sent out 20,000 emails in 4 seconds.

How do I put a limit on my database mail to only send out a few dozen emails per minute?


texts: Entity Relationship Model - double one to many

texts:
ERD

A customer submits one or many  leads.
A lead belongs to one customer.
A lead is a request or an inquiry.
A lead belongs to one manufacturer.
A manufacturer has one or many leads.

Tables

Only focusing on manufacturer and lead I set up the tables like below.

manufacturer            lead
--------------          ---------------
MId                     LId
name                    MId
description             date
...                     ...

Adding customer table I add CustomerId to lead table.

manufacturer            lead                customer
--------------          ---------------     ---------------
MId                     LId                 CId
name                    MId                 phone
description             CId                 ...
...                     date
                        ...

Issue:

With all three entity types, is there a benefit to have an additional linking table instead adding ManufacturerId(MId) and CustomerId(CId)  as column to Lead table? Does this offend any best practice? Or should I even include two separated linking tables? Thank you for Explanation and 'further reading links'.


texts: Optimising tracking page views Aggregation

texts:
select page.*, views.view_count
from page
         join (select page_id, count(page_id) view_count
               from (select page_id, user_id, floor(EXTRACT(epoch from time) / EXTRACT(epoch from interval '2 minutes')) time_bucket
                     from page_view
                     where time > now() - interval '7 days'
                     group by page_id, user_id, time_bucket) user_views
               group by page_id) views on page.id = views.page_id
order by views.view_count desc
offset :offset limit :limit;

I have this query that feels very clunky. Are there any notable optimisations I can utilise here?
I'm limiting the views registered per page to every 2 mins per user - from the last 7 days.
I'm also thinking it'd be better to track views based on IP rather than user - which will help support anonymous views.


texts: Detecting and fixing database corruption on SQL Server mirrored database

texts:
I’m new to my current company and we have a System Administrator but no DBA.  In the past I’ve taken care of DBA related issues at other companies.  I’ve run into and corrected database corruption in other databases (Foxpro and Access) and was able to at least document the data corrupted so it could be reentered.   I’ve never encountered or suspected corruption with a SQL Server database.  I suspect that I’m running into this with a SQL Server 2005 database that is being mirrored.

The situation is that simple delete commands passed to the database are not deleting data in certain tables.  I literally have to go in through SQL Server Management Studio and issue the delete command to clear out these tables.  There are anywhere from 144 thousand to 32 Million records in temp tables and this data is taking up more than 90% of the database (as determined after deleting the records and shrinking the database).  After I do this clean up, then the delete commands passed to the database on the test server work.

My first line of attack would be to run DBCC CHECKDB against copies of the database.  I ran it against a backup and there was no corruption found.  We can’t get exact copies of the database as my System administrator has had issues in the past where the database server has taken up to 6 hours to get online and he does not want to disable both the live and mirrored SQL servers.

My first question is if I should trust a database backup to include corrupted data that would be found by DBCC CHECKDB.  If the backup does not store the corruption, what is the best way to stop and then bring back both the live and mirrored database to get copies of the LDF and MDF files?

Finally if I do find corruption in testing how would I determine the contents of the page that could show the corruption?


texts:
I have this data set

ID  subject     class_score total_marks rank    subject_type
1   MATLAB      33.80       73.30       11  Core
1   SCIENCE     39.50       83.00       4   Core
1   ENGLISH     37.60       77.60       8   Elective
1   WQP         43.90       77.40       9   Core
1   BDT         42.00       71.50       12  Elective
1   ART         47.10       84.60       2   Elective
1   COMPUTING   26.00       65.50       13  Elective
2   MATLAB      0.00        0.00        14  Core
2   SCIENCE     38.60       73.60       10  Core
2   ENGLISH     43.80       83.30       3   Elective
2   WQP         45.00       89.00       1   Core
2   BDT         41.00       79.50       6   Elective
2   ART         38.00       78.00       7   Elective
2   COMP        40.80       80.80       5   Elective

I wish to calculate the overall score of each student by add the total_marks of all (Core subject + the top 3 score of the student's elective subjects) and rank them from first to last.Please can anyone assist ?

My current use is -

SELECT student_id,sum(t.total_marks)score
FROM ( select student_id,total_marks
       from tab_exam_tracker
       where (subject_type='Core')
     union all
       select t.student_id,t.total_marks
       from ( select student_id,total_marks
              from tab_exam_tracker
              where subject_type = 'Elective'
              order by total_marks desc limit 3
            ) t
     ) t
GROUP BY student_id


texts: Import Postgres 8.3 database in Postgres 10.5

texts:
I have a database in Postgres 8.3 (and PostGIS 4.2), which I need to import in Postgres 10.5.

I am a DB newbie and would appreciate any pointers to get started. Would you recommend a particular book or series of blogs, or is the process fairly simple?


texts: What tools are there for data masking? (MySQL, Linux)

texts:
I'm looking for (ideally free, open-source) data masking tools.  Do any such exist?

Note: this related question deals with tools for generating test data, but in this question I'm more interested in starting with real data, and masking it for use in test without losing any special relationships that make it interesting for test purposes.  Generated data is fine for some test purposes, but real-world data will bring up issues you never thought of.
Tool to generate large datasets of test data


texts:
How to read the value from URL and compare with index that i have created in MYSQL table. I have a form where provide list of selection using drop down list and i pass the selected value to the next page URL. Now i want to compare this value with my index. how to do this? please help me. i have something like this at the first form.

    <select id="ccrseid" name="selectvalue" onchange="showMe(this.value);">
  <option value="">Select</option>
  <option value="1">Computer Science</option>
  <option value="2">Engineering</option>
  <option value="3">Business</option>
  <option value="4">Education and Training</option>
  </select>
  <input type="submit" value="Submit">

then im trying to get the selected value and check with index that i have created in another form

$id=(int)$_GET['selectvalue'];;

$ret1="SELECT * FROM $db_tb_name WHERE MATCH(Id_index=$id) AGAINST('%$str%' IN BOOLEAN MODE)";
$result1 = MySQL_query($ret1);

but when i run i get this error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '==2) AGAINST('%%' IN BOOLEAN MODE)' at line 1

how to solve this?


texts:
I have a table that looks like this:

CREATE TABLE `message` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `user_id` int(10) unsigned NOT NULL,

  `grp_id` int(10) unsigned NOT NULL,

  `body` varchar(100) COLLATE utf8_unicode_ci NOT NULL,

  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`created_at`,`grp_id`,`user_id`,`id`),

  KEY `message_user_id_foreign` (`user_id`),

  KEY `message_grp_id_foreign` (`grp_id`),

  KEY `mssg_id_is` (`id`),

  CONSTRAINT `message_grp_id_foreign` FOREIGN KEY (`grp_id`) REFERENCES `groups` (`id`) ON UPDATE CASCADE,

  CONSTRAINT `message_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=50001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I want to fetch the latest 50 MESSAGES for every group that the user is in. This list will be supplied in the query . (something like 8,4,2,1,6,...)

What will the mysql query look like?
The number of groups a user is in is variable.


texts: MSSQL - Setting up simple Replication fails

texts:
Heads up: I'm a rookie when it comes to SQL Administration.

Im seting up a simple MSSQL Replication running server '19 (developer edition).

The server and agent services run as local admin on the machine.
I created a small DB with just a table and a couple of rows which I would like to replicate to the same server (just a different DB).

The destribution is setup to impersonate a the service process account and the snapshot share is just a local folder.

I creted a new transactional Publication and set all the accounts to run as the Agent Account (or just impersonate).

Now I added a subscription (push), picking an empty DB and setting everything up to run as Agent Account or imersonate to the process account.

Checking the status of the syncronization, will end up in "Agent Starting". After a while I'll get an error;

Agent 'XXX\SRV1-Source-3' is retrying after an error. 4 retries attempted. See agent job history in the Jobs folder for more details.

But the Verbose of this Job is just that:

2019-12-12 13:42:56.651 Microsoft SQL Server Log Reader Agent 15.0.2000.5
2019-12-12 13:42:56.652 Copyright (c) 2016 Microsoft Corporation
2019-12-12 13:42:56.652 Microsoft SQL Server Replication Agent: logread
2019-12-12 13:42:56.653
2019-12-12 13:42:56.653 The timestamps prepended to the output lines are expressed in terms of UTC time.
2019-12-12 13:42:56.654 User-specified agent parameter values:
            -Publisher XXX\SRV1
            -PublisherDB Source
            -Distributor XXX\SRV1
            -DistributorSecurityMode 1
            -Continuous
            -OutputVerboseLevel 2
            -Output C:\ReplOutput.txt
            -XJOBID 0xF855F65C5625C2439F8D41552D5F0C0C
            -XJOBNAME XXX\SRV1-Source-3
            -XSTEPID 2
            -XSUBSYSTEM LogReader
            -XSERVER KNWORK\SRV1
            -XCMDLINE 0
            -XCancelEventHandle 0000000000001A58
            -XParentProcessHandle 0000000000001A80
2019-12-12 13:42:56.661 Status: 0, code: 20015, text: ''.
2019-12-12 13:42:56.662 Disconnecting from OLE DB  ''

Could anyone provide some more insight?

EDIT: The Snapshot job fails with a more specific error message, which I can't explain:

2019-12-12 14:31:13.079 Microsoft SQL Server Distribution Agent 15.0.2000.5
2019-12-12 14:31:13.141 Copyright (c) 2016 Microsoft Corporation
2019-12-12 14:31:13.174 Microsoft SQL Server Replication Agent: distrib
2019-12-12 14:31:13.206
2019-12-12 14:31:13.226 The timestamps prepended to the output lines are expressed in terms of UTC time.
2019-12-12 14:31:13.237 User-specified agent parameter values:
            -Subscriber XXX\SRV1
            -SubscriberDB Dest1
            -Publisher XXX\SRV1
            -Distributor XXX\SRV1
            -DistributorSecurityMode 1
            -Publication T4
            -PublisherDB Source
            -Continuous
            -OutputVerboseLevel 2
            -Output C:\ReplOutput2.txt
            -XJOBID 0x0D26D00FF5848B42BA332C3579C7D1C0
            -XJOBNAME XXX\SRV1-Source-T4-XXX\SRV1-11
            -XSTEPID 2
            -XSUBSYSTEM Distribution
            -XSERVER XXX\SRV1
            -XCMDLINE 0
            -XCancelEventHandle 0000000000001A34
            -XParentProcessHandle 0000000000001A58
2019-12-12 14:31:13.240 Startup Delay: 3407 (msecs)
2019-12-12 14:31:16.649 Connecting to Distributor 'XXX\SRV1'
2019-12-12 14:31:16.650 Connecting to OLE DB Distributor at datasource: 'XXX\SRV1', location: '', catalog: '', providerstring: '' using provider 'MSOLEDBSQL'
2019-12-12 14:31:16.654 Agent message code 20084. The process could not connect to Distributor 'XXX\SRV1'.


texts: Stored procedure suddenly very slow, updating statistics not helping

texts:
Like the title says, I have a stored procedure that suddenly started running very slowly. Previously it took about 5 seconds to execute, now it is taking seven minutes or more. This seems to have started when I altered the procedure, but the change I made should not have impacted the performance, as all I did was change the alias on two columns being returned. I'm not sure if altering the procedure actually caused this, however, because a similar procedure that also used to be very fast is having the same issues.

I tried updating statistics on the relevant tables and it did not help. I had this same problem last week and was able to fix it by doing that, but now it's a no go. I also tried using with recompile to no avail and deleted the procedure then re-added it.

This is the line that is taking much longer than before:

select PK_ShipDataSubsystemConfigID
,timestamp
,cast((
        select top 1 Longitude
        from [RemoteServer].[dbo].[Ships].LocationAttributes la
        inner join [RemoteServer].[dbo].[Ships].Locations l on l.ShipLocationId = la.ShipLocationId
        where l.SampleDateTime <= timestamp
            and l.SampleDateTime <= @ToDT
            and ShipId = @ShipId
        order by SampleDateTime desc
        ) as varchar(20)) as Longitude
,'Vessel Position Lng'
from FuelCalc
inner join ShipDataSubsystemConfig s on s.PK_ShipDataSubsystemConfigID =       FuelCalc.FK_ShipDataSubsystemConfigId
where timestamp > @FromDT
and timestamp < @ToDT
and FuelCalc.FK_ShipId = @ShipId
order by timestamp desc

Running the inner select by itself now takes at least 2 seconds, so I can understand why the whole procedure is taking so long. I am wondering if it has something to do with it being a call to a linked server. I tried creating a view  joining the Locations and LocationAttributes tables to see if it made a difference, but it did not.

Any ideas? I have asked around with my coworkers and they are just saying I need to write a more efficient query (which may be true) but I really don't think that's causing my particular problem seeing as it was working fine until suddenly it wasn't. I looked at the execution plan but all it says is that the remote query is taking 100% of the time, but doesn't show the plan at more detail than that.


texts: Sql Server 2012 Restoring Master and Migrating a server

texts:
I am migrating an entire SQL Server instance with 10 databases on it.

It has many jobs and users. What is best practice regarding the master database? Should I backup and restore this too, or script the users and jobs?

Also, is there another better way to migrate an entire SQL Server instance with all of its databases?


texts:
I have a couple of SSIS packages that copy the content of several tables to another db. One of the columns updated in the process is a timestamp datatype. From what I read this datatype can't be updated, it gets updated automatically with each UPDATE or INSERT. But digging on the net I've found this and this about issues on SSIS when updating this type of column and using DT_BYTES and DT_WSTR types. If I got it right, using SSIS packages those columns can be modified.

My issue is that now I want to replace those SSIS packages with stored procedures that will be called by the SQL Server Agent. I'm reusing the already existing code on the packages but of course I'm getting the errors "Cannot insert an explicit value into a timestamp column." or "Cannot update a timestamp column.".

Is there any way I can replicate the timestamp values from one database to the other? Without changing the table structure or column type as the whole process is already configured and running on several databases.


texts: How do I draw the erd to model this scenario?

texts:
Customers are billed for the cost for the activity itself and also any "extra" items which are used as part of an activity.

So I have the customers entity (identified by customer id) and an item entity(identified by item id). Each of these has a cost attribute to determine the cost of each item/activity. But how do I model the purchases of this customer? E.g. the customer may pay for an activity and buy a few items for the activity.

Can I add a new entity called billingInfo?Or should there not be a new entity at all?


texts:
I recently migrated a database from Sybase ASE to Microsoft SQL 2008r2.
One category of queries that was broken in transit is:

SELECT person_id
  FROM leaveperiods
 WHERE group_id=146
 GROUP BY person_id
HAVING start_date = max(start_date)

According to Sybase, this is a valid query. But Microsoft SQL is stricter, so I get this error:

Column 'leaveperiods.start_date' is invalid in the HAVING clause
because it is not contained in either an aggregate function or the GROUP BY clause.

While looking at this query to try and correct it, I realized that it does not make sense to me. Isn't this exactly the same as the below?

SELECT DISTINCT person_id
  FROM leaveperiods
 WHERE group_id=146

Or maybe I should write like this?

SELECT DISTINCT person_id
  FROM leaveperiods
 WHERE group_id=146
   AND start_date IS NOT NULL

There are several queries like this in one application and I need to fix them without breaking anything else. Please help.


texts: On SQL Server Backup: Msg 3271, Level 16, State 1, Server Line 1 A nonrecoverable I/O error occurred on file

texts:
I am trying to back up a pretty big database and I keep getting this error:

Msg 3271, Level 16, State 1, Server {Server Name}, Line 1 A
nonrecoverable I/O error occurred on file "{Backup File Location}:"
112(failed to retrieve text for this error. Reason: 1815). Msg 3013,
Level 16, State 1, Server {Server Name}, Line 1 BACKUP DATABASE is
terminating abnormally.

Does anyone happen to know what this is?


texts:
I need to design a CMS based website using asp.net web form (c#) and MS SQL Server as backend database for a magazine website which will have monthly issue.

I am finding it hard to design a database that can help me fetch menus from different table for example as show in image below

Example of Database

Sample Data

MagazinePages Table
PageID  PageName        LangID  PagePositionNo  PageURL         PageInheritance
1       Home            1       10              Default.aspx    0
2       About Us        1       20              Page.aspx       0
3       PageOne         1       10              Page.aspx       2
4       PageTwo         1       20              Page.aspx       2
5       Multimedia      1       30              Page.aspx       0
6       Video           1       10              Videos.aspx     5
7       PhotoGallery    1       20              Gallery.aspx    5
8       News            1       40              News.aspx       0
9       Issues          1       50              #               0
10      Publication     1       60              Page.aspx       0
11      SpanishHome     2       10              Default.aspx    0
12      SpanisAbout Us  2       20              Page.aspx       0
------------------------------------------------------------------------------
Magazine Table
MagazineID  MagazineIssueCode   LangID  MagazineTitle       MagazineLiveIssue(CurrentIssue)
1           101                 1       Mag Title           0
2           102                 1       Mag Title           1
3           101                 2       SpanisgMag Title    0
4           102                 2       Mag Title           1
------------------------------------------------------------------------------

News Table
NewsID  NewsTitle   NewsCatID   MagazineID  Language
1       News one    100         1           1
2       News two    100         1           1
3       news three  200         1           1
4       News four   300         1           1
5       News Five   100         2           1
6       News Six    300         2           1
7       News seven  200         2           2

Problem with above approach is that I can create sub menus only if all the records are in MagazinePages based on above example I can create sub menus for  “About Us” & Multimedia how can i design my database so that i can pull data from different table such as news by category (Politics, Culture...) & Issue from Magazine Table (Issue 101, Issue 102, Issue 103....)

I am planning to use ASP Menu control for this which may not be very flexible, since i will have over 100 issue of magazine how or which multi-column menu i can use with asp.net

My database may have many flow, I would appreciate help in this regard so that i can use this database for this CMS system . Please feel free to ask question if any regarding this.


texts: Optimize MySQL FULLTEXT searches to use another index

texts:
I'm running a price comparison service, with a database of products that aims to become quite large (in the millions or more).

The Product table looks like this:

CREATE TABLE Product (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    site_id INT(10) UNSIGNED NOT NULL,
    brand VARCHAR(100) NULL,
    name VARCHAR(500) NULL,
    variation VARCHAR(100) NOT NULL,
    PRIMARY KEY (id),
    INDEX Product_Site (site_id),
    FULLTEXT INDEX Product_Fulltext (brand, name, variation),
)

Now I need to query products belonging to a given site, using a search string, which translates into this query:

SELECT * FROM Product
WHERE site_id = 7
AND MATCH (brand, name, variation) AGAINST('something')
ORDER BY MATCH (brand, name, variation) AGAINST('something') DESC
LIMIT 10;

The query is not very fast, even though my table is still quite small (< 1M).

The reason is given by EXPLAIN:

+----+-------------+---------+------------+----------+-------------------------------+------------------+---------+-------+------+----------+-------------------------------+
| id | select_type |  table  | partitions |   type   |         possible_keys         |       key        | key_len |  ref  | rows | filtered |            Extra              |
+----+-------------+---------+------------+----------+-------------------------------+------------------+---------+-------+------+----------+-------------------------------+
|  1 | SIMPLE      | Product | NULL       | fulltext | Product_Site,Product_Fulltext | Product_Fulltext |       0 | const |    1 |    50.00 | Using where; Ft_hints: sorted |
+----+-------------+---------+------------+----------+-------------------------------+------------------+---------+-------+------+----------+-------------------------------+

It looks like MySQL is choosing to use the FULLTEXT index first, finding all rows that match the search string, then filter the rows that belong to the site I'm interested in. This is obviously highly inefficient.

And AFAIK:

MySQL still cannot use two indexes in the same query,
there is no way to have FULLTEXT act as a "hybrid" index that would be segmented by an integer id,
partitioned tables do not support FULLTEXT indexes;

but I do hope that I'm missing something.

Is there any way to speed up this query? Or should I move away from MySQL to implement these searches? All suggestions welcome.


texts: Convert rows to columns in SSIS. Unpivot?

texts:
If I have data like this

"","",WAREHOUSE ID,1,2,3,4,5...
"","",WAREHOUSE CODE,BOS,HOU,LAX,NYC,CHI...
"","",WAREHOUSE NAME,BOSTON,HOUSTON,LOS ANGELES,NEW YORK CITY,CHICAGO...

But want to transform it like this

 WAREHOUSE ID, WAREHOUSE CODE, WAREHOUSE NAME
 1,BOS,BOSTON
 2,HOU,HOUSTON
 3,LAX,LOS ANGELES
 ...

If, as above, [Column 2] is the column that contains what should be the header row, and Columns 3 through 40 contain what should be row values, which columns get selected and set to pass through for the UNPIVOT to perform the desired transposition?

Or is UNPIVOT not even what I'm looking for?


texts: How to send+update on duplicate a big time series to SQL server

texts:
I use a SQL Server 2008
I would like to send (and update on duplicate datetime index) a big time series (more than 1000 datapoints and eventually much more than that).
What is the best way to do so?
I have been advised to use

MERGE

and

BULK INSERT

but,

can we use the 2 at the same time?
I don't want to save my data first to a file. It is already in memory, why should I have to save it first to a file? Plus I can have several threads calling this method at the same time and it is going to be a bit messy.

Any advice welcome


texts: What are all the XTP_THREAD_POOL processes doing?

texts:
I just ran sp_who on my SQL Server 2016 instance that uses MEMORY_OPTIMIZED tables, and I see several XTP_THREAD_POOL processes running:

Some other details about the output:

There are exactly 6 XTP_THREAD_POOL rows in the resultset
My system has two logical cores

Specifically, it's a VM with 2 sockets - each socket housing a 1-core processor

All of these tasks are running as background (sa)

What are these processes doing?  I'm not finding much on Google about this task name.

TIA


texts: Design db schema in correct way for parent child kind of requirement

texts:
I am creating schema of tables. But not able to understand best way to do it.

Following are the tables:

  Car Model - It contains all car models with types.  For eg. Mercedes(brand) - GLA 250(model) - Sedan(type)
  Car Panel - It will contain all car panels. For eg. Fender(panel name)
  Car Panel Price By Type - It will contain car panel price by type. For eg. Sedan(type) - Fender(car panel id) - 2500(price)
  Car Panel Price By Model - It will contain car panel price by Model. For eg. Mecedes GLA 250(car model id) - Fender(car panel id) -
  2700(price)
  Booking Package - It will contain all the order related fields. For eg. Order Id - Mercedes GLA 250 (car model id)
  Booking Package Panels  - It will contain all the panels in order along with their price. For eg. Order id - Car Part Price By Model - Car Part Price By Type

Here, It will contain car panel price by model if it exists other wise car panel price by type. Because it can be possible that for some models, we don't add the price and use the type level pricing.

Another way to store Order panels.

  For eg. Order id - Fender (panel id) - 2500(price from model or type)

Please suggest which is better way to design this order panel table.


texts:
In a database we have the full file path for a database field type is nvarchar(255) and structure/data would look like this:

M:\Blue\Red\Green\Folder\Pink\Purple\Database1.accdb
M:\Blue\Red\Orange\Folder1\Folder2\Folder3\Size\Small\Large\DbTest1232323Test.accdb

My question is, how can I extract only the database name – pull only this out:

Database1.accdb
DbTest1232323Test.accdb

Since the name of the database can vary, I can't use just a RIGHT() function.

Is there a way I can somehow use a wildcard in conjunction with RIGHT() to say, "Pull everything to the right of the last \"?


texts: How do I create a rule for a Primary Key

texts:
I am using SQL Server 2005 and I need to create a primary key with an alpha character and 3 numbers together:

    p001
    b201

I know I need to create a rule and bind that rule to the primary key column.  I am little confused in creating the rule using a LIKE query:

CREATE RULE pK-rule AS @pk LIKE'[a-z][0-9][0-9][0-9]'

Can anybody suggest what I need to change?


texts: Derived classes with same base class and class specific ids

texts:
I have three classes in my project. One base class and two derived classes.

public class Animal
{
    public long Id { get; set; }
    public DateTime Created { get; set; }
    public string Name { get; set; }
}

public class Cattle : Animal
{
    public int HornsCount { get; set; }
}

public class Fish : Animal
{
    public int FlippersCount { get; set; }
}

I want to store instances of these classes in database and since they have some same values (from base class) I thought that the best idea is to create Three tables: Animal (with PK Id), Cattle (with PK&FK to Animal.Id), Fish (with PK&FK to Animal.Id). But the problem occurs when I found out that each derived class has its own Id counter. So you can have Cattle.Id = 1 and at the same time Fish.Id = 1. Now I'm not sure how to proceed. Is one of these approaches correct?

Use just two tables (Cattle and Fish) with duplicate columns.
Use design specified above and add special column to Animal table which will be used for all Animals?
a) This new Id is string code that is defined eg derived class in first letter and then is original Id of derived class.
b) This new Id is auto increment database value. And move original Id column from Animal table to tables for derived classes.

1 downside - many duplicate columns -> have to change all tables for derived classes if I change Animal class.
2a downside - string as PK which actually doesn't mean anything in application.
2b downside - I have to insert into Animal table, then get auto Id of that record and then insert row to derived class table. Also there are identical original Id column in all tables for derived classes.

Or is there some better approach?

EDIT:
Question was put on hold as unclear so maybe some example data will help me explain my problem better. Here are five instances which I should save into database (I am not able to affect how these instances are created):

var c1 = new Cattle {Id = 1, Created = 2015-11-05 10:10, Name = "Cow1", HornsCount = 1}
var c2 = new Cattle {Id = 2, Created = 2015-11-05 12:12, Name = "Bull1", HornsCount = 2}
var f1 = new Fish {Id = 1, Created = 2015-11-04 05:05, Name = "Salmon1", FlippersCount = 5}
var f2 = new Fish {Id = 2, Created = 2015-11-05 06:06, Name = "Carp1", FlippersCount = 3}
var f3 = new Fish {Id = 3, Created = 2015-11-05 05:05, Name = "Trout1", FlippersCount = 2}

As you can see one Id property is unique only for all Cattle instances and different one is unique only for Fish instances. I should be able to return the right result when the app wants for example: "Fish with Id = 2."


texts: Cannot add “rowversion” data type in sql server 2008 r2 management studio, or even to modify existing column data type

texts:
I want to create a column with Rowversion data type, but inside my SQL Server 2008 R2 Management Studio, I cannot find such a data type:

Also when I tried altering existing column named timestamp which is of type timestamp using this statement:

ALTER TABLE [SkillManagement].[dbo].[Customer]
ALTER COLUMN timestamp rowversion

I get this error:

  Msg 4927, Level 16, State 1, Line 1
  Cannot alter column 'timestamp' to be data type timestamp

Can anyone advise?


texts:
Is there some function to normalize name string:
 select normalize('R.C. Bray')

 'R. C. Bray'

Or the opposite direction?
The simple replace('. ', '.') or replace('.', '. ') is wrong and may be effect another data.


texts: Optimizing queries on a range of timestamps (two columns)

texts:
I use PostgreSQL 9.1 on Ubuntu 12.04.

I need to select records inside a range of time: my table time_limits has two timestamp fields and one integer property. There are additional columns in my actual table that are not involved with this query.

create table (
   start_date_time timestamp,
   end_date_time timestamp,
   id_phi integer,
   primary key(start_date_time, end_date_time,id_phi);

This table contains roughly 2M records.

Queries like the following took enormous amounts of time:

select * from time_limits as t
where t.id_phi=0
and t.start_date_time <= timestamp'2010-08-08 00:00:00'
and t.end_date_time   >= timestamp'2010-08-08 00:05:00';

So I tried adding another index - the inverse of the PK:

create index idx_inversed on time_limits(id_phi, start_date_time, end_date_time);

I got the impression that performance improved: The time for accessing records in the middle of the table seems to be more reasonable: somewhere between 40 and 90 seconds.

But it's still several tens of seconds for values in the middle of the time range. And twice more when targeting the end of the table (chronologically speaking).

I tried explain analyze for the first time to get this query plan:

 Bitmap Heap Scan on time_limits  (cost=4730.38..22465.32 rows=62682 width=36) (actual time=44.446..44.446 rows=0 loops=1)
   Recheck Cond: ((id_phi = 0) AND (start_date_time <= '2011-08-08 00:00:00'::timestamp without time zone) AND (end_date_time >= '2011-08-08 00:05:00'::timestamp without time zone))
   ->  Bitmap Index Scan on idx_time_limits_phi_start_end  (cost=0.00..4714.71 rows=62682 width=0) (actual time=44.437..44.437 rows=0 loops=1)
         Index Cond: ((id_phi = 0) AND (start_date_time <= '2011-08-08 00:00:00'::timestamp without time zone) AND (end_date_time >= '2011-08-08 00:05:00'::timestamp without time zone))
 Total runtime: 44.507 ms

See the results on depesz.com.

What could I do to optimize the search? You can see all the time is spent scanning the two timestamps columns once id_phi is set to 0. And I don't understand the big scan (60K rows!) on the timestamps. Aren't they indexed by the primary key and idx_inversed I added?

Should I change from timestamp types to something else?

I have read a little about GIST and GIN indexes. I gather they can be more efficient on certain conditions for custom types. Is it a viable option for my use case?


texts: MS SQL Server Performance Issues After Enabling All CPU's

texts:
My system specifications are;

Windows Server 2008 R2 Enterprise SP1
128 GB RAM
Intel Xeon E5-2620 (3 processors)
MS SQL Server 2012 Standard (11.0.2100.60)

There is 3 NumaNodes and 12 CPU's in them. 6 of them was checked.
Yesterday, I have checked all of them (12 CPU's) and after that performance issues started.

First, I've restarted SQL Server. Problems were still going on. After that I have restarted OS, still the same issues was going on.

At night, user called me for performance problems and i unchecked them to 6. I have restarted OS and still performance problems are going on.

When I look resource usage on Task Manager, only 1-2 of them working on %100 performance for 1 second. 7 GB RAM used.

Do you have any suggestions?


texts: Strategy for Repair Database on SQL Server 2008 R2 Standard

texts:
(one guy pushed green button instead of blue, three racks left as was his original target, probably green is nicer then blue, on this SAN are saved database files)

after this accident I'm run consistency check against databases
but one of databases returns consistency error

  Failed:(-1073548784) Executing the query "DBCC CHECKDB(N'XxxXxxx')  WITH NO_INFOMSGS
      " failed with the following error: "Extent (1:511776) in database ID 9 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
      Extent (1:511784) in database ID 9 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

  .
     .
     .

  Extent (1:994048) in database ID 9 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
      The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:3318703) in object ID 0,
      index ID -1, partition ID 0, alloc unit ID 72057594374717440 (type Unknown), but it was not detected in the scan.
      CHECKDB found 32446 allocation errors and 0 consistency errors not associated with any single object.
      CHECKDB found 32446 allocation errors and 0 consistency errors in database 'XxxXxx'.

  repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (XxxXxx).".
     Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly,
      or connection not established correctly.

UAT looks like as with success, no data missing
exception is (reason why I asked this question) from allocation for a new space on HDD

my question is

may I run standard repair databases (will be my 1st. attempt)
search for details, and run this repair against concrete table(s)
built database from sets of backups and then to import a new data (is possible, in this database there are stored data snapshots)

EDIT

for future readers, there were some issues in my case, (DBCC CHECKTABLE passed with success without any exceptions)

SQL Server (both 2008_R2/2012_R2) repair only part of errors by using DBCC CHECKDB(DbName, REPAIR_ALLOW_DATA_LOSS) ....
I have to runs this process four times (number of allocated GAMs decreased), last run  fixed this problem,
interesting is fact that both SQL Servers 2008_R2 and 2012_R2 had the same processes with the same exceptions, number of exceptions, numbers of repairs required, very similair performances (one enviroment with two diff instances)


texts: Why am I able to create MV logs using PK — even though there is no PK?

texts:
I have a table that does not have a primary key:

I'm able to create MV logs on the table — without specifying whether to use the primary key or the rowid:
create materialized view log on maximo.workorder;

It looks like Oracle has chosen to create the MV logs using the primary key option (even though the table doesn't have a primary key):

I've tested the MV logs and they are working correctly. Any edits that I make to the WORKORDER table  are showing up in the MV logs.

Why is it possible for Oracle to create the MV logs using the primary key option — even though the table doesn't have a primary key?

Edit:
Related question here: Create fast-refresh MV over dblink on table without PK?


texts: Is it advantageous to scale out using multiple SQL Server instances on the same server?

texts:
For a use-case of 9 databases spread across 3 time zones, is it advantagous to spread the databases onto multiple SQL Server instances on the same server?

What will I gain and what will I lose?  Tagged sql-server-2012 because it's the most pertinent, although generally the question is across SQL Server versions.


texts: MaxScale not correctly recognizing new topology when we failover to a new master

texts:
So I currently have an architecture where we have one master and one slave and read/write splitting in Maxscale. When we fail the slave, Maxscale correctly recognizes it and displays the information accordingly (and does not bring our website down), as well as allow both read and writes on the master. However, when we fail the master, mysqlfailover promotes the slave to the master, but Maxscale does not recognize that the slave is now the master. It does not seem to be granting it write permissions (which causes our website to go down).

When we fail either of the two databases, MySQL recognizes that there has been a change in our topology, but Maxscale does not.

What Maxscale configuration would allow us to correctly have Maxscale recognize this new topology (Slave is now the new master)? Is there an option that we have to select in order to have Maxscale correctly give the proper read/write permissions to the new master?

Since our topology is quite simple, I feel that Maxscale should be smart enough to realize that the new slave is now the master, and adjust accordingly


texts: SQL Server Merge vs MySQL Insert On Duplicate Key performance comparison

texts:
I've been searching a lot for this kind of comparison or some sort of performance balance.

What I do know so far is this:

MERGE is T-SQL command that is already implemented on SQL Server. As far as I know, it does perform very well, since it uses "kind of like" INNER JOIN hash mapping for inserting and updating, but I've been having some issues when deleting on the same MERGE statement as well when using CLUSTERED INDEX. Besides that, it's very fast and make let me do some comparison clauses before updating or inserting something, so it's very flexible for me.
In SQL Server some specific scenarios would rather use UPDATE and INSERT as separate statements and I wouldn't argue that. In my short experience, I would use MERGE by default as a standard for my code.
Now as a DBA, I'm facing a new challenge, I have to manage MySQL servers as well, so I ended up looking for similar MERGE behavior in MySQL to improve performance of the queries. So far, I found nothing alike but INSERT ... ON DUPLICATE KEY ... UPDATE. Still, I have some performance questions, since I'm not sure how it behaves with the server, how does it works and if optional pair of statements would work better or faster than that.
Looking around in MySQL, I found UPDATE + INSERT IGNORE, UPDATE + INSERT, INSERT ... REPLACE, and so on..
MySQL documentation is a bit confusing when trying to determinate if I could use some other clauses ex. in MERGE statement I could use AND (TARGET.COLUMN_X > 'VALUE'):

MERGE _TABLE A_ AS TARGET
WITH _TABLE B_ AS SOURCE
ON (TARGET.KEY = SOURCE.KEY)
WHEN MATCHED AND (TARGET.COLUMN_X > 'VALUE')
  UPDATE
    TARGET.COLUMN_A = SOURCE.COLUMN_A
...

I don't find how to do this on MySQL.

*CONSIDERATIONS

I have to manage this to achieve better time result and performance friendly.

What I have as setting:

PERCONA MySQL

txt file that need to be uploaded to a table every moth with new data and some changes (this is why I'm looking for a MERGE like statement)
InnoDB MySQL Engine
Relational Database tables so I can't delete or truncate the target table because all of them are related.


texts: Matching a smaller piece of text in a local variable to a larger piece of text that I have pulled in a query

texts:
I think this is a simple question, but google searching for 30 minutes was mostly wasted time as all I can find is matching a variable to a 'randompieceoftext'.

Anyways, suppose I have a local variable called @ServerName. This server name will be something like CCPWIQAUL. I need to match this server name to various path names, which is of the form:

serverName.something.somethingelse.com

These path names are pulled from a database, and will be in the column vManagedEntity.Path

How do I do something like this?

Is @ServerName is IN vManagedEntity.Path?


texts:
I thought I read that NLS_CHARACTERSET has the encoding of the database and NLS_LANG the encoding of the client. Is that correct?

Does that mean that the two encodings can be different?

In some other documentations, I read that the NLS_LANG is defined by the NLS_CHARACTERSET. Which assertion is true?


texts: Checking SQL AlwaysOn backup target with fn_hadr_backup_is_preferred_replica, different answers with different permissions

texts:
I've implemented AlwaysON backup with Ola's script, those are running fine on designated server configured for backup. I would like to automatically check backups using sql-scripts fired from a monitoring system using a dedicated account just for this. This account has no permissions except public und view server state. If this account selects the following:

select sys.fn_hadr_backup_is_preferred_replica ('TestDB) AS IsPreferredBackupReplicaNow

It gets 0 as an incorrect answer. Firing the same query using sysadmin permissions give 1 as correct answer.

Anyone knows why this is like that and what permissions are needed to get a correct result? MSDN has no word about permissions.

Thanks in advance

Build: Microsoft SQL Server 2014 - 12.0.4100.1 (X64) Apr 20 2015 17:29:27 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)


texts:
I develop an application that saves records in MS Access DB.
Each user has it own DB copy on his local machine, and there is a central DB that reflects all the copies from everyone. The central DB is located in a URL that everybody can access.

I therefore distribute the central DB by creating local copies that are links to the central tables.
  This means that the local DBs are synchronized with the central since they are links to it.
However, this solution disable the option to work offline- if a user is not connected to the URL, he cannot save anything on the local DB which is a link to the central .

I therefore have another option: to distribute the central DB by copying deep copy, and locate a copy (not link) on each machine. This enables to work offline since each user has a full copy of DB on hos machine.
However, no synchronization among the copies and the central DB.

Is there any solution that enables both offline work and full synchronization?
Any Ideas?


texts: Will my query pick up where it stopped due to connection timeout?

texts:
I am doing a rather large query that hits MySQL connection timeout. I know how to change the timeout parameter, but I'm also query from a laptop that can't be left running for long.

The database admin told me that I could send the results to a table instead. After searching around, it seems like INSERT INTO dst_tbl SELECT * FROM src_tbl; is basically the idea (source article).

But even if my results are saved, will the query picked up from where it stopped when I reconnected? Is there a way to check that I'm not missing / duplicating records?

More generally, I'd appreciate any recommendation about how to do large query under timeout constraint.


texts: Any drawback to lots of creating and renaming tables at InnoDB?

texts:
I'm trying to optimize an important cronjob for my program, and what it basically does is generate a long list of user ids with their score points. I have a table called user_ranks and it will bulk insert all of that data into that table. Normally that went fine, but it is taking a longer and longer time, which now is giving huge problems to my website.
The bulk insert query takes about 5 seconds, which is too much. It is blocking other queries to that table. (I'm using row level locking but it locks the whole table)

Now what my idea to optimize this is, is to do the bulk inserting in another table, say user_ranks_new and then after the insert is done, rename the user_ranks table to user_ranks_old and user_ranks_new to user_ranks and afterwards dropping the old table.

This is obviously a lot faster than blocking the important table (user_ranks) for about 5 seconds.
This cronjob runs every 10 minutes, so I want it optimized a lot.

Are there any drawbacks to creating lots of tables, renaming them, and dropping them? Does the ibdata files gets a lot bigger, or any other?

p.s. I'm aware of settings like shown on this page (http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html), but they didn't really help significantly.


texts:
I have a database to normalize. The database has details of train ticket fares.

The trains have different fares depending on the distance and I have to store these fares in the database

It is not an option to calculate the fare based on distance because the fare is followed strictly by the fare chart. The only way I can think of to store the values is like this is in the following table. However using this half the table is going to have NULL values.

+--------------+-----------+-----------+-----------+-----------+
| Station Name | Distance  | Station A | Station B | Station C |
+--------------+-----------+-----------+-----------+-----------+
| Station A    |         0 | 0         | 10        |        25 |
| Station B    |         1 | NULL      | 0         |        10 |
| Station C    |         2 | NULL      | NULL      |         0 |
+--------------+-----------+-----------+-----------+-----------+

Can you suggest a better way to normalize the database.


texts: How to avoid running multiple selects with the same subquery join?

texts:
I am using MySQL 5.7.26.

I have 12 SELECT queries which run the same subquery join; since the subquery in a join is very complex (can be more complex, it is a filter query in a web app) I want to reuse "calculated" resultset through all my queries.

Here is one of my queries:

SELECT
    pc.categoryId,
    COUNT(pc.productId)
FROM
    product_categories pc
INNER JOIN
    (
    SELECT
        t1.productId AS tmpProductId
    FROM
        product_categories t1
    JOIN
        product_highlights t2
    ON
        t1.productId = t2.productId
    WHERE
        t1.categoryId IN(7, 5) AND t2.highlightId IN(9, 10, 11)
    GROUP BY
        t1.productId
    HAVING
        2 * 3 = COUNT(
            DISTINCT t1.categoryId,
            t2.highlightId
        )
) productsIds
ON
    pc.productId = productsIds.tmpProductId
GROUP BY
    pc.categoryId

I looked around for solutions, a dba.stackexchange user offered to use temporary tables, which fits my case because temporary tables are available and accessible to the client that creates it, also get destroyed automatically.

My plan is as follows:

1) Create a temporary table:

CREATE TEMPORARY TABLE IF NOT EXISTS tempProducts(
    tmpProductId SMALLINT NOT NULL,
    PRIMARY KEY(tmpProductId)
)
SELECT
    t1.productId AS tmpProductId
FROM
    product_categories t1
JOIN
    product_highlights t2
ON
    t1.productId = t2.productId
WHERE
    t1.categoryId IN(7, 5) AND t2.highlightId IN(9, 10, 11)
GROUP BY
    t1.productId
HAVING
    2 * 3 = COUNT(
        DISTINCT t1.categoryId,
        t2.highlightId
    );

2) Then JOIN it with required tables, like:

SELECT
    pc.categoryId,
    COUNT(pc.productId)
FROM
    product_categories pc
INNER JOIN
    tempProducts
ON
    pc.productId = tempProducts.tmpProductId
GROUP BY
    pc.categoryId

EXPLAIN:

+----+-------------+--------------+------------+-------+--------------------------+------------------+---------+------------------------------+------+----------+----------------------------------------------+
| id | select_type | table        | partitions | type  | possible_keys            | key              | key_len | ref                          | rows | filtered | Extra                                        |
+----+-------------+--------------+------------+-------+--------------------------+------------------+---------+------------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | tempProducts | NULL       | index | PRIMARY                  | PRIMARY          | 2       | NULL                         |    2 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | pc           | NULL       | ref   | PRIMARY,unq_catid_prodid | unq_catid_prodid | 2       | rr.tempProducts.tmpProductId |    2 |   100.00 | Using index                                  |
+----+-------------+--------------+------------+-------+--------------------------+------------------+---------+------------------------------+------+----------+----------------------------------------------+

Please, consider that the maximum amount of rows in a temporary table will be around 1500-1700 rows, not more. All of my tables are InnoDB with 90/10 read/write ratio.

Now, my questions are:

1) Is it wise using temporary tables in that situation? What else would you do if you were in my shoes?

2) Which MySQL settings I should tune for it? tmp_table_size, max_heap_table_size, innodb_buffer_pool_size?

3) Should I specify a storage engine in the CREATE TEMPORARY TABLE statement? Or it will be created in MEMORY and MySQL will move it to the disk if it becomes too large? If MySQL will move it to the disk which storage engine will be used? Perhaps MySQL default?


texts: AWS RDS: Running db dumps in to the server really slow

texts:
I am migrating data from out servers to AWS (from MySQL to Aurora) and inesrting the data is terribly slow. Is there a way to speed things up?

Right now I'm creating gzipped dump files on our server (these take anywhere from a megabyte to a few hundred megabytes) and taking the dump + zipping it takes for example 22 seconds. Transferring that dump takes 2 seconds. But then extracting the data and inserting it at AWS end takes 208 seconds. For these smaller (~2MB zipped) databases the problem isn't that big, but the times grow exponentially. If the dumping and gzipping takes 216 seconds, transmitting takes 20 seconds then extract+running dump in takes 5200 seconds..

Any advice on how to get around this bottleneck would be appreciated.


texts: Print, transaction and cursor performance in Transact-sql

texts:
I'm having some performance issues when I run a script. I was reading about the problem with print and its performance, but I cannot figure out how to reduce the impact.

The algorithm is O(n^2). The main loop goes through around 50k elements. For each of them it performs some selects to retrieve data from different tables and finally it does two sub-loops

Main loop(n)
-- some selects...
   nested loop(n^2)
   nested loop(n^2)

There are different prints inside, not only because of debugging purpose, but also for requirements.
Also, when I wrap all the script into a transaction, its turns dramatically low performance.

Three points:

-Is there a way to reduce the impact of print?

-What is better in performance?: cursor or loop with a counter

-It is normal the low performance within a transaction?

Code:

http://pastebin.com/X5SiKcgq

Server: Windows Server 2008 R2(2Gb RAM)
DBMS: SQL Server 2008 R2
Client: Windows 7 running SQL Management Studio 2012.

The requirement is to show the output with incorrect rows. It will be (in production) a small list with max 80lines. It doesn't create a print for 50k. This is why I don't know how to resolve the performance issue, when the print are relatively uncommon in the output. Now my test case shows between 500-800 lines. The script will run only once in 3 environments.


texts: move postgresql 9.4 cluster to new server

texts:
I have a redmine postgresql folder backup (no dump) from postgresql 9.4
and want to move it to a new server with debian 10 (buster) with postgres 11.

On my debian buster server I tried to get this old database running, so I installed the old postgresql-9.4 there with this tutorial: https://wiki.postgresql.org/wiki/Apt#Quickstart

When I tried to start the old cluster it said:

$ pg_createcluster 9.4 main --start
Configuring already existing cluster (configuration: /etc/postgresql/9.4/main, data: /var/lib/postgresql/9.4/main, owner: 117:120)
Error: move_conffile: required configuration file /var/lib/postgresql/9.4/main/postgresql.conf does not exist

I managed to start a new cluster2 with this instruction: https://stackoverflow.com/a/48624460/1069083

But that one is empty. My old main cluster contains all my data.

How do I start the old main cluster?


texts: Join tables where the table name changes every day

texts:
I need some help in figuring out how to join tables to my query.  The table names change every day as the date is used in the table name and older tables are dropped as they are no longer needed.

My query looks for "scans" that have data in the database and are in the process of being processed (for a lack of a better word).

My current query is:

USE RetinaCSDatabase
SELECT S.ScanID [Scan ID], S.PolicyName [Scan], DATEADD(hh, +2, S.StartTime) [Scan Start], A.AssetName [Scanner], COUNT(AIPS.AssetIPID) [Hosts], S.TransactionGroup [Transaction Group ID]
FROM Scan S (NOLOCK)
LEFT JOIN Scanner SC (NOLOCK) ON S.ScannerID = SC.ScannerID
LEFT JOIN Asset A (NOLOCK) ON SC.AssetID = A.AssetID
LEFT JOIN Asset_IP_Scan AIPS (NOLOCK) ON S.ScanID = AIPS.ScanID
WHERE S.ProcessStatus < 2
GROUP BY S.ScanID, S.PolicyName, S.StartTime, A.AssetName, S.TransactionGroup
ORDER BY S.StartTime ASC, S.PolicyName ASC, A.AssetName ASC

What I need to join is the table(s) with the data still requiring processing.  Those tables start with the name "evt_scan_" and then the date.  So, a full table name would be "evt_scan_20171222" for example.  The data for a scan performed on the 22nd of December 2017 will have data in the table "evt_scan_20171222" and possibly in the following days table, "evt_scan_20171223".  So, I need my query first to check if the table exists and then join it to do a count and also to check if the following days table exists, join it to do another count and add up the 2 counts to show the outstanding records requiring processing.

If the table names were static I would join them with this query:

LEFT JOIN evt_scan_20171222 evt1 ON S.TransactionGroup = evt1.evtTransactionGroup

It would also require a WHERE statement returning only records where evtStatus = 0.

Thanks


texts: What to do when you find slow running query?

texts:
I have two databases. One with 10.000 rows and one with 10 million rows.

I have a load test and I am running it towards a rest api endpoint. This endpoint does a query to database and average response time is 9 seconds. Same query on small DB runs on average 200ms. I am trying to understand why are the amount of rows causing this problem.

I used Azure Query Performance Insight to find the slowest query. This is all I have access at the moment.

The main thing is that database DTU is on 100% when this is running, but option is not to scale up the DB, but to tune what I have now and see what causes the issue.

I never did performance before, but I read on google that I should check execution plan. Since I am using load tests, does this execution plan means the app that does queries with DB(code) and then see the query itself?

I have a query, but I am clueless on how to continue to resolve this issue? segregate query per table (there are a lot of joins for this query - Entity Framework is used in code).

What can I actually do as only a developer, and no DBA experience from before with only Azure Insights?

Any good tutorials to learn this, or is this just DBA experience?

Table example:

SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE TABLE dbo.ContentItems
(
    Id int IDENTITY(10000,1) NOT NULL
    , SiteId int NOT NULL
    , ContentType int NOT NULL
    , Title nvarchar(500) NOT NULL
    , Name nvarchar(100) NULL
    , UrlTitle nvarchar(100) NOT NULL
    , Promo nvarchar(max) NOT NULL
    , PromoImageUrl nvarchar(500) NULL
    , Body nvarchar(max) NOT NULL
    , BodyImageUrl nvarchar(500) NULL
    , More nvarchar(max) NULL
    , AdSentence nvarchar(max) NULL
    , CreatedDate datetime NOT NULL
    , UpdateDate datetime NULL
    , PublishDate datetime NOT NULL
    , AuthorId int NULL
    , AuthorDisplayName nvarchar(50) NULL
    , ContentCategoryId int NULL
    , IsActive bit NOT NULL
    , IsLive bit NOT NULL
    , IsFeatured bit NOT NULL
    , PostCode nvarchar(9) NULL
    , VideoEmbedCode nvarchar(max) NULL
    , MainImageUrl nvarchar(500) NULL
    , UserGui nvarchar(100) NULL
    , UserName nvarchar(100) NULL
    , RowVersion timestamp NOT NULL
    , TopicTagId int NULL
    , TurnOffAdverts bit NULL
    , IsLegacy bit NOT NULL
    , ReferenceId int NULL
    , ShowDashboardPromo bit NULL
    , CanonicalUrl nvarchar(max) NULL
    , IsIncludeProductPromo bit NOT NULL
    , IsVisible bit NOT NULL
    ,  CONSTRAINT PK_ContentItems
        PRIMARY KEY CLUSTERED
        ( Id ASC )
        WITH (PAD_INDEX = OFF
            , STATISTICS_NORECOMPUTE = OFF
            , IGNORE_DUP_KEY = OFF
            , ALLOW_ROW_LOCKS = ON
            , ALLOW_PAGE_LOCKS = ON
        )
)
GO

ALTER TABLE dbo.ContentItems
ADD CONSTRAINT DF_ContentItems_CreatedDate
DEFAULT (getdate()) FOR CreatedDate
GO

ALTER TABLE dbo.ContentItems
ADD CONSTRAINT DF_ContentItems_PublishDate
DEFAULT (getdate()) FOR PublishDate
GO

ALTER TABLE dbo.ContentItems
ADD CONSTRAINT DF_ContentItems_IsActive
DEFAULT ((1)) FOR IsActive
GO

ALTER TABLE dbo.ContentItems
ADD CONSTRAINT DF_ContentItems_IsLive
DEFAULT ((0)) FOR IsLive
GO

ALTER TABLE dbo.ContentItems
ADD CONSTRAINT DF_ContentItems_IsFeatured
DEFAULT ((0)) FOR IsFeatured
GO

ALTER TABLE dbo.ContentItems
ADD DEFAULT ((0)) FOR IsLegacy
GO

ALTER TABLE dbo.ContentItems
ADD CONSTRAINT DF_ContentItems_IsIncludeProductPromo
DEFAULT ((0)) FOR IsIncludeProductPromo
GO

ALTER TABLE dbo.ContentItems
ADD CONSTRAINT DF_ContentItems_IsVisible_Default
DEFAULT ((1)) FOR IsVisible
GO

ALTER TABLE dbo.ContentItems
WITH CHECK
ADD CONSTRAINT FK_ContentItems_TopicTags
FOREIGN KEY (TopicTagId)
REFERENCES dbo.TopicTags (Id)
GO

ALTER TABLE dbo.ContentItems
CHECK CONSTRAINT FK_ContentItems_TopicTags
GO


texts:
We're currently running into some performance problems since our database is getting too big. There are data stored from the last 10 years and I don't see a reason why the data older than 2 years have to be stored in the same tables as the new data.

Now since I don't have very profound experience in administrating databases, I'm looking for the best ways to archive old data.

Info

There are about 310'000'000 records in the database in total.
The database needs 250 GB on the hard disk.
The Server version is SQL Server 2008 with compatibility level SQL Server 2005 (90), but we're planning on upgrading to SQL Server 2012 soon

I've thought about two possibilities:

New Database

Create Database similar to the one on the production server and insert all the old data in the new database.

Disadvantage: Since linked servers are not allowed in our environment, it would be difficult to join the old data if needed

History Schema

Create a new schema f.e. [hist] with the same tables as in the production database. Insert all old data in these new tables in the new schema.

Advantage: Easy joining, if old data would be needed in the future

Do you prefere one of the solutions over the other?

Why?

Are there any better possibilities?
Are there existing tools with which this task is easily possible?
Any other thoughts?

Thanks in advance

Edit

Additional Question:

Would the newly created archive table also need primary / foreign keys?

Or should they just have the columns but without keys / constraints?