| Author |
Topic |
|
NewMedia42
Starting Member
35 Posts |
 Posted - 2009-02-17 : 16:36:51
|
| I've been having an issue with a particular query/table, here's a bit of background:The table in question gets about 5 million rows added per day, spread across the entire day - the closest analogy would be a log, and I need to have access to all the data in it (so I can't archive/remove data). All the inserts happen one at a time, and they are typically preceded by a select to determine whether or not the row already exists.Now, I have two applications which are accessing it, starting at two different times, so when the second one starts up, it grabs a listing of everything that needs to be added for that day (let's say 10 million items), then it removes anything that already has been added today - the problem is that this query times out. I've tried breaking it down into the simpliest/smallest queries I can, but it still doesn't complete. At the end what I've even tried doing is doing a SELECT INTO, and copying all the data from today so far into a temp table to work on, but that times out as well. I suspected it has something to do with items being continuously added in the background, so I tried doing something like:SELECt * INTO [#TempTable] FROM [SourceTable] WITH (NOLOCK) WHERE Date >= 'today' AND ID < MaxIDIn the above example, today is today's date, and MaxID is just the result SELECT MAX(ID) AS bigint FROM [SourceTable] ... My idea in doing this was that this way I could get it to ignore anything new added to the table since the query started.Any thoughts, words of wisdom, etc on this? Any help would be greatly appreciated! :) |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
NewMedia42
Starting Member
35 Posts |
Posted - 2009-02-17 : 17:16:36
|
quote:
Originally posted by tkizer
Is the table indexed? What does the execution plan show? Is there blocking?
On the index front I have three indexes, one on the Data, one on the ID, and one on another ReferenceID,Date.In the execution plan, it's showing:SELECT INTOCost 0%Table Insert[#TempTable]Cost: 60%TopCost: 0%Compute ScalarCost 0%Nested Loops(Inner Join)Cost 0%Then it splits into two:Index Seek:DateCost: 20%RID Lookup[Table]Cost: 20%As far as blocking goes, what would you recommend as the easiest method when there's a ton of traffic already running on it?Thanks! |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
NewMedia42
Starting Member
35 Posts |
Posted - 2009-02-17 : 18:49:23
|
quote:
Originally posted by tkizer
The easiest way to check for blocking is via sp_who, sp_who2, or by querying master..sysprocesses.
I ran the query and watched sp_who2, none of the returns were being blocked. I refreshed it every 10-20 secs, but nothing was ever shown - and there definitely was a select/insert process running in the background at the same time. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
NewMedia42
Starting Member
35 Posts |
Posted - 2009-02-17 : 19:14:52
|
quote:
Originally posted by tkizer
What is your timeout value set to in the application? If you were run to the same queries in Management Studio, how long do they take to run?
In my app the timeout is 20 minutes, in Management Studio it's a couple hours. The query is still running right now, 29+ minutes - just ran sp_who2, no blocks. This should at most only be a couple million rows, and at most probably ~20 megs of data... |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
NewMedia42
Starting Member
35 Posts |
Posted - 2009-02-17 : 21:26:54
|
quote:
Originally posted by X002548
whats this?WHERE Date >= 'today'can you post some ddl of the table?And how many rows do we have right now?
So the query just completed, it returned 7,261,269 rows and it took 53 minutes...The table is:ID bigintCreated smalldatetimeReferenceID intValue1 intValue2 intValue3 bigintThat's it, so raw storage wise it would be 32 bytes per entry, for a total of about 232 mb - obviously this is excluding db overhead, in my previous post I meant to say 200 megs, not 20. :) |
|
|
|
|
Stalled out select...