homepage Welcome to WebmasterWorld Guest from 54.205.193.39
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
querying several million records
sql server 2000
musicales




msg:3057239
 1:42 pm on Aug 23, 2006 (gmt 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?

 

LifeinAsia




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

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]

fischermx




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

I'll be shorter: show your query or explain in words what is doing.

syber




msg:3057720
 7:37 pm on Aug 23, 2006 (gmt 0)

How many ids are you pulling out at a time?

mattglet




msg:3060669
 8:02 pm on Aug 25, 2006 (gmt 0)

Use "WITH NO LOCK".

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved