Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
manibad
Starting Member
9 Posts |
Posted - 2012-12-22 : 20:30:27
|
i have Demo,elections and electionshist tables where i should neglect pepole who have status='t' from demo table and for those people i should check whether electionstartdate is null and electionterminationdate is not null from election table then for those people i shld chk in electionhist table that he 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 so long time....i need to pull it in mins.Please guide me in this... |
|
manibad
Starting Member
9 Posts |
Posted - 2012-12-23 : 00:19:59
|
people are you able to understand my requirement?or shall i explain it clearly...?please post your reply:) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-23 : 01:36:52
|
can you show what you tried yet? it seems to be a simple join between involved tables with logic on electionhist table to get latest record. for that you can refer to below post scenario 2http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.htmlmake a try first and post in case you face any issues and then we will help out------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
manibad
Starting Member
9 Posts |
Posted - 2012-12-23 : 14:50:52
|
select seh.GID , seh.empno, 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_GIDempno)) ON s.gid >'0' AND s.empno > '0' AND s.GID=sl.GID AND s.GID=seh.GID AND s.empno=seh.empno 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.empno, seh.typeHere GID,Lid,Slid are id's from different tables which are referenced to demo,election and electionhist...then clt is an stationary table where we find description abt gid's...Acc to my guess record fetching takes time because of these inclusion of id's from referenced tables...but these are referenced to maintain integrity within our system.So we can't neglect it tooFYI i am an beginner so i am not sure how far this query is correct..please teach me to rectify mistakes in this.If you require more info please tell me i will explain you mmuch more than this.Thank you |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
manibad
Starting Member
9 Posts |
Posted - 2012-12-25 : 20:28:52
|
I need to find the record who has ElectionStartdate is null and electiontermdate is not null in the Election tablebut the same person has electionstartdate is not null and electiontermdate is null in Electionhist table.In hist i need to pull the latest record satisfying the given conditionCREATE 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 keyIC->Identity columnFK_XXX->Foregin key_Refereneced tablePic of the tableDemo 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 Electiontermdate25 2 1 123 1000 23 765 S NULL 3/2/201026 3 1 123 1090 25 765 S 9/9/2009 NULL27 4 2 123 1090 25 765 L 9/9/2009 NULL28 5 3 123 1101 87 321 S NULL NULL29 8 4 123 1190 89 765 S 9/9/2009 NULL30 9 5 123 1190 89 765 L 9/9/2009 NULLQuery what i have tried:select seh.GID , seh.eno, seh.type, Max(seh.LID) LID,MAX(seh.slid) SlidFROM 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.GIDAND g.ClID=90INNER JOIN dbo.Ld l with(nolock)on l.GID=g.GIDand l.LID=seh.LIDINNER JOIN dbo.SLd sl with (nolock)ON sl.gid=l.gidAND sl.LID=l.LIDAND sl.SLID=seh.SLIDINNER JOIN dbo.demo s WITH(NOLOCK, INDEX(IX_demo_GIDeno))ON s.gid >'0'AND s.eno > '0'AND s.GID=sl.GIDAND s.GID=seh.GIDAND s.eno=seh.enoAND s.LID=sl.LIDAND s.SLID=sl.SlIDAND 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.GIDAND se.GID=seh.GIDAND se.LID=s.LIDAND se.SLID=s.sldidand se.electionStartDate is nulland se.electionTerminationDate is not nullWHERE seh.gid >'0'AND seh.LID > '0'AND seh.type in('S','L')AND seh.electionStartDate IS NOT NULLAND seh.electionTerminationDate IS NULL)group by seh.GID , seh.eno, seh.typeExpected Result GID eno Type LID Slid 123 1190 89 765 S123 1190 89 765 LIf you are not clear with this pls let me knopw i will explain you more. |
|
|
|
|
|
|
|