November 3, 2010 at 6:05 pm
Hi,
I'm experiencing a very stange situation. I have a table [UI_Object_Details] with an NTEXT datatype column [txtObjectValue]. This column holds nvarchar data, datetime data, integer data and float data. This table is part of a vendor's system so changing the structure isn't an option for me.
I need just the float data which is indicated by another column [iObjectDataTypeId] (being equal to 3)
I execute the following query:
SELECT iObjectId
,iObjectKey
,cast(cast(txtObjectValue as nvarchar) as float)
FROM UI_Object_Details WITH (NOLOCK)
WHERE iObjectDataTypeId = 3
The above returns the error message "Error converting nvarchar to float"
So I attempted to make sure the data really is numeric (there are more than 10Million rows so line by line... well you get it)
SELECT iObjectId
,iObjectKey
,cast(CASE IsNumeric(cast(txtObjectValue as nvarchar)) WHEN 0 THEN null ELSE cast(txtObjectValue as nvarchar) END as float)
FROM UI_Object_Details WITH (NOLOCK)
WHERE iObjectDataTypeId = 3
Still returns the same error, "Error converting nvarchar to float"
Here's where it gets strange (at least to me)
If I add the txtObjectValue to the column list both queries work, which leads me to the conclusion that the data is fine, the problem is the Query Engine (more specifically the query optimizer).
SELECT iObjectId
,iObjectKey
,cast(cast(txtObjectValue as nvarchar) as float)
,txtObjectValue
FROM UI_Object_Details WITH (NOLOCK)
WHERE iObjectDataTypeId = 3
SELECT iObjectId
,iObjectKey
,cast(CASE IsNumeric(cast(txtObjectValue as nvarchar)) WHEN 0 THEN null ELSE cast(txtObjectValue as nvarchar) END as float)
,txtObjectValue
FROM UI_Object_Details WITH (NOLOCK)
WHERE iObjectDataTypeId = 3
The only notable difference is that by adding the column the execution plan moves a compute scalar to the end of the execution plan instead of the beginning.
Any advice on how to get the original query to work or why it would fail this way would be greatly appreciated?
Also worth noting, we have this vendor's system in 8 locations. Only 1 location has this problem and everything about these systems (OS Versions, SQL Server version, Vendor system versions, Table defintions, everything) is the same.
November 3, 2010 at 6:22 pm
The optimizer doesn't promise it's going to do anything in a specific order, just the fastest it can figure out.
The WHERE clause is a good start, but the issue here is that it might run AFTER it's pulled the data, and you're already converting.
Two general options here, both related.
First, Use a subquery in the FROM clause with the ISNUMERIC() clause of your choice (there are more specific ones that will ignore money symbols and the like that folks use), and an OPTION( FORCE ORDER) on it to make the subquery restrict the data, and then supply it to the outer query where you convert to float.
OR
Pull the data out of your original table and dump to a temp table with your ISNUMERIC() filter, then rejoin that data to the rest of your information, converting to FLOAT only in the second query.
The only way to make sure you don't shoot yourself with an overloaded field like this is to make sure you complete control the ordering, which means you can't let the optimizer get any decision in the order of where clause, then conversion.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 3, 2010 at 7:13 pm
You Rock! Thank you!
FORCE ORDER was exactly what was needed (still need to gauge performance impacts and such but the error is gone)
SELECT iObjectId
,iObjectKey
,cast(cast(txtObjectValue as nvarchar) as float)
FROM UI_Object_Details WITH (NOLOCK)
WHERE iObjectDataTypeId = 3
OPTION (FORCE ORDER)
OR in my case since this is a view
ALTER VIEW dbo.vwSurveyScores
AS
SELECT iObjectId
,iObjectKey
,cast(cast(txtObjectValue as nvarchar) as float)
FROM UI_Object_Details WITH (NOLOCK)
WHERE iObjectDataTypeId = 3
GO
SELECT * FROM vwSurveyScores OPTION (FORCE ORDER)
Another question that this raises though, FORCE ORDER can't be added to a view since OPTION clauses aren't allowed in the select_statement of a view definition. The option clause can be used when the view is referenced in a from clause (as seen above).
Outside of the FORCE ORDER, is there any other way to tell SQL Server's query engine to apply the WHERE clause before the convert function (a way that works in a view's definition)?
November 3, 2010 at 7:43 pm
SQL-Tucker (11/3/2010)
You Rock! Thank you!FORCE ORDER was exactly what was needed (still need to gauge performance impacts and such but the error is gone)
Hold up. Force Order is letting you get away with it for the moment, but it doesn't force the WHERE to happen first. It forces the join order to happen in the order declared.
From:http://msdn.microsoft.com/en-us/library/ms181714(SQL.90).aspx
FORCE ORDER
Specifies that the join order indicated by the query syntax is preserved during query optimization.
And this: http://msdn.microsoft.com/en-us/library/ms190237(SQL.90).aspx
Specifically:
When an SQL statement references a nonindexed view, the parser and query optimizer analyze the source of both the SQL statement and the view and then resolve them into a single execution plan. There is not one plan for the SQL statement and a separate plan for the view.
So, you're getting lucky at the moment. Here's what you want to do:
SELECT iObjectId
,iObjectKey
,cast(txtObjectValue as float)
FROM
(SELECT
iObjectID,
iObjectKey,
CAST( txtObjectvalue AS nVARCHAR(3000))
FROM
UI_Object_Details WITH (NOLOCK)
WHERE
iObjectDataTypeId = 3
) AS drv
OPTION (FORCE ORDER)
Notice the forced subquery. This almost completely fries the optimizer's ability to link the subquery up to other tables, but is the only way out of this mess.
You're not really going to be able to do this in a view unless you index the view, in which case the view stores its own leaf leves of the information. You leave the where clause in the view, and do the float conversion whenever you call the view.
Your true goal shouldn't be to make this work other than as a workaround until you can get some kind of staging load from the vendor's stuff into your own tables, where you manipulate this overloaded field into a series of unique fields with proper casting done through a series of updates via a proc. It's not perfect but that should be a goal in terms of getting this up to real speed.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 3, 2010 at 8:33 pm
Thank you, that is a HUGE help and gets me on the right track.
At this point I either need to rethink my approach and handle the conversion on the applications side or get the vendor involved (since indexed views prevent modification of the base tables).
November 3, 2010 at 10:35 pm
SQL-Tucker (11/3/2010)
Thank you, that is a HUGE help and gets me on the right track.At this point I either need to rethink my approach and handle the conversion on the applications side or get the vendor involved (since indexed views prevent modification of the base tables).
You're welcome, and my pleasure. I agree with the first choice as being better for the short term workaround, and the second as a long term goal to get them to learn to behave themselves with their databases. 😀
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply