Optimizing store proc

I have the following store proc and was wondering if I can optimized it by using a SELECT CASE instead of all those IF? I tried but don't know how to write it.

Thanks

set ANSI_NULLSONset QUOTED_IDENTIFIERONgoALTER PROCEDURE [dbo].[Get_Cl_SearchMultiColumn]( @.strSearchTermColumnNamenvarchar (50),@.strSearchTermSearchTermnvarchar (200) )as if (@.strSearchTermColumnName ='Monitor')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1,Monitor2FROM Cl_SystemsWHERE contains(Monitor1,@.strSearchTerm)orcontains(Monitor2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='MonitorSerial')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1Serial,Monitor2SerialFROM Cl_SystemsWHERE contains(Monitor1Serial,@.strSearchTerm)orcontains(Monitor2Serial,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Microscope')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Microscope1,Microscope2FROM Cl_SystemsWHERE contains(Microscope1,@.strSearchTerm)orcontains(Microscope2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='SerialMicroscope')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,SerialMicroscope1,SerialMicroscope2FROM Cl_SystemsWHERE contains(SerialMicroscope1,@.strSearchTerm)orcontains(SerialMicroscope2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Controller')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Controller1,Controller2FROM Cl_SystemsWHERE contains(Controller1,@.strSearchTerm)orcontains(Controller2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='ControllerFirmware')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Cont1Firmware,Cont2FirmwareFROM Cl_SystemsWHERE contains(Cont1Firmware,@.strSearchTerm)orcontains(Cont2Firmware,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='SerialController')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,SerialController1,SerialController2FROM Cl_SystemsWHERE contains(SerialController1,@.strSearchTerm)orcontains(SerialController2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Joystick')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Joystick1,Joystick2FROM Cl_SystemsWHERE contains(Joystick1,@.strSearchTerm)orcontains(Joystick2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='JoystickFirmware')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Joy1Firmware,Joy2FirmwareFROM Cl_SystemsWHERE contains(Joy1Firmware,@.strSearchTerm)orcontains(Joy2Firmware,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='SerialJoystick')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,SerialJoystick1,SerialJoystick2FROM Cl_SystemsWHERE contains(SerialJoystick1,@.strSearchTerm)orcontains(SerialJoystick2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Camera')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Camera1,Camera2,Camera3,Camera4FROM Cl_SystemsWHERE contains(Camera1,@.strSearchTerm)orcontains(Camera2,@.strSearchTerm)orcontains(Camera3,@.strSearchTerm)orcontains(Camera4,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='CameraSerial')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Camera1Serial,Camera2Serial,Camera3Serial,Camera4SerialFROM Cl_SystemsWHERE contains(Camera1Serial,@.strSearchTerm)orcontains(Camera2Serial,@.strSearchTerm)orcontains(Camera3Serial,@.strSearchTerm)orcontains(Camera4Serial,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='ZMotor')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,ZMotor1,ZMotor2,ZMotor3FROM Cl_SystemsWHERE contains(ZMotor1,@.strSearchTerm)orcontains(ZMotor2,@.strSearchTerm)orcontains(ZMotor3,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Stage')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Stage1,Stage2,Stage3FROM Cl_SystemsWHERE contains(Stage1,@.strSearchTerm)orcontains(Stage2,@.strSearchTerm)orcontains(Stage3,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Lens')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Lens1,Lens2,Lens3FROM Cl_SystemsWHERE contains(Lens1,@.strSearchTerm)orcontains(Lens2,@.strSearchTerm)orcontains(Lens3,@.strSearchTerm)return 0end

I don't know why you need a WHERE clause with CONTAINS predicate but try the link below for CASE statement for an expert. Hope this helps.

http://www.craigsmullins.com/ssu_0899.htm

|||

Well it seems that I am trying to use the CASE in a different situation then what explain everywhere.

I would like to use it one of the parameters sent to my store proc but I am not sure if it is possible and how to write it

Something like:

Select Case @.strSearchTermColumnName

Case 'Monitor'
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1,Monitor2
FROM Cl_Systems
WHERE contains(Monitor1,@.strSearchTerm) or contains(Monitor2,@.strSearchTerm)
return 0
end

case 'MonitorSerial'
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1Serial,Monitor2Serial
FROM Cl_Systems
WHERE contains(Monitor1Serial,@.strSearchTerm) or contains(Monitor2Serial,@.strSearchTerm)
return 0
end
...

End Select

|||

Oups I made a few mistakes in my last statement, I should have been:

Well it seems that I am trying to use the CASE in a different situation than what explained everywhere.

I would like to use it on one of the parameters sent to my store proc but I am not sure if it is possible and how to write it

Something like:

Select Case @.strSearchTerm

Case 'Monitor'
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1,Monitor2
FROM Cl_Systems
WHERE contains(Monitor1,@.strSearchTerm) or contains(Monitor2,@.strSearchTerm)
return 0
end

case 'MonitorSerial'
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1Serial,Monitor2Serial
FROM Cl_Systems
WHERE contains(Monitor1Serial,@.strSearchTerm) or contains(Monitor2Serial,@.strSearchTerm)
return 0
end
...

End Select

|||

What I am saying is I don't know if you can use the CONTAINS predicate with CASE statement. The links below one is a full text expert he knows more about fulltext and the other two are the two versions of CONTAINS. Hope this helps.

http://spaces.msn.com/jtkane/

http://msdn2.microsoft.com/en-US/library/ms189760.aspx

http://msdn2.microsoft.com/en-US/library/ms187787.aspx

sql

Optimizing SQL Query

Hello,
I have to run a script which will delete records from 10 tables
based on a certain condition. Instead of having to run the condition 10
times I thought running it once would have better performance and did
something like this
CREATE PROCEDURE testScript AS
set nocount on
declare @.script_table table (row_id int identity(1,1), sid int)
declare @.max int, @.now int, @.result varchar(100)
insert into @.script_table
select sid from Alpha where lname like 'DOWN' and (fname like 'Tom' or
fname like 'Seinfeld')
select @.max = max(row_id) from @.script_table
select @.now=2, @.result=convert(varchar(6), sid) from @.script_table
where row_id=1
while @.max >=@.now
Begin
select @.result = @.result + ',' + convert(varchar(6), sid) from
@.script_table where row_id=@.now
set @.now=@.now + 1;
End
select * from Beta where convert(varchar(5), sid) in ( @.result )
if @.@.error <> 0
BEGIN
ROLLBACK Transaction
RAISERROR ( 'E', 2, 127)
END
...
...
...
but when I run this I dont get any values. Instead when I run this
query I get the output rows
select * from Beta where convert(varchar(5), sid) in (select sid from
Alpha where lname like 'DOWN' and (fname like 'Tom' or fname like
'Seinfeld'))
since @.result has the output from Alpha with a comma delimiter I was
under the impression that this should give me the result but instead I
dont get any rows. Is this because @.result is a varchar? Am I doing
something wrong here? Any help would be great..
Thanks
Khris> Is this because @.result is a varchar?
Yes. You can, however, parse a delimited string and have values returned in
a table, that you can use in your query.
My favourite solution is this one (by Dejan Sarka):
http://solidqualitylearning.com/blo.../10/22/200.aspx
ML
http://milambda.blogspot.com/|||Thanks ML, I will try that out|||Thanks ML I will try that out
Khris

Optimizer not using the good index

Please,
Using SQL 7.0
The optimizer is not using the good index for this query
It's using the clustered index X2 (on WOT_DATE instead of
index X1 on WO_REF.
I did reindex but it's still the same. It looks to
happen every monday after i run sp_updatestats
What should i do or i should not do
SELECT 0
FROM WOTransit (readuncommitted)
INNER JOIN WO (readuncommitted) On
WoTransit.Wo_ref=Wo.Wo_ref
WHERE WO.Cmd_No=814352
AND ISNULL(WOTransit.Charge_ref,0) <> 0
thanks
Don
Don wrote:
> Please,
> Using SQL 7.0
> The optimizer is not using the good index for this query
> It's using the clustered index X2 (on WOT_DATE instead of
> index X1 on WO_REF.
> I did reindex but it's still the same. It looks to
> happen every monday after i run sp_updatestats
> What should i do or i should not do
> SELECT 0
> FROM WOTransit (readuncommitted)
> INNER JOIN WO (readuncommitted) On
> WoTransit.Wo_ref=Wo.Wo_ref
> WHERE WO.Cmd_No=814352
> AND ISNULL(WOTransit.Charge_ref,0) <> 0
> thanks
> Don
When you say "Using the clustered index", I assume you mean you are
seeing a clustered index scan operation instead of in Index Seek on
whatever index you have on the Wo_ref column. Correct? If the Wo_ref
index is there, but is not being used, can you let us know how many rows
are in the table and how many rows the query returns.
David Gugick
Imceda Software
www.imceda.com
|||My answer to your question is YES
And there is 4160164 rows in WO and 2666718 rows in
WOTRANSIT
The query returns 11 rows.
I appreciate your help
Don
[vbcol=seagreen]
>--Original Message--
>Don wrote:
of
>When you say "Using the clustered index", I assume you
mean you are
>seeing a clustered index scan operation instead of in
Index Seek on
>whatever index you have on the Wo_ref column. Correct? If
the Wo_ref
>index is there, but is not being used, can you let us
know how many rows
>are in the table and how many rows the query returns.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>
|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:2ca001c4b179$6be119d0$a501280a@.phx.gbl...
> My answer to your question is YES
> And there is 4160164 rows in WO and 2666718 rows in
> WOTRANSIT
> The query returns 11 rows.
How many rows in WO with Cmd_No=814352? If the number is small then you
might try an index on Cmd_No.
David
|||I allready have an index on wo.cmd_no
Here, there seems to be a problem using the good index on
the join clause (wo.wo_ref = wotransit.wo_ref)
Please some help, the production teem cannot work because
it so slow
thanks !
Don

>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2ca001c4b179$6be119d0$a501280a@.phx.gbl...
>
>How many rows in WO with Cmd_No=814352? If the number is
small then you
>might try an index on Cmd_No.
>David
>
>.
>
|||As David Browne asked, how many rows are there in WO with Cmd_No=814352?
Assuming that you are 100% sure that using the nonclustered index is better,
I would guess that the statistics are inaccurate.
To test this, use Query Analyzer and look at the estimated execution plan
for the query. Open another window and get the actual execution plan.
Compare the estimated number of rows against the actual number of rows
retrieved for each process. Somewhere in there, there should be a wide
divergence, which should indicate inaccurate statistics. Run the UPDATE
STATISTICS command for the relevant index with the FULLSCAN option. Might
not work always since SQL Server only maintains distribution stats for 200
steps and you have millions of rows in the table, but worth a shot.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:2c8801c4b170$5415b9e0$a501280a@.phx.gbl...
> Please,
> Using SQL 7.0
> The optimizer is not using the good index for this query
> It's using the clustered index X2 (on WOT_DATE instead of
> index X1 on WO_REF.
> I did reindex but it's still the same. It looks to
> happen every monday after i run sp_updatestats
> What should i do or i should not do
> SELECT 0
> FROM WOTransit (readuncommitted)
> INNER JOIN WO (readuncommitted) On
> WoTransit.Wo_ref=Wo.Wo_ref
> WHERE WO.Cmd_No=814352
> AND ISNULL(WOTransit.Charge_ref,0) <> 0
> thanks
> Don
|||<anonymous@.discussions.microsoft.com> wrote in message
news:021801c4b17c$6596cfe0$a301280a@.phx.gbl...
>I allready have an index on wo.cmd_no
> Here, there seems to be a problem using the good index on
> the join clause (wo.wo_ref = wotransit.wo_ref)
> Please some help, the production teem cannot work because
> it so slow
>
Well you better post the DDL. You haven't even told us which table the
index is on.
And what is the execution plan?
David
|||Here's a shot in the dark ...
Have you tried setting the ISOLATION LEVEL prior to the query and implement
and index hint forcing the index you want to be used ?
Like this ...
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
BEGIN TRANSACTION
SELECT 0
FROM WOTransit WITH (INDEX = idx1)
INNER JOIN WO WITH (INDEX = idx2) On
WoTransit.Wo_ref=Wo.Wo_ref
WHERE WO.Cmd_No=814352
AND ISNULL(WOTransit.Charge_ref,0) <> 0
COMMIT TRANSACTION
Hope this helps... Let me know ?
Anthony E. Castro
"Peter Yeoh" wrote:

> As David Browne asked, how many rows are there in WO with Cmd_No=814352?
> Assuming that you are 100% sure that using the nonclustered index is better,
> I would guess that the statistics are inaccurate.
> To test this, use Query Analyzer and look at the estimated execution plan
> for the query. Open another window and get the actual execution plan.
> Compare the estimated number of rows against the actual number of rows
> retrieved for each process. Somewhere in there, there should be a wide
> divergence, which should indicate inaccurate statistics. Run the UPDATE
> STATISTICS command for the relevant index with the FULLSCAN option. Might
> not work always since SQL Server only maintains distribution stats for 200
> steps and you have millions of rows in the table, but worth a shot.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:2c8801c4b170$5415b9e0$a501280a@.phx.gbl...
>
>
|||"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:%23WnlNxZsEHA.2316@.TK2MSFTNGP12.phx.gbl...
> As David Browne asked, how many rows are there in WO with Cmd_No=814352?
> Assuming that you are 100% sure that using the nonclustered index is
better,
> I would guess that the statistics are inaccurate.
> To test this, use Query Analyzer and look at the estimated execution plan
> for the query. Open another window and get the actual execution plan.
Actually, the actual execution plan shows the estmated row count, too. Very
handy. What would be even better is if large discrepancies were reported
similarly to missing statistics.
|||Thx for pointing that out. I should have mentioned that I was referring to
the graphical plan. Don, if you're still with us, you can view the actual
and estimated row counts side-by-side (well, almost) using the textual
execution plan. Just run SET STATISTICS PROFILE ON before running the query
in QA.
Mark, you just gave me an idea to add to our Query Analyzer Tool tool.
Thanks.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!

Optimizer not using the good index

Please,
Using SQL 7.0
The optimizer is not using the good index for this query
It's using the clustered index X2 (on WOT_DATE instead of
index X1 on WO_REF.
I did reindex but it's still the same. It looks to
happen every monday after i run sp_updatestats
What should i do or i should not do
SELECT 0
FROM WOTransit (readuncommitted)
INNER JOIN WO (readuncommitted) On
WoTransit.Wo_ref=Wo.Wo_ref
WHERE WO.Cmd_No=814352
AND ISNULL(WOTransit.Charge_ref,0) <> 0
thanks
DonDon wrote:
> Please,
> Using SQL 7.0
> The optimizer is not using the good index for this query
> It's using the clustered index X2 (on WOT_DATE instead of
> index X1 on WO_REF.
> I did reindex but it's still the same. It looks to
> happen every monday after i run sp_updatestats
> What should i do or i should not do
> SELECT 0
> FROM WOTransit (readuncommitted)
> INNER JOIN WO (readuncommitted) On
> WoTransit.Wo_ref=Wo.Wo_ref
> WHERE WO.Cmd_No=814352
> AND ISNULL(WOTransit.Charge_ref,0) <> 0
> thanks
> Don
When you say "Using the clustered index", I assume you mean you are
seeing a clustered index scan operation instead of in Index Seek on
whatever index you have on the Wo_ref column. Correct? If the Wo_ref
index is there, but is not being used, can you let us know how many rows
are in the table and how many rows the query returns.
--
David Gugick
Imceda Software
www.imceda.com|||My answer to your question is YES
And there is 4160164 rows in WO and 2666718 rows in
WOTRANSIT
The query returns 11 rows.
I appreciate your help
Don
>--Original Message--
>Don wrote:
>> Please,
>> Using SQL 7.0
>> The optimizer is not using the good index for this query
>> It's using the clustered index X2 (on WOT_DATE instead
of
>> index X1 on WO_REF.
>> I did reindex but it's still the same. It looks to
>> happen every monday after i run sp_updatestats
>> What should i do or i should not do
>> SELECT 0
>> FROM WOTransit (readuncommitted)
>> INNER JOIN WO (readuncommitted) On
>> WoTransit.Wo_ref=Wo.Wo_ref
>> WHERE WO.Cmd_No=814352
>> AND ISNULL(WOTransit.Charge_ref,0) <> 0
>> thanks
>> Don
>When you say "Using the clustered index", I assume you
mean you are
>seeing a clustered index scan operation instead of in
Index Seek on
>whatever index you have on the Wo_ref column. Correct? If
the Wo_ref
>index is there, but is not being used, can you let us
know how many rows
>are in the table and how many rows the query returns.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:2ca001c4b179$6be119d0$a501280a@.phx.gbl...
> My answer to your question is YES
> And there is 4160164 rows in WO and 2666718 rows in
> WOTRANSIT
> The query returns 11 rows.
How many rows in WO with Cmd_No=814352? If the number is small then you
might try an index on Cmd_No.
David|||I allready have an index on wo.cmd_no
Here, there seems to be a problem using the good index on
the join clause (wo.wo_ref = wotransit.wo_ref)
Please some help, the production teem cannot work because
it so slow
thanks !
Don
>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2ca001c4b179$6be119d0$a501280a@.phx.gbl...
>> My answer to your question is YES
>> And there is 4160164 rows in WO and 2666718 rows in
>> WOTRANSIT
>> The query returns 11 rows.
>
>How many rows in WO with Cmd_No=814352? If the number is
small then you
>might try an index on Cmd_No.
>David
>
>.
>|||As David Browne asked, how many rows are there in WO with Cmd_No=814352?
Assuming that you are 100% sure that using the nonclustered index is better,
I would guess that the statistics are inaccurate.
To test this, use Query Analyzer and look at the estimated execution plan
for the query. Open another window and get the actual execution plan.
Compare the estimated number of rows against the actual number of rows
retrieved for each process. Somewhere in there, there should be a wide
divergence, which should indicate inaccurate statistics. Run the UPDATE
STATISTICS command for the relevant index with the FULLSCAN option. Might
not work always since SQL Server only maintains distribution stats for 200
steps and you have millions of rows in the table, but worth a shot.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:2c8801c4b170$5415b9e0$a501280a@.phx.gbl...
> Please,
> Using SQL 7.0
> The optimizer is not using the good index for this query
> It's using the clustered index X2 (on WOT_DATE instead of
> index X1 on WO_REF.
> I did reindex but it's still the same. It looks to
> happen every monday after i run sp_updatestats
> What should i do or i should not do
> SELECT 0
> FROM WOTransit (readuncommitted)
> INNER JOIN WO (readuncommitted) On
> WoTransit.Wo_ref=Wo.Wo_ref
> WHERE WO.Cmd_No=814352
> AND ISNULL(WOTransit.Charge_ref,0) <> 0
> thanks
> Don|||<anonymous@.discussions.microsoft.com> wrote in message
news:021801c4b17c$6596cfe0$a301280a@.phx.gbl...
>I allready have an index on wo.cmd_no
> Here, there seems to be a problem using the good index on
> the join clause (wo.wo_ref = wotransit.wo_ref)
> Please some help, the production teem cannot work because
> it so slow
>
Well you better post the DDL. You haven't even told us which table the
index is on.
And what is the execution plan?
David|||Here's a shot in the dark ...
Have you tried setting the ISOLATION LEVEL prior to the query and implement
and index hint forcing the index you want to be used ?
Like this ...
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
BEGIN TRANSACTION
SELECT 0
FROM WOTransit WITH (INDEX = idx1)
INNER JOIN WO WITH (INDEX = idx2) On
WoTransit.Wo_ref=Wo.Wo_ref
WHERE WO.Cmd_No=814352
AND ISNULL(WOTransit.Charge_ref,0) <> 0
COMMIT TRANSACTION
Hope this helps... Let me know ?
Anthony E. Castro
"Peter Yeoh" wrote:
> As David Browne asked, how many rows are there in WO with Cmd_No=814352?
> Assuming that you are 100% sure that using the nonclustered index is better,
> I would guess that the statistics are inaccurate.
> To test this, use Query Analyzer and look at the estimated execution plan
> for the query. Open another window and get the actual execution plan.
> Compare the estimated number of rows against the actual number of rows
> retrieved for each process. Somewhere in there, there should be a wide
> divergence, which should indicate inaccurate statistics. Run the UPDATE
> STATISTICS command for the relevant index with the FULLSCAN option. Might
> not work always since SQL Server only maintains distribution stats for 200
> steps and you have millions of rows in the table, but worth a shot.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:2c8801c4b170$5415b9e0$a501280a@.phx.gbl...
> > Please,
> >
> > Using SQL 7.0
> >
> > The optimizer is not using the good index for this query
> >
> > It's using the clustered index X2 (on WOT_DATE instead of
> > index X1 on WO_REF.
> >
> > I did reindex but it's still the same. It looks to
> > happen every monday after i run sp_updatestats
> >
> > What should i do or i should not do
> >
> > SELECT 0
> > FROM WOTransit (readuncommitted)
> > INNER JOIN WO (readuncommitted) On
> > WoTransit.Wo_ref=Wo.Wo_ref
> > WHERE WO.Cmd_No=814352
> > AND ISNULL(WOTransit.Charge_ref,0) <> 0
> >
> > thanks
> >
> > Don
>
>|||"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:%23WnlNxZsEHA.2316@.TK2MSFTNGP12.phx.gbl...
> As David Browne asked, how many rows are there in WO with Cmd_No=814352?
> Assuming that you are 100% sure that using the nonclustered index is
better,
> I would guess that the statistics are inaccurate.
> To test this, use Query Analyzer and look at the estimated execution plan
> for the query. Open another window and get the actual execution plan.
Actually, the actual execution plan shows the estmated row count, too. Very
handy. What would be even better is if large discrepancies were reported
similarly to missing statistics.|||Thx for pointing that out. I should have mentioned that I was referring to
the graphical plan. Don, if you're still with us, you can view the actual
and estimated row counts side-by-side (well, almost) using the textual
execution plan. Just run SET STATISTICS PROFILE ON before running the query
in QA.
Mark, you just gave me an idea to add to our Query Analyzer Tool tool.
Thanks.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!|||"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:%23WWTPZmsEHA.1604@.TK2MSFTNGP15.phx.gbl...
> I should have mentioned that I was referring to
> the graphical plan. Don, if you're still with us, you can view the actual
> and estimated row counts side-by-side (well, almost) using the textual
> execution plan. Just run SET STATISTICS PROFILE ON before running the
query
> in QA.
The graphical plan shows both stats, too. Hover over a step and you'll see a
line for Row Count, then another line (at the bottom) for Estimated Row
Count.
> Mark, you just gave me an idea to add to our Query Analyzer Tool tool.
Sounds great!|||On Wed, 13 Oct 2004 15:02:27 -0700, "Don"
<anonymous@.discussions.microsoft.com> wrote:
>Using SQL 7.0
>The optimizer is not using the good index for this query
>It's using the clustered index X2 (on WOT_DATE instead of
>index X1 on WO_REF.
>I did reindex but it's still the same. It looks to
>happen every monday after i run sp_updatestats
>What should i do or i should not do
>SELECT 0
>FROM WOTransit (readuncommitted)
> INNER JOIN WO (readuncommitted) On
>WoTransit.Wo_ref=Wo.Wo_ref
>WHERE WO.Cmd_No=814352
>AND ISNULL(WOTransit.Charge_ref,0) <> 0
The ISNULL() function may be confusing the optimizer.
Or the data.
If there are actually 100k rows in the join with WO.Cmd_No=814352 but
they do have a null or 0 WOTransit.Charge_ref and so are not selected,
then maybe the optimizer doesn't know that. Is there an index on
Charge_ref? I'm not sure what the threshhold is for a scan (so I
guessed 100k), but once the optimizer wants to scan, it would just use
the clustered index, whatever it is. Actually, it's using no index.
I wonder what happens if you recode around the ISNULL?
J.|||Hi every one
Sorry for the delay,
I found that there were statitics (auto created) on table
WOTANSIT
I dropped them and the execution plan went back to normal.
I didn't go further to find a explanation. but for shure i
will never use sp_updatestats again. And i will
uncheck "Auto create statistics".
Does any one can explain why i had to drop these auto
created stats ?
Thanks !!!
>--Original Message--
><anonymous@.discussions.microsoft.com> wrote in message
>news:021801c4b17c$6596cfe0$a301280a@.phx.gbl...
>>I allready have an index on wo.cmd_no
>> Here, there seems to be a problem using the good index
on
>> the join clause (wo.wo_ref = wotransit.wo_ref)
>> Please some help, the production teem cannot work
because
>> it so slow
>Well you better post the DDL. You haven't even told us
which table the
>index is on.
>And what is the execution plan?
>David
>
>.
>|||This help but i don't want to rewrite the query.
See my other post and solution on 2004/10/18
thx
>--Original Message--
>Here's a shot in the dark ...
>Have you tried setting the ISOLATION LEVEL prior to the
query and implement
>and index hint forcing the index you want to be used ?
>Like this ...
>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>GO
>BEGIN TRANSACTION
>SELECT 0
>FROM WOTransit WITH (INDEX = idx1)
> INNER JOIN WO WITH (INDEX = idx2) On
>WoTransit.Wo_ref=Wo.Wo_ref
>WHERE WO.Cmd_No=814352
>AND ISNULL(WOTransit.Charge_ref,0) <> 0
>COMMIT TRANSACTION
>Hope this helps... Let me know ?
>Anthony E. Castro
>
>
>
>"Peter Yeoh" wrote:
>> As David Browne asked, how many rows are there in WO
with Cmd_No=814352?
>> Assuming that you are 100% sure that using the
nonclustered index is better,
>> I would guess that the statistics are inaccurate.
>> To test this, use Query Analyzer and look at the
estimated execution plan
>> for the query. Open another window and get the actual
execution plan.
>> Compare the estimated number of rows against the actual
number of rows
>> retrieved for each process. Somewhere in there, there
should be a wide
>> divergence, which should indicate inaccurate
statistics. Run the UPDATE
>> STATISTICS command for the relevant index with the
FULLSCAN option. Might
>> not work always since SQL Server only maintains
distribution stats for 200
>> steps and you have millions of rows in the table, but
worth a shot.
>> --
>> Peter Yeoh
>> http://www.yohz.com
>> Need smaller SQL2K backup files? Use MiniSQLBackup
Lite, free!
>>
>> "Don" <anonymous@.discussions.microsoft.com> wrote in
message
>> news:2c8801c4b170$5415b9e0$a501280a@.phx.gbl...
>> > Please,
>> >
>> > Using SQL 7.0
>> >
>> > The optimizer is not using the good index for this
query
>> >
>> > It's using the clustered index X2 (on WOT_DATE
instead of
>> > index X1 on WO_REF.
>> >
>> > I did reindex but it's still the same. It looks to
>> > happen every monday after i run sp_updatestats
>> >
>> > What should i do or i should not do
>> >
>> > SELECT 0
>> > FROM WOTransit (readuncommitted)
>> > INNER JOIN WO (readuncommitted) On
>> > WoTransit.Wo_ref=Wo.Wo_ref
>> > WHERE WO.Cmd_No=814352
>> > AND ISNULL(WOTransit.Charge_ref,0) <> 0
>> >
>> > thanks
>> >
>> > Don
>>
>.
>|||Effectively,
Isnull() was confusing the optimizer.
See my post on 2004/10/18 to see what i had to do to fix
the problem
Ttx
>The ISNULL() function may be confusing the optimizer.
>Or the data.
>If there are actually 100k rows in the join with
WO.Cmd_No=814352 but
>they do have a null or 0 WOTransit.Charge_ref and so are
not selected,
>then maybe the optimizer doesn't know that. Is there an
index on
>Charge_ref? I'm not sure what the threshhold is for a
scan (so I
>guessed 100k), but once the optimizer wants to scan, it
would just use
>the clustered index, whatever it is. Actually, it's
using no index.
>I wonder what happens if you recode around the ISNULL?
>J.
>.
>

Optimizer not using the good index

Please,
Using SQL 7.0
The optimizer is not using the good index for this query
It's using the clustered index X2 (on WOT_DATE instead of
index X1 on WO_REF.
I did reindex but it's still the same. It looks to
happen every monday after i run sp_updatestats
What should i do or i should not do
SELECT 0
FROM WOTransit (readuncommitted)
INNER JOIN WO (readuncommitted) On
WoTransit.Wo_ref=Wo.Wo_ref
WHERE WO.Cmd_No=814352
AND ISNULL(WOTransit.Charge_ref,0) <> 0
thanks
DonDon wrote:
> Please,
> Using SQL 7.0
> The optimizer is not using the good index for this query
> It's using the clustered index X2 (on WOT_DATE instead of
> index X1 on WO_REF.
> I did reindex but it's still the same. It looks to
> happen every monday after i run sp_updatestats
> What should i do or i should not do
> SELECT 0
> FROM WOTransit (readuncommitted)
> INNER JOIN WO (readuncommitted) On
> WoTransit.Wo_ref=Wo.Wo_ref
> WHERE WO.Cmd_No=814352
> AND ISNULL(WOTransit.Charge_ref,0) <> 0
> thanks
> Don
When you say "Using the clustered index", I assume you mean you are
seeing a clustered index scan operation instead of in Index Seek on
whatever index you have on the Wo_ref column. Correct? If the Wo_ref
index is there, but is not being used, can you let us know how many rows
are in the table and how many rows the query returns.
David Gugick
Imceda Software
www.imceda.com|||My answer to your question is YES
And there is 4160164 rows in WO and 2666718 rows in
WOTRANSIT
The query returns 11 rows.
I appreciate your help
Don

>--Original Message--
>Don wrote:
of[vbcol=seagreen]
>When you say "Using the clustered index", I assume you
mean you are
>seeing a clustered index scan operation instead of in
Index Seek on
>whatever index you have on the Wo_ref column. Correct? If
the Wo_ref
>index is there, but is not being used, can you let us
know how many rows
>are in the table and how many rows the query returns.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:2ca001c4b179$6be119d0$a501280a@.phx.gbl...
> My answer to your question is YES
> And there is 4160164 rows in WO and 2666718 rows in
> WOTRANSIT
> The query returns 11 rows.
How many rows in WO with Cmd_No=814352? If the number is small then you
might try an index on Cmd_No.
David|||I allready have an index on wo.cmd_no
Here, there seems to be a problem using the good index on
the join clause (wo.wo_ref = wotransit.wo_ref)
Please some help, the production teem cannot work because
it so slow
thanks !
Don

>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2ca001c4b179$6be119d0$a501280a@.phx.gbl...
>
>How many rows in WO with Cmd_No=814352? If the number is
small then you
>might try an index on Cmd_No.
>David
>
>.
>|||As David Browne asked, how many rows are there in WO with Cmd_No=814352?
Assuming that you are 100% sure that using the nonclustered index is better,
I would guess that the statistics are inaccurate.
To test this, use Query Analyzer and look at the estimated execution plan
for the query. Open another window and get the actual execution plan.
Compare the estimated number of rows against the actual number of rows
retrieved for each process. Somewhere in there, there should be a wide
divergence, which should indicate inaccurate statistics. Run the UPDATE
STATISTICS command for the relevant index with the FULLSCAN option. Might
not work always since SQL Server only maintains distribution stats for 200
steps and you have millions of rows in the table, but worth a shot.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:2c8801c4b170$5415b9e0$a501280a@.phx.gbl...
> Please,
> Using SQL 7.0
> The optimizer is not using the good index for this query
> It's using the clustered index X2 (on WOT_DATE instead of
> index X1 on WO_REF.
> I did reindex but it's still the same. It looks to
> happen every monday after i run sp_updatestats
> What should i do or i should not do
> SELECT 0
> FROM WOTransit (readuncommitted)
> INNER JOIN WO (readuncommitted) On
> WoTransit.Wo_ref=Wo.Wo_ref
> WHERE WO.Cmd_No=814352
> AND ISNULL(WOTransit.Charge_ref,0) <> 0
> thanks
> Don|||<anonymous@.discussions.microsoft.com> wrote in message
news:021801c4b17c$6596cfe0$a301280a@.phx.gbl...
>I allready have an index on wo.cmd_no
> Here, there seems to be a problem using the good index on
> the join clause (wo.wo_ref = wotransit.wo_ref)
> Please some help, the production teem cannot work because
> it so slow
>
Well you better post the DDL. You haven't even told us which table the
index is on.
And what is the execution plan?
David|||Here's a shot in the dark ...
Have you tried setting the ISOLATION LEVEL prior to the query and implement
and index hint forcing the index you want to be used ?
Like this ...
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
BEGIN TRANSACTION
SELECT 0
FROM WOTransit WITH (INDEX = idx1)
INNER JOIN WO WITH (INDEX = idx2) On
WoTransit.Wo_ref=Wo.Wo_ref
WHERE WO.Cmd_No=814352
AND ISNULL(WOTransit.Charge_ref,0) <> 0
COMMIT TRANSACTION
Hope this helps... Let me know ?
Anthony E. Castro
"Peter Yeoh" wrote:

> As David Browne asked, how many rows are there in WO with Cmd_No=814352?
> Assuming that you are 100% sure that using the nonclustered index is bette
r,
> I would guess that the statistics are inaccurate.
> To test this, use Query Analyzer and look at the estimated execution plan
> for the query. Open another window and get the actual execution plan.
> Compare the estimated number of rows against the actual number of rows
> retrieved for each process. Somewhere in there, there should be a wide
> divergence, which should indicate inaccurate statistics. Run the UPDATE
> STATISTICS command for the relevant index with the FULLSCAN option. Might
> not work always since SQL Server only maintains distribution stats for 200
> steps and you have millions of rows in the table, but worth a shot.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:2c8801c4b170$5415b9e0$a501280a@.phx.gbl...
>
>|||"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:%23WnlNxZsEHA.2316@.TK2MSFTNGP12.phx.gbl...
> As David Browne asked, how many rows are there in WO with Cmd_No=814352?
> Assuming that you are 100% sure that using the nonclustered index is
better,
> I would guess that the statistics are inaccurate.
> To test this, use Query Analyzer and look at the estimated execution plan
> for the query. Open another window and get the actual execution plan.
Actually, the actual execution plan shows the estmated row count, too. Very
handy. What would be even better is if large discrepancies were reported
similarly to missing statistics.|||Thx for pointing that out. I should have mentioned that I was referring to
the graphical plan. Don, if you're still with us, you can view the actual
and estimated row counts side-by-side (well, almost) using the textual
execution plan. Just run SET STATISTICS PROFILE ON before running the query
in QA.
Mark, you just gave me an idea to add to our Query Analyzer Tool tool.
Thanks.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!sql