CROSS APPLY RUNS SLOW - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

CROSS APPLY RUNS SLOW

Author

Topic

nhess80
Yak Posting Veteran

83 Posts

Posted - 2010-02-24 : 13:31:07

Hi,

The tables that will be used in this query are large. At minimum there will be 4 tables being used. In the largest table I will be searching over a month span which is about 5 to 10 million records the other tables have about 140,000 records a piece. Is the cross apply not meant for large amounts of data? Below is my query. I made it any easy one where I am only searching over 1 min time span. It only returns 13 records and it took 5 seconds to run. Maybe I’m using the cross apply wrong. If there is any other info that I should add please let me know.

Thanks,

Nick

SELECT a.*,b.[state],c.[state],CASE WHEN b.state = c.state THEN 'Intra' ELSE 'Inter' END AS InterIntra,

CASE WHEN b.state = c.state THEN
CASE WHEN ZoneSIP_intra IS NULL THEN NULL
ELSE ZoneSIP_intra END
ELSE
CASE WHEN ZoneSIP_inter IS NULL THEN NULL
ELSE ZoneSIP_inter END
END AS RateSelected,

CASE WHEN b.state = c.state THEN
CASE WHEN ZoneSIP_intra IS NULL THEN NULL
ELSE 'ZoneSIP_intra' END
ELSE
CASE WHEN ZoneSIP_inter IS NULL THEN NULL
ELSE 'ZoneSIP_inter' END
END AS VendorSelected

FROM CDRS a

CROSS APPLY (SELECT TOP 1 npanxxy,[state]
FROM LergData
WHERE CAST(ANI AS varchar(15)) LIKE CAST(npanxxy AS varchar(15)) + '%'
ORDER BY LEN(npanxxy) DESC) b

CROSS APPLY (SELECT TOP 1 npanxxy,[state]
FROM LergData
WHERE CAST(LRN AS varchar(15)) LIKE CAST(npanxxy AS varchar(15)) + '%'
ORDER BY LEN(npanxxy) DESC) c

CROSS APPLY (SELECT TOP 1 ZoneSIP_npanxx,ZoneSIP_inter,ZoneSIP_intra
FROM ZoneSIP
WHERE CAST(TermNumber AS varchar(15)) LIKE CAST(ZoneSIP_npanxx AS varchar(15)) + '%'
ORDER BY LEN(ZoneSIP_npanxx) DESC) ZoneSIP

Where (DateTimeInt Between 1266224400 AND 1266224459)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-02-24 : 14:14:42

the problem is not with CROSS APPLY, the problem is the way you are correlating your tables. Converting to varchar then doing LIKE comparisons will likely force table scans for each correlation. What is the underlying datatype of the values you're converting? If the number of rows that satisfies the LIKEs are large then you loose a fair bit of efficiency ordering the results to the top 1 longest value. Looks like the design is the problem - not the cross applies.

Be One with the Optimizer
TG

nhess80
Yak Posting Veteran

83 Posts

Posted - 2010-02-24 : 15:32:13

I went ahead and changed the data type to varchar which I should have done a long time ago and also removed things from the query that were not needed. I have two queries below the first one being the revised one that you looked at and the second one is the old way I used to do the query. The first one I had to stop from running it was taking so long and the second one runs in less than a second. I changed the Timespan the queries are running over to an entire day which is about 300K records. The reason I cannot use the second query anymore is because it just matches to 6 digits IE-"LEFT(a.TermNumber,6) = b.ZoneSIP_npanxx". The first one is the way I need to do it because it gets the closest match. I just cannot seem to write it so it runs fast. Any other advice you can give. The “LIKES” have to be there for me to get the results I need.

---------------------------------------------------------------

SELECT Sum(Convert(numeric(18),Duration) / 60),

SUM(CASE WHEN InterIntra = 'Intra' THEN
CASE WHEN ZoneSIP_intra IS NULL THEN NULL
ELSE ZoneSIP_intra END
ELSE
CASE WHEN ZoneSIP_inter IS NULL THEN NULL
ELSE ZoneSIP_inter END
END) AS RateSelected,

CASE WHEN InterIntra = 'Intra' THEN
CASE WHEN ZoneSIP_intra IS NULL THEN NULL
ELSE 'ZoneSIP_intra' END
ELSE
CASE WHEN ZoneSIP_inter IS NULL THEN NULL
ELSE 'ZoneSIP_inter' END
END AS VendorSelected

FROM CDRS a

CROSS APPLY (SELECT TOP 1 ZoneSIP_npanxx,ZoneSIP_inter,ZoneSIP_intra
FROM ZoneSIP
WHERE TermNumber LIKE ZoneSIP_npanxx + '%'
ORDER BY LEN(ZoneSIP_npanxx) DESC) ZoneSIP

Where (DateTimeInt Between 1266192000 AND 1266278399) AND (OrigGw = 'GB-GW2') AND (OrigPort = '3')

Group by CASE WHEN InterIntra = 'Intra' THEN
CASE WHEN ZoneSIP_intra IS NULL THEN NULL
ELSE 'ZoneSIP_intra' END
ELSE
CASE WHEN ZoneSIP_inter IS NULL THEN NULL
ELSE 'ZoneSIP_inter' END
END

---------------------------------------------------------------

SELECT Sum(Convert(numeric(18),Duration) / 60),

SUM(CASE WHEN InterIntra = 'Intra' THEN
CASE WHEN ZoneSIP_intra IS NULL THEN NULL
ELSE ZoneSIP_intra END
ELSE
CASE WHEN ZoneSIP_inter IS NULL THEN NULL
ELSE ZoneSIP_inter END
END) AS RateSelected,

CASE WHEN InterIntra = 'Intra' THEN
CASE WHEN ZoneSIP_intra IS NULL THEN NULL
ELSE 'ZoneSIP_intra' END
ELSE
CASE WHEN ZoneSIP_inter IS NULL THEN NULL
ELSE 'ZoneSIP_inter' END
END AS VendorSelected

FROM CDRS a

LEFT OUTER JOIN ZoneSIP b ON LEFT(a.TermNumber,6) = b.ZoneSIP_npanxx

Where (DateTimeInt Between 1266192000 AND 1266278399) AND (OrigGw = 'GB-GW2') AND (OrigPort = '3')

Group by CASE WHEN InterIntra = 'Intra' THEN
CASE WHEN ZoneSIP_intra IS NULL THEN NULL
ELSE 'ZoneSIP_intra' END
ELSE
CASE WHEN ZoneSIP_inter IS NULL THEN NULL
ELSE 'ZoneSIP_inter' END
END

quote:
Originally posted by TG

the problem is not with CROSS APPLY, the problem is the way you are correlating your tables. Converting to varchar then doing LIKE comparisons will likely force table scans for each correlation. What is the underlying datatype of the values you're converting? If the number of rows that satisfies the LIKEs are large then you loose a fair bit of efficiency ordering the results to the top 1 longest value. Looks like the design is the problem - not the cross applies.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources