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

Optimalization - 3 SELECTs with one condition OR one SELECT with three conditions

Hello everyone.
I have got a simple question, but very important for me.
I making a quite big report and I have a lot of conditions to compare.
And I wonder, what is faster:
One SELECT statement with three comparing conditions
OR
Three SELECTS, each with one comparing condition
All the conditions are separated by AND condition.
The Store Procedure is being made on MS SQL SERVER 2000, the size of
table is around 22098165 records.
Thanks a lot for help... marianowic
to try a believable answer, i would like to have some example:
could you post the tables design (just the create script), an example of
your queries and some information about indexes on that tables?
Gilberto Zampatti
"marianowic" wrote:

> Hello everyone.
> I have got a simple question, but very important for me.
> I making a quite big report and I have a lot of conditions to compare.
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
> The Store Procedure is being made on MS SQL SERVER 2000, the size of
> table is around 22098165 records.
> Thanks a lot for help... marianowic
>
|||> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
How will 3 independent SELECT statements to provide the desired results with
AND conditions? I would expect the single statement approach to be best.
Performance largely depends on available indexes.
Hope this helps.
Dan Guzman
SQL Server MVP
"marianowic" <marianowic@.gmail.com> wrote in message
news:1179398916.316799.118410@.h2g2000hsg.googlegro ups.com...
> Hello everyone.
> I have got a simple question, but very important for me.
> I making a quite big report and I have a lot of conditions to compare.
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
> The Store Procedure is being made on MS SQL SERVER 2000, the size of
> table is around 22098165 records.
> Thanks a lot for help... marianowic
>
|||If you (due to lack of indexes or the nature of the queries) have to read in
the full table for any of the three, then I would bet it is more efficient
to combine everything into one query since it is ALWAYS more efficient to
read data once than multiple times. I/O is the biggest reason for
performance issues.
However, if your 3 queries each use different indexes and hit different
sections of the tables, then a multi-statement approach may be best.
I will agree with other posters that we can't help you very much with such
limited information.
TheSQLGuru
President
Indicium Resources, Inc.
"marianowic" <marianowic@.gmail.com> wrote in message
news:1179398916.316799.118410@.h2g2000hsg.googlegro ups.com...
> Hello everyone.
> I have got a simple question, but very important for me.
> I making a quite big report and I have a lot of conditions to compare.
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
> The Store Procedure is being made on MS SQL SERVER 2000, the size of
> table is around 22098165 records.
> Thanks a lot for help... marianowic
>
|||> I have got a simple question, but very important for me.
ok

> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
If you have 3 selects, do you use them all in one report? If yes, then
it does not matter if you use 1 or 3 selects, you will end up with lot
of records.

Optimalization - 3 SELECTs with one condition OR one SELECT with three conditions

Hello everyone.
I have got a simple question, but very important for me.
I making a quite big report and I have a lot of conditions to compare.
And I wonder, what is faster:
One SELECT statement with three comparing conditions
OR
Three SELECTS, each with one comparing condition
All the conditions are separated by AND condition.
The Store Procedure is being made on MS SQL SERVER 2000, the size of
table is around 22098165 records.
Thanks a lot for help... marianowicto try a believable answer, i would like to have some example:
could you post the tables design (just the create script), an example of
your queries and some information about indexes on that tables?
Gilberto Zampatti
"marianowic" wrote:
> Hello everyone.
> I have got a simple question, but very important for me.
> I making a quite big report and I have a lot of conditions to compare.
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
> The Store Procedure is being made on MS SQL SERVER 2000, the size of
> table is around 22098165 records.
> Thanks a lot for help... marianowic
>|||> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
How will 3 independent SELECT statements to provide the desired results with
AND conditions? I would expect the single statement approach to be best.
Performance largely depends on available indexes.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"marianowic" <marianowic@.gmail.com> wrote in message
news:1179398916.316799.118410@.h2g2000hsg.googlegroups.com...
> Hello everyone.
> I have got a simple question, but very important for me.
> I making a quite big report and I have a lot of conditions to compare.
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
> The Store Procedure is being made on MS SQL SERVER 2000, the size of
> table is around 22098165 records.
> Thanks a lot for help... marianowic
>|||If you (due to lack of indexes or the nature of the queries) have to read in
the full table for any of the three, then I would bet it is more efficient
to combine everything into one query since it is ALWAYS more efficient to
read data once than multiple times. I/O is the biggest reason for
performance issues.
However, if your 3 queries each use different indexes and hit different
sections of the tables, then a multi-statement approach may be best.
I will agree with other posters that we can't help you very much with such
limited information.
--
TheSQLGuru
President
Indicium Resources, Inc.
"marianowic" <marianowic@.gmail.com> wrote in message
news:1179398916.316799.118410@.h2g2000hsg.googlegroups.com...
> Hello everyone.
> I have got a simple question, but very important for me.
> I making a quite big report and I have a lot of conditions to compare.
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
> The Store Procedure is being made on MS SQL SERVER 2000, the size of
> table is around 22098165 records.
> Thanks a lot for help... marianowic
>|||> I have got a simple question, but very important for me.
ok
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
If you have 3 selects, do you use them all in one report? If yes, then
it does not matter if you use 1 or 3 selects, you will end up with lot
of records.

Optimalization - 3 SELECTs with one condition OR one SELECT with three conditions

Hello everyone.
I have got a simple question, but very important for me.
I making a quite big report and I have a lot of conditions to compare.
And I wonder, what is faster:
One SELECT statement with three comparing conditions
OR
Three SELECTS, each with one comparing condition
All the conditions are separated by AND condition.
The Store Procedure is being made on MS SQL SERVER 2000, the size of
table is around 22098165 records.
Thanks a lot for help... marianowic> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
How will 3 independent SELECT statements to provide the desired results with
AND conditions? I would expect the single statement approach to be best.
Performance largely depends on available indexes.
Hope this helps.
Dan Guzman
SQL Server MVP
"marianowic" <marianowic@.gmail.com> wrote in message
news:1179398916.316799.118410@.h2g2000hsg.googlegroups.com...
> Hello everyone.
> I have got a simple question, but very important for me.
> I making a quite big report and I have a lot of conditions to compare.
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
> The Store Procedure is being made on MS SQL SERVER 2000, the size of
> table is around 22098165 records.
> Thanks a lot for help... marianowic
>|||If you (due to lack of indexes or the nature of the queries) have to read in
the full table for any of the three, then I would bet it is more efficient
to combine everything into one query since it is ALWAYS more efficient to
read data once than multiple times. I/O is the biggest reason for
performance issues.
However, if your 3 queries each use different indexes and hit different
sections of the tables, then a multi-statement approach may be best.
I will agree with other posters that we can't help you very much with such
limited information.
TheSQLGuru
President
Indicium Resources, Inc.
"marianowic" <marianowic@.gmail.com> wrote in message
news:1179398916.316799.118410@.h2g2000hsg.googlegroups.com...
> Hello everyone.
> I have got a simple question, but very important for me.
> I making a quite big report and I have a lot of conditions to compare.
> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
> All the conditions are separated by AND condition.
> The Store Procedure is being made on MS SQL SERVER 2000, the size of
> table is around 22098165 records.
> Thanks a lot for help... marianowic
>|||> I have got a simple question, but very important for me.
ok

> And I wonder, what is faster:
> One SELECT statement with three comparing conditions
> OR
> Three SELECTS, each with one comparing condition
If you have 3 selects, do you use them all in one report? If yes, then
it does not matter if you use 1 or 3 selects, you will end up with lot
of records.