Most Popular
1500 questions
43
votes
3 answers
Optimize a query that's running slow with nested loops inner ioin
Consider the following simple query (only 3 tables involved)
SELECT
l.sku_id AS ProductId,
l.is_primary AS IsPrimary,
v1.category_name AS Category1,
v2.category_name AS Category2,
v3.category_name AS…
Luis Ferrao
- 545
- 1
- 4
- 7
43
votes
2 answers
Most efficient way of bulk deleting rows from postgres
I'm wondering what the most efficient way would be to delete large numbers of rows from PostgreSQL, this process would be part of a recurring task every day to bulk import data (a delta of insertions + deletions) into a table. There could be…
tarnfeld
- 619
- 2
- 6
- 7
43
votes
3 answers
Parameter Sniffing vs VARIABLES vs Recompile vs OPTIMIZE FOR UNKNOWN
So we had a long running proc causing problems this morning (30 sec + run time). We decided to check to see if parameter sniffing was to blame. So, we rewrote the proc and set the incoming parameters to variables so as to defeat parameter…
RThomas
- 3,396
- 5
- 27
- 47
43
votes
1 answer
Transform all columns records to lowercase
I'm using PostgreSQL 9.1 and I have a users table with a login column.
login names are case-sensitive, for example Bob, MikE, john. I would like to transform all these records into lowercase. How can I do that?
flyer88
- 607
- 2
- 6
- 7
43
votes
9 answers
MySQL DB import/export command line in Windows
How do I import and export a local instance of a MySQL database via command-line interface?
My operating system is Windows and WampServer server is installed.
Bharanikumar
- 539
- 1
- 4
- 5
43
votes
7 answers
What's a good use case for SELECT * in production code?
Out of habit, I never use SELECT * in production code (I only use it with ad-hoc scrap queries, typically when learning the schema of an object).
But I ran across a case now where I'm tempted to use it but would feel cheap if I did.
My use case is…
J.D.
- 37,483
- 8
- 54
- 121
43
votes
4 answers
Using column alias in a WHERE clause doesn't work
Given a table users with two fields: id and email.
select id, email as electronic_mail
from (
select id, email
from users
) t
where electronic_mail = ''
Postgres complains that:
ERROR: column "electronic_mail" does not…
Victor
- 567
- 1
- 5
- 10
43
votes
4 answers
SQL Server "Suspect" database?
What do you do when you have a database that is marked as Suspect?
Restore from the last backup?
Please advise.
db7
- 1,371
- 4
- 16
- 20
43
votes
2 answers
Why does changing the declared join column order introduce a sort?
I have two tables with identically named, typed, and indexed key columns. One of the them has a unique clustered index, the other one has a non-unique.
The test setup
Setup script, including some realistic statistics:
DROP TABLE IF EXISTS…
Daniel Hutmacher
- 9,113
- 1
- 25
- 52
43
votes
3 answers
Is it possible to use SQLite as a client-server database?
Are there any techniques or tools to work with SQLite on a medium size/traffic/concurrency DB environment?
Maniero
- 2,728
- 6
- 28
- 29
43
votes
6 answers
PostgreSQL: Force data into memory
Is there a systematic way to force PostgreSQL to load a specific table into memory, or at least read it from disk so that it will be cached by the system?
Adam Matan
- 11,659
- 29
- 80
- 95
43
votes
2 answers
What is a WITH CHECK CHECK CONSTRAINT?
I have some auto-generated T-SQL, which is probably valid, but I don't really understand.
ALTER TABLE [dbo].[MyTable]
WITH CHECK
CHECK CONSTRAINT [My_FORIEGN_KEY];
I know what a foreign key constraint is, but what's the CHECK CHECK?
BanksySan
- 991
- 1
- 11
- 16
43
votes
3 answers
What does the word "SARGable" really mean?
SQL Server users use the term "sargable". I'm wondering if there is an objective implementation-agnostic timeless definition for "sargable."
For instance, WHERE foo LIKE '%bar%' is said by many to be not sargable, but some RDBMSs are able to use…
Evan Carroll
- 63,051
- 46
- 242
- 479
43
votes
1 answer
How long is "too long" for MySQL Connections to sleep?
I logged onto my server to check the status of the database and noticed on the SHOW PROCESSLIST; that there are a large number of connections sleeping that are very old.
What duration should the connection time limit be before the connection should…
Patrick
- 4,229
- 7
- 28
- 28
43
votes
4 answers
Table-Valued Parameter as Output parameter for stored procedure
Is it possibile to Table-Valued parameter be used as output param for stored procedure ?
Here is, what I want to do in code
/*First I create MY type */
CREATE TYPE typ_test AS TABLE
(
id int not null
,name varchar(50) not null
,value…
adopilot
- 2,423
- 8
- 31
- 46