Solved

Slow SQL Server View

Posted on 2014-03-18
9
360 Views
Last Modified: 2014-04-22
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
Comment
Question by:TechMommy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
9 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39938651
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
 
LVL 11

Author Comment

by:TechMommy
ID: 39938703
Good point about not using Between, but changing the SQL statement did not solve the performance problem.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39938722
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 11

Accepted Solution

by:
TechMommy earned 0 total points
ID: 39938753
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39938795
:) well that's one way I guess, at least you know the index is "fresh".
0
 
LVL 11

Author Comment

by:TechMommy
ID: 40005239
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40005368
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
 
LVL 11

Author Closing Comment

by:TechMommy
ID: 40014346
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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
convert null in sql server 12 58
Disable TLS1.0 on Win 2012 server 7 67
SQL Query Across Multiple Tables - Help 5 24
Please explain Equi-join 3 15
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question