July 18, 2014 at 1:57 pm
I am looking at some queries that were written by someone else and I just want to ask if I'm accurate in saying all of the VARCHAR(MAX) on this Temp table are a performance issue.
Please correct me if I'm wrong with this thinking. I need Grants book already.
CREATE TABLE #UDSTMP
(
PatientProfileID INT,
PatientID VARCHAR(MAX),
FacilityID INT,
ListName VARCHAR(MAX),
[Last] VARCHAR(MAX),
Suffix VARCHAR(MAX),
[First] VARCHAR(MAX),
Middle VARCHAR(MAX),
Birthdate DATETIME,
Sex VARCHAR(10),
Homeless VARCHAR(MAX),
HomelessStatusID VARCHAR(MAX),
AgWrkrStatusID VARCHAR(MAX),
Ethnicity VARCHAR(MAX),
Ethnicity2 VARCHAR(MAX),
Translation BIT,
Description4a VARCHAR(MAX),
Description4c VARCHAR(MAX),
Description VARCHAR(MAX),
Code VARCHAR(MAX),
Name VARCHAR(MAX),
Voided INT,
CompanyID VARCHAR(MAX),
ProceduresID VARCHAR(MAX),
CPTProcedureCodeQualifierMId VARCHAR(MAX),
DepartmentMId VARCHAR(MAX),
CPTCode VARCHAR(MAX),
Language VARCHAR(MAX),
Zip VARCHAR(150),
visit DATETIME
)
***SQL born on date Spring 2013:-)
July 18, 2014 at 2:09 pm
It... could be.
What's the rest of the query doing? I've done things like that before to allow the import to occur no matter what prior to validations.
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
July 18, 2014 at 2:18 pm
This is for a report nothing else. They are just trying to create a result set with this temp table. There are a few UPDATEs to the temp table that follow after. I'm not sure if my boss is okay with me posting the whole thing or not. I'll have to check.
***SQL born on date Spring 2013:-)
July 18, 2014 at 2:23 pm
thomashohner (7/18/2014)
This is for a report nothing else. They are just trying to create a result set with this temp table. There are a few UPDATEs to the temp table that follow after. I'm not sure if my boss is okay with me posting the whole thing or not. I'll have to check.
Just so you know, usually for optimization assistance we need a lot of stuff, like the execution plan, underlying table schemas and indexing, etc. It's very difficult to obfuscate that much stuff consistently after a certain point. Without it, we can give you generic answers and things to go look at, but it must be understood that everything said is an educated guess, nothing more. It's very easy for us to be wrong repeatedly and look like morons.
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
July 18, 2014 at 2:24 pm
I should add all of the INSERT Information is under 50 characters max.
***SQL born on date Spring 2013:-)
July 18, 2014 at 2:27 pm
Evil Kraig F (7/18/2014)
thomashohner (7/18/2014)
This is for a report nothing else. They are just trying to create a result set with this temp table. There are a few UPDATEs to the temp table that follow after. I'm not sure if my boss is okay with me posting the whole thing or not. I'll have to check.Just so you know, usually for optimization assistance we need a lot of stuff, like the execution plan, underlying table schemas and indexing, etc. It's very difficult to obfuscate that much stuff consistently after a certain point. Without it, we can give you generic answers and things to go look at, but it must be understood that everything said is an educated guess, nothing more. It's very easy for us to be wrong repeatedly and look like morons.
I know Kraig, sorry about the limited info. I do know better. I just didn't know if this was more of a general don't do this type thing. I would love to provide more info which I know you guys need, however I'm new at my job and don't know how they would feel about posting the whole code vs snips.
PS. None of you guys ever look like morons! Just new guys like me
***SQL born on date Spring 2013:-)
July 18, 2014 at 2:32 pm
From a general perspective, and I know purists will disagree simply because of the memory grants (which will affect OTHER queries), no, it shouldn't cause you issues directly. Not unless there's something afterwards which needs to work against these as a where clause and wants to treat them as LOBs or something else oddball goes on under the hood.
They're just strings. The MAX just tells it to prepare for off-page storage. In this case, with everything under 50 chars, your rows should stay in-line so that shouldn't be a concern.
Now, is it best practice? Of course not. You're setting it up to include word documents in every column of every row. SQL Server has absolutely no chance in hell of properly optimizing based on data expectations. Indexing one of those for later use in the query would be a curious endeavour (I forget if you can even straight index a MAX column, I'll have to check again). So, no, that's a bad idea if you're coding it from scratch. Is it worth a code roll to change when inherited? Well, that depends on how bad current performance is.
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
July 18, 2014 at 2:38 pm
Evil Kraig F (7/18/2014)
From a general perspective, and I know purists will disagree simply because of the memory grants (which will affect OTHER queries), no, it shouldn't cause you issues directly. Not unless there's something afterwards which needs to work against these as a where clause and wants to treat them as LOBs or something else oddball goes on under the hood.They're just strings. The MAX just tells it to prepare for off-page storage. In this case, with everything under 50 chars, your rows should stay in-line so that shouldn't be a concern.
Now, is it best practice? Of course not. You're setting it up to include word documents in every column of every row. SQL Server has absolutely no chance in hell of properly optimizing based on data expectations. Indexing one of those for later use in the query would be a curious endeavour (I forget if you can even straight index a MAX column, I'll have to check again). So, no, that's a bad idea if you're coding it from scratch. Is it worth a code roll to change when inherited? Well, that depends on how bad current performance is.
I was told the query takes up to 45 minutes on some customer db's I have written the same result set that they are looking for before on another platform with the same size result set and had results in under 10 seconds. I know they are different platforms, however I know the report that they are trying to produce and no way on earth should it take 45 minutes. The rest of the query is extremely ugly. They do use those columns in the WHERE clause. Also they do several calculations in the WHERE clause. Also a bunch of this " IS NULL OR""
Thanks for your answer that's exactly what I was looking for!
***SQL born on date Spring 2013:-)
July 18, 2014 at 3:05 pm
thomashohner (7/18/2014)
Also they do several calculations in the WHERE clause. Also a bunch of this " IS NULL OR""Thanks for your answer that's exactly what I was looking for!
Glad to help. 🙂
Something else to look into, especially if you're used to a different platform, is how to deal with your IS NULL OR components. They're colloqually known as 'catch all queries', and you might want to browse this technique to deal with them by Gail Shaw:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply