Erratic Performance on 5,000,000 records

Hi
I have a mssql 2000 database with 5 mil records in a table and the
performance seems to change from one day to the next from 5 sec result set
returns to 30sec. If i change the index sort order or sometimes move the
position of one of the lines in the "where" statement then it improves to 5
sec only to change the next day or two to 30 sec again.
Other variables like loading on the server, db etc are constant - it;s a
test system still so no users hitting DB.
Simple things like changing the position of the "null" condition below
improved things yesterday but today it's slow (3 sec to 30sec).
eg:
WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
worked well yesterday after being the other way around but now does not.
(This is for a param that could be passed thru as null or with a value)
Thanks
Mike
Hi,
According to information you provide i can only say that
WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
is not SARGABLE. It will perform Index Scan instead of Index Seek. If you're
index will grow or get fragmented you will have more costs.
Did you run this code in Stored Procedure?
I recommend you to use Dynamic SQL or if this is a SP then use wrapper sp
according to parameter passed to sp .
Many of the programmers using this kind of code but this type of usage is
easy to write for programmers but bad for performance.
Hope this helps.
"Mike C" wrote:

> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to 5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike
>
>
|||Try using isnull(CL.DEPTCODE,'') = isnull(@.DEPTCODE,'')
"Mike C" wrote:

> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to 5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike
>
>
|||Again this will perform index scan ..
(Assuming you have an index on DEPTCODE column)
"Saket" wrote:
[vbcol=seagreen]
> Try using isnull(CL.DEPTCODE,'') = isnull(@.DEPTCODE,'')
> "Mike C" wrote:
|||If column DeptCode does not contain NULLs, then you can use the
following statement. It assumes DeptCode is of datatype varchar. If is
of a different data type, then post back.
WHERE CL.DeptCode LIKE COALESCE(@.DeptCode,'%')
Hope this helps,
Gert-Jan
Mike C wrote:
> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to 5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike