Created a new SQL Partition but my select statement wont work - why?

Question

Created a new SQL Partition but my select statement wont work - why?

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

isuckatsql SSChampion Points: 10381 More actions · Answer 1

SQLKnowItAll (6/4/2012)
isuckatsql (6/4/2012)
I have four years of data that i would like to partition.
Would it be best to do four partitions on one year, or eight partitions on six months, and allow the overflow to the next partition, if a user wanted to search on a twelve month period?
Thanks
My first question would be "Why do you want to partition it?" There may be good reasons, but I don't know your setup. Those reasons would also help to dictate how you will set up the partition ranges.

4+ million resumes(and rising rapidly) that are being accessed using Full Text Indexed tables based on input dates.

At the moment, the best performance we are getting out of the SQL query is about 4 seconds for one search term eg. Java, for a six month period.

The SQL Query has already been tuned, and we have used data compression on the main indexes.

Table partitioning seems to be the next best thing to try.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Partitioning in this case may not buy you anything. You say you have tuned the query, what does the indexing look like on the table(s)?

Jared SSC Guru Points: 61793 More actions · Answer 3

isuckatsql (6/4/2012)
SQLKnowItAll (6/4/2012)
isuckatsql (6/4/2012)
I have four years of data that i would like to partition.
Would it be best to do four partitions on one year, or eight partitions on six months, and allow the overflow to the next partition, if a user wanted to search on a twelve month period?
Thanks
My first question would be "Why do you want to partition it?" There may be good reasons, but I don't know your setup. Those reasons would also help to dictate how you will set up the partition ranges.
4+ million resumes(and rising rapidly) that are being accessed using Full Text Indexed tables based on input dates.
At the moment, the best performance we are getting out of the SQL query is about 4 seconds for one search term eg. Java, for a six month period.
The SQL Query has already been tuned, and we have used data compression on the main indexes.
Table partitioning seems to be the next best thing to try.

So, you want to partition to get a gain in performance? Why do you think that partitioning will give you better performance? Do you already have an index on the input dates?

Jared
CE - Microsoft

isuckatsql SSChampion Points: 10381 More actions · Answer 4

This is my current execution plan.

To search 6 months of resumes for 'java', takes about 4 seconds and returns 22k records.

I would like to get that time down to two seconds.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Don't waste your time. Partitioning is not for performance. It's for ease of maintenance, bulk loads or removal of data from a table and possibly spreading the data over multiple disks (though files in a filegroup can do that about as well)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Try breaking the query down to use an intermediate temp table rather than a CTE, see if that helps at all. Maybe try breaking it down even further with more temp tables.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

isuckatsql SSChampion Points: 10381 More actions · Answer 7

GilaMonster (6/4/2012)
Try breaking the query down to use an intermediate temp table rather than a CTE, see if that helps at all. Maybe try breaking it down even further with more temp tables.

The original temp table was quite a bit slower than CTE, which is why we went the CTE route.