Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Question about VARCHAR(MAX) Expand / Collapse
Author
Message
Posted Friday, July 18, 2014 1:57 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:27 AM
Points: 179, Visits: 1,059
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
Post #1594220
Posted Friday, July 18, 2014 2:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:04 AM
Points: 5,383, Visits: 7,457
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.



- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1594221
Posted Friday, July 18, 2014 2:18 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:27 AM
Points: 179, Visits: 1,059
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
Post #1594226
Posted Friday, July 18, 2014 2:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:04 AM
Points: 5,383, Visits: 7,457
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.



- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1594227
Posted Friday, July 18, 2014 2:24 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:27 AM
Points: 179, Visits: 1,059
I should add all of the INSERT Information is under 50 characters max.



***SQL born on date Spring 2013
Post #1594228
Posted Friday, July 18, 2014 2:27 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:27 AM
Points: 179, Visits: 1,059
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
Post #1594231
Posted Friday, July 18, 2014 2:32 PM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:04 AM
Points: 5,383, Visits: 7,457
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.



- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1594237
Posted Friday, July 18, 2014 2:38 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:27 AM
Points: 179, Visits: 1,059
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
Post #1594240
Posted Friday, July 18, 2014 3:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:04 AM
Points: 5,383, Visits: 7,457
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/



- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1594244
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse