Questions tagged [cross-apply]
The cross-apply tag has no usage guidance.
43
questions
1
vote
1
answer
54
views
Slow Cross Apply Query Needs Optimization
I have a stored procedure which takes about 4 min to complete. I added some logging and discovered that the reason it is slow is the Cross Apply queries, of which there are 5. Each one takes about 40 ...
- 111
0
votes
0
answers
793
views
How to use column names stored in another table? [duplicate]
I have a few tables in which I store some table names, column names, and some data.
I need to get some column names from the content of these tables, and then use those names to create a bigger query.
...
- 135
3
votes
2
answers
3k
views
Two OPENJSON, return each array element in separate rows
I have a table where I save in JSON format the quantity that exit and return of a product.
The data saved in the two columns out_quantity and in_quantity is in array format.
CREATE TABLE products_info ...
- 33
6
votes
2
answers
2k
views
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....
- 69.1k
5
votes
1
answer
320
views
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 ...
- 51
3
votes
1
answer
718
views
Add query hints to a CROSS APPLY [duplicate]
I need to apply a query hint (like NOWAIT or NOLOCK) to the CROSS APPLY in this query. How do I do it:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT *
FROM sys.objects obj WITH (NOLOCK)
...
- 42.5k
3
votes
1
answer
3k
views
Select with CROSS APPLY runs slow
I am trying to optimize the query to run faster.
The query is the following:
SELECT grp_fk_obj_id, grp_name
FROM tbl_groups as g1
CROSS APPLY (SELECT TOP 1 grp_id as gid
...
- 133
11
votes
1
answer
768
views
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 ...
- 194
-1
votes
1
answer
1k
views
"No column name was specified for column..." when aliasing results of CROSS APPLY
I have this table:
from which I create this View:
This view's CardId allows me to JOIN against the Card table with so I can retrieve the Count from any CardId. Here's my SQL:
SELECT * FROM (
...
- 151
10
votes
2
answers
566
views
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: https://feedback.azure....
- 103
18
votes
2
answers
3k
views
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 ...
- 80.3k
1
vote
1
answer
1k
views
Refactor SQL to left join on TVF requiring input variable
I would like to join two tables and a table-valued function. The TVF take a date as input variable.
The expected output is:
Date, LimitName, Exposure, ProductValueEligible, ProductValueNonEligible
...
- 119
7
votes
1
answer
2k
views
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 ...
- 2,036
2
votes
1
answer
1k
views
Refactoring subquery to JOIN and CROSS APPLY, get only one row for each record in parent table
Given the following query:
SELECT
p.ProductName,
CASE
WHEN EXISTS(SELECT 1 FROM Product WHERE ProductSuperID = p.ProductSuperID AND HasImage = 1)
THEN
1
ELSE
0
END
...
- 187
1
vote
1
answer
72
views
is there a way to improve this query by not re calculating functions? cross apply?
when I set up replication I like to take a count of each table in the publisher, then I go to the subscriber I do the same thing in order to compare both pictures.
this is the picture from the ...
- 15.5k
6
votes
3
answers
4k
views
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,
[...
- 175
4
votes
1
answer
822
views
Convert columns into rows for use with indexed/materalized view
I'd like to convert columns into rows for a given SQL Server table. The table effectively is an audit table; it stores a given old and new value in separate columns for a given user attribute (e.g. ...
- 265
3
votes
1
answer
1k
views
Speed Up Cross Apply Without Index Hint
I have a very small table with 12 rows in it that can be created with the following statement:
CREATE TABLE dbo.SmallTable(ScoreMonth tinyint NOT NULL PRIMARY KEY,
...
- 451
3
votes
2
answers
283
views
Return batches of rows based on one row
Say I have a table like this in SQL Server 2008:
id | name | qty
-------------------
1 | john | 1
2 | bill | 3
3 | mary | 2
4 | jill | 5
I would like to query this ...
- 33
6
votes
3
answers
2k
views
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 ...
- 2,055
3
votes
1
answer
7k
views
Performance improvement Outer Apply
I have a query which runs little slower.
SELECT b.BreakdownClassificationId,
k.IsinCode,
k.SedolCode,
ClassificationDate,
NAME,
InstrumentType,
...
- 1,349
5
votes
1
answer
12k
views
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 ...
- 53
1
vote
1
answer
2k
views
Issue with SUM OVER PARTITION BY in conjunction with GROUP BY
I am getting a typical group by error that one would see when a field is not in a group by or an aggregate in the select.
Column 'v.EDTAmount' is invalid in the select list because it is not ...
- 607
4
votes
1
answer
3k
views
While using Cross Apply, can I force "empty" records to appear?
I have a query that is working fantastically, except that I would like to force some records to appear. I am using SqlServer 2016, and this query will ultimately be used in a VIEW and used by SSRS ...
- 607
1
vote
1
answer
104
views
Where and how to add selective totals in SSRS
I need to get some totals output in SSRS that I am having difficulty putting together. I am not sure if I should do the totals for the below query within the query, or in SSRS. The output is going to ...
- 607
1
vote
2
answers
319
views
TSQL help my feeble mind grasp Pivot/Unpivot on two columns
I've been trying to wrap my feeble mind around this one for some time, but I am having difficulty. I have looked at a few other examples like this SE.DBA answer, but I am having difficulty applying it ...
- 607
4
votes
1
answer
145
views
Does the apply operator use row by row processing?
I'm working with 2 of the same datasets. However, one is derived from a temp table and the other is from a derived table (cross apply operator). The derived table takes much longer to process and it ...
- 41
0
votes
1
answer
3k
views
CROSS APPLY query with CASE and running totals [closed]
I'm trying to make this business logic make sense, but I'm not having any luck.
The rule is I can have a sale with discounts from percentage and value. But the value is to be subtracted after ...
- 1
0
votes
1
answer
273
views
How to use reference to another table in aggregate function
I have following easy-to-explain task: we have a table, and we should calculate total count of rows grouped by columns. Column number is fixed so we write:
SELECT [GroupId] = cr.[GroupId],
[...
- 333
7
votes
1
answer
1k
views
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 ...
- 399
1
vote
2
answers
4k
views
How to keep row ordering with DISTINCT command or how to add row number to cross apply
I have problem that after distinct, order direction of Variable column change.
Here is example
Data Source
DECLARE @Table TABLE
(
FirstName NVARCHAR(255),
LastName NVARCHAR(255),
...
- 1,039
3
votes
1
answer
268
views
Translate query using APPLY to run under compatibility level 80
I want to use the following query:
SELECT
SUBSTRING(qt.TEXT, ( qs.statement_start_offset / 2 ) + 1,
( ( CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
...
- 4,717
8
votes
2
answers
315
views
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 ...
- 83
8
votes
3
answers
14k
views
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 ...
- 287
3
votes
1
answer
2k
views
Insert a range of numbers from a call to sp_sequence_get_range
Given the following code:
CREATE SEQUENCE dbo.NextTestId AS [bigint]
START WITH 10 INCREMENT BY 2 NO CACHE
GO
DECLARE
@variableNumberOfIdsNeeded INT = 7, -- This will change for each call
@...
- 2,454
8
votes
2
answers
17k
views
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 ...
- 1,039
0
votes
1
answer
2k
views
Pivoting some row data into columns (SQL Server 2008 R2)
Sorry if this has been answered before but I'm exhausted and on a time line. Maybe I'm just an idiot. I'm designing a data export from One application to import into another.
Full-Disclosure: I did ...
- 3
43
votes
2
answers
111k
views
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 ...
- 549
4
votes
1
answer
584
views
How Can the Same Query in Two Nearly Identical Instances Generate Two Different Execution Plans?
Server A and Server B have identical hardware and instance configurations (A is Production, B is QA). B's DBs were restored from A's backups from one week ago. I was provided this query by the ...
- 1,913
3
votes
1
answer
1k
views
Cross Apply without a function
I would like to run a query very similar to this on one of my production databases:
-- Setup the table (Already there in my real scenario)
create table dbo.MyTable(MyValue int, MyGroup varchar(5))
...
- 2,454
0
votes
3
answers
143
views
How to convert query results into groups of common results
I have a query that returns results like this:
MyValue MyGroup
_______________|_______________
56 | I
_______________|_______________
12 | I
...
- 2,454
5
votes
1
answer
2k
views
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 ...
- 1,297
3
votes
1
answer
3k
views
CROSS APPLY-ing a parameterised function
Given the following definitions:
CREATE TABLE MyTable
(
col1 int,
col2 int,
col3 int,
col4 int
)
CREATE FUNCTION fn_MyFunction
(
@Param1 int,
@Param2 int
)
RETURNS TABLE AS ...
- 4,492