select count(*), with a cap? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

select count(*), with a cap?

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-01-22 : 15:15:09

I've got a table with a couple of million rows. For some stats, I need to get approximate rather than exact counts.

Of course, a simple select count(*) with a bunch of criteria can take minutes to run. I really don't care if there are 30,000 matches or 100,000 matches; I just need to know if there are at least 500 matches.

Can anyone think of creative way to accomplish this with less query cost than the outright count(*)? I'm thinking of doing an INSERT / SELET TOP N into a table variable, but that seems inelegant.

Is there any other way to know if there are more than X records matching a criteria?

Thanks
-b

chadmat
The Chadinator

1974 Posts

Posted - 2002-01-22 : 15:53:36

I think select count(*) is going to be faster than any other solution.

-Chad

chadmat
The Chadinator

1974 Posts

Posted - 2002-01-22 : 15:56:27

I think select count(*) is going to be faster than any other solution.

-Chad

SqlZ
Yak Posting Veteran

69 Posts

Posted - 2002-01-22 : 16:21:56

If you are doing a SELECT Count(*) FROM tablename WHERE xyz='someval' then I would just create an index on column xyz and be done with it. If its a couple million records your going through, indexes are your friend and count(*) would be the quickest route.

========================
My username represents the two things that consume most of my time.
SQL, and my Z

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-01-22 : 16:21:57

How about...


select count(*)
from mytable
where recid in (select top 500 recid from mytable)
having...

This will stop it at 500 records if it gets that high. Haven't tested it so I cant say if it would be faster than the same query without the "in" clause againt a table with several million records.

Justin

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-01-22 : 16:34:06

The problem is that I've got about 8 criteria and 4 tables, 3 of which are user defined functions; it's more like

[code]
select count(*)
from table1
left join table2 on table1.i=table2.i_table1
left join table3 on table1.i=table3.i_table3
join table4 on table1.i=table4.i_table1
where table1.columnA=@iCriteria1
and table1.columnB=@iCriteria2
and table1.columnC=@iCriteria3
and dbo.f_function1(table2.ColumnA,table3.ColumnA)<@iCriteria4
and dbo.f_function2(table1.ColumnD) in dbo.f_function3(table4.ColumnA)

...it's very computationally expensive for all of the functions, and I'd like it to stop after getting to a certain point.

(I've got indexes where they can be used, of course)

I'm going to post another related note about execution plans and functions in another thread.

Thanks
-b

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-22 : 20:40:58

This might be worth a try.
The derived table would probably end up similar in performance to the table variable but is neater.

I take it yuo have indexes on the i_tablen?
table1.columnX
I would guess it is the functions that are causing problems
especially dbo.f_function2(table1.ColumnD) in dbo.f_function3(table4.ColumnA) - nasty corellated in clause.
Maybe worth prepopulating a temp table with some values to make it easier on the optimiser.

select count(*)
from
(
select top 500 table1.i
from table1
left join table2 on table1.i=table2.i_table1
left join table3 on table1.i=table3.i_table3
join table4 on table1.i=table4.i_table1
where table1.columnA=@iCriteria1
and table1.columnB=@iCriteria2
and table1.columnC=@iCriteria3
and dbo.f_function1(table2.ColumnA,table3.ColumnA)<@iCriteria4
and dbo.f_function2(table1.ColumnD) in dbo.f_function3(table4.ColumnA)
) as a

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources