Query Finetuning!!!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query Finetuning!!!!

Author

Topic

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-02-05 : 06:10:42

Hi,
could u please advice/Suggest me on the below sproc for optimization.

This sproc used for like it is doing 2 things.
1.if the user / member coming to our site,lets say member visting our site today 8 times ,then memberhitcount total 8 for this day..next day onwards his count starts from 1 goes on(incremented by 1) the same day hw many times he has been visited like that.

2.We updating the member profile to our database from the Remote database( we used Linked Sever)if member exists In our Db.if not,we inserting it.
please see t he below sproc and say where optimiztaion required.

Alter proc [dbo].[MemberCountDetails_sp]
(
@imember varchar(1000)=null,
@membername varchar(max)=null,
@membershipid varchar(max)=null,
@flag int =null
)
AS
BEGIN

SET NOCOUNT ON
DECLARE @sql varchar(1000)
DECLARE @temp TABLE
(
imember varchar(1000),
membername varchar(1000),
emailid varchar(1000),
mobileno varchar(1000)
)

IF (@flag = 1)
BEGIN

SET NOCOUNT ON
SET @sql = 'SELECT * FROM OPENQUERY(Linkedserver1, ''EXEC DB1.dbo. getMemberDetailFlag1_sp'''''+ @imember+ ''''''' )'
INSERT INTO @temp
EXEC (@sql)

END
ELSE
BEGIN
SET NOCOUNT ON
SET @membershipid=@imember
SET @sql = 'SELECT * FROM OPENQUERY(Linkedserver1, ''EXEC Db1.dbo.getMemberDetailFlag2_sp'''''+ @imember+ ''''''' )'
INSERT INTO @temp
EXEC (@sql)
END

SET @membername = (SELECT membername FROM @temp WHERE imember = @imember)

---- Start MemberHit Count ------
declare @cnt bigint
declare @total bigint
set @cnt = 0
select @cnt = count from MemberHitCount_tbl
where imember=@imember and convert(varchar(50),createdDate,103)=convert(varchar(50),getdate(),103)

if (@cnt=0)
begin
SET NOCOUNT ON
INSERT INTO MemberHitCount_tbl
(
imember,
membername,
createdDate,
count,
flag
)
VALUES
(
@imember,
@membername,
getdate(),
1,
@flag
)
end
else
begin
SET NOCOUNT ON
set @total=@cnt + 1;

update MemberHitCount_tbl set count=@total , membername = @membername
where imember=@imember and convert(varchar(50),createdDate,103)=convert(varchar(50),getdate(),103)
end
------ End Member Hit count

-------- start Member Detail Update
declare @cntmem int
set @cntmem =0
select @cntmem = count(1) from mepMemberdet_tbl where imember = @imember

if(@cntmem =0)
Begin
insert into Memberdet_tbl
(
imember,
membername,
memeberemail,
memberphone,
memberflag,
imembership,
createdate,
mcpflag
)
(
select imember,membername,emailid,mobileno,@flag as memberflag, @membershipid as imemebersip,getdate() as createadate,'N' as mcpflag from @temp
)
end
-------- end Member Detail Update

-------- start Member Detail Update
else
begin
SET NOCOUNT ON
update rt
set
rt.membername = t.membername,
rt.memeberemail = t.emailid,
rt.memberphone = t.mobileno,
rt.imembership = @membershipid
from mepMemberdet_tbl rt
inner join @temp t
on t.imember = rt.imember
end
-------- End Member Detail Update
end

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 06:43:18

change

convert(varchar(50),createdDate,103)=convert(varchar(50),getdate(),103)

to

createdDate >= DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0)
AND createdDate < DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 1)

which will use an index (if available) on createdDate column (and save lots of CPU even if no index)

However, do you have to have the TIME in the daily record for MemberHitCount_tbl? If not it would be much more efficient to store the date with NO time and then you can just use an EQUALS test, rather than a range test.

Your logic to detect if a record exists, or not, and the insert one if not seems very tortuous to me.


@DECLARE @Today datetime
SET @Today = DATEADD(Day, DATEDIFF(Day, 0, getdate()), 0)

INSERT INTO MemberHitCount_tbl(imember, membername, createdDate, count, flag)
SELECT @imember, @membername, @Today, 1, @flag
WHERE NOT EXISTS (SELECT * FROM MemberHitCount_tbl WHERE imember=@imember AND createdDate=@Today)
IF @@ROWCOUNT = 0
BEGIN
    UPDATE U
    SET count=count+1
    FROM MemberHitCount_tbl AS U
    WHERE imember=@imember AND createdDate=@Today
END

your logic is flawed anyway, a user accessing from two windows may fail to update the hit count (because you are getting the value, testing it, and then updating to Value+1 - a second instance could do the same, at the same time, and the last-one-would-win - so for two "simultaneous" hits the counter would only be updated by one.

Similarly with the mepMemberdet_tbl insert/update

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-02-05 : 07:26:46

Oh!..Great kristen..i havent think this way....Thanks for the light on

(your logic is flawed anyway, a user accessing from two windows may fail to update the hit count (because you are getting the value, testing it, and then updating to Value+1 - a second instance could do the same, at the same time, and the last-one-would-win - so for two "simultaneous" hits the counter would only be updated by one)

However, do you have to have the TIME in the daily record for MemberHitCount_tbl?
createdDate(Datetime Field)

as per ur sugesstion i have modified in
1.Date Conversion
2.insert or update based on the Rowcount

now its Ok?...please suggest me

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 07:40:16

Yes, createDate is a DATETIME datatype field, but if you only every store the date (i.e. time-part = 00:00:00) then you can compare to "Today" by just the date part - i.e. you don't have to also worry about the time part, which would be different for every record created today, of course.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-02-05 : 07:53:51

Thank you very much for your speedy response.As per ur advice i have chnaged as below.please have a look at below.
Alter proc [dbo].[MemberCountDetails_sp]
(
@imember varchar(1000)=null,
@membername varchar(max)=null,
@membershipid varchar(max)=null,
@flag int =null
)
AS
BEGIN

SET NOCOUNT ON
DECLARE @sql varchar(1000)
DECLARE @temp TABLE
(
imember varchar(1000),
membername varchar(1000),
emailid varchar(1000),
mobileno varchar(1000)
)

IF (@flag = 1)
BEGIN

SET NOCOUNT ON
SET @sql = 'SELECT * FROM OPENQUERY(Linkedserver1, ''EXEC DB1.dbo. getMemberDetailFlag1_sp'''''+ @imember+ ''''''' )'
INSERT INTO @temp
EXEC (@sql)

END
ELSE
BEGIN
SET NOCOUNT ON
SET @membershipid=@imember
SET @sql = 'SELECT * FROM OPENQUERY(Linkedserver1, ''EXEC Db1.dbo.getMemberDetailFlag2_sp'''''+ @imember+ ''''''' )'
INSERT INTO @temp
EXEC (@sql)
END

SET @membername = (SELECT membername FROM @temp WHERE imember = @imember)

---- Start MemberHit Count ------
declare @cnt bigint
declare @total bigint
set @cnt = 0
select @cnt = count from MemberHitCount_tbl
where imember=@imember and createdDate >= DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0)
AND createdDate < DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 1)

if (@cnt=0)
BEGIN
DECLARE @Today datetime
SET @Today = DATEADD(Day, DATEDIFF(Day, 0, getdate()), 0)

INSERT INTO MemberHitCount_tbl(imember, membername, createdDate, count, flag)
SELECT @imember, @membername, @Today, 1, @flag
WHERE NOT EXISTS (SELECT * FROM MemberHitCount_tbl WHERE imember=@imember AND createdDate=@Today)
IF @@ROWCOUNT = 0
BEGIN
UPDATE U
SET count=count+1
FROM MemberHitCount_tbl AS U
WHERE imember=@imember AND createdDate=@Today
END
END------ End Member Hit count

-------- start Member Detail Update
declare @cntmem int
set @cntmem =0
select @cntmem = count(1) from Memberdet_tbl where imember = @imember

if(@cntmem =0)
Begin
insert into Memberdet_tbl
(
imember,
membername,
memeberemail,
memberphone,
memberflag,
imembership,
createdate,
mcpflag
)
(
select imember,membername,emailid,mobileno,@flag as memberflag, @membershipid as imemebersip,getdate() as createadate,'N' as mcpflag from @temp
)
end
-------- end Member Detail Update

-------- start Member Detail Update
else
begin
SET NOCOUNT ON
update rt
set
rt.membername = t.membername,
rt.memeberemail = t.emailid,
rt.memberphone = t.mobileno,
rt.imembership = @membershipid
from mepMemberdet_tbl rt
inner join @temp t
on t.imember = rt.imember
end
-------- End Member Detail Update
end

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-02-05 : 08:00:16

Sorry..i have changes the Member Detail updations too as like using Rowcount..please see nw
Alter proc [dbo].[MemberCountDetails_sp]
(
@imember varchar(1000)=null,
@membername varchar(max)=null,
@membershipid varchar(max)=null,
@flag int =null
)
AS
BEGIN

SET NOCOUNT ON
DECLARE @sql varchar(1000)
DECLARE @temp TABLE
(
imember varchar(1000),
membername varchar(1000),
emailid varchar(1000),
mobileno varchar(1000)
)

IF (@flag = 1)
BEGIN

SET NOCOUNT ON
SET @sql = 'SELECT * FROM OPENQUERY(Linkedserver1, ''EXEC DB1.dbo. getMemberDetailFlag1_sp'''''+ @imember+ ''''''' )'
INSERT INTO @temp
EXEC (@sql)

END
ELSE
BEGIN
SET NOCOUNT ON
SET @membershipid=@imember
SET @sql = 'SELECT * FROM OPENQUERY(Linkedserver1, ''EXEC Db1.dbo.getMemberDetailFlag2_sp'''''+ @imember+ ''''''' )'
INSERT INTO @temp
EXEC (@sql)
END

SET @membername = (SELECT membername FROM @temp WHERE imember = @imember)

---- Start MemberHit Count ------
declare @cnt bigint
declare @total bigint
set @cnt = 0
select @cnt = count from MemberHitCount_tbl
where imember=@imember and createdDate >= DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0)
AND createdDate < DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 1)

if (@cnt=0)
BEGIN
DECLARE @Today datetime
SET @Today = DATEADD(Day, DATEDIFF(Day, 0, getdate()), 0)

INSERT INTO MemberHitCount_tbl(imember, membername, createdDate, count, flag)
SELECT @imember, @membername, @Today, 1, @flag
WHERE NOT EXISTS (SELECT * FROM MemberHitCount_tbl WHERE imember=@imember AND createdDate=@Today)
IF @@ROWCOUNT = 0
BEGIN
UPDATE U
SET count=count+1
FROM MemberHitCount_tbl AS U
WHERE imember=@imember AND createdDate=@Today
END
END------ End Member Hit count

-------- start Member Detail Update
declare @cntmem int
set @cntmem =0
select @cntmem = count(1) from mepMemberdet_tbl where imember = @imember

if(@cntmem =0)
Begin
insert into mepMemberdet_tbl
(
imember,
membername,
memeberemail,
memberphone,
memberflag,
imembership,
createdate,
mcpflag
)

select imember,membername,emailid,mobileno,@flag as memberflag, @membershipid as imemebersip,getdate() as createadate,'N' as mcpflag from @temp
WHERE NOT EXISTS (SELECT * FROM mepMemberdet_tbl where imember = @imember)
IF @@ROWCOUNT = 0
BEGIN
update rt
set
rt.membername = t.membername,
rt.memeberemail = t.emailid,
rt.memberphone = t.mobileno,
rt.imembership = @membershipid
from mepMemberdet_tbl rt
inner join @temp t
on t.imember = rt.imember
END
END

END

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 08:01:53

Get rid of


set @cnt = 0 
select @cnt = count from MemberHitCount_tbl 
where imember=@imember and createdDate >= DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0)
AND createdDate < DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 1)

if (@cnt=0) 
BEGIN

and the corresponding END lower down. This is not needed.

I recommend that if you do not understand the suggestions I have made that you don't use them - I'm not around to maintain the code, that's your job, so important that you understand it!!

Then change the Insert / Update of Memberdet_tbl table similarly.

And please format your code samples using the [CODE] tag - that will make it much easier to read.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-02-05 : 08:09:07

Thank you very much kristen.your help much appreciated..please dont mistake me ( not around to maintain the code, that's your job).sorry.

i have changed as per ur help.Thanks Again Kristen.

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 08:15:14

let me know if the performance it better than before

(Or maybe you didn't have a performance problem, just wanted to optimise it)

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-02-05 : 08:21:35

Yes ofcourse..i had made lot of mistakes in that sproc which doesnt even correct.u pointed out well.u done really good job here.i dont have words to appreciate u kristen.i have learned from u dateconvertion,rowcount,not exists in a very better way.thanks boss.
i never do this kind of mistake in future(hereafter).thanks again for your response...

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 08:23:54

"i have learned from u date convertion"
The whole DATEADD(Day, DATEDIFF(Day, 0, myDate), 0) thing to just get the DATE (with no Time) is really nasty - but it is the best way of doing it ...

"i never do this kind of mistake in future(hereafter"

FANTASTIC! Well done!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources