Create index - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Create index

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-03-21 : 19:14:58

Im trying to tune up this query but it has xml columns and won't let me create a covering index on them. What can i do to make it faster?
use temp tables, create other indexes, use derived tables??? any suggestion will be good. thanks

SELECT
temptest.ForeignID AS 'UniqueID',
CASE WHEN temptest.SiteID = 1 THEN
CASE WHEN Ad.AdStructure.exist('(AdStructures/AdStructure[@Type="Make"]/@Default)') = 1
AND Ad.AdStructure.exist('(AdStructures/AdStructure[@Type="Model"]/@Default)') = 1
AND Ad.AdStructure.exist('(AdStructures/AdStructure[@Type="Year"]/@Default)') = 1 THEN
33
ELSE
99
END
ELSE
Ad.SourceID
END AS 'OrigineID',
Coalesce(Company.ForeignID
,Substring( Ad.Contact.value('(Contacts/Contact[@Type="Main"]/Emails/Email[@Type="Main"]/@Value)[1]', 'varchar(50)'), 1,50)
,'Private') AS DealerID,
Case When temptest.IsShowRoom = 1 then 1 else 0 end AS ShowRoomID,
Ad.AdStructure.value('(AdStructures/AdStructure[@Type="Make"]/@Default)[1]', 'varchar(50)') AS 'Make',
Ad.AdStructure.value('(AdStructures/AdStructure[@Type="Model"]/@Default)[1]', 'varchar(50)') AS 'Model',
Ad.AdStructure.value('(AdStructures/AdStructure[@Type="Year"]/@Default)[1]', 'varchar(25)') AS 'Year',
Ad.AdField.value('(AdFields/AdField[@Type="Odometer"]/@Default)[1]', 'varchar(25)') AS 'KM',
Coalesce(ROUND(Ad.Price.value('(Prices/Price[@Type="Price"]/@Default)[1]', 'money'),0)
,ROUND(Ad.Price.value('(Prices/Price[@Type="WholeSalePrice"]/@Default)[1]', 'money'),0)
) As Price,
REPLACE(CONVERT(VARCHAR(20), Ad.StockNumber),'|','') AS StockNumber,
temptest.StatDate,
temptest.StatDate,
temptest.Views AS ListViews,
temptest.Clicks AS DetailViews,
0 AS SalesLeads,
temptest.SiteID,
1 AS Area,
'DSQLPR_Auto' AS Server,
'adstat_auto' AS DataTable,
1 AS AdID,
Ad.AdField.value('(AdFields/AdField[@Type="Status"]/@Value)[1]', 'varchar(100)') AS 'Status',
NULL AS 'Description',
NULL AS 'PrimaryAgentID',
NULL AS 'SecondaryAgentID',
NULL AS 'ParentCompanyId',
Ad.Category.value('(//Categories/Category/@Default)[1]', 'varchar(1000)') AS 'Category',
Ad.Location.value('(//Location/@Province)[1]', 'varchar(1000)') AS 'Province',
Ad.Location.value('(//Location/@City)[1]', 'varchar(1000)') AS 'City'
FROM
temptest
INNER JOIN dbo.Ad WITH (READUNCOMMITTED)
ON Ad.SourceID = temptest.SourceID
AND Ad.ForeignID = temptest.ForeignID
LEFT JOIN dbo.Company WITH (READUNCOMMITTED) ON Company.CompanyID = Ad.CompanyID
WHERE Ad.Category IS NOT NULL
AND Ad.Price.value('(Prices/Price[@Type="Price"]/@Default)[1]', 'money') < 2147483647

/*

select top 1 * from ad

drop index nci_coveredIndex222 on Tradersearch.dbo.Ad
create index nci_coveredIndex222 on Ad (
SourceID,
ForeignID,
CompanyID
--Category,
--Price,

--AdStructure
--AdField
--Location
)

*/

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-03-21 : 22:14:31

I am assuming you have significant amounts of data otherwise it wouldn't run particularly slowly. In these cases adding indexes will often not help. Also, becuase you don't have any predicates, I doubt adding indexes would help unless you pre-calculate this bit:
Ad.Price.value('(Prices/Price[@Type="Price"]/@Default)[1]', 'money') < 2147483647
Even then, unless it gives you sufficient selectivity it may not be worth it.
Make sure you index your PKs and FKs if they're not already but again, if you have significant volumne it most likely won't make any difference in this case.
A couple of general recommendations:
I'd not use XML where you don't have to. Split it out and put it into "proper" columns
Don't use "READUNCOMMITTED". If you think you're running into locking issues try using snapshot isolation mode instead.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-22 : 06:52:50

Inspect your query plan to see what is taking up the time/resources. I suspect it may be the shredding of the XML may be the culprit. You can look for steps such as XML Reader for Table Valued Function in the query plan to identify these.

If it is the XML that is causing the bottleneck, consider adding a primary XML index on the XML column. (http://msdn.microsoft.com/en-us/library/ms191497.aspx ) If you do, just make sure you have enough disk space - XML indexes take up a lot of space, sometimes as much as the column itself. Also, if you do frequent insertions/updates to the XML column that would be slowed down because of the presence of the XML index.

Hopefully that will help, if you still need performance improvement, you may want to add one or more secondary XML indexes. Given the nature of your query, I would start with a property index.

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-03-22 : 17:00:34

LoztinSpace,
Are you saying if we have a very large of data, adding indexes wont help?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-03-22 : 22:10:51

Yes that is what I am saying. If you are selecting a relatively small set of data then the optimiser should use the index. If you are selecting a significant proportion of your data then using an index is typically a bad thing because it would increase I/O.
So unless you have a way of pre-cooking or shrinking your data you can't get faster than a table scan. If that's a lot of data it will take a lot of time.
Consider the candidate filters you have in your query:

Ad.SourceID = temptest.SourceID
Ad.ForeignID = temptest.ForeignID
Company.CompanyID = Ad.CompanyID
Ad.Category IS NOT NULL
Ad.Price.value('(Prices/Price[@Type="Price"]/@Default)[1]', 'money') < 2147483647

will any of these significantly reduce the amount of data that needs to be picked off the disk and shoved out of the server? If the answer is "no" there's not much you can do, though reducing the amount of XML will help reduce I/O and CPU.

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-03-23 : 18:44:16

How indexes increase I/O when there is a large data proportion?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-23 : 18:52:49

Have you considered XML indexes?

--
Gail Shaw
SQL Server MVP

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-03-25 : 19:18:02

quote:
Originally posted by basicconfiguration

How indexes increase I/O when there is a large data proportion?

Think about it. Take the worst case example where you are reading every row in a table by going first to the index then to the data page in some random order.

First, to get to any row you need to find the row using the index (say 3 levels of read). Then you need to get the actual row from the table block - another read.
Worse still, you will even read each data page multiple times because the data is sorted differently from the index so each page will be required once per row on it at different times.

If you just scanned the data blocks your IO would be just the row blocks, not 4 blocks per row.

Using an index is great when there are a small number of reads proportional to the total number of blocks holding the rows, but it gets to the point where it's better to just whiz through the table and that point arrives sooner than you might think.

In your case you are doing very little, if anything, to reduce the number of rows you actually return, hence my comment about how you (or rather the optimiser) should probably not use an index.

(and yes, to anyone who knows SQL Server well, I know this is an over-simplified example that ignores several factors but it illustrates the point).

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-25 : 19:42:02

Have a read through this for info on index usage: http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/

The 'won't use an index for large portions of the table' is only true for non-covering indexes. When the index is covering, the optimiser will quite happily (and correctly) use it up to 100% of the table selected.

--
Gail Shaw
SQL Server MVP

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-03-25 : 23:55:22

Yes of course, though a covering index does not (in this case) become useful as an index in the literal sense of being a fast way of identifying interesting rows. For those cases like the above with no selectvity, all that's happened is we've now built a smaller table. In fairness though, in this case you are almost certainly correct that it will run faster. Because it's XML, a covering index is likely to reduce the amount of IO simply because it would reduce the massive storage overhead of XML to proper column values.
Had the database been modelled more relationally I'd be less sure. Unless there's significantly fewer columns in the select than in the actual table, it's entirely possible that the net result would be the same, only you could say "look I'm using an index and twice as much disk space"!

So as ever - there are never any absolutes. It all depends on your data and what you are doing with it.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-26 : 06:36:12

Hence the reason I asked "Have you (the OP) considered XML indexes?"

--
Gail Shaw
SQL Server MVP

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-03-29 : 19:52:28

Thanks a lot to all of you. Especially to LoztInSpace!

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-03-31 : 04:33:13

GilaMonster,
No I didn't try XML Indexes yet. But I will try on Monday. The thing is creating indexes on a large table takes very long (> 30 min)