| Author |
 Topic  |
|
|
funkseo
Starting Member
8 Posts |
 Posted - 12/10/2012 : 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
878 Posts |
Posted - 12/10/2012 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/10/2012 : 19:17:20
|
| 5-6 GB is not big table. Post you query and you can get suggestion. |
|
|
|
funkseo
Starting Member
8 Posts |
Posted - 12/11/2012 : 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 - 12/11/2012 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/11/2012 : 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 - 12/11/2012 : 11:56:46
|
| SiteID is not unique. For the same SiteID there may be lots of TagID. |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/11/2012 : 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 - 12/11/2012 : 12:15:03
|
| I'm sorry I didn't mention about it, there is a clustered key on SiteTags table, SiteTagID. |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/11/2012 : 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 - 12/11/2012 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/11/2012 : 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 - 12/11/2012 : 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 - 12/11/2012 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/11/2012 : 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. |
|
|
| |
Topic |
|
Optimization advice for huge tables
