Questions tagged [cross-apply]
42 questions
44
votes
2 answers
Outer Apply vs Left Join Performance
I am Using SQL SERVER 2008 R2
I just came across APPLY in SQL and loved how it solves query problems for so many cases,
Many of the tables I was using 2 left join to get the result, I was able to get in 1 outer apply.
I have small amount of data in…
Pratyush Dhanuka
- 559
- 1
- 5
- 9
19
votes
1 answer
CROSS APPLY produces outer join
In answer to SQL counting distinct over partition Erik Darling posted this code to work around for the lack of COUNT(DISTINCT) OVER ():
SELECT *
FROM #MyTable AS mt
CROSS APPLY ( SELECT COUNT(DISTINCT mt2.Col_B) AS dc
…
Paul White
- 94,921
- 30
- 437
- 687
11
votes
1 answer
Apply cardinality estimation problem in SQL Server
Now, I am faced with the problem of the logic of cardinality estimation that is not quite clear for me in a seemingly rather simple situation.
I encountered this situation at my work, therefore, for privacy reasons, I will provide only a general…
Павел Ковалёв
- 194
- 9
10
votes
2 answers
Microsoft SQL Server 2014 Nested From Query in Cross-Apply
When selecting from a nested query in an OUTER APPLY statement the nested query seems to be evaluated only once in certain circumstances.
Bug reported to Azure Feedback Forum:…
Phist0ne
- 103
- 5
9
votes
3 answers
No advantage of using Cross Apply or CTE over inline sub-query
I came across a query like this:
SELECT (SELECT COUNT(1) FROM Orders o WHERE i.ItemId = o.ItemId) [C]
FROM Items i
I changed it to following
;WITH cte_count
AS
(
SELECT COUNT(1) c, OrderId FROM Orders Group By ItemId
)
SELECT a.c [Count],…
TheVillageIdiot
- 297
- 1
- 4
- 8
8
votes
2 answers
CROSS APPLY on Scalar function
I have this:
SELECT
A
,B
,dbo.Func(C)
,dbo.Func(D)
,dbo.Func(E)
,F
FROM abcdef
WHERE
0 = dbo.Func(C) + dbo.Func(D)
I have read that this is not good practice because the function is called million of times and it has a bad impact on performance.
I…
Muflix
- 1,099
- 5
- 15
- 27
8
votes
2 answers
Most efficient way to generate a diff
I have a table in SQL server that looks like this:
Id |Version |Name |date |fieldA |fieldB ..|fieldZ
1 |1 |Foo |20120101|23 | ..|25334123
2 |2 |Foo |20120101|23 |NULL ..|NULL
3 |2 …
ame
- 83
- 5
7
votes
2 answers
Optimize extraction of json data via OPENJSON
I'm attempting to optimize extraction of values obtained from a REST API which returns json values in an array.
Here's an minimal, complete, and verifiable example that reflects exactly what I'm doing.
USE tempdb;
DROP TABLE IF EXISTS…
Hannah Vernon
- 70,928
- 22
- 177
- 323
7
votes
1 answer
How to get cross apply to operate row by row on a view?
We have a view that is optimized for single item queries (200ms no parallelism):
select *
from OptimizedForSingleObjectIdView e2i
where ObjectId = 3374700
It also works on small sets of static ids (~5).
select *
from…
crokusek
- 2,110
- 4
- 25
- 34
7
votes
1 answer
Cross Apply Fails When DateTime Added
In trying to achieve a Pivot-ed goal for a two row data table into one row, my first thought was to use a Cross Apply. By using the Cross Apply one can identify/generate each of the rows with a specific column name derived from the row's unique id.…
ΩmegaMan
- 409
- 1
- 8
- 23
6
votes
1 answer
Is there a more optimal way to create this view or stored procedure? Currently using a cross apply, but it's slow
I've updated this post with some test data.
I'm creating a report for my Movies database where I'd like for the end-user to be able to select movies of a certain genre. However, some movies have multiple genres and I've normalized the database so…
ck123
- 61
- 4
6
votes
3 answers
Using CROSS APPLY OPENJSON causes Azure to hang
I have a table with around 8 million rows with a schema of:
CREATE TABLE [dbo].[Documents](
[Id] [uniqueidentifier] NOT NULL,
[RemoteId] [int] NOT NULL,
[Json] [nvarchar](max) NULL,
[WasSuccessful] [bit] NOT NULL,
[StatusCode]…
Darren
- 175
- 1
- 7
6
votes
3 answers
How does this derived table with cross apply work?
I borrowed some code on how to compact and uncompact a string of diagnosis codes in order to build a bridge table for my data warehouse. It works well. However, I just do not understand how it is doing the uncompacting. Here Is the SQL Fiddle for…
Anthony Genovese
- 2,067
- 3
- 22
- 34
5
votes
1 answer
Build a three table join with a recusive table in the middle?
I have three relevant tables: Parts, PartGroup, and MarkupGroup.
Parts is simple.
PartID artificial primary key
Part part number
PartGroupID Foreign key
sample data:
1 T1000 5
2 wizbang gold 17
3 flux…
Jeff Sacksteder
- 1,317
- 2
- 19
- 30
5
votes
1 answer
Using CROSS APPLY with GROUP BY and TOP 1 with duplicate data
I have a table that contains Status information about Product items over time. Each row has a Modified DATETIME. I want to get the latest Status row, using the MODIFIED field, for each ProductNumber in one query. However the crux is that the…
Keegan
- 53
- 1
- 1
- 3