August 29, 2024 at 4:02 pm
select * from
dbo.resolutions r
where R.documentid = 828222222
and DATALENGTH(r.resolution_text) > 0
resolution_text Text column is taking More time while Querying,
Need to Replace DATALENGTH, when i replace datalength condition
as "r.resolution_text is not null" the above query is returning data
August 29, 2024 at 4:11 pm
Is this a blog post, or do you have a question?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 29, 2024 at 4:27 pm
I want resolution for above Question, Need to replace datalength and query should not return data ,
that is the expectation Please share me the alternative Query
August 29, 2024 at 4:55 pm
Just guessing - because you didn't actually provide any information on the issue. It seems you have a column that IS NOT NULL - but has some type of 'data' in it.
You can't use IS NOT NULL - because that still returns the rows where the 'data' exists but has no length.
If so - have you tried to simply check to see if the column is greater than an empty string (assuming this column is not TEXT/IMAGE deprecated data types).
r.resolution_text > ''
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 29, 2024 at 4:56 pm
select * from dbo.resolutions r where R.documentid = 828222222 and DATALENGTH(r.resolution_text) > 0
resolution_text Text column is taking More time while Querying,
Need to Replace DATALENGTH, when i replace datalength condition
as "r.resolution_text is not null" the above query is returning data
A value that contains only one or more spaces will still return a data length will return counts over 0. So that brings to question, what are you trying to avoid?
If you only want to return rows with values that contain characters that are not blank, then the following code will do and it eliminates a formula.
EDIT: I made the bad assumption in this 2022 forum that the resolution_text column as not one of the deprecated-in-2005 datatypes of TEXT or NTEXT. The following will NOT work on those old datatypes.
SELECT *
FROM dbo.resolutions r
WHERE r.documentid = 828222222
AND r.resolution_text) > ' ' -- '' would also work here in T_SQL
;
Still, if the resolution_text column is a BLOB (in or out of row), things could be a little slow. It's the nature of BLOBs.
The other thing to look at is... find out if you have an index on the document_id column. If you don't, it's always going to be slow no matter what because the system will need to do a scan.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 29, 2024 at 4:56 pm
Duplicate post removed.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 29, 2024 at 5:00 pm
if checking for NULL is faster, why not use that combined with if the value is an empty string?
WHERE r.resolution_text IS NOT NULL and r.resolution_text != ''
Same net result without a function in the WHERE clause. Mind you, if you don't have a covering index, it will probably still be slow.
But the second part of your reply where you say "query should not return data", the easiest way to have a query return no data is to put in a clause in the WHERE that is ALWAYS false like:
WHERE 1 = 0
doing that will make sure you always get 0 rows back.
An alternate solution to this as well - don't filter that out in SQL but filter it out at the application layer. Pull the data into Excel (for example) and filter it that way. IF my query is going to run too long when I do complex filtering (I don't see what you have as complex filtering, I suspect the problem is more with data volume), I push the filtering logic to the application layer. Filter it in SSRS, SSAS, SSIS, Excel, Power BI, Tableu, whatever tool you are using to consume the data.
Now, something to point out, a query being slow MAY be due to the query or MAY be due to the data volume and slow network (for example - pulling 1 TB of data across a 30 Mbps link is going to be slow no matter how you do it) or MAY be slow due to other queries (your query may be blocked when you go to run it and you MAY need to wait for another query to complete before yours will run). It COULD be the slow run is due to blocking and the lock got released and your query ran. Then you made changes and re-ran it with the IS NOT NULL instead and nobody had a lock on it so your query was fast.
Tuning a query requires a bit more than what you posted. Actual execution plans are a good start, but including the IO and TIME are beneficial to see if the slowness is due to your query or the system. For example, if the IO is small and TIME is small, then blocking is likely the cause of the slowness and you are possibly tuning the wrong query OR you may need to do dirty reads (if that is acceptable) or read the committed data only. If TIME is high, IO is high, or both are high, then your query is the problem. But with what you provided my best guess is that the issue is that it cannot use an index and is doing a scan across the whole table AND running a function on each row to get the data length of the column.
ANOTHER approach you could use is to use a nested SELECT where you pull in all the data for the one document ID in the nested select and pull the data length of that column in as a calculated column in the nested part of the select and the top level pulls in all data where that value is > 0. MAY get a performance boost as the calculation now only runs on the rows that are in that document ID, or it may be slow still if there are still a lot of rows to run through.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 29, 2024 at 5:05 pm
Msg 402, Level 16, State 1, Line 11
The data types text and varchar are incompatible in the greater than operator.
I am getting above error when i am trying below Query.
SELECT *
FROM dbo.resolutions r
WHERE r.documentid = 828222222
AND r.resolution_text) > ' '
August 29, 2024 at 5:13 pm
yeah, that's what I'd expect... why'd you use the grater than operator on a string?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 29, 2024 at 5:22 pm
Msg 402, Level 16, State 1, Line 11 The data types text and varchar are incompatible in the greater than operator.
I am getting above error when i am trying below Query.
SELECT * FROM dbo.resolutions r WHERE r.documentid = 828222222 AND r.resolution_text) > ' '
"Look eye. ALWAYS look eye"! One error there is that you have included an erroneous parenthesis.
The other error is correct. I didn't know you were using a TEXT datatype, which has been deprecated since 2005. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 29, 2024 at 5:23 pm
yeah, that's what I'd expect... why'd you use the grater than operator on a string?
Because I told him to. 😉 It works just fine on MAX datatypes. It doesn't work on TEXT datatypes and I didn't expect anyone to be using TEXT almost 2 decades after they've been deprecated, especially in a 2022 forum. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 29, 2024 at 5:33 pm
Duplicate post removed.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 29, 2024 at 7:46 pm
Mr. Brian Gale wrote:yeah, that's what I'd expect... why'd you use the grater than operator on a string?
Because I told him to. 😉 It works just fine on MAX datatypes. It doesn't work on TEXT datatypes and I didn't expect anyone to be using TEXT almost 2 decades after they've been deprecated, especially in a 2022 forum. 😉
Ah, I missed your reply where you suggested that. I've not been a fan of comparing strings with > or <. I always use =, LIKE, or !=, but that's mostly because I can't conceptually figure out what > or < on strings means with certainty. Like is 'a' > 'A'? I suspect that the answer is "it depends" on the collation that is set. Same applies to 'a'='A'... guess that's what I get for only reading part of the post 😛
I do agree that TEXT and NTEXT should not be used, especially in 2022... One day Microsoft will remove them and someone's production critical code will fail and that DBA will have a long night ahead of them...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 29, 2024 at 7:54 pm
Actually "<" is NOT a safe way to check for not space. CR and LF are both not "<" a space.
select case when char(10) > space(1) then 'LF ' else '' END +
case when char(13) > space(1) then 'CR' else '' end
There might be some fair overhead to getting the current length of a text column. If you really have to have that, add a column to the table to store the LEN or DATALENGTH and use a trigger(s) to maintain it. Then at least you're just doing a comparison on a numeric column.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 30, 2024 at 9:13 am
There might be some fair overhead to getting the current length of a text column. If you really have to have that, add a column to the table to store the LEN or DATALENGTH and use a trigger(s) to maintain it.
Would you use a trigger in preference to a COMPUTED PERSISTED column for this? If so, can you explain why? Thanks.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply