texts: CHECKDB Allocation Errors Msg 2576, could not repair, object id does not exist

texts:
I inherited a database that has no value in LastKnownGood. I ran repair allow data loss, says it was successfully repaired, but no change to the errors when I run checkdb again. In case it was a SQL 2000 database previously I ran updateusage, still no help. I tried my best to go through Paul Randal's IAM corruption blogs, but I'm too nervous without guidance because the reference object_id doesn't exist. Any help is appreciated, unfortunately this corruption exists in all backups.

DBCC CHECKALLOC (mydb)

Output:

Msg 2576, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:155005) in object ID 1489440380, index ID 1, partition ID 72057594086817792, alloc unit ID 72057594109886464 (type In-row data), but it was not detected in the scan.
Msg 2576, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:146209) in object ID 1489440380, index ID 2, partition ID 72057594086883328, alloc unit ID 72057594109952000 (type In-row data), but it was not detected in the scan.
Msg 2576, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:158251) in object ID 1489440380, index ID 3, partition ID 72057594086948864, alloc unit ID 72057594110017536 (type In-row data), but it was not detected in the scan.
Msg 2576, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:169275) in object ID 1489440380, index ID 4, partition ID 72057594087014400, alloc unit ID 72057594110083072 (type In-row data), but it was not detected in the scan.


texts: Optimization causes tmpdir to fill up with 'deleted' file handle

texts:
Using Percona 5.6.34-79.1. Storage Engine is InnoDB.
One of the table to be optimized is 300Gb in size.
tmpdir is a separate partition, and datadir is separate.

datadir has 500Gb of space, so optimize should work (taking its time).
tmpdir has less space than table size.

Its a standby slave, so no query of any kind is hitting this db instance, so tmpdir partition is un-used. Before I run any query, if I do lsof | grep -i deleted, I don't see any thing related to mysql.

Once I fire the optimize command, the tmpdir starts to fill up, and doing lsof | grep -i deleted shows large number of deleted but yet held files related to mysql.

Also on the datadir, the new file by the name #sql-ib4586-1246909784.ibd forms but remains in few kilobyte in size.

Normally tmpdir should not fill up and the new file (#sql-ib4586-1246909784.ibd) should increase in size taking up datadir space. Why is that not happening?


texts: import CSV into mysql table via command prompt (MySQL 5.6.25)

texts:
I'm trying to load CSV's into a mysql database with this command:

LOAD DATA LOCAL INFILE '\Users\userName\Downloads\tableName.csv'
INTO TABLE tableName
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'

But I recieve this error:

ERROR 2 <HY000>: File 'UsersuserNameDownloadstableName.csv' not found <Errocode: 2 - No such file or directory>

I log onto mysql with the command (as described here MySQL import csv file ERROR 13 (HY000): Can't get stat of /path/file.csv (Errcode: 2)):

mysql -u root -p --local-infile

doing what was described in the link above I was able to import the CSV's on a different MySQL Server (On a seperate machine running Ubuntu).  However the same process doesn't seem to be working for a differente MySQL Server instance on a Windows machine using Command Prompt.


texts: table organization with data that will never change

texts:
for reference, almost all my DB experience is with mongodb and this is my first postgres project.
I'm storing data updates from decentralized finance crypto pools.
The data structure I'm exploring is very simple.
There are tokens:
type Token =
    {
        Address:    string     // primary key
        Decimals:   int
        Symbol:     string
    }

This data will NEVER change but is referenced everywhere; roughly 2k entries
type LiquidityToken =
    {
        Address:        string    // primary key
        Symbol:         string
        Token0Address:  string    // matches the address from a token above
        Token1Address:  string    // matches the address from a token above
        Timestamp:      DateTime  // changes at every update
        Token0Amount:   decimal   // changes at every update
        Token1Amount:   decimal   // changes at every update
    }

this is updated every 5 mins, however ONLY Token0Amount and Token1Amount change and I want to keep a history.
I've roughly 500 of these objects with a new update every 5 minutes, so 140k new entries per day.
so the first thing I wanted to do is make a join between the Token0/1Address fields and the Token record, but since the Token just adds an int and a short (up to 32chars, but usually < 8 chars) string, I was wondering is this is doing much saving (I don't know how much space the join is taking).
then I was wondering if it is worth it to split the object into two pieces:
type LiquidityToken =
    {
        Address:        string    // primary key
        Symbol:         string
        Token0Address:  string    // matches the address from a token above
        Token1Address:  string    // matches the address from a token above
    }

and
type LiquidityTokenUpdate =
    {
        Address:        string    // matches the address of the liquidity token
        Timestamp:      DateTime  // changes at every update
        Token0Amount:   decimal   // changes at every update
        Token1Amount:   decimal   // changes at every update
    }

and just replicate this object. But then now a query may pull the update, which pulls the liquidity token which has to pull 2 token objects.
Since this is for a public GraphQL interface, there can be quite a few usage patterns that will pop up. I can't predict a specific type of queries because this is something I want to make public for the community once it's ready.
I understand that caching is my friend here and since there static data is small it all fits in RAM, but overall would it make sense to try to save space (each record is not very large) and add the cost of the joins, or lookup in the local cache, or is it better to just save everything at each update since disk is cheap after all?
Please, put in context that this is not something I have experience with; I've read about joins, normalizing vs denormalizing tables, etc but I don't have practical experience with that. I dealt with very large data sets but in a non SQL context and this is my first time here, so I hope the question provides the info needed :D


texts:
I have a base user profile table that stores data for users. Each user is able to upload multiple pictures so this is represented by a separate photo table.

When the user profile is queried, the entirety of the user's photos are not necessarily required, only the thumbnail of the first photo (profile picture). Only a further action will require the rest of the photos.

I would like to know if it is optimal to store a duplicate of the thumbnail of the profile photo on the user profile table or should I continue to do a complex nested query to fetch the thumbnail of the profile?

Profile

user_id | avatar --(cached copy of profile[0].thumbnail)

Photo

user_id | image | thumbnail | order


texts: ON CONFLICT ON CONSTRAINT fail postgresql

texts:
INSERT INTO O_USER (ID, NAME, LASTNAME, EMAIL, USERNAME, PASSWD, DATE_REG, DATE_UPD, DATE_DEL, IS_DELETE, TIPO, FTP)
VALUES (%s,%s,%s,%s,%s,%s,DEFAULT,NULL,NULL,%s,%s,%s)
ON CONFLICT ON CONSTRAINT ux DO NOTHING;

ux constraint:

CREATE UNIQUE INDEX ux ON public.o_user USING btree (email,username);

but in multiple insert have this error:

psycopg2.IntegrityError: duplicate key value violates unique constraint "o_user_username_key"
DETAIL:  Key (username)=(jferraro) already exists.

what is the problem?

update add table information:

CREATE TABLE O_USER (
    ID          INTEGER         PRIMARY KEY,
    NAME        VARCHAR(30)     NOT NULL,
    LASTNAME    VARCHAR(30)     NOT NULL,
    EMAIL       VARCHAR(50)     NOT NULL UNIQUE,
    USERNAME    VARCHAR(30)     NOT NULL UNIQUE,
    PASSWD      VARCHAR(30)     NOT NULL,
    DATE_REG    DATE            DEFAULT NOW(),
    DATE_UPD    DATE,
    DATE_DEL    DATE,
    IS_DELETE   NUMERIC         NOT NULL,
    TIPO        VARCHAR(10)     NOT NULL,
    FTP         VARCHAR(30)     NOT NULL,
    CONSTRAINT  DEL             CHECK       (IS_DELETE IN ('0','1')),
    CONSTRAINT  FK_FTP          FOREIGN KEY (FTP) REFERENCES FTP_USERS(FTP_USER),
    CONSTRAINT  UX              UNIQUE      (EMAIL,USERNAME),
    CONSTRAINT  TYP             CHECK       (TIPO IN ('ADMIN','STANDARD'))
);

update resolve the problem:
the problem was that i declared two different mode for make unique email and username, for resolve this problem i delete unique near the collums and use only unique constraint "UX" for email and username.
thanks all for the support.


texts: How can I check if connection to Sql Server is encrypted?

texts:
In SQL Server I can "Force Encryption" in protocols settings. If I leave that set to "No" any client can still require encryption.
Can I somehow see what connections to the database are using encryption?


texts: How do I reset the postgres password for PostgreSQL on Windows?

texts:
I have installed PostgreSQL on my Windows machine for development. Now I haven't used it for a while, and have forgotten the password for user postgres (default admin user in PostgreSQL). How can I reset the password for postgres in PostgreSQL on Windows?

I followed Milen's suggestion, but got this message when trying to reload the configuration file:

C:\Program Files\PostgreSQL\9.1\data>pg_ctl reload -D .
pg_ctl: could not send reload signal (PID: 3396): Operation not permitted

In Windows there is a menu option "Reload Configuration" that worked better, as shown below:


texts:
I want to find the tablespace of an index organized table with primary key.

With dba_segments I can only find the tablespace for the primary key.

But how can I find the tablespace for the table?


texts:
This is a general design question, not related to a specific DBMS.

I will use a basic employee record to illustrate.  I'm not worrying about the specific syntax, I am asking for help with the approach.

CREATE TABLE employee(
id SERIAL,
name TEXT,
manager_id INT4,
modified TIMESTAMP DEFAULT NOW(),
modified_by INT4
);

Some ways to track changes to the manager_id in the employee table -

A database trigger to an audit table.  Requires no additional application code, but must be kept current with the source table to be of value
An audit log file that saves only the changed values in a JSON string.
Move the manager_id into a separate table and add an entry for every change.  The separate table would have an id, employee_id, manager_id and perhaps a current flag.  Retrieval would mean the whole table would have to be searched for the most current manager.

I can't use an ORM, which is what I would really like to do.

My question:

Is it better to keep a distinct, single record of the current data or sift through a table with history of the data's state each time?


texts: What is the correct/fastest way to negate @> in Postgres?

texts:
I have a text[] column in Postgres 9.5
I am planning on running a lot of queries with @> against it
I have a GIN index on this column

Normally you need to use the correct Postgres operator so that an index is consulted for your query. There is no operator that is the "opposite" of  @> as far as I know.

I ran EXPLAIN ANALYZE on a query for this table and it takes 8 times as long to run WHERE NOT column @> array['thing'] as it does to run  WHERE column @> array['thing']. The planner reports that it does a filter for WHERE NOT instead of a bitmap index scan on my index.

What is the correct way to negate @> so that my index is consulted?

I'm willing to create new indexes for this column, including functional indexes if that's the answer.

Here are depesz links:

https://explain.depesz.com/s/uXOT
https://explain.depesz.com/s/nc7Q
https://explain.depesz.com/s/wC9m
https://explain.depesz.com/s/Eat


texts: What's the difference between partitions and many tables?

texts:
PostgresSQL 10 and 11 introduced partitioned tables. Quoted from the official site:

  "Partitioning refers to splitting what is logically one large table
  into smaller physical pieces"

And the benefit are mainly performance gains in querying/deleting. But if we create many tables based on the partitioning criteria, wouldn't the benefit be the same?

I had a DB with 10K tables to store financial data, which worked fine for me, except for slow loading in pgAdmin and later a foreign key issue that made me struggle since it's a huge pain to update a foreign key. So I decided to rebuild the DB with one or two big tables. Then this new feature gave me hope again. I'm not sure if it's so good. What's the major difference between multiple physical or logical tables?

EDIT:
   I studied Pg11 partition for a while. It seems it's a good solution for my DB need. But, the question is even more annoying to me.
  As I undersatand, partitioned tables are stored separately, I suppose there would be some internal "index/planning" in paritioned tables. So it would be slightly faster than multiple(<10k) physical tables. Then while partitioned tables could have partitions as well, it's even more interesting and that's what I wanted actually.
  Then I found this post "https://stackoverflow.com/questions/6104774/how-many-table-partitions-is-too-many-in-postgres" saying several hundreds partitions are proper. I will assume he's talking about earlier version and I believe PG11 should be better which I'm not sure. And I saw from another post that more than 1000 partitions would slow query down due to planning which I think should more or less improved in PG11 too.
   Anyway, for now, I'm sure partition is what I'm looking for but I'm going to try a  10k paritions(with sub-partitions). Hopefully I'll be lucky enough to get a good result.
   BTW, pgAdmin4 slow down somehow is a common problem for earlier version and latest 4.4 more or less fixed this, based on my search in stackoverflow. So probably 10k physical tables are not that bad, except for foreigh key update, which I decided not to use anymore.


texts: How do I diagnose missing rows on a MySQL slave?

texts: I'm running traditional master/slave replication on MySQL 5.1.52, my master is set for statement based replication.  A few tables are missing rows on the slave (differing row counts), these tables are insert only on the master using "load data infile ...", with auto-increment Pks.  It is strange that the missing rows on the slave have gaps in the PK values where the rows should be.  Slave is marked read-only so there are no accidental deletes.


texts: SQL Server: Case expression returns NULL even with an ELSE

texts:
Does anyone know why this case expression would be returning NULLs even when there is an ELSE?  It returns 'Normal' and 'Premature', but also numerous NULLs.

   select distinct(external_id), encounter_date,
(select
 case
   when h2.encounter_date < DATEADD(WEEK, -3, isnull(f2.edd_final_date,'01-jan-1900'))
      and h.encounter_date < DATEADD(dd, 29, isnull(t2.date_value,'01-jan-1900'))
     then 'Premature-Emergency'
   when h2.encounter_date < DATEADD(WEEK, -3, isnull(f2.edd_final_date,'01-jan-1900'))
     then 'Premature'
   when h.encounter_date < DATEADD(dd, 29, isnull(t2.date_value,'01-jan-1900'))
     then 'Emergency'
   else 'Normal'
 end
 from enhfile h2
inner join ar00file a2 on a2.patient_no=h2.patient_no and a2.trans_date=h2.encounter_date
      inner join endfile d2 on d2.patient_no=h2.patient_no and d2.encounter_no=h2.encounter_no
         and d2.charge_no=a2.charge_no
      left join fmhfile f2 on f2.patient_no = h2.patient_no
      left join notesdtl t2 on t2.patient_no=n.patient_no and t2.note_id=n.note_id
          and element_id in ('4559','4560')
 where h2.patient_no=a.patient_no and h2.encounter_date=
     (select MaxEncDate
      from (select MAX(h4.encounter_date) as MaxEncDate from enhfile h4
              inner join ENDFILE d4 on h4.PATIENT_NO=d4.patient_no and h4.ENCOUNTER_NO=d4.encounter_no
            where h4.patient_no=h2.patient_no
              and a2.procedure_code in ('59409','59410','59400','59514','59515','59510','59415')
           ) v
     )
  and f2.history_date=(select MaxHxDate
                            from (select MAX(f3.history_date) as MaxHxDate from fmhfile f3
                                  where f3.patient_no=h2.patient_no
                                 ) w
                      )
  group by h2.patient_no, h2.encounter_no, t2.date_value, h2.encounter_date, f2.edd_final_date)
  as Circumstance
from ar00file a
inner join mpfile m on m.patient_no=a.patient_no
inner join exidfile x on m.patient_no=x.patient_no
inner join chhfile c on a.procedure_code=c.procedure_code
inner join enhfile h on a.patient_no=h.patient_no and  a.trans_date=h.encounter_date
inner join endfile d on h.patient_no=d.patient_no and h.encounter_no=d.encounter_no
left join fmhfile f on f.patient_no=h.patient_no
left join noteshdr n on n.patient_no=h.patient_no and n.note_date_time=h.encounter_date
          and n.note_type='FSP'
left join notesdtl t on t.patient_no=n.patient_no and t.note_id=n.note_id
          and element_id in ('4559','4560')
where d.charge_no=a.charge_no
and a.trans_date between '01-jan-2016' and '30-jun-2016'
and a.PROCEDURE_CODE in ('58600','58611','58670','58671','58150','58150GY','58180GY','59525')


texts: MySQL router on server with tomcat memory leak

texts:
Memory is slowly used by mysql router. I am running mysql router version 8.0.20 (I found there was a memory leak on mysql router 2.1.2 but mysql router 8.x is newer)
I have a 3 node single-primary cluster on MySQL 5.7.30 with 13 application servers pointing to it from localhost and 1 server dedicated to mysql-router to developers.
Each application server has its own mysql router locally configured as well as a tomcat server. There are about 14 mysql routers pointed at this cluster. All mysql routers are configured the same.
mysqlrouter.conf
[DEFAULT]
user=mysqlrouter
logging_folder=/var/log/mysqlrouter
runtime_folder=/etc/mysqlrouter/run
data_folder=/etc/mysqlrouter/data
keyring_path=/etc/mysqlrouter/data/keyring
master_key_path=/etc/mysqlrouter/mysqlrouter.key
connect_timeout=15
read_timeout=30
dynamic_state=/etc/mysqlrouter/data/state.json

[logger]
level = INFO

[metadata_cache:prod1]
cluster_type=gr
router_id=5
user=mysql_router5_qgi8an1mm3s9
metadata_cluster=prod1
ttl=0.5
use_gr_notifications=0

[routing:prod1_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://prod1/?role=PRIMARY
routing_strategy=first-available
protocol=classic

[routing:prod1_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://prod1/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic

[routing:prod1_x_rw]
bind_address=0.0.0.0
bind_port=64460
destinations=metadata-cache://prod1/?role=PRIMARY
routing_strategy=first-available
protocol=x

[routing:prod1_x_ro]
bind_address=0.0.0.0
bind_port=64470
destinations=metadata-cache://prod1/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=x

The memory allocated to mysql router on these 13 production servers continues to grow until it crashes the server. Currently one is using 3gb of ram. If I restart the server it frees up the memory. It seems to randomly go through these 13 and start flaring up. The one dedicated to developers, only running mysql router has not had an issue.
On average the mysql router grows in memory usage by 1gb a week. About every three weeks I need to restart mysql router on these servers so that the tomcat server does not crash.
Here is the mysqlconf from the developer machine
#File automatically generated during MySQL Router bootstrap
[DEFAULT]
user=mysqlrouter
logging_folder=/var/log/mysqlrouter
runtime_folder=/etc/mysqlrouter/run
data_folder=/etc/mysqlrouter/data
keyring_path=/etc/mysqlrouter/data/keyring
master_key_path=/etc/mysqlrouter/mysqlrouter.key
connect_timeout=15
read_timeout=30
dynamic_state=/etc/mysqlrouter/data/state.json

[logger]
level = INFO

[metadata_cache:prod1]
cluster_type=gr
router_id=1
user=mysql_router2_6tobldcfht67
metadata_cluster=prod1
ttl=0.5
use_gr_notifications=0

[routing:prod1_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://prod1/?role=PRIMARY
routing_strategy=first-available
protocol=classic

[routing:prod1_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://prod1/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic

[routing:prod1_x_rw]
bind_address=0.0.0.0
bind_port=64460
destinations=metadata-cache://prod1/?role=PRIMARY
routing_strategy=first-available
protocol=x

[routing:prod1_x_ro]
bind_address=0.0.0.0
bind_port=64470
destinations=metadata-cache://prod1/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=x


texts: Mysql Innodb as Key value store, performance

texts:
I read about NoSQL databases using Key value pairs (KVP) tables. So i wanted to find out if KVP tables would be faster than conventional relational tables (with multiple columns) in mysql with innodb engine.

So I created a database table with an auto increment primary key and 12 other columns (firstname,lastname etc). All string values except the primary key. I filled it up with 500.000 records (random strings).

Now i created another database and a table but only with 2 columns: Key and value. Now i converted all the records of the conventional database to the KVP principle so row(1. john) becomes 000001_firstname = john throug queries like INSERT INTO kvp (SELECT CONCAT(LPAD(id, 8, "0"),"_firstname"),first_name FROM datatable);. This resulted in a table with 6 million records.

Now i disabled query caching and other caching and i ran the following query on the conventional table: "SELECT id FROM datatable WHERE first_name = 'Ehm'"
and the following query on the kvp table: "SELECT id FROM datatable WHERE key like '%firstname' and value = 'Ehm'"

I tested the speed of this query doing 100 iterations which resulted in 0.4 seconds for the kvp table but 20 seconds for the conventional table (with a flush tables after each query).

I used a php-script for testing:

public function benchmark($query,$dbName){
    $db = new mysqli("localhost", "root", null, $dbName);
    $db->set_charset("utf8");
    $time = microtime(true);
    for($i=0; $i<$this->iterations; $i++){
        mysqli_query($db,$query);
        mysqli_query($db,'flush tables;');
    }
    $duration = microtime(true)-$time;
    $db->close();
    return $duration;
}

how is this speed difference possible?

i initialized both tables the same way
disabled query caching
i did not put an index on value column of kvp table.

same result with the query: "SELECT id FROM datatable WHERE first_name like '%ad%'"

i tried to find if there was a fault in my testingscript but i could not find anny. Furthermor i tried to find literature explaining this but i could not find anny. I use mysql 5.6 and somehwere i read mysql uses memcache since 5.6, maybe this has influenced the result?

This is the result i got:

what could have influenced this result?


texts: What is a good unique identifier for customer and payment details for this tourism company?

texts:
Given a tourism company, Air T&T, has  customers.  When a customer checks in to an AirTnT facility (i.e. house-share), AirTnT records the customer's first and last names, address, date of birth, current mobile number, one unique identifier for identity verification, one unique identifier for payment details, and emergency contact number.

I was thinking social security number for identity verification and bank account number for payment details? What are the pros and cons of each-especially for payment details? I know credit card number is bad because of the security threats?


texts: Are there any extension/add-on tools to improve report-writing in SSRS?

texts:
My company is implementing some software which uses SSRS as the engine for report presentation both in and out of the application, so we'll be customizing a lot of SSRS reports soon.

Microsoft has put a good amount of functionality in SSRS but, frankly, Report Builder et al are still not the easiest tools to use for report-writing...

My Google searches seeking extensions or add-ons or other third-party tools to help with the report and query writing tasks doesn't find anything except what Microsoft has provided... Is this really the case, that there are no tools to improve on SSRS?


texts: When to break down attributes in tables into sub-tables for normalisation

texts:
I am currently developing a data model. This model contains an entity "Asset", which has many numerical attributes, volume, quantity, price, etc...

I also have string attributes that are part of the Asset table. Say one of those is Asset Type, this can be either a currency forward, stock, fund, index, etc... This is a good representation of the general cardinality of these string attributes, they have at most 10-15 different entries. The cardinality for the Asset table though, is in the tens of thousands.

Should I create a separate table for my Asset Type attribute and reference it using a foreign key in my Asset table, or is it simpler to leave the Asset table un-normalised and store these attributes directly, as strings, in the Asset table?


texts: Is there anyway to tell when the last query was made against a database?

texts: Is there anyway to tell when the last query was made against a SQL 2008 R2 database without downloading a third-party tool ?


texts: Calculate ticket sales by using SUM in SELECT clause

texts:
In this ticketing setup on dbfiddle, I am trying to calculate the ticket sales for a particular event by multiplying the ticket price and the total number of tickets sold for that event, based on their type i.e. standard, VIP.

So far I have this query,

SELECT tt.ticket_type,COUNT(tt.ticket_type) AS 'tickets_paid' , SUM((COUNT(tt.ticket_type)) *tc.price)
FROM tbl_tickets t INNER JOIN tbl_ticket_count tc ON t.ticket_count_id = tc.ticket_count_id
INNER JOIN tbl_events_new e ON tc.event_id = e.event_id
INNER JOIN tbl_ticket_type tt ON tt.ticket_type_id = tc.ticket_type_id
WHERE e.event_id = 164 AND t.pay_status ='paid' GROUP BY tt.ticket_type

but I keep getting 'invalid use of group function - 1111' error after adding the multiplication part of the clause. How can I carry out this operation with the SUM function using the ticket price of type decimal and number of tickets sold?


texts:
I am trying to make the table name dynamic

declare @varString as nvarchar(10)
set @varString = '06232016'
declare @testString as nvarchar(500)
set @testString = 'SELECT  *  from ' +  'Cases_' + @varstring
execute sp_executesql @testString
Select top 10 FROM @teststring

The first 5 lines of the code are working fine. Now how do I use the dynamic table name I just created? The last line is giving the error "Incorrect syntax near the keyword 'FROM'".


texts: Most efficient way to execute DELETE and INSERT operations in the same Table

texts:
I have the following structure:

Table1              Table2
C11, C12              C21
 1   A                 A
 1   B                 B
 1   E                 W
 2   F
 2   B
 3  ...

I need to execute the following two operations:

  To delete those rows which are in Table1 but not in Table2 and C11 = @Parameter (In this case @Parameter = 1)

  To insert those rows which are not present in Table1 but are present
  in Table2

 Result
 Table1
 C11, C12
  1   A
  1   B
  1   W
  2   F
  2   B
  3  ...

The simplest way to solve this is first deleteing all the records in Table1 and then inserting all records in Table2:

DELETE FROM Table1 WHERE C11 = @Parameter
INSERT INTO Table1 SELECT @Parameter, C21 FROM Table2

Definetely I am looking for something more efficient, so I tried the following:

DELETE t1 from Table1 t1 -- Delete
LEFT JOIN Table2 t2 ON t1.C12 = t2.C21
WHERE t1.T11 = @Parameter AND t2.C21 IS NULL

INSERT INTO Table1
SELECT @Parameter, C21 from Table2 t2 -- INSERT
LEFT JOIN Table1 t1 ON t1.C12 = t2.C21 AND t1.C11 = @Parameter
WHERE t1.C12 IS NULL

I am concern about performance because Table1 has millions of records. I think the join in both querys is a waste of resources.

I did try to create a select which could return both kind of records, those who need to be deleted and those who need to be inserted, but i did not succeed. I also tried using the MERGE operation but I think MERGE does not fit in this specifyc situation

Maybe someone has faced this problem before and can provide some advice.

I will appreciate any suggestion.


texts: Some users can have a fixed amount of additional fields, EAV or not?

texts:
Say I have to store data about two different types of person roles or occupations, i.e., Consumers and Merchants, and both types of roles share a single base table called User.

In this respect, Merchants need to have a few additional fields, e.g., CompanyName, CompanyDescription.

What is the most efficient way to design a database for these specifications?

Have extra columns on the User table.
Have an extra table called MerchantData which has a UserId FOREIGN KEY.
Have a UserMeta EAV table which allows any amount of additional values.

I am currently leaning towards option 2, as the additional fields are fixed. Are there any down sides that I am not seeing?


texts: Is data retrieved from SQL Server compressed for transmission?

texts:
Is data retrieved from Microsoft SQL Server compressed? If this is controlled by the connection string, is there any simple way to tell if any particular app is using it?

I'm examining analysis tools, and the volume of data can take minutes to transmit over our network. I'm wondering whether I should expect a performance increase if we pull data from a compressed data store on the same remote server.

As long as we're on the topic, I'm curious: is data transmitted in binary or ASCII? For example, if the value 12345 is queried from an INT column, is it transmitted as the five bytes 0x31, 0x32, 0x33, 0x34, 0x35; the two bytes that are required for the value; or four bytes as required for the column?

To be clear, I understand that there are options regarding storing data with compression, and backing it up. I'm asking about how data is transmitted.


texts: MySQL using different index when direct index exist on columns

texts:
I have following table (stripped off other columns, indexes for simplicity)
CREATE TABLE `issues` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `book_id` int(11) NOT NULL,
  `number` int(11) DEFAULT '0',
  `title` varbinary(1024) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_issues_on_book_id_and_number` (`book_id`,`number`),
  KEY `index_issues_on_book_id_and_updated_at` (`book_id`,`updated_at`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

My query:
EXPLAIN SELECT `issues`.* FROM `issues` WHERE `issues`.`book_id` = 1
AND `issues`.`updated_at` BETWEEN '2020-06-01 08:17:03' AND '2020-06-30 08:17:03'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: issues
   partitions: NULL
         type: ref
possible_keys: index_issues_on_book_id_and_number,index_issues_on_book_id_and_updated_at
          key: index_issues_on_book_id_and_number
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

Why it's not using index_issues_on_book_id_and_updated_at when the index is created on the exact columns that I want to query on.


texts: How can I efficiently downsize some Unicode fields?

texts:
We have a SQL Server 2012 Enterprise live transactional database that is now growing over 1G per month and is becoming a size problem for us. It is currently at 23G. Character type fields are all Unicode and I have calculated a savings of 5G in space converting only 2 such fields averaging 206 characters each to non-Unicode, and almost 10G in space if we convert a few more of them from nchar and nvarchar to char and varchar types. These fields will never have a requirement to hold Unicode characters that cannot be in the SQL_Latin1_General_CP1_CI_AS collation as they come in as plain ASCII originally and will always do so per the protocol standard.

I’m the software architect and chief C# developer though only a DBA hack or I would not have designed our database to have Unicode fields for high volume tables that did not need Unicode for those fields when the database was created 3 years ago. I want to correct this mistake now before we finalize converting to an AlwaysOn environment to help with various performance and backup issues.

After downsizing these two or more fields, we would like to shrink the database one time to take advantage of the space savings for full backups, and for seeding an AlwaysOn environment.

Questions are –

What is the safest and most efficient conversion technique for downsizing columns from nchar/nvarchar to char/varchar types? Esp. when there are multiple fields in the same table to be converted. I tested doing an “add new column, set new=old, drop old, rename old to new” for both of the main two fields I want to convert from nvarchar(max) to varchar(max), and it took 81 minutes on our test server (4 virtual core, 8G memory) before running out of disk space even though there was 8G left on the disk, and the db has unlimited size set (Could not allocate space for object 'dbo.abc'.'PK_xyz' in database 'xxx' because the 'PRIMARY' filegroup is full). I did delete an old database before it finished after getting a disk warning so maybe it did not count that new space. Regardless it was too slow. And this was on just the two largest of these columns (12.6M rows) and only ran 2 to 3% CPU busy so seemed not very efficient, and indicated unacceptable downtime if we were to convert even these two fields much less any additional fields. Average field size for these two fields was only 206 characters or 412 bytes each. Another technique I plan to try is to create the new table def in a new schema, select into it from the old table, then move tables amongst schema and delete the old table. I have a FK and indexes to contend with on the table.
If I figure out how to do #1 efficiently within an acceptable maint window, what is the safest practice for doing a one-time shrink and end up with organized/rebuilt indexes and updated Statistics? I understand the logic of not doing regular shrinks and that sometime it can actually increase the size.
Is there any third party tool that could take a backup and restore it into a new database with the modified field definitions or otherwise convert certain field types?

Any suggestions and best practices most welcome.

Thanks, Dave


texts: Change default timezone to America Pacific

texts:
SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;
Result: SYSTEM  SYSTEM

 SELECT CURRENT_TIME;
 Result: 06:25:07

  But MY System time is 11.55 AM IST  and PST TIME is 11.25 PM

  How can I change timezone to AMERICA PACIFIC as 11.25 PM in both
  MYSQL Workbench and using python.


texts:
I am facing a problem regarding SQL Log Reader Agent. Whenever, I start SQL Server Agent, Log Reader Agent does not start. I have to start manually.

Do I have start Log Reader Agent manually every time?
How can I start Log Reader Agent via T-SQL?


texts: 16 Cores 12 GB RAM server MySql Configuration - my.cnf

texts:
The server takes 20+ seconds (wait time/slow IO response time) to response to a HTTP request even with memcached and APC installed. I believe this has something to do with MYSQL since the site as lots INSERT queries.

Any help would be greatly appreciated. Thanks in advance.

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
               6.38    0.03    1.05    0.40    0.00   92.14
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          10.37    0.00    1.61    3.14    0.00   84.87
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           9.40    0.00    1.41    1.53    0.00   87.67
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          10.02    0.00    1.42    1.09    0.00   87.46
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           9.32    0.00    1.31    0.78    0.00   88.59
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          10.62    0.00    1.57    2.37    0.00   85.44

Dedicated server specs:

2 x Intel Xeon-Westmere 5620-Quadcore [2.4GHz] processors
6 x Hynix DDR3 2GB DDR3 x8 [2GB] (total of 12GB)
2 x Western Digital WD Caviar RE4 WD5003ABYX [500GB]
MySQL version: 5.1.56-log MySQL Community Server (GPL)
Current database size: 4.2 GiB ~25,350,659rows

Here is the current my.cnf configuration:

[mysqld]
set-variable = max_connections=1024
log-slow-queries
safe-show-database

back_log                        = 80
max_connect_errors              = 9999999
table_cache                     = 5000
binlog_cache_size               = 1M
max_heap_table_size             = 128M
sort_buffer_size                = 500K
join_buffer_size                = 500K
thread_cache_size               = 100
#thread_concurrency              = 16
query_cache_size                = 512M
query_cache_limit               = 8M
query_cache_min_res_unit        = 2K
thread_stack                    = 192K
tmp_table_size                  = 384M
default_table_type              = INNODB

long_query_time                 = 2
log-slow-queries=/var/log/mysql.slow.queries.log

innodb_additional_mem_pool_size = 80M
innodb_buffer_pool_size         = 9G
innodb_file_io_threads          = 4
innodb_thread_concurrency       = 33
innodb_log_buffer_size          = 20M
innodb_log_file_size            = 800M
innodb_log_files_in_group       = 3
innodb_max_dirty_pages_pct      = 90
innodb_lock_wait_timeout        = 120
innodb_file_per_table           = 1

read_rnd_buffer_size=2M

thread_concurrency=33

@DTest
Table Structure

CREATE TABLE IF NOT EXISTS `nameapp` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uid` bigint(20) NOT NULL,
  `name` varchar(225) CHARACTER SET latin1 NOT NULL,
  `pub` int(11) NOT NULL,
  `datetime` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

Slow query log

# User@Host: exootnet_lab[exootnet_lab] @ localhost []
# Query_time: 2.044988  Lock_time: 0.000098 Rows_sent: 0  Rows_examined: 0
SET timestamp=1326290464;
INSERT INTO `nameapp` (`uid`,`name`,`pub`,`datetime`) VALUES ('455330','AWESOMENESS','1','1326290462');


texts: Can install a test machine with Oracle 11G R2, not express edition, on a VirtualBox?

texts:
Im starting like a Oracle DBA, and I want play with the database and your administration task in a test enviroment.

I have a macbook pro i5, and had thought install on it oracle under virtual box machine.

What distro of linux your recommends me to start?
I can install Oracle RAC in a Vbox machine?
Some handbook?
Other considerations...

I appreciate all your comments, and excuse my poor english D:


texts: What can cause delay in SELECT statement execution and high ASYNC_NETWORK_IO even with "disregard results after execution"

texts:
We are facing performance problems on our SQL Servers.
7 servers, all running the same application, all have the same problem.
SELECT @@VERSION
Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64)   Jan 23 2020 21:00:04   Copyright (C) 2017 Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

When you look at the acummulated total server wait statistics the show 99% ASYNC_NETWORK_IO. We first assumed that this was caused by the application. However:
When we take query (captured from the application) and run it as a test from within SSMS with disregard results after execution. It takes about 6 seconds when we run it over a TCP connection and 50 seconds over a Named pipes connection. We get very slow results.
(without disregard results after execution the query gives a result of 57k rows 227 columns and a size of 221MB.)
It's core edition so we can't use a local connection. We tested connecting remotely with both TCP and Named pipes.
over TCP
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 4 ms, elapsed time = 4 ms.

(57844 rows affected)
Table 'STORE-CUW01$Item'. Scan count 1, logical reads 14774, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 1359 ms,  elapsed time = 6868 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

So even though though it only takes 0.62 seconds for the clustered index scan to complete, it still takes 6.8 seconds to complete the query even though disregards results after execution is checked
Over Named Pipes
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(57844 rows affected)
Table 'STORE-CUW01$Item'. Scan count 1, logical reads 14774, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 1578 ms,  elapsed time = 54202 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

With Named pipes it's even worse. Again it only takes half a second for the clustered index scan to complete and it takes 54 seconds for the query to complete.
If I restore the database to my laptop and run the same query I get 0.4 seconds for the clustered index scan and 1375ms CPU time, 1462ms elapsed time.
by using https://www.sqlskills.com/blogs/paul/capturing-wait-stats-for-a-single-operation/
I collected the wait stats for both the test with a TCP connection and Named Pipe connection:
TCP

Named Pipes

The wait's don't add up tot the total elapsed time. Only 0.472 seconds waittime and 6.8 elapsed time on the tcp test and only 40.7 Waittime vs 54.2 elapsed time on the Naped Pipe test.
further relevant info

The SQL Servers are virtual servers VMware
It's windows core edition
4 cores
64GB ram, only 16 GB assigned to SQL Server
The application service is running on the same server as SQL Server
The application itself connects with MARS Sessions

We don't understand where the time delay is coming from. Shouldn't the query complete much faster if you have disgard results checked?


texts: What is the migration/deployment path from a LocalDB solution to Enterprise instance?

texts:
Given the extant definition for SQL Server 2012's LocalDB as

  SqlLocalDB is a local, low-overhead database engine of SQL Server 2012
  (and future versions) that allows developers to focus on development
  rather than instance configuration or security.

I was curious what the path from development in LocalDB to a production instance would look like, especially in light of the issues with user instances (ca. SQL 2005) and trying to promote one of them to production and devs would lose track of which instance they were actually trying to promote. A quick search on StackOverflow betrays such issues in various forms. Does LocalDB improve/streamline deployment in such circumstances?


texts: SSMS not coloring tables, columns and variables

texts:
I use SSMS 2012 v11.0.5058.0.

MSDN says "System table, view, or table-valued function" should have green color. I'm used to have green color in variables and a few other token types too. But my SSMS keeps them black as raw values.

I followed Change Font Color, Size, and Style and tried to set default, but it didn't solve. I also tried to set them manually but didn't find them to select.

Any idea what may be the issue?

This happens in both cases of opening an .sql file or having SSMS create a select query for a table. Even when there are [] around the column name. It remains even after restarting Windows.


texts: finding out the Backup set physical name

texts:
I created a maintenance plan to back up two user databases and system databases over a month ago. I queried the msdb database to view the size of the backups using the following query.

SELECT
 bs.database_name,
 bs.type,
 (bs.backup_size/1024)/1024 [BackupSize (MB)],
 (bs.compressed_backup_size/1024)/1024 ,
 bs.recovery_model,
 bf.physical_device_name,
 bs.user_name,
 bs.backup_start_date,
 bs.backup_finish_date
FROM
 msdb..backupset bs
INNER JOIN msdb..backupmediafamily bf
 ON bf.media_set_id=bs.media_set_id

What I saw was, I was able to view the backup set I had created with all the details along with the physical device name. However, there was one more backup set that had all the metrics regarding the backup size, when it was taken etc,  but the physical device name had a hexadecimal representation. I verified the sql jobs and maintenance plans and found nothing related to this job. Where else should I find the source of this backup?


texts:
I apologize if the title for the question doesn't match what I am asking what to do.  I am not sure how to describe what I am trying to accomplish.

I have the following tables in my database:

Table customers
    customer_id (PK, int, NN)
    customer_name (varchar(100), NN)

Table jobs
    job_id (PK, int, NN)
    job_number (varchar(10), NN)
    job_description (varchar(MAX), NN)
    customer_id (FK, int, NN)

Table job_statuses
    status_id (PK, int, NN)
    status_description (varchar(50), NN)

Table job_status_logs
    log_id (PK, int, NN)
    job_id (FK, int, NN)
    status_id (FK, int, NN)
    log_date (date, NN)

Now I am trying to program a procedure where I pass it a Job Number and it does the following:

1) Checks if the Job Number exists within the database, if not, return an error message.

2) If the Job Number exists return the following columns from the following tables:

jobs: job_id, job_number, job_description
customers: customer_name
job_status_logs: log_date (WHERE status_description is 'Open') & log_date (WHERE status_description is 'Closed')

I am mainly having an issue with the very last part, as I am unsure how to form the query looking for the last two points of data (the ones from the job_status_logs table).

EDIT: This is currently what I have for the procedure

CREATE PROCEDURE dbo.GetJobData(
    @JobNum   VARCHAR(10),
    @response NVARCHAR(500) OUTPUT
) AS
BEGIN
    DECLARE @JobID INT
    SELECT @JobID = job_id
      FROM jobs
     WHERE job_number = @JobNum
    IF @JobID IS NULL
    BEGIN
        SET @response = 'Cannot find data for Job "' + @JobNum + '".  Job does not exist.'
        RETURN
    END
    SELECT j.job_id AS [ID], j.job_number AS [Job], j.job_description AS [Description], c.customer_name AS [Customer]
      FROM jobs AS j INNER JOIN
           customer AS c ON c.customer_id = j.customer_id
     WHERE j.job_id = @JobID
END
GO

As stated above, I do not know how to set up the query to grab the data I am looking for from the job_status_logs table.


texts: Understanding time format of the EXPLAIN command - Postgres

texts:
When I run the EXPLAIN ANALYZE command on a given query, I'm having a difficult time interpreting the outputted time value. For example (actual time=8163.890..8163.893). Do the internal decimals represent repeating characters?? Sorry, this may be a noobish question, but I want to make sure I'm interpreting the results correctly.

   ->  GroupAggregate  (cost=2928781.21..2929243.02 rows=1 width=27712) (actual time=8163.890..8163.893 rows=1 loops=1)


texts: Backing up my SQL Server System Databases

texts:
I'm trying to create a recurring scheduled job to fully backup the master, msdb and model databases, I'm using the Maintenance Plans for SQL Server 2008 R2, in the "Specific Databases to choose from the Backup DB Task Window" I wasn't sure if I should choose [System Databases] or [These databases: master, model, msdb]? Aren't they the same if I specifically check mark the specific databases or just choose the radio button for all system databases?
If I choose to create them under the SQL Agent, would that be the same results? as I just need to create a daily or weekly backups for them.


texts: Is index on foreign key used? (No difference in EXPLAIN.)

texts:
Essentially, I am joining two MyISAM tables and have found that the EXPLAIN output for the query does not change regardless of whether I have an index on the foreign key in the parent table.  I'm not sure if the index is in fact being used and it simply can't be seen in the EXPLAIN output, or if in fact for some reason the index isn't useful (either in this particular case or in general).

For example:

Table 1:
ex_cat, ex_name
(Primary key on ex_cat.)

Table 2:
cat, ex_cat, data
(Composite primary key on cat, ex_cat; separate
 non-unique index on ex_cat?)

I am running the following query:

SELECT *
FROM table1 INNER JOIN table2
     ON table1.ex_cat = table2.ex_cat
WHERE table2.cat = 'value'
ORDER BY table2.data;

It gives me the following EXPLAIN output, regardless of whether I have an index on the ex_cat column in table 2 or not:

id  select_type  table   type    possible_keys  key      key_len  ref             rows  Extra
1   SIMPLE       table2  ref     PRIMARY        PRIMARY  22       const           1     Using where; Using filesort
1   SIMPLE       table1  eq_ref  PRIMARY        PRIMARY  32       table2.ext_cat  1     Using where

Which seems right; it's narrowing using the WHERE clause on the parent table primary key, then it's joining the result using the ext_cat column in the parent table and the primary key in the child table.  However, since it's the same either way, there's nothing to explicitly show whether there is value in having an index on table2.ext_cat.  The tables are so small the queries complete near-instantly either way, so this is more out of academic interest at the moment.

I also tried switching to InnoDB to see if there was any difference in behaviour (not officially using foreign keys, since those would create indexes automatically; just the same test as above) but the explain output was identical.

So, is it indeed improving query performance to have an index on the foreign key?  If so, is there some way to 'see' the effect with an EXPLAIN (or otherwise)?  And if not, can you explain what about this situation causes it to be unnecessary?

Edit: OK, I expect what's going on is because MySQL is choosing to first narrow using the WHERE clause, then join the result to the child table, any indexes on the parent table are irrelevant at that point.  In cases where it is more efficient to join first, then apply the clause, it would use the foreign key index in the join.  Is that correct, and do both behaviors indeed occur depending on the table contents and the query?  If not, when are indexes on foreign keys useful?

Edit 2: According to this it would appear the answer is never, if the parent table appears first in the EXPLAIN: mysql-indexes.  An index is only used on one half of a join, since it essentially goes one table at a time, joining the result to the next table in the cascade.


texts: How to Get the Last Day of the Previous Month?

texts:
Is there a quicker and easier to read way in order to get the last day of the previous month in T-SQL?

I have found this piece of source code, which does the trick, but looks obfuscated unless properly previously commented:

-- Declar variabila pentru ultima zi din luna anterioară.
DECLARE @DSL [DATETIME]
SET @DSL = (SELECT DATEADD(  dd
                           ,  0
                           , DATEDIFF(  dd
                                      ,  0
                                      , DATEADD(   s
                                                , -1
                                                , DATEADD(  mm
                                                          , DATEDIFF(  m
                                                                     , 0
                                                                     , GETDATE())
                                                          , 0)))))
PRINT 'Declar variabila pentru ultima zi din luna anterioară.'
PRINT @DSL

The message window with the results show the following output:

Declar variabila pentru ultima zi din luna anterioară.
Mar 31 2015 12:00AM

Please forgive my ignorance, I feel like a new-born newbie when I see these tricks.

SELECT @@VERSION reports:

  Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (Intel X86)
      Sep 16 2010 20:09:22
      Copyright (c) 1988-2008 Microsoft Corporation
      Express Edition with Advanced Services on Windows NT 5.1  (Build 2600: Service Pack 3)


texts: Update table from calculated temporary table

texts:
I have two tables, one containing a set of user ID's, vote direction (-1 or 1) and ID of the object they voted on (casted votes table). In the other table, the object id, upvotes, downvotes and totalvotes.

I'm trying to write a command to update the values in the second table by tallying the votes in the table of votes cast. I'm trying to think of the best way to do this, currently I am thinking of generating a temporary table that contains Object Id, upvotes, downvotes and totalvotes from the votes cast table (using a few SUM functions and some other stuff to generate the columns), then using an update query to go through and change the values in the second table by referencing the temporary table.

Is this the best way to do it or am I massively overlooking a feature that does this already without all the messing around in MySQL?


texts: How Can I Determine Object Distribution in a Multiple-File Filegroup?

texts: I am looking to eliminate an extraneous data file in a filegroup in my DB but don't want to use the dreaded DBCC SHRINKFILE method, I am preferring to rebuild indexes into an alternate FG, perform the shrink with EMPTYFILE followed with removal of the file and then rebuild back into the original filegroup. Is there a method to identify which table/index objects are populating the utilized pages/extents in the target database file?


texts:
I want to push the data from database to application instead of application pull the data.I have installed ms sql server and apache tomcat server. I have my application in apache tomcat, here I made connection to database. Now I want database send the data whenever there is update in data. But all I know is fetch the data from database is not good idea, because application needs to monitor the database for updated data will lead to fire the query for every 5 sec, this is not efficient as well.

I read some tutorials in Google for this, Finally I found one method name as Query Notification is useful as per requirements.But I don't know how to implement.

Tell me, query Notification is it good way for me.
where will I get good examples.


texts: Oracle 10g and Active Directory not behaving as expeted

texts:
I'm trying to connect to our Oracle 10g 64bit database running on a Windows 2003 server authenticating with an Active Directory user.

I found this weird results on our Development environment which I cannot replicate on the testing environment (same setup, different Active Directory)

I created a user as "OPS$DOMAIN\USER" and works ok if I log-in locally from the server, for example, running SQLPLUS / would connect directly without asking for username/password.
SHOW USER would return "USER is "OPS$DOMAIN\USER"".

The weird thing happened when I tried to log-in from my computer (win 7 32bit oracle 11 client) logged as domain\user.
SQLPLUS /@MYINSTANCE did not work... it wouldn't connect, asking me for a valid user/password.

After several attempts, a colleague suggested creating the user "OPS$USER" without indicating the domain. This does not work when I try and connect locally, but it works when I connect from my machine.
So now, SQLPLUS /@MYINSTANCE from my computer works, and SHOW USER returns "USER is "OPS$USER"".

Makes any sense?

Finally, we tried the same on our Testing environment, where it all seems to work as expected (only works if I use "OPS$DOMAIN\USER").

Any ideas why might this be happening? My machine is part of the Development domain environment, before you ask :D

I'm starting to believe this has nothing to do with Oracle config, but maybe some weird setup on our Dev AD.

Thanks in advance!


texts: Relation Algebra: Selection of Cartesian Product

texts: I am learning Relational Algebra basics for sql. I came across an example where the selection of cartesian product of two relations (R1 x R2) has been taken and I realize that it is same as selection(R1) x selection(R2). I checked the cardinalities of both and it turns out to be correct. Is it always true? If so, does it mean that this operator, mathematically speaking, satisfies the equation f(x.y) = f(x).f(y) where f can be considered selection operation?


texts:
Possible Duplicate:
  Why Does the Transaction Log Keep Growing or Run Out of Space?

I have a 300 GB database log in our SQL Server. I want to shrink all the logs but it is not allowing me to do so. I tried by using query and UI in SQL Server 2008 R2 . I used

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

But without any error database logs are not shrinking.

Then I want to use one more method as below :

I will detach the database from SQL Server, then after the move particular .LDF file to some other location and attach only the database file back to SQL Server. I am able to successfully move all the logs from SQL Server to some other server.

Please let me know this is good practice to move transaction log to some other drive. If not please suggest some other solution to recover from shrinking database log.


texts: Throttling SQL Server database cpu usage

texts:
I'm wondering if there is away to limit specific database from using server's resources like setting max CPU and RAM for each database [simpler to IIS App pools]?

I'm working with SQL Server 2014 Enterprise & I want to prevent some databases from slowing down the whole server.

Thanks


texts: Does anyone know of resources for the Oracle 1Z0-072 exam objectives?

texts:
I am currently studying for the Oracle 1Z0-072 certification exam and I'm finding some of the early objects very ambiguous and difficult to study for.  Topics such as:

Relational Database Models
Connecting to Oracle Databases

What do they mean by those? What skills or knowledge do I need to possess?

Thanks in advance for any direction or insight!

https://education.oracle.com/oracle-database-12c-r2-administration/pexam_1Z0-072


texts: Mysql getting first and last value from timestamp column

texts:
I am not getting the right result from my below query:

SELECT t.id as TaskId, cl.name as ClientName, p.name as ProjectName
     , t.name as TaskName, tp.name as TaskType
     , t.createdate as DateCreated,t.enddate as DueDate
     , u.firstname as TaskOwner, pr.name as Priorities
     , t.hours as AllotedHours, t.workedhours as LoggedHours
     , tl.firstEntry, tl.lastEntry
FROM tasks t
inner join (
    select distinct tasks_id,complete, createdate
        ,  min(createdate) as firstEntry, max(createdate) as lastEntry
    from tasklogs
    WHERE complete in (90)
      and createdate > '2018-08-25'
    GROUP BY createdate
) AS tl
    ON tl.tasks_id = t.id
inner join projects p
    on p.id = t.projects_id
inner join tasktypes tp
    on tp.id = t.tasktypes_id
inner join users u
    on u.id = t.users_id
inner join priorities pr
    on pr.id = t.priorities_id
inner join clients cl
    on cl.id = p.clients_id
where p.projecttypes_id=1 ;

I am supposed to get a single result with firstEntry (first log) and lastEntry (last log) columns, but I'm getting multiple rows for each selection:

As you see in the below image I am getting 3 rows, I need one row with highlighted First entry of 90 and last entry of 90 complete. Maybe my query is the wrong way to do that?


texts: Need scalable database solution for very big automated data entry database

texts:
I've been working on MySQL database in simple web applications for a long time and i now have a project which should maintain about 1,000 up to 10,000 query per minute (it is some logging database system) and a lot of theme are Insert statement.

First thing worried me is i don't think MySQL could handle that much query per second,I know i should buy stronger Server but i don't know if MySQL can be good solution for that.Is there any way to calculate that ability before buying servers?

Second important thing is that the database will grow too fast and it will be use all server resources like hard disk or CPU usage sooner or later and i should have an expanding solution,my question in this part from experienced database admins is :

"Is MySQL suitable for distributing or cluster databases ?"

or

"Is there any easy to use database which support clustering and distributing systems?" (I know MySQL have a cluster version but i don't know if it is appropriate in my project).

Edit:

The project is about logging users(by their RF-ID tags) as they pass throw the antenna range or rf-id readers or Electronic gates and read their permission to enter or leave the area which specified in database for that tag ID, and record there activity in database.The operator should access all these data and be able to manipulate theme.

P.S:

I know there are very professional servers with large capacity of hard disk like SAN or NAS(Network-attached storage),but as it is not national or very big company project i need easy to distribute database to distribute it over normal servers.


texts: What are the differences between "Stored Procedures" and "Stored Functions"?

texts:
So a comment from this question mentions, that there is a slight difference in "Stored Procedrues" and "Stored Funtions" in PostgreSQL.

The comment links to a wikipedia article but some of this don't seem to apply (e.g. that they can be used in a SELECT statement).

The syntax itself seem to be a little bit confusing:

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
    BEGIN
       [...]
    END;
$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

You create a FUNCTION but refer to it as a PROCEDURE.

So what's the difference between these two?


texts: Looking for application which will provide to determine project database growth and cost per day or month

texts:
I am using SQL Server 2008 for product development

Currently application is live with 20 users now if I want to check how my database is growing and how much it will grow in month or year projection to determine the cost of hardware. Is there any application or SQL Server built-in function?

Using SQL Server profiler I am able to monitor database queries and fine tuning.


texts: Getting stuck with creating better index (Optimize Join)

texts:
Im using the below query and it took min 5s to max 11s. I tried to create single column and multi-column index. But still I can't able to increase it's performance.

Row count on each table:

tbl1 - 400000
tbl2 - 200000
tbl3 - 50

Table definition:

CREATE TABLE `tbl1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `modified` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `customer` varchar(155) DEFAULT NULL,
  `social` enum( 'Facebook','twitter') DEFAULT 'LI',
  `grade` char(1) DEFAULT NULL,
  `Monthly` int(11) DEFAULT NULL,
  `Off_comments` text,
  `createDate` datetime DEFAULT NULL
  );

create table tbl2
(
id int  NOT NULL
);

create table tbl3 (
id int, frnd_id int
);

Select Query:

SELECT    `T1`.*,
          `T2`.`Card_No`
FROM      `tbl1` `T1`
LEFT JOIN `tbl2` `T2`
ON        T1.id = T2.id
LEFT JOIN `tbl3`
ON        T3.id = T2.On_site_id
WHERE     T1.modified<=Subtime(Now(),'00:00:10')
AND       T1.customer != 'bhuvi'
AND       T1.social !='Facebook'
AND       T1.social != 'twitter'
AND       T3.frnd_id= '4'
AND       T1.grade IN ('A','B', 'O')
AND       (
                    T1.Monthly IS NULL
          OR        T1.Monthly = 1)
AND       (
                    Off_comments IS NULL
          OR        Off_comments = ''
          OR        Off_comments="''")
ORDER BY  `T1.createDate`;

Indexed columns:

tbl1 - id,modified,customer, social,frnd_id
tbl2 - id
tbl3 - id

Explain plan:

Filesort
+- TEMPORARY
   table          temporary(tbl3,T2,T1)
   +- JOIN
      +- Filter with WHERE
      |  +- Bookmark lookup
      |     +- Table
      |     |  table          T1
      |     |  possible_keys  fk_T_1_idx
      |     +- Index lookup
      |        key            T1->fk_T_1_idx
      |        possible_keys  fk_T_1_idx
      |        key_len        5
      |        ref            db.T2.id
      |        rows           1
      +- JOIN
         +- Index lookup
         |  key            T2->id
         |  possible_keys  PRIMARY, id
         |  key_len        5
         |  ref            db.t3.id
         |  rows           2537
         +- Index lookup
            key            tbl3->INX_frnd_id
            possible_keys  PRIMARY,INX_frnd_id
            key_len        5
            ref            const
            rows           16


texts: Cancel / Rollback Transaction if Time Exceeded

texts:
I won't go into the fine details, but I've got a stored procedure that tests an inserted string value to check if it currently exists in the database.

We are dealing with billions of rows, and although the table structure is sound, and the indexes are nicely tuned, this validation check sometimes takes a few seconds.

I want to put some logic in the stored procedure that say if a certain amount of time has passed (say, 2 seconds) then cancel the IF EXISTS transaction and do something (return a default value etc...)

In short - is it possible in a SQL Server stored proc to trigger another transaction block if a certain amount of time passes?


texts: MS Access: Capture ListBox Scroll Event?

texts:
An Access 2016 ListBox control has 10 items, only 1 showing at a time. My Goal: when the user scrolls up/down using the scrollbar arrows, I would like the item displayed to be automatically selected without the user directly clicking on the item (via an event handler).

The scrollbar action does not appear to be captured by any ListBox Event. While the On Got Focus event will capture the first mouse click on the scroll buttons, subsequent clicks on the buttons do not trigger any control event.

I do understand that I can create custom up/down buttons to simulate the scrollbar actions, but I prefer to use built in routines.

Am I correct to conclude that the scrollbar events are handled by the Windows event handler, not Access?


texts: Cannot take backup into NAS storage at specific time

texts:
I have a job which runs every 30 minute and takes t-log backup of the database suing Ola's script. Every Thursday and Friday at 22:45 I get an error:

11/07/2019 22:45:00,Backup,Unknown,Error: 3041<c/> Severity: 16<c/> State: 1.
11/07/2019 22:45:00,spid60,Unknown,BackupDiskFile::CreateMedia: Backup device 'B:\SDSOFT-DB\EXPRESS\LOG\SDSOFT-DB_EXPRESS_LOG_20191107_224500.trn' failed to create. Operating system error 5(Access is denied.).
11/07/2019 22:45:00,spid60,Unknown,Error: 18204<c/> Severity: 16<c/> State: 1.

I asked sysadmin to check all settings regarding NAS storage and he said that everything ok and he doesn't know what is the cause of this problem. From my side, I mapped NAS storage network path into 'B' using xp_cmdshell(SQL SERVER 2014) by the help of the following command:

EXEC xp_cmdshell 'net use B: \\192.*.*.*\DBbackups /user:<username> <password> /persistent:yes'

I would like to point out again that I get no error during the rest of the time, only on Thursday and Friday at 22:45. Maybe someone had faced with this issue before and help me to tackle this problem.


texts: How to use and optimize subquery on 100 million rows

texts:
What I'm trying to do is running a job on more than 100 million domains which haven't processed before.

I have two tables, "domain" and "domain_setting", on every batch (10.000 domains per batch) I'm getting a list of domains from domain table by checking their status on "domain_setting". At first it was working fine but now approximately 10 million domain processed and it's getting slower.

I know that my server is crappy for this kind of database, it has only 8 GB of memory and it's also shared with Apache web server (150 concurrent connections), some other small databases and some lightweight PHP applications. My innodb_buffer_pool_size is only 1 GB.

Anyway, I would like to learn if there is a better way or thoughts to write this query more efficiently or any other approach for doing this job and speed up the process. Here is my SQL query:

SELECT * FROM domain
WHERE domain_ID NOT IN (SELECT domain_ID FROM domain_setting) OR domain_ID IN (SELECT domain_ID FROM domain_setting WHERE is_keyword_checked = 0)
LIMIT 0,10000;

I must get domains which are not exist in domain_setting table OR has "is_keyword_checked = 0" value in domain_setting table. How can I optimize this query?

Thanks in advance :)

EDIT:
I can rewrite my query with LEFT JOIN as

SELECT *
FROM domain
LEFT JOIN domain_setting ON domain_setting.domain_ID = domain.domain_ID
WHERE domain_setting.is_keyword_checked = 0 OR domain_setting.is_keyword_checked IS NULL
ORDER BY domain.domain_ID ASC
LIMIT 0,10000;

but the main problem here seems full table scan. I can run it backwards by ORDER by domain.domain_ID DESC for recent added records and I can get them very fast. But still needs to scan full table if you didn't run it for a while and if recent records became more than millions of rows. So, is there any way to avoid full table scan for such work?


texts:
I'm newbie in MySQL. I installed MySQL5.5 Server and Client in my Debian Wheezy. All I did was enter aptitude install myqsl-server. Now, I have no idea what to do!

I found a webpage that says I have to set up a root user password first, and, to do that, I have to enter with the following command:

mysqladmin -u root password NEWPASSWORD

Where "NEWPASSWORD" is, well, the new password. The rest are literally commands. So, I opened my terminal and as normal user, tried to enter with this command and I got this output:

mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!

So, considering all I did, what are the next steps? I have to create a root user password, but for what? And after that what do I have to do to create a simple table representing data?

All tutorials I read on the internet are very difficult to understand!


texts: Need to create batches of 2000 records within a subset of records

texts:
I have a table with 200,000 records.
One of the columns is a location ID.
I need to break down the entire table in batches of 2000 but group them by the LocationID column.

I have about 200 locations with 600 records to 4100 records in each location.

I need to show batch numbers within each location.  So if location 001 has 600 records, then there is a batchID of 001-1 which contains 600 records.  If location 002 has 2800 records then there are batch IDs 002-1 (2000 records) and 002-2 (800 records).  And so on.  So a location, 010 with 4100 records would have batchIDs of 010-1,010-2,010-3,010-4,010-5.

I have the following code that works to create batches of 2000, but I can't figure out how to group it by the location #.

SELECT
 ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNumber,
(CASE WHEN CONVERT(int, (ROW_NUMBER() OVER (ORDER BY (SELECT 0)) % 2000)) = 0
 THEN 0
 ELSE 1 END) +
 CONVERT(int, (ROW_NUMBER() OVER (ORDER BY (SELECT 0)) / 2000)) AS BatchNumber,
 *
FROM [dbo].[LocationRecords]


texts: How to PIVOT/UNPIVOT if I don't know how many columns will it have?

texts:
I have this simple query:

I want to list something like this:

  Unidade1 \ PLaca1 \ Placa2 \ Placa3

  Unidade2 \ Placa1 \ Placa2

But I don't know how many placas a unidade will have.

I was trying to pivot like this:

SELECT   [undiade], [placa1],   [placa2], ..
FROM   Table1
join table2
bla bla bla
PIVOT
(
       Unidade
       FOR [unidade] IN ([placa1], [placa2], [placa3])
) AS P

if there's something I can't understand is how to pivot.

EDIT1:

I tried this with no succes:

Select distinct Unidade,
    substring(
        (
            Select ','+unidade  AS [text()]
            From unidades.Unidades u
            join t1.Viat v
            on u.unidade = v.unidade_id
                where v.parametrizado=0
            ORDER BY 1
            For XML PATH ('')
        ), 2, 1000) [Unidades]
From unid.unids.unids u

Conversion failed when converting the varchar value 'field' to data type int.


texts: Contact table for customers and employees

texts:
I have this relationship that I need to define in my database.
What would be a good way of referring contact details to my employee and to my customer in MYSQL?
I am not sure if the ||------|| is one to one or bidirectional? Which means the Employee can be searched for by contact details?
When I search for ContactDetails, phone number for example to get the Customer or Employee details

Update
Customer would have:
customerId(PK)
maybe other fields too.
Employee would have
employeeId(Pk)
maybe other fields too
ContactDetails would have:
contactDetailsId(PK)
phoneNumber
emailAddress
other fields
I want when I search for the customerId to get the details I have in the Customer table as well as the Contact details. Same for Employee.
How should I model my tables to achieve that?


texts: Create Calculated Fields from Distinct Values in Second Table

texts:
I'm trying to create a report in Microsoft SQL Server Report Builder 3.0 that has a date field populated with dates for a specified year and alongside, calculated fields for each distinct error code from the 'ERRCODE' field in 'Errors'. For each of the error codes, a count will be done for that error code from the 'Errors' table.

I already have a table function called 'CreateCalendar' that generates a date field with dates populated for the year specified. What I'm struggling to do is use the unique error codes from table 'Errors' and then create the calculated fields from those values.

The 'CreateCalendar' table function requires a 4 digit integer which is set as a parameter. This table function works fine alone and with another query I've used it with.

At the moment I have the below query but I have a feeling I'm going about this the wrong way. I'm still very new to SQL and have no formal training which I hope won't hinder me too much or annoy yourselves.

;WIth ErrorList As
(
 SELECT DISTINCT
  Errors.ERRCODE
 FROM
  Errors
 WHERE
  Errors.ERRTYPE = 'BATCH'
)

DECLARE @cnt int = 0;
DECLARE @name nvarchar(4);

SELECT
 CreateCalendar.cal_date,
 WHILE @cnt < (SELECT Count(ERRCODE) FROM ErrorList)
 BEGIN
  SET @name = (SELECT (ErrorList.ERRCODE) FROM ErrorList WHERE ROW_NUMBER() = @cnt);
  (SELECT Count(ErrorList.ERRCODE) FROM Errors WHERE Errors.ERRDATE = CreateCalendar.cal_date) As @name
  SET @cnt = @cnt + 1;
 END;
FROM
 CreateCalendar(@p_year)

If this is a workable approach, I believe a lot of where I'm going wrong is with my syntax and also picking the error code based on its row number. I could be way off the mark completely.

Any help is appriciated.

EDIT

EXAMPLE EXPECTED OUTPUT

cal_date   | 0001 | 0002 | 0003
01/01/2016 |   20 |   54 |    0
02/01/2016 |   15 |   22 |    2
03/01/2016 |   18 |   38 |    5
04/01/2016 |    0 |    0 |    0
05/01/2016 |   10 |    2 |    7


texts:
I have a table with columns like name, age, email etc. For some columns I also have parameters to control the behaviour of queries. I want to show the column's value if the corresponding parameter has the value 0. If that parameter has value 1 I don’t want to show that column.

For example, if I have data

name  age  show_email  email
abc,  22   1           abc@gmail.com  -- parameter = 1 do not show this value
def,  33   0           def@gmail.com  -- parameter = 0 => show this value

Then output of select name, age, email from MyTable1 will be

name  age  email
abc,  22   NULL          -- NULL because of parameter value.
def,  33   def@gmail.com


texts: Connection pooling with python mysql-connector

texts:
I would like to use connection pooling with mysql-connector 2.1.5

On Ubuntu everything works fine, but I would like to switch to Alpine and have used this file to install the connector (as described here).

The installation seems to work. I can use mysql.connector.connection(), but whenever I try mysql.connector.pooling.MySQLConnectionPool() I get the error AttributeError: 'module' object has no attribute 'pooling'.

I have also tried creating a pool implicitly using cnxpool = mysql.connector.connection(pool_name="...") but I cannot get the connection. Everytime I try cnx = cnxpool.get_connection() I get the error AttributeError: 'MySQLConnection' object has no attribute 'get_connection'

Is there any way to use pooling with the platform independent install package, or is it just not implemented there?


texts: Constraint - one boolean row is true, all other rows false

texts:
I have a column: standard BOOLEAN NOT NULL
I would like to enforce one row True, and all others False. The are no FK's or anything else depending on this constraint. I know I can accomplish it with plpgsql, but this seems like a sledgehammer. I would prefer something like a CHECK or UNIQUE constraint. The simpler the better.
One row must be True, they cannot all be False (so the first row inserted would have to be True).
The row will need to be updated, which means I have to wait to check constraints until updates are done, since all rows may be set False first and one row True afterwards.
There is a FK between products.tax_rate_id and tax_rate.id, but it has nothing to do with the default or standard tax rate, which is user selectable to ease creating new products..
PostgreSQL 9.5 if it matters.
Background
The table is the tax rate. One of the tax rates is the default (standard since default is a Postgres command). When a new product is added, the standard tax rate is applied to the product. If there is no standard, the database must either do a guess or all kinds of unneeded checks. The simple solution, I thought, was to make sure there is a standard.
By "default" above, I mean for the presentation layer (UI). There is a user option for changing the default tax rate. I either need to add extra checks to ensure the GUI/user does not try to set the tax_rate_id to NULL, or then just set a default tax rate.


texts: Identity proceeds as normal, but with a leading 10...?

texts:
So I noticed a very strange thing when tinkering with my database.

I had just inserted a row which was ID 47, then after inserting some more, I realised that somehow my Identity had skipped all the way to 146, so it had skipped 999 to get there.

It's not a huge problem, or a gamestopper, just very strange, is there any way I can solve this? Short of backing up the data and recreating the table.

For the record, I didn't do anything at all between the data insertion - it seems to be totally random (of course it's not, something caused it I'm sure...)


texts: moving table to another tablespace in primary server oracle 11g

texts:
What will be the effect on dataguard configuration when moving one table to another tablespace using the impdp remap_tablespace parameter?

In our production environment we have setup dataguard and I am curious that if move a table in primary what will be effect on dataguard or what steps should I do before the moving table.


texts: How to COUNT co-tags from a tag_map table in mysql?

texts:
I have a simple tag_map table

CREATE TABLE TagMap
(
TagID mediumint(7) unsigned,
ArticleID int(11) unsigned,
FOREIGN KEY(TagID) REFERENCES Tags(TagID) ON DELETE CASCADE,
FOREIGN KEY(ArticleID) REFERENCES Articles(ArticleID) ON DELETE CASCADE,
PRIMARY KEY(TagID,ArticleID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci KEY_BLOCK_SIZE=1

I get the co-tags (tags, which appear along with a specific tag):

SELECT TagID AS TagID2,COUNT(*) FROM TagMap WHERE ArticleID IN(
SELECT ArticleID FROM TagMap WHERE TagID=1 // This is TagID1
)
GROUP BY TagID

How can I do this query for all tags to get

TagID1,TagID2,COUNT(*)

The table is huge (10-50M rows) and each article has tens of tags. Thus, performance is critical.


texts: SQL Server transactional replication distributor

texts:
If I have a SQL Server 2012, with two databases, both publishers, replicating to a bunch of subscribers.

Can each database go through a different distributor?
Is the publisher the server or the database?

I understood it to be a publisher can only go to one distributor

Is that each db or the whole server?


texts: MySQL, many writes waiting on disk i/o access

texts:
I'm seeing a disk i/o problem with a MySQL DB under the following scenario:

24 CPU's total on the computer
22 threads writing simultaneously to separate tables
each write is done using INSERT INTO ... VALUES ..., where there can be ~5000 rows being inserted in a single insert statement
each row's input VALUES are all integers, looking something like this, for example: (13953,195,-149)
tables are MyISAM

The problem is that when all 22 threads are each busy writing the large bulk insert operation, MySQL reports that they are all stuck in state 'closing tables', which equates to flushing the data to disk.

My first analysis found that jdb2 process was most likely causing a bottleneck since the MySQL data directory was under the / partition and journaling was, of course, turned on.

I read that for MySQL data directories this isn't necessary, so I repartitioned the disk, moved the MySQL data directory over to the new partition, turned off journaling for this new partition, and restarted the system.

This resulted in an improvement where the 22 threads were able to complete with no i/o wait and no getting stuck on closing tables, but only for a while, when all threads again end up waiting on 'closing tables'.

mysqladmin processlist, for example, has this for output (cut to save space), where the bottleneck is quite obvious:
processlist output

And then when I check to which process is holding up disk i/o, i find it to be:
[kworker/u65:3]

Which is a kernel process worker.  google helped me little in identifying what this is.

So, my questions:

What is this kernel worker thread causing a disk i/o bottleneck, even when journaling is turned off?
If I were to switch the table type to InnoDB, and change the disk partition to raw (with journaling also off, of course), would this take kernel threads out of the picture?
And any other, general, recommendations to remove this bottleneck?

some additional info, per rick james' request:

1) show create table output:

'CREATE TABLE `psd1` (
  `psd_fk` int(10) unsigned NOT NULL,
  `perbin_fk` mediumint(8) unsigned NOT NULL,
  `power` smallint(6) NOT NULL,
  KEY `psd_fk` (`psd_fk`),
  KEY `perpow` (`perbin_fk`,`power`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs'

2) tables are not bigger than RAM, but there are thousands of tables, possibly hundreds of Mb's each.
3) RAM => 32Gb.
4) rows are ordered by 1st column: which is a foreign key (just my own, not a formal MySQL foreign key) to a primary key of another table.

thanks...


texts:
Possible Duplicate:
  Must-read Books for DBA

I'm already reading a book on Introduction to SQL (RDMS). But that only teaches SQL and commands and I feel like my knowledge lacks in database management. Can you recommend me a good book to Database management which teaches about managing the databases instead of just using the queries to get something done?


texts: Word for describing how normal (technical term) a database is

texts:
Database Normalization is the process of changing the structure of a formal database schema in order to minimize redundancy.

Database theory describes some states of normalization in order to make promises about the nature of queries on them - these are called Normal Forms, often abbreviated 1NF, 2NF, 3NF (and less commonly, 4NF, 5NF, 6NF).

I'm looking for a correct (grammatically and technically) term for describing how normal a database is - something like:

Implications on Database Normality


texts:
We are migrating 3 slightly different SQL databases into one (SQL Server 2008R2). We want to keep old databases "alive" for some period of time but once we've gone live we don’t want staff accidentally going into old database, so taking the database offline for awhile then drop it would be the solution? taking backups for it and test it in another serve prior to do this of course.

I’m not sure what best practice is for making archives, but suspect that making SQL server backups and placing on tape may be a solution.  Do you know if SQL Server backups are compressed and can be saved to tape?  There may be better solutions than tape these days, but I think tape is still the standard.

Thanks for any advice


texts: No data inserted with this WHERE NOT EXISTS expression?

texts:
I want to move data from MySQL to PostgreSQL. I am using mysql.connector and psycopg2 for querying the two databases. When I run WHERE NOT EXISTS for my PostgreSQL DB, it's doesn't insert the data from MySQL.

The subject table doesn't have a unique column so I am using WHERE NOT EXISTS instead of ON CONFLICT.

curr_msql.execute('''SELECT scode, sname sunits FROM subject_table''')

 for row in curr_msql:
            curr_pgsql.execute('''INSERT INTO subject (created, modified, code, name, units)
            SELECT current_timestamp, current_timestamp, %s, %s, %s
            WHERE NOT EXISTS(SELECT code FROM subject WHERE code = code)
            ''', (row['scode'], row['sname'], row['sunits']))

I am not getting any error when running the query but the data are not getting inserted.


texts: How do I list or search all the column names in my database?

texts:
I want to search for a string in the names of the columns present in a database.

I’m working on a maintenance project and some of the databases I deal with have more than 150 tables, so I'm looking for a quick way to do this.

What do you recommend?


texts: From where does the MySQL Query Optimizer read index statistics?

texts: I'm trying to determine from where the MySQL optimizer obtains the list of indexes that are available for a table when it estimates the cost of (prepares) a query from.


texts: SQL Server 2012 - Frequent SELECT and UPDATE on bit column. Index doesn't help

texts:
I have a SQL Server table with about 2 million rows in, this table stores short textual documents. Here's my schema:

CREATE TABLE Documents (
    documentId bigint IDENTITY NOT NULL,
    content nvarchar(MAX) NOT NULL,
    isIndexed bit NOT NULL
)

I have a separate Lucene-based indexer process that does this:

SELECT TOP 1 documentId, content FROM Documents WHERE isIndexed = 0

It then performs the indexing operation before executing this:

UPDATE Documents SET isIndexed = 1 WHERE documentId = @documentId

Initially this worked fine. When every document in the table was not indexed (i.e. all isIndexed values were 0) then each retrieval took about 5ms.

However as more and more documents were indexed then the retrieval time slowly increased. Currently it's about 150ms - a 30 time speed reduction. I note that the UPDATE statement always seems to run in under 2ms, so I know that's not a problem.

From the start the table has always had a non-clustered index on the isIndexed column, but the Actual Execution Plan shows the SQL Server uses an Index Scan (rather than Seek).

What can I do to speed up the system?

I am aware that the "isIndexed" column existing is bad in itself, but due to how the indexer works it can't request documents by documentId directly. For this and other reasons I can't accept any answers that don't solve the immediate problem at hand.


texts: I have uninstalled the database but it's instance is still available in SQL server 2005.I want to remove that instance.Is any possibilities to remove the instance while uninstall a database?


texts: Changing a certifcate+key algorithm by dropping & recreating with the same names

texts:
We have a situation where a symmetric key has been created (a long time ago) using TRIPLE_DES.  This is used to encrypt a password column, and is used by around ten stored procedures (that utilizes these with OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE MyCertificate01;)

I want to replace the certificate and key (to offer a modicum of future-proofing) with AES_192.  However, I'd prefer to avoid editing the stored procedures if possible.

I wrote the following routine that:

Unencrypts the password column into a plain text field
Drops the existing key and certificate
Creates a new certificate and key with the same names as above
Re-encrypts the plain text column using the new cert/key

My question is, to the trained-eye, is there anything here that sets off alarm bells, particularly because the old and new names are the same, and that these are used within stored procedures?

I've tested the code and it worked, but my lack of experience with encryption and a dose of information-overload today makes me very nervous, hence the plea for expertise...

-- Decrypt data

ALTER TABLE [dbo].[tbl_Users] ADD PasswordClear nvarchar(250) NULL;
GO

OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE MyCertificate01;
 UPDATE [tbl_Users] SET PasswordClear = CONVERT(nvarchar(250), DECRYPTBYKEY(PasswordEnc))
CLOSE SYMMETRIC KEY SSN_Key_01;
GO

-- Drop old encryption

ALTER TABLE [dbo].[tbl_Users] DROP COLUMN PasswordEnc;
GO

DROP SYMMETRIC KEY SSN_Key_01;
GO

DROP CERTIFICATE [MyCertificate01];
GO

-- Create new certificate and key

CREATE CERTIFICATE MyCertificate01 WITH SUBJECT = 'My Certificate 01';
GO

CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = AES_192 ENCRYPTION BY CERTIFICATE MyCertificate01;
GO

ALTER TABLE [dbo].[tbl_Users] ADD PasswordEnc varbinary(256) NULL;
GO

OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE MyCertificate01;
 UPDATE [tbl_Users] SET PasswordEnc = ENCRYPTBYKEY(Key_GUID('SSN_Key_01'), PasswordClear);
CLOSE SYMMETRIC KEY SSN_Key_01;
GO

ALTER TABLE [dbo].[tbl_Users] DROP COLUMN PasswordClear;
GO

ALTER TABLE [dbo].[tbl_Users] ALTER COLUMN PasswordEnc varbinary(256) NOT NULL;
GO


texts: Understanding datetime formats in SQL Server

texts:
I've dealt with MS SQL Server datetime types for a long time but never thought why the following is happening:

I query a table that contains a smalldatetime column. This smalldatetime is always returned in the format yyyy-MM-dd hh:mm:ss
Now I write a different query on which I want to apply a smalldatetime filter in the WHERE clause, something like WHERE TimeStamp >= 'yyyy-MM-dd hh:mm:ss'
SQL Server retrieves an error and tells me that was not possible to convert that nvarchar to a valid smalldatetime

It appears that it only works if I change the specified format and I write it using the european format, like WHERE TimeStamp >= 'dd-MM-yyyy hh:mm:ss'.
Why is SQL Server showing me the dates in a format that is not covertable or valid when applied back to itself?
I don't have any problem in changing the date format when writing queries, but I want to play with these dates at an application level (Java-JDBC app) and I don't want to be applying date format changes all the time...
Could anyone explain me why this is happening and if there is any way to solve it at a DB level?
Thanks!!

Edit: Please see the screenshot of the error in Management Studio below.


texts: How can i prevent decryption in SQL Server procedures

texts:
I came across in internet tool to decrypt procedures, I checked my database with this tool to decrypt my procedures and it works fine, now the problem is I wish to prevent this kind of tools from decryption of my procedures.

Any methods to prevent this kind of tool from decryption of my procedures?


texts: Are unfixable spatial index corruptions considered normal?

texts:
I have a spatial index for which DBCC CHECKDB reports corruptions:

DBCC CHECKDB(MyDB)
WITH EXTENDED_LOGICAL_CHECKS, DATA_PURITY, NO_INFOMSGS, ALL_ERRORMSGS, TABLERESULTS

  The spatial index, XML index or indexed view 'sys.extended_index_xxx_384000' (object ID xxx) does not contain all rows that the view definition produces.  This does not necessarily represent an integrity issue with the data in this database.

  The spatial index, XML index or indexed view 'sys.extended_index_xxx_384000' (object ID xxx) contains rows that were not produced by the view definition.  This does not necessarily represent an integrity issue with the data in this database.

  CHECKDB found 0 allocation errors and 2 consistency errors in table 'sys.extended_index_xxx_384000' (object ID xxx).

Repair level is repair_rebuild.

Dropping and recreating the index does not remove these corruption reports. Without EXTENDED_LOGICAL_CHECKS but with DATA_PURITY the error is not reported.

Also, CHECKTABLE takes 45 minutes for this table although its CI is 30 MB in size and there are about 30k rows. All data in that table is point geographydata.

Is this behavior expected under any circumstances? It says "This does not necessarily represent an integrity issue". What am I supposed to do? CHECKDB is failing which is a problem.

This script reproduces the issue:

CREATE TABLE dbo.Cities(
    ID int  NOT NULL,
    Position geography NULL,
 CONSTRAINT PK_Cities 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
INSERT dbo.Cities (ID, Position) VALUES (20171, 0xE6100000010C4E2B85402E424A40A07312A518C72A40)
GO
CREATE SPATIAL INDEX IX_Cities_Position ON dbo.Cities
(
    Position
)USING  GEOGRAPHY_AUTO_GRID
WITH (
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

This is version 12.0.4427.24 (SQL Server 2014 SP1 CU3).

I scripted the table with schema and data, fresh DB, execute. Same error. CHECKDB also has this incredible runtime of 45min. I captured the CHECKDB query plan using SQL Profiler. It has a misguided loop join apparently causing excessive runtime. The plan has quadratic runtime in the number of rows of the table! Doubly nested scanning loop joins.

Clearing all non-spatial indexes does not change anything.


texts: What does the icon of a server with a blue database symbol mean?


texts: How many users are supported on SQL Server 2012 Express?

texts:
I would like to say that i have gone through some of the sql blog and MSDN BOL, but still there is some confusion in mind like.

The limitation of SQL Server 2012 Express is 10 GB. It is the limitation of instance based or per database based?
No. of connections for SQL Server 2012 Express: the number of connections is limited or unlimited.

If I shall use Server version of OS then what can be the max concurrent user connections.
The limitation comes from DB level or OS level.
If I shall use Windows Server 2012 R2 with SQL Server 2012 Express SP1 then what will be the max concurrent connections?
How many databases can we keep under the hood of single SQL Server instance.

Any help or suggestion would be appreciated


texts: SSIS SQL Server Destination Good or Bad?

texts: Do people still recommend NOT using the SQL Server Destination? I know there have been questions about this in the past, but they seem to be from 4+ years ago. I have been timing OLEDB versus SQL Server Destination for ~5million+ records, the time savings are definitely enough where I need to consider it. The source/target databases and ssis packages are located on the same server.


texts: Understanding Indexes and using them better

texts:
I've been spending some time trying to optimise our MySQL queries that we use which essentially joins one table with 2 million rows onto another which has 1-2 million. I know MySQL is able to easily handle this, however we use a lot of GROUP BY clauses and pseudo tables to reduce our data to build our results.

It takes around 152 seconds to do this on the majority of results, but when we change one clause value, it eats up all of the space on the server and complains about some missing files.

After using EXPLAIN on our query, I found out that we were accessing over a trillion (yes...) rows. I've now reduced this to 655 billion. Our tables contain complex data.

I've been through our tables and really dug into the data to understand what field types we should be using, finding the maximum length of our columns and changing the structure to better fit. Also I've began making use of proper INT, TINYINT and SMALLINT field types.

Bearing in mind that it's extremely likely that these tables containing 1-2 million rows will soon jump to 2/3 times that amount, please help me answer these questions:

Does setting a column to allow NULL affect the indexing?
How & when should I create covering indexes? I was once told that data of similar values should be indexed into one index key. For example, dates, numbers etc. If not, what parameters should I use to define when to create a covering key?
We run our server in a virtual machine. It's got 200GB of HDD space, 64GB of RAM. We only use InnoDB. We write millions of rows each day, then delete it out, and rewrite it. Once the data has been written we then need to join it up and build CSV files from it. What kind of my.cnf settings should we be using to create this balance of read/write? Here is our current configuration file contents.

Any and all help would be much appreciated!


texts: What is the best practice for treating fields with only two possible values?

texts: A column/field will only have 2 possible values, either "Transplanted" or "Direct-seeding". Is it better to create a table for these options and give them unique IDs or are they better off put as raw string values in the table?


texts: Scaling mongo db shard set up at run time in heavy load condition

texts:
I am running a mongo sharded set up where page id is shard key.
Site has almost 200k different pages which are accessed by millions of users.
Situation is when at some time some of page related to particular event start getting too many hits.
Now to scale horizontally i need to add new shard but as users are visiting existing pages data will not be distributed to new shard.
Document structure:
PageId
SiteName
Page access Time
User unique ID

I need to find page views on for each in given time window.
Could you please suggest how this system will scale.

Thanks
Virendra Agarwal