March 29, 2012 at 12:27 pm
How can I get my query to get results and stop timing-out when I include a varchar(max) field in my where clause? All I need to do is check that the field IS NOT NULL and <> ''. Like this:
Select FieldA, FieldB
From TableA
Where FieldB IS NOT NULL
And FieldB <> ''
And FieldA In ('Value1', 'Value2', 'Value3')
The varchar(max) field (fieldb) cannot be indexed, so how do I accomplish this? The query returns data just fine as long as I leave it out of the where clause (even though it's in the select list), but as soon as I add it to the where clause...nothin.
FYI - FieldB contains xslfo (xml) data with a length of AT LEAST 20,000 characters, I don't care what values are there, I just want to know if it HAS a value...
March 29, 2012 at 12:28 pm
I have seen this on VERY large tables, you might need to add a statistic, that has seemed to resolve a lot of my issues.
CEWII
March 29, 2012 at 12:30 pm
Elliott Whitlow (3/29/2012)
I have seen this on VERY large tables, you might need to add a statistic, that has seemed to resolve a lot of my issues.CEWII
Yes Elliot, my table has 17305788 rows right now, so it's VERY large.
I'll look into trying that, thank you!
March 29, 2012 at 12:52 pm
Thats a big table, I've only got about 4M but about 1.4TB of space used. And this helped me a lot. Keep in mind they have to be updated periodically.. And it might make sense to add a calculated persisted column to indicate this condition.
As another point have you moved the BLOB data out of the same file as the rest of the table?
CEWII
March 29, 2012 at 1:00 pm
Elliott Whitlow (3/29/2012)
Thats a big table, I've only got about 4M but about 1.4TB of space used. And this helped me a lot. Keep in mind they have to be updated periodically.. And it might make sense to add a calculated persisted column to indicate this condition.As another point have you moved the BLOB data out of the same file as the rest of the table?
CEWII
I believe so, it was originally a text datatype field (still is in production) so it isn't stored in the same "row" as the rest of the data. Since text, ntext, and image datatypes are being deprecated (eventually), I'm converting now.
I'm curious, if all I'm after is whether or not the field has data or not, should I be using a filtered statistic with the where clause being "where Len(FieldB) > 0", or should I use a standard statistic?
Right now I'm adding a normal statistic and the script has been running for 17 minutes so far and still isn't done.
March 29, 2012 at 1:12 pm
I ended up not filtering myself. And it works well.
In SSMS if you right click on the table and get properties, if you go to storage under Filegroups if "Text filegroup" says primary the rows may not be in with the BLOB data but it shares the same file.
Because of its size I have gone further.
Data filegroup
BLOB filegroup
Index filegroup
CEWII
March 30, 2012 at 6:13 am
Wow, this took 3 hours 13 minutes to run. Should I be doing a percentage on the scan instead?
Create Statistics CorrespondenceLog_CorrespondenceFO On IARTS.dbo.CorrespondenceLog (CorrespondenceFO) With FULLSCAN
March 30, 2012 at 6:54 am
Try:
UPDATE STATISTICS TableA
SELECT *
FROM (
Select FieldA, FieldB
From TableA
Where FieldA In ('Value1', 'Value2', 'Value3')
) A
WHERE FieldB <> ''
Try sub-query separately to find what really takes a time, filtering for your values in FieldA or checking FieldB.
Please note: You don't need to check both for NOT NULL and <> '', just FieldB <> '' will be more than enough: NULL values will not match it!
March 30, 2012 at 7:52 am
Eugene Elutin (3/30/2012)
Try:
UPDATE STATISTICS TableA
SELECT *
FROM (
Select FieldA, FieldB
From TableA
Where FieldA In ('Value1', 'Value2', 'Value3')
) A
WHERE FieldB <> ''
Try sub-query separately to find what really takes a time, filtering for your values in FieldA or checking FieldB.
Please note: You don't need to check both for NOT NULL and <> '', just FieldB <> '' will be more than enough: NULL values will not match it!
Is the select apart of the update statistics statement? Or are they two separate transactions?
March 30, 2012 at 10:07 am
gregory.anderson (3/30/2012)
Wow, this took 3 hours 13 minutes to run. Should I be doing a percentage on the scan instead?
Create Statistics CorrespondenceLog_CorrespondenceFO On IARTS.dbo.CorrespondenceLog (CorrespondenceFO) With FULLSCAN
I would probably have done a fullscan too. but I guess it depends on what the mix is. If it is fairly high I would think a sample would be ok, but if these are the exceptions I am less sure.
CEWII
March 30, 2012 at 12:04 pm
Well, adding this line to my query:
And cl.CorrespondenceFO <> ''
My query returns data in 1 hour 26 minutes. I guess I never let it run that long BEFORE I added the statistics so I'm not sure if there's improvement or not. (I wasn't patient enough earlier to wait that long, but I got side-tracked this time and let the query run)
March 30, 2012 at 12:10 pm
gregory.anderson (3/30/2012)
Eugene Elutin (3/30/2012)
Try:
UPDATE STATISTICS TableA
SELECT *
FROM (
Select FieldA, FieldB
From TableA
Where FieldA In ('Value1', 'Value2', 'Value3')
) A
WHERE FieldB <> ''
Try sub-query separately to find what really takes a time, filtering for your values in FieldA or checking FieldB.
Please note: You don't need to check both for NOT NULL and <> '', just FieldB <> '' will be more than enough: NULL values will not match it!
Is the select apart of the update statistics statement? Or are they two separate transactions?
I tried your version and it ran in 1 hour 19 minutes. Removing the subquery it took 1 hour 26 minutes.
March 30, 2012 at 12:14 pm
One other thing that may be affecting all of this is there's a "Top" statement in this query and I'm ordering by the primary key...
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply