June 23, 2011 at 5:50 am
Hi All,
I am facing problem in getting the count * from a table.
The table contains 1526013586 rows. I am querying it for count * with a where condition for specific item_numbers but it takes long time to retrive the count. Is there any way to make query run fast
Thanks in advance
June 23, 2011 at 5:54 am
Does your table have an index on the column in your where condition ?
MCT
MCITP Database Admin 2008
MCITP Database Admin 2008
MCITP Database Dev 2008
www.jnrit.com.au/Blog.aspx
June 23, 2011 at 6:03 am
You can save this query and run the Database Engine Tuning Advisor against it...
This will create scripts to make your indexes
June 23, 2011 at 6:03 am
Warwick rudd (6/23/2011)
Does your table have an index on the column in your where condition ?
That's the only it'll ever run fast... and ideally that index needs to have that column as the leading index.
If you always search for the same id, then you can add a filtered index and that would run even faster.
June 23, 2011 at 6:04 am
Rhox (6/23/2011)
You can save this query and run the Database Engine Tuning Advisor against it...This will create scripts to make your indexes
NEVER a good idea to blindly do what the tuning advisor suggests.
June 23, 2011 at 6:04 am
vinod.saraswat (6/23/2011)
I am facing problem in getting the count * from a table.The table contains 1526013586 rows. I am querying it for count * with a where condition for specific item_numbers but it takes long time to retrive the count. Is there any way to make query run fast
Most likely query is doing a full table scan.
How many rows out of that billion and a half rows are expected to match the condition?
Is there an index on item_number column?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 23, 2011 at 6:10 am
Hi All,
Its doing the clustered index scan.
June 23, 2011 at 6:18 am
vinod.saraswat (6/23/2011)
Hi All,Its doing the clustered index scan.
... any reason you can't add the non clustered index on the item column??
June 23, 2011 at 6:27 am
Ninja's_RGR'us (6/23/2011)
Rhox (6/23/2011)
You can save this query and run the Database Engine Tuning Advisor against it...This will create scripts to make your indexes
NEVER a good idea to blindly do what the tuning advisor suggests.
Any recommendations for indexes whether they are from DTA or when you are looking at the execution plans and find your query (like your current query performing a CI Scan) needs to be tested.
MCT
MCITP Database Admin 2008
MCITP Database Admin 2008
MCITP Database Dev 2008
www.jnrit.com.au/Blog.aspx
June 23, 2011 at 6:31 am
Its business key column not from the DTA
June 23, 2011 at 6:36 am
vinod.saraswat (6/23/2011)
Its business key column not from the DTA
Please post table definition including keys and actual execution plan (as attached file).
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply