how to improve sub query performance

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

General SQL Server Forums

New to SQL Server Programming

how to improve sub query performance

Author

Topic

sanjeev124
Starting Member

5 Posts

Posted - 2012-08-07 : 00:25:57

I have two tables and from each table i have to take column value on condition basis.My query is working but it gives performance issue.
Query is:

select dTransferDate,isourceid,t.iSP,t.sSP,sum(t.DCefiletype) DCefiletype,COALESCE(sum(t.DClFileSize),0)DClFileSize,sum(t.VDOefiletype)VDOefiletype,COALESCE(sum(t.VDOlFileSize),0)VDOlFileSize,sum(t.VUefiletype)VUefiletype,COALESCE(sum(t.VUlFileSize),0)VUlFileSize from( select distinct lDeviceID as isourceid, '1' iSP, 'SP' sSP,convert (varchar, dTransferDate,101) dTransferDate,(select COUNT(FileType) from SVIntermediateFileStore.dbo.DailyFileTransDataSize d where d.Filetype in(128,129)and d.lDeviceID=a.lDeviceID and convert(varchar(10),d.dTransferDate,101) =convert(varchar(10),a.dTransferDate,101) and d.lInvoiceOrganisation=a.lInvoiceOrganisation) as DCefiletype,
(select COUNT(filetype) from SVIntermediateFileStore.dbo.DailyFileTransDataSize d where d.Filetype in(130) and d.lDeviceID=a.lDeviceID and convert(varchar(10),d.dTransferDate,101) =convert(varchar(10),a.dTransferDate,101) and d.lInvoiceOrganisation=a.lInvoiceOrganisation ) as VUefiletype ,
(select COUNT(filetype) from SVIntermediateFileStore.dbo.DailyFileTransDataSize d where d.Filetype in(131)and d.lDeviceID=a.lDeviceID and convert(varchar(10),d.dTransferDate,101) =convert(varchar(10),a.dTransferDate,101) and d.lInvoiceOrganisation=a.lInvoiceOrganisation) as VDOefiletype ,
(select sum(DataSize) from SVIntermediateFileStore.dbo.DailyFileTransDataSize d where d.Filetype in(128,129)and d.lDeviceID=a.lDeviceID and convert(varchar(10),d.dTransferDate,101) =convert(varchar(10),a.dTransferDate,101) and d.lInvoiceOrganisation=a.lInvoiceOrganisation) as DClFileSize,
(select sum(DataSize) from SVIntermediateFileStore.dbo.DailyFileTransDataSize d where d.Filetype in(130)and d.lDeviceID=a.lDeviceID and convert(varchar(10),d.dTransferDate,101) =convert(varchar(10),a.dTransferDate,101) and d.lInvoiceOrganisation=a.lInvoiceOrganisation) as VUlFileSize,
(select sum(DataSize) from SVIntermediateFileStore.dbo.DailyFileTransDataSize d where d.Filetype in(131)and d.lDeviceID=a.lDeviceID and convert(varchar(10),d.dTransferDate,101) =convert(varchar(10),a.dTransferDate,101) and d.lInvoiceOrganisation=a.lInvoiceOrganisation) as VDOlFileSize
from SVIntermediateFileStore.dbo.DailyFileTransDataSize a inner join
InvoiceOrganisation SP on (a.lInvoiceOrganisation = SP.lInvoiceOrganisation and charindex(','+ltrim(rtrim(cast(SP.lInvoiceOrganisation as varchar)))+',', ','+ltrim(rtrim(@sSPID ))+',') > 0)
and CAST(dTransferDate As DATE) >= CAST( @dtStartDate AS DATE) and CAST(dTransferDate As DATE) <= CAST( @dtEndDate AS DATE)
--and convert (varchar,dTransferDate,101) between convert (varchar, @dtStartDate,101) and convert (varchar, @dtEndDate,101)
and lDeviceID>0
and FileType in(128,129,130,131)
group by lDeviceID,FileType,DataSize,dTransferDate,SP.sOrganisationName,a.lInvoiceOrganisation

UNION
select e.iReadInWith as isourceid,'1' iSP, 'SP' sSP,convert (varchar, dTransferStartDate,101) dTransferDate,(select COUNT(efiletype) from SVIntermediateFileStore.dbo.FileTransfer d where d.eFiletype in(129) and d.iReadInWith=e.iReadInWith and d.guserid in (e.guserid)) as DCefiletype,(select COUNT(efiletype) from SVIntermediateFileStore.dbo.FileTransfer d where d.eFiletype in(130) and d.iReadInWith=e.iReadInWith and d.guserid in (e.guserid)) as VUefiletype ,
(select COUNT(efiletype) from SVIntermediateFileStore.dbo.FileTransfer d where d.eFiletype in(131)and d.iReadInWith=e.iReadInWith and d.guserid in (e.guserid)) as VDOefiletype ,
(select COALESCE((sum(convert(decimal,lOriginalFileSize))/1048576),0) from SVIntermediateFileStore.dbo.FileTransfer d where d.eFiletype in(128,129)and d.iReadInWith=e.iReadInWith and d.guserid in (e.guserid)) as DClFileSize,
(select COALESCE((sum(convert(decimal,lOriginalFileSize))/1048576),0) from SVIntermediateFileStore.dbo.FileTransfer d where d.eFiletype in(130)and d.iReadInWith=e.iReadInWith and d.guserid in (e.guserid)) as VUlFileSize,
(select COALESCE((sum(convert(decimal,lOriginalFileSize))/1048576),0) from SVIntermediateFileStore.dbo.FileTransfer d where d.eFiletype in(131)and d.iReadInWith=e.iReadInWith and d.guserid in (e.guserid)) as VDOlFileSize
from SVIntermediateFileStore.dbo.FileTransfer e,InvoiceOrganisation SP
where e.gUserID in (
select gUserID From Users where lAccountID in
(
select lAccountID from Account where lInvoiceOrganisation in
(
select lInvoiceOrganisation from InvoiceOrganisation where
SP.lInvoiceOrganisation=InvoiceOrganisation.lInvoiceOrganisation and (charindex(','+ltrim(rtrim(cast(InvoiceOrganisation.lInvoiceOrganisation as varchar)))+',', ','+ltrim(rtrim(@sSPID))+',') > 0)
)
)
)and CAST(e.dTransferStartDate As DATE) >=CAST(@dtStartDate As DATE) and CAST(e.dTransferStartDate as DATE)<=CAST(@dtEndDate as DATE)
and e.iReadInWith>0
and e.eState=3
and e.eTransferDirection=1
and eFileType in(128,129,130,131) )t
group by t.dTransferDate,t.isourceid,t.iSP,t.sSP

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-08-07 : 01:51:54

Functions on the WHERE predicates will have a performance hit. It's having to run the function on every row.
Have you checked the Execution Plans regarding optimised access to the defined indexes?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources