Help to simplify the query

Question

Post reply

Help to simplify the query

gagankhurana724

SSC Journeyman

Points: 78
More actions
May 12, 2008 at 3:20 pm

#190418

Hi All

I do have a sql statement to query the records from the table (which contains version records of person). For e.g. For each person record of Person Table i do have multiple records in PersonVers Table.

My query finds out all records from a third table by passing "PersonVersId" from third table. My query is as follows:

Select * from ABC

Where PersonVersId IN

(

Select PersonVersId

from PersonVers

where PersonID = (select PersonID

from PersonVers

where PersonVersId=1)

)

This query is working, But I want to simplify the query and want to make it faster. This query is a part of a large procedure, which is taking a lot of time due to this query.

Please help me to find out how can i make it faster.

Thanks in advance

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

I'd convert all of the WHERE IN's to proper joins and see if the proper indexes are in place. I'd also use a SELECT list instead of * to return only those columns that need to be returned.

You might want to take a look at JOINs in Books Online if you are not already familiar with them. Same goes for indexes and keys.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Lata Patel SSC Enthusiast Points: 129 More actions · Answer 2

Lata Patel

SSC Enthusiast

Points: 129

May 13, 2008 at 1:29 am

#814273

Please Refer "Join" in your query...u can use inner join.:)

gagankhurana724 SSC Journeyman Points: 78 More actions · Answer 3

Hi Jeff

Thanks for the reply.

I think i didn't clearify my problem correctly. Let me explain it once more with more details.

I do have 3 tables i.e. Contribution, Person, PersonVers

Here are the structure for all these tables.

Contribution

-----------

ContributionId, PersonVersId, Amount

Person

---------

PersonId, Name, Age

PersonVers

-------------

PersonVersId, PersonId, NewName

Now here is the sample data:

Contribution

------------

1, 101, $50

2, 102, $10

3, 103, $30

4, 104, $20

Person

---------

1, Joan, 50

2, Gagan, 30

3, Lois, 25

PersonVers

-----------

101, 1, Joan

102, 2, Gagan

103, 2, Gagan Jr.

104, 1, Dr. Joan

Now my requirement is to get the total amount of contribution given by each "Original Person". As in my sample data PersonVersId of 101 and 104 pointing to same personId 1 & PersonVersId of 102 and 103 pointing to same personId 2.

My resultset should return the following:

(Assume I am passing PersonVersId = 101)

Result Set

----------

ContributionId, PersonVersId, PersonId, Contribution, TotalContributionOfPerson

---------------------------------------------------------------------------

1, 101, 1, $50, $70

4, 104, 1, $20, $70

and If I pass PersonVersId = 102 then result set should be:

ContributionId, PersonVersId, PersonId, Contribution, TotalContributionOfPerson

---------------------------------------------------------------------------

2, 102, 1, $10, $40

3, 103, 1, $30, $40

Thanks in Advance

Cees Cappelle-442904 SSC Eights! Points: 917 More actions · Answer 4

gagankhurana724 (5/12/2008)

Select * from ABC

Where PersonVersId IN

(

Select PersonVersId

from PersonVers

where PersonID = (select PersonID

from PersonVers

where PersonVersId=1)

)

Select * from ABC

INNER JOIN PersonVers

ON ABC.PersonID = PersonVers.PersonID

WHERE PersonVers.PersonID =

Try this, as well as replacing select * with a list of only the columns needed.

Cees Cappelle

GSquared SSC Guru Points: 260824 More actions · Answer 5

I tried various ways to rewrite your query, and using a Join instead of an In does speed it up a bit, but the main thing is going to be having the right indexes on it.

The best I could do for your query is:



;with

Person (PersonVersID, PersonID) as

	(select PersonVersID, PersonID

	from PersonVers

	where PersonID =

		(select PersonID

		from PersonVers

		where PersonVersID = 101)),

Totals (Total) as

	(select sum(Amount)

	from Contributions

	inner join Person

		on Contributions.PersonVersID = Person.PersonVersID)

select ContributionID, Person.PersonVersID, Person.PersonID,

Amount as Contribution, Totals.Total as TotalContributionOfPerson

from Contributions

inner join Person

	on Contributions.PersonVersID = Person.PersonVersID

cross join Totals

(I'm assuming SQL 2005 because of the forum this was posted in. If it's actually SQL 2000, you'll need to convert the CTEs to standard derived tables in the From clause.)

The performance on this really depends on the number of rows in the tables and the indexes on them.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

gagankhurana724 SSC Journeyman Points: 78 More actions · Answer 6

gagankhurana724

SSC Journeyman

Points: 78

May 13, 2008 at 1:10 pm

#814749

Hi GSquared

Thanks for your reply.

It Works!! It increase the performance by around 40%.

Thanks a Lot!!:)

GSquared SSC Guru Points: 260824 More actions · Answer 7

You're welcome. (I still recommend taking a look at index use for this, if you haven't already.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon