November 30, 2015 at 12:45 pm
I have a table that is partitioned into multiple filegroups and spread out over multiple tables b y month. My partition scheme is using a date column. What I would like to do is something like the following:
SELECT a
, b
, c
FROM<table>
WHERE date between '2015-08-01' AND SYSDATETIME()
I am wondering if there is a way to do it without the need to use a UNION ALL on all the tables. Any advice would be appreciated. Thank you.
November 30, 2015 at 1:45 pm
If you have a partitioned table, as in CREATE TABLE ... ON <PartitionScheme(Column)>, then the query as you've written will work. A partitioned table is one table, you query it as a single table.
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
November 30, 2015 at 2:03 pm
I actually switched the partitions to other tables that follow a <tablename>_yyyymm format so I would have the data in each table for that particular month. I am starting to think that may have been over kill. I am trying to improve the overall performance of select statements made on the particular table.
November 30, 2015 at 2:25 pm
In that case you need to do a UNION ALL between the selects of each table.
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
November 30, 2015 at 2:35 pm
Okay.... I have tried both the UNION ALL option and the Select with all of the data in one table and the performance is roughly the same. I think I maybe approaching this the wrong way.
December 1, 2015 at 2:12 am
Once more with feeling... 🙂 Partitioning is not for performance. It's for data management.
https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/
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
December 1, 2015 at 7:27 am
The table is pretty large and it was becoming a little cumbersome to manage (it is a detailed table). I could provide some time of sums for this table but they do not want the information summed. They want the details. I partitioned it to be able to handle it better, and thought maybe it may give me some performance increase. Alas I was wrong. Record retrieval from this particular table is taking entirely too long even with the proper indices. At this point, I am not sure where to go from here as there is a requirement for this query to run in under a second. I will attempt another approach. Thank you for your help. I think you maybe at a high level of SQL master than Qui-gon perhaps Yoda?!?!?
December 1, 2015 at 7:45 am
They want detail? How many rows are we talking about? If it's over a hundred or so, they're not going to be reading the data. Maybe worth taking some time to chat about what they do with the details?
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
December 1, 2015 at 7:58 am
One dataset in particular is returning 650K + rows. It's supposed to pass through an API so the end user will be able to import it into their system and run their own stats on their end.
December 4, 2015 at 10:27 pm
Delivering 650K+ rows to a client consuming it as a rowset is going to prove to be quite the difficult challenge if your SLA is 1 second or less, no matter how narrow your resultset is or how performant your raw query is. At this point it is not a SQL Server problem, it's a throughput problem. You won't be able to pull that much data, deliver it as a rowset, get it over the wire to the caller and have them consume that much data (yes, them having consumed every row from your rowset is included in the overall query time)...it's too much data. If you want to get a sense of your side of the equation run your query using BCP and write the results to a native-format file on a disk attached to your server and see how fast it goes. Then compare that to the time it is taking to send all the data to the caller. The delta will give you a decent sense of the overhead introduced by sending the results to the caller and you can use that to begin to explain to the client what is going on.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 9, 2015 at 1:51 pm
I apologize for the late reply. I spoke with the PTB and we have decided that under a second SLA is not necessary for this query. Also I didn't realize it, but we will be using pagination as well. Thank you for the reply.
December 14, 2015 at 5:29 am
Are the columns of the table the correct data type to store the biggest value defined in the business requirements?
You could just select out converted values, leaving the base table as is if you can safely convert the values to something smaller without losing any information.
I have seen people use NVARCHAR for columns, but on analysing further, I found that for these columns, there was no difference between the varchar and nvarchar of that column.
The reason the DB was full of NVARCHAR was due to a graduate having heard about unicode in Algorithmics101.
This ends up doubling the size of the columns, and network throughput needed.
Also, if your max int value is say, 10, due to it being a typeID column, you could make it tinyint and save 75% for that column.
So you can reduce amount of data needed to be put over the network by optimising data types.
Of course, properly sizing the columns on the DB is the first prize.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply