February 14, 2010 at 9:58 pm
Hi
I have a purplexing problem that need to be looked at and I feel that a fresh set of eye will help
The code is what is causing the problem:
WITH CTE ( [seq], note_list, notetext, length )
AS ( SELECT [seq], CAST( '' AS VARCHAR(8000) ), CAST( '' AS VARCHAR(8000) ), 0
FROM tmp_restrict
GROUP BY [seq]
UNION ALL
SELECT p.[seq], CAST( note_list +
CASE WHEN length = 0 THEN '' ELSE ', ' END + p.notetext AS VARCHAR(8000) ),
CAST( p.notetext AS VARCHAR(8000)), length + 1
FROM CTE c
INNER JOIN tmp_restrict p
ON c.[seq] = p.[seq]
WHERE p.notetext > c.notetext
)
INSERT tmp_note
SELECT [seq], note_list
FROM ( SELECT [seq], note_list,
RANK() OVER ( PARTITION BY [seq] ORDER BY length DESC )
FROM CTE ) D ( [seq], note_list, rank )
WHERE rank = 1 ;
The server returns the result
Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases. I have restarted the server, (power and service). The code is trying to run about 20,000 records into about 7000
I feel the problem is with the CTE query. I have removed all the other temp tables and replaced them with database tables that are created at the start of the stored procedure and then droped at the end.
I can post screen shots if necessary
Any sugestions
Thanks
Dougal
February 15, 2010 at 1:18 pm
Would you mind providing some descriptive sample data together with table def and expected result?
To me it looks like it's not required at all to build those concatenated strings just to get the overall length of a sequence. I think there are much more efficient ways to do it... But I'd like to have some data to play around with...
February 15, 2010 at 1:31 pm
I can't tell for sure without some sample data, but it looks to me like the CTE should end up with an infinite loop in it based on that Join and Where.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 15, 2010 at 3:18 pm
This query creates the follwoing data set. It is the following data set the the CTE query is run against.
CREATE TABLE tmp_restrict
(
[seq] [int],
[seq2] [int],
[seq3] [int],
[BLDGID] [varchar] (6) NOT NULL ,
[LEASID] [varchar] (6) NOT NULL,
[NOTEDATE] [smalldatetime] NOT NULL,
[REF2] [varchar] (7) NOT NULL,
[NOTETEXT] [varchar] (max) NULL
);
INSERT INTO tmp_restrict ( [seq],[seq2],[seq3],[BLDGID],[LEASID],[NOTEDATE],[REF2],[NOTETEXT])
SELECT
DENSE_RANK() over (order by trans_Notemap.mriNoteREF,cti_CMPLRX.XASCK4,cti_CMPLR.CMLRRS,trans_lease2.mriLeaseID,trans_lease2.mriBuildID) as seq,
cti_CMPLRX.XASCK4 as [seq2],
cti_CMPLRX.XASCSQ as seq3,
trans_lease2.mriBuildID,
trans_lease2.mriLeaseID,
CAST(CONVERT(varchar, CONVERT(datetime, CONVERT(varchar,(CONVERT(int, cti_CMPLR.CMLRRS + 19000000)))), 112) AS smalldatetime) as [date],
cast(ltrim(rtrim(trans_Notemap.mriNoteREF))as [varchar])as [note_cat],
cti_CMPLRX.XASCTY as [text]
FROM
cti_CMPLRX
INNER JOIN
cti_CMPLR
INNER JOIN
trans_lease2
ON cti_CMPLR.CMLRTT = trans_lease2.ctiTenant
AND cti_CMPLR.CMLRPJ = trans_lease2.ctiProject
AND cti_CMPLR.CMLRLN = trans_lease2.ctiLease
ON cti_CMPLRX.XASCK1 = cti_CMPLR.CMLRTT
AND cti_CMPLRX.XASCK2 = cti_CMPLR.CMLRPJ
AND cti_CMPLRX.XASCK3 = cti_CMPLR.CMLRLN
AND cti_CMPLRX.XASCK4 = cti_CMPLR.CMLRRN
INNER JOIN
trans_Notemap
ON cti_CMPLR.CMLRTP = trans_Notemap.cti
INNER JOIN
(
SELECT
trans_lease2.mriLeaseID AS led, MAX(trans_lease2.ctispacesequenceNumber) AS maxresults
FROM
trans_lease2
INNER JOIN
mri_LEAS
ON trans_lease2.mriBuildID = mri_LEAS.BLDGID
AND trans_lease2.mriLeaseID = mri_LEAS.LEASID
WHERE
mri_leas.ADDLSPACE = 'N'
GROUP BY
trans_lease2.mriLeaseID
) AS maxresults
ON trans_lease2.ctispacesequenceNumber = maxresults.maxresults
AND trans_lease2.mriLeaseID = maxresults.led
WHERE
cti_CMPLRX.XASCSQ != 0 AND
cti_CMPLR.CMLRVW = 'O' AND
ltrim(rtrim(cti_CMPLRX.XASCK4)) != 'STANDARD'
ORDER BY
cti_CMPLRX.XASCK1,cti_CMPLRX.XASCK2,cti_CMPLRX.XASCK3,cti_CMPLRX.XASCK4,cti_CMPLRX.XASCSQ ;
Results for above query (sample). The total record count is about 20,000 rows
[seq][seq2][seq3][BLDGID][LEASID][NOTEDATE][REF2][NOTETEXT]
6791112005671049372006-03-15 00:00:00USERESTOffice for Building Services - Refer to Clause 4.1
6304412005671049372009-03-14 00:00:00TRMNATNRefer to Clause 9.5
6434512005671049372008-04-15 00:00:00TRMNATNRefer to Clause 14.7
6916112005901053842008-09-15 00:00:00USERESTOffice Accommodation - cl. 4.1/item 10
2185212005901053842008-09-15 00:00:00MISCIncentive to be used for fitout contribution. If fitout less than
2185222005901053842008-09-15 00:00:00MISC$57000 balance to be applied against base rent spread evenly
2185232005901053842008-09-15 00:00:00MISCthroughout lease term - cl. 17/item 17 [/color]
6381412005901053842013-09-14 00:00:00TRMNATNMake good requirements includ. carpet/paint - refer cl. 9.5 5.1(c)(1)
6381422005901053842013-09-14 00:00:00TRMNATN(paint) and 5.1(c)(3) (carpet)
1362512005901053842013-07-14 00:00:00HOLDOVRRefer cl. 9.8
2918612005901053842008-09-15 00:00:00MISCIf Lessor wishes to demolish all or part of bldg 6mnths notice must be
2918622005901053842008-09-15 00:00:00MISCgiven to Lessee - refer cl. 13.2
5560112005421042042011-10-31 00:00:00TRMNATNRefer Clause 25 of Lease for conditions
6258312005781051532017-12-31 00:00:00TRMNATNRefer Clause 9.5 of Lease for conditions
1372512005781051532017-12-31 00:00:00HOLDOVRRefer Clause 9.7 of Lease for conditions +10%
688612005781051532010-01-01 00:00:00ALTERTNRefer Clause 5.1 of Lease for conditions
6937111005031035782009-05-01 00:00:00USERESTPERMITTED USE - IN ACCORDANCE WITH THE CROWN LEASE. Cl. 23/item 22
5974211005031035782011-12-31 00:00:00TRMNATNMAKE GOOD - refer to cls 29 and 67 for requirements.
4232311005031035782009-05-01 00:00:00RELOCATRELOCATION ALLOWANCE - in accordance with relevant retail legislation.
4232321005031035782009-05-01 00:00:00RELOCATNote if we serve a Relocation Notice lessee can either terminate lease
4232331005031035782009-05-01 00:00:00RELOCATor acccept the relocation. Refer cl. 40.8/item 23.
2586411005031035782009-05-01 00:00:00MISCLESSOR CAN TERMINATE LEASE IF THEY DECIDE TO DEMOLISH RENOVATE OR
2586421005031035782009-05-01 00:00:00MISCRECONSTRUCT THE CENTRE OR ANY PART OF THE CENTRE. AT LEAST 6 MONTHS
2586431005031035782009-05-01 00:00:00MISCNOTICE TO BE PROVIDED - refer cl. 41.
5669113005041055212014-03-01 00:00:00TRMNATNRefer Clause 25
344213005041055212009-04-01 00:00:00ALTERTNRefer Clause 33
Example of desired outcome
2185212005901053842008-09-15 00:00:00MISCIncentive to be used for fitout contribution. If fitout less than $57000 balance to be applied against base rent spread evenly throughout lease term - cl. 17/item 17
February 15, 2010 at 4:28 pm
Wouldn't the following code give you the same result?
It should perform a lot better than what you have so far...
Also, you should consider changing the data type for tmp_restrict [NOTETEXT] from [varchar] (max) to [varchar] (8000) (or even lower, depending on the max length you'll need to store).
;WITH cte AS
(
SELECT
seq,
(SELECT ' ' + Notetext FROM tmp_restrict t2 WHERE t2.seq = t1.seq ORDER BY seq3 FOR XML PATH('')) s
FROM tmp_restrict t1
GROUP BY seq
)
SELECT *
FROM cte
ORDER BY len(s) DESC
February 15, 2010 at 4:37 pm
Hi Lutz
The updated code worked a treat (first time) - thank you
Dougal
February 15, 2010 at 4:44 pm
You're welcome!
Sometimes a working (and maybe even faster) solution is simple but we just can't find it... Been there, done that. 😉
February 15, 2010 at 5:57 pm
deveringham (2/15/2010)
Hi LutzThe updated code worked a treat (first time) - thank you
Dougal
The next question would be, do you understand how it works and could you use it to solve a similar problem?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2010 at 11:43 am
Jeff Moden (2/15/2010)
deveringham (2/15/2010)
Hi LutzThe updated code worked a treat (first time) - thank you
Dougal
The next question would be, do you understand how it works and could you use it to solve a similar problem?
If I need to remember how it works I usually go back to one of my posts where I tried to describe the basic concept... 😉
I left that note on the web just in case of amnesia. 😀
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply