subquery won't finish - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

subquery won't finish

Author

Topic

jhun_garma
Starting Member

15 Posts

Posted - 2002-02-11 : 23:11:13

pls help me, why does this query not finish executing, but it runs fine in a different server pls help

select * from Contract where Package in (SELECT Package
FROM Contract
WHERE (Package in ('2A12343')))

Nazim
A custom title

1408 Posts

Posted - 2002-02-11 : 23:32:02

You can rewrite this query simply this way

select * from Contract where Package in ('2A12343')

About your original query not working i think there might be lot of records in your contract table and the table is scanned multiple times hence the delay. you can create a index on package colum and try the query again. if you specifically want to try such a query which i wouldnt suggest you to do.

--------------------------------------------------------------
"Happiness is not something you experience, it's something you remember."

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-02-11 : 23:40:41

Or....

SELECT * FROM Contract WHERE Package = '2A12343'

DavidM

Tomorrow is the same day as Today was the day before.

jhun_garma
Starting Member

15 Posts

Posted - 2002-02-12 : 00:00:59

sorry this should be the query

select * from Contract where Package in (SELECT Package
FROM Contract
WHERE (Contract in ('2012343')))

jhun_garma
Starting Member

15 Posts

Posted - 2002-02-12 : 00:03:28

the query works fine in other server but does not in one my test server what might be the reason for this?

Nazim
A custom title

1408 Posts

Posted - 2002-02-12 : 00:12:25

As David suggested you can modify the query on that lines
select * from Contract where Contract = ('2012343')

Try Creating a index on Contract column.

--------------------------------------------------------------
"Happiness is not something you experience, it's something you remember."

jhun_garma
Starting Member

15 Posts

Posted - 2002-02-12 : 00:31:39

sorry Nazim but I can't do that if you inspect the query closely

select * from Contract where Package in (SELECT Package
FROM Contract
WHERE (Contract in ('2012343')))

or

select * from Contract where Package in (SELECT Package
FROM Contract
WHERE (Contract = ('2012343')))

the subquery results into a Package not Contract column.

this query is to display all rows having the same package of that of contract='2012343'

I already have an index in the contract column. This works fine in other server except for one. I don't know what might be the problem pls help

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-02-12 : 00:48:13

jhun_garma

I am sorry but Nazim is right. Either you have posted your code wrong, or you are not understanding (and have not tried) the code that has been posted for you.


select * from Contract where Package in (SELECT Package
FROM Contract 
WHERE (Contract = ('2012343')))

is the SAME as



select * from Contract where Contract = '2012343'

Damian

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-02-12 : 00:55:34

First of all, it can be confusing to name columns and tables the same thing.

Second, this query is odd. I rewrote it below using a little different naming scheme since it hurts my head to think about this query. Plus I got rid of some extra parentheses.

select * 
from ContractTable 
where PackageValue in (SELECT PackageValue
  FROM ContractTable 
  WHERE ContractValue = '2012343')

The subselect returns a list of PackageValues whose corresponding ContractValue is '2012343'. It my return one value or it may return a series of values. Then the main query returns records from Contract for those PackageValues. For each of those records returned the ContractValue will be '2012343'. It will return all the records where ContractValue is '2012343' and no record where ContractValue isn't '2012343'. Therefore that's functionallly equivalent to:

select * from Contract where Contract = '2012343'

Please run both piece of code and compare the results. And post the structure and some sample data from the table.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-02-12 : 00:58:09

quote:

this query is to display all rows having the same package of that of contract='2012343'

select * from Contract where Package in (SELECT Package
FROM Contract
WHERE (Contract = ('2012343')))

First up run "sp_updatestats" against the affected DB
and maybe even DBCC DBREINDEX

And then try This...


Select * from Contract C
where Exists (Select 1 from Contract where Package = C.Package and Contract = '2012343')

DavidM

Tomorrow is the same day as Today was the day before.

Edited by - byrmol on 02/12/2002 00:59:47

jhun_garma
Starting Member

15 Posts

Posted - 2002-02-12 : 01:07:52

quote:

jhun_garma

I am sorry but Nazim is right. Either you have posted your code wrong, or you are not understanding (and have not tried) the code that has been posted for you.

select * from Contract where Package in (SELECT Package
FROM Contract
WHERE (Contract = ('2012343')))

is the SAME as

select * from Contract where Contract = '2012343'

Damian

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-02-12 : 01:11:33

Lets assume that the following is representive of the table and data..

I have turned Contract into a Identity and PK for ease...


create table #Contract(Contract int identity(1,1) not null Primary Key, Package varchar(50))
go
insert #Contract (Package) values ('1234')
insert #Contract (Package) values ('1235')
insert #Contract (Package) values ('1233')
insert #Contract (Package) values ('1237')
insert #Contract (Package) values ('1234')
insert #Contract (Package) values ('1234')

Run these 3 statements


Select * from #Contract C
where Exists (Select 1 from #Contract where Package = C.Package and Contract = 1)

select * from #Contract where Package in (SELECT Package
FROM #Contract 
WHERE (Contract = (1)))

select * from #Contract where Contract = 1

DavidM

Tomorrow is the same day as Today was the day before.

jhun_garma
Starting Member

15 Posts

Posted - 2002-02-12 : 01:30:45

quote:

First of all, it can be confusing to name columns and tables the same thing.

Second, this query is odd. I rewrote it below using a little different naming scheme since it hurts my head to think about this query. Plus I got rid of some extra parentheses.

select *
from ContractTable
where PackageValue in (SELECT PackageValue
FROM ContractTable
WHERE ContractValue = '2012343')

The subselect returns a list of PackageValues whose corresponding ContractValue is '2012343'. It my return one value or it may return a series of values. Then the main query returns records from Contract for those PackageValues. For each of those records returned the ContractValue will be '2012343'. It will return all the records where ContractValue is '2012343' and no record where ContractValue isn't '2012343'. Therefore that's functionallly equivalent to:

select * from Contract where Contract = '2012343'

Please run both piece of code and compare the results. And post the structure and some sample data from the table.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

sorry graz but my query is not an equivalent of select * from Contract where Contract='2012343' the subquery will result in a series of values and its values is not equal to your ContractValue
but will be equal to your PackageValue thus

select * from Contract where PackageValue in ('result of subquery')

my sample data is something like this: (i altered it a bit for abbreviation purposes)

TableName=Contract

PackageValue ContractValue
A 123
A 125
A 127
A 129
A 135
A 748
B 789
B 784
B 484

if i run the query select * from Contract where PackageValue in (Select PackageValue from Contract where ContractValue='123') agains the data above it should give me the result:

PackageValue ContractValue
A 123
A 125
A 127
A 129
A 135
A 748

it does not finish running in my new server. but it runs perfectly in my old Server. I hope this helps

thanks in advance

Nazim
A custom title

1408 Posts

Posted - 2002-02-12 : 03:05:48

jhu_garma you are right , both the query's are not same.

i should say the whole confusion started with your first query and then naming conventions too.

Did you tried accessing data using a simple
select * from contract is it returning any rows or hanging?

if not i would suggest you to check your connection settings and might be a Restart should help(it works for me lot of times, though i cant figure out the exact reason . someone might enlighten me on that ).

you can optimize your query by adding a distinct clause

select * from ContractTable where PackageValue in (SELECT distinct PackageValue FROM ContractTable WHERE ContractValue = '2012343')

HTH

--------------------------------------------------------------
"Happiness is not something you experience, it's something you remember."

Edited by - Nazim on 02/12/2002 03:12:34

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-12 : 05:18:34

quote:

you can optimize your query by adding a distinct clause

If you like, but it won't make a blind bit of difference to the query plan or the performance.

Edited by - Arnold Fribble on 02/12/2002 05:21:52

Nazim
A custom title

1408 Posts

Posted - 2002-02-12 : 07:46:00

select * from ContractTable where PackageValue in (SELECT PackageValue FROM ContractTable WHERE ContractValue = '2012343')

Arnold, i thought if the inner query returns 10,000 same packagevalue and the time it will take for the outer where clause to evaluate all the 10,000 values in the In clause(Am not sure if it evaluates only once coz of the repetitive value) . is more then if i just return single value as i add up a distinct clause.

if this isnt true, i would like to know how does the query gets executed in the case i have mentioned

quote:

If you like, but it won't make a blind bit of difference to the query plan or the performance.

--------------------------------------------------------------
"Happiness is not something you experience, it's something you remember."

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-12 : 08:33:23

quote:

if this isnt true, i would like to know how does the query gets executed in the case i have mentioned

The answer, somewhat glibly, is that it is executed however the query plan generated says it will be executed. The point is that a SQL SELECT is not specifying exactly how the database engine retrieves the result set, just what the result set contains.
That's not to say that any two queries with the same semantics will result in the same query plan. Just that if the query optimizer can deduce the semantic equivalence of two queries, then it can use the same plan for both. In this case, we have:


a IN (SELECT t.a FROM t WHERE t.b = 1)

And since IN is checking for set membership (ignoring the case where the subquery contains NULL), the DISTINCT makes no semantic difference. It's possible, I'll admit, that the query optimizer could take DISTINCT as a hint to aggregate the values from the subquery before checking for membership, but I think SQL Server ignores it and makes its own decision.

Edited by - Arnold Fribble on 02/12/2002 08:40:15

btrimpop
Posting Yak Master

214 Posts

Posted - 2002-02-12 : 09:11:40

First off get rid of the IN predicate. Not knowing how much data you have it may have little effect, but if it is large volume your problem may be a simple time out since IN can be very slow with large volumes of data. Change the query to use an EXISTS or even a straight join.

Something like:

select * from Contract c1 where Exists(select c2.Package from contract c2 where c1.package = c2.package and c2.contract = '2012343')

I'm not entirely sure if the original and this query are equivalent but you get the idea. I'm like Graz, thinking about this hurts my head.

"In theory there is no difference between theory and practice. But in practice there is!"

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-12 : 12:23:29

It has the same semantics if package is not nullable.
It also generates the same query plan.

jhun_garma
Starting Member

15 Posts

Posted - 2002-02-12 : 20:36:15

the problem is not whether the query runs i have tried it using a few records, less than 20 records!! and it still does not run in my newly installed server.

but i have tried running it in another server with 60,000 records and it runs perfectly. i have tried the inner join but the perfomance is a lot better using the original query. I think the problem lies in the database server. This is the only query i have tried that does not execute.

This might be a bug. I'm not really sure, if you try the query itself i'm pretty sure it will run fast and give you a very good performance result.

the only difference between the two server is its procecessor the one is more slower and runs in windows NT the other one the server that works runs in windows 2000 server my sql2000. I will be installing the service pack 1 and try it again if it still does not work i'll try to install service pack 2. the difference in the processor, i think is not an issue since i tried using less than 20 records and it still won't run

thanks

Nazim
A custom title

1408 Posts

Posted - 2002-02-12 : 23:34:03

Hi ,

I will suggest you to have a look at Sql Profiler when the query gets executed. is it getting struck or taking longer time in execution.

you can post your query execution plan , someone might come with a bright idea. but as such AFAIK therez nothing wrong with the query itself. so , you need to check on your server settings. Check if other queries are working on that table, if not then try DBCC CHECKTABLE.

HTH

--------------------------------------------------------------
"Happiness is not something you experience, it's something you remember."

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources