Put it in another IDE port (swap with hard drives), and/or switch IDE cables. If that doesn't work, then put another drive in its place. If the other drive works, but the DVD-ROM doesn't, then you should most likely replace the DVD-ROM drive.
Also, try updating firmware and drivers. I don't know if this is the problem, but it could be SP2 as well.
I just bought a new DVD-ROM drive, and it is suddenly not reading any CDs with any kind of speed. The onset of this problem was very sudden; I just installed Half-Life two days ago and it worked fine. I began to copy some files from another CD tonight, and now it is reading very slowly (2+ hours for a 700 meg file). The drive is on it's own cable and IDE slot (my two hard drives are on my other slot).
I am running:
-WinXP Pro with SP2
-AMD Duron 1200
-256 Mb of PC-133 RAM
-128 Mb ATI Radeon 9600 SE (AGP 4x)
-VIA KT133 Motherboard