December 13, 2007 at 6:55 am
Hi Everyone,
I have a procedure to update and insert a table based on a couple of other quieries. The thing is that I have to implement this procedure accross multiple DBs. In every case I need to know the numeric Id of the row I have to update. Of course the ID for the same data_type is different fro different DBs is different, but the name is the same. So to make a "universal" procedure, I'm populating a couple of variable with select statements resolving ID from the name. The problem is that this way the procedure becomes very slow, compared to the version where I just put the actual values instead of the variables. Ok, this is predictable, but the difference is huge. Whithout variables the procedure runs in less than a sec (1), but with variables it takes it more than 20 sec to complete. Probably I'm doing something wrong. Could you please suggest how to improve the performance? Thank you!
CREATE PROCEDURE za_PRFLink_Updater
AS
DECLARE @PRFLinkID int --get the text_type id for the PRF Link
DECLARE @user-id int--get the user_id for inserting data
DECLARE @DocType int--get the type_id for the Pre-registration form
DECLARE @PreRegNumID int--get the Pre-Registration Num type id
DECLARE @LinkPart1 nvarchar(100)--the first part of the link string
DECLARE @LinkPart2 nvarchar(20)--the second part of the link string
DECLARE @LinkPart3 nvarchar(20)--the third part of the link string
DECLARE @CurrentDate datetime --get current date
--get current date
SET @CurrentDate = getdate()
--Populate link variables
SET @LinkPart1 = '<a href=ShowNotesLinkerLink("MAIN","'
SET @LinkPart2 = '")>'
SET @LinkPart3 = ' '
-- get PRFLink id
SELECT @PRFLinkID = text_type_id
FROM list_text_types
WHERE text_type = 'PRF Link'
-- get userId for the
SELECT @user-id = user_id
FROM list_users
WHERE username = 'Ringtail OTP Administrator'
--get Doc Type Id for the Pre-Reg Form
SELECT @DOCType = type_id
FROM list_types
WHERE type = 'Pre-Registration Form'
--get PreRegNum field type id
SELECT @PreRegNumID = text_type_id
FROM list_text_types
WHERE text_type = '1 Pre-registration Number (D)'
--first run update
UPDATE main_text
SET theText = detail.link
FROM
(SELECT
a.main_id AS prf_id
,@LinkPart1 + LTrim(Str(f.prf_main_id))+ @LinkPart2 +f.PRN+ @LinkPart3 AS link
FROM main_text a
INNER JOIN main b ON a.main_id = b.main_id AND a.text_type_id = @PreRegNumID AND b.type_id <> @DOCType
INNER JOIN (SELECT
c.main_id as prf_main_id
,Left(d.theText,5) as PRN
,c.doc_id
FROM main c
INNER JOIN main_text d ON c.main_id = d.main_id AND c.type_id = @DOCType) f ON Left(a.theText,5) = f.PRN
) detail
WHERE main_text.text_type_id = @PRFLinkID
AND detail.prf_id = main_text.main_id;
-- run an insert
INSERT INTO main_text(main_id, text_type_id, theText, user_id, dateStamp)
SELECT
a.main_id as prf_id
,@PRFLinkID
,@LinkPart1+LTrim(Str(f.prf_main_id))+@LinkPart2+f.PRN+@LinkPart3 as link
,@UserID
,@CurrentDate
FROM
main_text a
INNER JOIN main b ON a.main_id = b.main_id AND a.text_type_id = @PreRegNumID AND b.type_id <> @DOCType
INNER JOIN (SELECT
c.main_id as prf_main_id
,Left(d.theText,5) as PRN
,c.doc_id
FROM main c
INNER JOIN main_text d ON c.main_id = d.main_id AND c.type_id = @DOCType) f ON Left(a.theText,5) = f.PRN
LEFT JOIN main_text m ON m.main_id = a.main_id and m.text_type_id = @PRFLinkID
WHERE m.main_id IS NULL;
GO
December 13, 2007 at 8:09 am
I'm not sure what you mean the same ID is different data types?
That alone could cause issues. Why not make separate stored procs for each database? It's not much work for you and any admin savings could get lost instantly with performance issues or you digging around to make a general procedure instead of 10 specialized ones.
December 13, 2007 at 8:20 am
Hi Steve,
Thanks for the answer. Sorry for not making myself clear. What I mean is: a have to populate a table with the PK from another database, which changes from DB to DB, but the text field corresponding to that PK is the same all accross (i.e. text_type_id , text_type_name - I know what the text_type_name is but I need to insert the text_type_id). So I'm retrieving the id by SELECT three times for all the variables, which makes the procedure run 20 times slower compared to straight values. Of course tailoring the procedure to a particular database would solve the problem. But I was just curious, if there was a way to make it run faster (in terms of dealing with variable) for the sake of universality. Any input is much appreciated.
Thanks!
December 14, 2007 at 8:34 am
I'd have to see the actual databases involved to be sure, but most likely the reason for the slow execution is that the generic code requires the database to use a non-optimum execution plan.
The broader the solution, the less specific the execution plan. With very broad execution plans, the proc can end up taking a long time to run.
Variables pretty much force the engine to run a very broad execution plan and slow it down.
Check your execution plans and see if there are any table scans or index scans in the "universal" solution, which turn into index seeks when you make a more specific proc.
- 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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply