Cross Apply Issue (Last Call) :)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Cross Apply Issue (Last Call) :)

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-05-28 : 11:33:56

I can't figure out what can be causing this.

When I use this query


Select top 1000 a.EmployeeID,b.*
from #TmpActiveEmployeesWSeverance a
cross apply 
dbo.fn_Severance_AccountItemsTable(a.EmployeeID,a.BenefitTypeID,null,null,null,null) b
order by a.EmployeeID,a.BenefitTypeID

It runs 4 seconds

If I try to insert the results into anything It runs > 5 minutes (I have yet to let it finish)

I have tried the two following pieces of code, both with the same results


Select top 1000 a.EmployeeID,b.*
into #Tmp
from #TmpActiveEmployeesWSeverance a
cross apply 
dbo.fn_Severance_AccountItemsTable(a.EmployeeID,a.BenefitTypeID,null,null,null,null) b
order by a.EmployeeID,a.BenefitTypeID

--and
Insert Into TRP_ActiveEmployeesWSeverance
(EmployeeID
,PK
,BeginningBalance
,BenefitInterestRowID
,BenefitInterestID
,BenefitTypeID
,DateReceived
,InvoiceDate
,Amount
,Hours
,Fraction1
,Fraction2
,Interest
,InterestAmount
,StartDate
,EndDate
,PeriodApplied
,Offset
,Reserve
,Account
,BenefitClosedID
,PaidOut
,ClosedAccount
,ai
,ClosedDate
,StartAgain
,PartialDividend
,PartialFraction
,SameDateCount)
Select top 1000 a.EmployeeID,b.*
from #TmpActiveEmployeesWSeverance a
cross apply 
dbo.fn_Severance_AccountItemsTable(a.EmployeeID,a.BenefitTypeID,null,null,null,null) b
order by a.EmployeeID,a.BenefitTypeID

Any thoughts as to what can be disrupting this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 12:08:06

Is it that insert into any table is taking 5 minutes? or only to TRP_ActiveEmployeesWSeverance table? If only latter is problem, then you should have a look at how many indexes are there in table and also triggers existing.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-05-28 : 12:08:44

Both tables were causing the 5 minutes. It was only running in 4 seconds when I was not inserting anywhere.

removing the Order by resolved the issue.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-05-28 : 16:34:56

Just curious, Does anyone know why the Order By clause would effect this query???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 02:38:41

When using ORDER BY with CROSS APPLY operator the SQL Server nees to store a temporary resultset in tempdb, sort, and then return the recordset.
If you do not use ORDER BY, the resultset is returned immediately.

Check out the two different execution plans whenever in doubt.

E 12°55'05.25"
N 56°04'39.16"

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-05-29 : 11:48:34

Can you please help me in clarifying, I am not certain why this would only come into effect when I was INSERTING into a table, and not when I was simply running the query?

Also, Thanks Peso for your answer, the reason I didn't check out the execution plan was because I never let it finish. (I wasn't certain how long it would've kept running).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources