December 8, 2016 at 3:42 am
No graphics. It's as if it's stuck in an infinite loop of some sort. It's only a small report that they export to excel and it's filtered down to just one customer and between certain dates. When I run select * from carouseldailymileages in SQL it brings back all the data straight away.
December 8, 2016 at 3:52 am
paul 69259 (12/8/2016)
No graphics. It's as if it's stuck in an infinite loop of some sort. It's only a small report that they export to excel and it's filtered down to just one customer and between certain dates. When I run select * from carouseldailymileages in SQL it brings back all the data straight away.
This sounds like a problem with your SSRS report. if the query runs fine in SSMS, SSRS will have no problems running it either.
How are you running the query, as an SP or direct SQL? I'm assuming it's also parameterised, however, the query I've given doesn't have any variables, how are you achieving this?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 8, 2016 at 3:55 am
it is something to do with that line
WHERE J.JOBID IN (SELECT sq.JOBID FROM dbo.Drops sq)
that it doesn't like
December 8, 2016 at 3:59 am
I use the view to get the bulk of what I need that allows me to use a simple query in SSRS. This is the query in SSRS
SELECT
CarouselDailyMileages.WEEKDAY
,CarouselDailyMileages.[DATE]
,CarouselDailyMileages.Destination
,CarouselDailyMileages.Mileage
,CarouselDailyMileages.[Sale Price]
,CarouselDailyMileages.[Cost Price]
,CarouselDailyMileages.[Vehicle Type]
,CarouselDailyMileages.Reference
,CarouselDailyMileages.requestedby
,CarouselDailyMileages.referenceflag
,CarouselDailyMileages.JOBNUMBER
FROM
CarouselDailyMileages
where
CarouselDailyMileages.[DATE] >= @StartDate
AND CarouselDailyMileages.[DATE] <= @EndDate
AND CarouselDailyMileages.customerid = 1136
The only other thing in SSRS is some font formatting where I change the colours of the post codes depending on the reference flag number
December 8, 2016 at 4:06 am
paul 69259 (12/8/2016)
it is something to do with that line
WHERE J.JOBID IN (SELECT sq.JOBID FROM dbo.Drops sq)that it doesn't like
That statement should have very little effect on your run time, considering you had a join before. This should create a Left Semi Join. If the time is taking a ages to run, it doesn't sound like that's happening.
It also still does not make any sense that your above statement runs very quickly in SSMS and doesn't in SSRS. Simply, that doesn't happen. if something is fast in SSMS, it's fast in SSRS (as SSRS simply runs the SQL). If it's not, then something is different between what you're running in SSRS and SSMS.
Unfortunately, I can't trouble shoot that for you, as I have no DDL with Sample Data, nor do I have your report's rdl.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 8, 2016 at 4:17 am
Ok Thom, no problem. Thank you taking the time to help me out.
December 8, 2016 at 4:32 am
I decided to just let it run this time instead of killing it and it is working it just takes about 5 minutes to run. Brilliant. thank you
December 8, 2016 at 5:14 am
I think the reason it's taking so long to run is that I have put that line in the wrong place.
Whats happening now is that if a job has extra drops it is putting an awful lot of postcodes into the destination field. It has the correct collection and delivery post codes from the jobs table but then it is repeating the same two random postcodes over and over again in the field when it tries to get the post codes from the drops table and this is why it takes so long to run.
is this the correct way?
ISNULL(STUFF((SELECT ', ' + d.POSTCODE
FROM dbo.Drops d
WHERE J.JOBID IN (SELECT sq.JOBID FROM dbo.Drops sq)
FOR XML PATH('')),1,1,''),'')
) AS [Destination],
December 8, 2016 at 7:07 am
Thom A (12/8/2016)
paul 69259 (12/8/2016)
it is something to do with that line
WHERE J.JOBID IN (SELECT sq.JOBID FROM dbo.Drops sq)that it doesn't like
That statement should have very little effect on your run time, considering you had a join before. This should create a Left Semi Join. If the time is taking a ages to run, it doesn't sound like that's happening.
It also still does not make any sense that your above statement runs very quickly in SSMS and doesn't in SSRS. Simply, that doesn't happen. if something is fast in SSMS, it's fast in SSRS (as SSRS simply runs the SQL). If it's not, then something is different between what you're running in SSRS and SSMS.
Unfortunately, I can't trouble shoot that for you, as I have no DDL with Sample Data, nor do I have your report's rdl.
In testing, I can't get
WHERE J.JOBID IN (SELECT sq.JOBID FROM dbo.Drops sq) to generate a plan with this part represented by a Left Semi Join. It's always an inner join, and most frequently (with the tables I've been experimenting with), a nested loops inner join.
In any case, the problem is because the correlations are all messed up, and there’s almost certainly a gihugeous Cartesian join as a result - hence the unsubtle performance. Paul, can you post the current revision of the query you are testing? This shouldn’t take more than a few minutes to resolve.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 8, 2016 at 7:19 am
Hi Chris,
This is how it is currently....
alter VIEW [dbo].[CarouselDailyMileages] AS
SELECT DATENAME(DW,J.DELDATEANDTIME) AS [WEEKDAY],
J.DELDATEANDTIME AS [DATE],
(J.colpostcode + ' , ' + J.DELLPOSTCODE + ', ' +
ISNULL(STUFF((SELECT ', ' + d.POSTCODE
FROM dbo.Drops d
--WHERE d.JOBID = J.JOBID
WHERE J.JOBID IN (SELECT sq.JOBID FROM dbo.Drops sq)
FOR XML PATH('')),1,1,''),'')
) AS [Destination],
J.ACTUALMILEAGE AS Mileage,
J.PRICE AS [Sale Price],
CASE WHEN (SELECT COUNT (*)
FROM dbo.jobs j2
INNER JOIN driver dv2 ON dv2.DRIVERPRIMARYID = j2.DRIVERPRIMARYID
WHERE j2.DRIVERCOST = 0
AND j2.JOBID = J.JobID
AND dv2.SUBCONTRACTORINDICATOR = 0) > 0
THEN J.PRICE - (J.PRICE * .30)
ELSE J.DRIVERCOST END AS [COST PRICE],
V.VEHICLE AS [Vehicle Type],
J.JOBREFERENCE AS [Reference],
J.requestedby,
C.customerid,
Dv.employeenumber,J.JOBNUMBER,
CASE WHEN J.requestedby like '%John Deere%' THEN 1 -- Green
WHEN J.requestedby like '%FSL%' THEN 2 -- Blue
WHEN J.requestedby = 'Manroland' THEN 3 -- Orange
-- WHEN J.requestedby = 'John Deere Harvest 2016' THEN 4 --Pink
WHEN J.requestedby = 'Clothing' THEN 5 -- Grey
WHEN J.requestedby = 'Community Playthings' THEN 6 -- Red
WHEN J.requestedby = 'Siemens' THEN 7 -- Black
WHEN J.requestedby = 'Carousel Siemens' THEN 7 --Black
WHEN J.requestedby = 'Siemens - adhoc' THEN 7 -- Black
WHEN J.requestedby = 'OTRS' THEN 8 --purple
WHEN J.requestedby = 'AGCO' THEN 9 -- Navy
WHEN J.requestedby like '%Draeger%' THEN 10 --Dark Red
ELSE 20
end as referenceflag
FROM dbo.Jobs J
INNER JOIN dbo.Drops D ON J.JobID = D.JOBID
INNER JOIN dbo.Vehicle V ON J.VEHICLEID = V.VEHICLEID
INNER JOIN dbo.customer C on J.CUSTOMERID = C.customerid
INNER JOIN dbo.Driver Dv on J.DRIVERPRIMARYID = Dv.DRIVERPRIMARYID
GO
December 8, 2016 at 7:26 am
First thing I'd suggest is removing this line:
INNER JOIN dbo.Drops D ON J.JobID = D.JOBID
As it is no longer referenced in the outer query.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 8, 2016 at 7:34 am
The WHERE clause I asked you to add is in the Sub Query.... It should be at the bottom, where your WHERE clause goes. No wonder it's taking so long, it's passing that query for every row.
Your query should be...
CREATE VIEW [dbo].[CarouselDailyMileages] AS
SELECT DATENAME(DW,J.DELDATEANDTIME) AS [WEEKDAY],
J.DELDATEANDTIME AS [DATE],
(J.colpostcode + ' , ' + J.DELLPOSTCODE + ', ' +
STUFF((SELECT ', ' + d.POSTCODE
FROM dbo.Drops d
WHERE d.JOBID = J.JOBID
FOR XML PATH('')),1,1,'')
) AS [Destination],
J.ACTUALMILEAGE AS Mileage,
J.PRICE AS [Sale Price],
CASE WHEN (SELECT COUNT (*)
FROM dbo.jobs j2
INNER JOIN driver dv2 ON dv2.DRIVERPRIMARYID = j2.DRIVERPRIMARYID
WHERE j2.DRIVERCOST = 0
AND j2.JOBID = J.JobID
AND dv2.SUBCONTRACTORINDICATOR = 0) > 0
THEN J.PRICE - (J.PRICE * .30)
ELSE J.DRIVERCOST END AS [COST PRICE],
V.VEHICLE AS [Vehicle Type],
J.JOBREFERENCE AS [Reference],
J.requestedby,
C.customerid,
Dv.employeenumber,J.JOBNUMBER,
CASE WHEN J.requestedby like '%John Deere%' THEN 1 -- Green
WHEN J.requestedby like '%FSL%' THEN 2 -- Blue
WHEN J.requestedby = 'Manroland' THEN 3 -- Orange
-- WHEN J.requestedby = 'John Deere Harvest 2016' THEN 4 --Pink
WHEN J.requestedby = 'Clothing' THEN 5 -- Grey
WHEN J.requestedby = 'Community Playthings' THEN 6 -- Red
WHEN J.requestedby = 'Siemens' THEN 7 -- Black
WHEN J.requestedby = 'Carousel Siemens' THEN 7 --Black
WHEN J.requestedby = 'Siemens - adhoc' THEN 7 -- Black
WHEN J.requestedby = 'OTRS' THEN 8 --purple
WHEN J.requestedby = 'AGCO' THEN 9 -- Navy
WHEN J.requestedby like '%Draeger%' THEN 10 --Dark Red
ELSE 20
end as referenceflag
FROM dbo.Jobs J
--INNER JOIN dbo.Drops D ON J.JobID = D.JOBID
INNER JOIN dbo.Vehicle V ON J.VEHICLEID = V.VEHICLEID
INNER JOIN dbo.customer C on J.CUSTOMERID = C.customerid
INNER JOIN dbo.Driver Dv on J.DRIVERPRIMARYID = Dv.DRIVERPRIMARYID
WHERE J.JOBID IN (SELECT sq.JOBID FROM dbo.Drops sq)
On the side note, I do get a LEFT SEMI JOIN when doing to following:
USE DevTestDB;
GO
CREATE TABLE Jobs (JOBID INT IDENTITY(1,1),
VehicleID INT,
CustomerID INT,
DRIVERPRIMARYID INT);
GO
CREATE TABLE Drops (DropID INT IDENTITY(1,1),
JobID INT);
GO
CREATE TABLE Vehicle (VehicleID INT IDENTITY(1,1),
Registration VARCHAR(10));
GO
CREATE TABLE Customer (CustomerID INT IDENTITY(1,1),
CustomerName VARCHAR(100));
GO
CREATE TABLE Driver (DriverPrimaryID INT IDENTITY(1,1),
DriverName VARCHAR(100));
GO
INSERT INTO Jobs (VehicleID, CustomerID, DRIVERPRIMARYID)
VALUES (1, 1, 1),
(1, 2, 1),
(2, 3, 2),
(2, 4, 2),
(3, 5, 3);
INSERT INTO Drops (JobID)
VALUES (1),
(1),
(1),
(1),
(2),
(2),
(2);
INSERT INTO Vehicle (Registration)
VALUES ('AA11 AGB'),
('YE12 AGB'),
('AB16 AGB');
INSERT INTO Customer (CustomerName)
VALUES ('Mr Smith'),
('Mrs Jones'),
('Ms Green'),
('Mr Butter'),
('Dr Cook')
INSERT INTO Driver (DriverName)
VALUES ('John Free'),
('Hillary Clinton'),
('Oliver Dover');
SELECT *
FROM dbo.Jobs J
--INNER JOIN dbo.Drops D ON J.JobID = D.JOBID
INNER JOIN dbo.Vehicle V ON J.VEHICLEID = V.VEHICLEID
INNER JOIN dbo.customer C on J.CUSTOMERID = C.customerid
INNER JOIN dbo.Driver Dv on J.DRIVERPRIMARYID = Dv.DRIVERPRIMARYID
WHERE J.JOBID IN (SELECT sq.JOBID FROM dbo.Drops sq)
DROP TABLE Jobs;
DROP TABLE Drops;
DROP TABLE Vehicle;
DROP TABLE Customer;
DROP TABLE Driver;
Query plan attached.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 8, 2016 at 7:35 am
Phil Parkin (12/8/2016)
First thing I'd suggest is removing this line:
INNER JOIN dbo.Drops D ON J.JobID = D.JOBIDAs it is no longer referenced in the outer query.
and then...
alter VIEW [dbo].[CarouselDailyMileages] AS
SELECT
DATENAME(DW,J.DELDATEANDTIME) AS [WEEKDAY],
J.DELDATEANDTIME AS [DATE],
J.colpostcode + ' , ' + J.DELLPOSTCODE + ', ' + ISNULL(d.DropList, 'EMPTY') AS [Destination],
J.ACTUALMILEAGE AS Mileage,
J.PRICE AS [Sale Price],
CASE WHEN x.cnt > 0
THEN J.PRICE - (J.PRICE * .30)
ELSE J.DRIVERCOST END AS [COST PRICE],
V.VEHICLE AS [Vehicle Type],
J.JOBREFERENCE AS [Reference],
J.requestedby,
C.customerid,
Dv.employeenumber,
J.JOBNUMBER,
CASE WHEN J.requestedby like '%John Deere%' THEN 1 -- Green
WHEN J.requestedby like '%FSL%' THEN 2 -- Blue
WHEN J.requestedby = 'Manroland' THEN 3 -- Orange
-- WHEN J.requestedby = 'John Deere Harvest 2016' THEN 4 --Pink
WHEN J.requestedby = 'Clothing' THEN 5 -- Grey
WHEN J.requestedby = 'Community Playthings' THEN 6 -- Red
WHEN J.requestedby = 'Siemens' THEN 7 -- Black
WHEN J.requestedby = 'Carousel Siemens' THEN 7 --Black
WHEN J.requestedby = 'Siemens - adhoc' THEN 7 -- Black
WHEN J.requestedby = 'OTRS' THEN 8 --purple
WHEN J.requestedby = 'AGCO' THEN 9 -- Navy
WHEN J.requestedby like '%Draeger%' THEN 10 --Dark Red
ELSE 20
end as referenceflag
FROM dbo.Jobs J
OUTER APPLY (
SELECT DropList = (
SELECT STUFF((SELECT ', ' + d.POSTCODE
FROM dbo.Drops d
WHERE d.JOBID = J.JOBID -- correlation to outer query
FOR XML PATH('')),1,1,'')
)
) d
CROSS APPLY ( -- if JobID is unique in Jobs table, then remove Jobs table from this subquery and correlate to dbo.Jobs J
SELECT cnt = COUNT (*)
FROM dbo.jobs j2
INNER JOIN driver dv2
ON dv2.DRIVERPRIMARYID = j2.DRIVERPRIMARYID
WHERE j2.DRIVERCOST = 0
AND j2.JOBID = J.JobID -- correlation to outer query
AND dv2.SUBCONTRACTORINDICATOR = 0
) x
-- INNER JOIN dbo.Drops D ON J.JobID = D.JOBID no longer required
INNER JOIN dbo.Vehicle V ON J.VEHICLEID = V.VEHICLEID
INNER JOIN dbo.customer C on J.CUSTOMERID = C.customerid
INNER JOIN dbo.Driver Dv on J.DRIVERPRIMARYID = Dv.DRIVERPRIMARYID
Using APPLY in the FROM list rather than the SELECT list to reference your subqueries offers you the opportunity to examine the values directly and in a convenient manner. Too much faffing around otherwise. You can always put them back into the SELECT list when you're done.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 8, 2016 at 7:37 am
Hi Phil,
I have removed that line and it's got the problem where it takes a long time to run again and puts a lot of postcodes repeating in the "destination" field
December 8, 2016 at 7:40 am
Awesome thank you Chris, that seems to have done the trick for me.
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply