| Author |
Topic |
|
clpayne
Starting Member
7 Posts |
 Posted - 2002-10-16 : 16:41:40
|
| Hey all,I've got an issue where I'm trying to retrieve ~250k + records from a table. The query is a simple select, retrieving one varchar field. No wheres, no joins, nothing. The problem is, this simple query just takes too long to execute. On average, ~45 seconds to 1 minute. I've got a bunch of indexes on the table (the ones the index tuning wizard recommended), and it didn't change a thing. I've reindexed the indexes as well, and nothing. The database is an 8-proc something or other, with gobs of ram. In short, it's nice and powerful.Anyone have any ideas on how to speed this sucker up?Thanks |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-16 : 16:44:33
|
| What kind of client is it being sent to? A web page? 250,000 rows going to a web page is quite a lot, hell, for any client 250,000 is a lot. And if it is a web page, I'll bet you $20 that it's not the query that's slow, but the page rendering. |
 |
|
|
clpayne
Starting Member
7 Posts |
Posted - 2002-10-16 : 16:49:23
|
| Nope, it's just in query analyzer. I've tried putting the query in a stored proc too, and no joy.btw, that was a quick reply! |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-10-16 : 16:59:59
|
| 1 minute sounds about as far as I can tell. Pulling that much data at once takes time to pull across the network and render in the QA UI.May I ask why you need to look at 250k records at once? That seems like more data than a human can realistically process at once.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-16 : 17:06:56
|
| And I just tried it on my local machine, no network traffic at all, and it took 1:26 to get that many rows. And that was a single int column too. |
|
|
|
clpayne
Starting Member
7 Posts |
Posted - 2002-10-17 : 09:01:27
|
quote:
And I just tried it on my local machine, no network traffic at all, and it took 1:26 to get that many rows. And that was a single int column too.
Yea, that's unfortunately what I figured. I would have liked to be able to speed that up, but I'm afraid with that much data there's not much I can do.As for why I need it, I'm pulling data from a user database for a newsletter my company sends out. The query only needs to be run once a day, but anything that takes over 20 seconds IMHO needs to be fixed.Thanks all. |
|
|
|
sherrer
64 Posts |
Posted - 2002-10-17 : 09:59:10
|
| You have not given much information to work with here. I don't know the width of your table nor anything about it's structure. I don't know if 250K rows is .25MB or 100MB. If you are trying to move around large amounts of data, there may not be a way to make your query faster. Are you familure with execution plans? In query analyzer turn on the option to view the excecution plan, it will tell you just about everything you need to know. Since it is only one table, unless you are not effectivley using an index, it is probably simply due to the size of the data. One other question, since you are pulling from only one table, is an extreamly wide table... if so you may want to consider more normalization. I will be pleased to help if you give some more info to work with. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-17 : 10:14:16
|
| It really doesn't matter about the size of the table, it's the AMOUNT of data being returned to the client. Even with small columns, 250,000 rows of data will translate to well over 1 MB of network traffic, and can easily reach 4-5 MB. Even with no other connections on the pipe that's gonna take time to move (indeed, look at my earlier comment about the local box)Network traffic is always the Achilles' Heel of database performance, but in this case (a SELECT statement) there's very little you can do to improve it. Maybe playing with the packet size may give you some improvement. |
|
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2002-10-17 : 19:32:54
|
| If you are using ADO to connect to the server, set it up to use ASYNC_FETCH or whatever that option is called. That way, you can start processing records while it is returning items. You'll have to check the state of the recordset and whether there are more records that need to be returned, but you can make it seem like it's taking less time by starting your processing ASAP.This sounds like a network problem, and not a query problem as the others have said. As such, it needs to be solved by the app using it, not the database.Also, you need to find the level of "good enough." I took a hour process and reduced it to minutes. It might've tooken more than 20 seconds, but the users were so happy that it worked so much faster that they didn't care that I could spend a couple of days making it a little faster. Keep in mind diminishing returns and all that. Being ran once a day, it probably isn't a big deal if it takes a minute to run.----------------------"O Theos mou! Echo ten labrida en te mou kephale!" |
|
|
|
jeroena
Yak Posting Veteran
76 Posts |
Posted - 2002-10-18 : 07:55:24
|
| Hi all,Optimizing a query is always a good idea when it can be optimized, but... I think in two categories. OLTP queries and batch processes. When the former isn't fast, applications respond slow etc. so these must be fast. Concerning the latter, the idea that anyting that looks like SQL must run wihtin 20 secs. is a bit odd.You might consider using Data Transformation Services if the target let's you. It's typically designed for batch processing (incl. monitoring, etc.)Cya, Spike. |
|
|
|
|
Slow but simple query