Hi,
I'm trying to count rows in each of the tables in an SQL DB. I tried the
following ExecuteScalar command:
"Select Count(*) from " & mTableName
The problem is that one of the tables is huge and I'm getting a time out.
I thought I could do this with a stored procedure using the TableName as a
parmeter, but I couldn't get this to work. I thought that I could somehow
use the metadata (maybe from GetSchema) to just ask for the row count. I
couldn't figure that out either.
Can anyone help me out with this?
Art 7 4353
Art,
Try the following query.
SELECT
rowcnt
FROM
sysindexes
WHERE
indid <= 1
and [id] = OBJECT_ID('YourTableNameGoesHere')
I don't believe the value you get back is guarenteed to be accurate
though. You may have to execute DBCC UPDATEUSAGE to get the value to
match a SELECT COUNT(*) statement.
You'd get a better response if you posted this question in a SQL Server
group instead.
Brian
Art wrote: Hi,
I'm trying to count rows in each of the tables in an SQL DB. I tried the following ExecuteScalar command:
"Select Count(*) from " & mTableName
The problem is that one of the tables is huge and I'm getting a time out.
I thought I could do this with a stored procedure using the TableName as a parmeter, but I couldn't get this to work. I thought that I could somehow use the metadata (maybe from GetSchema) to just ask for the row count. I couldn't figure that out either.
Can anyone help me out with this?
Art
Brian,
Thanks -- I'll give it a try. The reason I didn't post this in a SQL Server
forum is that I thought that the answer might be a .net ADO method.
For my current purpose I just need orders of magnitude of the tables, so
that if your method gives me close answers, that will be good enough.
Thanks again.
"Brian Gideon" wrote: Art,
Try the following query.
SELECT rowcnt FROM sysindexes WHERE indid <= 1 and [id] = OBJECT_ID('YourTableNameGoesHere')
I don't believe the value you get back is guarenteed to be accurate though. You may have to execute DBCC UPDATEUSAGE to get the value to match a SELECT COUNT(*) statement.
You'd get a better response if you posted this question in a SQL Server group instead.
Brian
Art wrote: Hi,
I'm trying to count rows in each of the tables in an SQL DB. I tried the following ExecuteScalar command:
"Select Count(*) from " & mTableName
The problem is that one of the tables is huge and I'm getting a time out.
I thought I could do this with a stored procedure using the TableName as a parmeter, but I couldn't get this to work. I thought that I could somehow use the metadata (maybe from GetSchema) to just ask for the row count. I couldn't figure that out either.
Can anyone help me out with this?
Art
Brian,
I just wanted to let you know that I tried it and it appears to work great!
Thanks again.
Art
Art wrote: I just wanted to let you know that I tried it and it appears to work great! Thanks again.
Although you've found a suitable method, it might be worth pointing out that
you don't have to retrieve everything from the database to count it the
rows: count(1) gives the same answer as count(*).
Andrew
Andrew,
SQL Server generates the exact same execution plan for either one. At
least it did for me and I tried it on a dozen tables each with
different kinds of indexes. It was always doing an index or table
scan.
Brian
Andrew Morton wrote: Although you've found a suitable method, it might be worth pointing out that you don't have to retrieve everything from the database to count it the rows: count(1) gives the same answer as count(*).
Andrew
Andrew Morton wrote: Although you've found a suitable method, it might be worth pointing out that you don't have to retrieve everything from the database to count it the rows: count(1) gives the same answer as count(*).
Hmmm... that may not be at all useful. I tried Query Analyzer with both
methods with "set statistics io on" and there didn't seem to be any
difference.
Andrew
Brian Gideon wrote: Andrew,
SQL Server generates the exact same execution plan for either one. At least it did for me and I tried it on a dozen tables each with different kinds of indexes. It was always doing an index or table scan.
Agreed. I can't remember exactly where I read about using count(1) instead
of count(*), and I was unfortunate to find it rather than an article saying
"don't bother". Apparently it's written in quite a lot of places. I, for
one, won't be writing it again.
Andrew This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Matt Schroeder |
last post by:
Does anyone know how to count how many rows are in a mysql table? This is
what I have, but it doesn't work right:
<?
$db = mysql_connect("localhost", "username", "password");...
|
by: Lord Merlin |
last post by:
How do I add up the values of the data from all of the rows returned? i.e -
I have a DB, with 148 rows of client info, and I want to addup all the
scores. Each entry's score differes. Here is the...
|
by: DavidC |
last post by:
Anyone know how to count rows in a table depending on 2 columns, and
different values within the second column.
Basically i am trying to obtain the resultset example below from the table
example...
|
by: walexand |
last post by:
I use the database mysql v.4.
My problem is...
I have a select like:
select * from user where language = "de";
the result are then:
id name
===================
1 max
|
by: Viswanatha Thalakola |
last post by:
Hello,
Can someone point me to getting the total number of inserts and updates on a table
over a period of time?
I just want to measure the insert and update activity on the tables.
Thanks....
|
by: Adam |
last post by:
In my MYISAM table I have an index (Index_A) on 2 fields (Field_A,
Field_B). There are millions of rows in the table. The cardinality of
Index_A is 53. I think a query to count the number of rows...
|
by: Megan |
last post by:
hi everybody-
i'm having a counting problem i hope you guys and gals could give me
some help with. i have a query that retrieves a bevy of information
from several different tables.
first let...
|
by: David |
last post by:
Hi, I have a table that for ease has this data in:
R1, R2, R....z
---------------------
A | 12
A | 22
A | 30
B | 0
B | -1
B | -3
|
by: josur |
last post by:
Hello,
Im a new user of SSAS and at the moment im trying to browse a cube to output the total of contacts within a criteria.
I have one measure(fact table) named Contacts Count and 4...
|
by: akadeco |
last post by:
Hi
I need to write a script that will allow me to check the value of any link in a table cell. The desired result would be something like this: row.cell.innerHTML, allowing me to call the contents...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |