Correlated Joins Using "Apply"

Question

Correlated Joins Using "Apply"

GregoryAJackson

SSCrazy

Points: 2794
More actions
June 14, 2008 at 3:02 pm

#96043

Comments posted to this topic are about the item Correlated Joins Using "Apply"
Gregory A Jackson MBA, CSM

Viewing 15 posts - 1 through 15 (of 49 total)

You must be logged in to reply to this topic. Login to reply

Michele Ballarin Grasshopper Points: 17 More actions · Answer 1

i'm unable to read it

i try to turn the video, but SQLscritp turn whit the screen and the only way to read the script is to copy and paste into Notepad or similar....

Michele

Alex 469216 Valued Member Points: 60 More actions · Answer 2

it could be a descent article had it been written 2 years ago.

GregoryAJackson SSCrazy Points: 2794 More actions · Answer 3

Alex,

I wish I had known of this feature 2 years ago but it's one I missed until recently. My hope is that there are others out there that had been missing this also so now they can leverage it.

Cheers,

GAJ

Gregory A Jackson MBA, CSM

wordracr SSC Enthusiast Points: 176 More actions · Answer 4

This is possible in sql 2000 as well.

select 1 as id, '1st' as position into #data union all

select 1 as id, '2nd' as position union all

select 1 as id, '3rd' as position union all

select 1 as id, '4th' as position union all

select 1 as id, '5th' as position union all

select 2 as id, '1st' as position union all

select 2 as id, '2nd' as position union all

select 2 as id, '3rd' as position union all

select 2 as id, '4th' as position union all

select 2 as id, '5th' as position

select *

from #data d1

where position in (select top 3 position from #data d2 where d1.id = d2.id order by position)

DavidSimpson Hall of Fame Points: 3562 More actions · Answer 5

I disagree with Alex, it does not matter if SQL Server 2005 was release 2 minutes ago or 2 years ago, there is always something new to learn about SQL Server. I think this was a good article pointing out a feature introduced in SQL Server 2005. I have read about APPLY but have not used it yet which makes it easily forgettable :blink: , so I appreciate an article that reminds me of different ways of getting a job done.

David

Michael Clark-374593 SSC Rookie Points: 29 More actions · Answer 6

What's is the performance hit from using the UDF?

Would this be something for OLTP, or just OLAP?

GregoryAJackson SSCrazy Points: 2794 More actions · Answer 7

Hi Michael,

I'm not sure of the performance hit of the udf, I'd have to test the various query plans, etc.

The maintenace issue is what I'd want to stay away from (Having to create another object in SQL, Version control it, script it, maintain it, etc)

GAJ

Gregory A Jackson MBA, CSM

MudLuck SSCrazy Points: 2301 More actions · Answer 8

Hmm,

The first thought that came to me isn't this what I've use derived tables and identities for in the past.

I won't guarantee performance want suffer of be less then using apply because I'm still on sql 2000 :-(, but this is how I would solve the problem.

In the derived table query I would show the transaction id on the top three items and there dates sorted by transaction id and dates I would then have another query above it in the same derived table that mapped those rows to there identities and then use those identities as the join condition out of the derived table to return my max top 3 rows per transaction ID.

I haven't seen your schema put I'm pretty sure this can be done.

Enjoy.

Peter E. Kierstead SSCarpal Tunnel Points: 4114 More actions · Answer 9

We sometimes create our own "full-Text" indexes instead of using SQL's Full-Text feature because we have non-standard requirements...

I'll create a Multi-Statement table-valued function that parses a string parameter into a table of terms.

I'll Cross Apply this function to a database table passing the column (or appended columns) I wish to parse into a full-text index, outputing the resultant UDF-generated table along with the primary key of the database table row.

I'll index this table appropriately, and wallah!, home-grown full-text index...

Tastes great... less filling...

I leave the exercise of integration up to you to help burn the calories gained from ingesting this full-bodied brew!

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

Having done some amount of testing on this - while this scenario is certainly convenient in some scenarios, it has a tendency to perform on par with CSQ's, so it will suffer rather severely on large datasets. It unfortunately just seems to be a cleaner way to write a Correlated sub-query, but with the same kinds of perf challenges the CSQ's had (it forces row-by-row evaluation, the logical reads tend to get out of control, etc...)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Anipaul SSC-Insane Points: 24681 More actions · Answer 11

Yes I agree you can always learn new things. Even you can learn new things from your juniors. Some people may know these feature but this article is good for many people.

Cheers!!!

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 12

This is an excellent article by itself and even better that it suggests alternative solutions by other authors. I will try them all.

When you need to do something quick, you normally use most familiar tools and if you don't have to run this query often, performance does not matter. I needed to get top 10 calls for each contact and pivot call dates to the output columns. Well, I used s cursor with the outer loop going from contact to contact and the inner loop moving from call to call. The line number for the call for a certain customer also served as a part of the column name in the update statement:

select @strUpdate ='update ##TempTableCalls set Call_'+ convert(nvarchar(10),@LINE_NO) + ' = ' (the rest of the line going here)

The temp table with the columns like Call_1, Call_2 was created in advance and populated with something additional before adding calls.

After the update string for a call was composed, I used

Exec sp_executesql @strUpdate

Worked fine and reliable, I had to use it only twice, so not performance concerns, but I spent a lot of time writing it.

Yelena

Regards,Yelena Varsha

mark rubanovich Newbie Points: 5 More actions · Answer 13

Was not it easy to use more simple sql, like this one:

Select

?

GregoryAJackson SSCrazy Points: 2794 More actions · Answer 14

Mark,

Could you post your code again?

GAJ

Gregory A Jackson MBA, CSM