Performance -- count(*)

[gelangov]

I need to update the columns to blanks if it has Nulls; Most of the
time we do not have Nulls and to check that in a 3.2 million table it
takes 2 minutes and 45 seconds. (To do select count(*) from <table>
where <columnname> is Null) .

I just cut and pasted a part from my stored procedure:

EXEC ('IF (SELECT COUNT (*) FROM ' + @tblname + ' WHERE '
+@columnname + ' is Null ) >0
BEGIN
PRINT ''' + @columnName + ' has Null ''' +
' UPDATE ' + @tblname +
' SET ' + @columnName + ' = ''''
WHERE ' + @columnName + ' is Null
PRINT ''Updated ''
END'

Please let me know if there is a better way to do instead of doing
count (*).

Thanks in advance!

[Simon Hayes]

<ge******@hotma il.com> wrote in message
news:11******** **************@ l41g2000cwc.goo glegroups.com.. .

I need to update the columns to blanks if it has Nulls; Most of the
time we do not have Nulls and to check that in a 3.2 million table it
takes 2 minutes and 45 seconds. (To do select count(*) from <table>
where <columnname> is Null) .

I just cut and pasted a part from my stored procedure:

EXEC ('IF (SELECT COUNT (*) FROM ' + @tblname + ' WHERE '
+@columnname + ' is Null ) >0
BEGIN
PRINT ''' + @columnName + ' has Null ''' +
' UPDATE ' + @tblname +
' SET ' + @columnName + ' = ''''
WHERE ' + @columnName + ' is Null
PRINT ''Updated ''
END'

Please let me know if there is a better way to do instead of doing
count (*).

Thanks in advance!

If you only want to know that at least one row has NULL, then EXISTS should
be much faster:

if exists (
select *
from dbo.MyTable
where MyColumn is null
)
begin
-- do UPDATE here
end

But if you're updating all your NULLs to empty strings, you might want to
consider a default on the column:

create table dbo.MyTable (
....
MyColumn varchar(100) not null default '',
....
)

Or if NULL is correct in your data model, and you only want empty strings
for output purposes, then you could use COALESCE() to provide the empty
string when you query the data.

Simon

[Lee Tudor]

> Please let me know if there is a better way to do instead of doing

count (*).
can you not use defaults or an insert/update trigger? This is doing an awful
lot of work on the server.

Even with exists, if it fails you will have scanned the entire table. and if
it suceeds quickly youll scan it anyway with the update gaining no more than
a 0-50% reduction in runtime only if there are nulls found. You may be able
to speed up a specific example with an index but there is probably an
'elegant' solution rather than relying on brute force or dedicated indexes.

Mr Tea

<ge******@hotma il.com> wrote in message
news:11******** **************@ l41g2000cwc.goo glegroups.com.. .I need to update the columns to blanks if it has Nulls; Most of the
time we do not have Nulls and to check that in a 3.2 million table it
takes 2 minutes and 45 seconds. (To do select count(*) from <table>
where <columnname> is Null) .

I just cut and pasted a part from my stored procedure:

EXEC ('IF (SELECT COUNT (*) FROM ' + @tblname + ' WHERE '
+@columnname + ' is Null ) >0
BEGIN
PRINT ''' + @columnName + ' has Null ''' +
' UPDATE ' + @tblname +
' SET ' + @columnName + ' = ''''
WHERE ' + @columnName + ' is Null
PRINT ''Updated ''
END'

Please let me know if there is a better way to do instead of doing
count (*).

Thanks in advance!

[Erland Sommarskog]

(ge******@hotma il.com) writes:

I need to update the columns to blanks if it has Nulls; Most of the
time we do not have Nulls and to check that in a 3.2 million table it
takes 2 minutes and 45 seconds. (To do select count(*) from <table>
where <columnname> is Null) .

I just cut and pasted a part from my stored procedure:

EXEC ('IF (SELECT COUNT (*) FROM ' + @tblname + ' WHERE '
+@columnname + ' is Null ) >0
BEGIN
PRINT ''' + @columnName + ' has Null ''' +
' UPDATE ' + @tblname +
' SET ' + @columnName + ' = ''''
WHERE ' + @columnName + ' is Null
PRINT ''Updated ''
END'

Please let me know if there is a better way to do instead of doing
count (*).

Since this is likely to cause a table scan, the best is just to submit
the UPDATE statement. If you want to know whether the table was updated
or not, you can catch @@rowcount when you are done. (And this can be
done outside the dynamic SQL.)

Then again, why not just say:

ALTER TABLE tbl ALTER COLUMN col <datatype> NOT NULL DEFAULT ''


--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[gelangov]

Thank you everyone for all your replies.
Is there a performance difference for inserts when we make it Not Null
default ''VS when it is Null? We will be inserting a lot of data

Thanks
Erland Sommarskog wrote:

(ge******@hotma il.com) writes:

I need to update the columns to blanks if it has Nulls; Most of the
time we do not have Nulls and to check that in a 3.2 million table it takes 2 minutes and 45 seconds. (To do select count(*) from <table>
where <columnname> is Null) .

I just cut and pasted a part from my stored procedure:

EXEC ('IF (SELECT COUNT (*) FROM ' + @tblname + ' WHERE '
+@columnname + ' is Null ) >0
BEGIN
PRINT ''' + @columnName + ' has Null ''' +
' UPDATE ' + @tblname +
' SET ' + @columnName + ' = ''''
WHERE ' + @columnName + ' is Null
PRINT ''Updated ''
END'

Please let me know if there is a better way to do instead of doing
count (*).
Since this is likely to cause a table scan, the best is just to

submit the UPDATE statement. If you want to know whether the table was updated or not, you can catch @@rowcount when you are done. (And this can be
done outside the dynamic SQL.)

Then again, why not just say:

ALTER TABLE tbl ALTER COLUMN col <datatype> NOT NULL DEFAULT ''


--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

(ge******@hotma il.com) writes:

Thank you everyone for all your replies.
Is there a performance difference for inserts when we make it Not Null
default ''VS when it is Null? We will be inserting a lot of data

I don't remember exactly how NULL varchar values are stored. Possibly the ''
take up two bytes more than the NULL.

In any case, my opinion for all considerations like these is that you
should foremost look at this from a functional and logical point of view.
If all your values are known, but can be known to be empty use ''. If you
can have unknown values, permit NULL.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp