I need to retrieve the number of rows from syscolumns table
which wd give me the faster result?
select count(*) from syscolumns where object_name(id) = 'employee'
or
select count(1) from syscolumns where object_name(id) = 'employee'
and why?
There is another way using exec sp_spaceused <'tablename'>
Can we retrieve the number of rows in any way by executing this statement?
Is there any way of improving this query?
Thanks,
rose
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Bench mark yourself:
I do not think it will make a big difference:
select getdate()
go
select count(*) from syscolumns where object_name(id) = 'employee'
go
select getdate()
go
select count(1) from syscolumns where object_name(id) = 'employee'
go
select getdate()
go
Now, measure the time and see which is faster.
Hi all
Namasi is obviously top dog here but....
I just wanted to clarify an important point. As I stated, using count(*) vs count(1) is most likely not your major performance issue. In addition, if you simply gather time statistics, then you will most likely not see any major differences. My point was that the IO involved can be significantly larger using count(*) as opposed to count(1). Therefore, it is the IO that should be the focus here nad not the time. Note: The table size will come into play for this senario. Hopefully, you are looking at using 'count(1)' for the general practice of developing the code in your deliverable product. While I do not think that I would recode every SP to "fix" this issue. If I create a new SP and/or modify an existing SP, then I would "fix" these SP(s). Again, not a major P&T issue but why not take advantage of it if you can.
thanks
knel
all this for 20 points :)_
Business Accounts
Answer for Membership
by: knel1234Posted on 2004-01-28 at 07:59:52ID: 10219066
Rose,
The reason to use count(1) instead of count(*) is simple.
If you use count(*), then you are instructing sybase to fetch the complete row of table ABC. If your table has 20 columns totalling 1024K, then sybase must fetch all these bytes (note they are NOT returned to your query but they are touched in the process). If you use count(1) then sybase is just "handling" the 1 byte. This is usually a minor P&T issue, but why not start coding all your queries this way. All those IOs can add up over time.
As far as the sp_spaceused, you might want to go to
www.edbarlow.com then look at his SPs (specifically sp__helptable).
In general
If you find a sybase SP that you like, then the thing to to is to create
a modified version of this SP and save it as "sp__xxxxx". Basically, the double underscore indicates a "homegrown" or internal SP.
hope this helps
knel