May 21, 2012 at 11:53 am
Hi,
I'm running into a weird problem. I'm trying to do a simple select statement, which looks a little like this:
SELECT *
FROM MyView
where ({fn convert(MyDateField, SQL_CHAR)} = '2012-05-17')
This returns no results, even though when I query this without the WHERE clause, I see that plenty of records match this date.
On the other hand, if I use this query:
SELECT TOP 1000 *
FROM MyView
where ({fn convert(MyDateField, SQL_CHAR)} = '2012-05-17')
I see about 800 results that meet my criteria. Any idea what's going on here?
Thanks.
May 21, 2012 at 11:58 am
because you are putting a function on thee column, SQL has to convert the WHOLE table to give you results.
with a TOP, it can shortcut and convert fewer values to get you the results.
change the format of the query to this style instead, so there is no function on the column name:
SELECT *
FROM MyView
where MyDateField >='2012-05-17' AND < '2012-05-18'
Lowell
May 21, 2012 at 12:49 pm
Lowell (5/21/2012)
because you are putting a function on thee column, SQL has to convert the WHOLE table to give you results.with a TOP, it can shortcut and convert fewer values to get you the results.
Just curious, why would the query stop at 800 rows if the query had 'top 1000'?
May 21, 2012 at 12:51 pm
How many rows of data actually meet the criteia of the query?
May 21, 2012 at 1:10 pm
tarr94 (5/21/2012)
I'm running into a weird problem. I'm trying to do a simple select statement, which looks a little like this:SELECT *
FROM MyView
where ({fn convert(MyDateField, SQL_CHAR)} = '2012-05-17')
Don't do this.
You are converting a perfectly good date/time type field to character form and then performing a string comparison on the result. The converted string will generally not match the format 'yyyy-mm-dd'. On my system, this ODBC scalar function converts the date/time to CHAR(8000) using style zero, format 'mon dd yyyy hh:miAM/PM'.
Perhaps your system is configured differently, in any case the difference between the TOP and non-TOP plans will be in the execution plan. But please don't waste any time on this, lose the ODBC scalar function and write the query as Lowell suggests.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 21, 2012 at 1:17 pm
Lowell (5/21/2012)
--------------------------------------------------------------------------------
because you are putting a function on thee column, SQL has to convert the WHOLE table to give you results.
with a TOP, it can shortcut and convert fewer values to get you the results.
He mentioned that he did not get any rows without top 1000 ( if i have read it correctly) that means he got empty resultset.So it sounds that sql is returning the data when top 1000 is specified otherwise it is not returing the data even though there are 800 rows matching the criteria.
Yes, without top 1000 it is possible that getting the result might take time depending on what kind of plan was generated.But this all depends on the view definition..
tarr94
Can you confirm whether your query got executed and you got empty resulteset when you ran the query without any top ?
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 21, 2012 at 1:20 pm
Thanks for the responses. I figured the function was probably causing this, but unfortunately, this is a query that is getting executed in our .NET code, and we'd rather not have to change our code which had been working fine up until we changed the data source recently.
The answer Lynn's question, the number of records returned by the TOP 1000 was the actual number of records that I expected (800 or so). When I change it to TOP 800, I actually end up with fewer records.
May 21, 2012 at 1:21 pm
Gullimeel,
The query definitely finished executing; it simply returned zero results.
May 21, 2012 at 1:23 pm
Incidentally, the view being queried casts the date as a DATE. It was originally a DATETIME. The query getting run by the .NET code then proceeds to use that function. Not efficient, but shouldn't it still work?
May 21, 2012 at 1:34 pm
Can you post the plan for top 1000 and without it? It looks strange that it returns the data with top and not returning without it?
Maybe some data type conversion issue?If possible the definition of view as well?
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 21, 2012 at 2:15 pm
tarr94 (5/21/2012)
Not efficient, but shouldn't it still work?
Not efficient? Converting every date in every row to 8000 bytes of CHAR formatted as 'mon dd yyyy hh:miAM/PM' and expecting it to match a *string* literal formatted as 'yyyy-mm-dd'? No, it shouldn't still work. The fact that it used to (on SQL Server?) is probably chance or a bug. Chances are, the execution plans will show SQL Server misplacing an implicit data type conversions in the TOP plan. I wouldn't want to support a system that relies on such things.
Unless the guy that wrote the .NET code left, and you don't have source code, I can't think of any good reason to even attempt debugging this. Fix the .NET code and pay better attention to data type issues in future; you'll be rewarded with better performance and a lack of these types of problems.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 21, 2012 at 2:36 pm
and of course, your function causes any indexes defined on that column not to be used as efficient as possible, if used at all.
No doubt, change the code or buy a bigger server to cope with your little load.:doze:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 21, 2012 at 2:55 pm
ALZDBA (5/21/2012)
and of course, your function causes any indexes defined on that column not to be used as efficient as possible, if used at all.No doubt, change the code or buy a bigger server to cope with your little load.:doze:
He said both queries finished, but with different results, he wasn't even asking about performance, he was asking about the discrepency in results. I'm still wondering how it happens although Pauls suggestion that the implicit or explicit cast/convert(s) is/are getting rearranged by different plans certainly gives a clue about what might be going on.
May 21, 2012 at 11:07 pm
SQL Kiwi (5/21/2012)
Unless the guy that wrote the .NET code left, and you don't have source code, I can't think of any good reason to even attempt debugging this. Fix the .NET code and pay better attention to data type issues in future; you'll be rewarded with better performance and a lack of these types of problems.
Developers hiding SQL code in their .Net applications are the bane of SQL Servers everywhere. :w00t: The practice should be banned.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 21, 2012 at 11:23 pm
Best would be if you just run the query without convert function on the sql server through ssms for both top 1000 and without it. Then see what output put it is giving you.
SELECT *
FROM MyView
where MyDateField= '2012-05-17'
SELECT TOP 1000 *
FROM MyView
where MyDateField= '2012-05-17'
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply