August 7, 2008 at 12:08 pm
HI,
Suppose i have table with say 10 columns, say 2-4 columns are used in where clause and rest will be used in select clause very frequently. So which index option will be the best.
1-) Create seperate index on all the columns used in where clause
2-) Create seperate index on all the columns used in where clause and use all the columns of select clause in "Included Columns" option of the index
3-) Create one combined index on all the columns used in where clause
4-) Create one combined index on all the columns used in where clause and use all the columns of select clause in "Included Columns" option of the index
Thanks,
Usman
August 7, 2008 at 12:17 pm
with no table schema or query info its hard to say, but a covering index on all columns in the where clause usually gives best result. remember clustered index is already included in nonclustered indexes.
including all columns in an include sounds like way overkill to me.
Only one way to be sure, test it out.
---------------------------------------------------------------------
August 8, 2008 at 5:16 am
we cannot decide w/o knowning few factors like how frequently data will be selected, frequency of insert / update, selectivity on columns to be search..
Abhijit - http://abhijitmore.wordpress.com
August 8, 2008 at 6:51 am
Agreed, if you create a covering index to cover the columns used in where clause and use included columns for columns used in select statement the query will should be fine. But keep an eye on the index size as well (sp_spaceused). Few points to note down,
1. Use columns whose size are huge in included columns because in a normal index key can have maximum of 16 columns and a combined size of 900 bytes.
2. Also note that the index used in included columns will not be sorted so usually create included columns index on columns in select statement.
If you are confused check the execution plan and try to exclude table scans, index scans and clustered index scans using proper indexing.
[font="Verdana"]- Deepak[/font]
August 8, 2008 at 6:54 am
Very hard to say without seeing the queries and maybe the table definition.
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, 2008 at 7:32 am
Actually there are multiple queries and most of the times they are dynamic.
But what is the best practice or general practice for creating indexes?seperate index on each column or covering index on most used where clause columns?
August 8, 2008 at 7:43 am
usman.tanveer (8/8/2008)
Actually there are multiple queries and most of the times they are dynamic.But what is the best practice or general practice for creating indexes?seperate index on each column or covering index on most used where clause columns?
The wider the index, in general the more useful it is. SQL won't usually use multiple indexes on one table if there are multiple conditions. Of course, making it too wide means that its taking up unnecessary space and requiring lots of updates.
Also bear in mind that a query can only use an index if the columns in the where clause of the query are a left-based subset of the index columns.
To explain, if you have an index on 4 columns (A,B,C,D) that's useful for queries that filter on any of the following
A
A and B
A, B and C
A, B, C and D
A query filtering on C and D cannot seek on the above index.
If you want to post a couple sample queries we can suggest indexes and maybe you could generalise from there.
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, 2008 at 7:49 am
Following is the most commonly used query.....
exec sp_executesql N'SELECT TOP 50
(SELECT TOP 1 Name FROM Product WHERE Id IN (SELECT CurrentProductId FROM LicenseProduct WHERE LicenseId = License.Id) ORDER BY ReleaseNumber, Created DESC) AS ProductName,
(SELECT TOP 1 ProductNumber FROM Product WHERE Id IN (SELECT CurrentProductId FROM LicenseProduct WHERE LicenseId = License.Id) ORDER BY ReleaseNumber, Created DESC) AS ProductNumber,
License.Id AS LicenseId,
License.SerialNumber,
License.ServiceExpires,
License.FirstName,
License.LastName,
License.CompanyName,
License.EmailAddress AS LicenseEmail,
u.LoweredUsername AS AccountEmail,
License.Activations,
License.ReplacedBy,
License.Enabled
FROM
License WITH( NOLOCK )
LEFT OUTER JOIN
MyIpswitch_UserLicense ul WITH( NOLOCK ) ON License.Id = ul.LicenseId
LEFT OUTER JOIN
MyIpswitch_User u WITH( NOLOCK ) ON ul.UserId = u.UserId
WHERE
(@EmailAddress = '''' OR (License.EmailAddress LIKE @EmailAddress + ''%'' OR u.LoweredUsername LIKE @EmailAddress + ''%'')) AND
(@CompanyName = '''' OR (License.CompanyName LIKE @CompanyName + ''%'')) AND
(@LastName = '''' OR (License.LastName LIKE @LastName + ''%'')) AND
(@FirstName = '''' OR (License.FirstName LIKE @FirstName + ''%'')) AND
(@OrderNumber = '''' OR (SerialNumber IN(SELECT Number FROM v_CombinedOrders WHERE OrderNumber LIKE @OrderNumber + ''%''))) AND
(@SerialNumber = '''' OR (License.SerialNumber LIKE @SerialNumber + ''%''))
ORDER BY License.SerialNumber',N'@SerialNumber nvarchar(127),@EmailAddress nvarchar(200),@LastName nvarchar(200),@CompanyName nvarchar(200),@FirstName nvarchar(200),@OrderNumber nvarchar(9)',@SerialNumber=N'',@EmailAddress=N'',@LastName=N'',@CompanyName=N'',@FirstName=N'',@OrderNumber=N'202039836'
August 8, 2008 at 8:06 am
I'll be blunt. There's little point in indexing that. The multiple constant comparisons and ORs confuse the optimiser, plus there is no one stable, optimal plan for it. It will probably tablescan regardless of indexes.
What I normally suggest to people with that format of query is to construct the string dynamically, only putting in search criteria that are passed in, then execute that string. So, if only the first name and the last name are passed in, the query constructed only contains the first and last names in the where clause.
I discussed this on another thread a couple weeks back. I'll try and find the link for you.
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, 2008 at 8:09 am
Thanks alot gail.....well i have already adviced the developer to modify this query just like the way you said........and now i m monitoring the results
August 8, 2008 at 8:23 am
Look here - http://www.sqlservercentral.com/Forums/Topic540094-338-1.aspx
Haven't I helped you with this query before? It looks awfully familiar.
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply