Query/index Optimisation, again ;)

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 2000 Forums

SQL Server Development (2000)

Query/index Optimisation, again ;)

Author

Topic

bjornh
Yak Posting Veteran

87 Posts

Posted - 2003-10-21 : 17:13:19

iam getting pretty sick (of myself the most) about these preformance problems... But, every day iam learning! but learning doesn't always mean that things work properly

So, iam going to post a part of my sp, and then i hope, i got something to start of for my whole app.

This part returns the data for a graph for the past 47 days. (there only data in for 47 days, so the select dates are some bit extreme...)

declare @siteID int,
	@firstday datetime,
	@lastday datetime

select @siteID = 259000, @firstday = '1-1-2003', @lastday = '1-1-2005'

--Grafiek
set language 'dutch'
select cast(convert(varchar(20), datum, 105) as datetime) as datum, count(distinct bezoekerID) as bezoekers, 
count(*) as pageviews 
	    from dbo.log2
	    where paginaID not in (select id from dbo.pagina where active = 0 AND siteID = @siteID) 
            AND datum between @firstday AND @lastday AND siteID = @siteID
	    group by cast(convert(varchar(20), datum, 105) as datetime) 
            order by cast(convert(varchar(20), datum, 105) as datetime)

information about the table log2:
This table contains the pageviews, every row is a pageview
id: int, primary key (clustered index)
SiteID: int (nonclustered index)
datum: datetime, (nonclustered index desc)
bezoekerID: int, visitorID (dutch), links to the table log1, wich contains info about browser, os, etc. (no index)
PaginaID: int, pageID, link to the table pagina, wich contains the names for all the pages (no index)

After the select, i have 47 rows, with an average bezoekers (vistors) of about 500 and pageviews about 3000 a day. (138801 rows in total for siteID 259000)

the table contains 436.862 rows (live data), so there is also a bunch of data from different sites.

But this query takes up way too much time, in QA it takes about 8 secs, and this is unacceptably...

so, these are the facts, what is the best place to start optimizing...

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-21 : 17:40:30

Take a look at the query plan

I assume (select id from dbo.pagina where active = 0 AND siteID = @siteID) uses an index
paginaID not in ... won't.

datum between @firstday AND @lastday AND siteID = @siteID
needs to use an index - it needs to use something on the site as you are selecting all days.

from dbo.log2
left outer join
dbo.pagina
on active = 0
AND siteID = @siteID
where pagina .siteID is null

probably won't make a difference but is worth a try.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bjornh
Yak Posting Veteran

87 Posts

Posted - 2003-10-21 : 18:06:07

ok, ill take a look at it.

When iam looking @ the query plan, it's only showing that it is using the PK of log1 and pagina. not the other indexes.

(optimisation isn't fun :()

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-21 : 18:12:04

Look at the relative costs in the plan.
Probably should have an index on siteID, Active in pagina

Try giving a query hint on SiteID in log2 and see what happens - update the statistics first.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources