faster query - 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 2005 Forums

Transact-SQL (2005)

faster query

Author

Topic

farry
Starting Member

2 Posts

Posted - 2008-09-12 : 05:36:40

[1]
SELECT * FROM sims s, fos f
WHERE s.distribution_date IS NOT NULL
AND s.brand_code = f.brand_code

[2]
SELECT * FROM sims s, fos f
WHERE s.brand_code = f.brand_code
AND s.distribution_date IS NOT NULL

Please help me...

which one of queries above will give the faster result?
I expect a simple reason...
thank...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-12 : 06:25:38

have you try these out at all ? Just execute the query and timed it see which is faster

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-12 : 06:36:44

[code]SELECT *
FROM fos AS f
INNER JOIN (
SELECT Brand_Code
FROM sims
WHERE distribution_date IS NOT NULL
) AS s ON s.Brand_Code = f.Brand_Code[/code]

E 12°55'05.63"
N 56°04'39.26"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-09-12 : 07:14:08

Is that better, worse, or no different to.


SELECT
	*
FROM
	fos f
	INNER JOIN sims s ON
		s.brand_code = f.brand_code
		AND s.distribution_date IS NOT NULL

-------------
Charlie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-12 : 08:36:28

Somewhat better.

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-12 : 08:45:21

Which version of SQL Server are you using?
In 2000, the order of the WHERE clause matters
This gives result


select data from
(
	select 'A098' as data union all
	select '456' union all
	select '98'
) as t
where data not like '%[^.0-9]%' and data*1>100

whereas the following throws error


select data from
(
	select 'A098' as data union all
	select '456' union all
	select '98'
) as t
where  data*1>100 and data not like '%[^.0-9]%'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources