December 11, 2007 at 6:59 am
Hi everyone,
I'm quite new to the SQL 200, so apologizes if the question is dumb.
I have to create procedure that will populate a table in the DB based on the values in another table. The two ways of doing this I see are:
1. create a procedure that will only have to statements INSERT and UPDATE. These two statements will incorporate all the logic I need.
2. Create a procedure that will open a cursor, populate a temp field, then construct the text for the query and finally run : exec sp_executesql@text_Sql
Í like the first solution as it's more concise. Are there any reasons for using the second.
The number of records to be updated on every go is around 40,000. The procedure has to run once a 24 hours.
Any comments are appreciated. Also if somebody could provide a link shedding some light on this, I will be very grateful.
Thanks a lot.
Z
December 11, 2007 at 7:07 am
zamiran,
First of all, welcome to the site!
Secondly, can you post a bit more on what your wanting to do? In this case, give us the INSERT and UPDATE statements you're planning to use. Along with the logic to why you think you need to use a cursor.
The reason I say this is that you'd be quite surprised at the level of help that you'll get on this site. And something about your post screams at me "This might be a single statement"
Thanks,
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 11, 2007 at 7:19 am
Hi Jason,
Thanks for the quick answer and welcome! I'm able to put it all in one statement. But my concern was the best practice, as I'm totally new to SQL 2000. What is the rule of thumb? My understanding is that the all-in-one statement will work much faster. Thank you.
Here is the code:
CREATE PROCEDURE za_PRF_Link_update_insert
as
update main_text set theText = detail.link from (select a.main_id as prf_id, ' ' as link
from main_text a, main b,
(select c.main_id as prf_main_id, Left(d.theText,5) as PRN, c.doc_id from main c , main_text d where type_id = 54 and c.main_id = d.main_id) f
where text_type_id = 2 and a.main_id = b.main_id and b.type_id <>54 and Left(a.theText,5) = f.PRN and b.main_id = a.main_id) detail where main_text.text_type_id = 19 and detail.prf_id = main_text.main_id;
insert into main_text (main_id, text_type_id, theText, user_id, dateStamp)
select a.main_id as prf_id,19, ' ' as link,
3, getdate()
from main_text a, main b,
(select c.main_id as prf_main_id, Left(d.theText,5) as PRN, c.doc_id from main c , main_text d where type_id = 54 and c.main_id = d.main_id) f
where text_type_id = 2 and a.main_id = b.main_id and b.type_id <>54 and Left(a.theText,5) = f.PRN and b.main_id = a.main_id and
not exists(Select text1.main_id from main_text text1 where text1.text_type_id = 19 and text1.main_id = a.main_id);
December 11, 2007 at 7:31 am
While I look at this, here's an answer to your question ....
Well, the rule of thumb is that there is no rule of thumb. :w00t:
Each and every problem has many solutions and in some cases one method won't be the best for problem A but will be for problem B. I know this is vague, but it just means that you need to try different methods until you get the hang of SQL Server and Set Based programming (OOPS I said it)....:hehe:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 11, 2007 at 8:53 am
*hint* you can format your SQL by placing
"["code"]"
... your code here ...
"[/"code"]"
OK, if I've read your code correctly, you are updating all of the theText fields to an empty string that match the specfied criteria and then INSERTing an entry for anything that doesn't have an entry with theText as an empty string, but also setting a few other columns.
So here goes my humble recommendations.
1. Use ANSI Standard JOINS. i.e...
.....
FROM main a
LEFT JOIN main_text b
ON a.main_id = b.main_id
-- AND NOT
.....
FROM main c , main_text d
WHERE type_id = 54
AND c.main_id = d.main_id
2. With something this complex, you might think about creating a view. They will simplify your procedures and make things a lot easier to understand. So for the code you posted (and my solution to it...)
CREATE VIEW vw_main
AS
SELECT
a.main_id AS prf_main_id
,LEFT(b.theText,5) AS PRN
,a.doc_id
,a.type_id
FROM main a
LEFT JOIN main_text b
ON a.main_id = b.main_id
3. Since your doing an INSERT and an UPDATE you will have to do to steps/statements. At least until you move to 2005, then you can use the MERGE command.
4. Think about spacing your code, you may already do that and this site just ate it when you posted.
So here goes my attempt at this (using the view created above)... and bare with me, I had to piece this together with little to go on. 😀
UPDATE main_text
SET theText = ' '
FROM
(SELECT
a.main_id AS prf_id
FROM
vw_main a
LEFT JOIN vw_main f
ON a.PRN = f.PRN AND f.TYPE_ID = 54
WHERE
a.text_type_id = 2
AND a.type_id <> 54) detail
WHERE main_text.text_type_id = 19
AND detail.prf_id = main_text.main_id;
INSERT INTO main_text (main_id, text_type_id, theText, user_id, dateStamp)
SELECT
prf_id
,19
,' '
,3
,getdate()
FROM
vw_main a
LEFT JOIN vw_main f
ON a.PRN = f.PRN AND f.TYPE_ID = 54
LEFT JOIN main_text m
ON m.main_id = a.main_id AND m.text_type_id = 19
WHERE text_type_id = 2
AND a.type_id <> 54
AND m.main_id IS NULL;
Let me know how this turns out ... ON YOUR TEST SERVER !:hehe:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 11, 2007 at 9:19 am
Hi Jason,
This is awesome! Thank you very much for all your effort, I'm very grateful.
Your comments are very valuable, I'll try to stick to the way you propose in future.
Could you please expand this topic a bit? What is step/statement? Thanks for this.
3. Since your doing an INSERT and an UPDATE you will have to do to steps/statements. At least until you move to 2005, then you can use the MERGE command.
Another question I have: what is the difference between running straight SQL and running the SQL text through the SQL sp_executesql?
I failed to find any good reference on the thorough description of the procedure optimization techniques. So any hints or links will be much appreciated.
thanks,
zamiran
December 11, 2007 at 9:38 am
In SQL 2000 the only practical way to INSERT and UPDATE data in/into a table is with two individual statements. In 2005, the MERGE command can be used. I'm not 100% on the syntax, but I've read it can be done.
With sp_executeSQL the query optimizer is unable to use any query plans that it has cached. So when you use a procedure that isn't dynamic the optimizer stores it's plan and then when you call that proc again it will not have to reevaluate the procedure. sp_executeSQL will ALWAYS have to be reevaluated each time it's called.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 12, 2007 at 12:21 am
Jason Selburg (12/11/2007)
In 2005, the MERGE command can be used. I'm not 100% on the syntax, but I've read it can be done.
Afaik, Merge didn't make it into SQL 2005 (at least there's nothign in BoL, except replication and the join type) though it was in some of the CTPs.
It should be there in SQL 2008.
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
December 12, 2007 at 1:24 am
I'm returning back to the original question... although there is not a rule that would apply always, IMHO "rule of the thumb" means something different - a rule that can be applied in most cases. And I would formulate such rule as "start thinking about cursor only if you are unable to do it set-based, or if your set-based solution shows very bad performance". Many people say "never use cursors", but other say that sometimes they can be helpful - if you are really sure that there is no other way... To be honest, I never really needed a cursor so far.
If you are looking for some info about how to write good-performing SQL queries, you can start looking on these forums using Search. Search for "RBAR", "triangular join", "hidden cursor" or "Tally". You should find a few pointers on how to avoid code with bad performance, why and how to avoid cursors, etc.
And one link to lots of useful information about SQL, most interesting parts being probably "Curse and blessings of Dynamic SQL" and "Arrays and lists in SQL Server":
December 12, 2007 at 4:26 am
Thank you All, for all your contribution. It's a very good start for me.
Thanks again,
zamiran
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply