why there is a diffrence in speed in that way ..

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

General SQL Server Forums

New to SQL Server Programming

why there is a diffrence in speed in that way ..

Author

Topic

fabianus76
Posting Yak Master

191 Posts

Posted - 2008-06-23 : 04:50:13

Hello all,

I am very astonished about the following (recplus has 4.2 Million records).

if I do this :

select sum (quantityInKg) from recplus where element_ID in (160,
161,
162,
164,
165,
166,
1756,
21707,
22052,
22063)

it takes about 2 seconds.

If I do this

select sum (quantityInKg) from recplus where element_ID in (select element_2_id from element_element where element_id = '159')

it takes 1 second. (select element_2_id from element_element where element_id = '159' gives the same list as in the previous query)

Now, why is there a speed diffrence, or better, why in this way? I would have expected it to be the othere way around !

Thanks for your feedbacks.

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 04:55:42

Do the first method always take two seconds? Or only the first execution?
Run the first method 5 times to verify.

I think this will be fastest with proper indexes.

select sum (rp.quantityInKg)
from recplus as rp
inner join element_element as ee on ee.element_2_id = rp.element_ID
where ee.element_id = '159'

E 12°55'05.25"
N 56°04'39.16"

fabianus76
Posting Yak Master

191 Posts

Posted - 2008-06-23 : 05:23:27

Hello Peso,

yes, I did both a lot of times, at first it took 6 seconds or something like this, and than it came to the values I indicate.
I would be VERY interested to understand why your proposal should be faster. Hope it's not bothering you too much to give a litle explination ... :-)
When you say "proper indexes", you mean an ordenary index on ee.element_2_id and one on rp.element_ID ?

Thanks a lot for any feedback !

Regards,
Fabianus

PS I did the measurements again, here are the results :

3 seconds :
select sum (quantityInKg) from recplus where element_ID in (160,
161,
162,
164,
165,
166,
1756,
21707,
22052,
22063)

2 seconds :

select sum (rp.quantityInKg)
from recplus as rp
inner join element_element as ee on ee.element_2_id = rp.element_ID
where ee.element_id = '159'

1 second :
select sum (quantityInKg) from recplus where element_ID in (select element_2_id from element_element where element_id = '159')

(I only know the seconds, not more precise, because in the interface of Micorosoft SQL Server Management Studio Express you see the seconds in the satus bar - if you have any more precise indication, let me know!

my favorit hoster is ASPnix : www.aspnix.com !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 05:42:16

declare @dt datetime

set @dt = getdate()

...
do your thing here
...

select datediff(ms, @dt, getdate()) as [time taken in ms]

E 12°55'05.25"
N 56°04'39.16"

ToniMarieM
Starting Member

6 Posts

Posted - 2008-06-25 : 17:07:23

What indexes are on each of the tables? What do the execution plans look like? How many records in the joined tables?

Toni

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources