Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
38
votes
1 answer

Index optimization with dates

I have a large table of objects (15M+ row) in PostgreSQL 9.0.8, for which I want to query for outdated field. I want to divide the query by millions, for scalability & concurrency purposes, and I want to fetch all data with the updated_at field with…
xlash
  • 561
  • 1
  • 5
  • 6
38
votes
3 answers

For absolute performance, is SUM faster or COUNT?

This relates to counting the number of records that match a certain condition, e.g. invoice amount > $100. I tend to prefer COUNT(CASE WHEN invoice_amount > 100 THEN 1 END) However, this is just as valid SUM(CASE WHEN invoice_amount > 100 THEN 1…
孔夫子
  • 4,330
  • 3
  • 29
  • 49
38
votes
6 answers

Make SqlClient default to ARITHABORT ON

First things first: I'm using MS SQL Server 2008 with a database at compatibility level 80, and connecting to it with .Net's System.Data.SqlClient.SqlConnection. For performance reasons I've created an indexed view. As a result, updates to tables…
Peter Taylor
  • 521
  • 1
  • 4
  • 6
38
votes
2 answers

How (and why) does TOP impact an execution plan?

For a moderately complex query I am trying to optimize, I noticed that removing the TOP n clause changes the execution plan. I would have guessed that when a query includes TOP n the database engine would run the query ignoring the the TOP clause,…
David
  • 483
  • 1
  • 4
  • 6
38
votes
22 answers

SQL Server Management Studio slow opening new windows

After upgrading both SQL Server and Management Studio from 2008 to 2012, Management Studio 2012 is very slow when new query windows and dialog boxes are opened. Even right clicking on tables is slow. There is typically at least a 5 second delay…
David S.
  • 481
  • 1
  • 4
  • 4
38
votes
3 answers

Are composite primary keys bad practice?

I want to know if composite primary keys are bad practice and if not, in which scenarios is their use beneficial? My question is based on this article Note the part about composite primary keys: Bad Practice No. 6: Composite Primary Keys This is…
hackvan
  • 507
  • 1
  • 4
  • 7
38
votes
5 answers

MySQL: How to create Column if not exists?

I am trying to create a Column for my table only if it does not exist. I have researched a lot but I could not find any solution yet. Is this really possible to conditionally create Column ?
zzzzz
  • 483
  • 1
  • 4
  • 5
38
votes
3 answers

Binary path in the pgAdmin preferences

I installed PostgreSQL because I need to restore a large .backup file I received for upload to another platform (it's 38 million rows which was apparently too large for the person to export any other way). When I try to "restore" in pgAdmin 4 I get…
randodatauser
  • 381
  • 1
  • 3
  • 3
38
votes
2 answers

Why is using a table variable more than twice as fast as a #temp table in this specific case?

I was looking at the article here Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance and on SQL Server 2008 was able to reproduce similar results to those shown there for 2005. When executing the stored procedures…
Martin Smith
  • 84,644
  • 15
  • 245
  • 333
38
votes
1 answer

Setting BUFFERCOUNT, BLOCKSIZE, and MAXTRANSFERSIZE for BACKUP command

I am looking for practical guidance for setting the values for the BUFFERCOUNT, BLOCKSIZE, and MAXTRANSFERSIZE of the BACKUP command. I have done a bit of research (see below), I have done a bit of testing, and I am fully aware that any truly…
Solomon Rutzky
  • 68,731
  • 8
  • 152
  • 292
38
votes
1 answer

JSONB with indexing vs. hstore

I am trying to decide on the database design, with as few assumptions (regarding how the web app actually evolves) as possible at this stage. As a first step, understanding that JOINS are expensive, I am considering a small number of monolithic…
ahron
  • 803
  • 1
  • 10
  • 18
37
votes
4 answers

mysql dump import incredibly slow on my developer's machine

I have an SQL dump, it's pretty big (411 MB) and it took 10 minutes to import on server A, the same import on my workstation B has an estimate (pipeviewer) of 8 hours to import (it imported 31 MB in 40 minutes) So this is factor 53 slower. The…
Alex
  • 1,203
  • 1
  • 9
  • 11
37
votes
10 answers

How can I track database dependencies?

As internal applications evolve over a number of years, you occasionally find there are a number of tables that people believe are no longer relevant and want to cull. What are the practical methods for identifying database dependencies, both within…
Rowland Shaw
  • 557
  • 4
  • 11
37
votes
2 answers

How do I drop all constraints from all tables?

I want to drop all default constraints, check constraints, unique constraints, primary keys and foreign keys from all tables in a SQL Server database. I know how to get all the constraint names from sys.objects, but how do I populate the ALTER TABLE…
Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614
37
votes
6 answers

How can I enter Mongo as a superuser or reset users?

I'm was playing around with permissions and locked myself out of Mongo database. I'm pretty sure I did this by trying to explicitly add access to a database but instead I overwrote only allowing permission to the database. So I'm effectively locked…
Tony
  • 579
  • 2
  • 5
  • 7
1 2 3
…
99 100