Optimization advice for huge tables

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)

Optimization advice for huge tables

Author

Topic

funkseo
Starting Member

8 Posts

Posted - 2012-12-10 : 18:28:20

Hello,

I need tuning advices for large tables query performance.
I have 3 big tables and I need to join these 3 tables on some queries and it's getting slower every day because of the table rows size increasing.

I believe I made the index tuning.
I'd be glad if you suggest me for better performance tuning.

It is not easy to split tables. I tried to create another data files but it didn't make a change.
Each table 5-6 GB size by the way.
Splitting tables or partitioning or something else?

Thanks in advance.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-12-10 : 19:17:19

Generally it's not about the size of the tables but the amount of data in the queries. You can have billions of rows and instant query response if you're just pulling out by a few keys. On the other hand, if you have a relatively small table (like yours at only a few GB) it can still take a while if you actually need to trawl through the whole lot to get your answer (SUM for example).
So you'll need to describe your tables and the queries you are doing in order to get any decent help because there is no single answer to your question as it stands.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-10 : 19:17:20

5-6 GB is not big table. Post you query and you can get suggestion.

funkseo
Starting Member

8 Posts

Posted - 2012-12-11 : 10:18:36

Ok. I have a website which analyzes websites and give information to webmasters for better SEO.http://www.websiteanalyzer.info
Websites have keyword metatags(one to many relationship) and for same keywords there may be other sites too. For example sqlservercentral.com has "SQL Server" tag and there are lots of websites has the same tag.

So I have 3 big tables.

1 - Sites table - 20.000.000 rows
SiteID - Primary Key
SiteUrl - Nonclustered Index

2 - Tags table - 30.000.000 rows
TagID - PK
Tag - Nonclustered Index

3 - SiteTags table - 120.000.000 rows
SiteID - Nonclustered Index, FK
TagID - Nonclustered Index, FK

If you check following urls you will see the problem. On first page there is no performance problem but if you navigate the pager, it will be slower(you will see why if you check stored procedure).

http://www.websiteanalyzer.info/Tag.aspx?tag=SQL%20Server&page=1
http://www.websiteanalyzer.info/Tag.aspx?tag=SQL%20Server&page=100

Following is the stored procedure running on these pages.

ALTER PROCEDURE [dbo].[spGetSiteListFromTagPager]
@Tag nvarchar(64),
@page int = 0
AS

DECLARE @TagID int
SET @TagID = (select top 1 TagID FROM Tags with(nolock) WHERE Tag=@Tag)

IF(@page = 1)
BEGIN

SELECT TOP 20 Sites.SiteUrl,Sites.Title,Sites.Description
from Sites with(nolock)
INNER JOIN SiteTags with(nolock) ON Sites.SiteID=SiteTags.SiteID
WHERE SiteTags.TagID=@TagID

END

ELSE IF(@page > 1)
BEGIN
create table #t(SiteID bigint NOT NULL)
ALTER TABLE #t ADD PRIMARY KEY CLUSTERED (SiteID)

insert #t
select distinct SiteID FROM SiteTags with(nolock) WHERE TagID = @TagID
--CREATE INDEX #t_IDX_SiteID1 ON #t(SiteID)

CREATE TABLE #t2(UID int IDENTITY(1,1) NOT NULL,SiteUrl varchar(120),Title varchar(500),Description nvarchar(500))
ALTER TABLE #t2 ADD PRIMARY KEY CLUSTERED (UID)
INSERT #t2
SELECT TOP (@page*20) Sites.SiteUrl,Sites.Title,Sites.Description from Sites with(nolock)
INNER JOIN #t with(nolock) ON Sites.SiteID = #t.SiteID

SELECT
SiteUrl,Title,Description FROM #t2 WHERE
UID>=((@page-1)*20) AND UID<(@page*20)
END

funkseo
Starting Member

8 Posts

Posted - 2012-12-11 : 10:20:37

By the way I tried SQL Server builtin paging but it didn't change the performance.

DECLARE @TagID int
SET @TagID = (select top 1 TagID FROM Tags with(nolock) WHERE Tag='sql')

SELECT Sites1.SiteUrl--,Sites1.Title--,Sites1.Description
from Sites1 with(nolock)
INNER JOIN SiteTags with(nolock) ON Sites1.SiteID=SiteTags.SiteID
WHERE SiteTags.TagID=@TagID
ORDER BY Sites1.SiteID
OFFSET 1000 ROWS
FETCH NEXT 20 ROWS ONLY

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-11 : 10:29:50

Can you try making SIteID Clustered index in SiteTags and see the execution plan and why you are inserting into temp table? Try this in test environment though.

funkseo
Starting Member

8 Posts

Posted - 2012-12-11 : 11:56:46

SiteID is not unique. For the same SiteID there may be lots of TagID.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-11 : 12:01:36

Yes. SiteTags table needs to have clustered index and i see you don't have clustered index in that table as NCI needs to have pointer to data pages(CI) so that it can access faster.Also I don't see a reason to have temp table unless you are doing other logic.

funkseo
Starting Member

8 Posts

Posted - 2012-12-11 : 12:15:03

I'm sorry I didn't mention about it, there is a clustered key on SiteTags table, SiteTagID.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-11 : 13:17:30

Check and show the execution plan for this

SELECT TOP 20 Sites.SiteUrl,Sites.Title,Sites.Description 
from Sites with(nolock)
INNER JOIN SiteTags with(nolock) ON Sites.SiteID=SiteTags.SiteID
WHERE SiteTags.TagID=@TagID

funkseo
Starting Member

8 Posts

Posted - 2012-12-11 : 14:39:26

it took 6 seconds and 49% cost for Clustered Index seek Sites.PK_Sites, 48% cost for Key Lookup(Clustered) SiteTags.PK_SiteTags2

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-11 : 14:57:10

I see Key lookup so it has to go through clustered index to get it. Now change SiteId to Clustered index in SiteTags table and check execution plan.Also do you seek predicate or predicate in execution plan?

funkseo
Starting Member

8 Posts

Posted - 2012-12-11 : 16:22:19

I modified as following and I think it's little bit changed. I removed tep tables and use built-in paging.

ALTER PROCEDURE [dbo].[spGetSiteListFromTagPager]
@Tag nvarchar(64),
@page int = 0
AS

DECLARE @TagID int
SET @TagID = (select top 1 TagID FROM Tags with(nolock) WHERE Tag=@Tag)

IF(@page = 1)
BEGIN

SELECT TOP 20 Sites.SiteUrl,Sites.Title,Sites.Description
from Sites with(nolock)
INNER JOIN SiteTags with(nolock) ON Sites.SiteID=SiteTags.SiteID
WHERE SiteTags.TagID=@TagID
END
ELSE IF(@page > 1)
BEGIN

SELECT Sites.SiteUrl,Sites.Title,Sites.Description from Sites with(nolock)
INNER JOIN (select distinct SiteID FROM SiteTags with(nolock) WHERE TagID = @TagID) as t ON Sites.SiteID = t.SiteID
ORDER BY t.SiteID
OFFSET (@page-1)*20 ROWS
FETCH NEXT 20 ROWS ONLY

END

funkseo
Starting Member

8 Posts

Posted - 2012-12-11 : 16:23:36

BTW I will try to add clustered index on SiteID column, it will take time, i should create a test database.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-11 : 16:26:34

quote:
Originally posted by funkseo

BTW I will try to add clustered index on SiteID column, it will take time, i should create a test database.

Yes try with that and tell how does execution plan look like. Can't you use ROW_NUMBER(ORDER by...) for Paging instead.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources