• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 373
  • Last Modified:

Slow SQL Server View

I have a SQL Server view that times out every time that I run it. I am executing the following statement to retrieve data from the view:

SELECT * FROM vwMyView 
WHERE MyDateField Between '1/1/2014' And '1/31/2014' 
And MyField1 = '1-0' And MyField2 >=1000000 AND MyField3 IS NULL

Open in new window


When I execute this statement without the date criteria it returns records immediately. I have an index on the date field, so I don't understand why it is timing out.

Any ideas?
0
TechMommy
Asked:
TechMommy
  • 4
  • 3
1 Solution
 
PortletPaulCommented:
I assume MyDateField is datetime/smalldatetime or similar ad not (n)varchar.

See if this makes any difference:
SELECT * FROM vwMyView 
WHERE MyDateField Between '20140101' And '20140131' 
And MyField1 = '1-0' And MyField2 >=1000000 AND MyField3 IS NULL

Open in new window

Please also be aware that using that query could miss the vast majority of data for the 31st of January.

Imaging that a record has a MyDateFiled value of 2014-01-31 17:16:15 +00005
Right now such a record would be missed completely.
A more reliable way to filter for a date range is to avoid using between. This will be certain to include all of Jan 31.:
SELECT * FROM vwMyView 
WHERE MyDateField >= '20140101' And MyDateField < '20140201' 
And MyField1 = '1-0' And MyField2 >=1000000 AND MyField3 IS NULL

Open in new window

For more on this topic please see: "Beware of Between"
0
 
TechMommyAuthor Commented:
Good point about not using Between, but changing the SQL statement did not solve the performance problem.
0
 
PortletPaulCommented:
Are you able to provide the execution plan .sqlplan file of the actual query? (it an be attached)

and can you confirm the data type of MyDateField please
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
TechMommyAuthor Commented:
Well, I figured it out....I decided to try deleting the index on MyDateField, saving,and then adding the index back again. Now the SQL statement executes in less than 1 second!
0
 
PortletPaulCommented:
:) well that's one way I guess, at least you know the index is "fresh".
0
 
TechMommyAuthor Commented:
I solved this myself, so I'm not sure how to indicate that here. How do I designate that no one gets any points on this since I came up with the solution?
0
 
Anthony PerkinsCommented:
I decided to try deleting the index on MyDateField, saving,and then adding the index back again.
You do realize there are better ways to rebuild an index than dropping and recreating it, right?  I mean I trust you are not planning to do this on a scheduled basis.  That is why there are jobs that rebuild indexes when the fragmentation is high.

How do I designate that no one gets any points on this since I came up with the solution?
I answered my own question. How do I close the question?
0
 
TechMommyAuthor Commented:
None of the suggestions involved deleting or rebuilding the index. It was only after I eliminated the other possible solutions that I decided to pursue my own solution. That was when I discovered the problem with the index.
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now