Most Popular
1500 questions
21
votes
7 answers
Efficiently select beginning and end of multiple contiguous ranges in Postgresql query
I've got about a billion rows of data in a table with a name and an integer in the range 1-288. For a given name, every int is unique, and not every possible integer in the range is present--so there are gaps.
This query generates an example…
Stew
- 493
- 2
- 5
- 9
21
votes
4 answers
SQL Server cardinality hint
Is there a way how to 'inject' a cardinality estimation to a SQL Server optimizer (any version)?
i.e. something similar to Oracle's cardinality hint.
My motivation is driven by the article, How Good Are Query Optimizers, Really?[1], where they…
Radim Bača
- 233
- 2
- 8
21
votes
1 answer
Querying non-ASCII rows from Postgres
Does [:ascii:] class work in Postgres at all? It is not listed in their help, however I see examples in the web which utilize it.
I have a UTF-8 database, where collation and c_type are en_US.UTF-8, and Postgres version is 9.6.2.
When I search for…
Suncatcher
- 377
- 2
- 4
- 12
21
votes
6 answers
How to create a row for every day in a date range using a stored procedure?
I would like to create a stored procedure that will create a row in a table for every day in a given date range. The Stored Procedure accepts two inputs - A start date and end date of the date range desired by the user.
So, let's say I have a table…
Rob V
- 375
- 2
- 4
- 8
21
votes
5 answers
What standard should I follow when naming tables and views?
What standard should I follow when naming tables and views? For instance, is it a good idea to put something like tbl_ at the beginning of table names? Should I designate code/lookup tables in some way like ct_, lut_, or codes_? Are there any other…
Beth Lang
- 952
- 1
- 10
- 19
21
votes
1 answer
Performance comparison between using Join and Window function to get lead and lag values
I have a table with 20M rows, and each row has 3 columns: time, id, and value. For each id and time, there is a value for the status. I want to know the lead and lag values of a certain time for a specific id.
I have used two methods to achieve…
Jason
- 683
- 1
- 12
- 26
21
votes
3 answers
Wrapping query in IF EXISTS makes it very slow
I have the below query :
select databasename
from somedb.dbo.bigtable l where databasename ='someval' and source <>'kt'
and not exists(select 1 from dbo.smalltable c where c.source=l.source)
The above query completes in three seconds.
If the…
TheGameiswar
- 2,999
- 4
- 30
- 50
21
votes
3 answers
Why did Postgres UPDATE take 39 hours?
I have a Postgres table with ~2.1 million rows. I ran the below update on it:
WITH stops AS (
SELECT id,
rank() OVER (ORDER BY offense_timestamp,
defendant_dl,
offense_street_number,
…
Aren Cambre
- 355
- 1
- 3
- 7
21
votes
1 answer
What are the best practices for running SQL Server in a Virtual Machine?
What are the best practices for running SQL Server in a Virtual Machine? My on-line transaction activities are very low, but there is a high amount of data processing for the purpose of providing reporting data to multiple web sites.
JerryOL
- 335
- 1
- 3
- 7
21
votes
1 answer
Use Where Clause With Merge
In the below syntax with the When Matched is it possible to only update if the value in the update table differs from the value in the employee table? Something similar to my below DDL - but of course this throws an error and does not work.
What…
SmallFries BigGuys
- 509
- 5
- 12
- 22
21
votes
3 answers
MySQL - Delete row that has a foreign key constraint which reference to itself
I have a table in which I store all the forum messages posted by the users on my website. The messages hierarchy strucrue is implement using a Nested set model.
The following is a simplified structure of the table:
Id (PRIMARY KEY)
Owner_Id…
Alon Eitan
- 349
- 1
- 4
- 13
21
votes
2 answers
Can you explain this execution plan?
I was researching something else when I came across this thing. I was generating test tables with some data in it and running different queries to find out how different ways to write queries affects execution plan. Here is the script that I used to…
Andrew Savinykh
- 1,325
- 5
- 15
- 28
21
votes
3 answers
How to connect to SQL Server using sqlcmd on Linux?
I have set up the Microsoft ODBC Driver 13 for SQL Server on an Ubuntu 16.04 machine. I am now trying to test a database connection, that includes both the server name and an instance name.
The following both work fine from a Windows…
geographika
- 533
- 2
- 4
- 15
21
votes
5 answers
Why is this explicit cast causing problems only with a Linked Server?
I am querying data from a linked server through a view on the origin server.
The view has to include a couple of standardized columns, such as Created, Modified and Deleted, but in this case the table on the source server doesn't have any suitable…
krystah
- 747
- 9
- 19
21
votes
3 answers
Common Table Expression (CTE) benefits?
From msdn :
Unlike a derived table, a CTE can be self-referencing and can be
referenced multiple times in the same query.
I'm using CTEs quite a lot, but I've never thought deeply about the benefits of using them.
If I reference a CTE multiple…
Royi Namir
- 1,233
- 3
- 13
- 26