October 30, 2011 at 8:34 am
I have a table with 200,000,000 records. It contains a date field. When I query the table using a "New Query" window, all the records within the parameters are returned in about 2 minutes. Yet the infomation bar at the bottom contiues to indicate that the query is running for another 9 minutes (total time of query execution is 11 minutes).
The query is:
Select TimePoint, daPrice - rtPrice, versifyID
from dbo.ECoImportedData
where TimePoint >= '01/01/2008 00:00:00' and TimePoint <= '12/31/2011 00:00:00'
The field TimePoint has been indexed. At least I think it has been indexed. The table definition can be found at :
http://www.easyware.com/computer/table_definition.txt
This was obtained by Right clicking on the table name and sending the "Create To" output to a file. There isn't any indexing statements in this Create To output. Has the index actually been created?
If you look the the Index screen which is accessed from the Design table window, the index does appear there. See:
http://www.easyware.com/computer/index.png
Getting back to the question, why does the query continue to run even after all the rows have been returned. To me it seems like the query is causing a full table scan instead of using the index shown in the .png image. However, since the create index code was not generated by the Create To command, is there really an index?
Could some please take a minute to explain?
Thanks,
pat
Background: This SELECT statement will eventually feed a INSERT statement. Because of the number of records in the table, I want to create a smaller Temp table for reporting/manipulation.
I do have a date Lookup table because many of the manipulations require date comparisons. I did not include the Date Lookup table in the SELECT statement because I want to understand why the query continues to run.
October 30, 2011 at 8:47 am
mpdillon (10/30/2011)
This was obtained by Right clicking on the table name and sending the "Create To" output to a file. There isn't any indexing statements in this Create To output. Has the index actually been created?
Create to file (or to anywhere else for that matter) just scripts the create table and any table-level constraints, nothing more.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2011 at 9:59 am
Out of the 200,000,000 rows in the table, how many match your search predicate? Also, can you please generate and post the actual execution plan? (To get the actual plan, choose "Include Actual Execution Plan" from the "Query" menu in SSMS, or just hit Ctrl+M, before running the query).
Looking at the execution plan will tell you whether or not your index is actually being used. I suspect that it is not; since it is a non-clustered index that doesn't cover the query, the optimizer may have determined that it is cheaper to scan the table instead of seeking the index and doing lookups against the table for each row to get the values of rtPrice and daPrice. The data may have been loaded into the table in date order, but without a clustered index on TimePoint the optimizer has no way to know or guarantee that, and so keeps on scanning to the end of the table even though there are no more matching rows to be found.
In any case, taking a look at the actual execution plan will likely clear things up in a hurry.
October 30, 2011 at 11:10 am
I did as you suggested.
The results appeared on the Execution plan tab. But they exented beyond the visible window and there were not any scroll bars. Nor could I find a way to select, copy and paste the results. Instead I had to hover my mouse, write a few words in Notepad, then hover again, etc. until I copied every thing. There must be a better way. What is it?
Here are the results.
Missing Index (Impact 85.694): Create NonClustered Index [<Name of Missing Index, sysname>]
On dbo.ECoImportedData ([TimePoint])
include ([rtPrice], [daPrice], [VersifyID])
I assume this means I should create a new Nonclustered Index on TimePoint. I should also include the fields rtPrice, daPrice and VesifyID. Then my query will use the value in the index for the calculation defined in my Select, rather than using the acutal table.
Did I get that right?
This record set returns 6,000,000 records. I need to work with much larger sets. On concern I have is that the Sever will run out of memory if I issue a Create NonCluster Index command. The server has 16 GB of memory. Is my concern valid or will SQL manage the memory?
Thanks,
pat
October 30, 2011 at 12:00 pm
mpdillon (10/30/2011)
The results appeared on the Execution plan tab. But they exented beyond the visible window and there were not any scroll bars. Nor could I find a way to select, copy and paste the results. Instead I had to hover my mouse, write a few words in Notepad, then hover again, etc. until I copied every thing. There must be a better way. What is it?
Right-click -> Save as.
Attach the resultant file to your post.
I assume this means I should create a new Nonclustered Index on TimePoint. I should also include the fields rtPrice, daPrice and VesifyID. Then my query will use the value in the index for the calculation defined in my Select, rather than using the acutal table.
No. Missing indexes is just a place to start, nothing more.
Edit your existing index (which I assume is on TimePoint) and include those columns.
This record set returns 6,000,000 records. I need to work with much larger sets. On concern I have is that the Sever will run out of memory if I issue a Create NonCluster Index command. The server has 16 GB of memory. Is my concern valid or will SQL manage the memory?
A couple weeks ago I created several indexes on a 138 million row table on my laptop, which has 1GB of memory allocated to SQL Server.
6 million rows is nothing these days.
p.s. If that query returns 6 million rows, what are you going to do with that data? If it's for report, no one is ever going to read that much data, so what's the reason for querying it?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2011 at 12:33 pm
I thought I tried right clicking and it did not work. But after your post I tried again and it works fine.
I did create a NonClustered index. Now the query stops when all the records are returned. So that was the jist of my post.
My next problem is the server is running out of memory. But I am starting a seperate Post to deal with that.
Thank you for your help.
pat
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
