Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kourvoisier2k2
Starting Member
1 Post |
 Posted - 2011-01-08 : 10:50:33
|
| Hi all,Skill from 1 to 10 on SQL server i would give my self a 5 1/2 compared to the gurus i have seen.So i have a little more then the basic understanding.Here is my issue.1> I have a table that currently only has 1588 rows2> If i do a select * on this table it takes 59 seconds to finish the query3> I have a int as Primary Key on this table that gets incremented every new record inserted4> There are 3 fields that are of type xml that are fairly large5> I will never query the database based on those 3 fields6> There is a DateTime Field called CreateDate that i would love to do a range query off of. Example CreateDate > 12/10/2010 and CreateDate < 1/10/2011.What is the step by step instructions to streamline my select * query and streamline q query for a DateTime range?Do i need to creat Indexes on the DateTime field? non clustered?How do i go about this? If i dont need Indexes what do i need?Please help and thanks in advance |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-01-08 : 11:06:17
|
don't use Select *59 seconds is an absurdly long time to return only 1588 rows. Something else is going on with this query.Create an index on the datetime field if you intend to use it in your where clause, or you can create a nonclustered index which contains ONLY the columns returned by your select statement (called a covering index). you can do this be creating a nonclustered index on the PK, and INCLUDE the additional columns.Hard to say for certain, but 1588 records should be returned in about 0 seconds.
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-08 : 20:44:50
|
If the data includes TIME, as well as DATE, then you should useCreateDate >= 12/10/2010 and CreateDate < 1/10/2011 and you should only use string dates in the unambiguous format '20101210' and '20110110'I appreciate you were only presenting an example, so this is only in case you are not already doing this in your code.And as the others have said, list ONLY the columns you need in your SELECT statement. Quite apart from the fact that your XML columns are large, someone might add another wide column in the future - and any SELECT * will pull that, unnecessarily, from the database and across the network ... |
|
|
|
|
|
|
|
|
Indexing and Performance SQL 2005