| Author |
Topic |
|
Chribba
Starting Member
8 Posts |
 Posted - 2005-06-21 : 06:54:45
|
| This question might have been asked before but I try again as I could not find any matching information using searches.I have a table with about 1,500,000 rows, which takes forever to go through even with a simple query, what I was thinking if it is possible:A way to either return the top 20 records of my query, or in case it does not find 20 matching rows within a certain time limit, the query aborts and returns the results found.Eg I specify a timeout of 30 secs and execute the query, 30 seconds pass and the query is aborted and returns example 5 rows that it did find during the search.Anyone know if this is possible at all?Or perhaps another way of searching through the table.Currently searching with the query "SELECT TOP 1 ID FROM WEBDATA WHERE MESSAGE = 'test'" takes over 1 minute to complete, and no results thus there is no such row.Maybe it is just me but shouldn't a table with 1.5M rows go a bit faster to search, it is running on a Dual Xeon 2.4ghz machine with 2GB ram.Any ideas or thoughts?Thanks!/c |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-06-21 : 07:00:53
|
| Indexes are a good idea. If you index the Message column you'll see some rather dramatic improvement in performance. Books Online can give you more information on indexes. |
 |
|
|
Chribba
Starting Member
8 Posts |
Posted - 2005-06-21 : 07:02:59
|
| Yeah the table is already indexed, and it improved a lot when it got indexed, however it's still very slow even when indexed. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-06-21 : 07:06:48
|
| Have you looked at the execution plan for that query? It's under the Query menu in Query Analyzer. If you see scans instead of seeks, you may need to create statistics on the Message column as well. And if that column contains a high number of the same exact values, then it won't be selective enough for the optimizer to use the index.Another thing that will slow you down is a wide table, i.e. lots of columns, lots of wide and/or variable-length columns. Can you post the structure of your table (the actual CREATE TABLE statement would be most helpful) |
|
|
|
Chribba
Starting Member
8 Posts |
Posted - 2005-06-21 : 07:27:12
|
| The index is not based upon the content of the Message column though, it is based on the post date of the message (another column).The table was created with Enterprise Manager I belive, but the table looks like this:ID int (4) <-- identity key on this oneTHREADID int (4)AUTHOR varchar (100)MESSAGE varchar (7000)POSTED varchar (50)PAGE varchar (50)So the Message column is pretty large, but needed to fit the data I think, maybe it's better to run an "average" query on that column? and then limit the width to that average as it's not a major thing to have all data in that field.As for the statistics, if I got the correct info on what index do it checks if it is worth using the index when running a query? In my case the query should always list the last added posts, thus always start the scan from the end of the table, I don't think adding statistics will help me there, or?Thanks. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-06-21 : 07:39:40
|
| Well, indexing a varchar(7000) is out of the question (900 bytes is the limit for an index key), same thing goes for the statistics. I would suggest checking the data you have already, at least find the longest entry you have in the Message column:SELECT Max(Len(Message)) FROM WebDataIf you can reduce the column width then I'd say do it, although in your situation I don't think it matters much. Very often it does no good to allow people to put a lot of text into a comments section anyway.If you really need to do these kinds of queries against the Message column then full-text indexing might be the only way to improve the performance significantly. Regular indexing won't do it, unless you can get that column down to 250 characters or less. Anything more and you'll end up with huge indexes that use up more space than they're worth. |
|
|
|
Chribba
Starting Member
8 Posts |
Posted - 2005-06-21 : 07:51:39
|
| The max is 6999 as I limited it to that initially, and I have a few thousand rows above 5000 so maybe the difference wouldn't matter that much if I limit it.Although the average lenght is 595 so basicly I should be able to scale it down to 2-3000.But I think I will try full-text indexing and see if it can do any performance boost for me, otherwise I will give the width a try.Altho the best way would be the initial question, if I can abort and return the results found after 30 seconds as it will solve my question.Thanks for all help! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-06-21 : 18:39:23
|
| The best you can do to achieve that effect is to use the FAST(n) query hint, you can find more about it in Books Online under "hints". You would have to write your application code to use asynchronous execution, and you may not be able to correctly retrieve the results into an ADO recordset anyway. I've not tried to do this kind of async operation so I don't know if it's really practical, but it should be possible. |
|
|
|
Chribba
Starting Member
8 Posts |
Posted - 2005-06-23 : 03:27:58
|
| So I played around with various things trying to improve the speed and one thing is really strange.I made a copy of my live db to a new one for me to play around with.On the live db a query takes about 2½ minute to complete, while the same query is run on the dev db it completes in 20 seconds.Any idea why the big difference?Is the transaction log involved in anything that has to do with the queries since the live transaction log is 9gb while the dev log is only a few 100 mb.Would I see improvment if I copied over all data and made a new "live" db, or any other thoughts?Thanks! |
|
|
|
|
Abort/Return Query with Results