0

My main concern:

I have an existing table with huge data.It is having a clustered index. My c++ process has a list of many keys with which it checks whether the key exists in the table, and if yes, it will then check the row in the table and the new row are similar. if there is a change the new row is updated in the table. In general there will less changes. But its huge data in the table. S it means there will be lot of select queries but not many update queries.

What I would I like to achieve:

I just read about partitioning a table in sybase here. I just wanted to know will this be helpful for me, as I read in the article it mentions about the insert queries only. But how can I improve my select query performance.

Could anyone please suggest what should I look for in this case?

user1939168
  • 547
  • 5
  • 20
  • You mentioned that it will be primarily select statements, but whether partitioning will help also depends on what you are selecting. Are you selecting a subset of data by date, or other identifier, or are you doing queries on the whole dataset? Without more details on your table structure, and what you are selecting, we can not answer this question. – Mike Gardner Jul 07 '14 at 14:14
  • What do you mean by "huge data"? You should be able to get reasonable performance on millions of records if you've tuned appropriately without partitioning. – Brad Jul 15 '14 at 17:31

1 Answers1

0

Yes it will improve your query (read) performance so long as your query is based on the partition keys defined. Indexes can also be partitioned and it stands to reason that a smaller index will mean faster read performance.

For example if you had a query like select * from contacts where lastName = 'Smith' and you have partitioned your table index based on first letter of lastName, then the server only has to search one partition "S" to retrieve its results.

Be warned that partitioning your data can be difficult if you have a lot of different query profiles. Queries that do not include the index partition key (e.g. lastName) such as select * from staff where created > [some_date] will then have to hit every index partition in order to retrieve it's result set.

No one can tell you what you should/shouldn't do as it is very application specific and you will have to perform your own analysis. Before meddling with partitions, my advice is to ensure you have the correct indexes in place, they are being hit by your queries (i.e. no table scans), and your server is appropriately resourced (i.e got enough fast disk and RAM), and you have tuned your server caches to suit your queries.

Brad
  • 15,186
  • 11
  • 60
  • 74