ASE Date range partition

[Former Member]

We have a table that has between 40 and 60 million rows, and holds 2 to 3 years of data. The most recent 90 days of data accounts for over 80% of the queries. Access to this table is very slow and we're looking at partitioning the table to improve performance. This could even include keeping the last 90 days of data on a high speed device.

Is there any way to define a partition and/or local index to work on a dynamic value, such as "the last 90 days"? How do other users manage this problem? Do we need to manually alter the table to keep the most recent 90 days of data on the fast device? Will ASE automatically transfer data between devices when the partition is altered?

I would expect that this isn't a new problem, but I can't find any specific discussions about it.

[Former Member]

Hi all,

Thanks for the replies. I have a bit of learning to do on partitions, and I'll try to be more knowledgeable in the future.

For now, the consensus is to go with 1 year partitions, but I think 6 months would be more efficient. I'm sure we'll try different sizes.

I had already thought of the suggestions Mark made about scripting the data movement to keep the most recent 90 or 120 days in a known partition, but that will be far down the road, if at all.

As to why performance is such an issue, that really needs to be examined more thoroughly. Maybe even bring in someone to do a health check. Potential causes include recent migration from physical SUN hardware running Solaris to Redhat VMs, and upgrading from ASE 12.5 to 15.7. I don't know why they settled on 15.7, since it's EOL date had already been published and 16.0 had been out for over a year.

[Former Member]

Mark. Fair enough about my misuse of EOL for 15.7, but I had seen the 2020 date and figured it meant that was when support ended. Since the DB upgrade was a major migration for the company (performed last year before I came on board), I figured they would have not wanted to go through it again in 4-5 years (after running the last database for well over a decade). But the bleeding edge argument is also valid and this company is extremely risk adverse.

The company brought in a supposed database expert who hasn't worked out well. I think his days are numbered. I know enough about a performance health check to skim the surface, but we really need a guru. A lot of the performance issues are likely going to be found in the 10-20 year shell scripts and stored procs (some of which are >3000 lines), but the tables themselves are large and slow.

I know some really knowledgeable ASE experts from my days in the Sybase PS group, and maybe I can convince powers that be to stop messing around and bring one of them in to fix it.

[Former Member]

No you can't create a partition based on the last 90 days.

Personally, I'd set up partitions based on quarter of years

ie 2015Q1, 2015Q2, 2015Q3, 2015Q4, 2016Q1, 2016Q2, 2016Q3, 2016Q4

- that would mean that you'd only need to access 2 partitions for the last 90 days.

You could set up a partition per month - but that would mean accessing 3 or 4 partitions for the last 90 days

However, the question is why are you queries slow getting the last 90 days. You could just recluster your table around the date to ensure contiguousness over the last 90 days ?

Personally, I partition date on a year basis because getting large data volumes off the SAN is very slow. Partitioning by year keeps the current year in cache and old years on SAN.

And always a good idea to pre-create partitions into the future.