July 8, 2010 at 2:18 pm
Ok so I will try to keep a long story short yet provide the details.
recently I was reveiwing some reporting services reports when I cam across one with a poorly written Query attached to it. the wuery was taking around 10 minutes to run.
long story short the report is used to pull accounts that are ready to be disconnected for non payment it was doing this by pulling the max posting date for each account from a very large table. the posting date field was not indexed so this was talking a very very long time.
So I changed the query to pull the last record for each account out of the large table using row_number and holding these records in a CTE I then replaced the large table with the CTE and poof the 10 min query now runs in 2 seconds. Fealing good about myself I set out to implement my improved query. Here is were things go all wrong.
After placing the query in the dataset in reporting services I receive the following message
The OVER SQL Construct or statment is not supported.
Ok so reporting services can not deal with the ROW_NUMBER function no big deal I will just place the query in a SP and call that.
OH Wait I can not create the SP because I am using a CTE!
I would have to terminate the create stored procedure to start the CTE.
Start bashing head on desk!:crazy:
So Here is the question, How should I handle this? I could take out the CTE and store the results in a table variable but I wanted to get some oppinoins on the best way to handle this.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
July 8, 2010 at 2:38 pm
dan don't forget a CTE is just a named sub select...you can move the CTE equivalent into the query as a sub query.
;with CTE AS (select other stuff...)
select stuff from CTE
is the same as
select stuff from (select other stuff...)CTE
Lowell
July 8, 2010 at 2:47 pm
Please post query, table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2010 at 2:59 pm
Thanks for the replies
I am using the CTE in a join and I have taken care of this for now by dupming the results into a table variable rather than a CTE interesting factoid is that doing this actually took the Query from 2 second run time to 6 second run time.
Gail I apologize I did not post all the particulars since I have a working query my question more was what is the best way to use it since Reporting services apparently does not support the ROW_NUMBER function.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
July 8, 2010 at 3:03 pm
Stick the code in a stored procedure, call the stored procedure from SSRS. That way you can use anything you like.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2010 at 3:06 pm
that is what I ended up doing but I had to chagne the CTE to a table varible.
Is it possible to put a CTE in a stored procedure? I kept getting an error since all previous statements are expected to be terminated before the with statement.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
July 8, 2010 at 3:21 pm
Dan.Humphries (7/8/2010)
Is it possible to put a CTE in a stored procedure? I kept getting an error since all previous statements are expected to be terminated before the with statement.
Yes, absolutely.
As in all cases where the CTE is part of a larger batch, the preceding command must be terminated by a ;
Stupid example:
CREATE PROCEDURE TestCTE
AS
SELECT TOP 10 name, type_desc, create_date INTO #someTable
FROM sys.objects
ORDER BY create_date DESC;
WITH CTE AS (
SELECT name, type_desc, ROW_NUMBER() OVER (ORDER BY create_date ASC) AS RowNo
FROM #someTable
)
SELECT name, type_desc FROM CTE WHERE RowNo = 5;
DROP TABLE #someTable;
GO
EXEC TestCTE;
GO
DROP PROCEDURE TestCTE;
Omit the ; after the first select, and you'll get the error you mentioned.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2010 at 3:33 pm
What if the CTE is the first thing? for example here is more or less what I am doing.
I tried placing a ; after the SET NOCOUNT ON but I get the error.
create procedure [dbo].[sp_rptCollectionNotice]
(@cycle varchar(2))
AS
SET NOCOUNT ON
WITH PullMaxPrintdate (account,customer,PRINTNOTICE,BALANCEOWING) AS
(
select t.account,t.customer,t.PRINTNOTICE,BALANCEOWING
from
(
Select
c_account,c_customer,D_PRINTNOTICE,BALANCEOWING,
ROW_NUMBER() OVER(Partition BY c24.c_account,c24.c_customer ORDER BY c24.D_PRINTNOTICE desc) as NoticeOrder
from PaymentTable
) t
where t.NoticeOrder=1
)
Select
--Insert lengthy account data here
from accountTable
join --there are multiple joins here based on need for details about accoutn and or customer
join --join to CTE to pull latest records
Where --this section is rather massive
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
July 8, 2010 at 3:43 pm
July 8, 2010 at 3:46 pm
you need a semi-colon like this:
SET NOCOUNT ON;
July 8, 2010 at 3:48 pm
Works fine (as in creates without syntax error) for me
create procedure [dbo].[sp_rptCollectionNotice]
(@cycle varchar(2))
AS
SET NOCOUNT ON;
WITH PullMaxPrintdate (account,customer,PRINTNOTICE,BALANCEOWING) AS
(
select t.account,t.customer,t.PRINTNOTICE,BALANCEOWING
from
(
Select
c_account,c_customer,D_PRINTNOTICE,BALANCEOWING,
ROW_NUMBER() OVER(Partition BY c24.c_account,c24.c_customer ORDER BY c24.D_PRINTNOTICE desc) as NoticeOrder
from PaymentTable
) t
where t.NoticeOrder=1
)
SELECT ...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2010 at 3:53 pm
GilaMonster (7/8/2010)
As in all cases where the CTE is part of a larger batch, the preceding command must be terminated by a ;
I order to not forget to terminate the previous command with a ; I have made a "rule" for myself to move the ; to the beginning of the WITH statement. This way I don't get all the "meaningless" parse errors I used to get.
select col1, col2 from table99
;with cte as
(
select 1 as col
)
select * from cte
July 8, 2010 at 4:12 pm
July 8, 2010 at 4:23 pm
Nils Gustav Stråbø (7/8/2010)
GilaMonster (7/8/2010)
As in all cases where the CTE is part of a larger batch, the preceding command must be terminated by a ;I order to not forget to terminate the previous command with a ; I have made a "rule" for myself to move the ; to the beginning of the WITH statement. This way I don't get all the "meaningless" parse errors I used to get.
select col1, col2 from table99;with cte as
(
select 1 as col
)
select * from cte
I prefer not to, especially when posting here as it gives the incorrect impression that a CTE must start with a ;. I'm getting myself into the habit of always terminating every statement with a ;, as more and more language features are starting to require them. Eg a MERGE must be terminated with a ;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2010 at 4:27 pm
Actually a good point Gail. Now I have to change my bad habit again 😎
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply