I have these tables:
CREATE TABLE [dbo].[COREAttribute] (
[oid] [uniqueidentifier] NOT NULL ,
[CLSID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX [COREAttributeOidIndex] ON
[dbo].[COREAttribute]([oid], [CLSID]) WITH FILLFACTOR = 90 ON
[PRIMARY]
CREATE TABLE [dbo].[COREBstrAttribute] (
[oid] [uniqueidentifier] NOT NULL ,
[iid] [uniqueidentifier] NOT NULL ,
[dispid] [int] NOT NULL ,
[value] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
CREATE CLUSTERED INDEX [COREBstrAttributeOidIndex] ON
[dbo].[COREBstrAttribute]([oid]) WITH FILLFACTOR = 90 ON [PRIMARY]
Now when I try this query, it's taking 8-10mins.
Declare @.t TABLE (oid uniqueidentifier primary key,
[Description] nvarchar(1024) NULL,
[Name] nvarchar(1024) NULL,
[UID] nvarchar(1024) NULL)
DECLARE @.COREBSTRAttribute TABLE (oid uniqueidentifier, dispid int
NULL, value nvarchar(1024) NULL)
INSERT INTO @.COREBSTRAttribute select oid, dispid, value
FROM dbo.COREBSTRAttribute
WHERE iid ='{1449DB20-DB97-11D6-A551-00B0D021E10A}'
INSERT @.t
SELECT distinct
c0.oid,
c1.Value,
c2.Value,
c3.Value
FROM(
SELECT oid FROM dbo.COREAttribute
WHERE CLSID IN (
'{1449DB2B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB2D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB2F-DB97-11D6-A551-00B0D021E10A}',
'{1449DB31-DB97-11D6-A551-00B0D021E10A}',
'{1449DB33-DB97-11D6-A551-00B0D021E10A}',
'{1449DB35-DB97-11D6-A551-00B0D021E10A}',
'{1449DB37-DB97-11D6-A551-00B0D021E10A}',
'{1449DB39-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3F-DB97-11D6-A551-00B0D021E10A}',
'{1449DB43-DB97-11D6-A551-00B0D021E10A}',
'{1449DB45-DB97-11D6-A551-00B0D021E10A}',
'{1449DB47-DB97-11D6-A551-00B0D021E10A}',
'{1449DB49-DB97-11D6-A551-00B0D021E10A}',
'{1449DB4B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB4D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB51-DB97-11D6-A551-00B0D021E10A}',
'{DAA598D9-E7B5-4155-ABB7-0C2C24466740}',
'{6921DAC3-5F91-4188-95B9-0FCE04D3A04D}',
'{128F17D4-2014-480A-96C6-370599F32F67}',
'{9F3A64C9-28F3-440B-B694-3E341471ED8E}',
'{2E3AB438-7652-4656-9A18-4F9C1DC27E8C}',
'{B69E74A7-0E48-4BA2-B4B7-5D9FFEDC2D97}',
'{2BB836D3-2DC1-4899-9406-6A495ED395C3}',
'{9CFFDC3A-5DF5-4AD8-B067-6EF5A9736681}',
'{E18E470B-B297-43D2-B9CD-71AF65654970}',
'{9BDCDA97-1171-409D-B3AB-71DA08B1E6D3}',
'{0E91AC62-7929-4B42-B771-7A6399A9E3B0}',
'{C8BAE335-CCB7-4F1D-8E9D-85C301188BE2}',
'{97E6E186-8F32-42E6-B81C-8E2E0D7C5ABA}',
'{BE5B6233-D4E7-4EF6-B5FC-91EA52128723}',
'{4ECDAAE1-828A-4C43-8A66-A7AB6966F368}',
'{19082B90-EF02-45CC-B037-AFD0CF91D69E}',
'{6F76CEF7-EBC0-48C6-8B78-C5330324C019}',
'{18492042-B22A-4370-BFA3-D0481800BBC7}',
'{A71343AD-CC09-4033-A224-D2D8C300904A}',
'{EC10BD0A-FDE3-4484-BEA6-D5A2E456256C}',
'{F7F8A4E1-651A-4A48-B55A-E8DA59D401B2}',
'{A923226F-B920-4CFA-9B0D-F422D1C36902}',
'{A95ACA6A-16AC-47E4-A9A6-F530D50A475A}',
'{C31DB61A-5221-42CF-9A73-FE76D5158647}')
) AS c0
LEFT JOIN @.COREBSTRAttribute AS c1
ON (c0.oid = c1.oid)
AND c1.dispid = 28
LEFT JOIN @.COREBSTRAttribute AS c2
ON (c0.oid = c2.oid)
AND c2.dispid = 112
LEFT JOIN @.COREBSTRAttribute AS c3
ON (c0.oid = c3.oid)
AND c3.dispid = 192
Any help is greatly appreciated.
thanks
SunitI admit I haven't read your post in detail, but clustering on a GUID
probably isn't a good idea. Since the GUID values are random, but the
index is ordered, that means a lot of fragmentation as the index has to
be constantly rebuilt and reordered. You might try changing to a
nonclustered index instead, and keep the clustered index for columns
which you often use in GROUP BY or ORDER BY clauses.
Simon|||I changed to this and now I get 'Invalid object name 'bstr' during
runtime. It parses successfully though !
Select distinct c0.oid, c1.Value, c2.Value, c3.Value
From
(SELECT oid FROM dbo.COREAttribute
WHERE CLSID IN (
'{1449DB2B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB2D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB2F-DB97-11D6-A551-00B0D021E10A}',
'{1449DB31-DB97-11D6-A551-00B0D021E10A}',
'{1449DB33-DB97-11D6-A551-00B0D021E10A}',
'{1449DB35-DB97-11D6-A551-00B0D021E10A}',
'{1449DB37-DB97-11D6-A551-00B0D021E10A}',
'{1449DB39-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3F-DB97-11D6-A551-00B0D021E10A}',
'{1449DB43-DB97-11D6-A551-00B0D021E10A}',
'{1449DB45-DB97-11D6-A551-00B0D021E10A}',
'{1449DB47-DB97-11D6-A551-00B0D021E10A}',
'{1449DB49-DB97-11D6-A551-00B0D021E10A}',
'{1449DB4B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB4D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB51-DB97-11D6-A551-00B0D021E10A}',
'{DAA598D9-E7B5-4155-ABB7-0C2C24466740}',
'{6921DAC3-5F91-4188-95B9-0FCE04D3A04D}',
'{128F17D4-2014-480A-96C6-370599F32F67}',
'{9F3A64C9-28F3-440B-B694-3E341471ED8E}',
'{2E3AB438-7652-4656-9A18-4F9C1DC27E8C}',
'{B69E74A7-0E48-4BA2-B4B7-5D9FFEDC2D97}',
'{2BB836D3-2DC1-4899-9406-6A495ED395C3}',
'{9CFFDC3A-5DF5-4AD8-B067-6EF5A9736681}',
'{E18E470B-B297-43D2-B9CD-71AF65654970}',
'{9BDCDA97-1171-409D-B3AB-71DA08B1E6D3}',
'{0E91AC62-7929-4B42-B771-7A6399A9E3B0}',
'{C8BAE335-CCB7-4F1D-8E9D-85C301188BE2}',
'{97E6E186-8F32-42E6-B81C-8E2E0D7C5ABA}',
'{BE5B6233-D4E7-4EF6-B5FC-91EA52128723}',
'{4ECDAAE1-828A-4C43-8A66-A7AB6966F368}',
'{19082B90-EF02-45CC-B037-AFD0CF91D69E}',
'{6F76CEF7-EBC0-48C6-8B78-C5330324C019}',
'{18492042-B22A-4370-BFA3-D0481800BBC7}',
'{A71343AD-CC09-4033-A224-D2D8C300904A}',
'{EC10BD0A-FDE3-4484-BEA6-D5A2E456256C}',
'{F7F8A4E1-651A-4A48-B55A-E8DA59D401B2}',
'{A923226F-B920-4CFA-9B0D-F422D1C36902}',
'{A95ACA6A-16AC-47E4-A9A6-F530D50A475A}',
'{C31DB61A-5221-42CF-9A73-FE76D5158647}'
)) AS c0 ,
(select oid, dispid, value
FROM dbo.COREBSTRAttribute
WHERE iid = '{1449DB20-DB97-11D6-A551-00B0D021E10A}'
) As bstr
LEFT JOIN bstr AS c1
ON (c0.oid = c1.oid)
AND c1.dispid = 28
LEFT JOIN bstr AS c2
ON (c0.oid = c2.oid)
AND c2.dispid = 112
LEFT JOIN bstr AS c3
ON (c0.oid = c3.oid)
AND c3.dispid = 192
thanks
Sunit|||Simon Hayes (sql@.hayes.ch) writes:
> I admit I haven't read your post in detail, but clustering on a GUID
> probably isn't a good idea. Since the GUID values are random, but the
> index is ordered, that means a lot of fragmentation as the index has to
> be constantly rebuilt and reordered. You might try changing to a
> nonclustered index instead, and keep the clustered index for columns
> which you often use in GROUP BY or ORDER BY clauses.
As always in the database world, it depends!
SQL Server MVP Greg Linwood taught me a great way to use GUID for a
clustered index. You start off with a fairly low fill factor, say 50%.
Now, new guids will most cases find empty holes to jump in, so there
will not be any page split. Instead, fragmentation will decrease by
time.
Until, that is, pages starts to become full, and there will be page
splitting galore. That's when you need a maintenance window so yuu
can reindex with your fill factor. Obviously, you need a good feeling
for which fill factor you need to the next maintenance window. This is
definitely not a method for the DBA on auto-pilot.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||sjoshi (sjoshi@.ingr.com) writes:
> Now when I try this query, it's taking 8-10mins.
While we got the tables and the query, we did not get any details on
the amount of data involved. For instance, how many rows could we
expect in the involved tables?
Here are some assorted thoughts:
o Replace table variable with temp tables. Temp tables have statistics,
and this may the optimizer a second chance for a better job.
o Add a WHERE clause to insert only the dispid of interest into
@.COREBSTRAttribute
o Leave out Value from @.COREBSTRAttribute. That serves to make the
table slower.
o Add a clustered index on dispid. (Possible for temp table.)
o Long IN clauses are takes long time to optimize. Could be a better
idea to put the guids into a temp table instead.
o You insert into a table variable @.t. When you insert into temp
variables, you kill parallelism. Someties parallelism is good for
your queries. (Sometimes it is not!)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
No comments:
Post a Comment