Video Screencast Help

Enhancement Request - eVault Stored Proc

Created: 01 Mar 2010 • Updated: 02 Nov 2010 | 1 comment
Steven_1's picture
1 Agree
0 Disagree
+1 1 Vote
Login to vote
Status: Implemented

In Stored Proc:  usps_OrphanSavesets

Part of the code contains a very poorly performing "like" predicate:
SELECT JournalDeleteTID as JDTID FROM @XMLTable
           INNER JOIN JournalDelete on SavesetID like '%' + JournalDeleteTID
           WHERE DeletionStatus = 0

Can this be changed to eliminate the "%" on the front-end?

This is extremely CPU intensive and we need to limit the number of CPU's SQL Server has access to while this runs (each individual execution is a few seconds, but cumulatively, they take days). 
The following is the entire parameter passed to the stored proc:
exec usps_OrphanSavesets '<?xml version="1.0" encoding="UTF-16"?>
<ROOT><ITEM TID="FC13CCCD-12B6-4DB7-B81D-161301C52950" JDTID="FC13CCCD12B64DB7B81D161301C5295"/><ITEM TID="B0DE105C-A812-4DD0-85D4-F40D92AAA100"
JDTID="B0DE105CA8124DD085D4F40D92AAA10"/><ITEM TID="5778A0B7-7598-42CD-AAEC-6076B8AD3FE0" JDTID="5778A0B7759842CDAAEC6076B8AD3FE"/><ITEM
TID="6B4FAF7E-BC7E-4DBD-BB2B-42BF020661D0" JDTID="6B4FAF7EBC7E4DBDBB2B42BF020661D"/><ITEM TID="36704543-9F69-4580-86BD-4715BD09A240"
JDTID="367045439F69458086BD4715BD09A24"/><ITEM TID="AE613976-976B-4B04-8E99-432A83BDD4E0" JDTID="AE613976976B4B048E99432A83BDD4E"/><ITEM
TID="CAC3A2FA-958B-4CD0-8387-B1E551DB1B00" JDTID="CAC3A2FA958B4CD08387B1E551DB1B0"/><ITEM TID="02FD41FC-5843-4D76-B712-03A7FF8E50C0"
JDTID="02FD41FC58434D76B71203A7FF8E50C"/><ITEM TID="7459640A-C9E4-4C04-9AA0-215D34644A80" JDTID="7459640AC9E44C049AA0215D34644A8"/><ITEM
TID="4F219EFB-C15B-41B6-B866-7890DED3F250" JDTID="4F219EFBC15B41B6B8667890DED3F25"/><ITEM TID="93187760-907C-4098-9397-4E75FEA85310"
JDTID="93187760907C409893974E75FEA8531"/><ITEM TID="A84D8A49-CF69-448F-B041-8F0B8AB07300" JDTID="A84D8A49CF69448FB0418F0B8AB0730"/><ITEM
TID="1630C2A7-70A4-47EA-B105-D42547927420" JDTID="1630C2A770A447EAB105D4254792742"/><ITEM TID="FA19841A-B203-4193-A745-52FE9C80FEF0"
JDTID="FA19841AB2034193A74552FE9C80FEF"/><ITEM TID="BD421E78-4A85-4E9B-95E9-F42D18106EE0" JDTID="BD421E784A854E9B95E9F42D18106EE"/><ITEM
TID="C9FED7A0-C57A-495E-8D3D-B1D85C708E70" JDTID="C9FED7A0C57A495E8D3DB1D85C708E7"/><ITEM TID="79CD0D3B-4CE4-426A-B0B7-0739D3344970"
JDTID="79CD0D3B4CE4426AB0B70739D334497"/><ITEM TID="968878A9-B586-4DC2-8CCB-D3373E027470" JDTID="968878A9B5864DC28CCBD3373E02747"/><ITEM
TID="6544BF2A-E4FC-4BB0-A779-8E34906D29E0" JDTID="6544BF2AE4FC4BB0A7798E34906D29E"/><ITEM TID="04361EE0-7ACD-43B3-AFAC-3B3DBF7EC8B0"
JDTID="04361EE07ACD43B3AFAC3B3DBF7EC8B"/><ITEM TID="D15CDA87-494F-43C5-9D99-879896AD4450" JDTID="D15CDA87494F43C59D99879896AD445"/><ITEM
TID="3CF63587-0171-44EE-A9AC-4DF44EBBFE90" JDTID="3CF63587017144EEA9AC4DF44EBBFE9"/><ITEM TID="715BF159-AC70-4391-8982-EE1550D94E00"
JDTID="715BF159AC7043918982EE1550D94E0"/><ITEM TID="DFD7881C-DD6B-412E-8EF9-E68F2AA18050" JDTID="DFD7881CDD6B412E8EF9E68F2AA1805"/></ROOT>'

Comments 1 CommentJump to latest comment

Michael Bilsborough's picture

Hi,

You are quite correct in the issue you have raised.  This has been resolved in V7.5 sp6 as well as V8 sp3.

Yours,
Mike Bilsborough
Director, Enterprise Vault Engineering Support

0
Login to vote