performance question
-
you are never going to go past that error - you are converting from a nvarchar(255) to an int - to avoid the issue you will need to change the datatype of design.attValue to int (and remove the cast as not needed at that time) - which I do not believe you can do.
what you can try is reducing the size of those variables - and change them varchar unless the content of some rows do require nvarchar
- Bruin wrote:
Still can't get pass this error:
Type conversion in expression (CONVERT(int,[d2].[attValue],0)) may affect "CardinalityEstimate" in query plan choice, The query memory grant detected "ExcessiveGrant", which may impact the reliability. Grant size: Initial 8360 KB, Final 8360 KB, Used 40 KB.
You've ignored at least four separate requests to post the actual execution plan. I can think of the following reasons for not doing so:
- You do not know how to do that.
- You have so little work to do that you would rather spend a week solving a problem which could otherwise be solved in a day.
- You enjoy getting attention from unpaid volunteers in an online forum.
Which one is it?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
not sure how to upload the execution plan.
-
Easiest way is probably to use Brent Ozar's PasteThePlan. Instructions here. Once you've done that, post the link here.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Thanks will do... sorry for the delays in getting this info...
What about comment: from --frederico_fonseca
"
you are never going to go past that error - you are converting from a nvarchar(255) to an int - to avoid the issue you will need to change the datatype of design.attValue to int (and remove the cast as not needed at that time) - which I do not believe you can do."
- Bruin wrote:
Thanks will do... sorry for the delays in getting this info...
What about comment: from --frederico_fonseca " you are never going to go past that error - you are converting from a nvarchar(255) to an int - to avoid the issue you will need to change the datatype of design.attValue to int (and remove the cast as not needed at that time) - which I do not believe you can do."
You are actually looking at 2 different warnings (not errors)
- Type conversion in expression (CONVERT(int,[d2].[attValue],0)) may affect "CardinalityEstimate" in query plan choice - This is related to Frederico's comment
- The query memory grant detected "ExcessiveGrant", which may impact the reliability. Grant size: Initial 8360 KB, Final 8360 KB, Used 40 KB. - This is related to the table definitions. SQL needs memory to process a query. To figure out how much memory to request, SQL uses teh meta-data and not the actual data. It calculates the size of 1 record based on the field definitions, and multiplies that by the estimated number rows that it will get back (based on statistics). In this case, it estimated that it would require 8360KB (or more) of RAM, but due to the actual number of records as well as the empty space in the actual records, it only required 40KB of RAM. This is a waste of memory, which could have been allocated to another query.
-
Is changing the datatype on design.attValue to int the only way around the issue?
- Bruin wrote:
Still can't get pass this error:
Type conversion in expression (CONVERT(int,[d2].[attValue],0)) may affect "CardinalityEstimate" in query plan choice, The query memory grant detected "ExcessiveGrant", which may impact the reliability. Grant size: Initial 8360 KB, Final 8360 KB, Used 40 KB.
Isn't the attValue column a generic catch all for virtually anything like you might find in any place and so it has to be able to hold any almost any "attribute values"? If so, then you shouldn't change it to an INT. Only change it to an INT if all "attribute values" will be integers for sure. And, yeah... if you want the warnings to go away, then the datatypes have to match. Which place is it easier for them to be made to match, is the real question at the point.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You are correct in assessment of the field def....a catch all... so how in CODE based upon what I supplied can it be fixed?
Thanks.
- Bruin wrote:
You are correct in assessment of the field def....a catch all... so how in CODE based upon what I supplied can it be fixed?
Thanks.
The only way to get rid of the (CONVERT(int,[d2].[attValue],0)) may affect "CardinalityEstimate" warning, is to change the data type before SQL compiles its plan.
Below are 3 different approaches, which MAY work. Since I do not have the actual tables or data to test, you will need to test each one to see if they do remove the warning. BUT, I would test for performance at the same time.
/*** Try using CROSS APPLY instead of INNER JOIN ***/INSERT INTO bom
SELECT DISTINCT
d1.design
, d1.version
, BOMitem = d1.attvalue
, d1.designAlternative
, att.BOMdesignAlternative
FROM design AS d1
CROSS APPLY ( SELECT BOMdesignAlternative = CAST(d2.attValue AS int)
FROM design AS d2
WHERE d1.design = d2.design
AND d1.version = d2.version
AND d1.designAlternative = d2.designAlternative
AND d1.layerCode = d2.layerCode
AND d1.position = d2.position
AND d1.operation = d2.operation
AND d2.attribute = N'BOMdesignAlternative'
) AS att
WHERE d1.attvalue IS NOT NULL
AND d1.attribute = N'BOMitem'
AND d1.design = N'CORE/FILLED'
AND d1.designAlternative = 0
AND d1.version = 0;/*** Try using a CTE to pre-select the Alternbative attributes and cast to int ***/WITH cteAttrib AS (
SELECT TOP ( 9223372036854775807 ) /* Force SQL to evaluate the CTE before doing the join */ d2.design
, d2.version
, d2.designAlternative
, d2.layerCode
, d2.position
, d2.operation
, BOMdesignAlternative = CAST(d2.attValue AS int)
FROM design AS d2
WHERE d2.design = N'CORE/FILLED'
AND d2.version = 0
AND d2.designAlternative = 0
AND d2.attribute = N'BOMdesignAlternative'
GROUP BY d2.design, d2.version, d2.designAlternative, d2.layerCode d2.position, d2.operation, d2.attValue
)
INSERT INTO bom
SELECT DISTINCT
d1.design
, d1.version
, BOMitem = d1.attvalue
, d1.designAlternative
, att.BOMdesignAlternative
FROM design AS d1
INNER JOIN cteAttrib AS att
ON d1.design = att.design
AND d1.version = att.version
AND d1.designAlternative = att.designAlternative
AND d1.layerCode = att.layerCode
AND d1.position = att.position
AND d1.operation = att.operation
WHERE d1.attvalue IS NOT NULL
AND d1.attribute = N'BOMitem'
AND d1.design = N'CORE/FILLED'
AND d1.designAlternative = 0
AND d1.version = 0;/*** Try using a #TempTable to pre-select the Alternbative attributes and cast to int ***/
SELECT d2.design
, d2.version
, d2.designAlternative
, d2.layerCode
, d2.position
, d2.operation
, BOMdesignAlternative = CAST(d2.attValue AS int)
INTO #Attributes
FROM design AS d2
WHERE d2.design = N'CORE/FILLED'
AND d2.version = 0
AND d2.designAlternative = 0
AND d2.attribute = N'BOMdesignAlternative'
GROUP BY d2.design, d2.version, d2.designAlternative, d2.layerCode d2.position, d2.operation;
INSERT INTO bom
SELECT DISTINCT
d1.design
, d1.version
, BOMitem = d1.attvalue
, d1.designAlternative
, att.BOMdesignAlternative
FROM design AS d1
INNER JOIN #Attributes AS att
ON d1.design = att.design
AND d1.version = att.version
AND d1.designAlternative = att.designAlternative
AND d1.layerCode = att.layerCode
AND d1.position = att.position
AND d1.operation = att.operation
WHERE d1.attvalue IS NOT NULL
AND d1.attribute = N'BOMitem'
AND d1.design = N'CORE/FILLED'
AND d1.designAlternative = 0
AND d1.version = 0; -
Thanks very much !!!
I'll test the 3 scenario's...
-
1st) Still have the Warning in Query Plan
2nd) Column 'design.attValue' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
3rd) Incorrect syntax near the keyword 'INTO'.
-
I say again - you will never remove that warning from your plan unless the underlying type is the same as your destination column
stop wasting time on things that can't be solved, and likely not the issue here either.
and again you failed to supply with the actual execution plan asked by everyone else.
for the other 2 errors - if you already have enough knowledge to look at performance and explain plans then you should also have enough knowledge to understand that code supplied on a forum by a volunteer does not always work and it may need YOU to fix the minor syntax errors done.
If nothing else the the code from Des should be taken as an example of what you may try to improve your own code.
-
I'm not sure what else in the Plan would be helpful other than what I provided...
So just to be clear without changing the Table field Def this is no way to solve in code?
If that is the case I'll close this Topic ...
Thanks for all who have given advice and examples...
- Bruin wrote:
I'm not sure what else in the Plan would be helpful other than what I provided...
Ok, so you totally understand all aspects of an execution plan, and understand how the various parts and pieces fit together? Then why are you asking us if you know all the things that matter and the things that do not?
Throughout this thread, a lot of pretty high level people have asked for more information. You have never provided it.
When you go to say, your car mechanic, and he asks you questions about your vehicle, you answer his questions, correct? With us, you've given us nothing.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 15 posts - 46 through 60 (of 60 total)
You must be logged in to reply to this topic. Login to reply