Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

SQLServerCentral.com Best Practices Clinic: Part 5 Expand / Collapse
Author
Message
Posted Wednesday, August 3, 2011 10:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 8, 2014 8:23 AM
Points: 4, Visits: 162
Contest Entry:

For query 1
I would put an index on Time and Data for the analysis log table.

For query 2
The query 1 item would take care of this query as well.

For query 1 and 2. The interesting thing would be to cache the days counts off in server memory or another temporary table then do a nightly or hourly job to write all that down to the databaase or copy it to the analysis log. One other arc change I would make is down use a varchar(50) if possible, can these data events be a key of an int or bigint. That would far improve the indexing. Maybe provide a lookup table to find the description.

For query 3 remove the order by if possible, the sort might be better selecting out of the temp table later if needed. So, add a PostDate column there. Alternately, add an index on cs_Posts with a ThreadID,IsApproved,PostDate,PostID,PostLevel,ApplicationPostType (though this is wide).

Query 4:
Looks like an index is needed on cs_weblog_weblogs on sectionID, but selectivity might just be poor. I would alias applicationtype in the where clause, also is W.* really needed? Expand the W.* items out. Write the , out as an ANSI join style just for best practice.
Post #1153695
Posted Thursday, August 4, 2011 8:57 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 10, 2014 7:20 AM
Points: 175, Visits: 723
Thanks for everyone who has contributed to the contest so far. The goal of the contest was to try an interactive article format with reader feedback on “SQLServerCentral.com Best Practices Clinic: Part 5”, and have a little fun trying to solve a “puzzle of sorts” with the information provided. I feel that there is enough information in the article to provide a “good enough” answer. Sure, with more information you might be able to provide a “better” answer, but this "puzzle of sorts" was designed this way not to place too much impact on our reader’s time. Please keep the answers coming!

Thanks!


Brad M. McGehee
Microsoft SQL Server MVP
Director of DBA Education, Red Gate Software
www.bradmcgehee.com
Post #1154302
Posted Thursday, August 4, 2011 11:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:28 PM
Points: 6,236, Visits: 7,375
Ninja's_RGR'us (8/3/2011)
Ninja's_RGR'us (8/2/2011)
UPDATE dbo.AnalysisLog SET @Count = Count = Count + 1 WHERE ...


Nevermind that part, I had missed the fact that there's 1 row per date where there's action on the page.


We both did. I was inspired to believe it was a single row first generated when the first click of the page was performed, primarily because of this quote from the article:

When a web page is accessed, Query #2 runs first, looking to see if that particular article has ever been viewed before or not. If not, then another query (which is not in the top 10 list) is run to create a new record where clicks are tracked in a table;


Brad, can you clarify please?



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1154472
Posted Thursday, August 4, 2011 12:49 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 10, 2014 7:20 AM
Points: 175, Visits: 723
Craig Farrell (8/4/2011)
Ninja's_RGR'us (8/3/2011)
Ninja's_RGR'us (8/2/2011)
UPDATE dbo.AnalysisLog SET @Count = Count = Count + 1 WHERE ...


Nevermind that part, I had missed the fact that there's 1 row per date where there's action on the page.


We both did. I was inspired to believe it was a single row first generated when the first click of the page was performed, primarily because of this quote from the article:

When a web page is accessed, Query #2 runs first, looking to see if that particular article has ever been viewed before or not. If not, then another query (which is not in the top 10 list) is run to create a new record where clicks are tracked in a table;


Brad, can you clarify please?


Based on a Profiler trace I took, here is what appears to happen. Query 1 runs first, and if it doesn't find a record for page that is viewed, then an INSERT is performed into the table so that its views can be tracked. This INSERT query is not in the 10 ten, so I did not list it. If Query 1 does find a record, then Query 2 runs, updating the record for the page that is viewed. The developer of this code was a contractor and did not document eactly what happens or why, so I have had to infer what happens based on what I see in the trace.


Brad M. McGehee
Microsoft SQL Server MVP
Director of DBA Education, Red Gate Software
www.bradmcgehee.com
Post #1154526
Posted Thursday, August 4, 2011 11:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 7:08 AM
Points: 2, Visits: 15
Contest Entry

--------------------------------------------------------------
Recommendation For Query#1 and @Query#2
--------------------------------------------------------------
Understanding: It seems that AnalysisLog table used to keep count of clicks on each artical(data) date wise.

Logical Change Recommended : first execute Update query to increase count (query#1).
if not row affected by Update Query then execute insert query to make first record. This will eliminate execution of query#2

Solution:
UPDATE dbo.AnalysisLog SET [Count] = [Count] + 1 WHERE [Time] = @RoundedTime AND [Data] = @Data

IF @@ROWCOUNT = 0
**Insert query**


--------------------------------------------------------------
Recommendation For Query#3
--------------------------------------------------------------
Recommendation#1. use nolock with first select query, which is used to get ThreadID and PostID
Recommendation#2. create index on [cs_Posts].[PostDate], this saves sorting time. Because in execution plan of Insert into #PageIndex query sort cost is 36%.

Solution:

CREATE NONCLUSTERED INDEX [] ON [dbo].[cs_Posts] ([PostDate])
GO

Select
@ThreadID = p.ThreadID, @PostID = p.PostID
From
cs_Posts p with (nolock)
Where
p.SectionID = @SectionID and p.PostName = @PostName
GO
--------------------------------------------------------------
Recommendation For Query #4
--------------------------------------------------------------
1. We can use inner join between cs_Sections F, cs_weblog_Weblogs W on F.SectionID = W.SectionID
2. We can use nolock with both tables
3. Make non-cluster index on F.SectionID and W.SectionID

Solution:
CREATE NONCLUSTERED INDEX [] ON [dbo].[cs_Sections] ([SectionID])
GO

CREATE NONCLUSTERED INDEX [] ON [dbo].[cs_weblog_Weblogs] ([SectionID])
GO

SELECT
F.[SectionID], F.[SettingsID], F.[IsActive], F.[ParentID], F.[GroupID], F.[Name],
F.[NewsgroupName], F.[Description], F.[DateCreated], F.[Url], F.[IsModerated],
F.[DaysToView], F.[SortOrder], F.[TotalPosts], F.[TotalThreads], F.[DisplayMask],
F.[EnablePostStatistics], F.[EnablePostPoints], F.[EnableAutoDelete],
F.[EnableAnonymousPosting], F.[AutoDeleteThreshold], F.[MostRecentThreadID],
F.[MostRecentThreadReplies], F.[PostsToModerate], F.[ForumType], F.[IsSearchable],
F.[ApplicationType], F.[ApplicationKey], F.PropertyNames as SectionPropertyNames,
F.PropertyValues as SectionPropertyValues, F.[DefaultLanguage], F.[DiskUsage], W.*,
null as LastUserActivity
FROM
cs_Sections F with (nolock)
inner join cs_weblog_Weblogs W with (nolock) on F.SectionID = W.SectionID

WHERE
F.SettingsID = @SettingsID AND
ApplicationType = 1
GO
--------------------------------------------------------------

Pradeep Chaurasia
cpradeep@pisoftek.com
P. I. Softek Ltd
Sec-62, Noida, UP


Pradeep Chaurasia
P.I. Softek Ltd
Sec-62, Noida, UP
Post #1154750
Posted Tuesday, August 23, 2011 12:10 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 10, 2014 7:20 AM
Points: 175, Visits: 723
Sorry for the delay in announcing the winner of this contest, but I was out last week traveling. After reviewing the entries, and talking about them to several other DBAs, we selected WilliamD's response as the winning entry. We all thought his answer seemed the most complete. Thanks to everyone who participated in this contest.

Brad M. McGehee
Microsoft SQL Server MVP
Director of DBA Education, Red Gate Software
www.bradmcgehee.com
Post #1164231
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse