February 9, 2012 at 11:01 am
Hi All,
I am trying to tune the code in the sp here, which I uses around 10 times daily..
CREATE TABLE ActrProfile
( LID int, -- Primary Key
Location varchar(20), -- Foreign Key and Index
ActrID int,-- Foreign Key and Index
LCount int,
UpdateTime datetime)
-- It has 1 billion Records
--I have 4 statements here in the procedure
@ActrID int,
@Lid int,
@Location varchar(20),
@Screen varchar(20),
@counts int)
AS
-- 1) In one condition
Update ActrProfile
set LCount = LCount + @counts
Where Location = @Location
and ActrID = @ActrID
and LID = @Lid
-- 2) If above condition fails
Delete from ActrProfile
where Location = @Location
and ActrID = @ActrID
and LID = @Lid
-- 3) direct statement
Delete from ActrProfile
where Location = @Location
and ActrID = @ActrID
and Lcounts < 1
-- 4) Link with another table
Delete from ActrProfile
where Location in (
Select Location from Actors
where ScreenID= @ScreenID -- It doesn't have any index on it.
)
and actrid = @actrID
GO
--Exec TestOnActr 999999,209,'MyLocation',5,1
-- This is the Procedure..
Now I wanted to tune this fro better performance.
I am thinking of Changing the order in the where clauses...
As LID is the Primary Key I wanted to make sure LID be the first in the where clause.
and actrId, Location in last place...as Location is string
Like below
1)
Update ActrProfile
set LCount = LCount + @counts
Where LID = @Lid
and ActrID = @ActrID
and Location = @Location
And also I wanted to change in rest of the above delete statements...
Does it make Sense?
So Can some one give me suggetions ?
Please inform me, If requreid more information
I would really appreciate that.
February 9, 2012 at 12:05 pm
bvsc (2/9/2012)
I am thinking of Changing the order in the where clauses...As LID is the Primary Key I wanted to make sure LID be the first in the where clause.
and actrId, Location in last place...as Location is string
Won't make a difference. Order of predicates in a where clause is irrelevant.
If you want help optimising, please post exact code, table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
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
February 9, 2012 at 12:31 pm
Won't make a difference. Order of predicates in a where clause is irrelevant.
Thank you Gail Shaw,
And If it doesnt really matter the order in where clause, I Would just use the same code.
Though, I am just wondering how the query being processed that kind of situations..
First It will look into the page where Location = 'MyLocation' stored..
From there it look for ActrID = 999999 ( can have multiple rows on same ActrID)
and then Look for LID (though it is unique)
How Come it is not better If used
'Directly look at LID and check for other two conditions straight away Cause LID is Unique.' ?
I just wanted to make sure, weather I am thinking right way.
Thanks,
bvsc
February 9, 2012 at 12:39 pm
bvsc (2/9/2012)
Though, I am just wondering how the query being processed that kind of situations..First It will look into the page where Location = 'MyLocation' stored..
From there it look for ActrID = 999999 ( can have multiple rows on same ActrID)
and then Look for LID (though it is unique)
No, not at all. The query optimiser generates the best plan it can based on the query, the indexes, the data distribution, etc. The order that you specify the conditions in the where clause does not determine the order they are evaluated.
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
February 9, 2012 at 1:51 pm
No, not at all. The query optimiser generates the best plan it can based on the query, the indexes, the data distribution, etc. The order that you specify the conditions in the where clause does not determine the order they are evaluated.
But Gail Shaw,
I tested the queries against one of the above statements
with
--1)
SET STATISTICS IO ON
SET STATISTICS TIME ON
Update ActrProfile
set LCount = LCount + 1
Where LID = 209
and ActrID = 999999
and Location = 'mylocation'
--2)
SET STATISTICS IO ON
SET STATISTICS TIME ON
Update ActrProfile
set LCount = LCount + 1
Where Location = 'myLocation'
and ActrID = 999999
and LID = 209
Why the elapsed time is showing differnce...
The second query is giving less elapsed Time than the first....
I even tried in reverse order like
--1)
SET STATISTICS IO ON
SET STATISTICS TIME ON
Update ActrProfile
set LCount = LCount + 1
Where Location = 'myLocation'
and ActrID = 999999
and LID = 209
--2)
SET STATISTICS IO ON
SET STATISTICS TIME ON
Update ActrProfile
set LCount = LCount + 1
Where LID = 209
and ActrID = 999999
and Location = 'mylocation'
Still the the same statement (here 1)st one) is giving less elapsed time..
Can I get any reason behind that?
Thank You,
bvsc
February 9, 2012 at 1:54 pm
how many rows generally would your nested statement of
Select Location from Actors
where ScreenID= @ScreenID
return?
If table Actors is large and ScreenID is not indexed, as you say, that might be a point of concern.
February 9, 2012 at 2:01 pm
you might want to throw this in your statements too so you can really get an accurate measurement of a difference.
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
February 9, 2012 at 2:09 pm
how many rows generally would your nested statement of
Select Location from Actors
where ScreenID= @ScreenID
return?
It's Just one row all the times.
If table Actors is large and ScreenID is not indexed, as you say, that might be a point of concern.
Nope, this table is not large, I dont think 7k table make huge role here.
Thank you for a try Geoff.
Thank you,
bvsc
February 9, 2012 at 2:22 pm
Yes, these are the two commands I have been used for test before..
I just tried once again No luck....
But this time
--1)
SET STATISTICS IO ON
SET STATISTICS TIME ON
Update ActrProfile
set LCount = LCount + 1
Where LID = 209
and ActrID = 999999
and Location = 'mylocation'
--2)
SET STATISTICS IO ON
SET STATISTICS TIME ON
Update ActrProfile
set LCount = LCount + 1
Where Location = 'myLocation'
and ActrID = 999999
and LID = 209
The first statement got less elapsed time in both the cases...
Even ran in differnt query windows in the middle I used this DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Anyone any suggessions?
bvsc
February 9, 2012 at 2:23 pm
Did you run more than once and discard the first results (plan and data caching)? Did you run multiple times and average the times (especially elapsed time does vary due to other stuff running on the client machine, network transmission speed, etc, etc)?
Post your results (stats IO and stats time?)
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
February 9, 2012 at 2:31 pm
GilaMonster (2/9/2012)
Post your results (stats IO and stats time?)
Sure, for the 1st stament SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'PossessionsUser'. Scan count 0, logical reads 4, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 76 ms.
(1 row(s) affected)
The second statementSQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'PossessionsUser'. Scan count 0, logical reads 4, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 85 ms.
(1 row(s) affected)
Thank You,
bvsc
February 9, 2012 at 2:42 pm
You cannot draw meaningful comparisons or conclusions with that little data.
4 pages and 0 ms of CPU time. 10ms difference in elapsed time is well within the measuring inaccuracy or absolutely any background processes (especially if you're running that on a desktop machine with a tonne of other stuff running)
Test with a significant amount of data (and 4 pages of data, 32kb is nowhere close to significant), test multiple times. Average results. Look for significant differences in CPU time, reads (if SQL was using an inefficient search mechanism as you suggest, it would show a different number of reads from inappropriate index usage) and give less consideration to elapsed time, as that includes the time to transmit and display the rows.
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
February 9, 2012 at 3:06 pm
You could try to run the two statements in reverse order.
I'm confident "all of a sudden" the query running "fast" in the beginning will be the "slow" one.
What is the reason for fighting for such a minor difference?
Is it possible that the sproc you're trying to optimize is called a few thousand times to process a larger amount of data? In this case I recommend to change the concept to a set based logic (processing all data at once instead of one at the time).
February 9, 2012 at 3:12 pm
Sure, Gail Shaw...
I will test with another application for the results as I can't do much on these statements because LID is PK so its unique,
Location is also unique per Actor, And more over only one result i am looking from this statement(or entire sp)..
Moreover I m kind of agree your Convincing arguments.
By the way thank you very much for resolving my issues.
So,
Can I drop only particular sps execution plans from the cahce ?
Since a while I have been waiting for this solution.. As of I know, I cant.
Because,all kind of these testings on Dev wud be fine. But I cant drop whole plan cache from Prod.
Thank you,
bvsc
February 9, 2012 at 3:22 pm
LutzM (2/9/2012)
You could try to run the two statements in reverse order.I'm confident "all of a sudden" the query running "fast" in the beginning will be the "slow" one.
What is the reason for fighting for such a minor difference?
Is it possible that the sproc you're trying to optimize is called a few thousand times to process a larger amount of data? In this case I recommend to change the concept to a set based logic (processing all data at once instead of one at the time).
I m sure I cant change the concept. While looking at the sp, table structure and indexes I thought I could get better results by order change in the where clause. I felt the process works in the where clause should be the way I explained above in my initiation post.
Just had a try..
Thanks though.
bvsc.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply