Speeding up a select count(*)
-
I am using the following query:
Select count(*)
From dbname..tablename
Where sdate=CONVERT(varchar, GETDATE()-1, 101)
The table has 12 columns. The sdate column is in the PK with 3 other fields. The total count of the table is around 10 million records. The above query brings back 128k records but it takes around 3 minutes. Is there any way I could speed this up?
Thanks for your help!
-
Is there an index on the sdate column?
-
No, none other than the PK generated index.
-
Try this:
Select count(1)
From dbname..tablename
Where sdate=CONVERT(varchar, GETDATE()-1, 101)
/**A strong positive mental attitude will create more miracles than any wonder drug**/
-
I actually tried the count(1) method previously, but read a good article on google where that method was tested against count(*) and it makes no difference whatsoever.
-
Exactly..., they both find the first usefull index and scan it to get the row count.
-
Try adding one and see if an index seek is used to get the count.
-
See my article here at SSC for an explanation why that would not do any difference: Advice on using COUNT()
-
I added the index, it comes back in 1 second now. Yes an Index Seek was used to get the count. Is this my solution? Could I just drop the PK and create a Unique Index on the 4 fields with sdate being first? If I am off base here let me know.
-
Depends on the other queries. I don't have the info required to make that decision for you. Is 1 second aceptable for this query??
If so I don't see any need to change anything else on that table... unless you have another slow query.
-
I haven't tested, but I would suspect that using this WHERE clause should be faster:
WHERE DateDiff(day, GetDate(), sDate) =1
Mark -
1 second is very acceptable, but I am just exploring other options and creating the 4 field unique index on the table versus having a 4 field PK with another index seems more efficient.
-
No chance, function on a column = SCAN.
If you are inferring that a math operation is faster than a convert between datatypes, then you are right. But I would assume that the server runs that convert only once so it shouldn't be an issue.
-
We can have a look if you want.
Please post the ddl of the table (including indexes).
Post all the queries (with execution plan) ran on that table and their frequency, their normal run time and their acceptable run time and your proposed index modification.
Then we'll see if there's something better to do.
-
Actually I am just going to go with the PK and additional index. Thanks for everyones help! Very much appreciated!
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply