can anyone tell me how partitioning a table will affect read performance?
My situation: I need to convert a database from one schema to another.
The data is BCP:d into (source and destination are on different platforms) the database and some of the tables are partitioned.
Right now I have a script that first unpartitions the tables, BCP:s data into them, and repartitions them again. Immediately after that I execute my conversion script, that creates new tables (again, some of these are partitioned), copies data using SELECT statements from the old tables and then drops the old tables.
In other words, the tables that I BCP data into are never updated or inserted into, they are just used for reading. I still need to create indexes on them, but can I skip the partition step and save some time?
Originally posted by hacker
Right now I have a script that first unpartitions the tables, BCP:s data into them, and repartitions them again.
Hacker you are on the right track. bcp IN's are much quicker when you have the table paritioned and used in conjuction with Parallesim. Without worker processes configured, the BCP in would be serial and there is not much point in partioning or not.
The BCP is not really a huge problem, since the entire "BCP out of source/BCP in to destination" doesn't take more than 2 hours.
The entire conversion process however takes 13 hours at the moment, most of the time is being spent on statements like:
select into Table (col1, col2)
select col1, col2 from Table_old
where Table_old is the table that had data BCP:d into it. When the above statement is executed, both Table and Table_old are partitioned. The question is whether the table partitions will actually help statements like the above.
I don't have worker processes configured. Should I look into that? The server has 3 dataserver engines on 4 CPUs. My script starts 3 conversion scripts at a time (there are a couple of hundred of them).
Maybe it's better to run just one at a time and configure worker processes? Do I need to look into the parallellism parameters as well?