1
vote
1answer
96 views

Complex “activity” based query optimization

I'm working on a query in the same style as Facebook's feed on a table that currently has 500,000 rows. For now I am just looking to get the 20 newest items (new records created by a user's followees,...
0
votes
0answers
2k views

SQLite UPDATE two tables from VIEW or TEMPORARY TABLE

Here is the query that doesn't compile just to show how it should look like. But since I can't do Multiple table UPDATE's. This fake query should be able to run the inner UPDATE's for both tables if ...
0
votes
1answer
781 views

Shopping cart schema with multiple product tables

I have a shopping cart table used to store customer id and product id of their chosen items. However I have multiple product tables, for example cars, books, houses. I started out with one table but ...
4
votes
1answer
382 views

Why clustered index scan?

I just started to learn to optimize my queries and to analyze their query plans. I thought this query would generate nonclustered index seek + key lookup. SELECT ct.* FROM Person.ContactType AS ct ...
1
vote
0answers
48 views

Data Integration [closed]

This is not exactly a DBA question but definitely a DB question and I think StackExchange DBA is my best bet for this question. Here I go : are there any frameworks ( free / open source preferred ) ...
1
vote
1answer
486 views

SQL Server Reporting Services: How to check who accessed certain report?

I am in process of eliminating who should have access to SQL Server Reporting Services reports that I have created. Is there a way to find out who has accessed certain reports during past month? My ...
1
vote
1answer
2k views

Using OFFSET in a large table by a simple sub-query

The slow performance for using OFFSET in large table has been widely discussed in various blogs, and the most efficient way is to use an INNER JOIN as SELECT * FROM table INNER JOIN ( SELECT id FROM ...
0
votes
0answers
396 views

Replication Monitor Missing Subscriptions in Watch List

In Replication Monitor, for one of our publications (transactional replication), none of the subscriptions are showing up in the Subscription Watch List. This is new behavior that just started ...
1
vote
1answer
66 views

Formulating a Join Query for PostqreSQL

I have a quick question, if someone can help, I would be grateful I have the following tables User, Video, User_video user has userdetails (user_id, name, email) video has videoDetails (...
1
vote
1answer
179 views

Can one part of a multi-column primary key be used across tables?

I want to use SQLite as file format for my app, a reporting program. Below is an idea of the kind of schema I'll use (just the first few tables). There will be one report per file and all tables are ...
4
votes
1answer
3k views

SQL Server SSL connection - What am I missing?

I am trying to set up my SQL Server 2008 R2 instance so that it only accepts SSL connections. My understanding is that once this is set up, any client machine should need to have a certificate ...
2
votes
0answers
2k views

MySQL Queries taking too long to execute

I have a database table, with 300,000 rows and 113.7 MB in size. I have my database running on Ubuntu 13.10 with 8 Cores and 8GB of RAM. As things are now, the MySQL server uses up an average of 750% ...
6
votes
2answers
3k views

Mirroring in SQL Server 2012

I've got newly installed Win 2008 R2 machines, in a workgroup, in the same subnet, and of course SQL Server 2012. I allowed login with SQL Server authentication, thinking it may help with each ...
8
votes
2answers
4k views

What are the possible causes for sp_reset_connection taking a long time to execute?

Why would the sp_reset_connection system stored procedure be taking longer than a few milliseconds to execute, as viewed through SQL Server Profiler? I took a simple trace from a production system ...
6
votes
1answer
365 views

SQL Server 2008R2 minimum RAID/Disk config

I want to take advantage of the great price/performance offered by the new Intel E5-2620 (v2) machines being offered by some dedicated server webhosts. Unfortunately most of these E5-2620v2 dedicated ...
1
vote
1answer
354 views

Where can I check my permissions as SQL Server user?

Everytime I have to do a Restore from a backup, I have to log out from my personal user account from the SQL Server instanc and log back in using our main group user account, which I manage it too as ...
1
vote
0answers
175 views

join performance, index vs. index+foreignkey

I'm using an orm framework that handles data (and its integrity) on top of mysql. So far I ignored foreign keys because the orm handles that just fine. I recently read that foreign keys can enhance ...
1
vote
1answer
53 views

Moving large database to another drive, how to measure performance before, and setup some type of replication for fast fail back?

Sorry if this is kind of more than question at a time, but here goes. Running SQL Server 2008 on windows server 2008 as well. We have a large database just over 200gb right now (it holds images of ...
3
votes
1answer
121 views

Understanding SQL Server plan cache

I've been reading about the SQL Server plan cache and optimizing for adhoc workloads, etc. I'm fairly new to the topic and have a few general questions to help paint a better picture: Suppose I have ...
0
votes
1answer
48 views

How do I create a non localhost connection with MySQL? [closed]

I'm currently hosting a small website on my college domain where people can fill out a form and post it to database. I'm trying to figure out how I can set up/edit my MySQL so that its not localhost. ...
1
vote
1answer
750 views

Drop table without its dependencies and solve block corruption

I have found alert about corrupted block in alert log. Corrupted block belonged to the table named DOCS. Because of I don't have any backup of this database, decided to recreate DOCS table to solve ...
6
votes
2answers
527 views

Data Flow Task Produces No Rows with Declare Statement

I have a SSIS package that is doing a simple SELECT statement and then loading into a table. I have anonymized the sql statement which should be painfully obvious when you look at the table name. I ...
1
vote
1answer
703 views

can I Run a Stored Procedure across all SQL servers and return 1 html report

I have the following set up as a sp and it runs daily. It gives me a report for each SQL cluster in my environment but what I'd like to do is configure it to run across all clusters and return 1 ...
0
votes
1answer
55 views

Should the value of a select max be indexed when the where clause is already indexed

I have a customer_orders table in a schema designed to be shared between multiple companies. Each customer_order has an invoice number that should always start at 1 and increment based on company (...
1
vote
1answer
95 views

normalization of data and duplicate rows

All right, this is a hobby question, so nothing critical if I don't get a quick response. But you guys have been so good at helping me, I thought why not chance it again. I am creating a db that can ...
0
votes
1answer
166 views

Select historical prices for an exact datetime - MySQL

I have a price_list table where I store historical prices for each item. columns are - id, name, price, start_date, item_id When a price is changed, a one new row is inserted with a current ...
1
vote
1answer
837 views

How to isolate that network is issue of bad query performance [closed]

I would like to know how to isolate SQL Server bad query performance is caused by NETWORK and not the SQL Server. Are there any specific tips and tricks in SSMS, wait types, or specific PerfMon ...
6
votes
4answers
29k views

List table sizes for all tables on all databases

Is there a simple way of listing the size of every table in every database on a MSSQL server? I have used a query on sys.tables to get results for a single database, but we have >100 databases per ...
1
vote
0answers
98 views

What to look for in query execution plans? [closed]

Assuming the query execution is slow, I have collected the xml plan. What should I look for in the actual execution plan. What are the top 10 things I need to watch out for? I know this is a broad ...
0
votes
0answers
114 views

Select rows in random order from a huge MySQL database

I have a problem.. I would like to take 1000 rows in random order from a database that contains about 1.5 billion rows and increasing daily. I understood that using RAND is not that good idea because ...
4
votes
2answers
2k views

Set random value from set

I need to put some random values into database, but I don't want to end up with completely randomized text (like 7hfg43d3). Instead I would like to randomly pick one of values supplied by myself.
0
votes
2answers
2k views

Massive MySQL delete from CSV file

To insert massive CSV file into MySQL, there is LOAD DATA INFILE, but is there any similar command to delete massive entries with keys stored in a massive CSV file? If not, what would you see as the ...
1
vote
1answer
9k views

How to grant permission for creating,reading and executing symmetric key in sql server 2008

We have user associated to a schema with limited privileges.During run time we are generating symmetric key using same user log-in.since the user has limited privilege, we are not able to create key ...
3
votes
2answers
2k views

Missing Transaction log in chain, Possible to skip

We have had one of our clients SQL Servers crash and need to restore it to the most recent possible time. We have a full backup from Monday and transaction logs that run every hour up until this ...
1
vote
1answer
603 views

What happens when the index size of my Mysql instance exceeds the innodb_buffer_pool_size?

I am using Amazon's RDS db.m1.small. innodb_buffer_pool_size is set to 1125.00 MB. Current index size is 1110.00 MB and growing slowly but steadily. I do not need more hard drive space. I want ...
1
vote
1answer
74 views

RMAN backup copy - read once and write many?

I am trying to find out what happens in this scenario: BACKUP COPIES 2 What actually happens in the BACKUP COPIES above? Does Oracle RMAN read the data files each time for each separate copy? ...
0
votes
1answer
151 views

Postgres cannot rename table because of AccessExcusiveLock

I'm trying to rename table. But it had error: LOG: sending cancel to blocking autovacuum PID 11911 Detail: Process 10377 waits for AccessExclusiveLock on database 21819191 relation 24338310 ...
4
votes
2answers
7k views

How can I prevent so many aborted clients in MySQL (increasing aborted_clients count)?

I am running MySQL 5.6.14 on an Ubuntu server and noticed the error log constantly filling with: Aborted connection 17201 to db: 'somedb' user: 'sdb' host: 'x.x.x.x' (Got timeout reading ...
-1
votes
2answers
3k views

Write filename, servername/IP and date+time in spool file

I'm having some problems to verify the installation/execution of some oracle scripts in different production servers. I've created a launcher script which executes all the sql scripts in sqlplus and ...
1
vote
1answer
170 views

Having trouble with a query. DBMS Assignment [closed]

Working on an assignment for school, was able to figure out all but this one. I need to display the employee name and employee identification number along with their HOD's name and identification ...
4
votes
1answer
2k views

SQL Server 2008 R2 Upgrade (Express to Standard) fails. No “Select Instance”

Hello there, i got stuck in the progress of upgrading 3 systems from SQL Server 2008 R2 Express Edition --> SQL Server 2008 R2 Standard Edition Problem: The Option "Select Instance" in the ...
0
votes
1answer
315 views

PL/SQL Developer - empty object list

I'm using PL/SQL Developer v9, brio intelligence 6.6 and on another computer SQL Developer v7. On version 7 everything is ok. My table objects are 'expanding' shows all avaiable databases. In ...
3
votes
1answer
6k views

How can I view special characters in SQL Server 2012 Management Studio grid view?

In a SQL Server 2012 database, I have a log table where I log SQL queries executed by a VB.NET application. The field is defined as nvarchar(MAX) In Microsoft SQL Server Management Studio, when I ...
4
votes
2answers
3k views

How can I change the number of @schedule_uid=N'????' to re-create a SQL Server Agent Job?

I have a SQL Server 2005 job in the SQL-Server Agent Jobs that I need to move/transfer to another updated SQL Server 2008 R2 instance. I was able to script it out and then use it as a new query window ...
-1
votes
2answers
6k views

Query to compare two rows in MySQL and make an UPDATE

I have a table (MySQL) like this: ID | AnotherID | Number1 | Number2 | Data 1 A 101 199 Lorem 2 A 102 ...
1
vote
1answer
1k views

Does the recovery model affect query speed?

Would using the Full Recovery model make something like a long running DELETE operation slower than it would be under the Simple Recovery model?
1
vote
1answer
10k views

Lazy Writes Performance Issues

My SQL Server sometimes reports high Lazy Writes e.g. "Lazy Writes per second is 119 Writes/sec" What should I be checking to find out why this is high? What are potential causes? Thanks.
0
votes
1answer
53 views

Is it safe to use “NOT BLOCKING” option on HSQL backup?

The HSQL documentation has the following to say about the "NOT BLOCKING" option on backups: Hot backup is performed if NOT BLOCKING is specified. In this mode, the database can be used during ...
2
votes
1answer
95 views

False positive error message in MS Access (concurrent users)

I have discovered a strange apparent bug in MS Access and would like to know if others know it already and hopefully how to get around it. Trying to count records based on known column categories. ...
0
votes
1answer
39 views

How to specify the primary key number space for a postgres table?

I have a postgres table that contains data where the id has been set by the code that injected the data. So lets say the id is 0..49. How can I instruct postgres to start with 50 on the id column ...

15 30 50 per page