Distributed databases?

Hi everybody.

I have a question about the database performance.

Suppose we have 15 tables distributed to 3 databases. So if we want to to load some related data from diffrent tables of diffrent databases, we have to make more connections to the other databases, and it takes some time to establish the connection. but if we use 1 database with 15 tables, then we wont have this problem and we wont make more connections. but using 1 database, will grow the size of database file (*.mdf) and sure this couses to take more time of file operation like record-seek-time, record-insertion-time and etc.

which of these solutions have better performance? Distributed databases or single one?

Thanks

Even if you put them all in one database, you dont have to put them all in the same mdf file, or even on the same drive, and then even as the tables grow very large, you can partition them, which further helps the optimization.

The driving factor though, for me, is logical separation.

Are these tables all part of the same application? In this case I would put them in the same database. Are some of them shared between applications? In this case, I would put the shared tables in a database, and the application specific tables in another.|||Are you talking about "Databases" on the same server, or different servers?

Databases on the same server would not require multiple connections to be made for each database, you connect to the server, not the database.

|||

The main reason for having multiple databases on different machines would be if you are trying to scale out instead of scale up on a single server. Unless a single server cannot handle the workload, you probably don't need/want to scale out yet.

The size of the data file(s) in a single database does not have nearly as much impact on performance as other factors like having the proper indexes in place for your workload.

|||

Hi, thanks for the replyes.

I can seperate my data in 3 sets. first, the data which are stattic, like the list of countries, second, the memberships data of the members, like member's general information, membership information and etc. and third, the data of the members' posts.

the 1st set, is static, so never (or seldom) grows. but the 2nd and 3rd are dynamic and would grow alot.

data of 2nd and 3rd sets can be related, but also can not. I mean, as you know, the posts of the members are related to the members, but they are POSTS, so it may be logical to use a seperate database for them and a seperate database for members.

but from another view, they are related, they are the posts of the members! so it would be logical again, to put both the sets in one database.

and by this time, i'm going to use just one server, not many servers (may be in future).

and I dont know, putting all the data in one database, would reduce the performance of my database (and so my application) or not.

by the way, the data of the members, will be used by seperate applications, but the posts are for a single application. infact all of my applications will use the membership information, but the each of these, have their own application-specific data.

Distinct Sum - How To?

Hello everybody,
My data looks like this:
Program StudentID CreditHours
MBA 1 4
MBA 2 3
MSC 1 4
MSC 3 8
(student id=1 is in both programs)
Then, based on the above data, I need to do a summary report by Program
and grand total. Grand total should NOT count the same student twice.
Program CreditHours
MBA 7
MSC 12
--
Total 15
Again, for the grand total I do NOT want to count the same student
twice.
It looks like what I need is DistinctSum() function, distinguishing by
parameter other than the one being summarized (StunentId in this case).
How do I accomplish this with MS Reporting Services?
I remember I was able to do that with Crystal Reports.
Of course this contrived example is only meant to present the problem.
Please advice,
Tomasz
(I apologize if this post shows twice)Hi Tom,
Welcome to the MSDN newsgroup.
Regarding on the Distinct Sum problem, it is a bit difficult to directly
calculate the distinct results from your given dataset. For the first part
in your report
MBA 7
MSC 12
this can be done through a group on the "program" column. For the grand
total value, do you think if convenient that we put another Dataset on the
report which already queried the distinct data(according to the StudentID
column) from datasource? Thus, we can make our Grand total textbox bind to
that dataset. This is done at datasource/database side.
So far there is not direct support on calculate distinct sum in SSRS,
however, there are some other members use the custom code to calculate such
value, you can have a look at the following article which may give you some
idea on this:
http://72.14.203.104/search?q=cache:sSV0CC-nWpwJ:forums.microsoft.com/MSDN/S
howPost.aspx%3FPostID%3D240606%26SiteID%3D1+SSRS+2005+grouping+distinct+data
+&hl=en&ct=clnk&cd=1
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Well, I tried something similar already.
The problem I had was that SSRS processes the outer groups first, so the
however stored "running sum", even checked against some dictionary of
already added values first, is simply not available when the outer group
total, like grand total, is being rendered.
For the grand total or report total I can add additional textbox, outside
the table, but this actually does not solve my problem since my reports are
grouped on 4 levels and I need distinct sum on levels 3 and 4.
Another question: is it guaranteed that some textbox placed under a table
renders in server memory always after the table renders? I would rather not
relay on such assumptions.
Another method I tried was adding some hidden, calculated field with
function like: =Fields!CreditHours.Value /
CountDistinct(Fields!Program.Value, "Session") and than adding those
adjusted values on appropriate levels but then, again, due to the order SSRS
processes groups (outer first) this did not work.
Is there any way I can extend SSRS functionality and add DistinctSum(value,
id) function? I would rather spent some time doing that than resource to SQL
Server based solutions, which, in may case might not even be an option since
I need distinct sums on mid grouping levels.
Please advice.
Tomasz
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:G$xgn1tUGHA.5016@.TK2MSFTNGXA01.phx.gbl...
> Hi Tom,
> Welcome to the MSDN newsgroup.
> Regarding on the Distinct Sum problem, it is a bit difficult to directly
> calculate the distinct results from your given dataset. For the first part
> in your report
> MBA 7
> MSC 12
> this can be done through a group on the "program" column. For the grand
> total value, do you think if convenient that we put another Dataset on the
> report which already queried the distinct data(according to the StudentID
> column) from datasource? Thus, we can make our Grand total textbox bind to
> that dataset. This is done at datasource/database side.
> So far there is not direct support on calculate distinct sum in SSRS,
> however, there are some other members use the custom code to calculate
> such
> value, you can have a look at the following article which may give you
> some
> idea on this:
> http://72.14.203.104/search?q=cache:sSV0CC-nWpwJ:forums.microsoft.com/MSDN/S
> howPost.aspx%3FPostID%3D240606%26SiteID%3D1+SSRS+2005+grouping+distinct+data
> +&hl=en&ct=clnk&cd=1
> Regards,
> Steven Cheng
> Microsoft Online Community Support
>
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Thanks for your response Tomasz,
I'm afraid this would be a bit limited here. As far as I know, SSRS is
planning to provider user ability to create own report item(like Image,
List ,Line...), however, so far this is not available in the current
version, no public document on this. If you do want to do some
customization or extension on this, I think you may consider contacting the
MS consulting service since they usually focus on such decent requests.
Anyway, I'm sorry for the inconvenience this brings you.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Is there any way I can access underlying DataSet? I believe SSRS internally
uses DataSet object, does not it?
That would help in solving my problem because I could access it
programmatically and do all the custom summarization myself.
Tomasz
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:spQjOH%23UGHA.1364@.TK2MSFTNGXA01.phx.gbl...
> Thanks for your response Tomasz,
> I'm afraid this would be a bit limited here. As far as I know, SSRS is
> planning to provider user ability to create own report item(like Image,
> List ,Line...), however, so far this is not available in the current
> version, no public document on this. If you do want to do some
> customization or extension on this, I think you may consider contacting
> the
> MS consulting service since they usually focus on such decent requests.
> Anyway, I'm sorry for the inconvenience this brings you.
> Regards,
> Steven Cheng
> Microsoft Online Community Support
>
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Thanks for your followup Tomasz,
I'm afraid so far the reporting service interface dosn't support this since
underlying dataset is not exposed. Actually this limitation also somewhat
related to the particular table used in your case, the certain table which
contains the two columns does not quite conform to the NF rules which make
the common RS items unable to do the work at report level. And yes, I
totally agree with you that this can be done if there has interface which
expose the underlying dataset object, however, so far we still need to wait
for the later update or version.
Thanks for your understanding.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Distinct Sum - How To?

Hello everybody,
My data looks like this:
Program StudentID CreditHours
MBA 1 4
MBA 2 3
MSC 1 4
MSC 3 8
(student id=1 is in both programs)
Then, based on the above data, I need to do a summary report by program
and grand total. Totals should NOT count the same student twice.
Program CreditHours
MBA 7
MSC 12
--
Total 15
Again, for the grand total I do NOT want to count the same student
twice.
It looks like what I need is distinct sum function, distinguishing
parameter other than the one being summarized (StunentId in this case).
How do I accomplish this with MS Reporting Services?
I remember I was able to accomplish this with Crystal Reports.
Of course this contrived example is only meant to present the problem.
Please advice,
TomaszCountDistinct()
thomas wrote:
> Hello everybody,
> My data looks like this:
> Program StudentID CreditHours
> MBA 1 4
> MBA 2 3
> MSC 1 4
> MSC 3 8
> (student id=1 is in both programs)
> Then, based on the above data, I need to do a summary report by program
> and grand total. Totals should NOT count the same student twice.
> Program CreditHours
> MBA 7
> MSC 12
> --
> Total 15
> Again, for the grand total I do NOT want to count the same student
> twice.
> It looks like what I need is distinct sum function, distinguishing
> parameter other than the one being summarized (StunentId in this case).
> How do I accomplish this with MS Reporting Services?
> I remember I was able to accomplish this with Crystal Reports.
> Of course this contrived example is only meant to present the problem.
> Please advice,
> Tomasz|||kbrennan wrote:
> CountDistinct()
>
Count is not Sum.
Tomasz

Distinct Query

Hi everybody,

I would like to select all "id" in a table ONLY where "raff" is distinct.

I think it shoult be such a thing:

SELECT id FROM mytable WHERE (SELECT DISTINCT raff) or

SELECT id FROM (SELECT DISTINCT raff FROM mytable)

So what is the correct syntax

Thnak U

BaRRonThis sounds familiar

select id = max(id)
from tbl
group by raff
having count(*) = 1

Displaying CustomerID in the report header on mutliple pages

Hi everybody,
I would like to have a field (CustomerID) that is displayed in the header of
the page. I can reference a field of the page with
ReportItems!CustomerID.Value. This works fine as long the customer
information is not spanned across two different pages.
If this is the case the field in the header is empty on the second page. Is
there another solution to this? Why can't I just reference a field item?
Thanks,
tomIf you have any control in the Header of a report , then
by default the controls are repeated in all pages.
There is a Report Header property where you can set print
on first page and print on last page to true or false.
If you have "Print on Last Page " set to false and if 2nd
page is your last page then you will not see the header on
the second page.
Just see if this is the case with your report.
>--Original Message--
>Hi everybody,
>I would like to have a field (CustomerID) that is
displayed in the header of
>the page. I can reference a field of the page with
>ReportItems!CustomerID.Value. This works fine as long the
customer
>information is not spanned across two different pages.
>If this is the case the field in the header is empty on
the second page. Is
>there another solution to this? Why can't I just
reference a field item?
>Thanks,
>tom
>
>.
>