SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Counting Records


Counting Records

Author
Message
Christopher Stobbs
Christopher Stobbs
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20106 Visits: 2233
As mentioned please could you post the query plan, and could you let us know how many rows are in the table or am I right in saying it's just one?
Could you also post a create table sample for us so we can see what the table looks like. e.g any text fields etc, and of coarse what the index construction looks like.

It seems that your work around might work because the non-clustered index is smaller than the clustered index and hence runs faster but that is guess...
I'm also very interested in the scan count here

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

w00t
Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
sql_prodigy
sql_prodigy
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1982 Visits: 288
ok i was also interested to know how many scans were taking place since i added the non clustered index



(1 row(s) affected)
Table 'EntityCollection'. Scan count 1, logical reads 2351, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 39 ms.
The Dixie Flatline
The Dixie Flatline
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50148 Visits: 6962
Just whenever you get a chance. Thanks, prodigy.

The great thing about this is we all get a deeper understanding of what's going on under the hood. That's one of the best things about SSC.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
sql_prodigy
sql_prodigy
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1982 Visits: 288
@Christopher, yes i the non clustered index was created on a smaller columnCool
Christopher Stobbs
Christopher Stobbs
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20106 Visits: 2233
cool well when you get a chance, it would be nice to see the table and index definitions along with the query plan.

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

w00t
Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
sql_prodigy
sql_prodigy
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1982 Visits: 288
i get a count of 315000.

this is the create table script.

CREATE TABLE [dbo].[EntityCollection](
[EntityIdentifierContext] [varchar](32) NOT NULL,
[EntityIdentifier] [varchar](64) NOT NULL,
[EntityIdentifierSequence] [varchar](32) NULL,
[EntityIdentifierComposite] [varchar](128) NOT NULL,
[Data] [ntext] NULL,
[XML] [xml] NULL,
CONSTRAINT [PK_Entity] PRIMARY KEY CLUSTERED
(
[EntityIdentifierComposite] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF




this is the clustered index
ALTER TABLE [dbo].[EntityCollection] ADD  CONSTRAINT [PK_Entity] PRIMARY KEY CLUSTERED 
(
[EntityIdentifierComposite] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]




this is the non clustered index i added
CREATE NONCLUSTERED INDEX [idx_entity_identifier] ON [dbo].[EntityCollection] 
(
[EntityIdentifier] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


sql_prodigy
sql_prodigy
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1982 Visits: 288
this is the query plan without the non clustered index which produced 9 table scans

|--Compute Scalar(DEFINESad[Expr1003]=CONVERT_IMPLICIT(int,[globalagg1005],0)))
|--Stream Aggregate(DEFINESad[globalagg1005]=SUM([partialagg1004])))
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINESad[partialagg1004]=Count(*)))
|--Clustered Index Scan(OBJECTSad[Wallet0000].[dbo].[EntityCollection].[PK_Entity]))



this is with the index i added
[code] |--Compute Scalar(DEFINESad[Expr1003]=CONVERT_IMPLICIT(int,[Expr1004],0)))
|--Stream Aggregate(DEFINESad[Expr1004]=Count(*)))
|--Index Scan(OBJECTSad[Wallet0000].[dbo].[EntityCollection].[idx_entity_identifier]))
J-F Bergeron
J-F Bergeron
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7935 Visits: 2707


this is the non clustered index i added
CREATE NONCLUSTERED INDEX [idx_entity_identifier] ON [dbo].[EntityCollection] 
(
[EntityIdentifier] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]



Yes, it's obvious the query time improves, now that you have a nonclustered index that is twice as small as your Clustered index. It would be even better if you used "EntityIdentifierSequence" as the nonclustered, since it's also twice as small as your new nonclustered index.

Now that you get a real good time running this query, that is because the query is cached in the sql server, if you want to have a real portrait of the time the query will take on the first run, you should use this:


dbcc dropcleanbuffers;
dbcc freeproccache;

select Count(*) from TheTable;




It will give you the exact time the query will take on the first run, or when the results are not cached, and please post the .sqlplan for this (.sqlplan zipped, so we get a graphical view), I'm sorry to ask again, but I'm really curious to see what could be the issue on this, going from 9 scans, to 1.. that is weird in my book!

Thanks,

Cheers,

J-F
Christopher Stobbs
Christopher Stobbs
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20106 Visits: 2233
Just a side not for anyone that is interested.

I've created a table on my test system with two columsn
VARCHAR(900) --Max size for a clustered index
VARCHAR(32)

even when I create two index one clustered on the first col and one nonclustered on the second column, my query always uses parallelism and always uses the first index(the bigger one) and always runs slow.

I'm going to trying to find out how the optimizer decides which index/column etc to use for a count :-)
wish me luck

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

w00t
Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
J-F Bergeron
J-F Bergeron
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7935 Visits: 2707
Christopher Stobbs (5/13/2009)
Just a side not for anyone that is interested.

I've created a table on my test system with two columsn
VARCHAR(900) --Max size for a clustered index
VARCHAR(32)

even when I create two index one clustered on the first col and one nonclustered on the second column, my query always uses parallelism and always uses the first index(the bigger one) and always runs slow.

I'm going to trying to find out how the optimizer decides which index/column etc to use for a count :-)
wish me luck


I'm interested to know what will be the results of those queries, and how the optimiser will behave!

I'm guessing a
select count(*) from tableX


should take the most updated statistics or the index with the smaller size. Well, that would make sense, unless all indexes were updated at the same time.

You might as well want to try

select count(smallerIndexedField) from tableX



to see if it changes from the Select count(*), and if it forces the optimiser to choose the index, or at least, help it take the decision?

Anywayz, update this thread if you get a chance, I'm interested in having a conclusion on this, Wink

Good luck,

Cheers,

J-F
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search