SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to improve performance of a sub query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sanjeev124
Starting Member

5 Posts

Posted - 10/15/2012 :  01:38:56  Show Profile  Reply with Quote
Hi My query takes a lot of time how we can improve its perfirmance.

select e.iReadInWith as isourceid,'1' iSP, 'SP' sSP,convert (varchar, dTransferStartDate,101) dTransferDate
(select COUNT(efiletype) from #FileTransfer d where d.eFiletype in(129,128) and d.iReadInWith=e.iReadInWith and d.guserid in (e.guserid)and CAST(d.dTransferStartDate As DATE)=CAST(e.dTransferStartDate As DATE)) as DCefiletype,
(select COALESCE((sum(convert(decimal,lOriginalFileSize))/1048576),0) from #FileTransfer d where d.eFiletype in(128,129)and d.iReadInWith=e.iReadInWith and d.guserid in (e.guserid)and CAST(d.dTransferStartDate As DATE)=CAST(e.dTransferStartDate As DATE)) as DClFileSize,
(select COUNT(efiletype) from #FileTransfer d where d.eFiletype in(130) and d.iReadInWith=e.iReadInWith and d.guserid in (e.guserid) and CAST(d.dTransferStartDate As DATE)=CAST(e.dTransferStartDate As DATE)) as VUefiletype ,
(select COALESCE((sum(convert(decimal,lOriginalFileSize))/1048576),0) from #FileTransfer d where d.eFiletype in(130)and d.iReadInWith=e.iReadInWith and d.guserid in (e.guserid)and CAST(d.dTransferStartDate As DATE)=CAST(e.dTransferStartDate As DATE)) as VUlFileSize,
(select COUNT(efiletype) from #FileTransfer d where d.eFiletype in(131)and d.iReadInWith=e.iReadInWith and d.guserid in (e.guserid)and CAST(d.dTransferStartDate As DATE)=CAST(e.dTransferStartDate As DATE)) as VDOefiletype ,
(select COALESCE((sum(convert(decimal,lOriginalFileSize))/1048576),0) from #FileTransfer d where d.eFiletype in(131)and d.iReadInWith=e.iReadInWith and d.guserid in (e.guserid)and CAST(d.dTransferStartDate As DATE)=CAST(e.dTransferStartDate As DATE)) as VDOlFileSize
from #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)
--CAST( @dtStartDate AS DATE) and CAST( @dtEndDate AS DATE)
and e.iReadInWith>0
and e.eState=3
and e.eTransferDirection=1
and eFileType in(128,129,130,131)

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2046 Posts

Posted - 10/15/2012 :  01:54:10  Show Profile  Visit jackv's Homepage  Reply with Quote
could you also supply the Exceution Plan?
Some observations:
if possible avoid functions in WHERE
are suitable indexes being used?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

sql-programmers
Posting Yak Master

USA
190 Posts

Posted - 10/15/2012 :  02:35:41  Show Profile  Visit sql-programmers's Homepage  Reply with Quote
Hi Sanjeev,

I have altered your query. I have done following things.
1. Sub queries in the where clause converted into INNER JOIN.
2. Sub queries in the SELECT statement converted into another query your calculations are done in #FileTransfer2 table then joined with #FileTransfer table later.

I think It may complete your functionality. If any error occur in the below query pls inform to us. Thank you.

quote:

SELECT d.guserid ,
d.iReadInWith ,
d.dTransferStartDate ,
SUM(CASE WHEN efiletype = 129
OR efiletype = 128 THEN 1
ELSE 0
END) AS DCefiletype ,
( SUM(CASE WHEN efiletype = 129
OR efiletype = 128
THEN CONVERT(DECIMAL, lOriginalFileSize)
ELSE 0
END) / 1048576 ) AS DClFileSize ,
SUM(CASE WHEN efiletype = 130 THEN 1
ELSE 0
END) AS VUefiletype ,
( SUM(CASE WHEN efiletype = 130
THEN CONVERT(DECIMAL, lOriginalFileSize)
ELSE 0
END) / 1048576 ) AS VUlFileSize ,
SUM(CASE WHEN efiletype = 131 THEN 1
ELSE 0
END) AS VDOefiletype ,
( SUM(CASE WHEN efiletype = 131
THEN CONVERT(DECIMAL, lOriginalFileSize)
ELSE 0
END) / 1048576 ) AS VDOlFileSize
INTO #FileTransfer2
FROM #FileTransfer d
GROUP BY d.guserid ,
d.iReadInWith ,
d.dTransferStartDate

SELECT e.iReadInWith AS isourceid ,
'1' iSP ,
'SP' sSP ,
COALESCE(DCefiletype, 0) DCefiletype ,
COALESCE(DClFileSize, 0) DClFileSize ,
COALESCE(VUefiletype, 0) VUefiletype ,
COALESCE(VUlFileSize, 0) VUlFileSize ,
COALESCE(VDOefiletype, 0) VDOefiletype ,
COALESCE(VDOlFileSize, 0) VDOlFileSize
FROM #FileTransfer e
LEFT JOIN #FileTransfer2 f ON e.guserid = f.guserid
AND e.iReadInWith = f.iReadInWith
AND CAST (e.dTransferStartDate AS DATETIME) = CAST (f.dTransferStartDate AS DATETIME)
INNER JOIN Users u ON e.gUserID = u.gUserID
INNER JOIN Account a ON u.lAccountID = a.lAccountID
INNER JOIN InvoiceOrganisation SP ON a.lInvoiceOrganisation = sp.lInvoiceOrganisation
AND ( CHARINDEX(','
+ LTRIM(RTRIM(CAST(InvoiceOrganisation.lInvoiceOrganisation AS VARCHAR)))
+ ',',
','
+ LTRIM(RTRIM(@sSPID))
+ ',') > 0 )
WHERE e.iReadInWith > 0
AND e.eState = 3
AND e.eTransferDirection = 1
AND eFileType IN ( 128, 129, 130, 131 )



SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Edited by - sql-programmers on 10/15/2012 02:37:23
Go to Top of Page

sanjeev124
Starting Member

5 Posts

Posted - 10/15/2012 :  04:56:57  Show Profile  Reply with Quote
Hi i have updated that query in following way:

select e.iReadInWith as isourceid,'1' iSP, 'SP' sSP,convert (varchar, dTransferStartDate,101) dTransferDate
from #FileTransfer e
INNER JOIN Users u ON e.gUserID = u.gUserID
INNER JOIN Account a ON u.lAccountID = a.lAccountID
INNER JOIN InvoiceOrganisation SP ON a.lInvoiceOrganisation=SP.lInvoiceOrganisation and (charindex(','+ltrim(rtrim(cast(InvoiceOrganisation.lInvoiceOrganisation as varchar)))+',', ','+ltrim(rtrim(@sSPID))+',') > 0)
INNER JOIN
(
select d.iReadInWith,d.dTransferStartDate,d.guserid,
DCefiletype = COUNT(case when d.eFiletype in (128,129) then eFileType end),
DClFileSize = SUM(case when d.eFileType in (128, 129) then CONVERT(DECIMAL, lOriginalFileSize)/1048756 end),
VUefiletype = COUNT(case when d.eFiletype in (130) then eFileType end),
VUlFileSize = SUM(case when d.eFileType in (130) then CONVERT(DECIMAL, lOriginalFileSize)/1048756 end),
VDOefiletype= COUNT(case when d.eFiletype in (131) then eFileType end),
VDOlFileSize = SUM(case when d.eFileType in (131) then CONVERT(DECIMAL, lOriginalFileSize)/1048756 end)
from #FileTransfer d
group by d.iReadInWith, convert(varchar(10), d.dTransferStartDate,101),d.guserid
)d on d.iReadInWith=e.iReadInWith
and d.guserid=e.guserid
and CAST(d.dTransferStartDate As DATE)=CAST(e.dTransferStartDate As DATE)
and e.iReadInWith>0
and e.eState=3
and e.eTransferDirection=1
and CAST(e.dTransferStartDate As DATE) >=CAST(@dtStartDate As DATE) and CAST(e.dTransferStartDate as DATE)<=CAST(@dtEndDate as DATE)
and eFileType in(128,129,130,131)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/15/2012 :  22:39:22  Show Profile  Reply with Quote
using join conditions like below

a.lInvoiceOrganisation=SP.lInvoiceOrganisation and (charindex(','+ltrim(rtrim(cast(InvoiceOrganisation.lInvoiceOrganisation as varchar)))+',', ','+ltrim(rtrim(@sSPID))+',') > 0)
can really hurt performance in case of large datasets

why not rewrite it as LIKE condition instead?

Also make sure you always specify a length while casting to varchar

see

http://visakhm.blogspot.com/2010/02/importance-of-specifying-length-in.html

also no need of converting to varchar for stripping off timepart from the date

just use like DATEADD(dd,DATEDIFF(dd,0,dTransferStartDate),0) and use format functions at your front end to get it in format you want

http://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000