HELP WITH QUERY SPEED - 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)

HELP WITH QUERY SPEED

Author

Topic

nhess80
Yak Posting Veteran

83 Posts

Posted - 2010-03-09 : 14:53:40

Hi,

I need some help with my query. It runs really slow and I have been working on it for the past week and I cannot figure out how to speed it up. Please take a look at it below and let me know if you have any suggestions. Also whatever other information I can provide please let me know and I will do it ASAP.

Thanks for your help,

Nick

I'm sure that the problem is with (TermNumber LIKE ZoneSIP_npanxx + '%'). With that statement it will take forever but if I change it to this (LEFT(TermNumber,6) = ZoneSIP_npanxx) it takes no time at all. But I have to run it like the first because the second will only match on 6 digits where the first will find the closest match.

SELECT TermNumber,(Convert(numeric(18),Duration) / 60) AS Minutes,

(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

FROM CDRS a

CROSS APPLY (

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

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-03-09 : 16:47:16

Is this a correct interpretation for the column table aliases?

SELECT		a.TermNumber,
		CONVERT(NUMERIC(18), a.Duration) / 60) AS Minutes,
		CASE a.InterIntra
			WHEN 'Intra' THEN z.ZoneSIP_intra
			ELSE z.ZoneSIP_inter 
		END AS RateSelected
FROM		CDRS AS a
CROSS APPLY	(
			SELECT TOP(1)	w.ZoneSIP_inter,
					w.ZoneSIP_intra
			FROM		ZoneSIP AS w
			WHERE		a.TermNumber LIKE w.ZoneSIP_npanxx + '%'
			ORDER BY	w.ZoneSIP_npanxx DESC
		) AS z
WHERE		a.DateTimeInt BETWEEN 1266192000 AND 1266278399
		AND a.OrigGw = 'GB-GW2'
		AND a.OrigPort = '3'
		AND a.Duration > 0

N 56°04'39.26"
E 12°55'05.63"

nhess80
Yak Posting Veteran

83 Posts

Posted - 2010-03-09 : 16:54:53

Yes it is

quote:

Originally posted by Peso

Is this a correct interpretation for the column table aliases?

SELECT		a.TermNumber,
		CONVERT(NUMERIC(18), a.Duration) / 60) AS Minutes,
		CASE a.InterIntra
			WHEN 'Intra' THEN z.ZoneSIP_intra
			ELSE z.ZoneSIP_inter 
		END AS RateSelected
FROM		CDRS AS a
CROSS APPLY	(
			SELECT TOP(1)	w.ZoneSIP_inter,
					w.ZoneSIP_intra
			FROM		ZoneSIP AS w
			WHERE		a.TermNumber LIKE w.ZoneSIP_npanxx + '%'
			ORDER BY	w.ZoneSIP_npanxx DESC
		) AS z
WHERE		a.DateTimeInt BETWEEN 1266192000 AND 1266278399
		AND a.OrigGw = 'GB-GW2'
		AND a.OrigPort = '3'
		AND a.Duration > 0

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources