February 6, 2014 at 10:52 pm
Hi,
Below mentioned query, whenever it runs it fills up tempdb space and resulting disk space issues and it's taking more than 10 hours to complete. I have checked the indexes and statistics of the related tables of that query they are not fragmented. Please help me resolving the issue-
Query-
SELECT distinct '0' AS sno, a.iOdfno AS id, a.iAcNo, a.iShipToParty, a.cPType, REPLACE(a.cPType, '62', 'OTS') AS Ptype,
a.cSiteCode, a.iId, a.cPName, a.iId AS scopeid, a.iFranchiseeAcNo,a.icno as ContactNo,
isnull(d.vcAddr_Street,'')+isnull(d.vcaddr_street1,'')+isnull(d.vcaddr_loc,'') as Address ,a.dtExpDate, a.cSONo,
b.vcAcName, c.vcAcName AS FName, d .vcAddr_City, e.vcCityName, ISNULL(a.cUsedFor, '') AS UsedFor, a.iJSNo,
ISNULL(a.vcRodlcNo, '') AS vcRodlcNo, ISNULL(a.dtActDate, '') AS ActDate, IsNull(a.crono, '') AS LinEid,
CASE WHEN a.vcRodlcNo IS NULL THEN 'RODLC NO. not filled'
WHEN a.iFranchiseeAcNo IS NULL THEN 'Franchisee assignment not done'
WHEN a.dtActDate = '1/1/1900' OR a.dtActDate IS NULL THEN 'ACD not filled'
WHEN a.dtActDate IS NOT NULL AND a.dtActDate <> '1/1/1900' THEN 'Complete'
END AS status,
z.cname AS Hub, a.csapcode,case when o.vcfilename is null then 'No' else
'<a href=OpenFileViewer1(''IC'',''' + rtrim(a.iodfno) + ''',''H'','
+ convert( varchar(30), a.iid) + ')> View File </a>' end as vcfile,a.dtjsheet_trigger,isnull(O.dtICUplaod,'') as dtICUplaod ,isnull(a.dtacdentereddate,'') as dtacdentereddate
,isnull(inv.invoiceno,'') as [InvoiceNo],case when o.vcfilename is null then 'No' else 'Yes' end as IcUploadStatus ---,inv1.Invoice_amt,inv1.Invoice_date
FROM
(
select * FROM R_Scope WHERE (iOdfno <> '0-0') and (cPType = '62') AND (cSONo IS NOT NULL)
) a
left outer join rl_jsheet_ots o on a.iid=o.iscopeid
INNER JOIN SFA.dbo.Account b ON a.iAcNo = b.iAcNo
inner join sFA.dbo.codes z ON b.chub = z.ccode
left outer JOIN SFA.dbo.Account c ON c.iAcNo = a.iFranchiseeAcNo
INNER JOIN dbo.R_sitemaster d ON a.cSiteCode = d .vcSite_Code
LEFT OUTER JOIN SFA.dbo.State_City e ON d .vcAddr_City = e.iId
inner join
rec_revenue_sap_new inv on ltrim(rtrim(a.icno))=convert(varchar,inv.icno) and ltrim(rtrim(a.crono))=convert(varchar,inv. crono)-- left outer join
Thanks & Regards
Rahul Singh
February 21, 2014 at 10:40 am
Share the execution plan of the query.
Also check whether the table(s) has required index(s).
February 24, 2014 at 1:07 pm
Post the execution plan ..
--
SQLBuddy
February 24, 2014 at 2:45 pm
http://technet.microsoft.com/en-us/library/ms190646.aspx
To save an execution plan by using SQL Server Management Studio options
Generate either an estimated execution plan or an actual execution plan by using Management Studio.
In the Execution plan tab of the results pane, right-click the graphical execution plan, and choose Save Execution Plan As.
As an alternative, you can also choose Save Execution Plan As on the File menu.
In the Save As dialog box, make sure that the Save as type is set to Execution Plan Files (*.sqlplan).
In the File name box provide a name, in the format <name>.sqlplan, and then click Save.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply