Effecient way to query large data

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 2000 Forums

Transact-SQL (2000)

Effecient way to query large data

Author

Topic

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2007-09-20 : 15:22:09

Hello,

If I have 50 million records and looking to extract a small portion (one day of 1000 records) where date is sometime last year, what's the best way of doing this?

Data: 50 million equals 4 years of daily inserts, all in one table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-20 : 15:24:12

SELECT TOP 1000 *
FROM YourTable
WHERE YourDateTimeColumn = '01-01-2006'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-20 : 15:28:21

I have noticed SET ROWCOUNT is even faster than TOP.

SET ROWCOUNT 1000
SELECT *
FROM YourTable
WHERE YourDateTimeColumn = '01-01-2006'

SET ROWCOUNT 0

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-20 : 15:28:43

quote:
Originally posted by tkizer
SELECT TOP 1000 *
FROM YourTable
WHERE YourDateTimeColumn = '01-01-2006'

(0 row(s) affected)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-20 : 15:30:26

quote:
Originally posted by X002548

quote:
Originally posted by tkizer
SELECT TOP 1000 *
FROM YourTable
WHERE YourDateTimeColumn = '01-01-2006'

(0 row(s) affected)

I don't see your point here.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-20 : 15:37:06

Unless all the rows at midnight, that's what you're gonna get, except for the ones that are excatly at midnight

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-20 : 15:38:34

It was just an example.

Some people split their dates and times into different columns anyway, so my example would work on those systems. I believe Jeff does this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-20 : 15:39:47

quote:
Originally posted by tkizer

It was just an example.

Some people split their dates and times into different columns anyway, so my example would work on those systems. I believe Jeff does this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Not to split hairs, but he's the only one I know that does that

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-20 : 15:45:05

I think it'll become more common in SQL Server 2008 since there is native support for it I heard.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2007-09-20 : 15:53:29

This is the best forum yet! Thanks all for quick response.

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2007-09-20 : 16:03:07

Benchmark: both 7 seconds

/*------------------------
SET ROWCOUNT 200000
SELECT *
FROM dbo.myTable
WHERE (Month(ProcDate) = '12' AND Year(ProcDate) = '2006')
SET ROWCOUNT 0
------------------------*/

/*------------------------
SELECT TOP 200000 *
FROM dbo.myTable
WHERE (MONTH(ProcDate) = '12') AND (YEAR(ProcDate) = '2006')
------------------------*/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-20 : 16:06:59

Your statements are not efficient as they can't make use of an index.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2007-09-20 : 16:17:36

I'm just running as suggested above.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-20 : 16:21:18

what is the datatype of your ProcDate column? Do you have any index on the ProcDate column?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-20 : 16:21:18

I realize that, but they can't make use of an index. It would be more efficient if you did something like this:

WHERE ProcDate >= '12-01-2006' AND ProcDate <= '12-31-2006'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2007-09-20 : 16:23:46

O I see. Thanks, Tara.

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-20 : 17:05:39

Actually:

WHERE ProcDate >= '12-01-2006' AND ProcDate < '1-1-2007'

hehe

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-20 : 17:08:58

Yes, oops!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-09-20 : 20:05:27

Pedantic point:

String dates ought to be in 'yyyymmdd' format - note there are no hyphens.

Kristen

dewacorp.alliances

452 Posts

Posted - 2007-09-20 : 20:40:04

If you only want to get that snapshot data and you don't care during this query is running ("dirty read") if someone else locking that table by doing INSERT/UPDATE/DELETE, then you can use WITH (NOLOCK) after the table syntax.

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-21 : 04:45:03

quote:
Originally posted by Kristen

Pedantic point:

String dates ought to be in 'yyyymmdd' format - note there are no hyphens.to get out of any errors like
Server: Msg 242, Level 16, State 3, Line 3
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Kristen

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources