Advice on Partitioning and Processing

I'd appreciate some advice on the following:

I'm building a cube which is based on data in a SQL 2000 DB. There are around 1.2 billion rows of fact data in 26 tables. Each table uses approx 6 Gig. There are 4 Dimensions, 3 dimensions have less than a 1000 members with the 4th having 65000. For convenience I am creating 26 Cube partitions aligned to the DB tables, each partition will have around 50 million rows. Does this sound like a reasonable proposal? The reason I ask is that I am seeing very poor performance whilst calling ProcessData. I have processed various numbers of partitions in parallel and cannot achieve the same times that I can get on the existing AS2000 rig using the Parallel Processing Utility.

I have tested the data read by writing a .net application to execute the same DB queries in parallel that the partitions use (achieving 200 million rows in 20 minutes) and there are no issues related to network, disk or the DB server.

Processing seems to start well but around a minute in the Network utilisation drops down to 10 percent and CPU utilisation to 30%. It then goes back up to 100% for a short period and then drops again. This is repeated continually until processing is finished. At current speeds it will take 26 hours to complete as opposed to 10 hours on AS2000. The box is a 64-bit 4way hyperthreaded with 32 GB of RAM. AS2005 is Enterprise with SP1.

Any suggestions would be appreciated as I'm running out of ideas!

Rob.

You should check yourself against best practices you would find the project REAL implemetation http://www.microsoft.com/sql/solutions/bi/projectreal.mspx

I would say the partition size is a bit big. You should try and partition some more for and split a single partition in two. But that doesnt look like a major problem.

Question for you: in AS2005 you processing partitions in parallel. How many? Is the pattern the same for any number of partitions?

What OLEDB provider you are using to read data from SQL Server?

The reading rates looks to be slow. See where the bottleneck is. In your test application, the fair comparison not only when you execute SQL query, but you also retrevie all the data returned by the query.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Advice on dynamically generating RDL files

I would prefer to dynamically create my RDL files on the fly instead of
building them ahead of time. My question is this: the creation of these files
would occur on the server, but the reporting server may or may not be the
same box. Is the best way to upload the RDL file to use the web service
interface and import it, or are there other options here?
Thanks!
MichaelHi moflaherty,
You can create your RDL on the fly, the creation of the files can be
located at the client or server side, it does not matter as long you can
create the XML/RDL after that you can use the WS interface to upload them
into the server. In this example
http://www.rdlcomponents.com/ASPExamples/Default.aspx?sm=b1_a
You can see how the RDL creation process is isolated from the upload process.
Thanks
JErry
"moflaherty" wrote:
> I would prefer to dynamically create my RDL files on the fly instead of
> building them ahead of time. My question is this: the creation of these files
> would occur on the server, but the reporting server may or may not be the
> same box. Is the best way to upload the RDL file to use the web service
> interface and import it, or are there other options here?
> Thanks!
> Michael
>sql

Advice needed: DW and ETL tools

Hi!
I'm involved in building a medium sized ware house with MS SQLServer 2000=20
as the backend. I've been searching for some good tools for SQLServer to=20
help me with the demanding tasks of planning and building the database and =
(preferrably the same tool) making the mappings and data transformations=20
when populating the database, but still I've not come across any that has=20
satisfied my needs. Oracle has a great tool for this called Oracle=20
Warehouse Builder, but haven't seen anything like it for MS databases.=20
Therefore I'm asking: Does anyone know about a tool that can be used in=20
planning, deploying and maintaining a data warehouse built on a MS=20
SQLServer 2000? Doesn't matter if it has a price tag on it or not
S=F6ren Hakola
FinlandI think that anyone that uses SQL Server to deploy a Data Warehouse... uses
SQL Server 2000 capabilities only. And it's cheeper )
- Diagram: create logic models with direct link to the tables
- Tables: Info repository
- Data Transformation Services (DTS): ETL, data transformation (populate the
tables)
- Jobs: schedule DTS for automatic ETL
- Analysis Services: If you desire to work with a MOLAP tool
Other tools:
Data Stage (Unix and Windows, all databases) - Top tool, expensive
Informatica (Unix and Windows, all databases) - Top tool, expensive|||Thanks for the response. I'll check those out!
Greetings
S.sql

Advice Needed On Very Large Full Text Indexes

I'm currently building a product that will need to index and search roughly
500 million documents (perhaps more). Currently I have about 60 million
documents in the index (table size about 100GB and index size about 35GB)
Performance is (obviously) getting worse and I doubt my current architecture
will support the needed growth and still provide the required speed.
Documents are added 24x7 at the rate of 100's per minute (roughly 250,000
new ones each day). Plus, due to the search requirements, I had to clear
the noise file. I've been thinking of doing table partitioning (using
2005), creating indexed views and then using multiple full-text indexes to
query the data (however, of course, I will not be able to rank effectively
then).
So, I'm curious if any one else has this type of volume and has come up with
a solid solution. Appeciate any ideas.
I should also mention that I'm looking for short term consulting help on
this, as well as a full-time position - so while I'm grateful for free
suggestions from this board, should anyone be looking for work, please
contact me as well (company located in New Jersey).
Thanks!
Joel
Partitioning works, but you need to have well defined partitions which the
data and the queries will align itself with.
For example if you are querying by a search phrase and your
buckets/partitions are by date partitioning won't help you. If you are
partitioning by date you will get some advantages if you have time
dependencies. For example in Google, if you query on your name
http://www.google.com/search?sourcei...=Joel+Macaluso
you'll notice 70,100 hits. Then as you move to other pages you'll notice
that the number goes down to 69,900. What has happened is the first hit is
on one server which has mainly new stuff. The subsequent queries hit the
bigger archive which represents the totality. The time partition works for
them even though most of their queries are have no real time dependency, ie
web pages from 2001.
You might want to analyze your queries looking for colocants - words which
are search for as a unit - Bacon and Eggs, Sonny and Cher, George Bush.
Likewise you might choose not to index hapax legomenon, dis legomenon, tris
legomenon or even tetrakis legomenon. You also might want to look at words
over a certain length and not index them.
One other point that you aren't answering is while I know about your
indexing requirements - what are you querying requierments like? IE How may
queries per day/min/sec.
I am facing some of the same problems where I work now. I'd be interested in
following up with you on this one. I'm in NJ as well.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Joel Macaluso" <jmacaluso@.digitalgrit.com> wrote in message
news:%23OjwJkXGGHA.1088@.tk2msftngp13.phx.gbl...
> I'm currently building a product that will need to index and search
> roughly 500 million documents (perhaps more). Currently I have about 60
> million documents in the index (table size about 100GB and index size
> about 35GB) Performance is (obviously) getting worse and I doubt my
> current architecture will support the needed growth and still provide the
> required speed.
> Documents are added 24x7 at the rate of 100's per minute (roughly 250,000
> new ones each day). Plus, due to the search requirements, I had to clear
> the noise file. I've been thinking of doing table partitioning (using
> 2005), creating indexed views and then using multiple full-text indexes to
> query the data (however, of course, I will not be able to rank effectively
> then).
> So, I'm curious if any one else has this type of volume and has come up
> with a solid solution. Appeciate any ideas.
> I should also mention that I'm looking for short term consulting help on
> this, as well as a full-time position - so while I'm grateful for free
> suggestions from this board, should anyone be looking for work, please
> contact me as well (company located in New Jersey).
> Thanks!
> Joel
>

advice for storing reservation/booking date ranges...

I am building a small app that will display availability data for properties. I have a calendar that displays the dates for a whole year in month rows with each days colour representing the availability status, i.e. booked, on hold etc.

My question is about how to store these dates in the db. At the moment I have the table below:

TABLE Availability
[PropertyID] [int] NOT NULL ,
[StatusID] [tinyint] NOT NULL ,
[StartDate] [smalldatetime] NOT NULL ,
[EndDate] [smalldatetime] NOT NULL

I was planning on having four status's for any given date, unknown, available, on hold or booked.

Displaying the dates has proved pretty simple but updating availability means I would need to query the db to see if any of the dates overlapped, I would then have to add the new date range/status as well as change any date ranges that overlapped, either in the sp or in the code and this is what made me wonder if there was a better way.

Does this sound a reasonable approach? Any advice or pointers would be greatly appreciated, This is the first time I have had to store date ranges and I want to make sure I am doing it right.

Instead of an Availability table, I would think you'd want a Booked table. Assume the property is available and create records in the Booked table when it's booked or held. Don't create records if you'd have an overlap by doing so. If no record for a time frame, then it's available.

It seems the way you're doing it, you would have to create a record for every date for every property. That makes it tough to be more granular if you need to go to the hour or minute level.

|||

Yes, unfortunately I have to cater for users who are not keeping the calendar uptodate hence using both the unknown and available status.

The default will be unknown, so anything not in the table will be that status and then it is up to users to use the calendar to update the properties availability, so there are three status's, available, on-hold and booked. These three will be in the table.

I think I will proceed as is and see how I go...thanks for the input.

ADO.NET DATAVIEWS VERSUS SQL ENGINE

Hi All,

Any suggestions / views / help on below question would be welcomed.

I am building an asp.net 2.0 application with sql 2005 express as back end. My back end has 3 major tables which are:

tblArticles - saves basic info on articles posted by user (like articleid, title, short desc, rating, views, etc)

tblCategories - saves various categories and their hierarchies (id, parented, name, etc)

tblArticleCategories - saves info on which articles fall in which categories (like articleid, categoryid)

as of now, i am caching all rows from the first 2 tables, but i am in a bit of doubt for caching the third table (tblArticleCategories), although data in this table wont change very often and also this table will just have 2 columns and not many rows as well and this is a good target for caching,

but the reason I am in a bit of doubt to cache this table is, when my website visitor clicks on any category link in the category tree view, I need to use an inner join across all these 3 tables to locate and return all articles found in that particular category.

But I can do the same thing without hitting the database as I already have 2 of the required 3 tables in my cache, I can simply add the third table to my cache and then using the dataview objects rowfilter property on these 3 cached tables, I can very well get the appropriate results.

But I wonder which of the 2 methods would you prefer and suggest, I mean do you feel that just to save hits against the database, I am going to far and doing a lot of crap using the dataview (which might not be as efficient as sql engine) or you feel that the inefficiency of the dataview will still win compared to the cost of hitting the database for this

Thanks in advance, bye take care

Raj Chaudhari, Mumbai, India (MCAD.NET)

www.xtremebiz.biz

i would cache smaller objects that are the result of a more specific query.

let the db do its joins and return from the db only articles for a specific category.
cache this data.

when other categories are selected, hit the db, and cache that data too as a seperate object.

the result is that you dont need to perform rowfiltering on every page hit and you will only have articles in cache for categories that have been selected

|||If you are using SQL Express or SQL Server 2005 as the back end, it sounds like you are doing a lot of manual work that you really don't need to. Just use the SQLDatasource object, put your query in the select query text property, set the caching to true, and set the cache dependancy property to the appropriate value "tblArticles,tblCategories,tblArticleCategories" for the query that access all 3 tables. If you have performance problems after that, address them at the time. Heck, remember prior to ASP.NET 2.0 *most* applications did no caching of database queries at all and ran just fine.