T-SQL Query Optimization

Question

I'm working on some upgrades to an internal web analytics system we provide for our clients (in the absence of a preferred vendor or Google Analytics), and I'm working on the following query:

select 
    path as EntryPage, 
    count(Path) as [Count] 
from 
    (
        /* Sub-query 1 */
        select 
            pv2.path
        from 
            pageviews pv2 
                inner join
                    (
                        /* Sub-query 2 */
                        select
                            pv1.sessionid,
                            min(pv1.created) as created
                        from
                            pageviews pv1 
                                inner join Sessions s1 on pv1.SessionID = s1.SessionID
                                inner join Visitors v1 on s1.VisitorID = v1.VisitorID
                        where
                            pv1.Domain = isnull(@Domain, pv1.Domain) and
                            v1.Campaign = @Campaign
                        group by
                            pv1.sessionid
                    ) t1 on pv2.sessionid = t1.sessionid and pv2.created = t1.created
    ) t2
group by 
    Path;

I've tested this query with 2 million rows in the PageViews table and it takes about 20 seconds to run. I'm noticing a clustered index scan twice in the execution plan, both times it hits the PageViews table. There is a clustered index on the Created column in that table.

The problem is that in both cases it appears to iterate over all 2 million rows, which I believe is the performance bottleneck. Is there anything I can do to prevent this, or am I pretty much maxed out as far as optimization goes?

For reference, the purpose of the query is to find the first page view for each session.

EDIT: After much frustration, despite the help received here, I could not make this query work. Therefore, I decided to simply store a reference to the entry page (and now exit page) in the sessions table, which allows me to do the following:

select
    pv.Path,
    count(*)
from
    PageViews pv
        inner join Sessions s on pv.SessionID = s.SessionID
            and pv.PageViewID = s.ExitPage
        inner join Visitors v on s.VisitorID = v.VisitorID
where
    (
        @Domain is null or 
        pv.Domain = @Domain
    ) and
    v.Campaign = @Campaign
group by pv.Path;

This query runs in 3 seconds or less. Now I either have to update the entry/exit page in real time as the page views are recorded (the optimal solution) or run a batch update at some interval. Either way, it solves the problem, but not like I'd intended.

Edit Edit: Adding a missing index (after cleaning up from last night) reduced the query to mere milliseconds). Woo hoo!

What's the best way to do that? I'm not sure how to export it to a readable format for posting here. — Chris
– Chris, Commented Dec 4, 2008 at 4:25
Ouch not a single index seek there ... this can be optimised big time — Sam Saffron
– Sam Saffron, Commented Dec 4, 2008 at 4:33
@sambo99: Well, yeah! Sorry, I'm just an intermediate SQL guy - I'm venturing outside my realm in terms of optimization here. — Chris
– Chris, Commented Dec 4, 2008 at 4:35
We all know this is the only way to learn this stuff. I might suggest on the way by here, that perhaps you noticed that you haven't helped yourself by trying to design one query for two conditions. — dkretz
– dkretz, Commented Dec 4, 2008 at 5:00

dkretz · Accepted Answer · 2008-12-04 04:10:03Z

3

For starters,

    where pv1.Domain = isnull(@Domain, pv1.Domain)

won't SARG. You can't optimize a match on a function, as I remember.

answered Dec 4, 2008 at 4:10

dkretz

37.6k13 gold badges84 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Chris Over a year ago

Any suggestions to replace that line? I want to allow a domain specific match, or match all if no domain is specified. Is there a better way besides duplicating the entire query inside an if/else statement?

Chris Over a year ago

FWIW, I tried commenting out that line and the execution time went down to 8 seconds, so that appears to be the culprit. Any suggestions would be appreciated.

Learning Over a year ago

you can use a case statement : where pv1.Domain = case when @Domain is null then pv1.Domain else @Domain end

Chris Over a year ago

Marked your answer as accepted, because you helped me get closest to a solution. Hopefully one pops up in my sleep.

dkretz · Accepted Answer · 2008-12-04 04:46:59Z

2

I'm back. To answer your first question, you could probably just do a union on the two conditions, since they are obviously disjoint.

Actually, you're trying to cover both the case where you provide a domain, and where you don't. You want two queries. They may optimize entirely differently.

edited Dec 4, 2008 at 4:46

answered Dec 4, 2008 at 4:38

dkretz

37.6k13 gold badges84 silver badges140 bronze badges

Comments

RobS · Accepted Answer · 2008-12-04 04:52:15Z

2

What's the nature of the data in these tables? Do you find most of the data is inserted/deleted regularly?

Is that the full schema for the tables? The query plan shows different indexing.. Edit: Sorry, just read the last line of text. I'd suggest if the tables are routinely cleared/insertsed, you could think about ditching the clustered index and using the tables as heap tables.. just a thought

Definately should put non-clustered index(es) on Campaign, Domain as John suggested

answered Dec 4, 2008 at 4:52

RobS

9,4023 gold badges38 silver badges63 bronze badges

1 Comment

Chris Over a year ago

No data is deleted. Visitors and Sessions are updated infrequently, while PageViews has an insert each time someone visits a page. That is the full schema for the tables involved.

Jim McLeod · Accepted Answer · 2008-12-04 05:01:00Z

2

Your inner query (pv1) will require a nonclustered index on (Domain).

The second query (pv2) can already find the rows it needs due to the clustered index on Created, but pv1 might be returning so many rows that SQL Server decides that a table scan is quicker than all the locks it would need to take. As pv1 groups on SessionID (and hence has to order by SessionID), a nonclustered index on SessionID, Created, and including path should permit a MERGE join to occur. If not, you can force a merge join with "SELECT .. FROM pageviews pv2 INNER MERGE JOIN ..."

The two indexes listed above will be:

CREATE NONCLUSTERED INDEX ncixcampaigndomain ON PageViews (Domain)

CREATE NONCLUSTERED INDEX ncixsessionidcreated ON PageViews(SessionID, Created) INCLUDE (path)

edited Dec 4, 2008 at 5:01

answered Dec 4, 2008 at 4:26

Jim McLeod

9626 silver badges8 bronze badges

15 Comments

dkretz Over a year ago

Put Campaign first in the index - that will cover the case where Domain is null; the other way won't.

Chris Over a year ago

That index won't work anyway - campaign isn't in the page views table. Also, I could put an index on campaign on the visitors table, but in the test case, all rows have the same value for this column. My understanding is that for lack of uniqueness, an index on that column won't help much.

dkretz Over a year ago

An index on SessionID + Created won't benefit from adding path - it's already unique on the first two fields.

dkretz Over a year ago

OK - Now I'm going to have to really look at it - so far I've been optimizing locally. :)

Jim McLeod Over a year ago

Ah, I missed that Campaign was in Visitors. Just an index on pv1.Domain then. I'll update my answer.

|

dkretz · Accepted Answer · 2008-12-04 05:20:56Z

2

SELECT  
    sessionid,  
    MIN(created) AS created  
FROM  
    pageviews pv  
JOIN  
    visitors v ON pv.visitorid = v.visitorid  
WHERE  
    v.campaign = @Campaign  
GROUP BY  
    sessionid

so that gives you the sessions for a campaign. Now let's see what you're doing with that.

OK, this gets rid of your grouping:

SELECT  
    campaignid,  
    sessionid,   
    pv.path  
FROM  
    pageviews pv  
JOIN  
    visitors v ON pv.visitorid = v.visitorid  
WHERE  
    v.campaign = @Campaign  
    AND NOT EXISTS (  
        SELECT 1 FROM pageviews  
        WHERE sessionid = pv.sessionid  
        AND created < pv.created  
    )

edited Dec 4, 2008 at 5:20

answered Dec 4, 2008 at 5:14

dkretz

37.6k13 gold badges84 silver badges140 bronze badges

2 Comments

dkretz Over a year ago

So the next thing is you want the first page they hit for the session, right?

Chris Over a year ago

Yes. Tell you want, do you have MSN messenger? It's getting a bit convoluted on here.

Sam Saffron · Accepted Answer · 2008-12-04 05:23:16Z

2

To continue from doofledorf.

Try this:

where
   (@Domain is null or pv1.Domain = @Domain) and
   v1.Campaign = @Campaign

Ok, I have a couple of suggestions

Create this covered index:

 create index idx2 on [PageViews]([SessionID], Domain, Created, Path)

If you can amend the Sessions table so that it stores the entry page, eg. EntryPageViewID you will be able to heavily optimise this.

edited Dec 4, 2008 at 5:23

answered Dec 4, 2008 at 4:20

Sam Saffron

132k81 gold badges336 silver badges511 bronze badges

11 Comments

Chris Over a year ago

What's the best way to post the EP here?

Chris Over a year ago

Also, the suggested modification allows the conditional specification of a domain, like I wanted. So thanks for that. Still hovering around 9 seconds though. The final query still runs a clustered index scan at 37% cost iterating over all 2 million rows.

Sam Saffron Over a year ago

How many results is this thing returning when it takes 9 seconds?

Chris Over a year ago

It's a summary - returns less than 5 rows atm. Also, added the execution plan to the original question for reference.

Sam Saffron Over a year ago

Ok, if I were you I would not rest until this takes less than 100millisecs to return, Ill see what I can do, please post table definitions and indexes for [Visitors] and [PageViews]

|

Collectives™ on Stack Overflow

T-SQL Query Optimization

6 Answers 6

4 Comments

Comments

1 Comment

15 Comments

2 Comments

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

Comments

1 Comment

15 Comments

2 Comments

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related