I am using SSRS report and use dataset in it. When it execute in less data, result shows in seconds, but when it runs on huge data it takes two minute approximately below mentioned in my query. Please suggest a way weather there is problem in query
or not.
SELECT TOP 1000 VarianceRequestID
,vr.Created
,j.FullName AS Job
,
STUFF
(
(
SELECT DISTINCT ',' + v.Name
FROM (SELECT * FROM VarianceRequestDetail WHERE (TenantID = (SELECT TenantID FROM Tenant WHERE (SQLUserID = SUSER_ID('ReportUser_1_37'))))) vrd
INNER JOIN (SELECT * FROM Vendor WHERE (TenantID = (SELECT TenantID FROM Tenant WHERE (SQLUserID = SUSER_ID('ReportUser_1_37'))))) v ON v.VendorID = vrd.VendorID
WHERE vrd.VarianceRequestID = vr.VarianceRequestID
FOR XML PATH(''), type
).value('.', 'varchar(max)'), 1, 1, ''
) AS Vendors
,vReas.Name ...
Go to the complete details ...