Sql help - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Sql help

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-12-12 : 10:45:40

I need a query to get desired output with affecting performance since i have 1 billion records for the below table.


DECLARE @TestData TABLE 
( SId Varchar(50)
,Sdate Varchar(50)
,sstatus Varchar(50)
)

INSERT @TestData
SELECT 200900, '12/12/2008', 'Registered' UNION ALL
SELECT 200900, '12/12/2008', 'Enroll' UNION ALL
SELECT 200903, '12/12/2009', 'Registered' UNION ALL
SELECT 200904, '12/11/2008', 'Registered' UNION ALL
SELECT 200909, '12/11/2008', 'Enroll'

SELECT * FROM @TestData

-- Desired output with affecting performance..(since i have huge data)

SID     Sdate       sstatus      ESID     ESdate      Esstatus 
-----   ---------   ---------  --------  -------     ------
200900	12/12/2008  Registered   200900	 12/12/2008   Enroll
200903	12/12/2009  Registered   NULL      NULL        NULL
200904	12/11/2008  Registered   NULL      NULL        NULL
NULL    NULL        NULL	 200909	 12/11/2008   Enroll

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 10:49:44

[code]
SELECT t1.*,t2.*
FROM (SELECT
FROM @TestData
WHERE sstatus = 'Registered'
)t1
FULL OUTER JOIN (SELECT
FROM @TestData
WHERE sstatus = 'Enroll'
)t2
ON t2.SId = t1.SId
AND t2.Sdate = t1.Sdate
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-12-12 : 10:53:48

Thanks Visakh for fast response..

How about performance of this query..Is there any way this query be tunned..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 10:56:06

for better performance you can dump the results onto two temporary tables and then add index inside that on SId,Sdate columns to improve query times.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-12-12 : 11:40:29

Loading of 1 billion records of data into 2 temp table would be ok..
Do i need to do any maintainance for temp db if i have to create temp tables.

sunnyleoneo
Starting Member

19 Posts

Posted - 2011-12-12 : 13:12:48

Hi,

Check some performance tips
http://stackoverflow.com/questions/137226/how-do-you-optimize-tables-for-specific-queries#138949
http://stackoverflow.com/questions/18783/sql-what-are-your-favorite-performance-tricks#103176

Also
Try to find way other than dumping value in temp table otherwise you can go for indexing temp table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 00:54:18

quote:
Originally posted by sqlfresher2k7

Loading of 1 billion records of data into 2 temp table would be ok..
Do i need to do any maintainance for temp db if i have to create temp tables.

Are you sure you want all 1 billion at same time for processing? If not, you may apply some filter and take subset of data.
Didnt understand what you mean by maintainance of temp table?
the scope of temp (#) table will only for connection so everytime you close connection it will get destroyed automatically

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-12-13 : 09:42:21

Yes,i want 1 billion to do processing at same time..

Would be any performance impact if use of temporary tables..Do i have to do anything for tempdb database for usage of temporary tables.

I am using query select * into #temptable from table1

Can i create index on the #temptable to improve the performance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 10:33:14

quote:
Originally posted by sqlfresher2k7

Yes,i want 1 billion to do processing at same time..

Would be any performance impact if use of temporary tables..Do i have to do anything for tempdb database for usage of temporary tables.

I am using query select * into #temptable from table1

Can i create index on the #temptable to improve the performance.

you can create index on temporary tables as well
I hope your db recovery model is simple

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

saritatcs
Starting Member

1 Post

Posted - 2011-12-13 : 10:58:56

Hi ,

I am facing some performance issue in sqlserver 2008,same query I am running in sqlserver 2000 it is taking only 5 min,while in sqlserver 2008 itis taking one hour.could any body help on this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 11:02:44

quote:
Originally posted by saritatcs

Hi ,

I am facing some performance issue in sqlserver 2008,same query I am running in sqlserver 2000 it is taking only 5 min,while in sqlserver 2008 itis taking one hour.could any body help on this?

you didnt provide more details?
is there any different in data volumes between tables in 2000 and 2008?
Are the indexes same in both 2000 and 2008 server databases?
Are the statistics of 2008 db up to date?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources