July 19, 2011 at 7:07 am
I have this simple query:
SELECT Count('x') FROM PayeeMaster PM
INNER JOIN PayeeLink PL (nolock) ON PM.PayeeID = PL.PayeeID
that results in an index scan (i tried to attach plan but being blocked)
I have a clustered index for both tables that has all fields, but yet I still get an index scan and not a 'seek'....
Guessing it has something to do with the Primary keys, since it shows in the Execution plan as using the PK_. How can I get it to do an index seek and keep my existing primary keys?
July 19, 2011 at 7:12 am
you are asking for a count to be returned, that cannot be done by a seek. the smallest index will be scanned to return the count value.
---------------------------------------------------------------------
July 19, 2011 at 7:15 am
thank you
July 19, 2011 at 7:17 am
anway to get the count in the above query without using 'count'?
July 19, 2011 at 7:28 am
SELECT
OBJECT_NAME(object_id) AS ObjectName
, object_id
, SUM(rows) AS rows
, SUM(data_pages) data_pages
, CONVERT(DECIMAL(18,2), SUM(data_pages) * 8.0 / 1024 / 1024) GBs
FROM
sys.partitions p
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE
index_id IN (0, 1)
GROUP BY
object_id
HAVING
SUM(rows) > 0
July 19, 2011 at 7:28 am
One way would be to hi the sys.partitions table. Here's an example of a query that returns row counts for every table in the msdb database. You could take this and easily tweak it for your needs by changing what db it's pointing to, and possibly adding a where clause on the table name.
select
sum (spart.rows) as CntOfRows,
t.name
FROM msdb.sys.partitions spart
inner join msdb.sys.tables t on spart.object_id = t.object_id
where spart.index_id < 2
group by t.name
order by CntOfRows desc
A.J.
DBA with an attitude
July 19, 2011 at 7:34 am
if you are just after a rowcount then
sp_spaceused after a dbcc updateusage,
rowcnt in sysindexes where indid = 0 or 1,
a max value if you know you have consecutively increasing value with no gaps
---------------------------------------------------------------------
July 19, 2011 at 7:39 am
I'm having trouble with the syntax when I try to add this to my query:
SELECT Count('x') FROM PayeeMaster PM
INNER JOIN PayeeLink PL ON PM.PayeeID = PL.PayeeID
INNER JOIN PaymentParent PP (nolock) ON PL.UserID = PP.UserID AND PL.PayeeRefNo = PP.PayeeRefNo
INNER JOIN PaymentChild PC (nolock) ON PP.UserID = PC.UserID AND PP.PaymentRefNo = PC.PaymentRefNo
INNER JOIN AccountProfile Acct (nolock) ON PP.UserID = Acct.UserID AND PP.AccountRefNo = Acct.AccountRefNo
July 19, 2011 at 7:42 am
sys.partitions is a count of all rows in the table, nothing more.
If you want to count rows of a query, use count(*) (count('x') just looks silly). It will be index scans, you're asking SQL to read the entire of every table, the most efficient way to do that is to scan it. A seek on every single row of the table is blithering stupid, SQL will not do that, it knows better. (a seek on every row would require 2-3 page reads per row, whereas a scan requires one read per page)
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
July 19, 2011 at 7:43 am
krypto69 (7/19/2011)
I'm having trouble with the syntax when I try to add this to my query:
SELECT Count('x') FROM PayeeMaster PMINNER JOIN PayeeLink PL ON PM.PayeeID = PL.PayeeID
INNER JOIN PaymentParent PP (nolock) ON PL.UserID = PP.UserID AND PL.PayeeRefNo = PP.PayeeRefNo
INNER JOIN PaymentChild PC (nolock) ON PP.UserID = PC.UserID AND PP.PaymentRefNo = PC.PaymentRefNo
INNER JOIN AccountProfile Acct (nolock) ON PP.UserID = Acct.UserID AND PP.AccountRefNo = Acct.AccountRefNo
No other way than count(*) for this scenario.
July 19, 2011 at 7:47 am
I'm pretty sure we told you already but you really need to read this and THEN decide if you risk using nolock.
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
July 19, 2011 at 7:49 am
okay thanks everyone
good karma to you all thanks for taking the time top answer..
July 19, 2011 at 7:51 am
yeah I know...I've talked to the devs about no lock...
they stopped using it, and I remove it where/when I see it...
July 19, 2011 at 8:20 am
my dev just emailed me back and said that if I look at this query it uses 'SEEK' not scan and he;s right!!
what the hell?
SELECT Count('x') FROM PayeeMaster PM
INNER JOIN PayeeLink PL (nolock) ON PM.PayeeID = PL.PayeeID
Where userid='10041'
above results in seek
SELECT Count('x') FROM PayeeMaster PM
INNER JOIN PayeeLink PL (nolock) ON PM.PayeeID = PL.PayeeID
above results in scan
July 19, 2011 at 8:24 am
It's not the same query.
The first one filters out more rows. While doing the seek, it can also count.
Also the estimated # of rows returned must be really low (% of whole table) for the seek to be used as the plan.
http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply