sqlserver2000 date: queries

Showing posts with label queries. Show all posts

Wednesday, March 21, 2012

Incorrect query results with embedded comments

The following two identical queries return different
results. The first one with comments embedded in the query
returns all rows (ignoring the condition after the
comment). The second query with no comment works fine.
My SQL Server version is:
Microsoft SQL Server 2000 - 8.00.679 (Intel X86) Aug 26
2002 15:09:48 Copyright (c) 1988-2000 Microsoft
Corporation Enterprise Edition on Windows NT 5.2 (Build
3718: )
--
select CLEC_EU_DISCONNECT_INFORMATION.DNUM
CLEC_EU_DISCONNECT_INFORMATION_DNUM,
CLEC_TXN_PON.PON_VER
CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
left outer join CLEC_EU_DISCONNECT_INFORMATION
on
CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATION
.DISC_INFO_PON_VER
and
CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DISC
_INFO_LOCNUM
--left outer join CLEC_EU_TRANSFER_CALLS on
--
CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_LOCNUM=CLEC_EU_TRA
NSFER_CALLS.TC_LOCNUM
--and
CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_PON_VER=CLEC_EU_TR
ANSFER_CALLS.TC_PON_VER
--and
CLEC_EU_DISCONNECT_INFORMATION.DNUM=CLEC_EU_TRANSFER_CALLS.
TC_DNUM
where CLEC_TXN_PON.PON_VER='217-00'
select CLEC_EU_DISCONNECT_INFORMATION.DNUM
CLEC_EU_DISCONNECT_INFORMATION_DNUM,
CLEC_TXN_PON.PON_VER
CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
left outer join CLEC_EU_DISCONNECT_INFORMATION
on
CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATION
.DISC_INFO_PON_VER
and
CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DISC
_INFO_LOCNUM
where CLEC_TXN_PON.PON_VER='217-00'Shame this lost it's formatting - but I'm guessing all the
joins are on single lines.
I've come across this a couple of times where comments
mess up (or help) a query but never with v2000.
Look at syscomments to see what the query looks like.
Especially the line termination character on the last line.
It's worthwhile just retyping the query too.
Are you using query analyser?
>--Original Message--
>The following two identical queries return different
>results. The first one with comments embedded in the
query
>returns all rows (ignoring the condition after the
>comment). The second query with no comment works fine.
>My SQL Server version is:
>Microsoft SQL Server 2000 - 8.00.679 (Intel X86) Aug
26
>2002 15:09:48 Copyright (c) 1988-2000 Microsoft
>Corporation Enterprise Edition on Windows NT 5.2 (Build
>3718: )
>--
>
>select CLEC_EU_DISCONNECT_INFORMATION.DNUM
>CLEC_EU_DISCONNECT_INFORMATION_DNUM,
>CLEC_TXN_PON.PON_VER
>CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
>from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
>on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
>left outer join CLEC_EU_DISCONNECT_INFORMATION
>on
>CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
N
>..DISC_INFO_PON_VER
>and
>CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
C
>_INFO_LOCNUM
>--left outer join CLEC_EU_TRANSFER_CALLS on
>--
>CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_LOCNUM=CLEC_EU_TR
A
>NSFER_CALLS.TC_LOCNUM
>--and
>CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_PON_VER=CLEC_EU_T
R
>ANSFER_CALLS.TC_PON_VER
>--and
>CLEC_EU_DISCONNECT_INFORMATION.DNUM=CLEC_EU_TRANSFER_CALLS
.
>TC_DNUM
>where CLEC_TXN_PON.PON_VER='217-00'
>
>select CLEC_EU_DISCONNECT_INFORMATION.DNUM
>CLEC_EU_DISCONNECT_INFORMATION_DNUM,
>CLEC_TXN_PON.PON_VER
>CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
>from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
>on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
>left outer join CLEC_EU_DISCONNECT_INFORMATION
>on
>CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
N
>..DISC_INFO_PON_VER
>and
>CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
C
>_INFO_LOCNUM
>where CLEC_TXN_PON.PON_VER='217-00'
>.
>|||Hi Swami,
Does the problem occur if you place the two queries in Query Analyzer? I
think the command is not correctly generated.
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Bill Cheng
Microsoft Support Engineer
--
| Content-Class: urn:content-classes:message
| From: "Swami Muthuvelu" <swami@.mclsystems.com>
| Sender: "Swami Muthuvelu" <swami@.mclsystems.com>
| References: <00b301c340c4$a033c1a0$a501280a@.phx.gbl>
| Subject: Incorrect query results with embedded comments
| Date: Wed, 2 Jul 2003 11:34:41 -0700
| Lines: 78
| Message-ID: <463701c340c8$99cc8460$a401280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Thread-Index: AcNAyJnM9s4j20agSQe1sjFP5SfVvw==| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.sqlserver.server
| Path: cpmsftngxa09.phx.gbl
| Xref: cpmsftngxa09.phx.gbl microsoft.public.sqlserver.server:22629
| NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| The query mentioned below was a programmatically generated
| query, with lines ending with "Carriage return" (char(13))
| character, and did not have line feed (char(10)). When
| such generated SQL was copied and pasted into SQL Query
| analyzer displayed perfectly fine, but did not recognize
| the break of the line.
|
|
| >--Original Message--
| >The following two identical queries return different
| >results. The first one with comments embedded in the
| query
| >returns all rows (ignoring the condition after the
| >comment). The second query with no comment works fine.
| >
| >My SQL Server version is:
| >
| >Microsoft SQL Server 2000 - 8.00.679 (Intel X86) Aug
| 26
| >2002 15:09:48 Copyright (c) 1988-2000 Microsoft
| >Corporation Enterprise Edition on Windows NT 5.2 (Build
| >3718: )
| >
| >--
| >
| >
| >select CLEC_EU_DISCONNECT_INFORMATION.DNUM
| >CLEC_EU_DISCONNECT_INFORMATION_DNUM,
| >CLEC_TXN_PON.PON_VER
| >CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
|
| >from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
| >on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
| >left outer join CLEC_EU_DISCONNECT_INFORMATION
| >on
| >CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
| N
| >..DISC_INFO_PON_VER
| >and
| >CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
| C
| >_INFO_LOCNUM
| >--left outer join CLEC_EU_TRANSFER_CALLS on
| >--
| >CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_LOCNUM=CLEC_EU_TR
| A
| >NSFER_CALLS.TC_LOCNUM
| >--and
| >CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_PON_VER=CLEC_EU_T
| R
| >ANSFER_CALLS.TC_PON_VER
| >--and
| >CLEC_EU_DISCONNECT_INFORMATION.DNUM=CLEC_EU_TRANSFER_CALLS
| .
| >TC_DNUM
| >where CLEC_TXN_PON.PON_VER='217-00'
| >
| >
| >select CLEC_EU_DISCONNECT_INFORMATION.DNUM
| >CLEC_EU_DISCONNECT_INFORMATION_DNUM,
| >CLEC_TXN_PON.PON_VER
| >CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
|
| >from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
| >on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
| >left outer join CLEC_EU_DISCONNECT_INFORMATION
| >on
| >CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
| N
| >..DISC_INFO_PON_VER
| >and
| >CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
| C
| >_INFO_LOCNUM
| >where CLEC_TXN_PON.PON_VER='217-00'
| >
| >.
| >
||||Here are two queries. Each one generates a SQL query. Copy
and paste the results of the query to the query pane of
query analyzer and run it. Both looks identical. The first
one does not work, but the second one does.
--
select 'select * ' + char(13) + '--comment--' + char(13)
+ 'from sysobjects'
select 'select * ' + char(13) + '--comment--' + char(10)
+ 'from sysobjects'
--
You are not considering a carriage return as a line break
in the server engine, but you are considering as a line
break for the query analyzer.
Swami
>--Original Message--
>Hi Swami,
>Does the problem occur if you place the two queries in
Query Analyzer? I
>think the command is not correctly generated.
>
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Regards,
>Bill Cheng
>Microsoft Support Engineer
>--
>| Content-Class: urn:content-classes:message
>| From: "Swami Muthuvelu" <swami@.mclsystems.com>
>| Sender: "Swami Muthuvelu" <swami@.mclsystems.com>
>| References: <00b301c340c4$a033c1a0$a501280a@.phx.gbl>
>| Subject: Incorrect query results with embedded comments
>| Date: Wed, 2 Jul 2003 11:34:41 -0700
>| Lines: 78
>| Message-ID: <463701c340c8$99cc8460$a401280a@.phx.gbl>
>| MIME-Version: 1.0
>| Content-Type: text/plain;
>| charset="iso-8859-1"
>| Content-Transfer-Encoding: 7bit
>| X-Newsreader: Microsoft CDO for Windows 2000
>| Thread-Index: AcNAyJnM9s4j20agSQe1sjFP5SfVvw==>| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>| Newsgroups: microsoft.public.sqlserver.server
>| Path: cpmsftngxa09.phx.gbl
>| Xref: cpmsftngxa09.phx.gbl
microsoft.public.sqlserver.server:22629
>| NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
>| X-Tomcat-NG: microsoft.public.sqlserver.server
>|
>| The query mentioned below was a programmatically
generated
>| query, with lines ending with "Carriage return" (char
(13))
>| character, and did not have line feed (char(10)). When
>| such generated SQL was copied and pasted into SQL Query
>| analyzer displayed perfectly fine, but did not
recognize
>| the break of the line.
>|
>|
>| >--Original Message--
>| >The following two identical queries return different
>| >results. The first one with comments embedded in the
>| query
>| >returns all rows (ignoring the condition after the
>| >comment). The second query with no comment works fine.
>| >
>| >My SQL Server version is:
>| >
>| >Microsoft SQL Server 2000 - 8.00.679 (Intel X86)
Aug
>| 26
>| >2002 15:09:48 Copyright (c) 1988-2000 Microsoft
>| >Corporation Enterprise Edition on Windows NT 5.2
(Build
>| >3718: )
>| >
>| >--
>| >
>| >
>| >select CLEC_EU_DISCONNECT_INFORMATION.DNUM
>| >CLEC_EU_DISCONNECT_INFORMATION_DNUM,
>| >CLEC_TXN_PON.PON_VER
>|
>CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
>|
>| >from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
>| >on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
>| >left outer join CLEC_EU_DISCONNECT_INFORMATION
>| >on
>|
>CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
>| N
>| >..DISC_INFO_PON_VER
>| >and
>|
>CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
>| C
>| >_INFO_LOCNUM
>| >--left outer join CLEC_EU_TRANSFER_CALLS on
>| >--
>|
>CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_LOCNUM=CLEC_EU_TR
>| A
>| >NSFER_CALLS.TC_LOCNUM
>| >--and
>|
>CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_PON_VER=CLEC_EU_T
>| R
>| >ANSFER_CALLS.TC_PON_VER
>| >--and
>|
>CLEC_EU_DISCONNECT_INFORMATION.DNUM=CLEC_EU_TRANSFER_CALLS
>| .
>| >TC_DNUM
>| >where CLEC_TXN_PON.PON_VER='217-00'
>| >
>| >
>| >select CLEC_EU_DISCONNECT_INFORMATION.DNUM
>| >CLEC_EU_DISCONNECT_INFORMATION_DNUM,
>| >CLEC_TXN_PON.PON_VER
>|
>CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
>|
>| >from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
>| >on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
>| >left outer join CLEC_EU_DISCONNECT_INFORMATION
>| >on
>|
>CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
>| N
>| >..DISC_INFO_PON_VER
>| >and
>|
>CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
>| C
>| >_INFO_LOCNUM
>| >where CLEC_TXN_PON.PON_VER='217-00'
>| >
>| >.
>| >
>|
>.
>|||Hi Swami,
I think the comment to be generated inside a single query causes the
problem. Could you make it generated before or after the query?
In addition, none of the query works on my side in Query Analyzer.
select 'select * ' + char(13) + '--comment--' + char(13)
+ 'from sysobjects'
select 'select * ' + char(13) + '--comment--' + char(10)
+ 'from sysobjects'
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Bill Cheng
Microsoft Support Engineer
--
| Content-Class: urn:content-classes:message
| From: "Swami Muthuvelu" <swami@.mclsystems.com>
| Sender: "Swami Muthuvelu" <swami@.mclsystems.com>
| References: <00b301c340c4$a033c1a0$a501280a@.phx.gbl>
<463701c340c8$99cc8460$a401280a@.phx.gbl>
<8zvyhVVQDHA.412@.cpmsftngxa09.phx.gbl>
| Subject: RE: Incorrect query results with embedded comments
| Date: Thu, 3 Jul 2003 07:06:21 -0700
| Lines: 155
| Message-ID: <0a1e01c3416c$47c51e80$a501280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Thread-Index: AcNBbEfCiVpFmLFfSJ+NOObv2qtfuw==| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.sqlserver.server
| Path: cpmsftngxa09.phx.gbl
| Xref: cpmsftngxa09.phx.gbl microsoft.public.sqlserver.server:22750
| NNTP-Posting-Host: TK2MSFTNGXA13 10.40.1.165
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Here are two queries. Each one generates a SQL query. Copy
| and paste the results of the query to the query pane of
| query analyzer and run it. Both looks identical. The first
| one does not work, but the second one does.
|
| --
| select 'select * ' + char(13) + '--comment--' + char(13)
| + 'from sysobjects'
|
| select 'select * ' + char(13) + '--comment--' + char(10)
| + 'from sysobjects'
| --
|
| You are not considering a carriage return as a line break
| in the server engine, but you are considering as a line
| break for the query analyzer.
|
| Swami
|
|
|
| >--Original Message--
| >Hi Swami,
| >
| >Does the problem occur if you place the two queries in
| Query Analyzer? I
| >think the command is not correctly generated.
| >
| >
| >This posting is provided "AS IS" with no warranties, and
| confers no rights.
| >
| >Regards,
| >
| >Bill Cheng
| >Microsoft Support Engineer
| >--
| >| Content-Class: urn:content-classes:message
| >| From: "Swami Muthuvelu" <swami@.mclsystems.com>
| >| Sender: "Swami Muthuvelu" <swami@.mclsystems.com>
| >| References: <00b301c340c4$a033c1a0$a501280a@.phx.gbl>
| >| Subject: Incorrect query results with embedded comments
| >| Date: Wed, 2 Jul 2003 11:34:41 -0700
| >| Lines: 78
| >| Message-ID: <463701c340c8$99cc8460$a401280a@.phx.gbl>
| >| MIME-Version: 1.0
| >| Content-Type: text/plain;
| >| charset="iso-8859-1"
| >| Content-Transfer-Encoding: 7bit
| >| X-Newsreader: Microsoft CDO for Windows 2000
| >| Thread-Index: AcNAyJnM9s4j20agSQe1sjFP5SfVvw==| >| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| >| Newsgroups: microsoft.public.sqlserver.server
| >| Path: cpmsftngxa09.phx.gbl
| >| Xref: cpmsftngxa09.phx.gbl
| microsoft.public.sqlserver.server:22629
| >| NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
| >| X-Tomcat-NG: microsoft.public.sqlserver.server
| >|
| >| The query mentioned below was a programmatically
| generated
| >| query, with lines ending with "Carriage return" (char
| (13))
| >| character, and did not have line feed (char(10)). When
| >| such generated SQL was copied and pasted into SQL Query
| >| analyzer displayed perfectly fine, but did not
| recognize
| >| the break of the line.
| >|
| >|
| >| >--Original Message--
| >| >The following two identical queries return different
| >| >results. The first one with comments embedded in the
| >| query
| >| >returns all rows (ignoring the condition after the
| >| >comment). The second query with no comment works fine.
| >| >
| >| >My SQL Server version is:
| >| >
| >| >Microsoft SQL Server 2000 - 8.00.679 (Intel X86)
| Aug
| >| 26
| >| >2002 15:09:48 Copyright (c) 1988-2000 Microsoft
| >| >Corporation Enterprise Edition on Windows NT 5.2
| (Build
| >| >3718: )
| >| >
| >| >--
| >| >
| >| >
| >| >select CLEC_EU_DISCONNECT_INFORMATION.DNUM
| >| >CLEC_EU_DISCONNECT_INFORMATION_DNUM,
| >| >CLEC_TXN_PON.PON_VER
| >|
| >CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
| >|
| >| >from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
| >| >on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
| >| >left outer join CLEC_EU_DISCONNECT_INFORMATION
| >| >on
| >|
| >CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
| >| N
| >| >..DISC_INFO_PON_VER
| >| >and
| >|
| >CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
| >| C
| >| >_INFO_LOCNUM
| >| >--left outer join CLEC_EU_TRANSFER_CALLS on
| >| >--
| >|
| >CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_LOCNUM=CLEC_EU_TR
| >| A
| >| >NSFER_CALLS.TC_LOCNUM
| >| >--and
| >|
| >CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_PON_VER=CLEC_EU_T
| >| R
| >| >ANSFER_CALLS.TC_PON_VER
| >| >--and
| >|
| >CLEC_EU_DISCONNECT_INFORMATION.DNUM=CLEC_EU_TRANSFER_CALLS
| >| .
| >| >TC_DNUM
| >| >where CLEC_TXN_PON.PON_VER='217-00'
| >| >
| >| >
| >| >select CLEC_EU_DISCONNECT_INFORMATION.DNUM
| >| >CLEC_EU_DISCONNECT_INFORMATION_DNUM,
| >| >CLEC_TXN_PON.PON_VER
| >|
| >CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
| >|
| >| >from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
| >| >on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
| >| >left outer join CLEC_EU_DISCONNECT_INFORMATION
| >| >on
| >|
| >CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
| >| N
| >| >..DISC_INFO_PON_VER
| >| >and
| >|
| >CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
| >| C
| >| >_INFO_LOCNUM
| >| >where CLEC_TXN_PON.PON_VER='217-00'
| >| >
| >| >.
| >| >
| >|
| >
| >.
| >
|sql

Monday, March 12, 2012

Inconsistent Query Performance

Hello!
We are having a problem with one of the queries that an application runs.
This query uses some temp tables (subbed as table variables with no
performance change) and heavily uses some user defined functions. When run
on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
2000 Developer Editions), it returns within a second. When run against a
enterprise class server with 8 GB of RAM and at least four processors, maybe
eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
is nothing else going on with the large server. This will be the only
activity on the system.. I disabled awe and turned off /PAE switch with no
change. The timings are repeatable and the only improvement on the
enterprise class server is shortly after a reboot and even then the results
take 2-4 seconds vs, 1 second on the workstation. This performance quickly
deteriorates on the server. I have tried this with the database both on SAN
or on local disk on the server and there is no measurable difference.
Any ideas on what I should check next?
ThanksSteve,
> Any ideas on what I should check next?
Execution Plans
AMB
"Steve H" wrote:
> Hello!
> We are having a problem with one of the queries that an application runs.
> This query uses some temp tables (subbed as table variables with no
> performance change) and heavily uses some user defined functions. When run
> on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> 2000 Developer Editions), it returns within a second. When run against a
> enterprise class server with 8 GB of RAM and at least four processors, maybe
> eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
> is nothing else going on with the large server. This will be the only
> activity on the system.. I disabled awe and turned off /PAE switch with no
> change. The timings are repeatable and the only improvement on the
> enterprise class server is shortly after a reboot and even then the results
> take 2-4 seconds vs, 1 second on the workstation. This performance quickly
> deteriorates on the server. I have tried this with the database both on SAN
> or on local disk on the server and there is no measurable difference.
> Any ideas on what I should check next?
> Thanks|||First step is to check the execution plans. Same?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve H" <SteveH@.discussions.microsoft.com> wrote in message
news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
> Hello!
> We are having a problem with one of the queries that an application runs.
> This query uses some temp tables (subbed as table variables with no
> performance change) and heavily uses some user defined functions. When run
> on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> 2000 Developer Editions), it returns within a second. When run against a
> enterprise class server with 8 GB of RAM and at least four processors, maybe
> eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
> is nothing else going on with the large server. This will be the only
> activity on the system.. I disabled awe and turned off /PAE switch with no
> change. The timings are repeatable and the only improvement on the
> enterprise class server is shortly after a reboot and even then the results
> take 2-4 seconds vs, 1 second on the workstation. This performance quickly
> deteriorates on the server. I have tried this with the database both on SAN
> or on local disk on the server and there is no measurable difference.
> Any ideas on what I should check next?
> Thanks|||Yes, execution plans are very similar. Not exactly identical - but all
components are very similar in costs.
"Tibor Karaszi" wrote:
> First step is to check the execution plans. Same?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
> > Hello!
> > We are having a problem with one of the queries that an application runs.
> > This query uses some temp tables (subbed as table variables with no
> > performance change) and heavily uses some user defined functions. When run
> > on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> > 2000 Developer Editions), it returns within a second. When run against a
> > enterprise class server with 8 GB of RAM and at least four processors, maybe
> > eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
> > is nothing else going on with the large server. This will be the only
> > activity on the system.. I disabled awe and turned off /PAE switch with no
> > change. The timings are repeatable and the only improvement on the
> > enterprise class server is shortly after a reboot and even then the results
> > take 2-4 seconds vs, 1 second on the workstation. This performance quickly
> > deteriorates on the server. I have tried this with the database both on SAN
> > or on local disk on the server and there is no measurable difference.
> >
> > Any ideas on what I should check next?
> >
> > Thanks
>
>|||Similar <> same :-)
What does STATISTICS I/O say. I usually complement execution plans with statistics I/O. Also check
for parallelism operators in the plan for the large machine. Sometimes parallelism is done with a
inefficient result, and can be turned off using the MAXDOP hint (set to 1).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve H" <SteveH@.discussions.microsoft.com> wrote in message
news:348166BF-09E2-46F7-A42D-C16D337A8C4F@.microsoft.com...
> Yes, execution plans are very similar. Not exactly identical - but all
> components are very similar in costs.
> "Tibor Karaszi" wrote:
>> First step is to check the execution plans. Same?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
>> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
>> > Hello!
>> > We are having a problem with one of the queries that an application runs.
>> > This query uses some temp tables (subbed as table variables with no
>> > performance change) and heavily uses some user defined functions. When run
>> > on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
>> > 2000 Developer Editions), it returns within a second. When run against a
>> > enterprise class server with 8 GB of RAM and at least four processors, maybe
>> > eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
>> > is nothing else going on with the large server. This will be the only
>> > activity on the system.. I disabled awe and turned off /PAE switch with no
>> > change. The timings are repeatable and the only improvement on the
>> > enterprise class server is shortly after a reboot and even then the results
>> > take 2-4 seconds vs, 1 second on the workstation. This performance quickly
>> > deteriorates on the server. I have tried this with the database both on SAN
>> > or on local disk on the server and there is no measurable difference.
>> >
>> > Any ideas on what I should check next?
>> >
>> > Thanks
>>|||Stats IO showed very small differences in number of logical reads. Physical
reads were 0 all around - well cached I guess. I had tried to set the server
to have SQL Server use only 1 processor which should have the same affect as
the MAXDOP setting = 1 (there is some looping and several queries involved
in this proc). Funny thing is that this behavior is repeatable on other
large servers and the quick performance occurs on other workstations.
"Tibor Karaszi" wrote:
> Similar <> same :-)
> What does STATISTICS I/O say. I usually complement execution plans with statistics I/O. Also check
> for parallelism operators in the plan for the large machine. Sometimes parallelism is done with a
> inefficient result, and can be turned off using the MAXDOP hint (set to 1).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> news:348166BF-09E2-46F7-A42D-C16D337A8C4F@.microsoft.com...
> > Yes, execution plans are very similar. Not exactly identical - but all
> > components are very similar in costs.
> >
> > "Tibor Karaszi" wrote:
> >
> >> First step is to check the execution plans. Same?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> >> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
> >> > Hello!
> >> > We are having a problem with one of the queries that an application runs.
> >> > This query uses some temp tables (subbed as table variables with no
> >> > performance change) and heavily uses some user defined functions. When run
> >> > on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> >> > 2000 Developer Editions), it returns within a second. When run against a
> >> > enterprise class server with 8 GB of RAM and at least four processors, maybe
> >> > eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
> >> > is nothing else going on with the large server. This will be the only
> >> > activity on the system.. I disabled awe and turned off /PAE switch with no
> >> > change. The timings are repeatable and the only improvement on the
> >> > enterprise class server is shortly after a reboot and even then the results
> >> > take 2-4 seconds vs, 1 second on the workstation. This performance quickly
> >> > deteriorates on the server. I have tried this with the database both on SAN
> >> > or on local disk on the server and there is no measurable difference.
> >> >
> >> > Any ideas on what I should check next?
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>|||Hmm, I'm out of ideas, I'm afraid. In general, if the execution plan is the same (or cost is very
close), you tend to get the same performance (or a higher on the high-end machine). You could try
posting to the group -relationalserver.performance and see if you get some more attention there. Or
perhaps even time to open a ticket with MS?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve H" <SteveH@.discussions.microsoft.com> wrote in message
news:A24BB7B3-D5EC-40E9-8739-3DB0EB4E9A2C@.microsoft.com...
> Stats IO showed very small differences in number of logical reads. Physical
> reads were 0 all around - well cached I guess. I had tried to set the server
> to have SQL Server use only 1 processor which should have the same affect as
> the MAXDOP setting = 1 (there is some looping and several queries involved
> in this proc). Funny thing is that this behavior is repeatable on other
> large servers and the quick performance occurs on other workstations.
> "Tibor Karaszi" wrote:
>> Similar <> same :-)
>> What does STATISTICS I/O say. I usually complement execution plans with statistics I/O. Also
>> check
>> for parallelism operators in the plan for the large machine. Sometimes parallelism is done with a
>> inefficient result, and can be turned off using the MAXDOP hint (set to 1).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
>> news:348166BF-09E2-46F7-A42D-C16D337A8C4F@.microsoft.com...
>> > Yes, execution plans are very similar. Not exactly identical - but all
>> > components are very similar in costs.
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> First step is to check the execution plans. Same?
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
>> >> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
>> >> > Hello!
>> >> > We are having a problem with one of the queries that an application runs.
>> >> > This query uses some temp tables (subbed as table variables with no
>> >> > performance change) and heavily uses some user defined functions. When run
>> >> > on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
>> >> > 2000 Developer Editions), it returns within a second. When run against a
>> >> > enterprise class server with 8 GB of RAM and at least four processors, maybe
>> >> > eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
>> >> > is nothing else going on with the large server. This will be the only
>> >> > activity on the system.. I disabled awe and turned off /PAE switch with no
>> >> > change. The timings are repeatable and the only improvement on the
>> >> > enterprise class server is shortly after a reboot and even then the results
>> >> > take 2-4 seconds vs, 1 second on the workstation. This performance quickly
>> >> > deteriorates on the server. I have tried this with the database both on SAN
>> >> > or on local disk on the server and there is no measurable difference.
>> >> >
>> >> > Any ideas on what I should check next?
>> >> >
>> >> > Thanks
>> >>
>> >>
>> >>
>>|||Thank you for your help.
"Tibor Karaszi" wrote:
> Hmm, I'm out of ideas, I'm afraid. In general, if the execution plan is the same (or cost is very
> close), you tend to get the same performance (or a higher on the high-end machine). You could try
> posting to the group -relationalserver.performance and see if you get some more attention there. Or
> perhaps even time to open a ticket with MS?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> news:A24BB7B3-D5EC-40E9-8739-3DB0EB4E9A2C@.microsoft.com...
> > Stats IO showed very small differences in number of logical reads. Physical
> > reads were 0 all around - well cached I guess. I had tried to set the server
> > to have SQL Server use only 1 processor which should have the same affect as
> > the MAXDOP setting = 1 (there is some looping and several queries involved
> > in this proc). Funny thing is that this behavior is repeatable on other
> > large servers and the quick performance occurs on other workstations.
> >
> > "Tibor Karaszi" wrote:
> >
> >> Similar <> same :-)
> >> What does STATISTICS I/O say. I usually complement execution plans with statistics I/O. Also
> >> check
> >> for parallelism operators in the plan for the large machine. Sometimes parallelism is done with a
> >> inefficient result, and can be turned off using the MAXDOP hint (set to 1).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> >> news:348166BF-09E2-46F7-A42D-C16D337A8C4F@.microsoft.com...
> >> > Yes, execution plans are very similar. Not exactly identical - but all
> >> > components are very similar in costs.
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> First step is to check the execution plans. Same?
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> >> >> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
> >> >> > Hello!
> >> >> > We are having a problem with one of the queries that an application runs.
> >> >> > This query uses some temp tables (subbed as table variables with no
> >> >> > performance change) and heavily uses some user defined functions. When run
> >> >> > on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> >> >> > 2000 Developer Editions), it returns within a second. When run against a
> >> >> > enterprise class server with 8 GB of RAM and at least four processors, maybe
> >> >> > eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
> >> >> > is nothing else going on with the large server. This will be the only
> >> >> > activity on the system.. I disabled awe and turned off /PAE switch with no
> >> >> > change. The timings are repeatable and the only improvement on the
> >> >> > enterprise class server is shortly after a reboot and even then the results
> >> >> > take 2-4 seconds vs, 1 second on the workstation. This performance quickly
> >> >> > deteriorates on the server. I have tried this with the database both on SAN
> >> >> > or on local disk on the server and there is no measurable difference.
> >> >> >
> >> >> > Any ideas on what I should check next?
> >> >> >
> >> >> > Thanks
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||On Wed, 17 May 2006 13:01:02 -0700, Steve H
<SteveH@.discussions.microsoft.com> wrote:
>We are having a problem with one of the queries that an application runs.
>This query uses some temp tables (subbed as table variables with no
>performance change) and heavily uses some user defined functions. When run
>on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
>2000 Developer Editions), it returns within a second. When run against a
>enterprise class server with 8 GB of RAM and at least four processors, maybe
>eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
>is nothing else going on with the large server. This will be the only
>activity on the system.. I disabled awe and turned off /PAE switch with no
>change. The timings are repeatable and the only improvement on the
>enterprise class server is shortly after a reboot and even then the results
>take 2-4 seconds vs, 1 second on the workstation. This performance quickly
>deteriorates on the server. I have tried this with the database both on SAN
>or on local disk on the server and there is no measurable difference.
>Any ideas on what I should check next?
Dunno, but I think I've got it too, or a close relative.
SP uses some permanent temp tables - tables that I truncate at start
of routine, then fill up with about a million records, perhaps
confusing the optimizer somewhat. Maybe I need to trigger more
recompiles or something? Also uses a simple UDF in the (newest
version) select clause only.
On same machine:
SP runs in ten minutes when it feels like it.
SP runs in twenty minutes other times.
SP runs in about 90 minutes other times.
Data is exactly the same.
Of course, production is seeing the longer times.
Not completely controlled for contending loads, but such as there is
does not explain the 20 to 90 jump in any case. It seems to go CPU
bound for extended periods. Stats IO doesn't show anything outrageous
except for the elapsed time - the time spent in the UDF seems shielded
from stats time, btw, thanks MSFT.
--
Unfortunately we're running this on SQL2K sp2, kind of retro, and
don't ask about the hardware!
But it's very frustrating to have this kind of poor performance and
variability for no apparent reason.
Josh

Friday, March 9, 2012

Inconistent time out error during update operation.

Using our ASP.net application we are getting inconsistent results when we are trying to update a table with more than 15 update queries sequentially.

We are using Merge replication in our SQL server database. On updating one row, it invokes a trigger to update another table and one more trigger for the merge replication. At only few
instances, the update operation fails and it reports "Time out expired" error.

We are not able to simulate this error in the Test environment since it does not have replication. Can any one help me in resolving this issue.

Please advise .

Hi Krithi,

Is this SQL 2000 or SQL 2005?
Would the updates be touch a lot of rows? If so, I can understand the timeout because, it seems like there are multiple triggers here and also replication adds its own triggers on the tables. That bing the case, if the number of rows touched are large, timeouts can occur.

And are you doing the update operations through isqlw/Management Studio?
If so, can you increase the timeout value in the options in isqlw/Management Studio and try again?

Wednesday, March 7, 2012

Incompability between MSSQL2000 SERVER 64 AND 32 BITS

eirs,
I'm using a "MSSQL2000 64 Bits" version, but I need to make some
distributed queries in the "MSSQL2000 32 bits" version. Do you know if there
is any incompatibility between these versions related to the using of the
linked server?
When I try to run a query from SQL 32 bits to 64 bits I have a sucess on
that execution, but when I try to execute query from 64 bits server I have
the message below:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Unspecified error]
[OLE/DB provider returned message: The stored procedure required to complete
this operation could not be found on the server (they were supplied with the
ODBC setup disk for the SQL Server driver). Please contact your system
administrator.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBSchemaRowset::GetRowset
returned 0x80004005:]
Linked Servers use xp .dll's. These are incompatible with the 64-bit OS.
When you call it on the 32-bit box, the .dll's run and the 64-bit responds
to the query.
The 64-bit BOL explains this.
Sincerely,
Anthony Thomas

"Wagner" <Wagner@.discussions.microsoft.com> wrote in message
news:102CA674-9EC2-4E53-ACEC-BB3A01EEC2F1@.microsoft.com...
eirs,
I'm using a "MSSQL2000 64 Bits" version, but I need to make some
distributed queries in the "MSSQL2000 32 bits" version. Do you know if
there
is any incompatibility between these versions related to the using of the
linked server?
When I try to run a query from SQL 32 bits to 64 bits I have a sucess on
that execution, but when I try to execute query from 64 bits server I have
the message below:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Unspecified error]
[OLE/DB provider returned message: The stored procedure required to
complete
this operation could not be found on the server (they were supplied with
the
ODBC setup disk for the SQL Server driver). Please contact your system
administrator.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBSchemaRowset::GetRowset
returned 0x80004005:]

Incompability between MSSQL2000 SERVER 64 AND 32 BITS

eirs,
I'm using a "MSSQL2000 64 Bits" version, but I need to make some
distributed queries in the "MSSQL2000 32 bits" version. Do you know if there
is any incompatibility between these versions related to the using of the
linked server?
When I try to run a query from SQL 32 bits to 64 bits I have a sucess on
that execution, but when I try to execute query from 64 bits server I have
the message below:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Unspecified error]
[OLE/DB provider returned message: The stored procedure required to complete
this operation could not be found on the server (they were supplied with the
ODBC setup disk for the SQL Server driver). Please contact your system
administrator.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBSchemaRowset::GetRowset
returned 0x80004005:]This is a multi-part message in MIME format.
--=_NextPart_000_042C_01C4E169.899D9C80
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
Linked Servers use xp .dll's. These are incompatible with the 64-bit OS.
When you call it on the 32-bit box, the .dll's run and the 64-bit responds
to the query.
The 64-bit BOL explains this.
Sincerely,
Anthony Thomas
"Wagner" <Wagner@.discussions.microsoft.com> wrote in message
news:102CA674-9EC2-4E53-ACEC-BB3A01EEC2F1@.microsoft.com...
eirs,
I'm using a "MSSQL2000 64 Bits" version, but I need to make some
distributed queries in the "MSSQL2000 32 bits" version. Do you know if
there
is any incompatibility between these versions related to the using of the
linked server?
When I try to run a query from SQL 32 bits to 64 bits I have a sucess on
that execution, but when I try to execute query from 64 bits server I have
the message below:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Unspecified error]
[OLE/DB provider returned message: The stored procedure required to
complete
this operation could not be found on the server (they were supplied with
the
ODBC setup disk for the SQL Server driver). Please contact your system
administrator.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBSchemaRowset::GetRowset
returned 0x80004005:]
--=_NextPart_000_042C_01C4E169.899D9C80
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Linked Servers use xp .dll's. =These are incompatible with the 64-bit OS. When you call it on the 32-bit =box, the .dll's run and the 64-bit responds to the query.
The 64-bit BOL explains =this.
Sincerely,
Anthony Thomas
--
"Wagner" wrote in message news:102=CA674-9EC2-4E53-ACEC-BB3A01EEC2F1@.microsoft.com...eirs,I'm = using a "MSSQL2000 64 Bits" version, but I need to make some distributed queries in the "MSSQL2000 32 bits" version. Do you =know if there is any incompatibility between these versions related to the =using of the linked server?When I try to run a query from SQL 32 =bits to 64 bits I have a sucess on that execution, but when I try to execute =query from 64 bits server I have the message below:Server: Msg 7399, =Level 16, State 1, Line 1OLE DB provider 'SQLOLEDB' reported an =error. [OLE/DB provider returned message: Unspecified error][OLE/DB =provider returned message: The stored procedure required to complete this =operation could not be found on the server (they were supplied with the ODBC =setup disk for the SQL Server driver). Please contact your system administrator.]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBSchemaRowset::GetRowset returned 0x80004005:]

--=_NextPart_000_042C_01C4E169.899D9C80--

Sunday, February 19, 2012

in vs. exists

What is the difference between in and exists performance wise? I have
tuned(aatempted to tune) queries where a subselect was introduced with an in
.
I have replaced th in with an exists and found the performance and plan to
be the same.> What is the difference between in and exists performance wise?
It depends on the selectivity of the column(s) being checked.
IN() can never perform better than EXISTS(). EXISTS() *can* perform better
since it will "short circuit" (loose definition here) as soon as it sees a
single row that satisfies the condition. IN(), on the other hand, has to
continue resolving the entire query.|||Hi,
In some situation EXISTS will be good and some times IN will be good.
When Exists is good:-
If your second table (Table used inside inner table) contains more distinct
values (High cardinality) then EXISTS will be good.
When IN is good :-
If your second table (Table used inside inner table) contains more duplicate
values (Low cardinality) then IN will be good.
Thanks
Hari
SQL Server MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:5F6D97A3-94A2-4236-A467-13D53496A726@.microsoft.com...
> What is the difference between in and exists performance wise? I have
> tuned(aatempted to tune) queries where a subselect was introduced with an
> in.
> I have replaced th in with an exists and found the performance and plan to
> be the same.|||> When IN is good :-
> If your second table (Table used inside inner table) contains more
> duplicate values (Low cardinality) then IN will be good.
I may be mistaken, but I don't believe there is any case where IN can
perform better than an equivalent query using either EXISTS() or NOT
EXISTS().|||>> What is the difference between in and exists performance wise?
Nothing. These are simply SQL operators asking its difference would be like
asking the performance difference between say, addition and subtraction.
Without testing your queries under the specific setup ( indexes, dataset
size, hints, processor involvements, memory etc. ) you have, it is
impossible to consistently claim one performs better than the other.
For a very small illustration, check the plans for two queries with no
"actual" disk access performs for NOT IN & NOT EXISTS:
SELECT *
FROM ( SELECT 1 UNION SELECT 2 UNION
SELECT 3 UNION SELECT 4 ) t1 ( c )
WHERE t1.c NOT IN ( SELECT t2.c FROM ( SELECT 1 ) t2 ( c ) ) ;
SELECT *
FROM ( SELECT 1 UNION SELECT 2 UNION
SELECT 3 UNION SELECT 4 ) t1 ( c )
WHERE NOT EXISTS ( SELECT * FROM ( SELECT 1 ) t2 ( c )
WHERE t2.c = t1.c ) ;
Simpy change the number of union-ed items in the derived tables and see how
the plan varies.
Anith|||I used to see such differences in the plans in 7.0, but in 2000 IN is
usually treated like EXISTS, where a similar short circuit you're referring
to is used also for IN.
Check out the plans and io stats for both queries:
set statistics io on;
use northwind;
select * from employees as e
where exists
(select * from orders as o
where o.employeeid = e.employeeid);
select * from employees as e
where employeeid in
(select employeeid from orders);
And you will see that in both cases the plans perform a single s

