October 8, 2008 at 2:29 am
Hi All,
The following simple select query is getting timeout,
select '12' as Norder, 'Total Number of Disabled Users Mailboxes in Active Users Email Box Stores' as description,
(SELECT count(DISTINCT InstanceName_PK)
FROM
DC02RPTS02.SystemCenterReporting.dbo.SC_SampledNumericDataFact_View SND INNER JOIN
DC02RPTS02.SystemCenterReporting.dbo.SC_CounterDetailDimension_View CDD ON CDD.SMC_InstanceID = SND.CounterDetail_FK INNER JOIN
DC02RPTS02.SystemCenterReporting.dbo.SC_ComputerDimension_View CD ON CD.SMC_InstanceID = SND.Computer_FK
WHERE
CDD.ObjectName_PK in ('McExchDG')
AND CDD.CounterName_PK in ( 'Mailbox MB','Mailbox Message Count')
and SND.LocalDateTimeSampled between getdate()-1 and getdate()
and InstanceName_PK in (select name from [dc01sqlv03\sql_inst3].it_infrastructure.dbo.v_DisabledUsers )
and CD.FullComputerName ='DUCORP\DC02EXBV01')
But When running the main select sub query,
SELECT count(DISTINCT InstanceName_PK)
FROM
DC02RPTS02.SystemCenterReporting.dbo.SC_SampledNumericDataFact_View SND INNER JOIN
DC02RPTS02.SystemCenterReporting.dbo.SC_CounterDetailDimension_View CDD ON CDD.SMC_InstanceID = SND.CounterDetail_FK INNER JOIN
DC02RPTS02.SystemCenterReporting.dbo.SC_ComputerDimension_View CD ON CD.SMC_InstanceID = SND.Computer_FK
WHERE
CDD.ObjectName_PK in ('McExchDG')
AND CDD.CounterName_PK in ( 'Mailbox MB','Mailbox Message Count')
and SND.LocalDateTimeSampled between getdate()-1 and getdate()
and InstanceName_PK in (select name from [dc01sqlv03\sql_inst3].it_infrastructure.dbo.v_DisabledUsers )
and CD.FullComputerName ='DUCORP\DC02EXBV01'
The resulset is displayed with in 30-45 second. In this main query I created the linked server and selecting the data.
The timeout is occuring when integrating the below simple text select query
select '12' as Norder, 'Total Number of Disabled Users Mailboxes in Active Users Email Box Stores' as description,
with the main select sub query and getting 'timeout error' or 'OLE DB resource limit error'.
Please advice me for any problem with these queries.
BR,
Parthipan.S
October 8, 2008 at 3:15 am
Why are you using a sub-query for the first example? this could be why the query is timing out, try writing it like this;
select '12' as Norder, description as 'Total Number of Disabled Users Mailboxes in Active Users Email Box Stores' , count(DISTINCT InstanceName_PK)
FROM
DC02RPTS02.SystemCenterReporting.dbo.SC_SampledNumericDataFact_View SND INNER JOIN
DC02RPTS02.SystemCenterReporting.dbo.SC_CounterDetailDimension_View CDD ON CDD.SMC_InstanceID = SND.CounterDetail_FK INNER JOIN
DC02RPTS02.SystemCenterReporting.dbo.SC_ComputerDimension_View CD ON CD.SMC_InstanceID = SND.Computer_FK
WHERE
CDD.ObjectName_PK in ('McExchDG')
AND CDD.CounterName_PK in ( 'Mailbox MB','Mailbox Message Count')
and SND.LocalDateTimeSampled between getdate()-1 and getdate()
and InstanceName_PK in (select name from [dc01sqlv03\sql_inst3].it_infrastructure.dbo.v_DisabledUsers )
and CD.FullComputerName ='DUCORP\DC02EXBV01')
October 8, 2008 at 3:37 am
Hi,
You are great and Thanks alot..Now the query is generating the result set.
Please brief me why the sub select query was causing the problem?
October 8, 2008 at 3:43 am
The sub-query means that for each row in the outer query, sql server needs to search the inner query so you exponentially increase the amount of work sql sever has to do for each sub-query you add.
They are useful when selecting data from two or more tables, but a much preferred method is to use joins between the tables.
You shouldn't need to use sub-queries for simple select queries.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply