Sql-server – Query over text column takes ages to run

performancequery-performancesql server

I'm not used to querying text columns with a large amount of data as I've always used NVARCHAR(MAX) and VARCHAR(MAX) but this is not my Database that I'm querying.

myTable has 305,000 rows.

myDescriptionField is a text column and allows nulls & has loads of data in it.

When I run the following query, it returns a row instantly.

SELECT TOP 1 userid
FROM myTable 
WHERE ISNULL(userID,0) > 0 
AND TRIM(SUBSTRING(myDescriptionField,1,1)) is not null

However, when I add this line to the where clause, the query just sits for over a minute (not sure how long it actually takes to finish as I always end up stopping the execution).

AND myDescriptionField NOT LIKE ''

I thought it might be because I'm forced to do a not like as it's a text column and also that it may be where a lot of rows have blanks or null so I tried different combinations of the following :-

AND ISNULL(TRIM(SUBSTRING(myDescriptionField,1,1)),'') <> ''

None of this worked.

So now a quick explanation as to what I need to achieve :-

I just need to find out if at least one row has data in the column myDescriptionField and if so, I can move on to the next table.

Is there a quick way of returning just one row once it's hit one?

Best Answer

Can you use DATALENGTH?

SELECT TOP 1
    userID
FROM
    myTable 
WHERE
    DATALENGTH(myDescriptionField) >= 1

Related Solutions

Sql-server – SQL Server select columns which contain data

You can use this stored procedure to the trick You need to provide the table name you wish to query note that if you'll pass to procedure the @exec parameter = 1 it will execute the select query

  SET ANSI_NULLS ON
   GO
  SET QUOTED_IDENTIFIER ON
  GO
 CREATE PROCEDURE [dbo].[SP_SELECT_NON_NULL_COLUMNS] ( @tablename varchar    (100)=null, @exec int =0)
  AS BEGIN
  SET NOCOUNT ON
        IF @tablename IS NULL
              RAISERROR('CANT EXECUTE THE PROC, TABLE NAME IS MISSING',16 ,1)
                          ELSE
        BEGIN
              IF OBJECT_ID('tempdb..#table') IS NOT NULL DROP TABLE #table
              DECLARE @i VARCHAR (max)=''
              DECLARE @sentence VARCHAR (max)=''
              DECLARE @SELECT VARCHAR (max)
              DECLARE @LocalTableName VARCHAR(50) = '['+@tablename+']'
              CREATE TABLE  #table  (ColumnName VARCHAR (max))
              SELECT @i+=
              ' IF EXISTS ( SELECT TOP 1 '+column_name+' FROM '  +@LocalTableName+' WHERE ' +column_name+
                   ' '+'IS NOT NULL) INSERT INTO #table VALUES ('''+column_name+''');'
                    FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name=@tablename
                    INSERT INTO #table
                    EXEC (@i)
                     SELECT @sentence = @sentence+' '+columnname+' ,' FROM #table                  
             DROP TABLE #table                 
                    IF @exec=0
                          BEGIN
                                SELECT 'SELECT '+ LTRIM (left (@sentence,NULLIF(LEN (@sentence)-1,-1)))+
                                            +' FROM ' +@LocalTableName
                    END
                    ELSE
                          BEGIN 
                                SELECT @SELECT=  'SELECT '+ LTRIM (left (@sentence,NULLIF(LEN (@sentence)-1,-1)))+
                                                  +' FROM '+@LocalTableName
                        EXEC (@SELECT)
                    END
  END
  END

Sql-server – Line-breaks in Text column of CSV file

This is probably due to the fact that the text has Carriage Returns in it and that you are using the default row delimiter which is the Carriage Return.

This will be tough to fix since you can't know when a Carriage Return is part of a column or actually a new line. the file needs to be fixed manually.

Unless you know where the file came from... If there's a possibility for you to reprocess the file, then I would reprocess it, replacing all Carriage Returns found in columns by blanks ('').

I hope this helps.

Related Question