Count for a query for partitioned table

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 2008 Forums

Transact-SQL (2008)

Count for a query for partitioned table

Author

Topic

Masum7
Starting Member

33 Posts

Posted - 2011-06-24 : 16:24:49

Hi,
I have a partitioned table named "Logs". Unfortunately count works too slow on any query on the table.

I have searched internet for a solution, but solutions are for getting count for whole table. But I need count for a specific query.

My main query that fetches result looks like following:
SELECT * FROM (
SELECT TOP 50000
Id,
CASE WHEN ContactId=0 THEN ContactNumber ELSE (SELECT Name FROM Contacts WHERE Id=ContactId) END AS Destination,
ROW_NUMBER() OVER(ORDER BY Id DESC) AS ORank
FROM WebSMS
WHERE IsDeleted=0 AND UserId='xxxxxxxxxxxx'
ORDER BY Id DESC
) As tbl WHERE ORank BETWEEN 1 AND 10

========
I need to get count for the inner portion but without any TOP. That is I need to count for the following:

SELECT
Id,
CASE WHEN ContactId=0 THEN ContactNumber ELSE (SELECT Name FROM Contacts WHERE Id=ContactId) END AS Destination,
ROW_NUMBER() OVER(ORDER BY Id DESC) AS ORank
FROM WebSMS
WHERE IsDeleted=0 AND UserId='xxxxxxxxxxxx'

========================

Please show me a way, I will grateful. Again, please note, table is partitioned table using a datetime field.

Masum

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-06-24 : 18:25:10

I'm not sure what you are looking for so let me start with a few questions:
1) Why are you talking about a table partitioned by datetime when none of the logic uses datetime?
2) Why can't you just remove the "TOP 1000" and add a COUNT(*) to your select list?

Elaborate a bit and we'll see if we can shed some light.

=======================================
I have never met a man so ignorant that I couldn't learn something from him. -Galileo Galilei, physicist and astronomer (1564-1642)

Masum7
Starting Member

33 Posts

Posted - 2011-06-24 : 19:06:05

Dear Bustaz,
Thanks for your kind reply. Please find the answers below:
1) Though this logic does not use datetime, yet other queries use it. Furthermore, to distribute data evenly between partition, I choose datetime
2) COUNT(*) works too slow. In the table there are millions of rows. Basically currently I am using this:
SELECT COUNT(*) FROM WebSMS WHERE IsDeleted=0 AND UserId='xxxxxxxxxxxx'

But it works too slow

Masum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources