SQL Error - 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

General SQL Server Forums

New to SQL Server Programming

SQL Error

Author

Topic

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-02-13 : 12:23:44

Can anyone help me with this? I don't really know how to fix it. This is what my query looks like.

select
SVCMO = year(first_svc_date) * 100 + Month(first_svc_date),
OPR.dbo.fn_MonthName_Year(year(first_svc_date) * 100 + Month(first_svc_date)) as ServiceMonth
,prov_no,
prov_name,
prov_type,
PROGRAM_CODE,
MATCH_CODE,
MEDICAL_CODE,
PROC_CODE_CPT,
PROC_CODE_MOD,
li_reimb_amt,
first_svc_date,
prov_county_code,
RECIP_AGE,
ICN,
lineitem_code
--into DBO.CHEMDEPTEMP_1
from dshsdboly7205.claims.dbo.ExtendedLineItem
where first_svc_date between '07/01/2007' and '12/31/2007'
and prov_type in ('75')
and prov_no NOT IN (select * from huynhtl.tblprovider)

When I run it, this is the error that come up.

Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-13 : 12:31:34

change and prov_no NOT IN (select * from huynhtl.tblprovider)
to and prov_no NOT IN (select prov_no from huynhtl.tblprovider)

also and prov_type in ('75')
should be and prov_type = '75'
It will run much faster

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-02-13 : 13:41:21

Thanks!!!

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-13 : 13:44:16

!!! Don't ever use NOT IN (Select...)!! It will perform terribly. Instead use:


prov_no Not Exists (Select prov_no From huynhtl.tblprovider)

OR

From dshsdboly7205.claims.dbo.ExtendedLineItem E
Left Outer Join huynhtl.tblprovider P On E.prov_no = P.prov_no
Where first_svc_date Between '07/01/2007' And '12/31/2007'
And prov_type = '75'
And P.prov_no Is Null

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-02-13 : 13:46:52

What's the different between NOT IN and NOT EXISTS?

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-13 : 13:57:35

[Not] In
http://msdn2.microsoft.com/en-us/library/ms177682.aspx

[Not] Exists
http://msdn2.microsoft.com/en-us/library/ms188336.aspx

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-13 : 14:00:37

Aside from performance, this article gives some explanation.

http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-13 : 14:07:46

Also, there is an error in the Exists query I posted for you. It should be


Not Exists (Select prov_no From huynhtl.tblprovider Where prov_no = ExtendedLineItem.prov_no)

The execution plan of the not exists and the left outer join are identical by the way. Not Exists is just a shorthand way of the left outer join. I am running some statistics numbers on performance for you.

My ssislog table has 19151 records in it.


-- Query 1
Select * 
From ssislog
Where id Not In 
(
	Select id 
	From ssislog 
	Where id < 8000
)

-- Results
/*
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 30 ms.

(11152 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ssislog'. Scan count 3, logical reads 39540, physical reads 0, read-ahead reads 618, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 1954 ms,  elapsed time = 2339 ms.
*/


-- Query 2
Select * 
From ssislog S
Where Not Exists 
(
	Select id 
	From ssislog 
	Where id < 8000 And id = S.id
)

-- Results
/*
SQL Server parse and compile time: 
   CPU time = 9 ms, elapsed time = 9 ms.

(11152 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ssislog'. Scan count 2, logical reads 1238, physical reads 0, read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 547 ms.
*/

As you can see, Not In is very expensive. Look at the Logical Reads. It is nearly 32x the reads of Not Exists. Logical reads are expensive time wise because it is IO based and IO is extremely slow.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources