Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Ghanta
Yak Posting Veteran
96 Posts |
 Posted - 2011-06-01 : 02:11:51
|
| Hi Guys,We have a situation and we need some insight from experienced folks here...We have a couple of database which has financial data. Data is sent everyday in XML file which is flattened and loaded into tables in say DATABASE "A". Each of these tables (one for each file) have few billions of records and have indexes as per need. Tables have paritions based on the datefield which if often used by queries. Data from these tables are further loaded into a database which is used by front-end applications.Bunch of users have access to Database "A" which has those huge tables. They run queries against the data and create data-sets (exports data) to be used for their analysis or do their analysis against the data. What we need is a separate DATABASE "B" which they should use for that purpose... Now how should I create the DATABASE "B" so that the query runs faster? Their analysis and export is faster? We are using SQL Server 2008 right now.. Do we have to use tools like Netezza? Any suggestion is appreciated.Thanks a lot. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-01 : 05:33:23
|
| Depends on what they want to do with it.Would analysis services be applicable here - it gives an interface that means the users don't need to be experts in sql to query large amounts of data.I've usualy done this by creating aggregates on sunsets on the large server which are then exported to the smaller server (via files to disconnect the two). Some of this will be a complete aggregation and some an incremental detail depending on the need.That's assuming that the two servers are very different in power and space - otherwise you might wnat to just load a subset of data direct from the xml files.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Ghanta
Yak Posting Veteran
96 Posts |
Posted - 2011-06-01 : 09:56:50
|
| Nigel thanks for the response. Query the analysts runs or data-sets they create vary a lot case by case and hence I do not think we can come up with set of aggregates that they can use... any more suggestion or things that I look for? Thanks a lot for your response. |
|
|
|
Ghanta
Yak Posting Veteran
96 Posts |
Posted - 2011-06-01 : 10:08:44
|
| Do you think breaking huge tables into small ones a good solution? Also can there be any issues in partitions which might make the query run slow? Do we need RAID? What about tool like Netezza? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-01 : 10:13:45
|
| I've been to a few companies that have said they need netezza but when I've looked at what they wanted to do it wouldn't help.You need to look at what the analysts want to do. There is no generic solution - you have a choice between designing for the analysis they might want to do or giving them access to all the data.Sounds like you want to do the later in which case if you don't look at their requirements you are stuck. They will always be able to run queries that will bring down the system and any product will either restrict what they can do, come with a long training period or probably both.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Ghanta
Yak Posting Veteran
96 Posts |
Posted - 2011-06-01 : 10:25:54
|
| Nigel so you are suggesting creating data-marts, cubes, aggregrates for most of the analysis they do? They export data (create data-sets) that they use in SAS... also, the query they run are different for every investigation they do based on the financial institutions, parties, dates as well... so u think I think about creating a Data Warehouse and using OLAP cubes? Thanks for your assistance. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-01 : 11:10:08
|
| It depends on what they want to do.Trying to cater for everything won't help you or them.Have a look - there will be some things they do regularly and some things that they need to be responsive. Concentrate on those and leave the rest to be slow. Treat the system as something that is evolving - when they have problems put in something that will help.You could start with something that is causing major issues - you'll probably find that once a few things have been dealt with that they will have a lot more time and won't worry so much ablout the performance of the rest.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Ghanta
Yak Posting Veteran
96 Posts |
Posted - 2011-06-01 : 11:21:40
|
| You are correct Nigel. Thanks a lot for your suggestions... I will monitor and look at queries they run and export they do. |
|
|
|
|
|
|
Data Analysis/Export from huge tables!!!!