operation in the index on orders.employeeid for each outer employee.
The index is not fully scanned to retrieve the list of employees. The
optimizer is more sofisticated now.
I'd just add a caution when considering whether to use EXISTS or IN, that
they are not logically identical when NULLs are involved.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uY3zMUfmFHA.4056@.TK2MSFTNGP10.phx.gbl...
> It depends on the selectivity of the column(s) being checked.
> IN() can never perform better than EXISTS(). EXISTS() *can* perform
> better since it will "short circuit" (loose definition here) as soon as it
> sees a single row that satisfies the condition. IN(), on the other hand,
> has to continue resolving the entire query.
>|||Jeffrey,
That is my experience too (that SQL2K handles IN as efficiently as
EXISTS).
In theory there are cases in which IN would be faster than EXISTS (for
example when the selection in the IN-clause has many duplicates).
However, in practice the query optimizer is smart enough to determine
whether the IN strategy (selection with aggregation to remove
duplicates) or the EXISTS (lookups) is faster, regardless whether you
specify IN or EXISTS.
Gert-Jan|||I agree. Post your DDL along with some sample data and we can help you find
the optimal solution for your specific situation.
Aincent Romans created a name for this approach: casuistic (by case). And
it's still used today... :)
ML|||> In theory there are cases in which IN would be faster than EXISTS (for
> example when the selection in the IN-clause has many duplicates).
How? In would have duplicates too? I always use EXISTS because it is just
as easy to code and you never have to worry about the fact that IN only
works with single values and EXISTS covers all.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42F3F200.3BB4CE81@.toomuchspamalready.nl...
> Jeffrey,
> That is my experience too (that SQL2K handles IN as efficiently as
> EXISTS).
> In theory there are cases in which IN would be faster than EXISTS (for
> example when the selection in the IN-clause has many duplicates).
> However, in practice the query optimizer is smart enough to determine
> whether the IN strategy (selection with aggregation to remove
> duplicates) or the EXISTS (lookups) is faster, regardless whether you
> specify IN or EXISTS.
> Gert-Jan|||It is just academic because (as explained before) the optimizer will
select the best plan regardless of the IN/EXISTS syntax. But here it
goes...
For an IN subquery, only the unique values are relevant, so under the
covers the query will contain the DISTINCT keyword. If there are very
few distinct values, this will result in a set of hashing keys which is
is very cheap to match against the main query and will use very limited
memory (very compact).
The same query with an EXISTS would have to do a lookup for each row of
the main query, so there will be many logical reads. If there is a good
index in place for the lookups in the correlated subquery (and again
assuming few distinct values), then in practice you will get good
performance too, because all relevant pages are likely to be cached.
However, if the key is narrow and the distinct values are scattered over
the pages (with on average one distinct key per page), then the EXISTS
version will require much more memory to get the same performance as the
IN-variant, because on average each (cached) page will only contain one
distinct key, whereas the hash set of the IN version is a very compact
set, not dependant on the page cache.
The IN version will have to read all those pages too, but only needs to
do that once, something which is not true for the EXISTS version.
If there is no useful index for the subquery, then theoretically the
EXISTS version will run like a dog (a table scan for each lookup), and
the IN version will need 'just' one table scan.
Gert-Jan
Louis Davidson wrote:
>
> How? In would have duplicates too? I always use EXISTS because it is jus
t
> as easy to code and you never have to worry about the fact that IN only
> works with single values and EXISTS covers all.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:42F3F200.3BB4CE81@.toomuchspamalready.nl...

IN SQL Server Management Studio - Can connect to database sort of but Icon isn't green anymore

Preface: I'm a newbie at 2005 and sql server in general.

I can connect to a sql server (2005 express) but I can only make views and not run queries. When I open up a new query and create it with query designer and click add table I see no tables, views or anything in the box. When I create a new view in the database I do see tables and views though.

The little circle part of the icon on the server in the left hand pane is clear now when before it was green. I still can connect to the database ok but I can't use the query analyzer for queries anymore. I need to do that because I do have some slow running queries.

~Capt howdy

Select the database first (ie:AdventureWorks), 'new query' and then click design view.

|||I'm such a n00b with sql server it hurts sometimes. Thanks! I am used to access but it is definitely time to step up.

~Capt. Howdy

IN SQL Server Management Studio - Can connect to database sort of but Icon isn't green anym

Preface: I'm a newbie at 2005 and sql server in general.

~Capt howdy

Select the database first (ie:AdventureWorks), 'new query' and then click design view.

|||I'm such a n00b with sql server it hurts sometimes. Thanks! I am used to access but it is definitely time to step up.

~Capt. Howdy

Wednesday, March 21, 2012

Incorrect query results with embedded comments

Monday, March 12, 2012

Inconsistent Query Performance

Inconsistent Query Performance

Friday, March 9, 2012

Inconistent time out error during update operation.

Wednesday, March 7, 2012

Incompability between MSSQL2000 SERVER 64 AND 32 BITS

Incompability between MSSQL2000 SERVER 64 AND 32 BITS

Sunday, February 19, 2012

in vs. exists

IN SQL Server Management Studio - Can connect to database sort of but Icon isn't green anymore

IN SQL Server Management Studio - Can connect to database sort of but Icon isn't green anym

sqlserver2000 date

Blog Archive

About Me