performance of select count sproc

I have a sproc :
select count(itemId) from Items where itemName = @.itemName and
itemDescription = @.itemDescription and itemParentId is null
The Items table does not have indexes on itemName nvarchar(150) or
itemDescription nvarchar(512) because inserts are frequently made, and such
indexes would kill that performance ...
How to speed up my "select count(itemId) ..." sproc ?Add some indexes?
Seriously - how frequent do you mean by frequent with regard to the inserts?
In order to get the rows back that match you probably need some indexes, but
how many rows are there in the table, what is the structure, and what other
indexes already exist?
Mike John
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:OkNolGEVGHA.1868@.TK2MSFTNGP09.phx.gbl...
>I have a sproc :
> select count(itemId) from Items where itemName = @.itemName and
> itemDescription = @.itemDescription and itemParentId is null
> The Items table does not have indexes on itemName nvarchar(150) or
> itemDescription nvarchar(512) because inserts are frequently made, and
> such indexes would kill that performance ...
> How to speed up my "select count(itemId) ..." sproc ?
>|||John A Grandy (johnagrandy-at-yahoo-dot-com) writes:
> I have a sproc :
> select count(itemId) from Items where itemName = @.itemName and
> itemDescription = @.itemDescription and itemParentId is null
> The Items table does not have indexes on itemName nvarchar(150) or
> itemDescription nvarchar(512) because inserts are frequently made, and
> such indexes would kill that performance ...
> How to speed up my "select count(itemId) ..." sproc ?
You add an index that includs at least of the columns itemName and
itemDescription. If it also includes itemParentID and ItemId,
the index will be covering, which may be even better.
Yes, an index will degrade INSERT performance, but "kill" is too
strong a word. Even if the potential index columns are a tad long
to be in an index.
On the other hand, when you run your SELECT query, you will have a
table lock, and you will block inserts entirely while your query
runs.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I forget to mention this is on SS2K and the itemDescription field does
contain values exceed 900 bytes ... so creation of a compound index is
impossible.
I'm thinking of adding a non-clustered non-uniquedly indexed HashValue
column that is computed from itemName and itemDescription. But how to write
the SQL that computes the hashes for existing rows where itemParentID = null
?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97974A93A02DYazorman@.127.0.0.1...
> John A Grandy (johnagrandy-at-yahoo-dot-com) writes:
> You add an index that includs at least of the columns itemName and
> itemDescription. If it also includes itemParentID and ItemId,
> the index will be covering, which may be even better.
> Yes, an index will degrade INSERT performance, but "kill" is too
> strong a word. Even if the potential index columns are a tad long
> to be in an index.
> On the other hand, when you run your SELECT query, you will have a
> table lock, and you will block inserts entirely while your query
> runs.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||If the problem is the nvarchar being wider than 900 I would ask if it
*really* needs to be Nvarchar. What data is actually stored in there -Ofthe
people use it just in case, but in fact varchar will do the job, and save
half the disc space, and (in your case) allow the index.
Regardless of the above I suspect having an index on name and parentid would
seem to be likely to help. But please give us some facts - how many rows,
what existing indexes and how many inserts per second are you looking at to
make you worry about an index *killing* insert performance.
Mike John
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:%23l6EIoEVGHA.736@.TK2MSFTNGP12.phx.gbl...
>I forget to mention this is on SS2K and the itemDescription field does
>contain values exceed 900 bytes ... so creation of a compound index is
>impossible.
> I'm thinking of adding a non-clustered non-uniquedly indexed HashValue
> column that is computed from itemName and itemDescription. But how to
> write the SQL that computes the hashes for existing rows where
> itemParentID = null ?
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns97974A93A02DYazorman@.127.0.0.1...
>
>|||About half a million rows.
Can't change nvarchar to varchar (not sure why nvarchar but too late to
change)
Existing indexes:
itemId (PK unique non-clustered)
itemParentId (non-unique, non-clustered)
compound index : itemId_itemPartialName (non-unique, non-clustered)
inserts target rate = approx 8 per second
itemId (PK , int , not null)
itemPartialName (nvarchar(24) , not null)
itemParentId (int , null)
itemName (nvarchar(150) , not null)
itemDesc (nvarchar(512) , null)
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:uvienyEVGHA.6048@.TK2MSFTNGP11.phx.gbl...
> If the problem is the nvarchar being wider than 900 I would ask if it
> *really* needs to be Nvarchar. What data is actually stored in
> there -Ofthe people use it just in case, but in fact varchar will do the
> job, and save half the disc space, and (in your case) allow the index.
> Regardless of the above I suspect having an index on name and parentid
> would seem to be likely to help. But please give us some facts - how many
> rows, what existing indexes and how many inserts per second are you
> looking at to make you worry about an index *killing* insert performance.
> Mike John
> "John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
> news:%23l6EIoEVGHA.736@.TK2MSFTNGP12.phx.gbl...
>|||John,
create an index on checksum(itemDescription) and checksum(itemName)|||John A Grandy (johnagrandy-at-yahoo-dot-com) writes:
> I forget to mention this is on SS2K and the itemDescription field does
> contain values exceed 900 bytes ... so creation of a compound index is
> impossible.
Create the index itemName alone then. That will have to do.

> I'm thinking of adding a non-clustered non-uniquedly indexed HashValue
> column that is computed from itemName and itemDescription. But how to
> write the SQL that computes the hashes for existing rows where
> itemParentID = null ?
You could add a checksum column:
chksum AS checksum(itemDescription)
and then index that column. The condition would then have to be:
chksum = checksum(@.itemDescription) and
itemDescrtipion = @.itemDescription
I would try indexing only itemName first, though. Or itemParentID + itemName
if the condition itemParenID is selective.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||What effect will this solution have on the performance of inserts ?
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1143769674.159298.83680@.j33g2000cwa.googlegroups.com...
> John,
> create an index on checksum(itemDescription) and checksum(itemName)
>|||What is wrong with creating a bigint hash col and populating it dynamically
in C# whenever a new item is added ? ( Write a console app to update the
existing rows. ) The hashValue is MD5 algorithm for itemName concatenated
itemDescription (throw a pipe char inbetween to prevent accidental dupes
from being created ).
Then write a sproc which
1. creates a temp table
selects hashValue, count(hashValue) from Items where parentItemId = null
group by hashValue
2. write an update query that sets itemCount for each parentless child item
to the hashCount selected from the temp table joined to the original table
on the hashValue
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97976CD79D92BYazorman@.127.0.0.1...
> John A Grandy (johnagrandy-at-yahoo-dot-com) writes:
> Create the index itemName alone then. That will have to do.
>
> You could add a checksum column:
> chksum AS checksum(itemDescription)
> and then index that column. The condition would then have to be:
> chksum = checksum(@.itemDescription) and
> itemDescrtipion = @.itemDescription
> I would try indexing only itemName first, though. Or itemParentID +
> itemName
> if the condition itemParenID is selective.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx