I have a stored procedure that takes 3 parameters.  They can be null and
the the logic is basically :


create procedure a
@cola nvarchar.., @colb nvarchar.., @colc nvarchar..

select cola, colb, colc, cold (etc) from tablea
inner join tableb (on primary key)
where (@cola=cola or @cola is null)
and   (@colb=colb or @colb is null)
and   (@colc=colc or @colc is null)

Tablea and table b contain 22K rows.  Row length is less then 300 bytes  on
each table.  This is a dual processor with 4 gig of memory set aside for
sql server.

Any help will be appreciated!

I forgot to put the catcher in the body of the explanation.  TempDB is
getting a 64K write every .2 seconds.  Why?

Do you have an index by those columns?

Try:

select
    cola, colb, colc, cold (etc)
from
    tablea
    inner join
    tableb
    (on primary key)
where
    cola like coalesce(@cola, '%')
    and colb like coalesce(@colb, '%')
    and colc like coalesce(@colc, '%')


AMB

Show quoteHide quote

Hi

In Query Analyzer, Enable the Show execution plan, (CONTROL - K or Query >
Show Execution Plan)

Run the query and check is there are any spool operations. SQL Server will
put data in TempDb if the data set exceeds the computers RAM available or it
is a faster way to process the query..

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: m***@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

Show quoteHide quote

Major Performace problem
execure store procedure from Query Analizer
sqlservr.exe using high I/O
Enterprise SQL move to Standard
restore question on sql2000
repair_rebuild and the transaction log
Move existing table to another file or filegroup
SQL Agent Autostart
Can Triggers Help Me out
replication error - agent is suspect