Managing sql Log File: tables

Showing posts with label tables. Show all posts

Wednesday, March 28, 2012

Mapping fields from two tables

Hi all,

I'm not sure how easy of a task this is, or how to go about it, so I thought I'd ask here. I am doing a major overhaul to my site and am re-programming the backend which involves re-formatting my tables, table names etc.

What I want to do is set up a new table in the format I want for the new site, but still be able to have the live data that is being submitted to the site now, be entered into the new table so that I don't have to shut down the site for days to transfer and reformat data. Example

CURRENT SITE

Table 1
Field1: Name
Field2: Address

NEW SITE

Table 1
Field1: Name
Field2: myAddress
Field3: City

I want to be able to somehow map the two together so that when data is entered into the Name field on the current site, that same name is also inserted into the new site table as well. Mapping like this:

(current site)Name -> (new site)Name
(current site)Address -> (new site)myAddress

The city field would be left unaffected as that data would only be entered once the new site is up and running.

I know I could do this from a stored procedure on the exiting site or by re-writting all of my SQL to insert into both tables, but right now all of the SQL is hardcoded into the pages and I have A LOT of pages. Is there a way to map the tables together from within SQL Server?

Cheers,
JeffYou can use DTS to copy the data from one database to the next. I've done hundreds of thousands of rows in a short period of time.|||Yeah I did know of the DTS and have used it in the past but there is a problem with doing that. Here's why:

As the first version of the site was programmed years ago I made alot of mistakes in the way I did things and does not match what I want to do now. One thing in partilcuar is that I want to assign categories to each news item in my database. This requires manually tagging thousands of items, but is worth the effort. The problem is that becuase of the way I am re-structuing the data and tables I want to be able to update the live data as it comes in, but also be submitting those updated categories tags to the new database. The old tables do not have fields and are not structured to accomodate the new data I want to enter. This is why I wanted some short and easy method from within SQL Server to map particulr fields from the old tables to the new tables. I woudl then start adding the new information to the new tables.

I hope that makes sense. Perhaps such a thing does not exist.|||In the DTS designer, there is an option to map particular fields from an old table structure into a new one, even across tables. As long as you aren't breaking fields up, it's not too hard to do. I forget which options to mess around with, but definitely check it out. No sense in doing work you don't need to do! :)|||I spent quite a while playing with DTS designer and was able to map fields like I wanted BUT I was not able to figure out how to perform updates to information. At least not easily. I can append data and I can overwrite exiting data, but not UPDATE it. I started playing with Replication and that seemed to do what I wanted in terms of creating real-time mirroring of the old tables into a new database, but it does not look like there is a way to map to different field names. Is there a way for me to either combine the DTS and replication or a way to map fields when I push a subscription?

Cheers,
Jeff|||I couldn't find anything on combining DTS with replication. From what I saw and from my own knowledge of SQL Server, it sounds like what you want to do would have to be custom-built either in VBScript in the DTS Designer or through stored procs, which is what we were trying to avoid in the first place! Oh well, sorry to lead you down the wrong path. Good luck!

mapping

hi! i have two different databases (SQL 2005 and Oracle) and i need to map their tables with one another. how will i do this?

thanks

Hi,

you can create a Linked server with Oracle. and query the oracle table as local tables. or you can use OpenRowset to query oracle database

|||

ahm, I'm just new at using oracle and i'm a little bit confused.. could you please explain a little more?

thanks!

|||If you create a linked server for you Oracle Server in SQL Server (See the Books online for SQL Server for detailed information) you can access the tables of the Oracle instance using the four part notation of SQL Server:

SELECT * FROM OracleLinkedServerName..Schema.ObjectName

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

However, I found that in order to get it to work, I needed to use brackets around each element such as the following: SELECT * FROM [LINKEDSERVERNAME]..[DATABASENAME].[TABLENAME]. I wouldn't have found it out had I not used the "Script table as..." command by right-clicking the table in the Object Explorer! This wasn't mentioned in BOL.

Brian J. Matuschak

|||

Actually there is no need to use the brackets unless you have special characters in the names.

Monday, March 26, 2012

Many-to-Many Variation

In establishing a many-to-many relationship between two tables, a 3rd
"junction" table is created that contains at least two foreign keys.
No problem.
But - what is a recommended way to proceed to create a many-to-many
relationship between (1) one table, and (2) two other tables.
For example, say you are designing a database for a school and you are
establishing a many-to-many relationship between [Classes] and [Students]
and [Teachers].
There would obviously be a Classes table. A "junction table" could then have
foreign keys to Classes and Students. That would get us a many-to-many
relationship between Classes and Students. But we also need to show that
relationship between Classes and Teachers.
What do we do with the "junction table"? Do we add a 3rd foreign key to the
existing table? Do we create another junction table altogether?
I'd appreciate some guidance on this.
Thanks!"Smithers" <A@.B.COM> wrote in message
news:OSoQrlVlGHA.3740@.TK2MSFTNGP02.phx.gbl...
> In establishing a many-to-many relationship between two tables, a 3rd
> "junction" table is created that contains at least two foreign keys.
> No problem.
> But - what is a recommended way to proceed to create a many-to-many
> relationship between (1) one table, and (2) two other tables.
> For example, say you are designing a database for a school and you are
> establishing a many-to-many relationship between [Classes] and [Students]
> and [Teachers].
> There would obviously be a Classes table. A "junction table" could then
> have foreign keys to Classes and Students. That would get us a
> many-to-many relationship between Classes and Students. But we also need
> to show that relationship between Classes and Teachers.
> What do we do with the "junction table"? Do we add a 3rd foreign key to
> the existing table? Do we create another junction table altogether?
>
Assuming that a class has many teachers, you would use seperate junction
table. Each junction table models a different relation. One models the
"student is a member of class" relation, and the other models "class is
taught by teacher" relation.
If each class has a single teacher, then add a foreign key on the Classes
table referencing Teachers.
David|||Google up "Fifth Normal Form (5NF)"|||And then if you want to model the relationship between Teachers and Students
...
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OUsBopVlGHA.2128@.TK2MSFTNGP04.phx.gbl...
> "Smithers" <A@.B.COM> wrote in message
> news:OSoQrlVlGHA.3740@.TK2MSFTNGP02.phx.gbl...
> Assuming that a class has many teachers, you would use seperate junction
> table. Each junction table models a different relation. One models the
> "student is a member of class" relation, and the other models "class is
> taught by teacher" relation.
> If each class has a single teacher, then add a foreign key on the Classes
> table referencing Teachers.
> David|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:Ol7roDWlGHA.408@.TK2MSFTNGP03.phx.gbl...
> And then if you want to model the relationship between Teachers and
> Students ...
>
That information can be derived from the existing relations:
Select Class.Teacher, StudentClass.Student
from Class
join StudentClass
on Class.ID = StudentClass.ID
David|||I disagree.
But my disagreement is positioned on having had to work out this kind of
issue before. (I may be overlooking some simple solution you have worked
out. )
Student A has a Class B
StudentClass:
StudentClass_ID = 1, Student=A, Class=B
Teacher ZZ teaches Class B, Teacher YY also teaches Class B.
Class:
Class_ID=1,Class=B, Teacher=YY
Class_ID=2,Class=B, Teacher=ZZ
Student A's Class B is taught by Teacher ZZ
Your query will tell me that Student A has a relationship with Teacher YY
which is NOT true.
More has to be done to develop a relationship between Teachers and Students.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:elGU93WlGHA.2392@.TK2MSFTNGP04.phx.gbl...
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:Ol7roDWlGHA.408@.TK2MSFTNGP03.phx.gbl...
>
> That information can be derived from the existing relations:
> Select Class.Teacher, StudentClass.Student
> from Class
> join StudentClass
> on Class.ID = StudentClass.ID
> David
>
>|||Arnie Rowland wrote:
> I disagree.
> More has to be done to develop a relationship between Teachers and Student
s.
> --
Correct. This problem is solved by 5th Normal Form.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I don't know if I am good enough to interefere in the clash of the titans.
But Can't we have something like this?
Take it simply.. A student is associated to a particular class by a
particular teacher.
And there is no explicit teacher student relation.
The same example you had quoted.
Student A has a Class B
StudentClass:
StudentClass_ID = 1, Student=A, Class=B
Teacher ZZ teaches Class B, Teacher YY also teaches Class B.
Class:
Class_ID=1,Class=B, Teacher=YY
Class_ID=2,Class=B, Teacher=ZZ
If Student A's Class B is taught by Teacher ZZ
The the student class table should look like this
StudentClass:
StudentClass_ID = 1, Student=A, Class_ID=2
If a student is attending a session, it needs a class and a teacher.
and if such a class is happening then we should be having an entry in the
class table. Am I missing something?
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi David (and all in the discussion),
To tell the truth, I haven't really bothered about 5NF till now.Assumed
anything above 3rd was not practical :)
But the post yesterday and the replies intrigues me. I had checked up on a
few websites and the examples that is given there doesn't convince me. Can
you direct me to a website where the explaination is given. And If I am not
able to understand, then I can discuss with reference to that.
Thanks a lot.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"David Portas" wrote:

> Arnie Rowland wrote:
> Correct. This problem is solved by 5th Normal Form.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

Many-to-many SQL Query (Failed to enable constraints)

Hi,

I have two tables (Accounts and Contacts) that have a many-to-many relationship, maintained by the AccountContactLinks table.

I would like to populate a Contacts DropDownList with all of the Contacts associated with the Account Selected in Accounts DropDownList.

Here is the (SP) SQL Query I'm trying to make work:

SELECT Contact.ContactID,
Contact.ContactLastName,
Contact.ContactFirstName,
Contact.ContactLastName+', '+ Contact.ContactFirstNameAS ContactName
FROM ContactINNERJOIN AccountContactLinksON Contact.ContactID= AccountContactLinks.ContactID
WHERE(AccountContactLinks.AccountID= @.AccountID)
ORDERBY Contact.ContactLastName
END

I keep getting the following error:

Failed to enable constraints. One or more rows contains values violating non-null, unique or foreign key constraints.

I haven't implemented any non-null, unique or foreign key constraints between any of these tables, so suspect that I've got the SQL Query wrong.

Thanks very much.

Regards

Gary

You must be trying to read that into a dataset that is set up wrong. If you are just trying to fill a dropdown, then put a dropdown control on the page, put a sqldatasource control on the page, put the SQL you have there (Or the name of the SP) as the query string, and set up the @.AccountID parameter.|||

Thanks, Motley - what you suggest is exactly what I'm doing. I've tried it both a SQL query (which runs fine in the Query Editor) and as an SP.

It's just when I test the Query in the SqlDataSource Wizard and when I try to run the page that it doesn't work.

Do you have any other ideas?

Thanks very much.

Regards

Gary

|||

It looks good to me... Well, except that you have an END statement, but I'm assuming that you cut and pasted from your SP which had a BEGIN before the select statement.

Are you sure there are no foreign key constraints? Sure looks like the AccountContactsLinks and Contacts table would be a good candidate for one.

|||

Thanks, Motley - I've found out what's wrong (by creating a new set of tables to ensure a clean start and making the same mistake, inadvertently).

The mistake that I made (twice) was to create identical records, which in a table without a primary key, introduces duplicates (i.e. creates non-unique records). I thought that I would have had to specify that records had to be unique, but now realise that, if the linking table doesn't have a key field, there is no way of identifying one record from another if the two fields are the same.

I'm concerned that SQL Server allowed this to happen and unless you or someone else has ways of preventing it from happening again, I'm going to re-introduce a Primary Key field (which someone else suggested I remove) as a safeguard.

So, I'll have to rebuild my linking table. Fortunately I've been playing around with a test database, so it won't take too long. I'll then conclude this thread with a confirmation, for the sake of people with the same problem in future.

Regards

Gary

|||I'm glad you found it. I always recommend that all tables have a primary key -- even if it's one that you create yourself via a uniqueidentifier or an identity field. In some rare cases, like in your link table, where there really is no "new" information, but just linking 2 different objects IDs to show a relationship, I'll put both fields into the primary key.

many-to-many selection

Hi all, I am stuck on the case of many-to-many selection.

Now, I have 3 tables

Table: candidate_skills
candidate_id (PKey)
skill_id (PKey)

Table: skills
skill_id (PKey)

Table: job_skill_required
job_id(PKey)
skill_id(PKey)

Now, I want to select all cadidates who has the same skills of job_skill_required table.

I really don't know how to select it...
Please help, thank you.what's the output supposed to look like? see the sticky at the top of this page.|||you're up early, sean :)
select candidate_id
from candidate_skills
where skill_id
in ( select skill_id
from job_skill_required
where job_id = 937 )
group
by candidate_id
having count(*)
= ( select count(*)
from job_skill_required
where job_id = 937 )|||At the office by 7:15 everyday because little of substance gets done between 10 and 5.

Playing around with this Embracadero Change Manager thingy because one of my clients db's is woefully out of sync. Not sure if it is going to do what I need it to do yet.|||create table #candidate_skills
(
candidate_id int,
skill_id int
)
go
create table #job_skill_required
(
job_id int,
skill_id int
)
go
insert into #candidate_skills select 1,1
union
select 1,2
union
select 1,4
union
select 2,1
union
select 2,2
union
select 2,3
union
select 3,1
union
select 3,2
union
select 3,3
union
select 3,6
union
select 4,7
union
select 4,1
union
select 4,2

insert into #job_skill_required select 1,1
union
select 1,2
union
select 1,3
union
select 2,1
union
select 2,2
union
select 2,4
union
select 3,7
union
select 4,8

--select * from #job_skill_required j
--select c.* from #candidate_skills c,

select c.candidate_id,j.job_id from #candidate_skills c,#job_skill_required j
where j.skill_id=c.skill_id
group by c.candidate_id,j.job_id
having count(*)=(select count(distinct(skill_id)) from #job_skill_required j1 where j1.job_id=j.job_id)
order by c.candidate_id|||Thanks for your fast-reply...

many-to-many search

Hi,
I've currently got three tables, with the second being a link table to
establish a many-to-many relationship, and I'm trying to do a full-text
search. I want it to bring up instances in the first table (only one row of
the left table per search) where the text is found, and increase the rank of
the search results, where the search string occurs in the related table.
I'm currently doing a subquery that adds the sum of the rank of the linked
tables to create a total rank (example below), but I'm concerned that since
the two full-text queries aren't linked, the two ranks aren't relevant to
each other.
Has anyone else found a good/better way to do this?
Regards,
Dunc
--/ snip /--
SELECT * FROM (
SELECT VenueDetailID, VenueName, Addr1, Addr3, Addr4,
ISNULL(B.RANK, 0) + ISNULL(
(SELECT SUM(Rank)
FROM VenueExtras Y
INNER JOIN LinkExtrasVenue
ON Y.VenueExtrasID = LinkExtrasVenue.VenueExtrasID
AND LinkExtrasVenue.VenueID = A.VenueDetailsID
INNER JOIN CONTAINSTABLE(VenueExtras, ExtraName, 'ISABOUT ("cocktails"
WEIGHT(0.1))') AS Z
ON Y.VenueExtrasID = Z.[Key]), 0)
AS Rank
FROM VenueDetail AS A
LEFT JOIN CONTAINSTABLE(VenueDetail , VenueName, 'ISABOUT ("cocktails"
WEIGHT(0.1))') AS B
ON A.VenueDetailID = B.[Key]
WHERE A.Status > 0
) SubQuery
ORDER BY Rank DESC, VenueName
can you post the schema of all related tables?
Is it possible to consolidate VenueExtras and VenueDetails into a single
table?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Duncan Welch" <dunc@.ntpcl.f9.co.uk> wrote in message
news:esp%234lheEHA.3928@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I've currently got three tables, with the second being a link table to
> establish a many-to-many relationship, and I'm trying to do a full-text
> search. I want it to bring up instances in the first table (only one row
of
> the left table per search) where the text is found, and increase the rank
of
> the search results, where the search string occurs in the related table.
> I'm currently doing a subquery that adds the sum of the rank of the linked
> tables to create a total rank (example below), but I'm concerned that
since
> the two full-text queries aren't linked, the two ranks aren't relevant to
> each other.
> Has anyone else found a good/better way to do this?
> Regards,
> Dunc
> --/ snip /--
> SELECT * FROM (
> SELECT VenueDetailID, VenueName, Addr1, Addr3, Addr4,
> ISNULL(B.RANK, 0) + ISNULL(
> (SELECT SUM(Rank)
> FROM VenueExtras Y
> INNER JOIN LinkExtrasVenue
> ON Y.VenueExtrasID = LinkExtrasVenue.VenueExtrasID
> AND LinkExtrasVenue.VenueID = A.VenueDetailsID
> INNER JOIN CONTAINSTABLE(VenueExtras, ExtraName, 'ISABOUT ("cocktails"
> WEIGHT(0.1))') AS Z
> ON Y.VenueExtrasID = Z.[Key]), 0)
> AS Rank
> FROM VenueDetail AS A
> LEFT JOIN CONTAINSTABLE(VenueDetail , VenueName, 'ISABOUT ("cocktails"
> WEIGHT(0.1))') AS B
> ON A.VenueDetailID = B.[Key]
> WHERE A.Status > 0
> ) SubQuery
> ORDER BY Rank DESC, VenueName
>
|||In the name of keeping things a little secure, I've changed the names to
protect the innocent (table and field names, that is) on the example. I've
actually normalised them out of being one table (it used to contain a bitmap
field that related to each extra (which used to be stored in a global
array)) so I can search on them with a ranking; also there may eventually be
as many as 100 different extras.
In the example, I've simplified the scheme to the following:
Table: VenueDetails
VenueDetailsID
VenueName
Addr1
Addr2
Addr3
Addr4
Description
Table: LinkExtrasVenue
VenueID
VenueExtrasID
Table: VenueExtras
VenueExtrasID
ExtraName
A venue listing may have a name of "Bob's Cocktail Palace", which may have
extras of "Cocktails" and "Cocktails recommended". This needs to be ranked
above "Bill's Bar" which has an extra of "Cocktails", which in turn needs to
be ranked higher than "Brian's bar" which is situated on "15 Cocktail St"
Confused yet? I am!
At the risk of looking like I'm having a free plug, so you can see how it
works - check out:
http://www.fluidfoundation.com/venue...sp?BarRef=1405 - you'll notice
this one has the two cocktails flags set, whereas:
http://www.fluidfoundation.com/venue...sp?BarRef=2820 doesn't, so
*should* come second in the rankings when you search on "Cocktails". But
doesn't.
Dunc
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OOec7vheEHA.2812@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> can you post the schema of all related tables?
> Is it possible to consolidate VenueExtras and VenueDetails into a single
> table?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Duncan Welch" <dunc@.ntpcl.f9.co.uk> wrote in message
> news:esp%234lheEHA.3928@.TK2MSFTNGP11.phx.gbl...
row[vbcol=seagreen]
> of
rank[vbcol=seagreen]
> of
linked[vbcol=seagreen]
> since
to
>

Friday, March 23, 2012

Many-To-Many

I have tried this post in the PostgreSQL, but it is probably more specific to this forum. Sorry for any duplicates.

I have two tables one is a tasks table and the other an employees table. A third table I have is called participants. This third table holds the IDs of tasks and the IDs of employees assigned to complete those tasks. My problem is figuring out how to select employee IDs and names from the employees table that are not associated with a specific taskid in the participants table.
My first attampt was:
SELECT Employees.employeeid,Employees.username FROM Employees,Participants WHERE Participants.taskid=# AND Employees.employeeid!=Participants.employeeid.

#=any valid integer id in the taskid field of the Participants table.

This attempt just gave me everyone in the Employees table.

A more graphical representation of the tables:
tasks
-taskid
-task_name
-due_date
-assignment_date

employees
-employeeid
-first_name
-last_name

participants
-participantid
-taskid
-employeeid

Thanks for any input.Select *
from tableA
where id NOT IN
(Select id from tableB)sql

Many Year Payments

I need to create a stored procedure that will show all clients that have made payments every year for a number of years (3) or (5). My tables are simple but I have no clue how to ask the SQL question, mostly because it is each and every year.

tblClients ClientID (pk)
tblOrders OrderID (pk) ClientID (fk)
tblOrderPayments PaymentID (pk) OrderID (fk)

Thanks for any help,Depends on how you would like the output. Also: In your tables there's no date. And is not possible for one payment to apply to many orders?

And before I start: You do not 'ask SQL questions', rather you 'construct SQL queries'. And lastly: Always use singular names for your tables, e.g. Client and not Clients, since all tables can hold multiple records all of your table names end up being plural and so the plural becomes redundant.

Anyway. I'll use some common sense to fill in the gaps in your question: I'll assume the payment date rather than the order date is the important one, and I'll assume therefore there's a date on the payment table. Also I'll assume for a moment that your DB design is correct although I doubt it, since it's almost certainly the case that several orders can be paid with one order. I do not know of businesses that does not allow you to pay off several orders in one payment.

So your question as stated is like follows: You want a list of all clients that's made at least one payment a year for the last X years. Is that right? Sounds like a weird question for a business to ask...

If this is indeed what they want, it's a rather interesting SQL problem. I'll give you a few solutions just for my own amusement, even though I suspect they'll be useless for you because your DB design is wrong and you probably misunderstood the needs of your management.

Here's my first attempt:


set @.EndYear = 2004select CustomerID,
 CustomerName
 from Customer C
 where CustomerID in (select distinct CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and datepart(year, P.Date) = @.EndYear)
 and CustomerID in (select distinct CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and datepart(year, P.Date) = @.EndYear - 1)
 and CustomerID in (select distinct CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and datepart(year, P.Date) = @.EndYear - 2)

It will work for the 3 year case, but sucks bigtime. If you have a small database (less than 1 million orders) then this will be the way to go since it's easy to understand and you probably won't notice and bad performance. If one of my guys wrote this query for me I'd not be impressed though.
It sucks because:
- Only works for a set number of years. You'll have to write 2 of them to cater for 3 or 5 year queries
- Uses IN with large sets, several times. Bad for performance.
It's not all bad, though. It's cool because:
- It's simple to understand for anyone out there
- Does not use cursors or temp tables

Here's a slightly better one:


set @.EndYear = 2004select CustomerID,
 CustomerName
 from Customer C
 join (select distinct CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and datepart(year, P.Date) = @.EndYear) as Y1
 on C.CustomerID = Y1.CustomerID
 join (select distinct CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and datepart(year, P.Date) = @.EndYear - 1) as Y2
 on C.CustomerID = Y2.CustomerID
 join (select distinct CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and datepart(year, P.Date) = @.EndYear - 2) as Y3
 on C.CustomerID = Y2.CustomerID

The same as the first one, but without the expensive IN statements. Still uses a lot of DISTINCTs though, and you still have to write 2 queries for the 3 and 5 year cases. Let's extrapolate this query to one that will work for any number of years from 1 to 5:


set @.EndYear = 2004
set @.NoYears = 4select CustomerID,
 CustomerName
 from Customer C
 join (select CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and datepart(year, P.Date) = @.EndYear) as Y1
 on C.CustomerID = Y1.CustomerID
 join (select CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and (datepart(year, P.Date) = @.EndYear - 1
 or @.NoYears < 2)) as Y2
 on C.CustomerID = Y2.CustomerID
 join (select CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and (datepart(year, P.Date) = @.EndYear - 2
 or @.NoYears < 3)) as Y3
 on C.CustomerID = Y3.CustomerID
 join (select CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and (datepart(year, P.Date) = @.EndYear - 3
 or @.NoYears < 4)) as Y4
 on C.CustomerID = Y4.CustomerID
 join (select CustomerID
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and (datepart(year, P.Date) = @.EndYear - 4
 or @.NoYears < 5)) as Y5
 on C.CustomerID = Y5.CustomerID

Whoa! That's a good one eh? Again, it will work, but I'll probably get blasted by the community here for even thinking about posting **** like that. The query will work for any number of years to check from 1 to 5, so you can do your 3 and 5 year queries on it just fine without the need to write 2 seperate queries.

Ok, enough of that. Now let's try another approach: Instead of doing it all in one SQL statement, we'll create a temp table with all CustomerIDs in it. Then we'll iterate through the years we want to check, and remove all customers that did not make a payment in each year. So in the end we're left with only the customers that made payments in all the years checked.


set @.EndYear = 2004
set @.NoYears = 4
set @.I = 0select CustomerID
 into #Temp
 from Customer
while (@.I < @.NoYears)
begin
 delete T
 from Order O
 join Payment P
 on O.OrderID = P.OrderID
 and datepart(year, P.Date) = @.EndYear - @.I
 right join #Temp T
 on O.CustomerID = T.CustomerID
 where P.OrderID is null
 set @.I = @.I + 1
end
select C.CustomerID,
 C.CustomerName
 from Customer C
 join #Temp T
 on C.CustomerID = T.CustomerID

This is also a nice and simple solution, and will probably give you fairly decent performance. The good thing about it is that it will work for ANY number of years, although the more years you do the longer it will take. It's also sexy because it uses a RIGHT JOIN, my favourite type of join. :)

I'm going to stop here, but there are almost certainly more elegant solutions out there. I can imagine one where you use a helper table for all the years you want to check, and then do a distinct and a having count on a simple join in a sub-query - it will work in a single query and will be able to do an arbitraty number of years. I'll challenge anyone else out there to give it a shot and I'll post it in a day or two.|||Hi i think i found a nice solution for you :).

Data: i used the Northwind database for testing, i created a payments table containing customerid, orderid and paymentdate and populated it with testdata using the orders table.


DECLARE @.YearSpan int
SET @.YearSpan = 3SELECT
 YearsOfPayment.CustomerID,
 Sum(FirstYear) As FirstYearPayments,
 Sum(SecondYear) As SecondYearPayments,
 Sum(ThirdYear) As ThirdYearPayments,
 Sum(FourthYear) As FourthYearPayments,
 Sum(FifthYear) As FifthYearPayments
FROM
 (
 Select
 Customers.CustomerID,
 (
 Case When Payments.Paymentdate Between '01/01/1996' And '12/31/1996' Then 1 Else 0 End
 ) AS FirstYear,
 (
 Case When Payments.Paymentdate Between '01/01/1997' And '12/31/1997' Then 1 Else 0 End
 ) AS SecondYear,
 (
 Case When Payments.Paymentdate Between '01/01/1998' And '12/31/1998' Then 1 Else 0 End
 ) AS ThirdYear,
 (
 Case When Payments.Paymentdate Between '01/01/1999' And '12/31/1999' Then 1 Else 0 End
 ) AS FourthYear,
 (
 Case When Payments.Paymentdate Between '01/01/2000' And '12/31/2000' Then 1 Else 0 End
 ) AS FifthYear
 From
 Customers
 Inner Join Payments On Payments.CustomerID = Customers.CustomerID
 ) AS YearsOfPayment
GROUP BY
 YearsOfPayment.CustomerID
 HAVING
 SUM(FirstYear) > 0
 AND SUM(SecondYear) > 0
 AND SUM(ThirdYear) > 0
 AND SUM(FourthYear) > (CASE WHEN @.YearSpan > 3 THEN 0 ELSE -1 END)
 AND SUM(FifthYear) > (CASE WHEN @.YearSpan > 4 THEN 0 ELSE -1 END)

Todo:
You have to replace the hardcoded dates with a @.FirstYear Parameter and modify the WHEN
clauses accordingly ;)

Good things here:
- if you need to check for more years, just modify it to return an additional CASE column
- performance won't change with the number of years checked (= the number of CASES you return since no subselects are involved

Further Tuning:
- to speed it up a even little more you can create and index on the paymentdate and put a WHERE statement in the "inner" SELECT to only check the years between @.firstdate and @.firstdate + @.yearspan

- Moon|||:: And before I start: You do not 'ask SQL questions', rather you 'construct SQL queries'.

... and before you post again, you should rethink your arrogant attidue and stop correcting someone since most people will find this very offensive.

:: And lastly: Always use singular names for your tables, e.g. Client and not Clients,
:: since all tables can hold multiple records all of your table names end up being plural
:: and so the plural becomes redundant.

... from OO point of view where you think of collections and items, a table is a collection and holding items (records) and therefore it is 100% valid to use plural.|||Moon's solution is novel, but only works for up to a certain number of years. Here's my solution that will work for an arbitrary number of years, with only one query and no temp tables or cursors:


set @.LastYear = 2004
set @.NumYears = 5 select D.CustomerId,
 C.[Name]
 from Customer C
 join (select distinct O.CustomerId,
 datepart(year, P.[Date]) as PaidYear
 from [Order] O
 join Payment P
 on O.OrderId = P.OrderId
 and datepart(year, P.[Date]) <= @.LastYear
 and datepart(year, P.[Date]) > @.LastYear - @.NumYears) as D
 on C.CustomerID = D.CustomerID
group by D.CustomerID,
 C.[Name]
 having count(*) = @.NumYears

Who's your daddy! $5 for anyone with a more elegant solution.
And if I come off as arrogant, feel free to skip my comments and just look at my code.|||Relational databases are NOT OO and therefore your plural argument does not hold.

From IDEF1X

December 21, 1993

Section: 3.1.2 Entity Syntax

...The entity name is a noun phrase that describes the set of things the entity
represents. The noun phrase is in singular form, not plural. Abbreviations and
acronyms are permitted, however, the entity name must be meaningful and consistent
throughout the model...

IE and other data modeling standards organisations agree with the above statement. Also I was taught the same thing at University in my first year. I was under the impression that this is common knowledge.|||*points at the year, cough, :P*

well as long as Microsoft uses the plural convention in their own databases: master, Northwind, Pubs, ... i imply that there is a point in using plural for his own database objects as well ;)

Also i didn't say that using singular is a complete NoNo, just that using plural is not forbidden but valid to use.

PS: University != real life ;). I also was taught a few things in university such as C++ that are invalid by now - since when i learned C++, it was still a draft, without namespaces, and whatnot and if i would need to code in C++ today it would be a nearly a full restart.|||Yeah this one is nice :)

And sorry my yesterdays comment was rude as well :S.|||Like I said: If you start naming tables with plurals, then in the end ALL your tables (or at least a very large percentage) will end up being plural, and so the 's' at the end of every table becomes just another letter you have to type in every line of SQL code and serves no purpose whatsoever. Remember that 99% of all tables will have more than one row and therefore should in your argument be plural in the naming.

And who ever said MS wrote good code, eh?

The date may be 10 years old, but please feel free to provide me with a more recent reference from an SQL standards authority that says 'Yea, we made a mistake in '93, go ahead and use plurals now'

I disagree with you. There's no good reason to use plurals. Using plurals is a nono. Except if you're MS in which case you are expected to write bad code and thus are allowed plurals in table names.

Many to one Select

I have 2 tables related as:

T1.KEY, T1.FIELD1, T1.FIELD2

T2.KEY, T2.FIELDA, T2.FIELDB
T2.KEY, T2.FIELDA, T2.FIELDB

T1.KEY = T2.KEY

I want to return a SELECT as:

T1.FIELD1, T1.FIELD2, T2.FIELDA, T2.FIELDB, T2.FIELDA, T2.FIELDA

The second table, in some cases but not all, has multiple rows for each
row in T1. I want to return a single row with all values for T2.FEILDA
and B.

--
jeffvh
----------------------
jeffvh's Profile: http://www.dbtalk.net/m47
View this thread: http://www.dbtalk.net/t293766jeffvh (jeffvh.251ikz@.no-mx.forums.yourdomain.com.au) writes:
> I have 2 tables related as:
> T1.KEY, T1.FIELD1, T1.FIELD2
> T2.KEY, T2.FIELDA, T2.FIELDB
> T2.KEY, T2.FIELDA, T2.FIELDB
> T1.KEY = T2.KEY
> I want to return a SELECT as:
> T1.FIELD1, T1.FIELD2, T2.FIELDA, T2.FIELDB, T2.FIELDA, T2.FIELDA
> The second table, in some cases but not all, has multiple rows for each
> row in T1. I want to return a single row with all values for T2.FEILDA
> and B.

So for T1.Key = 8 there are six rows in T2, there should be 14 columns,
two for T1 and seven for T2?

I'm afraid that is not easily doable.

The result of a query is alwys a table, and a table has a fixed number
of columns; it cannot be jagged.

It still possible to define a query that has maximum of columns needed,
but that number must be known in advance. You cannot write a query
which produces 16 columns on one execution, and 20 columns next time.

Furthermore, we need rules to say which row goes into which column.

So in the general case, this is very messy, and may be easier to sort
this out client-side.

However, if there are further conditions that you know, but didn't tell us,
it might be easier. The general recommendation is that you post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.
o A short narrative of the busines problem.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||this is called a "cross tab" report. very hard to do in sql.
do some research, and you can find some examples, but they all require
custom sql.

Many to Many to Many SQL Query

I have 3 data tables, A, B and C, with many to many relationship
tables between A-B and A-C.

The data in A and C changes rarely, and the A-C relationship relates
all possible combinations of A to a C

If A contains A.1 to A.3 and C contains C.1 - C.8 then A-C could
contain the records:

A.1, C.1
A.2, C.2
A.3, C.3
A.1, C.4
A.2, C.4
A.1, C.5
A.3, C.5
A.2, C.6
A.3, C.6
A.1, C.7
A.2, C.7
A.3, C.7

so that any set of records from A (including the empty set) relates to
exactly on record in C

and suppose that B contains records from B.1 to B.3, and A-B contains
records
A.2, B.1
A.1, B.2
A.3, B.2

What I am having touble doing is crafting a query that will take me
from a record in B to the record in C that has the corrisponding set
of records in A-C as is in A-B for the chosen B.

ie, I want a query that will give me a result set something like

B.1, C.2
B.2, C.5
B.3, C.8

As far as I can come up with, this is not doable in a single query,
but perhaps I am missing something...

Simon WithersPlease post DDL (CREATE TABLE statements) for these tables - simplified if
possible but including keys and constraints. Also post some sample data as
INSERT statements. It will save you a lot of typing and make it easier for
others to understand your problem.

--
David Portas
----
Please reply only to the newsgroup
--|||Create/Insert scripts:

CREATE TABLE [Product] (
[ProductID] [int] NOT NULL ,
[Name] [char] (10) COLLATE Compatibility_52_409_30003 NULL ,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ProductID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [Project] (
[ProjectID] [int] NOT NULL ,
[Name] [char] (10) COLLATE Compatibility_52_409_30003 NULL ,
CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED
(
[ProjectID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [ProjectProduct] (
[ProjectID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
CONSTRAINT [PK_ProjectProduct] PRIMARY KEY CLUSTERED
(
[ProjectID],
[ProductID]
) ON [PRIMARY] ,
CONSTRAINT [FK_ProjectProduct_Product] FOREIGN KEY
(
[ProductID]
) REFERENCES [Product] (
[ProductID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_ProjectProduct_Project] FOREIGN KEY
(
[ProjectID]
) REFERENCES [Project] (
[ProjectID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO

CREATE TABLE [SubledgerID] (
[SubledgerID] [int] NOT NULL ,
[Name] [char] (10) COLLATE Compatibility_52_409_30003 NULL ,
CONSTRAINT [PK_SubledgerID] PRIMARY KEY CLUSTERED
(
[SubledgerID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [SubledgerProduct] (
[SubledgerID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
CONSTRAINT [PK_SubledgerProduct] PRIMARY KEY CLUSTERED
(
[SubledgerID],
[ProductID]
) ON [PRIMARY] ,
CONSTRAINT [FK_SubledgerProduct_Product] FOREIGN KEY
(
[ProductID]
) REFERENCES [Product] (
[ProductID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_SubledgerProduct_SubledgerID] FOREIGN KEY
(
[SubledgerID]
) REFERENCES [SubledgerID] (
[SubledgerID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO

INSERT INTO [Product] ([ProductID], [Name]) VALUES (1, 'Product A')
INSERT INTO [Product] ([ProductID], [Name]) VALUES (2, 'Product B')
INSERT INTO [Product] ([ProductID], [Name]) VALUES (3, 'Product C')

INSERT INTO [Project] ([ProjectID], [Name]) VALUES (1, 'Project 1')
INSERT INTO [Project] ([ProjectID], [Name]) VALUES (2, 'Project 2')
INSERT INTO [Project] ([ProjectID], [Name]) VALUES (3, 'Project 3')

INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (1, 'Subl a')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (2, 'Subl b')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (3, 'Subl c')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (4, 'Subl d')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (5, 'Subl e')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (6, 'Subl f')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (7, 'Subl g')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (8, 'Subl h')

INSERT INTO [ProjectProduct] ([ProjectID], [ProductID]) VALUES (1, 3)
INSERT INTO [ProjectProduct] ([ProjectID], [ProductID]) VALUES (2, 1)
INSERT INTO [ProjectProduct] ([ProjectID], [ProductID]) VALUES (2, 3)

INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (1,
1)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (2,
2)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (3,
3)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (4,
1)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (4,
2)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (5,
2)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (5,
3)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (6,
1)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (6,
3)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (7,
1)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (7,
2)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (7,
3)

Desired Output:

SELECT Project.Name, Project.ProjectID, SubledgerID.Name,
SubledgerID.SubledgerID FROM ???

Producing:

Project 1, 1, Subl c, 3
Project 2, 2, Subl f, 6
Project 3, 3, Subl h, 8

Simon Withers

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||try this script

SELECT Project.Name, Project.ProjectID, SubledgerID.Name,
SubledgerID.SubledgerID FROM Project , SubledgerID , ProjectProduct,
SubledgerProduct WHERE SubledgerProduct.ProductID IN
( SELECT ProjectProduct.ProductID FROM ProjectProduct WHERE
ProjectProduct.ProjectID = Project.ProjectID )
AND SubledgerID.SubledgerID=SubledgerProduct.Subledger ID GROUP BY
Project.Name, Project.ProjectID, SubledgerID.Name,
SubledgerID.SubledgerID

it will produce

Project 1 1 Subl c 3
Project 1 1 Subl e 5
Project 1 1 Subl f 6
Project 1 1 Subl g 7
Project 2 2 Subl a 1
Project 2 2 Subl c 3
Project 2 2 Subl d 4
Project 2 2 Subl e 5
Project 2 2 Subl f 6
Project 2 2 Subl g 7

is it ok?

--
..bucho
ICQ#: 38153136
Distributed.net RC5-72
ID: support@.1580.ru|||I'm not sure I've understood your requirement correctly, but does this help:

SELECT DISTINCT PR.name, PR.projectid, SL.name, SL.subledgerid
FROM Project AS PR
JOIN ProjectProduct AS PP
ON PP.projectid = PR.projectid
JOIN SubLedgerProduct AS SP
ON SP.productid = PP.productid
JOIN SubLedgerID AS SL
ON SL.subledgerid = SP.subledgerid

--
David Portas
----
Please reply only to the newsgroup
--|||[posted and mailed, please reply in news]

What a devilish problem! Took me quite some time understand what you
are looking for!

What I am offering is only a partial solution, and may even prove to be
a non-solution. It looks like this:

SELECT a.SubledgerID, b.ProjectID
FROM (SELECT s.SubledgerID, sumid = SUM(coalesce(sp.ProductID, 0)),
cnt = COUNT(sp.ProductID)
FROM SubledgerID s
LEFT JOIN SubledgerProduct sp ON s.SubledgerID = sp.SubledgerID
GROUP BY s.SubledgerID) AS a
JOIN (SELECT p.ProjectID, sumid = SUM(coalesce(pp.ProductID, 0)),
cnt = COUNT(pp.ProductID)
FROM Project p
LEFT JOIN ProjectProduct pp ON pp.ProjectID = p.ProjectID
GROUP BY p.ProjectID) AS b
ON a.sumid = b.sumid
AND a.cnt = b.cnt

It works that you get the same signature on ProductID no matter you
come from Subledger or you come from Project.

Now, the trouble here is finding a signature which does not give any false
positive. I know that there a similar problem, and I offered a suggestion
that Steve Kass proved not be safe. Unfortunately, I don't remember if
we ever found something that was waterproof. This time I didn't even
try, but I kind of hope that Steve who is a mathematician jumps in.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Simon,

This is my solution. I used your first example (because of its shorter
names!)
It's funny that we don't need table A at all. All we need is its
relations to B and C.

select
b,
c = (select C.c
from C left outer join AC on (C.c = AC.c and
a in (select a
from AB
where b = B.b)
)
group by C.c
having count(a) = (select count(*) from AB where b = B.b)
and count(a) = (select count(*) from AC where AC.c = C.c)
)
from B

Good Luck,
Shervin

simon@.klickit.com (Simon Withers) wrote in message news:<78088784.0309220828.62c349e4@.posting.google.com>...
> I have 3 data tables, A, B and C, with many to many relationship
> tables between A-B and A-C.
> The data in A and C changes rarely, and the A-C relationship relates
> all possible combinations of A to a C
> If A contains A.1 to A.3 and C contains C.1 - C.8 then A-C could
> contain the records:
> A.1, C.1
> A.2, C.2
> A.3, C.3
> A.1, C.4
> A.2, C.4
> A.1, C.5
> A.3, C.5
> A.2, C.6
> A.3, C.6
> A.1, C.7
> A.2, C.7
> A.3, C.7
> so that any set of records from A (including the empty set) relates to
> exactly on record in C
> and suppose that B contains records from B.1 to B.3, and A-B contains
> records
> A.2, B.1
> A.1, B.2
> A.3, B.2
> What I am having touble doing is crafting a query that will take me
> from a record in B to the record in C that has the corrisponding set
> of records in A-C as is in A-B for the chosen B.
> ie, I want a query that will give me a result set something like
> B.1, C.2
> B.2, C.5
> B.3, C.8
> As far as I can come up with, this is not doable in a single query,
> but perhaps I am missing something...
> Simon Withers|||Shervin Shapourian (ShShapourian@.hotmail.com) writes:
> This is my solution. I used your first example (because of its shorter
> names!)
> It's funny that we don't need table A at all. All we need is its
> relations to B and C.

Very nice solution! It spent some time to convince myself that it
addresses the general problem and not just produces the desired output
by chance, so to speak.

I did find a need for a minor improvement:

SELECT ProjectID, SubledgerID
FROM (SELECT ProjectID,
SubledgerID =
(SELECT C.SubledgerID
FROM SubledgerID C
LEFT JOIN SubledgerProduct AC
ON C.SubledgerID = AC.SubledgerID
AND AC.ProductID IN (SELECT AB.ProductID
FROM ProjectProduct AB
WHERE AB.ProjectID = B.ProjectID)
GROUP BY C.SubledgerID
HAVING COUNT(AC.ProductID) = (SELECT COUNT(*)
FROM ProjectProduct AB
WHERE AB.ProjectID = B.ProjectID)
AND COUNT(AC.ProductID) = (SELECT COUNT(*)
FROM SubledgerProduct AC
WHERE AC.SubledgerID =
C.SubledgerID)
)
FROM Project B) B
WHERE SubledgerID IS NOT NULL

As I understand the problem, it may be possible that there are projects
that does not map to subledgers and vice versa.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland,

Thanks for the improvement and converting the script.

Good luck,
Shervin

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94002C72C286Yazorman@.127.0.0.1...
> Shervin Shapourian (ShShapourian@.hotmail.com) writes:
> > This is my solution. I used your first example (because of its shorter
> > names!)
> > It's funny that we don't need table A at all. All we need is its
> > relations to B and C.
> Very nice solution! It spent some time to convince myself that it
> addresses the general problem and not just produces the desired output
> by chance, so to speak.
> I did find a need for a minor improvement:
> SELECT ProjectID, SubledgerID
> FROM (SELECT ProjectID,
> SubledgerID =
> (SELECT C.SubledgerID
> FROM SubledgerID C
> LEFT JOIN SubledgerProduct AC
> ON C.SubledgerID = AC.SubledgerID
> AND AC.ProductID IN (SELECT AB.ProductID
> FROM ProjectProduct AB
> WHERE AB.ProjectID = B.ProjectID)
> GROUP BY C.SubledgerID
> HAVING COUNT(AC.ProductID) = (SELECT COUNT(*)
> FROM ProjectProduct AB
> WHERE AB.ProjectID = B.ProjectID)
> AND COUNT(AC.ProductID) = (SELECT COUNT(*)
> FROM SubledgerProduct AC
> WHERE AC.SubledgerID =
> C.SubledgerID)
> )
> FROM Project B) B
> WHERE SubledgerID IS NOT NULL
> As I understand the problem, it may be possible that there are projects
> that does not map to subledgers and vice versa.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Wow,

Those are some well worked querys.

Thank you very much for them, they seem to address my situation.

In my specific probelem, there are no Projects that do not map to
Subledgers, but Subledger to Project does not alway match - the
Subeldger table will always map directly to all possible combinations of
Products, but not all possible combinations of Products will map to a
Project.

Simon Withers

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Many to many & other relationships

Can someone help me sort something out. Suppose we have two tables in a database.
One is named Person, and one is named Birthday. Is this a many to many relationship
or a 1 to many relationship?

Person has many birthdays. Example. Joe Schmoe has a birthday for every year of his life.

birthday(7/11/1976) has many People (Many people have a birthday on 7/11/1976.

My first question would be:

why are you creating a table just for birthdays, when you can just add a field to the Person table?

|||

This is just a hypothetical question. Probably not the best one in the world but one I believe will help me to understand. So if someone can help me out I would appreciate it.

|||

Hi,

these articles will help you get familiar with Entity Rlationship Model

http://en.wikipedia.org/wiki/Many-to-many

http://en.wikipedia.org/wiki/Entity-Relationship_Model

I hope this helps.

|||

A hypothetical question or a philosophical one? Well, those wikipedia articles are a bit dry so let me explain with a different example.

ASP.Net 2.0 membership gives you the concepts of a User (person) and a Role. A User can be in many Roles - the same login can be assigned to the Administrator role, the Sales role, the Helpdesk role etc. A Role can have many users. This is a many-to-many relationship.

To model this, we have a Users table and a Roles table. And key to the many-to-many relationship is a table "in the middle", containing the User ID and the Role ID. This allows the same User to be in the connecting table many times with different roles. And the same role to be in the connecting table with many Users.

Hope that helps!

|||

If we are going to talk about database design, then your database has to be normalized (Idea: google it).

The fast solution which is also normalized is to have a column in the "Users" table for the BirthDay.

But if you still beileve for the example you wrote in the last line:

AppDevForMe:
Person has many birthdays. Example. Joe Schmoe has a birthday for every year of his life.

birthday(7/11/1976) has many People (Many people have a birthday on 7/11/1976.

If we supposed this is the case (which is a very bad design), then the relation is Many-to-Many and here is how to deal with this relation.

You have to create a new table (namedAccessor table in database world), which will take the primary key of each of the two tables ... those primary keys will be forign key in the newly created table (the accessor table) and they will form the priamey key for it (it called a composite primary key in database world).

Now, the relation become One-to-Many for each of the old two tables with the newly created table (Accessor).

I suggest you to read about: ERD, database design, database relations, normalization (must), denormalization (good some time to increase the perfomance by making number of joining with tables less).

Hope this will help you a alot.

|||

If we are going to talk about database design, then your database has to be normalized (Idea: google it).

The fast solution which is also normalized is to have a column in the "Users" table for the BirthDay.

But if you still beileve for the example you wrote in the last line:

AppDevForMe:
Person has many birthdays. Example. Joe Schmoe has a birthday for every year of his life.

birthday(7/11/1976) has many People (Many people have a birthday on 7/11/1976.

If we supposed this is the case (which is a very bad design), then the relation is Many-to-Many and here is how to deal with this relation.

Now, the relation become One-to-Many for each of the old two tables with the newly created table (Accessor).

I suggest you to read about: ERD, database design, database relations, normalization (must), denormalization (good some time to increase the perfomance by making number of joining with tables less).

Hope this will help you a alot.

sql

Many tables-> bad?

Currently, I can chose two ways to implement a function:
The easy way would lead to many (300) tables.
The harder way would lead to considerable fewer tables.
Is there a reason to chose the harder way? Are there any penalties, if you
have many tables (with few entries) compared to few tables with many entries
?
Every input is welcome.Explain why implementing a function would require implementing new tables.
Do these tables store some type of meta data?
"the friendly display name"
<thefriendlydisplayname@.discussions.microsoft.com> wrote in message
news:99C532C3-3F4D-46F9-8F86-BF5EE6930441@.microsoft.com...
> Currently, I can chose two ways to implement a function:
> The easy way would lead to many (300) tables.
> The harder way would lead to considerable fewer tables.
> Is there a reason to chose the harder way? Are there any penalties, if you
> have many tables (with few entries) compared to few tables with many
> entries?
> Every input is welcome.|||I will attempt to answer this in haiku:
Since I cannot clearly see
What your problem is
The answer is 42
[url]http://en.wikipedia.org/wiki/ The_Answer_to_Life,_the_Universe,_and_Ev
erything[/url
]
On a serious note, there are a lot of ways to answer this question; if
you're building a data warehouse and using attribute splitting, 300
tables in a partitioned view is a perfectly acceptable practice. 300
joins may not be.
What is the function of which you speak? Can you post more
information, and perhaps we can come up with some suggestions on how to
implement what you are trying to do.
Stu|||Are you familiar with relational design principles and the concept of
normalization? That should be your guide to the logical design.
Physical design comes after logical but since you've told us nothing
about your logical design we can't help you.
If you aren't familiar with design principles then you need to take and
course or study some books. It's much too big a topic for a newsgroup.
David Portas
SQL Server MVP
--|||If you can figure out what is an entity and what is an attribute you will
answer your own question.
"the friendly display name"
<thefriendlydisplayname@.discussions.microsoft.com> wrote in message
news:99C532C3-3F4D-46F9-8F86-BF5EE6930441@.microsoft.com...
> Currently, I can chose two ways to implement a function:
> The easy way would lead to many (300) tables.
> The harder way would lead to considerable fewer tables.
> Is there a reason to chose the harder way? Are there any penalties, if you
> have many tables (with few entries) compared to few tables with many
> entries?
> Every input is welcome.

many tables vs one big table

Dear experts,

If I have a number of related groups of data (e.g., stock prices for
different stocks) is it better to put them into many tables (e.g., one
table per stock) or into one big table (e.g., a single table where one
column is the stock id other columns are the price and other data)?

The single table approach with a clustered primary key including the
stock id and other information that is different for each stock seems
cleaner to me. But I worry that having a single table could hurt
preformance when the database gets very large. The clustered primary
key should make queries on the single table fast, but I'm worried about
inserts. According to my vague understanding of a clustered primary
key, records are physically stored on disk based on the primary key. So
if I insert a record for a given stock does the database have to
physicall "move down" all the records that are below that or does it do
something smarter?

To summarize:

1. Should I use a single table or many tables?

2. If using a single table, how are inserts handled by the DB to
prevent inserts from getting slower and slower as more items are added
to the DB?

Thanks in advance for your help and advice.Use a single table, not many tables. Use a multi-column key that
includes the stock. When a new stock comes along it should be a user
transaction to add a row to the Stocks master table, not a database
administrator's job to add tables. The database design should be
stable, not in a constant state of change.

Performance of INSERTS does not change much between inserting to a
table with 10000 rows or 10000000 rows.

Roy Harvey
Beacon Falls, CT

On 2 Jan 2007 08:57:21 -0800, "Emin" <emin.shopper@.gmail.comwrote:

Quote:

Originally Posted by

>Dear experts,
>
>If I have a number of related groups of data (e.g., stock prices for
>different stocks) is it better to put them into many tables (e.g., one
>table per stock) or into one big table (e.g., a single table where one
>column is the stock id other columns are the price and other data)?
>
>The single table approach with a clustered primary key including the
>stock id and other information that is different for each stock seems
>cleaner to me. But I worry that having a single table could hurt
>preformance when the database gets very large. The clustered primary
>key should make queries on the single table fast, but I'm worried about
>inserts. According to my vague understanding of a clustered primary
>key, records are physically stored on disk based on the primary key. So
>if I insert a record for a given stock does the database have to
>physicall "move down" all the records that are below that or does it do
>something smarter?
>
>To summarize:
>
1. Should I use a single table or many tables?
>
2. If using a single table, how are inserts handled by the DB to
>prevent inserts from getting slower and slower as more items are added
>to the DB?
>
>Thanks in advance for your help and advice.

|||Emin (emin.shopper@.gmail.com) writes:

Quote:

Originally Posted by

There are two reasons why you would have more than one table:

1) Information is so different for different (groups of) stocks,
that you get different set of columns.

2) The table is really big. Then you would might make into a partioned
view or table, based on condition. But that would not be one table
per stock id.

So, in the logical design, that's one table.

Quote:

Originally Posted by

If the PK is monotonically ascending new rows are just added at the
end, all continues smoothly. If the PK is not ascending, but for instance
a random GUID, then you will get a lots of page splits, and that
takes extra power. But the answer in this case is that you should
not cluster on the GUID, but on something else. It's perfectly possible
to have the clustered index on some other column(s).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 2 Jan 2007 08:57:21 -0800, Emin wrote:

(snip)

Quote:

Originally Posted by

>if I insert a record for a given stock does the database have to
>physicall "move down" all the records that are below that or does it do
>something smarter?

Hi Emin,

Roy answered most of your questions, but was friendly enough to leave
this bit for me to comment on.

Yes, it does something smarter. Rows are *NOT* physically stored in the
order imposed by the clustered index. The clustered index imposes a
*logical* ordering on the data, which is implemented by pointers that
form a logical chain of database pages.

If a row in inserted, SQL Server first navigates the clustered index to
find the correct location (database page). Then, there are two
possibilities:

1. There's still space left on the page. The row is added and the insert
is finished.

2. There's not enough free space on the page. A page split occurs: half
of the data on the page is moved to a newly allocated page and pointers
are updated to reflect the location of the new page in the chain. After
that, the new row is inserted.

If your database has to process *LOTS* of inserts (in the order of
thousands per second or more), it makes sense to define your clustered
index such that new rows are always inserted at the logical end of the
pointer chain. In that case, page splits will never happen.
For lower amounts of inserts, the overhead of a page split is
insignificant.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Dear Roy, Erland, and Hugo,

Thank you very much for your excellent responses. It sounds like a
single logical table is the way to go since the frequency of inserts is
not very high. If necessary, where would I get more information about
low level details like page splits (e.g., how to control the page size,
how to determine when and how many page splits are occuring, etc.)?

Thanks again,
-Emin

On Jan 2, 5:53 pm, Hugo Kornelis <h...@.perFact.REMOVETHIS.info.INVALID>
wrote:

Quote:

Originally Posted by

On 2 Jan 2007 08:57:21 -0800, Emin wrote:
>
(snip)
>

Quote:

Originally Posted by

if I insert a record for a given stock does the database have to
physicall "move down" all the records that are below that or does it do
something smarter?Hi Emin,

>
Roy answered most of your questions, but was friendly enough to leave
this bit for me to comment on.
>
Yes, it does something smarter. Rows are *NOT* physically stored in the
order imposed by the clustered index. The clustered index imposes a
*logical* ordering on the data, which is implemented by pointers that
form a logical chain of database pages.
>
If a row in inserted, SQL Server first navigates the clustered index to
find the correct location (database page). Then, there are two
possibilities:
>
1. There's still space left on the page. The row is added and the insert
is finished.
>
2. There's not enough free space on the page. A page split occurs: half
of the data on the page is moved to a newly allocated page and pointers
are updated to reflect the location of the new page in the chain. After
that, the new row is inserted.
>
If your database has to process *LOTS* of inserts (in the order of
thousands per second or more), it makes sense to define your clustered
index such that new rows are always inserted at the logical end of the
pointer chain. In that case, page splits will never happen.
For lower amounts of inserts, the overhead of a page split is
insignificant.
>
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

|||One more question, is there a process similar to defragmentation that I
should run periodically to collect split pages together?

Thanks again,
-Emin

On Jan 3, 1:42 pm, "Emin" <emin.shop...@.gmail.comwrote:

Quote:

Originally Posted by

Dear Roy, Erland, and Hugo,
>
Thank you very much for your excellent responses. It sounds like a
single logical table is the way to go since the frequency of inserts is
not very high. If necessary, where would I get more information about
low level details like page splits (e.g., how to control the page size,
how to determine when and how many page splits are occuring, etc.)?
>
Thanks again,
-Emin
>
On Jan 2, 5:53 pm, Hugo Kornelis <h...@.perFact.REMOVETHIS.info.INVALID>
wrote:
>

Quote:

Originally Posted by

On 2 Jan 2007 08:57:21 -0800, Emin wrote:

Quote:

Originally Posted by

(snip)

Quote:

Originally Posted by

Quote:

Originally Posted by

>if I insert a record for a given stock does the database have to
>physicall "move down" all the records that are below that or does it do
>something smarter?Hi Emin,

Quote:

Originally Posted by

Roy answered most of your questions, but was friendly enough to leave
this bit for me to comment on.

Quote:

Originally Posted by

Quote:

Originally Posted by

If a row in inserted, SQL Server first navigates the clustered index to
find the correct location (database page). Then, there are two
possibilities:

Quote:

Originally Posted by

1. There's still space left on the page. The row is added and the insert
is finished.

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

|||Emin (emin.shopper@.gmail.com) writes:

Quote:

Originally Posted by

One more question, is there a process similar to defragmentation that I
should run periodically to collect split pages together?

Yes, DBCC DBREINDEX on SQL 2000. The preferred syntax on SQL 2005 is
ALTER INDEX WITH REBUILD or something like that.

Quote:

Originally Posted by

If necessary, where would I get more information about
>low level details like page splits (e.g., how to control the page size,
>how to determine when and how many page splits are occuring, etc.)?

Hi Emin,

For that kind of information, the only serious resource are the books
written by Kalen Delaney. Quite advanced stuff, definitely not entry
level, but that's unavoidable if you want to know about page splits.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Wednesday, March 21, 2012

Many left joins and slow performance

I am combining several columns and tables and I am wondering if there is a way to improve performance, for instance can I write an implicit join version of the following code, rather than having these explicit joins?

objCmd = new OleDbCommand ("SELECT MAmunicipalities.*, MAcitytown.*, countiesalias1.countyname, countiesalias1.countylinktitle AS clink1, countiesalias2.countyname, countiesalias2.countylinktitle AS clink2, countiesalias3.countyname, countiesalias3.countylinktitle AS clink3 FROM (MAmunicipalities LEFT OUTER JOIN MAcitytown ON MAmunicipalities.citytown = MAcitytown.citytown) LEFT OUTER JOIN MAcounties AS countiesalias1 ON MAcitytown.county1 = countiesalias1.countyname LEFT OUTER JOIN MAcounties AS countiesalias2 ON MAcitytown.county2 = countiesalias2.countyname LEFT OUTER JOIN MAcounties AS countiesalias3 ON MAcitytown.county3 = countiesalias3.countyname WHERE MAmunicipalities.municipality='plymouth'", objConn);

Sadly, I have even more joins left to add as well as other columns to add to the SELECT statement and more aliases to add as well. Any thoughts would be greatly appreciated.

So far, this appears to be a five table JOIN. That in itself 'shouldn't be a preformance issue.

Judicious usage of TABLE aliases would make the code a bit more readible (and maintainable).

And it is widely considered a 'best practice' to specifically identify columns instead of using [ SELECT * ].

For Example:

Code Snippet

SELECT
m.*,
c.*,
c1.CountyName,
c1.CountyLinkTitle AS cLink1,
c2.CountyName,
c2.CountyLinkTitle AS cLink2,
c3.CountyName,
c3.CountyLinkTitle AS cLink3
FROM MaMunicipalities m
LEFT JOIN MaCityTown c
ON m.CityTown = c.CityTown
LEFT JOIN MaCounties c1
ON c.County1 = c1.CountyName
LEFT JOIN MaCounties c2
ON c.County2 = c2.CountyName
LEFT JOIN MaCounties c3
ON c.County3 = c3.CountyName
WHERE m.Municipality = 'plymouth'

Are there additional tables to JOIN, or is it additional JOINs to the same tables (like with MaCounties above)?

|||I have additional tables to join. Right now it is taking between 10 and 15 seconds for a page to load using the code that is posted. I have indexed columns within those tables which has helped somewhat but it is still taking way too long. I suspect that having varchars being joined instead of ints is also contributing to the problem. Would a stored procedure help?|||

A stored procedure may help some -but its doubtful that it would provide the kind of improvement you really need.

As you suspect, the major issue is most likely the varchar() fields used for the JOINs.

One of the most significant things that help with speed on JOINs is the 'width' of an index. An integer field is 4 bytes. A varchar() field is as many bytes as characters (nvarchar() is double that.) The 'wider' the index, the fewer entries on an index page, therefore the more pages that have to be 'crawled' through and read to find the data.

Ideally, your CityTown and CountyName values would be integer values (CityTownID and CountyNameID).

I suggest that you explore using the Database Tuning Advisor to get assistance in fine tuning the indexes.

Refer to Books Online, Topic: 'Database Engine Tuning Advisor'

many inserts results in a massive reserved space for table

Hi,
I've got a real problem inserting approx 1,000,000 rows of data into
some SQL server 2000 tables. The data is being inserted 1 row at a
time using a T-sql cursor. When I view the table size using
sp_spaceused I get the following type of results:
reserved = 27021280 KB
data = 3376216 KB
Unused = 23642952
why is the reserved space sooooo much more than the actual amount of
data in the table?
The table cannot be fragmented as this is the first data which has
been inserted into it.
I cannot use a different insert method (like DTS) as logic needs to be
applied to the data before it is inserted.
Is there any way round this?
Shrinking the tables after the event is also not an option, as I would
run out of disk space way before all of the tables are populated.Out-of-date space usage info? Have you tried DBCC UPDATEUSAGE?
Also, what indexes do you have on the table. This along with the data distribution of the data you
are inserting will determine the level of fragmentation you get when you perform your inserts. Did
you check fragmentation level using DBCC SHOWCONTIG?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jackie" <jackiesmith_3@.hotmail.com> wrote in message
news:6cb5ab33.0410040640.4e8dc430@.posting.google.com...
> Hi,
> I've got a real problem inserting approx 1,000,000 rows of data into
> some SQL server 2000 tables. The data is being inserted 1 row at a
> time using a T-sql cursor. When I view the table size using
> sp_spaceused I get the following type of results:
> reserved = 27021280 KB
> data = 3376216 KB
> Unused = 23642952
> why is the reserved space sooooo much more than the actual amount of
> data in the table?
> The table cannot be fragmented as this is the first data which has
> been inserted into it.
> I cannot use a different insert method (like DTS) as logic needs to be
> applied to the data before it is inserted.
> Is there any way round this?
> Shrinking the tables after the event is also not an option, as I would
> run out of disk space way before all of the tables are populated.|||Thanks for your advice, I am new to SQL server so had not heard of
these procedures before...
DBCC UPDATEUSAGE - has no effect.
When I do DBCC SHOWCONTIG it shows that the table is MASSIVELY
fragmented (at least I think that's what it's saying) - results:
- Pages Scanned........................: 429774
- Extents Scanned.......................: 429755
- Extent Switches.......................: 429754
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 12.50% [53722:429755]
- Extent Scan Fragmentation ...............: 98.13%
- Avg. Bytes Free per Page................: 7712.6
- Avg. Page Density (full)................: 4.71%
There are no indexes on the table at all (I am trying this routine on
a test database before running it elsewhere and I assumed that leaving
off the indexes would increase the rate of the inserts) - are you
suggesting that if I had the indexes on the table it would reduce the
fragmentation as the data is inserted?
you say "This along with the data distribution of the data you are
inserting", but how can I control where the data is physically written
to? (and therefore control the fragmentation)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<uRyrH1iqEHA.1992@.TK2MSFTNGP09.phx.gbl>...
> Out-of-date space usage info? Have you tried DBCC UPDATEUSAGE?
> Also, what indexes do you have on the table. This along with the data distribution of the data you
> are inserting will determine the level of fragmentation you get when you perform your inserts. Did
> you check fragmentation level using DBCC SHOWCONTIG?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jackie" <jackiesmith_3@.hotmail.com> wrote in message
> news:6cb5ab33.0410040640.4e8dc430@.posting.google.com...
> > Hi,
> >
> > I've got a real problem inserting approx 1,000,000 rows of data into
> > some SQL server 2000 tables. The data is being inserted 1 row at a
> > time using a T-sql cursor. When I view the table size using
> > sp_spaceused I get the following type of results:
> >
> > reserved = 27021280 KB
> > data = 3376216 KB
> > Unused = 23642952
> >
> > why is the reserved space sooooo much more than the actual amount of
> > data in the table?
> > The table cannot be fragmented as this is the first data which has
> > been inserted into it.
> >
> > I cannot use a different insert method (like DTS) as logic needs to be
> > applied to the data before it is inserted.
> >
> > Is there any way round this?
> >
> > Shrinking the tables after the event is also not an option, as I would
> > run out of disk space way before all of the tables are populated.|||The pages seems indeed very empty, on average. To say anything more conclusive, we would need the
table layout and what indexes you have on the table. You say no indexes, but that means they you
didn't define a primary key (or unique constraint). This is not recommended! Sp_helpindex will list
the indexes you have on the table.
In general every table should have a clustered index. Which column(s) you define in the clustered
index is based on both the data distribution and your queries. It is likely that you will have less
"emptiness" in the pages with a proper clustered index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jackie" <jackiesmith_3@.hotmail.com> wrote in message
news:6cb5ab33.0410050059.7bbdb78f@.posting.google.com...
> Thanks for your advice, I am new to SQL server so had not heard of
> these procedures before...
> DBCC UPDATEUSAGE - has no effect.
> When I do DBCC SHOWCONTIG it shows that the table is MASSIVELY
> fragmented (at least I think that's what it's saying) - results:
> - Pages Scanned........................: 429774
> - Extents Scanned.......................: 429755
> - Extent Switches.......................: 429754
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 12.50% [53722:429755]
> - Extent Scan Fragmentation ...............: 98.13%
> - Avg. Bytes Free per Page................: 7712.6
> - Avg. Page Density (full)................: 4.71%
> There are no indexes on the table at all (I am trying this routine on
> a test database before running it elsewhere and I assumed that leaving
> off the indexes would increase the rate of the inserts) - are you
> suggesting that if I had the indexes on the table it would reduce the
> fragmentation as the data is inserted?
> you say "This along with the data distribution of the data you are
> inserting", but how can I control where the data is physically written
> to? (and therefore control the fragmentation)
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:<uRyrH1iqEHA.1992@.TK2MSFTNGP09.phx.gbl>...
>> Out-of-date space usage info? Have you tried DBCC UPDATEUSAGE?
>> Also, what indexes do you have on the table. This along with the data distribution of the data
>> you
>> are inserting will determine the level of fragmentation you get when you perform your inserts.
>> Did
>> you check fragmentation level using DBCC SHOWCONTIG?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Jackie" <jackiesmith_3@.hotmail.com> wrote in message
>> news:6cb5ab33.0410040640.4e8dc430@.posting.google.com...
>> > Hi,
>> >
>> > I've got a real problem inserting approx 1,000,000 rows of data into
>> > some SQL server 2000 tables. The data is being inserted 1 row at a
>> > time using a T-sql cursor. When I view the table size using
>> > sp_spaceused I get the following type of results:
>> >
>> > reserved = 27021280 KB
>> > data = 3376216 KB
>> > Unused = 23642952
>> >
>> > why is the reserved space sooooo much more than the actual amount of
>> > data in the table?
>> > The table cannot be fragmented as this is the first data which has
>> > been inserted into it.
>> >
>> > I cannot use a different insert method (like DTS) as logic needs to be
>> > applied to the data before it is inserted.
>> >
>> > Is there any way round this?
>> >
>> > Shrinking the tables after the event is also not an option, as I would
>> > run out of disk space way before all of the tables are populated.|||Well, I don't understand at all!!
It was true that the tables did not have indexes or primary keys on -
this was simply because I was running a test and I thought it would
run more quickly - again my not understanding SQL Server properly (not
because I think it's a good idea in terms of design!).
After your latest message - here's what I did (sorry if this is
longwinded).
I loaded the indexes / PKs onto the relevent tables - although I
notice there are no clustered indexes on any of the 5 tables which I
am inserting data into - please bear in mind though that I have no
influence over the design of the database...
I re-ran my routine against THE SAME SET OF DATA - this time the data
loaded in a 100th of the time, and took up approx 350Mb per table!!
(prior to this the tables had 27Gb of reserved space, with maybe 3.5Gb
of data) - HOW CAN THIS BE SO DIFFERENT? (total size of database was
61Gb and now is 3Gb !!)
So, then I tried to run my same routine loading data into a different
database on a different server which already had the indexes on (same
data model). This server only had 12Gb of free space (the total data
in my first db took up 1.9Gb). Afetr a very short space of time the
database ran out of disk space after only a fraction of the data had
been inserted. Looking at SP_SPACEUSED, the reserved space was way out
of sync with the data figure as before.
I ran DBCC UPDATEUSAGE, truncated the 5 tables and re-ran my routine,
now the data is inserting happily and is taking up 353Mb per table
again!
I have no more servers to play with!!
Is the data "behaving properly" the 2nd time around simply because it
is the 2nd time the same routine has been run?
Or is the fact that each time I have run DBCC UPDATEUSAGE relevent?
Surely there must be a way of achieving the proper data figures during
the first time this routine is run (I clearly can't keep running the
same routines and running out of disk space when I come to run this
against production databases).
For reference - here is an example of one of the tables I am inserting
data into (results from sp_help) -
BookingPayment dbo user table 2004-08-24 11:48:44.210
BooRefNo int no 4 10 0 no (n/a) (n/a) NULL
PayID int no 4 10 0 no (n/a) (n/a) NULL
BpyDate datetime no 8 no (n/a) (n/a) NULL
BpyAmount money no 8 19 4 no (n/a) (n/a) NULL
BpyTzoName varchar no 3
yes no no SQL_Latin1_General_CP1_CI_AS
BpyDateUTC datetime no 8 yes (n/a) (n/a) NULL
BpyPayAmount money no 8 19 4 yes (n/a) (n/a) NULL
BpyExrRate float no 8 53 NULL yes (n/a) (n/a) NULL
WrkID varchar no 20 yes no no SQL_Latin1_General_CP1_CI_AS
UseID varchar no 20 yes no no SQL_Latin1_General_CP1_CI_AS
BpyCreatedWkgID varchar no 3
yes no no SQL_Latin1_General_CP1_CI_AS
BpyCreatedUgrID varchar no 3
yes no no SQL_Latin1_General_CP1_CI_AS
BpyCreatedProID varchar no 5
yes no no SQL_Latin1_General_CP1_CI_AS
BpyCreatedPrgID varchar no 3
yes no no SQL_Latin1_General_CP1_CI_AS
BpyInvStatus tinyint no 1 3 0 yes (n/a) (n/a) NULL
PaymentKey nonclustered, unique located on PRIMARY PayID, BooRefNo
PK___3__21 nonclustered, unique, primary key located on
PRIMARY BooRefNo, PayID, BpyDate
PRIMARY KEY (non-clustered) PK___3__21 (n/a) (n/a) (n/a) (n/a) BooRefNo,
PayID, BpyDate
there is a difference in 27Gb (!!!) in total space used between the
1st and 2nd times I insert data into this table...

Many dbs / few tables vs. Few Dbs / many tables

Our infrastructure group had a consultant come in for an eval.Currently, one development group who support many different groupscreate a database per group (sales, etc). The consultant said that thiswas inefficient, and that we should combine all of the databases intoone database. The Infrastructure group now wants us to use one db foreverything.
Does anyone have any thoughts on the advantages / disadvantages of these approaches.It depends what "everything" is. If you are mixing sales data, manufacturing data, security data, then I would still create a number of smaller databases. If, however, all the data is related (for instance, all data uses a common product table, all data uses a common user table, etc.) then maybe it is an OK idea. If the various databases exist currently and there is never any need to do joins across databases, then I think it is a silly idea.|||The data isn't related at all. Are there any performance implications to either method?

Many data entry tables - which ones hold records?

We have an asp.net app with about 200 data entry forms. Customers may
enter data into any number of forms. Each form's data is persisted in
a corresponding sql table. When data entry is complete, it needs to be
processed. Here's where the questions start.

How can we easily determine in which tables a customer has data and how
best to select that data?

We're not opposed to putting all the data in a single table. This
table would wind up having ~15 million records and constantly have CRUD
operations performed against it by up to 5000 users simultaneously.
With sufficient hardware, is this too much to ask of the db?heromull (heromull@.gmail.com) writes:
> We have an asp.net app with about 200 data entry forms. Customers may
> enter data into any number of forms. Each form's data is persisted in
> a corresponding sql table. When data entry is complete, it needs to be
> processed. Here's where the questions start.
> How can we easily determine in which tables a customer has data and how
> best to select that data?
> We're not opposed to putting all the data in a single table. This
> table would wind up having ~15 million records and constantly have CRUD
> operations performed against it by up to 5000 users simultaneously.
> With sufficient hardware, is this too much to ask of the db?

Whether the 200 tables should be 1, 10, 74, or 200 is impossible to tell
from without knowledge about what's in them.

But from a performance point of view, it would not really matter whether
it's one or two hundred tables. Provided, that is, the the single table
has proper indexing.

There are a couple a ways of finding to find data to process:

1) Timestamp column. A timestamp column is automatically updated by SQL
Server with a database-unique value that is monotonically increasing.
(Binary, completely unrelated to date and time). The process that looks
for data would keep track of the most recent timestamp per table, and
retrieve the rows with higher timestamp value. If the process updates the
rows itself, it needs to combine the lookup with a status column. The
drawback with this solution is that the timestamp column must be indexed,
and since it's updated each time the row is updated, there will be a lot
of shuffling around in that index.

2) IDENTITY column. All tables would have an identity column, and then
the process would keep track of the most recently processed value. With
this solution you can only handle inserts, not if users update existing
data.

3) Having triggers on the that enters data about rows to process into a
table. Again, you may need a mechanism to differentiate between user-entered
changes and changes from your processing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, the data to select for processing is not time based, it's based
on whatever tables a customer has data in at the time of processing,
which is determined by the customer. So, option three would be the
best and is similar to our current process. Also, the data is not
updated by the processing, only selected. Even with a table that tells
us which tables have data, how would we build the select statement(s)
to only include those tables with data?|||heromull (heromull@.gmail.com) writes:
> Thanks, the data to select for processing is not time based, it's based
> on whatever tables a customer has data in at the time of processing,
> which is determined by the customer. So, option three would be the
> best and is similar to our current process.

Not that see what time has to do with it. I did say timestamp, but
the timestamp datatype has nothing to do with time. Then again, if you
already have a process similar to option three, then go with that.

> Also, the data is not updated by the processing, only selected. Even
> with a table that tells us which tables have data, how would we build
> the select statement(s) to only include those tables with data?

I can not say that. I don't know your tables. I don't know in which context
this process runs etc. I would assume that since there are 200 tables,
that you would have a stored procedure or a SELECT statement for that
table, as I would assume that all tables would generally have a different
set of columns. If they all have the same schema, then there is a strong
indication of that you should have one single table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"heromull" <heromull@.gmail.com> wrote in message
news:1130029056.070783.59510@.g14g2000cwa.googlegro ups.com...
> We have an asp.net app with about 200 data entry forms. Customers may
> enter data into any number of forms. Each form's data is persisted in
> a corresponding sql table. When data entry is complete, it needs to be
> processed. Here's where the questions start.
> How can we easily determine in which tables a customer has data and how
> best to select that data?
> We're not opposed to putting all the data in a single table. This
> table would wind up having ~15 million records and constantly have CRUD
> operations performed against it by up to 5000 users simultaneously.
> With sufficient hardware, is this too much to ask of the db?

Don't design a database schema around a user interface. Design your database
schema around your DATA and BUSINESS RULES, then build a data access layer
that supports the UI. You say "Each form's data is persisted in a
corresponding sql table". This make little sense to me as a description of a
data model. I suspect (admittedly on the basis of too little information)
that with a better design your perceived problems would disappear. The
stataement "We're not opposed to putting all the data in a single table"
also suggests a very arbitrary approach to database design. Is your current
design a normalized one? If so, I don't understand your confusion about "How
can we easily determine in which tables a customer has data". The answer is
presumably that you use the Customer key - whatever that is in your model.

--
David Portas
SQL Server MVP
--|||Erland Sommarskog wrote:
...
> If they all have the same schema, then there is a strong
> indication of that you should have one single table.
...

Yes, this is the case. They share the same schema.

David Portas wrote:
> Don't design a database schema around a user interface.

I think this is our first mistake.

> Design your database
> schema around your DATA and BUSINESS RULES, then build a data access layer
> that supports the UI. You say "Each form's data is persisted in a
> corresponding sql table". This make little sense to me as a description of a
> data model. I suspect (admittedly on the basis of too little information)
> that with a better design your perceived problems would disappear.

I think you're right. It's that "better design" that we're
researching.

> The
> stataement "We're not opposed to putting all the data in a single table"
> also suggests a very arbitrary approach to database design.

It does and I say that only to mean that it's not too late for us to
implement a better design.

> Is your current
> design a normalized one? If so, I don't understand your confusion about "How
> can we easily determine in which tables a customer has data". The answer is
> presumably that you use the Customer key - whatever that is in your model.

Here's DDL that describes our current "model" (I hear you laughing
already). Notice that all 200 talbes have the same schema. If we
determine a single table approach would be a better design, what would
be some areas of concern? Obviously indexing is on the list.

CREATE TABLE [Customer] (
[CustomerId] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (100),
CONSTRAINT [PK_Customer] PRIMARY KEY NONCLUSTERED
(
[CustomerId]
)
)
--we have ~200 tables with this table's schema
CREATE TABLE [Table1] ( --Table1 thru Table200
[Table1Id] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerId] [int] NOT NULL ,
[Value] [nchar] (1024),
CONSTRAINT [PK_Table1] PRIMARY KEY NONCLUSTERED
(
[Table1Id]
) ,
CONSTRAINT [FK_Table1_Customer] FOREIGN KEY
(
[CustomerId]
) REFERENCES [Customer] (
[CustomerId]
)
)
GO
--each of the 200 tables have a trigger
--similar to this (i'll omit the delete trigger from the DDL)
create trigger
Table1CreateInterview
on
Table1
for insert
as
begin
declare @.CustomerId int
declare @.RecordId int
select
@.RecordId = Table1Id,
@.CustomerId = CustomerId
from
inserted

insert into
interview
(
CustomerId,

TableId,--represents the table caused the trigger eg. 1=Table1
RecId--the value of the primary key of the record causing the trigger
)
values
(
@.CustomerId,
1,
@.RecordId
)
end
GO
--a record is inserted into this table each
--time a record is written to any of the 200 tables
--a record is deleted from this table each time
--a record is deleted from any of the 200 tables
CREATE TABLE [Interview] (
[InterviewId] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerId] [int] NULL ,
[TableId] [int] NOT NULL ,
[RecId] [int] NULL
CONSTRAINT [PK_Interview] PRIMARY KEY NONCLUSTERED
(
[InterviewId]
),
CONSTRAINT [FK_Interview_Customer] FOREIGN KEY
(
[CustomerId]
) REFERENCES [Customer] (
[CustomerId]
)
)
GO
insert into customer(name) values ('some name')
insert into table1(customerid, value) values (1, 'my value in table1')|||heromull (heromull@.gmail.com) writes:
>> Don't design a database schema around a user interface.
> I think this is our first mistake.

An unusually candid confession!

>> If they all have the same schema, then there is a strong
>> indication of that you should have one single table.
> ...
> Yes, this is the case. They share the same schema.

You should make them one table, adding one more column that specifies
the entity that is now hidden in the table name. That table would look
like:

CREATE TABLE [Answers] ( --Table1 thru Table200
[CustomerId] [int] NOT NULL ,
TableNo int NOT NULL,
RowNo smallint NOT NULL,
[Value] [nchar] (1024),
PRIMARY KEY (CustomerID, TableNo, RowNo)

I've added RowNo, beause I don't know if one customer can add more
than one value in the same form. If he can't RowNo should not be
there. I've very deliberate taken out the IDENTITY column, because
this table should have a composite key. My RowNo is indeed a surrogate,
but only in the sub-realm of CustomerId, TableNo. (And TableNo is just
a name I use, in lack of knowledge about the business domain.)

I put the key on CustomerID, TableNo, but depending how you use the
table, you may also have a need for an index on (TableNo, CustomerID).
CustomerID first is good for queries like "what is customer 1233 up to"?,
but not for "What do we have in table 12?".

> It does and I say that only to mean that it's not too late for us to
> implement a better design.

That's great to hear!

> begin
> declare @.CustomerId int
> declare @.RecordId int
> select
> @.RecordId = Table1Id,
> @.CustomerId = CustomerId
> from
> inserted

Uh-uh, classic mistake. A trigger fires once per statement, not once
per row. Yes, as long as data through that form, it will only come
one by one, but then suddenly there is a batch processing loading lots
of data at the same time. So write your trigger as:

INSERT interview (...)
SELECT ...
FROM inserted

> insert into
> interview

And INSERT without a column list is very poor practice in production code.
Someone adds a column to the table, and the statement blows up. That's
bad.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> An unusually candid confession!

Acceptance is the first step right?

Anyway, thank you so much for the support! I think the suggestions are
going to help us out a lot. A lightbulb went off over my head when I
read your composit key explaination.|||Thank you so much for the support! I think this will work for us. My
only other question would be about managing the surrogate column
(RowNo). During Inserts, is there a way to manage it within SQL Server
or would my client apps need to select max(RowNo) and increase it by 1?

Again, thanks!|||On 26 Oct 2005 07:55:17 -0700, heromull wrote:

>Thank you so much for the support! I think this will work for us. My
>only other question would be about managing the surrogate column
>(RowNo). During Inserts, is there a way to manage it within SQL Server
>or would my client apps need to select max(RowNo) and increase it by 1?

Hi heromull,

A typical INSERT statement would roughly look like this:

INSERT INTO Answers (CustomerId, TableNo, RowNo, [Value])
SELECT @.CustomerId, @.TableNo, COALESCE(MAX(RowNo),0) + 1, @.Value
FROM Answers
WHERE CustomerId = @.CustomerId
AND TableNo = @.TableNo

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks! Works perfectly.

Wednesday, March 28, 2012

Mapping fields from two tables

mapping

Monday, March 26, 2012

Many-to-Many Variation

Many-to-many SQL Query (Failed to enable constraints)

many-to-many selection

many-to-many search

Friday, March 23, 2012

Many-To-Many

Many Year Payments

Many to one Select

Many to Many to Many SQL Query

Many to many & other relationships

Many tables-> bad?

many tables vs one big table

Wednesday, March 21, 2012

Many left joins and slow performance

many inserts results in a massive reserved space for table

Many dbs / few tables vs. Few Dbs / many tables

Many data entry tables - which ones hold records?

Managing sql Log File

Blog Archive

About Me