SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Optimization advice for huge tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

funkseo
Starting Member

8 Posts

Posted - 12/10/2012 :  18:28:20  Show Profile  Reply with Quote
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

938 Posts

Posted - 12/10/2012 :  19:17:19  Show Profile  Reply with Quote
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.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/10/2012 :  19:17:20  Show Profile  Reply with Quote
5-6 GB is not big table. Post you query and you can get suggestion.
Go to Top of Page

funkseo
Starting Member

8 Posts

Posted - 12/11/2012 :  10:18:36  Show Profile  Reply with Quote
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
Go to Top of Page

funkseo
Starting Member

8 Posts

Posted - 12/11/2012 :  10:20:37  Show Profile  Reply with Quote
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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/11/2012 :  10:29:50  Show Profile  Reply with Quote
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.
Go to Top of Page

funkseo
Starting Member

8 Posts

Posted - 12/11/2012 :  11:56:46  Show Profile  Reply with Quote
SiteID is not unique. For the same SiteID there may be lots of TagID.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/11/2012 :  12:01:36  Show Profile  Reply with Quote
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.
Go to Top of Page

funkseo
Starting Member

8 Posts

Posted - 12/11/2012 :  12:15:03  Show Profile  Reply with Quote
I'm sorry I didn't mention about it, there is a clustered key on SiteTags table, SiteTagID.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/11/2012 :  13:17:30  Show Profile  Reply with Quote
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
Go to Top of Page

funkseo
Starting Member

8 Posts

Posted - 12/11/2012 :  14:39:26  Show Profile  Reply with Quote
it took 6 seconds and 49% cost for Clustered Index seek Sites.PK_Sites, 48% cost for Key Lookup(Clustered) SiteTags.PK_SiteTags2
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/11/2012 :  14:57:10  Show Profile  Reply with Quote
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?
Go to Top of Page

funkseo
Starting Member

8 Posts

Posted - 12/11/2012 :  16:22:19  Show Profile  Reply with Quote
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
Go to Top of Page

funkseo
Starting Member

8 Posts

Posted - 12/11/2012 :  16:23:36  Show Profile  Reply with Quote
BTW I will try to add clustered index on SiteID column, it will take time, i should create a test database.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/11/2012 :  16:26:34  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000