Large Structure of a table

Question

Large Structure of a table

sqlenthu_11

SSC Eights!

Points: 800
More actions
October 16, 2012 at 7:41 am

#283501

I need help regarding the issue having on performance and size.
We have a Database : (History_price) which contain all the historical prices of the securities. There is one table per year in this database For. ex History20121231, History20111231........History19981231. Each table contains at-least 50 Million records.
Currently there is Dynamic Store procedure which extracts the data from these tables. There are couple of issues:
1) Performance- SP runs fine if need to get price of 5000 securities, but in order to get the prices more than 5000 securities the SP is taking hell lot of time.
2) Size of Dynamic sql- Every year there is a new table added to this DB, which will increase the dynamic Sql drastically, sson we willl run out of space even after using varchar(Max).
Thanks for reading the post !!!!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

nitin_456 (10/16/2012)
I need help regarding the issue having on performance and size.
We have a Database : (History_price) which contain all the historical prices of the securities. There is one table per year in this database For. ex History20121231, History20111231........History19981231. Each table contains at-least 50 Million records.
Currently there is Dynamic Store procedure which extracts the data from these tables. There are couple of issues:
1) Performance- SP runs fine if need to get price of 5000 securities, but in order to get the prices more than 5000 securities the SP is taking hell lot of time.
2) Size of Dynamic sql- Every year there is a new table added to this DB, which will increase the dynamic Sql drastically, sson we willl run out of space even after using varchar(Max).
Thanks for reading the post !!!!

That sounds like a nightmare. Have you looked at table partitioning as an alternative to adding new tables for historical data?

Are you seriously worried that you don't have enough space to create dynamic sql with varchar max??? That is 2GB of characters. The entire bible had about 3.5 million characters, varchar(max) can hold a little over 2 billion characters. I would imagine that performance is hideous if you have to process that much character data in a variable.

Unfortunately there are no actual details in your post so nobody is going to provide any actual detailed help. The best anybody can do is to say that you need to look into reorganizing some things.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 2

Check update statistics and fragmentation level of the tables

Need look at the table structure,indexes and SP to know more...

sqlenthu_11 SSC Eights! Points: 800 More actions · Answer 3

Hi Sean ,

Thanks for the reply , can you just give me a little bit overview , how can I use partitioning in my current situation.

Thanks.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

Yes! Partitioning is exactly what you should use. This exactly feature made SQL Server to be one of RDBMS's for Enterprise Size solutions.

I would start from here:

http://msdn.microsoft.com/en-us/library/ms188232(v=sql.105).aspx

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

vyas (10/16/2012)
Check update statistics and fragmentation level of the tables
Need look at the table structure,indexes and SP to know more...

Take a look at this article. It is a good overview and has some links to dive deeper. http://www.brentozar.com/archive/2012/03/how-decide-if-should-use-table-partitioning/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/