IF NOT EXISTS (... - EXISTS TABLE : nested iteration. Table Scan.Forward scan.

Hi,

This is on Sybase but I'm guessing that the same situation would happen on SQL Server. (Please confirm if you know).

I'm looking at these new databases and I'm seeing code similar to this all over the place:

if not exists (select 1 from dbo.t1 where f1 = @.p1)
begin
select @.errno = @.errno | 1
end

There's a unique clustered in dex on t1.f1.

The execution plan shows this for this statement:

FROM TABLE
dbo.t1
EXISTS TABLE : nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.

It's not using my index!!!!!

It seems to be the case with EXISTS statements. Can anybody confirm?

I also hinted to use the index but it still didn't use it.

If the existence check really doesn't use the index, what's a good code alternative to this check?

I did this and it's working great but I wonder if there's a better alternative. I don't really like doing the SET ROWCOUNT 1 and then SET ROWCOUNT 0 thing. SELECT TOP 1 won't work on Sybase, :-(.

SET ROWCOUNT 1
SELECT @.cnt = (SELECT 1 FROM dbo.t1 (index ix01)
WHERE f1 = @.p1
)
SET ROWCOUNT 0

Appreciate your help.

Do you have the code in a SP? I don't know if Sybase does parameter sniffing or not. Since the search argument is parameterized, the optimizer has to guess the value of @.p1 and use it for generating a plan. That will explain why it picks a table scan. And index hints will not help in this case. You could rewrite the code like below:

if (select min(1) from dbo.t1 where f1 = @.p1) is null

begin

select @.errno = @.errno|1

end

If Sybase has something like OPTIMIZE FOR clause then you can provide hint about the value of @.p1.

|||

Hi, thank you for the reply.

Yes, the code is in an sp. Not sure why the parameter needs to be evaluated for the plan to be generated correctly. I would think that it only needs the table and columns that are being queried. ?

I implemented your suggestion but it still didn't use the index. Once I added a hint, it did use the index.

I'd like to find a solution that doesn't have any aggregates in it, ie min(1). I'm looking for top performance; this query is executed a lot. I'll keep digging.

Thank you,

Etienne

|||

The optimizer analyzes the predicates and search arguments to decide which indexes to use. Indexes have statistics and if a particular search argument value is not known at the time of compilation then a guess will be made. For example, consider following:

Index on table t, column i

Statistics in the index will contain information on how the values of column "i" are distributed in the table

Given query like: "select i from t where i > 1"

Query optimizer can use the statistics to infer how many values of i are greater than 1. Based on this information, a plan to either use index seek or index scan or table scan will be used. The choice depends on the cardinality of the returned result set based on the search arguments.

Now, for the same query if you do: "select i from t where i > @.i"

If query optimizer cannot sniff the value of the parameter/variable @.i then it has to make a guess on what the value of @.i could be at run-time. This guess is often conservative because based on the value of @.i the query can either return zero rows, one row, many rows or entire table.

Both approaches has their pros and cons. Even with parameter sniffing the plan choice depends on the supplied value and that plan will not be great for other potential search arguments. So as you can see it is not just the tables, columns and indexes that matter.

Anyway, it looks like you have to ask this question in a Sybase forum. I don't know about the details of the query optimizer in Sybase SQL Server. You can try various rewrites but I think the crux of the problem is that the value of the search argument is guessed by the optimizer and then the query plan is generated.

Identifying unused columns in multiple databases

Hi - we have a situation where we have 23 identical databases on
servers across the country. We know that there are fields within this
db that no one is using and would like to be able to identify all of
them so they can be removed across the board. (There are multiple
tables and columns) We would of course have to verify that none of the
23 offices are using a particular field before it's removed as the db's
need to stay identical. (data in each db varies).
Anyway - I came across this posting which sounds like what we're
looking for, but it specifically references varchar and nvarchar fields
- I can't figure out what to modify in it to make this work for any
type of field. Can anyone point me in the right direction?
This is the posting:
> How can I find all the varchar columns in a table?

> I'm looking for an existing empty or null column rather than add a column.
This will generate a script for the current database that will tell you
all
of the varchar or nvarchar columns, and what percentage of them are
"unused" -- either NULL or empty strings.
SELECT 'SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
+DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
+')'',''empty'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
+' WITH (NOLOCK) WHERE
LTRIM(RTRIM(COALESCE('+COLUMN_NAME+','''
')))=''''
UNION
SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
+DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
+')'',''total'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
+' WITH (NOLOCK)'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE LIKE '%varchar'
You can run this in Query Analyzer, and it will generate a script in
the
bottom pane. Copy to a new query window and let 'er rip. Should take
a
while on larger databases. I use WITH (NOLOCK) for this kind of task
to
avoid concurrency and blocking issues; if you need an
up-to-the-millisecond
guaranteed read on the whole table, you may opt to leave that out (but
I do
not recommend it on a production server).
Thank you in advance.Please do not take offense, but if are not familiar enough with SQL Server
and SQL in general to make the necessary change to this query, then you
really are not qualified to be working on the changes you are talking about.
I am not trying to be mean, but your question is much simpler than the task
you intend to eventually perform. If you need help at this point, you will
get disastrous results later on. It is best to get someone who is more of
an expert in such things to handle this project.
"Debbie" <deborah.young@.mail.va.gov> wrote in message
news:1140104286.351275.153030@.g43g2000cwa.googlegroups.com...
> Hi - we have a situation where we have 23 identical databases on
> servers across the country. We know that there are fields within this
> db that no one is using and would like to be able to identify all of
> them so they can be removed across the board. (There are multiple
> tables and columns) We would of course have to verify that none of the
> 23 offices are using a particular field before it's removed as the db's
> need to stay identical. (data in each db varies).
> Anyway - I came across this posting which sounds like what we're
> looking for, but it specifically references varchar and nvarchar fields
> - I can't figure out what to modify in it to make this work for any
> type of field. Can anyone point me in the right direction?
> This is the posting:
>
column.
>
> This will generate a script for the current database that will tell you
> all
> of the varchar or nvarchar columns, and what percentage of them are
> "unused" -- either NULL or empty strings.
> SELECT 'SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
> +')'',''empty'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
> +' WITH (NOLOCK) WHERE
> LTRIM(RTRIM(COALESCE('+COLUMN_NAME+','''
')))=''''
> UNION
> SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
> +')'',''total'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
> +' WITH (NOLOCK)'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE DATA_TYPE LIKE '%varchar'
>
> You can run this in Query Analyzer, and it will generate a script in
> the
> bottom pane. Copy to a new query window and let 'er rip. Should take
> a
> while on larger databases. I use WITH (NOLOCK) for this kind of task
> to
> avoid concurrency and blocking issues; if you need an
> up-to-the-millisecond
> guaranteed read on the whole table, you may opt to leave that out (but
> I do
> not recommend it on a production server).
>
> Thank you in advance.
>|||By "using the column", do you mean:
#1 if the column is populated with at least some data
#2 if there are any queries or procedures that actually reference the
column
The programming below seems to be querying for any columns that are all NULL
or zero length.
Also, a column may have been populated at some point in the past, but is
currently obsolete and not used. If you really need to audit which tables /
columns are in use by the application during normal usage, then you can
setup an object trace in SQL Server Profiler and let it run the background
for a complete business cycle.
http://msdn.microsoft.com/library/d...
ethowto15.asp
"Debbie" <deborah.young@.mail.va.gov> wrote in message
news:1140104286.351275.153030@.g43g2000cwa.googlegroups.com...
> Hi - we have a situation where we have 23 identical databases on
> servers across the country. We know that there are fields within this
> db that no one is using and would like to be able to identify all of
> them so they can be removed across the board. (There are multiple
> tables and columns) We would of course have to verify that none of the
> 23 offices are using a particular field before it's removed as the db's
> need to stay identical. (data in each db varies).
> Anyway - I came across this posting which sounds like what we're
> looking for, but it specifically references varchar and nvarchar fields
> - I can't figure out what to modify in it to make this work for any
> type of field. Can anyone point me in the right direction?
> This is the posting:
>
>
> This will generate a script for the current database that will tell you
> all
> of the varchar or nvarchar columns, and what percentage of them are
> "unused" -- either NULL or empty strings.
> SELECT 'SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
> +')'',''empty'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
> +' WITH (NOLOCK) WHERE
> LTRIM(RTRIM(COALESCE('+COLUMN_NAME+','''
')))=''''
> UNION
> SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
> +')'',''total'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
> +' WITH (NOLOCK)'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE DATA_TYPE LIKE '%varchar'
>
> You can run this in Query Analyzer, and it will generate a script in
> the
> bottom pane. Copy to a new query window and let 'er rip. Should take
> a
> while on larger databases. I use WITH (NOLOCK) for this kind of task
> to
> avoid concurrency and blocking issues; if you need an
> up-to-the-millisecond
> guaranteed read on the whole table, you may opt to leave that out (but
> I do
> not recommend it on a production server).
>
> Thank you in advance.
>|||I'd suggest running the trace (after it has been tuned to only trace relevan
t
events and return relevant data) for a month (or whatever the actual
turn-over time is).
After that you need to analyze the trace data, identifying individual unused
objects.
The following activities should not be done in live production.
Unused objects should first be renamed before actually being dropped -
rename one at a time to get a clear picture of their actual usage.
After the system has been running successfully for your specific turn-over
time with the renamed objects, you could consider dropping them - in a test
environment first, of course.
This is the bottom-to-top approach.
There is, however, another approach to this - analyze your actual business
requirements. If you think you have unused columns, maybe the entire data
model is wrong for you, and needs to be redesigned. This approach may even
yield better results.
ML
http://milambda.blogspot.com/