Welcome to WebmasterWorld Guest from 23.22.140.143

Forum Moderators: open

Message Too Old, No Replies

querying several million records

sql server 2000

     
1:42 pm on Aug 23, 2006 (gmt 0)

Preferred Member

10+ Year Member

joined:May 8, 2002
posts:440
votes: 0


I'm trying to pull out ids from a table that has 5 million plus records. I'm using an ASP page and SQL server 2000. The trouble I'm having is each such query takes around 5 seconds or more, during which time the database seems to grind to a halt, slowing the rest of the site down with it.

Does anyone know any way round this?

3:22 pm on Aug 23, 2006 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5550
votes: 24


Do you have your tables indexed? Have you re-indexed the tabels recently? What data type are the IDs? Are you pulling out just the fields you need or are you doing a "SELECT *" and pulling out a lot of data you don't need?

Have you confirmed that it's the actual query taking so long or might it be the processing of the ASP page as well? Depending on exactly what you're doing, it might also make sense to move the query from ASP into a stored procedure.

Another thought- does your DB server have enough memory/processing power? And are you using separate servers for your web server & SQL 2000?

[edited by: LifeinAsia at 3:24 pm (utc) on Aug. 23, 2006]

4:42 pm on Aug 23, 2006 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Jan 31, 2005
posts:1651
votes: 0


I'll be shorter: show your query or explain in words what is doing.
7:37 pm on Aug 23, 2006 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 12, 2004
posts:393
votes: 0


How many ids are you pulling out at a time?
8:02 pm on Aug 25, 2006 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Nov 12, 2002
posts:1482
votes: 0


Use "WITH NO LOCK".