Index on large tables and performance

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

Transact-SQL (2005)

Index on large tables and performance

Author

Topic

RoyalSher
Yak Posting Veteran

95 Posts

Posted - 2009-08-06 : 10:55:19

Hi Folks,

I have these two large tables consisting of 1+ million rows and are joined together to get values. Here is the query which is currently constructed to extract data.

SELECT DISTINCT
df.[Uid],
df.1,
df.2,
df.3,
df.4,
df.5,
df.6,
df.7,
df.8,
df.9,
df.10,
df.11,
df.12,
df.13,
df.14,
df.15,
df.16,
df.17,
df.18,
df.19,
df.20,
df.21,
df.22,
df.23,
df.24,
df.25,
df.26,
df.27,
df.28,
df.29,
df.30

FROM
DFF AS DFF
INNER JOIN @SCT AS SCT ON SCT.SubCategoryId = DFF.SubCategory
INNER JOIN @CT AS CT ON CT.CategoryId = DFF.Category
INNER JOIN @CNT AS CNT ON CNT.CountryId = DFF.Citizenship
INNER JOIN @OLT AS OLT ON OLT.OfficialListId = DFF.OfficialList
INNER JOIN DF AS DF ON DFF.UID = DF.UID
WHERE DF.UID > 0 AND DF.InActive = 0

The column UId of table DF is clustered indexed, column UId of DFF is clustered index, and non other columns are indexed. Because of this the query takes around 10 mins per process id and this is real performance issue. This query is running on an 8 GB RAM, 8 core machine. The table variables used in the query are small in size (max number of rows - 300+).

I have introduce df.uid > 0 and df.inactive = 0 to ensure clustered index seek is done on the table df instead of a scan.

Can someone help me on how to make this query run quicker? Any index, partition, query changes? Please help.

Thanks!

RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

RoyalSher
Yak Posting Veteran

95 Posts

Posted - 2009-08-06 : 10:57:05

Missed a point. Some of the columns in the select are of lob type so I am seeing lob logical reads too.

RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-06 : 11:35:53

What does the execution plan say it's doing?

An infinite universe is the ultimate cartesian product.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-06 : 11:43:04

Have you tried somethin like this?

SELECT DISTINCT
DFF.[Uid]
into #temp
FROM
DFF AS DFF
INNER JOIN @SCT AS SCT ON SCT.SubCategoryId = DFF.SubCategory
INNER JOIN @CT AS CT ON CT.CategoryId = DFF.Category
INNER JOIN @CNT AS CNT ON CNT.CountryId = DFF.Citizenship
INNER JOIN @OLT AS OLT ON OLT.OfficialListId = DFF.OfficialList

select DF.uid, ....etc...
From #temp DFF
INNER JOIN DF AS DF ON DFF.UID = DF.UID
WHERE DF.UID > 0 AND DF.InActive = 0

If your UID is unique then there's no point in using distinct.

An infinite universe is the ultimate cartesian product.

ramya.he
Starting Member

2 Posts

Posted - 2009-08-07 : 03:12:43

Your problem can be sorted by creating an indexed view and using that view for selecting. This will surely slow the performance. If this answer has solved your issue please reply back saying yes or no.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources