May 13, 2008 at 2:46 am
while for storing 31 days of data into 31 tables (table_1, table_2, ..., table_31), which is better/faster, using
1) if statement (this is pre-compiled)?
DECLARE @iTodayDate int
SET @iTodayDate = DATEPART(day, GETDATE())
IF @iTodayDate = 1
BEGIN
insert into Table_1 ..
END
ELSE IF @iTodayDate = 2
BEGIN
insert into Table_2..
END
2) dynamic query ( MSDN: The Transact-SQL statement or batch in the sp_executesql stmt parameter is not compiled until the sp_executesql statement is executed. )
SET @SQLString = N'INSERT Table_' + CAST(@iTodayDate as varchar(10)) + '( ...)
EXECUTE sp_executesql @SQLString
May 13, 2008 at 3:16 am
Neither. Put all the data into 1 table with a date column
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
May 13, 2008 at 4:49 am
what is wrong with putting the data into one table as Gail mentioned?
is seems like you are making things difficult for yourself
May 13, 2008 at 4:59 am
gan_kim_heng (5/13/2008)
I also think about table partitioning in MSSQL 2005, by using date as partition
Why? What volumes of data are we talking here? Why kind of queries? How fast are you loading data?
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
May 13, 2008 at 6:14 am
As far as the actual question, having a procedure keep it's execution plan is going to be bad for you since you have a 1 in 31 chance that you will even want the execution plan is caches (plans do not cache all possibilities of conditionals).
And now I am getting on the bandwagon - you should not have 31 tables. You should have 1 table with a field indicating the day (or date). Proper indexing on a single table will end up with it performing much better than 31 individual tables and you will not be constantly writing queries 31 times or using dynamic SQL.
May 13, 2008 at 6:51 am
replication is used, but the data is more than 2,147,483,647 (size of int) less than several months (big int will be using), so will need something like partitioning to speed up the thing when SSRS is presented.
May 13, 2008 at 7:58 am
Could you please explain more what you're trying to do and what problems you're encountering.
You have a table with 2.1 billion rows in it?
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
May 13, 2008 at 8:22 am
Not 2.1 billions in one table . Column ID (with identity) seems to be overrflow (because it hit overrflow issue, thus i think it's deleted , not trucated)
this table is actually, the data is stored weekly in to 7 tables in 7 db previously (to shift the workload), the data is trucated weekly (let's say today is monday, the previous monday data will be deleted before today insertion) . Now we are thinkhing the best way to keep the most data (ideally one month) with less overhead on production server(this server is running 24 hrs and keep inserting data from another application). Previously the replication have been used, but still not so fast. Or any better idea. Now one options is MSMQ/JMS/Jboss during insertion, but problem still have when on SSRS, thus thinking a best way for storing the data.
May 13, 2008 at 8:28 am
I am a little confused,
how many rows are you dealing with in your tables?
May 13, 2008 at 8:34 am
I think should be hundreds thousand monthly? why?
May 13, 2008 at 8:55 am
I think a partitioned table (possibly even a federated database) will be your best bet for this. If you really insist on sticking with the 31 tables idea, the dynamic query will be better than the if statement the way you've written it. But I really would use a single partitioned table (or federated database) and query that in the usual fashion.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 13, 2008 at 2:58 pm
I'll go with a simple partitioned table (partitioned on the day of week, or the day of month, depending on data volumes) Don't see any need to add the complexity of federating this.
What kind of reporting are you trying to do on this? Just from Reporting services, or from other apps too?
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
May 13, 2008 at 3:02 pm
GilaMonster (5/13/2008)
I'll go with a simple partitioned table (partitioned on the day of week, or the day of month, depending on data volumes) Don't see any need to add the complexity of federating this.What kind of reporting are you trying to do on this? Just from Reporting services, or from other apps too?
The reason I mention federating is because it was mentioned that this used to be 7 tables on 7 servers, if I'm reading it correctly.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 13, 2008 at 5:58 pm
GilaMonster (5/13/2008)
I'll go with a simple partitioned table (partitioned on the day of week, or the day of month, depending on data volumes) Don't see any need to add the complexity of federating this.What kind of reporting are you trying to do on this? Just from Reporting services, or from other apps too?
I think is normal charting SSRS application, just because the data is too huge to view when presentation, thus need some performance improvement on how the data is stored.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply