about store proc

Hi i have store proc where i am excuting a query look like
Select A.* From A where a.Col1=0 Or A.Col2='ABC'
in this query the only problem is if A.Col1 result is null then it give me
error message that timeout expired. the timeout period elapsed prior to
completion of the operation or the server is not responding ....
i dont know why its behaving like that while with And operator its not
giving that problem ... any body has any idea what it is about thankstry thi instead...
Select A.* From A where a.Col1=0
union
Select A.* From A where A.Col2='ABC'|||Amjad
Be careful using UNION clause ,because it removes a "duplicate " rows
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:2C1E6EF7-4479-4E2E-97AB-C4F406469010@.microsoft.com...
> try thi instead...
> Select A.* From A where a.Col1=0
> union
> Select A.* From A where A.Col2='ABC'
>|||Hi Uri,
I suggested UNION because he was using an OR, so anyways there wouldn't
have been any duplicates, unless the table didn't have a primary key.
Anyways, thanks for the disclaimer, I should have given it :)|||Uri Dimant (urid@.iscar.co.il) writes:
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:2C1E6EF7-4479-4E2E-97AB-C4F406469010@.microsoft.com...
> Amjad
> Be careful using UNION clause ,because it removes a "duplicate " rows
So does OR.
Then again, given the WHERE condition, there cannot be any duplicates, so
UNION ALL is a good idea.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> Uri Dimant (urid@.iscar.co.il) writes:
> So does OR.
> Then again, given the WHERE condition, there cannot be any duplicates, so
> UNION ALL is a good idea.
Eh, I didn't read the query well enough, but thought it was the same column
in both cases.
Given that, you should use UNION and not UNION ALL, if you try this
workaround.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||examnotes (amjad@.discussions.microsoft.com) writes:
> Hi i have store proc where i am excuting a query look like
>
> Select A.* From A where a.Col1=0 Or A.Col2='ABC'
> in this query the only problem is if A.Col1 result is null then it give me
> error message that timeout expired. the timeout period elapsed prior to
> completion of the operation or the server is not responding ....
> i dont know why its behaving like that while with And operator its not
> giving that problem ... any body has any idea what it is about thanks
There is a very big difference between AND and OR.
Are both columns indexed? And is the distribution in the column selective
enough?
Say that there is only a column on Col2 and you run:
Select A.* From A where a.Col1=0 AND A.Col2='ABC'
SQL Server can find the rows by using the index on Col2, and then check Col1
to see if the row is to be included.
But with OR, SQL Server must check all rows for Col1, which means that it
has to scan the table. Which can take a long time if the table is huge.
If both columns are indexed, SQL Server can use both indexes, but in such
case it needs to add an extra operator to the query plan to sort out the
duplicates.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi i got different result using union operator
like if i run Select A.* From A Where a.col1=0 give me 133 record and then
if i run Select A.* From A where a.col2='abc' it give me 186 but when i run
with union it gave me 169 records i dont know why thanks
"Omnibuzz" wrote:

> try thi instead...
> Select A.* From A where a.Col1=0
> union
> Select A.* From A where A.Col2='ABC'
>|||Thats because union removes the duplicates.
if you use UNION ALL, then you will get the total right.
But I guess you would want to eiliminate the duplicates|||thanks for all of you. its working now with Union All
"Omnibuzz" wrote:

> Thats because union removes the duplicates.
> if you use UNION ALL, then you will get the total right.
> But I guess you would want to eiliminate the duplicates
>