February 24, 2009 at 8:30 am
I have following SP that is taking time, is there any efficient way to write this SP?
Like i may change the sp name from sp_UpdateSession to usp_UpdateSession
use BEGIN and END for whole SP
use SET NOCOUNT ON so that it may not return text to client
And what else ?
CREATE PROC sp_UpdateSession
(
@Session_id INT,
@Url varchar(400),
@Querystring varchar(500),
@Page_name varchar(100),
@Page_title varchar(200) ,
@CookieValue varchar(5000),
@trackingid numeric(9) OUTPUT
)
AS
DECLARE @Page_id as numeric(9)
SELECT @Page_id = Page_Id
FROM tbl_Tracking_Webpages
WHERE Page_Name like @Page_name
IF @Page_id is null
BEGIN
INSERT INTO tbl_Tracking_Webpages(Page_Name, Page_Title)
VALUES(@Page_name,@Page_title)
SELECT @Page_id = Page_Id
FROM tbl_Tracking_Webpages
WHERE Page_Name like @Page_name
END
INSERT INTO tbl_Tracking_dtl(Session_Id, Page_Id, DateStamp, URL, QueryString, CookieValue)
VALUES(@Session_id, @Page_id, GetDate(), @url, @Querystring, @CookieValue)
SET @trackingid = @@identity
February 24, 2009 at 8:43 am
Is Page_ID an identity field?
- 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 24, 2009 at 9:18 am
I'd start with something like this:
IF NOT EXISTS(SELECT 1 FROM dbo.tbl_Tracking_WebPages WHERE Page_Name = @Page_Name)
BEGIN
INSERT INTO
tbl_Tracking_Webpages (Page_Name, Page_Title)
VALUES
(
@Page_name,
@Page_title
)
SET @page_Id = SCOPE_IDENTITY()
END
SELECT @page_Id
INSERT INTO
dbo.tbl_Tracking_dtl
(
Session_Id,
Page_Id,
DateStamp,
URL,
QueryString,
CookieValue
)
VALUES
(
@Session_id,
@Page_id,
GetDate(),
@url,
@Querystring,
@CookieValue
)
/* scope Identity is more reliable than @@Identity
look it up in BOL */
SET @trackingid = SCOPE_IDENTITY()
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 25, 2009 at 9:22 am
Jack Corbett (2/24/2009)
I'd start with something like this:
IF NOT EXISTS(SELECT 1 FROM dbo.tbl_Tracking_WebPages WHERE Page_Name = @Page_Name)BEGIN
INSERT INTO
tbl_Tracking_Webpages (Page_Name, Page_Title)
VALUES
(
@Page_name,
@Page_title
)
SET @page_Id = SCOPE_IDENTITY()
END
SELECT @page_Id
Jack, Is your @page_Id not going to be null if the IF returns FALSE?
February 25, 2009 at 9:33 am
David McKinney (2/25/2009)
Jack Corbett (2/24/2009)
Jack, Is your @page_Id not going to be null if the IF returns FALSE?
That's why that is just a starting point 😀
Really an oversight on my part. Add this to my previous IF:
Else
Begin
SELECT
@page_id = Page_Id
FROM
dbo.tbl_Tracking_Webpages
WHERE
Page_Name = @Page_name
End
Or do this for the second insert, which eliminates the need for the Page_ID variable.
INSERT INTO dbo.tbl_Tracking_dtl
(
Session_Id,
Page_Id,
DateStamp,
URL,
QueryString,
CookieValue
)
SELECT
Session_id,
Page_id,
GetDate(),
@url,
@Querystring,
@CookieValue
FROM
dbo.tbl_Tracking_Webpages
WHERE
Page_Name = @Page_Name
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 26, 2009 at 12:18 am
I would assume that in the majority of cases you will not be creating a new page - you will just be inserting into the tbl_Tracking_dtl.
For that reason, it would perhaps be worth doing the insert into tbl_Tracking_dtl as Jack stated in the previous post first - wrapped in a try...catch, and if there's a foreign key error with the tbl_Tracking_Webpages, then insert the new page.
Regards,
David.
February 26, 2009 at 12:23 am
Afterthought, if the proc is taking too long, perhaps you should be focusing on the indexes on the tables rather than the proc itself!
February 27, 2009 at 8:38 am
Just an observation:
The SELECT has LIKE in the WHERE clause which implies that the @Page_name has a wildcard (%).
But when INSERTing the page @Page_name is sent in intact (which seems to imply that it's a complete string -- no wildcards).
If this is true then I would use = instead of LIKE and make sure there is an index on Page_name.
March 3, 2009 at 12:01 pm
As your SP has several operations - figuring out what is actually the slow part is probably a good way to start instead of just rewriting it in a number of ways to see what happens 😉
Activate "view actual execution plan" and type "set statistics io on" in your query analyzer, and then run each statement (copied from your SP) separately to inspect how long they take to run, the number of reads and how the execution plan looks.
Based on the SP there are a number of ways to rewrite it (as some has suggested), but there's no obvious reason it should be very slow except the LIKE part, assuming that column doesn't have a usable index and/or the table is huge.
Some information that might help: Your table size, what indexes you have on it - and last but not least - what you mean when you say slow? 🙂 How long does it take to execute, how many reads? This would help identify wether you're just looking for fine tuning, or if you're doing something seriously wrong 🙂
Thorbjorn Kvam,
Project manager and DBA (design) at Payex (http://www.payex.com)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply