Tag Info

New answers tagged

0

For what it's worth, to get the read-speed I needed I ended-up taking a different approach (although date I say it on a database Q&A site!). Instead of using a database table to store the data, I created a text file with one line per value, sorted alphabetically. Whenever I need to query to see whether a given value exists, I use a binary search ...


1

For the portion of the question asking about compression, Dave is correct on the trade off. I would investigate adding more capacity to your system before you head down the road of compression. For the partition portion the answer is going to be it depends. Partitioning takes a lot of planning and analysis of your current queries before you can measure ...


2

Sorry to disappoint, but your boss is right on target. Some of us BI folks know about DB optimization, at least a little. =) As with any major architectural change you need to test and adapt appropriately to your unique environment, workload, servers etc. Due to heavy blocking, we can't purge data while the database is online. Table partitioning is ...


0

The answer over here is well written and makes arguments similar to zgguy's answer, that partitioning doesn't buy you much, if any, benefit to a single-machine scenario where the most frequent lookups are predicated on the primary key or something similar (because indexed lookups should be just as fast). In fact, a common thread of advice seems to be that ...


0

Split above current maximum value, drop the empty MAXVALUE partition, then convert to interval partitioning: alter table parttest4 split partition p_max at (2030) into (partition p_2030, partition p_max) update global indexes; alter table parttest4 drop partition p_max; alter table parttest4 set interval (10);


3

There is no silver bullet for performance problems, and partitioning is not one either. Every partition is essentially a table for itself. Hence queries that are written in a way that allows the database to look for rows in only one partition become faster. Difference can be huge for queries that would need to scan the entire large table, but can restrict ...


1

Very first thing comes to mind is partition pruning; if that's not something your queries can use. Are your going to need the purging large amount of data from the table as partitioning would help you out. Though old but this post from Peter has few points to consider. and another thing one can think of is ease of use for simple tables... partitioning ...


0

You may want to try Percona's pt-online-schema-change pt-online-schema-change emulates the way that MySQL alters tables internally, but it works on a copy of the table you wish to alter. This means that the original table is not locked, and clients may continue to read and change data in it. pt-online-schema-change works by creating an empty copy of ...


0

This is not a perfect solution, but maybe better than a 100% downtime: Make a script (bash/sql) to first rename the db.table to db.tableBig, and immediately recreate db.table. This will happen in a split second, so inserts to your table can continue. Do your repartitioning. 'Alter table' or 'csv dump + load data', I don't know what is fastest. For the ...


1

Since #1 requires copying data from the master to the child while it is in an active production environment, I personally went with #2 (creating a new master). This prevents disruptions to the original table while it is actively in use and if there are any issues, I can easily delete the new master without issue and continue using the original table. Here ...


1

Backing up a step -- to answer the question behind the question: How many rows in the table? PARTITIONing is rarely of any benefit on tables with fewer than a million rows. If your goal is to make use of multiple drives, RAID striping is better and any manual spreading of data between drives.


1

There are a couple of issues here. First, the partition/filegroup mapping shown in your question does not match the DDL. A RANGE RIGHT function boundary is inclusive of the partition on the right so the results should be as below to match the DDL. Consider downloading the partition details custom report from codeplex that can run from an SSMS object ...



Top 50 recent answers are included