April 11, 2013 at 2:23 am
Hello,
I hope someone can answer a question I was asked but was not sure about.
If you partition a table by year, but the query does not use year, but some other criteria such as namej for example, does the engine still scan through all the partitions like if would if the table was just standard?
Is it worth partitioning a 2-3 GB table? Would it be a better idea to review the indexes instead if performance is believed to be a little slow?
Thanks in advance.
Regards,
D.
April 12, 2013 at 9:45 am
I believe the answer is yes, if you do not filter on the partitioning column you will scan the entire table, unless you have a covering index.
Our fact tables have several billion rows. The only way I can query them is to filter on the partition key first. Most querying is done in a cube. It takes all day to create a new index, so we make use of the partitioning when we write sql.
For a 2-3 gig table, I don't think that I would bother partitioning.
It is my understanding that partitioning is meant to be used to manage file sizes and fast loading of data, not for query improvement.
You are probably better off working on indexes or tuning code to use existing indexes.
April 12, 2013 at 10:06 am
Partitioning is not (mostly) about performance.
https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply