August 6, 2009 at 8:48 am
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.
[updt]:Missed a point. Some of the columns in the select are of lob type so I am seeing lob logical reads too.
Viking
August 6, 2009 at 9:35 am
Try switching the table variables to temp tables, see if that make a difference.
If it doesn't, please post table definitions, index definitions and execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
Also, is that distinct really necessary?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 8, 2009 at 10:40 am
try getting only Pk in selection
like
SELECT ID
FROM TABLES and JOINS and WHERE Clause
put the result set in a Temp table and then select the orignal data like
SELECT All DAta
FROM TABLES AND JOIN
JOIN #TempTable T ON T.ID = DF.ID
and WHERE clauses
it will reduce your datafetch at first time and from small set you will select
Try it will will give you better performance and if it does not then kindly post the Actual Execution plan so that i can see whats happening
Musab
http://www.sqlhelpline.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply