Query efficiency

[cfcProgrammer]

Hi,

I have written a query however when I execute it... the query takes a very long time, I have never let the query complete, it is definitely running over 5 mins as that is the max I allowed the query to run before I cancelled it. I'm unsure why it is taking so long and was hoping someone could take a look and advise me of modifications I should make in order to have this query run in a reasonable amount of time. I will not be able to put this into our production environment as it stands right now.

STARSRO is a linked server.

I need to extract data from a sqlserver database and an oracle database.
The link is working, it is just taking a very long time!

Thanks so much

SELECT NMS.SERIAL_NUM, c.ParentCustomerId as 'Parent Cust ID',
m.CustomerId as 'Customer ID',
c.ARAccountCode as 'Account Number',
m.SerialNum as 'Serial Number',
c.ContactCompanyName as 'Comapny Name',
CDR.ACCT_PERIOD,
sum(CDR.MINUTES)as Minutes
FROM STARSRO..BILL_PROD.NMS_MOBILE_ID NMS ,
STARSRO..BILL_PROD.CDR_CUSTOMER CDR,
ird.dbo.Mobile m join ird.dbo.Customer c on c.Id =m.CustomerId
WHERE
nms.serial_num = m.serialNum
AND
m.CustomerId in(SELECT c.Id
FROM [ird].[dbo].[Customer] c
where (c.Id = 12345 or c.ParentCustomerId = 12345)
and c.IsActive ='1')
AND m.IsActive ='1'
GROUP BY NMS.SERIAL_NUM, m.SerialNum, m.CustomerId, c.ParentCustomerId,c.ARAccountCode,c.ContactCompanyName,CDR.ACCT_PERIOD

cfcProgrammer

 

[gmmastros]

I don't see a condition involving NMS and CDR. You're probably getting a Cartesian join on these 2 tables.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gk53]

YOU ARE JOIN STARSRO..BILL_PROD.NMS_MOBILE_ID NMS ,
STARSRO..BILL_PROD.CDR_CUSTOMER CDR,
ird.dbo.Mobile m ON WHAT CONDITIONS?
it should be something like, but I do not know you table structure to use right "fieldName"

SELECT NMS.SERIAL_NUM, c.ParentCustomerId as 'Parent Cust ID',
	m.CustomerId as 'Customer ID',
	c.ARAccountCode as 'Account Number',
	m.SerialNum as 'Serial Number',
	c.ContactCompanyName as 'Comapny Name',
	CDR.ACCT_PERIOD,
	sum(CDR.MINUTES)as Minutes	
FROM STARSRO..BILL_PROD.NMS_MOBILE_ID NMS 
	join STARSRO..BILL_PROD.CDR_CUSTOMER CDR
		on NMS.fieldName = CDR.fieldName
	join ird.dbo.Mobile m 
		on m.fieldName = CDR.fieldName
	join ird.dbo.Customer c 
		on c.Id =m.CustomerId
	WHERE nms.serial_num = m.serialNum
	AND m.CustomerId in(SELECT	c.Id
							FROM [ird].[dbo].[Customer] c
							where (c.Id = 12345 or c.ParentCustomerId = 12345)
								and c.IsActive ='1')
			AND m.IsActive ='1'
	GROUP BY NMS.SERIAL_NUM, 
			m.SerialNum, 
			m.CustomerId, 
			c.ParentCustomerId,
			c.ARAccountCode,
			c.ContactCompanyName,
			CDR.ACCT_PERIOD