January 25, 2013 at 12:43 am
hello,
I have few questions about partition,
something i found on internet, but something no or it was wrong written
So
1. partition is not faster than normal table? true ?
2. we copy lot of data every month, we have one program for this and we need to copy manually, you select all and copy, but i am not sure is the program is really ok and good solution, its about 200-300tables and in all tables are lot of row ( 1000-25000).
I had idea about partition, its is good idea or not ? so first i think , effort of administrator will not like now.
But problem would be, because I would have to change all DB---
so will be any problem with procedures and another queries ?
i would had to change all tables in our db and procedures ? or i can do partition on all db or just on the table.
we have 2DB in server - first actually month, second - another months and years
this is just thinking about partition for better understanding, and if it will be good for us, i will use
January 25, 2013 at 5:46 am
Table partitioning is primarily a mechanism of data management, not of performance tuning. Partitions allow you to add or remove data by working with large chunks, the partition, rather than individual inserts. This means that other parts of the table are isolated off, feeding out the data without interruption. Partitioning really does help there.
As far as performance goes, for really large tables, if you query primarily against the partition first, and then other data second, so that the query always goes against a single partition, you can see performance improvements over a single table. But as soon as you start scanning across partitions, that goes away and performance gets much, much worse than on a single table. You also have to take into account your disks. Just because you've partitioned and you're querying by partition, if everything is on a single disk/lun you're unlikely to see performance enhancements. If you can isolate access to a partition and each partition is on a separate disk/lun, then the queries can run a lot faster.
In short, it's not a magic bullet.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 25, 2013 at 3:53 pm
hello Tony,
I agree with Grant Fritchey. Maybe I can give you a summarized aspect:
Let's say you have data that you can group by month.
Partitioning is great if you want to delete the data of a specific month or want to move it to a another database, kind of archive database. If a table is partitioned this process can be done much faster than with an unpartitioned table.
Of course you will have to do some administrative work creating and maintaining partition functions and partition schemes, but that's a work to be done once and you just have to control it once per month (if we keep the example of partitioning by month).
As Grant Fritchey mentioned there is the danger of getting bad perfomance. In the meantime I know why this can happen.
For partitioning you must define a parition key, for example the month. The partition key has to be part of the primary key, SQL Server will tell you so if when implementing partitioning.
If you have a query using some primary key values you must not forget that your primay key now contaings the partition key too. Let's make an example.
Let's have a table "Invoce", having the primary key "InvoceNumber". You add a column "PartitionKey", which contains the year and month of the creation day.
Usually when searching for an invoice you would use the invoice number in the where-clause. This would end in a clustered index seek.
Now you have to add the partition key column to the primary key. This means that an invoice number COULD is not unique any more. It's only unique together with the parition key.
If your query only seeks for the invoice number you might get an clustered index seek. This is because the quey optimizer seeks any partition seperately for the invoice number. Sometimes the optimizer decides to to an clustered index SCAN instead of a seek. This is where your peformance goes down.
So if you implement partitioning don't forget that your primary keys change. This means your foreing keys have to change too. And of course, your queries too.
Ok, I have written a lot. I'm not sure this helps you but I hope so. It's just a summarize of my experience, being quite new to this topic too. I hope my explanation is not too difficult or theoratical.
Good evening, Wolf
January 25, 2013 at 11:45 pm
Good morning
thank you both for response , it was very good explanation, i understand now,
because i knew before ,how the partition works , but i didnt know about performance etc
and how you said about primary key and etc, i think if we start query on lot of positions/PCs with lot of data i think it will be better, still move data manually to another database. than create partition, because almost in all table we have 25 000 per month.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply