Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

how to fetch records from multiple tables Expand / Collapse
Author
Message
Posted Thursday, December 27, 2012 5:51 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:46 PM
Points: 91, Visits: 143
i have Demo,elections and electionshist tables where i should neglect the pepole who have status='t' from demo table and for those who are not neglected i should check whether electionstartdate is null and electionterminationdate is not null from election table then for these people i should check in electionhist table that whether the person has electionstartdate is not null(the value must be fetched from the top most load.
In demo and election tables we have nearly 50000 -60000 records but in electionhist tables nearly 10000000 records are there.
Can anyone please suggest me in pulling records when a situation prevails like this....i am able to do it but it takes a longer time....i need to pull it in mins.
I have given you the entire picture of the situation..Pleas go through this and let me know your suggestions.Thanks in advance

CREATE TABLE Demo
(
Sno bigint,(PK,IC)
GId bigint,(FK_clt)
lid bigint,(Fk_ld)
Slid bigint,(Fk_Sld)
eno varchar(15),
status varchar(5)
)INDEX(Gidempno)

CREATE TABLE Election
(
SSno bigint,(PK,IC)
Sno bigint,(FK_demo)
GId bigint,(FK_clt)
lid bigint,(Fk_ld)
Slid bigint,(Fk_Sld)
eno varchar(15),
Type varchar(6),
electionstartdate datetime(8),
electiontermdate datetime(8)
)
INDEX(Gidlidtype)

CREATE TABLE Electionhist
(
SSSno bigint,(PK,IC)
SSno bigint,(FK_election)
Sno bigint,(FK_demo)
GId bigint,(FK_clt)
lid bigint,(Fk_ld)
Slid bigint,(Fk_Sld)
eno varchar(15),
electionstartdate datetime(8),
electiontermdate datetime(8)
)

Create table clt
(
sid int,[PK,iC]
gid bigint,
clid bigint
)

Create table ld
(
Sid int,[pk,ic]
lid bigint,
gid bigint
)

Create table sld
(
sid int,[PK,IC]
slid bigint,
lid bigint,
gid bigint
)

PK->primary key
IC->Identity column
FK_XXX->Foregin key_Refereneced table


Pic of the table

Demo
Sno Gid Lid Slid Eno Status
6 123 9876 546 765 A
7 123 9876 546 546 R
8 123 9876 546 321 T

Election
Ssno Sno Gid Lid Slid Eno type ElectionStartdate Electiontermdate
10 6 123 9876 546 765 S NULL 3/2/2012
11 6 123 9876 546 765 L NULL 3/2/2012
12 7 123 9876 546 546 S 3/2/2012 NULL

Electionhist
SSSno Ssno Sno Gid Lid Slid Eno type ElectionStartdate Electiontermdate
25 2 1 123 1000 23 765 S NULL 3/2/2010
26 3 1 123 1090 25 765 S 9/9/2009 NULL
27 4 2 123 1090 25 765 L 9/9/2009 NULL
28 5 3 123 1101 87 321 S NULL NULL
29 8 4 123 1190 89 765 S 9/9/2009 NULL
30 9 5 123 1190 89 765 L 9/9/2009 NULL

Query what i have tried:

select seh.GID , seh.eno, seh.type, Max(seh.LID) LID,MAX(seh.slid) Slid
FROM dbo.clt g WITH(NOLOCK)
CROSS APPLY(select seh.gid,seh.LID,seh.Slid,seh.SSN,seh.type from dbo.Electionhist seh WITH (NOLOCK)
INNER JOIN dbo.clt g with(nolock)
on g.GID=seh.GID
AND g.ClID=90
INNER JOIN dbo.Ld l with(nolock)
on l.GID=g.GID
and l.LID=seh.LID
INNER JOIN dbo.SLd sl with (nolock)
ON sl.gid=l.gid
AND sl.LID=l.LID
AND sl.SLID=seh.SLID
INNER JOIN dbo.demo s WITH(NOLOCK, INDEX(IX_demo_GIDeno))
ON s.gid >'0'
AND s.eno > '0'
AND s.GID=sl.GID
AND s.GID=seh.GID
AND s.eno=seh.eno
AND s.LID=sl.LID
AND s.SLID=sl.SlID
AND s.Status <>'T'
INNER JOIN dbo.Election se WITH (NOLOCK,INDEX (IX_Election_GIDLIDtype))
ON se.GID >'0'
AND se.LID > '0'
AND se.PlType in('S','L')
AND se.GID=s.GID
AND se.GID=seh.GID
AND se.LID=s.LID
AND se.SLID=s.sldid
and se.electionStartDate is null
and se.electionTerminationDate is not null

WHERE seh.gid >'0'
AND seh.LID > '0'
AND seh.type in('S','L')
AND seh.electionStartDate IS NOT NULL
AND seh.electionTerminationDate IS NULL
)
group by seh.GID , seh.eno, seh.type


Expected Result
GID eno Type LID Slid
123 1190 89 765 S
123 1190 89 765 L

If you are not clear with this please let me know i will explain you more.
Post #1400763
Posted Thursday, December 27, 2012 11:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:42 AM
Points: 14,371, Visits: 12,347
CELKO (12/27/2012)


Please learn the ISO-11179 rules for data element names.


Which can be downloaded where?

CELKO (12/27/2012)

I guess PK means “NOT NULL PRIMARY KEY”, but a good SQL programmer never uses IDENTITY for a key.


That's hilarious. Care to explain why?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1400800
Posted Thursday, December 27, 2012 11:49 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:09 AM
Points: 2,849, Visits: 4,028
Also please post exec plan(people would prefer graphical plan ) along with index definition too as index play significant role to pull out the data faster.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1400803
Posted Friday, December 28, 2012 9:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:16 PM
Points: 14,098, Visits: 13,720
You also might want to read up on the NOLOCK hint. It can produce all sorts of really difficult to replicate bugs. It can (and will) miss some rows and even return duplicates.

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/



_______________________________________________________________

Need help? Help us help you.

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

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1400949
Posted Friday, December 28, 2012 10:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 2,807, Visits: 4,228
Bhuvnesh (12/27/2012)
Also please post exec plan(people would prefer graphical plan ) along with index definition too as index play significant role to pull out the data faster.



Agreed. The indexes on Electionhist will be especially critical to performance. If you've got the "default", no-thought identity-only clustered index, performance will never be that good.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1400984
Posted Friday, December 28, 2012 12:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:16 PM
Points: 14,098, Visits: 13,720
Joe I certainly understand your position but I do have a question. You continually refer to Cindy Lou Who as employee #42. In a Joe Celko database what would be used for the primary key in this table?

You can't use SSN. That would be a reasonable choice at first glance but there are two MAJOR flaws with that. First of all, SSN have and will be reused. When people die their SSN gets recycled into the available pool. Secondly, and probably far more important, is that SSN is sensitive information. Anytime you have SSN in your database it should be in one and only one location and it should be encrypted at rest. This pretty much rules out SSN as a valid primary key. What else is there? Name certainly doesn't do it.


_______________________________________________________________

Need help? Help us help you.

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

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1401006
Posted Friday, December 28, 2012 1:27 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 2,807, Visits: 4,228
Sean Lange (12/28/2012)
Joe I certainly understand your position but I do have a question. You continually refer to Cindy Lou Who as employee #42. In a Joe Celko database what would be used for the primary key in this table?

You can't use SSN. That would be a reasonable choice at first glance but there are two MAJOR flaws with that. First of all, SSN have and will be reused. When people die their SSN gets recycled into the available pool. Secondly, and probably far more important, is that SSN is sensitive information. Anytime you have SSN in your database it should be in one and only one location and it should be encrypted at rest. This pretty much rules out SSN as a valid primary key. What else is there? Name certainly doesn't do it.


Not to mention that some employees may not even have SSNs: foreign representatives, etc..

An identity is clearly applicable to many things, including (but not limited to) employee numbers and order numbers. Obviously you create a unique constraint of some type to guarantee uniqueness.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1401040
Posted Friday, December 28, 2012 1:31 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 4:23 PM
Points: 415, Visits: 2,333
CELKO (12/27/2012)

I guess PK means “NOT NULL PRIMARY KEY”, but a good SQL programmer never uses IDENTITY for a key.


That's hilarious. Care to explain why?


I would like to second this evaluation. IDENTITY for a table's primary key is out there as an accepted practice, especially when there's no suitable natural key. Surely there's some good SQL programmers out there doing exactly what CELKO says they do not do.

edit: quote tag
Post #1401041
Posted Friday, December 28, 2012 2:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:16 PM
Points: 14,098, Visits: 13,720
No offense Joe but I think you have lost your mind if you actually think that Amazon uses the customer email as part of the primary key. How many supporting tables would that value be needed as a foreign key? We all know that varchar is not a great candidate for performance when the length can be all over the place like an email. Given that the user is allowed to change their email at any time it goes against the notion that a primary key not change. Can you imagine how ridiculous it would be to change your email? There is not a chance on this earth that Amazon uses that as part of a primary key. They would be better off using the person's name instead of email, most people's names never change unlike their email address. If they used email as part of the key they would just about be forced to use an update trigger to update all the other tables in their system that need that value. We can also be pretty sure that they don't have an update trigger on the account table to propagate foreign key values all over the place.

I agree that identity is probably overused but to say that it has no place in the real world is just flat out rubbish. To use your own arguments about turning Cindy Lou Who upside down to verify an identity, the same is true of SSN and email. They are no more a part of the physical entity than some arbitrary number.


_______________________________________________________________

Need help? Help us help you.

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

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1401059
Posted Friday, December 28, 2012 2:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:02 AM
Points: 7,223, Visits: 12,989
CELKO (12/28/2012)
Usually I have to identify a person by the role they play in the Universe of Discourse. Most often the role does actually involve a person; a bank account number is universal (part of the IBAN, used by the Fed Reserve, etc) and can belong to a "lawful person", such as corporations, organization, etc.

For a reasonable level of trust, I use the email address + password, like every website on earth. Seems to work well enough for Paypal, Amazon, et al. People do not change them much any more

I am with you on the theoretical aspects of this discussion but I have a hard time wrapping my head around the practical application aspects. In this example how would you model the primary key in the parent-table that stores people from which most people-attribute tables might be related? Assuming email is stored as a CHAR(320) and password is stored as a one-way hash in the database, let's say in a CHAR(100), then are you saying your primary key would be 420 bytes wide? Would you use a variable-length type like VARCHAR for email to save on some of the storage? What would happen if the email did change to something wider than before?

All of that would seem impractical when compared to a 4-byte INT, or in the case of Amazon likely an 8-byte BIGINT would be required, regardless of how it might fly in the face of the theory that a machine's idea of a physical insert attempt is an invalid key to identify a person. Or do you use a checksum or hash algorithm of some sort on the combination of email and password to reduce the size while maintaining uniqueness?

Even in the case of a bank account number you'll have a 34-byte key which could weigh down internal data processing when compared to an 8-byte BIGINT.

Bottom line question: what actual value would you store in the parent table to use in uniquely identifying a person, as well as to relate the parent person table other tables where that person's addresses (0-n) might be stored?

As an aside, a SQL Server identity column's values in a database stored on serverA are portable to serverB. In the case where serverA dies and we restore the database to serverB from backups SQL Server will know where serverA left off in terms of the next number to issue from serverB.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1401070
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse