sql bit data type: bizarre

Showing posts with label bizarre. Show all posts

Sunday, February 12, 2012

Bizarre zero suppression on print

Hi All,

I have a report (using CR XI) that uses conditional suppression.

Simple stuff, it prints out a few data values if at least one of the values is not null but prints a row of text (made up of the appropriate number of .00s in the right places) if all data values for the line are null.

In print preview on the screen it works perfectly but when I print it out, all the zeroes disappear and all that is left is the dot!

In case anyone is wondering why I doing it this way at all and not simply unselecting the suppress when zero checkbox it is because this report is a subreport of another report and if all the data values are null in the sub then the main just has a blank patch (rather than zeroes).

Anyway, any ideas re how to get the printer to print out what the preview says it will?

Thanks,

HywelCHello again,

I tried to export the report as a PDF and that works perfectly too. The problem just seems to be with the printer...very odd.

HywelC|||Did you set the page setup correctly?

Bizarre Stored Procedure Behavior

I have dozens of stored procedures, but one of them is suddenly behaving badly.

Basically, if the sp is run as a query (full text), it runs fine and finishes successfully, but if 'executed' (Exec [sp_name]) it fails, saying that one of the referenced tables doesn't exist. The really weird thing is that the table is created earlier in the same procedure and is recognized enough to allow an index to be created on it. But when it comes to an Update statement, forget it. The sp dies saying the object (table) is unrecognized. I have dropped and recreated it, tried everything I can think of.

Has anyone else experienced this? Any ideas what may be causing it?

I'm stumped.

Thanks!you do have the correct names on your command object of the update statement?

Bizarre SQL statement

I have a SQL UNION (I know I know...they're bad, but trust me, I have to us
e
it) in a stored proc.
Effectively, it liiks like this:
SELECT f1, f2, f3 FROM T1
UNION
SELECT f1, f2, f3 FROM T2
note:
T1 and T2 are not tables, but parameterized query expressions that contain 3
and 4 tables, respectively.
If I just run "SELECT f1, f2, f3 FROM T1" I get 0 results in 2 or 3 seconds
If I just run "SELECT f1, f2, f3 FROM T2" I get 31 results in less than 1
second
If I run:
SELECT f1, f2, f3 FROM T1
UNION
SELECT f1, f2, f3 FROM T2
I wait 28 minutes and nothing happens...
I know that unions deprecate performance, but this is REALLY strange.
Also, this only happens for certian parameters. But its the same SQL that's
running...
What the heck is going on'David Jessee wrote:
> I have a SQL UNION (I know I know...they're bad, but trust me, I have to
use
> it) in a stored proc.
> Effectively, it liiks like this:
> SELECT f1, f2, f3 FROM T1
> UNION
> SELECT f1, f2, f3 FROM T2
> note:
> T1 and T2 are not tables, but parameterized query expressions that contain
3
> and 4 tables, respectively.
> If I just run "SELECT f1, f2, f3 FROM T1" I get 0 results in 2 or 3 second
s
> If I just run "SELECT f1, f2, f3 FROM T2" I get 31 results in less than 1
> second
> If I run:
> SELECT f1, f2, f3 FROM T1
> UNION
> SELECT f1, f2, f3 FROM T2
> I wait 28 minutes and nothing happens...
> I know that unions deprecate performance, but this is REALLY strange.
> Also, this only happens for certian parameters. But its the same SQL that
's
> running...
> What the heck is going on'
try UNION ALL just to see if that helps. Maybe the optimizer in
whatever DB you're using (you didn't say) is trying to do stuff so the
sort is 'faster'|||examnotes <DavidJessee@.discussions.microsoft.com>
wrote in news:F951CFD8-F105-46FB-8D2E-0752C6399B78@.microsoft.com:

> I have a SQL UNION (I know I know...they're bad, but trust me, I have
> to use it) in a stored proc.
> Effectively, it liiks like this:
> SELECT f1, f2, f3 FROM T1
> UNION
> SELECT f1, f2, f3 FROM T2
> note:
> T1 and T2 are not tables, but parameterized query expressions that
> contain 3 and 4 tables, respectively.
Does "T1" and "T2" share some of these tables? If so, are you sure that you
are not experiencing a deadlock? This is not my strongest field, but as far
as I've understood, the two statements that are merged with a union may
actually run in parallell. If some of the values passed to the
parameterized causes a table-lock on a shared table, you could experience
the behavior you describe, or am I completely wrong here?
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||The individual queries are utilizing some of the same tables. However, the
tables are references "WITH (NOLOCK)" so deadlocking shouldn't be an issue,
should it?
"Ole Kristian Bang?s" wrote:

> examnotes <DavidJessee@.discussions.microsoft.com>
> wrote in news:F951CFD8-F105-46FB-8D2E-0752C6399B78@.microsoft.com:
>
> Does "T1" and "T2" share some of these tables? If so, are you sure that yo
u
> are not experiencing a deadlock? This is not my strongest field, but as fa
r
> as I've understood, the two statements that are merged with a union may
> actually run in parallell. If some of the values passed to the
> parameterized causes a table-lock on a shared table, you could experience
> the behavior you describe, or am I completely wrong here?
> --
> Ole Kristian Bang?s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>|||Well, I'm using a SQL Server database (this snippet of code is inside of a
stored procedure).I'll try the UNION ALL to see if it works. Truth to tell,
I'm hoping it doesn't because each of thequeries can potentially return
duplicate records. Rach of them have DISTINCT clauses in them already. For
this query, placing DISTINCT in each of the queries and them UNIONING them
gives better performance than just relying on the UNOIN to remove duplicates
.
(not my data model...but I have to work with it *ack*)
"wolfing1@.gmail.com" wrote:

> David Jessee wrote:
> try UNION ALL just to see if that helps. Maybe the optimizer in
> whatever DB you're using (you didn't say) is trying to do stuff so the
> sort is 'faster'
>|||examnotes <DavidJessee@.discussions.microsoft.com>
wrote in news:D0476075-74A5-4890-A48B-3048292DE2BD@.microsoft.com:

> The individual queries are utilizing some of the same tables.
> However, the tables are references "WITH (NOLOCK)" so deadlocking
> shouldn't be an issue, should it?
I don't remember when, but I seem to remember that Kimberly Tripp had a
TechNet session or so regarding this, where she specified that even with
nolock table locks can occur. But, as I've said, I do not remember in what
situation.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Are you familiar with query and join hints? Take a look at the execution
plan generated by each individual SELECT in the UNION and try coercing the
optimizer to match the execution plan. Remember also that a UNION requires
a sort of the result set from each SELECT in order to eliminate duplicates.
You could try this:
SELECT DISNTINCT f1, f2, f3 FROM
( SELECT f1, f2, f3 FROM T1
UNION ALL
SELECT f1, f2, f3 FROM T2 ) T3
By the way, I hope you're aware of the dangers of using WITH(NOLOCK).
Except in rare circumstances, WITH(NOLOCK) will make your queries return
incorrect results at lightning speed.
"David Jessee" <DavidJessee@.discussions.microsoft.com> wrote in message
news:D0476075-74A5-4890-A48B-3048292DE2BD@.microsoft.com...
> The individual queries are utilizing some of the same tables. However,
> the
> tables are references "WITH (NOLOCK)" so deadlocking shouldn't be an
> issue,
> should it?
> "Ole Kristian Bangs" wrote:
>

Bizarre sql management studio problem...cant see server status for

This one has me stumped.
Basically when I open sql management studio 2005 and connect to one of
my servers, the little server status icon adjacent to the server name
contains a blank circle. Normally it contains a small green triangle
to indicate the server is running. If I right click on the server
name, all the start/stop/pause/resume etc options are greyed out. This
is very confusing for the following reasons:
- I am logged on as 'sa' and all my other permissions work.
- Other logins (both sql server and windows authentication) show the
same issue
- Connecting to other database servers works fine (and shows the
correct status icon)
I originally thought it was some bizarre permissions issue, however if
I am logged directly into the server start the sql management tool
there using the same database login, the server shows the 'started'
green triangle and the start/stop/pause/resume etc options are
available.
This issue appears to have occurred randomly, I can not think of
anything I have done recently that involved changing permissions or
anything like that.
Any ideas?The connection as sa is a regular SQL Server connection. The
indicator that shows that the server is running is not updated based
on making a SQL Server connection; if you open SSMS without connecting
to any SQL Server the status of the servers should show.
The indication that SQL Server is running, or not, is based on SSMS
finding out if the service is running. That query is made to the
server's operating system, and is based on the rights of the Windows
account you are logged in with. For the servers that are displayed
with the status you have rights, for the others you do not.
I admit to a bit of guesswork in the above, but I think it is the
answer.
Roy Harvey
Beacon Falls, CT
On Fri, 11 Jan 2008 17:16:50 -0800 (PST), Alex Danger
<alex.csp@.gmail.com> wrote:
>This one has me stumped.
>Basically when I open sql management studio 2005 and connect to one of
>my servers, the little server status icon adjacent to the server name
>contains a blank circle. Normally it contains a small green triangle
>to indicate the server is running. If I right click on the server
>name, all the start/stop/pause/resume etc options are greyed out. This
>is very confusing for the following reasons:
>
>- I am logged on as 'sa' and all my other permissions work.
>- Other logins (both sql server and windows authentication) show the
>same issue
>- Connecting to other database servers works fine (and shows the
>correct status icon)
>I originally thought it was some bizarre permissions issue, however if
>I am logged directly into the server start the sql management tool
>there using the same database login, the server shows the 'started'
>green triangle and the start/stop/pause/resume etc options are
>available.
>This issue appears to have occurred randomly, I can not think of
>anything I have done recently that involved changing permissions or
>anything like that.
>Any ideas?

Bizarre slow query problem (again)

I previously posted about a problem where it seemed that changing the
case of the word "BY" in a SELECT query was causing it to run much much
faster.

Now I've hit the same thing again, where basically almost any change I
make to how the query is executed (so that it still performs the same
function) causes the performance to jump from a dismal 7 or 8 seconds
to instantaneous. It's a very simple query of the form:

SELECT Min(MyValue) FROM MyTable WHERE MyKey = @.0

which was running fine until a moment ago, when it suddently started
running hopelessly slowly. If change anything in the query to
lowercase (or the Min to uppercase), it runs fine again. Last time
someone suggested something about a bad plan being cached, and after a
bit of research I found the commands DBCC DROPCLEANBUFFERS and DBCC
FREEPROCCACHE. Sure enough, after running these, the query started
running fine again. The question is

a) why is this happening? Is it a bug in my code, or in SQL server?
b) is it worth detecting it and fixing it automatically? I.e, should I
put some code in that notices that a query is running far too slowly,
then runs "DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE"? Or will that
cause other problems?

ThanksHi
http://blogs.msdn.com/khen1234/arch.../02/424228.aspx
<wizofaus@.hotmail.comwrote in message
news:1168494297.719888.324130@.77g2000hsv.googlegro ups.com...

Quote:

Originally Posted by

>I previously posted about a problem where it seemed that changing the
case of the word "BY" in a SELECT query was causing it to run much much
faster.
>
Now I've hit the same thing again, where basically almost any change I
make to how the query is executed (so that it still performs the same
function) causes the performance to jump from a dismal 7 or 8 seconds
to instantaneous. It's a very simple query of the form:
>
SELECT Min(MyValue) FROM MyTable WHERE MyKey = @.0
>
which was running fine until a moment ago, when it suddently started
running hopelessly slowly. If change anything in the query to
lowercase (or the Min to uppercase), it runs fine again. Last time
someone suggested something about a bad plan being cached, and after a
bit of research I found the commands DBCC DROPCLEANBUFFERS and DBCC
FREEPROCCACHE. Sure enough, after running these, the query started
running fine again. The question is
>
a) why is this happening? Is it a bug in my code, or in SQL server?
b) is it worth detecting it and fixing it automatically? I.e, should I
put some code in that notices that a query is running far too slowly,
then runs "DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE"? Or will that
cause other problems?
>
Thanks
>

|||Uri Dimant wrote:

Quote:

Originally Posted by

Hi
http://blogs.msdn.com/khen1234/arch.../02/424228.aspx
>

SELECT Min(MyValue) FROM MyTable WHERE MyKey = @.0, with @.0 = 999
followed by
SELECT Min(MyValue) FROM MyTable WHERE MyKey = @.0, with @.0 = 123

Caused the second query to run absurdly slowly, because in the first
case only very few rows in the table had MyKey = 999 whereas almost
every row had MyKey = 123. After doing a DBCC FREEPROCCACHE and
swapping the queries around, they both ran fine.

In the end I ended up de-parameterizing the query just for this case,
but now I'm worried - how can I be sure that my other queries won't
suffer from the same problem? Should I never use parameters because of
this possibility?|||In the end I ended up de-parameterizing the query just for this case,

Quote:

Originally Posted by

but now I'm worried - how can I be sure that my other queries won't
suffer from the same problem? Should I never use parameters because of
this possibility?
>

An ability using parameters is very powerful , don't afraid using parameters
, just test it carefuly

<wizofaus@.hotmail.comwrote in message
news:1168509843.760497.149900@.i56g2000hsf.googlegr oups.com...

Quote:

Originally Posted by

Uri Dimant wrote:

Quote:

Originally Posted by

>Hi
>http://blogs.msdn.com/khen1234/arch.../02/424228.aspx
>>

Thanks for that...amazingly enough it turned that that was exactly my
problem, although I'm using ad-hoc queries rather than stored procs. I
did some more testing, and it turned out that it was because it was
executing the same query twice, the first time with an atypical
parameter value, and the second time with a more typical one, that the
query was running so slowly. That is, executing
>
SELECT Min(MyValue) FROM MyTable WHERE MyKey = @.0, with @.0 = 999
followed by
SELECT Min(MyValue) FROM MyTable WHERE MyKey = @.0, with @.0 = 123
>
Caused the second query to run absurdly slowly, because in the first
case only very few rows in the table had MyKey = 999 whereas almost
every row had MyKey = 123. After doing a DBCC FREEPROCCACHE and
swapping the queries around, they both ran fine.
>
In the end I ended up de-parameterizing the query just for this case,
but now I'm worried - how can I be sure that my other queries won't
suffer from the same problem? Should I never use parameters because of
this possibility?
>

|||Uri Dimant wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

An ability using parameters is very powerful , don't afraid using parameters
, just test it carefuly
>

Sure, except that the content of the database is out of my control -
this particular scenario (where nearly all the records matched a
particular key, but the query was first run against a different key)
could easily arrise in a production environment. More to the point,
I've seen no evidence that I'm getting any performance benefits from
using parameterized queries.
I suspect I will at least add a configuration option to avoid
parameterized queries (relatively straightforward, as I have a layer of
code that handles query parameters) if I see a problem like this again.|||wizofaus@.hotmail.com wrote:

Quote:

Originally Posted by

Uri Dimant wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>>In the end I ended up de-parameterizing the query just for this
>>case,

"de-parameterizing"? You mean changing to dynamic sql and leaving yourself
vulnerable to sql injection??

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>but now I'm worried - how can I be sure that my other queries won't
>>suffer from the same problem? Should I never use parameters
>>because of this possibility?
>>>

>An ability using parameters is very powerful , don't afraid using
>parameters , just test it carefuly
>>

Sure, except that the content of the database is out of my control -
this particular scenario (where nearly all the records matched a
particular key, but the query was first run against a different key)
could easily arrise in a production environment. More to the point,
I've seen no evidence that I'm getting any performance benefits from
using parameterized queries.
I suspect I will at least add a configuration option to avoid
parameterized queries (relatively straightforward, as I have a layer
of
code that handles query parameters) if I see a problem like this
again.

This is a ridiculous overreaction. Problems due to parameter-sniffing are
too rare to justify eliminating the benefits of using parameters. Talk about
"throwing the baby out with te bath water".

The article showed two, no three, ways to alleviate the problems caused by
parameter sniffing and still use parameters. So what do you do? ignore the
article's advice and "de-parameterize" your query...

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||a) why is this happening? Is it a bug in my code, or in SQL server?

Quote:

Originally Posted by

b) is it worth detecting it and fixing it automatically? I.e, should I
put some code in that notices that a query is running far too slowly,
then runs "DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE"? Or will that
cause other problems?

Pardon me but

Why did you not say which version of SQL Server you are running on?
And why not mention the version of MDAC you have installed (since you posted
to microsoft.public.data.ado)?
And why not mention something about the Primary Keys & Indexes of the table
you are querying - I take it you have an index on MyKey?
And have you checked the documentation, whitepapers, MSDN for details on
query performance?
And have you checked the query plan in Query Analyser?

Cheers

Stephen Howe|||Bob Barrows [MVP] wrote:

Quote:

Originally Posted by

wizofaus@.hotmail.com wrote:

Quote:

Originally Posted by

Uri Dimant wrote:

Quote:

Originally Posted by

>In the end I ended up de-parameterizing the query just for this
>case,

>
"de-parameterizing"? You mean changing to dynamic sql and leaving yourself
vulnerable to sql injection??

No, because the parameter values are fully under my control - they are
not submitted directly by the user. At any rate, in this case it's
always just a simple integer.

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>but now I'm worried - how can I be sure that my other queries won't
>suffer from the same problem? Should I never use parameters
>because of this possibility?
>>
An ability using parameters is very powerful , don't afraid using
parameters , just test it carefuly
>

Sure, except that the content of the database is out of my control -
this particular scenario (where nearly all the records matched a
particular key, but the query was first run against a different key)
could easily arrise in a production environment. More to the point,
I've seen no evidence that I'm getting any performance benefits from
using parameterized queries.
I suspect I will at least add a configuration option to avoid
parameterized queries (relatively straightforward, as I have a layer
of
code that handles query parameters) if I see a problem like this
again.

>
This is a ridiculous overreaction. Problems due to parameter-sniffing are
too rare to justify eliminating the benefits of using parameters. Talk about
"throwing the baby out with te bath water".

Well, yes, but as I said, the testing I've done has revealed that my
app definitely is suffering badly from parameter-sniffing problems
(I've come across yet another one since), and that the only reliable
way I've found to solve it is to NOT use parameters, which doesn't seem
to be adversely affecting performance.

Quote:

Originally Posted by

>
The article showed two, no three, ways to alleviate the problems caused by
parameter sniffing and still use parameters. So what do you do? ignore the
article's advice and "de-parameterize" your query...

Yes, but they use stored procs. I'm trying to avoid stored procs in
order to keep RDBMS independence (although for the time being, we've no
immediate need to support other databases).|||wizof...@.hotmail.com wrote:

Quote:

Originally Posted by

>
Yes, but they use stored procs. I'm trying to avoid stored procs in
order to keep RDBMS independence (although for the time being, we've no
immediate need to support other databases).

I would not do that unless there is a very strong business reason to do
so.
IMO maintaining RDBMS independence is like living in an RV instead of
living in a house - you get less comfort for your money, you pay a high
price for your mobility.
Achieving true RDBMS independence is both complex and expensive.
Details here

http://www.devx.com/dbzone/Article/32852
Under most circumstances I would settle down and use SQL Server
proprietary features to get the biggest bang for my buck. Should a need
arise to move - I would move and settle down again.

--------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||On 11 Jan 2007 08:35:45 -0800, "Alex Kuznetsov"
<AK_TIREDOFSPAM@.hotmail.COMwrote:

Quote:

Originally Posted by

>IMO maintaining RDBMS independence is like living in an RV instead of
>living in a house - you get less comfort for your money, you pay a high
>price for your mobility.

I like it!

Roy Harvey
Beacon Falls, CT|||IMO maintaining RDBMS independence is like living in an RV instead of

Quote:

Originally Posted by

living in a house - you get less comfort for your money, you pay a high
price for your mobility.
Achieving true RDBMS independence is both complex and expensive.

Hahaha, now that is picture that will never make it in any of Joe
Celko's books :-)

Gert-Jan|||be EXTREMELY wary when using DBCC DROPCLEANBUFFERS and DBCC
FREEPROCCACHE on a production machine. They clear all cached SPs,
queries and plans. The instance is bound to run under extreme stress
for a considerable amount of time.

wizof...@.hotmail.com wrote:

Quote:

Originally Posted by

I previously posted about a problem where it seemed that changing the
case of the word "BY" in a SELECT query was causing it to run much much
faster.
>
Now I've hit the same thing again, where basically almost any change I
make to how the query is executed (so that it still performs the same
function) causes the performance to jump from a dismal 7 or 8 seconds
to instantaneous. It's a very simple query of the form:
>
SELECT Min(MyValue) FROM MyTable WHERE MyKey = @.0
>
which was running fine until a moment ago, when it suddently started
running hopelessly slowly. If change anything in the query to
lowercase (or the Min to uppercase), it runs fine again. Last time
someone suggested something about a bad plan being cached, and after a
bit of research I found the commands DBCC DROPCLEANBUFFERS and DBCC
FREEPROCCACHE. Sure enough, after running these, the query started
running fine again. The question is
>
a) why is this happening? Is it a bug in my code, or in SQL server?
b) is it worth detecting it and fixing it automatically? I.e, should I
put some code in that notices that a query is running far too slowly,
then runs "DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE"? Or will that
cause other problems?
>
Thanks

|||swaroop.a...@.gmail.com wrote:

Quote:

Originally Posted by

be EXTREMELY wary when using DBCC DROPCLEANBUFFERS and DBCC
FREEPROCCACHE on a production machine. They clear all cached SPs,
queries and plans. The instance is bound to run under extreme stress
for a considerable amount of time.
>

The instance? You mean it affects all databases?
In this case, I determined I'd have to do it before every single query
call, so obviously that's not practical.|||(wizofaus@.hotmail.com) writes:

Quote:

Originally Posted by

Which version of SQL Server are you using? Here is a test that you can
try to see that you can do to actually test the benefit of
parameterised queries. First create this database:

CREATE DATABASE many_sps
go
USE many_sps
go
DECLARE @.sql nvarchar(4000),
@.x int
SELECT @.x = 1000
WHILE @.x 0
BEGIN
SELECT @.sql = 'CREATE PROCEDURE abc_' + ltrim(str(@.x)) +
'_sp @.orderid int AS
SELECT O.OrderID, O.OrderDate, O.CustomerID, C.CompanyName,
Prodcnt = OD.cnt, Totalsum = OD.total
FROM Northwind..Orders O
JOIN Northwind..Customers C ON O.CustomerID = C.CustomerID
JOIN (SELECT OrderID, cnt = COUNT(*), total = SUM(Quantity * UnitPrice)
FROM Northwind..[Order Details]
GROUP BY OrderID) AS OD ON OD.OrderID = O.OrderID
WHERE O.OrderID = @.orderid'
EXEC(@.sql)
SELECT @.x = @.x - 1
END

(Don't worry if you don't have Northwind on your server, you are not going
to run these procedures.)

Then use F7 to get the Summary page, and navigate to the Stored Procedures
node for many_sps. Select some 200 procedures, right-click and select
Script As Create To New Query Window. Go for a cup of coffee - this will
take some time depending on your hardware.

When the operation has completed (or you have gotten tired of waiting
and killed SSMS), issue this command:

ALTER DATABASE db SET PARAMETERIZATION FORCED

Redo the scripting operation. It will now complete in five seconds.

The reason for this is that SQL Server Management Studio does not use
parameterised queries. For every procedure it scripts, Mgmt Studio
issues around five queries. All these queries makes it to the
cache that explodes, and all these queries are compiled.

So dismissing caching of parameterised queries can be a serious mistake.
But it is certainly true that there are situations where parameter
sniffing can be a problem. If it is possible for you to tell in the
appliocation "this is an odd value that needs a special plan", then
you can modify the query text by adding a redudant condition like
"AND 1 = 1". Actually as you have found, changing "BY" to "by" or even
adding extra spaces help. This is because the lookup in the cache
is done on a hash without first collapsing spaces or parsing the
query text.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog (esquel@.sommarskog.se) writes:

Quote:

Originally Posted by

When the operation has completed (or you have gotten tired of waiting
and killed SSMS), issue this command:
>
ALTER DATABASE db SET PARAMETERIZATION FORCED
>
Redo the scripting operation. It will now complete in five seconds.

By the way, this is something important for your application as well.
Say that a DBA finds out that your app is thrashing the cache by not
using parameterised queries, and sets the database to forced
parameterisation, you will get back the behaviour you have now.

A bettery remedy is to add OPTION (RECOMPILE) at the end of sensitive
queries. This forces a statement recompile, and the query will not be
put in cache. This means that you can still use parameterised queries
and get the other benefits of it. (Protection for SQL injection and
repsecting the user's regional settings.) You also avoid thrashing
the cache.

Quote:

Originally Posted by

When you set a database to forced parameterisation, SQL Server will
auto-parameterise all statements (with some exceptions documented in
Books Online); normally it only auto-parameterise very simple queries.
In the case of Mgmt Studio it's reallly a go-faster switch.
>
So dismissing caching of parameterised queries can be a serious mistake.
But it is certainly true that there are situations where parameter
sniffing can be a problem. If it is possible for you to tell in the
appliocation "this is an odd value that needs a special plan", then
you can modify the query text by adding a redudant condition like
"AND 1 = 1". Actually as you have found, changing "BY" to "by" or even
adding extra spaces help. This is because the lookup in the cache
is done on a hash without first collapsing spaces or parsing the
query text.
>

Thanks...one of the most helpful replies I've had on usenet for some
time now!

The problem is that it's pretty hard for me to know that a value is
"odd". In this case, like I said the query in this case is

SELECT Min(MyValue) FROM MyTable WHERE MyKey = @.0

Where @.0 is actually a value from a list that is obtained from
elsewhere (not the database). It loops through this list, calling the
same query for each one.

Now, I suppose I could first do

SELECT Count(*) FROM MyTable WHERE MyKey = @.0

and determine if the number was very low, and if so, de-parameterize it
or add a space or whatever, but then this second query would
potentially suffer from the same problem.

I suppose another alternative is to build another query first

SELECT MyKey, Min(MyValue) FROM MyTable GROUP BY MyKey

then use this to obtain the minimum value for each key, but there's
only so much time I can spend rewriting queries to side-step quirky
performance issues (the application has many many ad-hoc queries,
nearly all parameterized on the same key, so they are all prone to the
same problem).

BTW, this is under SQL server 2000. I've yet to determine if this
particular problem does actually exist under 2005, which is what we're
using for the production environment. Will definitely do that before I
waste too much more time on it.|||(wizofaus@.hotmail.com) writes:

Quote:

Originally Posted by

Thanks...one of the most helpful replies I've had on usenet for some
time now!
>
The problem is that it's pretty hard for me to know that a value is
"odd".

I can understand that this is not always simple. I didn't say this, in
hope it would be. :-)

However, I think I have a cure for you:

Quote:

Originally Posted by

In this case, like I said the query in this case is
>
SELECT Min(MyValue) FROM MyTable WHERE MyKey = @.0
>
Where @.0 is actually a value from a list that is obtained from
elsewhere (not the database). It loops through this list, calling the
same query for each one.

Stop! Don't do that! The problems with query plans aside, this is an
ineffecient use of SQL Server. Get all data at once with:

SELECT t.MyKey, Min(t.MyValue)
FROM MyTable t
JOIN list_to_table(@.list) f ON t.MyKey = f.value
GROUP BY t.MyKey

Where list_to_table is a table-valued function that transform the list
to a table. I have a whole bunch of such functions on
http://www.sommarskog.se/arrays-in-sql.html.

You forget in the list, the version of the framework that is used. It is to
AdoNet as well you know.

:-)

Cor

"Stephen Howe" <stephenPOINThoweATtns-globalPOINTcomschreef in bericht
news:%23kg1j%23YNHHA.992@.TK2MSFTNGP04.phx.gbl...

Quote:

Originally Posted by

Quote:

Originally Posted by

>a) why is this happening? Is it a bug in my code, or in SQL server?
>b) is it worth detecting it and fixing it automatically? I.e, should I
>put some code in that notices that a query is running far too slowly,
>then runs "DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE"? Or will that
>cause other problems?

>
Pardon me but
>
Why did you not say which version of SQL Server you are running on?
And why not mention the version of MDAC you have installed (since you
posted
to microsoft.public.data.ado)?
And why not mention something about the Primary Keys & Indexes of the
table
you are querying - I take it you have an index on MyKey?
And have you checked the documentation, whitepapers, MSDN for details on
query performance?
And have you checked the query plan in Query Analyser?
>
Cheers
>
Stephen Howe
>
>

|||Erland Sommarskog wrote:

Quote:

Originally Posted by

(wizofaus@.hotmail.com) writes:

Quote:

Originally Posted by

Thanks...one of the most helpful replies I've had on usenet for some
time now!

The problem is that it's pretty hard for me to know that a value is
"odd".

>
I can understand that this is not always simple. I didn't say this, in
hope it would be. :-)
>
However, I think I have a cure for you:
>

Quote:

Originally Posted by

In this case, like I said the query in this case is

SELECT Min(MyValue) FROM MyTable WHERE MyKey = @.0

Where @.0 is actually a value from a list that is obtained from
elsewhere (not the database). It loops through this list, calling the
same query for each one.

>
Stop! Don't do that! The problems with query plans aside, this is an
ineffecient use of SQL Server. Get all data at once with:
>
SELECT t.MyKey, Min(t.MyValue)
FROM MyTable t
JOIN list_to_table(@.list) f ON t.MyKey = f.value
GROUP BY t.MyKey
>
Where list_to_table is a table-valued function that transform the list
to a table. I have a whole bunch of such functions on
http://www.sommarskog.se/arrays-in-sql.html.
>

I agree this sort of thing would be preferable, and more efficient, but
as it is, providing I don't get the bad plan problem, it's efficient
enough as it is, and I'm wary of making too many big changes at this
point. That sort of thing I'd prefer to leave for the next version.

The thing is, I just tried exporting all the records to a CSV file,
creating a clean database with the same tables/indices, re-importing
all the records, and the same problem DOESN'T happen: I can run that
query with the "odd" value first, then with the common value, and both
queries run fast. So something else has happened to my database that
surely I must be able to reset somehow?|||(wizofaus@.hotmail.com) writes:

Quote:

Originally Posted by

But keep in mind that the solution you have now will not scale well. If
the data in production is ten times larger than you have expected, you
will get ten times more execution time, even with the good plan.

Quote:

Originally Posted by

I eavesdropped a discussion at PASS in Seattle last year, when a guy
had done extensive tests, and he could repeat a scenario that depending
on which order he loaded the same data, he would get different plans,
good or bad. I presume that part of the answer lies what exactly is in
the statistics. Normally, statistics are only samples, and if the
statistics does not well reflect the data distribution, your plans
will not always be the best.

Quote:

Originally Posted by

(wizofaus@.hotmail.com) writes:

Quote:

Originally Posted by

>
But keep in mind that the solution you have now will not scale well. If
the data in production is ten times larger than you have expected, you
will get ten times more execution time, even with the good plan.

Sure. I definitely plan on doing some query optimization and
consolidation for the next version. Your routines may well come in
handy, so thanks.

Quote:

Originally Posted by

Quote:

Originally Posted by

>
I eavesdropped a discussion at PASS in Seattle last year, when a guy
had done extensive tests, and he could repeat a scenario that depending
on which order he loaded the same data, he would get different plans,
good or bad. I presume that part of the answer lies what exactly is in
the statistics. Normally, statistics are only samples, and if the
statistics does not well reflect the data distribution, your plans
will not always be the best.
>

Well I found another solution - reindex the table.

I ran

dbcc dbreindex('mytable', ' ', 90) on the initial database, and now the
problem is gone away. My main concern was that if we did see this
problem occuring in productoion databases, how could we fix it, other
than changing the code, and at least now I have such a solution, and
it's a bit less drastic than exporting and re-importing all the data
(which potentially could have taken hours).

I'm not sure whether 90 is the best parameter value here, that was just
from the MSDN article.|||(wizofaus@.hotmail.com) writes:

Quote:

Originally Posted by

Well I found another solution - reindex the table.
>
I ran
>
dbcc dbreindex('mytable', ' ', 90) on the initial database, and now the
problem is gone away. My main concern was that if we did see this
problem occuring in productoion databases, how could we fix it, other
than changing the code, and at least now I have such a solution, and
it's a bit less drastic than exporting and re-importing all the data
(which potentially could have taken hours).

It's recommended to run a maintenance job to reindex the table with some
frequency. The main reason for this is to prevent defragmentation. A side
effect of this is that statistics are updated with fullscan, that is all
rows are considered. That gives the optimizer more accurate information.

Quote:

Originally Posted by

>Well I found another solution - reindex the table.
>
>I ran
>
>dbcc dbreindex('mytable', ' ', 90) on the initial database, and now the
>problem is gone away. My main concern was that if we did see this
>problem occuring in productoion databases, how could we fix it, other
>than changing the code, and at least now I have such a solution, and
>it's a bit less drastic than exporting and re-importing all the data
>(which potentially could have taken hours).

How large is your table?

Maybe a simple "update statistics" would also fix things?

J.|||JXStern wrote:

Quote:

Originally Posted by

On 13 Jan 2007 18:15:11 -0800, wizofaus@.hotmail.com wrote:
>

Quote:

Originally Posted by

Well I found another solution - reindex the table.

I ran

>
How large is your table?

'bout 2 million records, 9 columns.

Quote:

Originally Posted by

>
Maybe a simple "update statistics" would also fix things?
>

Quite possibly - unfortunately I can't re-create the problem now to
test it!
Will be the first thing I try if I see the same problem again, though.|||On 15 Jan 2007 13:36:21 -0800, wizofaus@.hotmail.com wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>How large is your table?

>
>'bout 2 million records, 9 columns.

Quote:

Originally Posted by

>>
>Maybe a simple "update statistics" would also fix things?
>>

>Quite possibly - unfortunately I can't re-create the problem now to
>test it!
>Will be the first thing I try if I see the same problem again, though.

Could probably reproduce it - insert a 1,000,000 rows = 1, then a
handfull of rows numbered 2-10. Query it for =1, it will scan, then
query it for =2. Or something like that.

J.|||JXStern wrote:

Quote:

Originally Posted by

On 15 Jan 2007 13:36:21 -0800, wizofaus@.hotmail.com wrote:
>

Quote:

Originally Posted by

Quote:

Originally Posted by

How large is your table?

'bout 2 million records, 9 columns.

Quote:

Originally Posted by

>
Maybe a simple "update statistics" would also fix things?
>

Quite possibly - unfortunately I can't re-create the problem now to
test it!
Will be the first thing I try if I see the same problem again, though.

>
Could probably reproduce it - insert a 1,000,000 rows = 1, then a
handfull of rows numbered 2-10. Query it for =1, it will scan, then
query it for =2. Or something like that.
>

Not sure, but now I have another issue which does seem to point towards
parameters being a problem.

If I run a query with quite a large number of parameters which
basically

SELECT Count(*) FROM MyTable WHERE Key1 IN (@.0, @.1, @.2...@.12) AND Key2
IN (@.13, @.14, @.15...@.20)

it takes over 2 seconds, but when I substitute the last 8 parameters
(13-20) with their values, i.e.

SELECT Count(*) FROM MyTable WHERE Key1 IN (@.0, @.1, @.2...@.12) AND Key2
IN ('value1', 'value2', 'value3', 'value4'...'value8')

the query takes 15 milliseconds! (actually it took slightly longer -
about 400msec - on the first run - the previous query always takes ~2
seconds no matter how often I run it).

Curiously, the fewer parameters I substitute for the Key2 filter, the
slower it runs (I can't improve on the 15 msec by doing the same for
they Key1 filter), in a more or less linear relationship.

I've tried all the previously mentioned "tricks", including reindexing
the table, but no luck.

So it seems maybe SQL server has problems optimizing if there are too
many parameters.
FWIW, I did try putting the parameter values in a temporary table and
even using a join, but the total time is significantly longer than
15ms.

Again, the parameter values are under my control, so there's no risk of
SQL injection, so if there's another good reason NOT to do my own
parameter substitution in this case, I'd be interested in hearing it.|||wizofaus@.hotmail.com wrote:

Quote:

Originally Posted by

Not sure, but now I have another issue which does seem to point
towards parameters being a problem.
>
If I run a query with quite a large number of parameters which
basically
>
SELECT Count(*) FROM MyTable WHERE Key1 IN (@.0, @.1, @.2...@.12) AND Key2
IN (@.13, @.14, @.15...@.20)
>
it takes over 2 seconds, but when I substitute the last 8 parameters
(13-20) with their values, i.e.
>
SELECT Count(*) FROM MyTable WHERE Key1 IN (@.0, @.1, @.2...@.12) AND Key2
IN ('value1', 'value2', 'value3', 'value4'...'value8')
>
the query takes 15 milliseconds! (actually it took slightly longer -
about 400msec - on the first run - the previous query always takes ~2
seconds no matter how often I run it).

Now you've got me really intrigued. Based on everything I've ever read, IN
comparisons are supposed to be non-sargable, and therefore non-optimizable.
Someone from the SQL Server groups please correct me if I am wrong. Oh wait,
maybe you have a sufficient number of values to cause the query engine to
use a temp table and join, in which case optimization can occur.

I suggest you use SQL Profiler to determne the difference in the execution
plans for each method. If the fast query plan involves an index that is not
used in the slow query plan, you can use an index hint to force the query
engine to use that index
(http://www.sql-server-performance.c...t_sql_where.asp)

Quote:

Originally Posted by

>
Curiously, the fewer parameters I substitute for the Key2 filter, the
slower it runs (I can't improve on the 15 msec by doing the same for
they Key1 filter), in a more or less linear relationship.
>
I've tried all the previously mentioned "tricks", including reindexing
the table, but no luck.

What about the trick that involves using local variables in your batch,
instead of directly using the parameters:

declare @.tmp1, @.tmp2, etc.
set @.tmp1=@.parm1
etc.
SELECT ...(@.tmp1, ...)

Personally, I would do this in a stored procedure, but you have ignored
previous advice to use stored procedures.

Quote:

Originally Posted by

>
So it seems maybe SQL server has problems optimizing if there are too
many parameters.
FWIW, I did try putting the parameter values in a temporary table and
even using a join, but the total time is significantly longer than
15ms.

ISTR reading somewhere that with a sufficient number of values, that the
query engine does this anyways behind the scenes.

Quote:

Originally Posted by

>
Again, the parameter values are under my control, so there's no risk
of SQL injection,

I always cringe when I hear somebody say this. Unless those values are
hard-coded into your application code, you have to be getting those values
from somewhere. If a user was involved at any point in the process that
generates those values, then you need to at least entertain the possibility
that some funny business may have occurred. Look in these articles for
"secondary sql injection". It is always a mistake to assume that your user
base is too ignorant to take advantage of these techniques:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/adv...l_injection.pdf
http://www.nextgenss.com/papers/mor...l_injection.pdf
The bottom line may turn out to be that you need to choose between secure
and fast.

Quote:

Originally Posted by

>
JXStern wrote:

Quote:

Originally Posted by

On 15 Jan 2007 13:36:21 -0800, wizofaus@.hotmail.com wrote:

Quote:

Originally Posted by

>How large is your table?
>
>'bout 2 million records, 9 columns.
>>
>Maybe a simple "update statistics" would also fix things?
>>
>Quite possibly - unfortunately I can't re-create the problem now to
>test it!
>Will be the first thing I try if I see the same problem again, though.

Could probably reproduce it - insert a 1,000,000 rows = 1, then a
handfull of rows numbered 2-10. Query it for =1, it will scan, then
query it for =2. Or something like that.

Not sure, but now I have another issue which does seem to point towards
parameters being a problem.
>
If I run a query with quite a large number of parameters which
basically
>
SELECT Count(*) FROM MyTable WHERE Key1 IN (@.0, @.1, @.2...@.12) AND Key2
IN (@.13, @.14, @.15...@.20)
>
it takes over 2 seconds, but when I substitute the last 8 parameters
(13-20) with their values, i.e.
>
SELECT Count(*) FROM MyTable WHERE Key1 IN (@.0, @.1, @.2...@.12) AND Key2
IN ('value1', 'value2', 'value3', 'value4'...'value8')
>
the query takes 15 milliseconds! (actually it took slightly longer -
about 400msec - on the first run - the previous query always takes ~2
seconds no matter how often I run it).

Indeed, that is because with literals will really compile the statement
based on the actual values. The optimizer will build a kind of binary
tree. It will also remove any duplicates (when applicable). So the
execution phase will be very fast.

On the other hand, the compilation phase is relatively expensive. And if
you have hundreds of values, the optimizer start to choke.

Quote:

Originally Posted by

Curiously, the fewer parameters I substitute for the Key2 filter, the
slower it runs (I can't improve on the 15 msec by doing the same for
they Key1 filter), in a more or less linear relationship.

Explanation: see above

Quote:

Originally Posted by

I've tried all the previously mentioned "tricks", including reindexing
the table, but no luck.
>
So it seems maybe SQL server has problems optimizing if there are too
many parameters.
FWIW, I did try putting the parameter values in a temporary table and
even using a join, but the total time is significantly longer than
15ms.

It is hard to optimizer a scenario like yours. You might try something
like this:

SELECT SUM(cnt)
FROM (
SELECT COUNT(*) AS cnt FROM MyTable WHERE Key1=@.0
UNION ALL
SELECT COUNT(*) AS cnt FROM MyTable WHERE Key1=@.1
UNION ALL
...
) AS T

Although the optimizer might automatically come up with a query plan
that reflects this strategy, I doubt that it actually will.

Please let me know if it actually increases your query performance.

HTH,
Gert-Jan|||Bob Barrows [MVP] wrote:

Quote:

Originally Posted by

wizofaus@.hotmail.com wrote:

Quote:

Originally Posted by

Not sure, but now I have another issue which does seem to point
towards parameters being a problem.

If I run a query with quite a large number of parameters which
basically

SELECT Count(*) FROM MyTable WHERE Key1 IN (@.0, @.1, @.2...@.12) AND Key2
IN (@.13, @.14, @.15...@.20)

it takes over 2 seconds, but when I substitute the last 8 parameters
(13-20) with their values, i.e.

SELECT Count(*) FROM MyTable WHERE Key1 IN (@.0, @.1, @.2...@.12) AND Key2
IN ('value1', 'value2', 'value3', 'value4'...'value8')

the query takes 15 milliseconds! (actually it took slightly longer -
about 400msec - on the first run - the previous query always takes ~2
seconds no matter how often I run it).

>
Now you've got me really intrigued. Based on everything I've ever read, IN
comparisons are supposed to be non-sargable, and therefore non-optimizable.
Someone from the SQL Server groups please correct me if I am wrong. Oh wait,
maybe you have a sufficient number of values to cause the query engine to
use a temp table and join, in which case optimization can occur.

But that's the thing - I tried doing that explicitly myself, and it's
considerably slower.

Quote:

Originally Posted by

>
I suggest you use SQL Profiler to determne the difference in the execution
plans for each method. If the fast query plan involves an index that is not
used in the slow query plan, you can use an index hint to force the query
engine to use that index
(http://www.sql-server-performance.c...t_sql_where.asp)
>

Quote:

Originally Posted by

Curiously, the fewer parameters I substitute for the Key2 filter, the
slower it runs (I can't improve on the 15 msec by doing the same for
they Key1 filter), in a more or less linear relationship.

I've tried all the previously mentioned "tricks", including reindexing
the table, but no luck.

>
What about the trick that involves using local variables in your batch,
instead of directly using the parameters:
>
declare @.tmp1, @.tmp2, etc.
set @.tmp1=@.parm1
etc.
SELECT ...(@.tmp1, ...)
>
Personally, I would do this in a stored procedure, but you have ignored
previous advice to use stored procedures.

Actually I tried stored procs as well, and it didn't seem to be
helping. Also, can you write a stored proc to take a variable number
of parameters?

Quote:

Originally Posted by

Quote:

Originally Posted by

>
ISTR reading somewhere that with a sufficient number of values, that the
query engine does this anyways behind the scenes.
>

Quote:

Originally Posted by

Again, the parameter values are under my control, so there's no risk
of SQL injection,

>
I always cringe when I hear somebody say this. Unless those values are
hard-coded into your application code, you have to be getting those values
from somewhere. If a user was involved at any point in the process that
generates those values, then you need to at least entertain the possibility
that some funny business may have occurred.

Perhaps, but in this case they are auto-generated - the user has no
control over what the actual values are (only how many there are).

At any rate, if the only thing to take into consideration here is
performance vs security, then I'm afraid performance does win. No-one
will use the application if it's a dog. And as it happens, the
database doesn't hold particularly sensitive (or irreplaceable) data
anyway.|||Bob Barrows [MVP] (reb01501@.NOyahoo.SPAMcom) writes:

Quote:

Originally Posted by

coi IN (val1, val2, ...)

is just a shortcut for

col1 = val1 OR col2 = val2 OR ...

and it's perfectly possible for the optimizer to work with IN to produce a
good plan. The main problem is that for many values, the optimization time
can exceed the real execution time by far.

Bizarre set of SQL results...

Here's the first statement:
select DCII.SEQ_NBR, count(1)
FROM PS_DEM_CHG_INF_INV DCII , PS_PL_TRANS_IT_OPT PTIO
WHERE DCII.SCHED_DATE >= '2004-10-05'
AND DCII.SCHED_DATE <= '2007-10-05'
AND DCII.DEMAND_SOURCE = 'PL'
AND DCII.PROCESSED_FLG <> 'C'
AND PTIO.PROBINST = 'COMBE_1005'
AND DCII.BUSINESS_UNIT = PTIO.BUS_UNIT_SOURCE
AND DCII.DESTIN_BU = PTIO.BUSINESS_UNIT
AND DCII.INV_ITEM_ID = PTIO.INV_ITEM_ID
AND DCII.SHIP_TYPE_ID = PTIO.SHIP_TYPE_ID
AND (EXISTS (
SELECT 'X' FROM PS_PL_BU_ITEMS
WHERE PROBINST = 'COMBE_1005'
AND BUSINESS_UNIT = DCII.BUSINESS_UNIT
AND INV_ITEM_ID = DCII.INV_ITEM_ID
AND 'Y' = 'Y')
OR EXISTS (
SELECT 'X' FROM PS_PL_BU_ITEMS
WHERE PROBINST = 'COMBE_1005'
AND BUSINESS_UNIT = DCII.DESTIN_BU
AND INV_ITEM_ID = DCII.INV_ITEM_ID
AND 'Y' = 'Y'))
group by DCII.SEQ_NBR
having count(1) > 1
one of the resulting rows is:
110441,2
so then I alter the SQL to get some deatil on the duplicate SEQ_NBR
above as follows:
select *
FROM PS_DEM_CHG_INF_INV DCII , PS_PL_TRANS_IT_OPT PTIO
WHERE DCII.SCHED_DATE >= '2004-10-05'
AND DCII.SCHED_DATE <= '2007-10-05'
AND DCII.DEMAND_SOURCE = 'PL'
AND DCII.PROCESSED_FLG <> 'C'
AND PTIO.PROBINST = 'COMBE_1005'
AND DCII.BUSINESS_UNIT = PTIO.BUS_UNIT_SOURCE
AND DCII.DESTIN_BU = PTIO.BUSINESS_UNIT
AND DCII.INV_ITEM_ID = PTIO.INV_ITEM_ID
AND DCII.SHIP_TYPE_ID = PTIO.SHIP_TYPE_ID
AND (EXISTS (
SELECT 'X' FROM PS_PL_BU_ITEMS
WHERE PROBINST = 'COMBE_1005'
AND BUSINESS_UNIT = DCII.BUSINESS_UNIT
AND INV_ITEM_ID = DCII.INV_ITEM_ID
AND 'Y' = 'Y')
OR EXISTS (
SELECT 'X' FROM PS_PL_BU_ITEMS
WHERE PROBINST = 'COMBE_1005'
AND BUSINESS_UNIT = DCII.DESTIN_BU
AND INV_ITEM_ID = DCII.INV_ITEM_ID
AND 'Y' = 'Y'))
and DCII.SEQ_NBR = 110441
when I execute the above statement, I only get 1 row. Based on the
count from the 1st statement, I would expect 2. Can anyone come up with
an explanation for that?Sorry - false alarm - just require and UPDATE STATISTICS.

bizarre query problem

Below is part of a stored proc.
This code returns an error ONLY if Field.OrderId is included in the query.
It runs ok if the OrderID is not included.
The error recieved is:
Server: Msg 512, Level 16, State 1, Procedure Jobs_TrackInserts, Line 18
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Running the code without field.OrderID returns..
(0 row(s) affected)
(1 row(s) affected) << ALSO: What does this represent ?
(297 row(s) affected)
CREATE TABLE [OrderRequest_Equipment] (
[OR_EQ_id] [int] IDENTITY (1, 1) NOT NULL ,
[OrderID] [int] NULL ,
[Class] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Preference] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Attachment] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UNIT] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MoveEarliest] [datetime] NULL ,
[MoveLatest] [datetime] NULL ,
[UnLoadEarliest] [datetime] NULL ,
[UnLoadLatest] [datetime] NULL ,
[EditedBy] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MoveType] [int] NULL ,
[OffRentID] [int] NULL ,
[Action] [bit] NOT NULL CONSTRAINT [DF_OrderRequest_Equipment_Action]
DEFAULT (0),
[FromSiteID] [int] NULL CONSTRAINT [DF_OrderRequest_Equipment_FromSiteID]
DEFAULT (0),
[RD] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RD] DEFAULT
(0),
[RW] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RW] DEFAULT
(0),
[RM] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RM] DEFAULT
(0),
[Dur] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[trucking] [smallmoney] NULL ,
[fuel] [smallmoney] NULL ,
[JobNumber] [int] NULL ,
[UnitNotes] [nchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FinishedAsOf] [smalldatetime] NULL ,
CONSTRAINT [PK_OrderRequest_ClassesNeeded] PRIMARY KEY NONCLUSTERED
(
[OR_EQ_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
----
--
CREATE TABLE [DCCPendingMoves] (
[jobNumber] [int] NOT NULL
) ON [PRIMARY]
GO
THIS IS THE QUERY CAUSING THE PROBLEM...
delete from bobmlt.dcc.dbo.OrderRequest_Equipment --Purge Jobs
OrderRequest_Equipment Table
set IDENTITY_INSERT OrderRequest_Equipment on
insert into OrderRequest_Equipment
(OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
FinishedAsOf)
select
OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
FinishedAsOf
from bobmlt.dcc.dbo.DCCPendingMoves_ore
set IDENTITY_INSERT OrderRequest_Equipment off
Thanks in advance for any help on this...
Bob McClellan.> (1 row(s) affected) << ALSO: What does this represent ?
This is a clue. You probably have an INSERT trigger on the table that is
not written correctly to handle multi-row inserts.
Hope this helps.
Dan Guzman
SQL Server MVP
"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:emOh9lxBGHA.740@.TK2MSFTNGP12.phx.gbl...
> Below is part of a stored proc.
> This code returns an error ONLY if Field.OrderId is included in the query.
> It runs ok if the OrderID is not included.
> The error recieved is:
> Server: Msg 512, Level 16, State 1, Procedure Jobs_TrackInserts, Line 18
> Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression.
> The statement has been terminated.
> Running the code without field.OrderID returns..
> (0 row(s) affected)
>
> (1 row(s) affected) << ALSO: What does this represent ?
>
> (297 row(s) affected)
>
> CREATE TABLE [OrderRequest_Equipment] (
> [OR_EQ_id] [int] IDENTITY (1, 1) NOT NULL ,
> [OrderID] [int] NULL ,
> [Class] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Preference] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Attachment] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UNIT] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MoveEarliest] [datetime] NULL ,
> [MoveLatest] [datetime] NULL ,
> [UnLoadEarliest] [datetime] NULL ,
> [UnLoadLatest] [datetime] NULL ,
> [EditedBy] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MoveType] [int] NULL ,
> [OffRentID] [int] NULL ,
> [Action] [bit] NOT NULL CONSTRAINT [DF_OrderRequest_Equipment_Action]
> DEFAULT (0),
> [FromSiteID] [int] NULL CONSTRAINT [DF_OrderRequest_Equipment_FromSiteID]
> DEFAULT (0),
> [RD] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RD] DEFAULT
> (0),
> [RW] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RW] DEFAULT
> (0),
> [RM] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RM] DEFAULT
> (0),
> [Dur] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [trucking] [smallmoney] NULL ,
> [fuel] [smallmoney] NULL ,
> [JobNumber] [int] NULL ,
> [UnitNotes] [nchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FinishedAsOf] [smalldatetime] NULL ,
> CONSTRAINT [PK_OrderRequest_ClassesNeeded] PRIMARY KEY NONCLUSTERED
> (
> [OR_EQ_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ----
--
> CREATE TABLE [DCCPendingMoves] (
> [jobNumber] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
>
> THIS IS THE QUERY CAUSING THE PROBLEM...
> delete from bobmlt.dcc.dbo.OrderRequest_Equipment --Purge Jobs
> OrderRequest_Equipment Table
> set IDENTITY_INSERT OrderRequest_Equipment on
> insert into OrderRequest_Equipment
> (OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
> MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
> Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
> FinishedAsOf)
> select
> OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
> MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
> Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
> FinishedAsOf
> from bobmlt.dcc.dbo.DCCPendingMoves_ore
> set IDENTITY_INSERT OrderRequest_Equipment off
>
> Thanks in advance for any help on this...
> Bob McClellan.
>|||Seems that you have a trigger defined on the table whch isn=B4t able to
process multiple rows affected.
Triggers are fired on a statement basis NOT on a row basis...
HTH, jens Suessmeyer.|||Awesome...
Thanks guys...
"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:emOh9lxBGHA.740@.TK2MSFTNGP12.phx.gbl...
> Below is part of a stored proc.
> This code returns an error ONLY if Field.OrderId is included in the query.
> It runs ok if the OrderID is not included.
> The error recieved is:
> Server: Msg 512, Level 16, State 1, Procedure Jobs_TrackInserts, Line 18
> Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression.
> The statement has been terminated.
> Running the code without field.OrderID returns..
> (0 row(s) affected)
>
> (1 row(s) affected) << ALSO: What does this represent ?
>
> (297 row(s) affected)
>
> CREATE TABLE [OrderRequest_Equipment] (
> [OR_EQ_id] [int] IDENTITY (1, 1) NOT NULL ,
> [OrderID] [int] NULL ,
> [Class] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Preference] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Attachment] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UNIT] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MoveEarliest] [datetime] NULL ,
> [MoveLatest] [datetime] NULL ,
> [UnLoadEarliest] [datetime] NULL ,
> [UnLoadLatest] [datetime] NULL ,
> [EditedBy] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MoveType] [int] NULL ,
> [OffRentID] [int] NULL ,
> [Action] [bit] NOT NULL CONSTRAINT [DF_OrderRequest_Equipment_Action]
> DEFAULT (0),
> [FromSiteID] [int] NULL CONSTRAINT [DF_OrderRequest_Equipment_FromSiteID]
> DEFAULT (0),
> [RD] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RD] DEFAULT
> (0),
> [RW] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RW] DEFAULT
> (0),
> [RM] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RM] DEFAULT
> (0),
> [Dur] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [trucking] [smallmoney] NULL ,
> [fuel] [smallmoney] NULL ,
> [JobNumber] [int] NULL ,
> [UnitNotes] [nchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FinishedAsOf] [smalldatetime] NULL ,
> CONSTRAINT [PK_OrderRequest_ClassesNeeded] PRIMARY KEY NONCLUSTERED
> (
> [OR_EQ_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ----
--
> CREATE TABLE [DCCPendingMoves] (
> [jobNumber] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
>
> THIS IS THE QUERY CAUSING THE PROBLEM...
> delete from bobmlt.dcc.dbo.OrderRequest_Equipment --Purge Jobs
> OrderRequest_Equipment Table
> set IDENTITY_INSERT OrderRequest_Equipment on
> insert into OrderRequest_Equipment
> (OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
> MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
> Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
> FinishedAsOf)
> select
> OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
> MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
> Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
> FinishedAsOf
> from bobmlt.dcc.dbo.DCCPendingMoves_ore
> set IDENTITY_INSERT OrderRequest_Equipment off
>
> Thanks in advance for any help on this...
> Bob McClellan.
>

Bizarre Query

Good afternoon,

I have a bizarre question. When running the following query:

select SomeColumnName from TableA where PK_TableA in
(select PK_TableA from TableB)

I get results. This should not be feasible, because the query within
the in clause:

select PK_TableA from TableB

is not possible - there is no PK_TableA column within the TableB table.
Running the sub-query alone gives an error, but when using it as
sub-query in the first statement, I get every row within TableA.

If it helps any, the exact query I'm running is:

select demonstratorid from DirectSalesAgent where DirectSalesAgentId in
(select directsalesagentid from WebsiteSubscriptionPayment)

Shouldn't a query return an error if the sub-query has an invalid
column name?On 5 Jul 2006 15:30:42 -0700, Xeth Waxman wrote:

Quote:

Originally Posted by

>Good afternoon,
>
>I have a bizarre question. When running the following query:
>
>select SomeColumnName from TableA where PK_TableA in
>(select PK_TableA from TableB)
>
>I get results. This should not be feasible, because the query within
>the in clause:
>
>select PK_TableA from TableB
>
>is not possible - there is no PK_TableA column within the TableB table.
Running the sub-query alone gives an error, but when using it as
>sub-query in the first statement, I get every row within TableA.
>
>If it helps any, the exact query I'm running is:
>
>select demonstratorid from DirectSalesAgent where DirectSalesAgentId in
>(select directsalesagentid from WebsiteSubscriptionPayment)
>
>Shouldn't a query return an error if the sub-query has an invalid
>column name?

Hi Xeth,

There are two types of subqueries: correlated and uncorrelated. The
correlated ones refer to columns in the outer query.

If a column name in a subquery is not qualified with table name (or
table alias), SQL Server will first test if it matches a column from a
table used in the subquery. If it doesn't, it will then go on and check
if it matches a column in one of the tables in the outer table, assuming
you want a correlated subquery if it does.

The query you wriite is equivalent to this one (adding table qualifiers
for clarity):

SELECT TableA.SomeColumnName
FROM TableA
WHERE TableA.PK_TableA IN (SELECT TableA.PK_TableA
FROM TableB)

This will return all rows from TableA if at least one row exists in
TableB, or no rows at all if TableB is empty. (If TableB is not empty,
the subqeury will have one row for each row in TableB, but the only
column will have the value of TableA.PK_TableA in each of those rows).

--
Hugo Kornelis, SQL Server MVP

bizarre performance issue

Ive got a box that is having bizarre performance behavior. There is plenty of
CPU and RAM available. There is no blocking. I cannot find any table scans,
(other than - - 120 row temp table scans) nor does the Index Tuning Wizrd
recommend that I add anything. No software other than SQL Server and some
CommVault backup software are on the box. But still, performance is all over
the place. Sometimes everything is humming right along between 3 and 5
seconds, but later on it will be between 40 and 50 seconds. Of course I see
all of these Procs in Profiler, but everything appears to be OK with them.
Ive been on this all day and am really at a loss as to what to do from here.
Any ideas?
--
TIA,
ChrisRThat is not much to go on. What is it you are doing? 3 to 5 seconds isn't
humming along in my book but it is hard to say without seeing what you are
trying to do. No that it should make that much of a difference but maybe
you are getting a lot of recompiles due to the temp table populations. Have
you tried replacing them with table vars? They sound like the optimal size
for table vars over temp tables.
--
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:594022ED-21B3-44F6-84A3-803F877D243D@.microsoft.com...
> Ive got a box that is having bizarre performance behavior. There is plenty
> of
> CPU and RAM available. There is no blocking. I cannot find any table
> scans,
> (other than - - 120 row temp table scans) nor does the Index Tuning Wizrd
> recommend that I add anything. No software other than SQL Server and some
> CommVault backup software are on the box. But still, performance is all
> over
> the place. Sometimes everything is humming right along between 3 and 5
> seconds, but later on it will be between 40 and 50 seconds. Of course I
> see
> all of these Procs in Profiler, but everything appears to be OK with them.
> Ive been on this all day and am really at a loss as to what to do from
> here.
> Any ideas?
> --
> TIA,
> ChrisR|||ChrisR wrote:
> Ive got a box that is having bizarre performance behavior. There is
> plenty of CPU and RAM available. There is no blocking. I cannot find
> any table scans, (other than - - 120 row temp table scans) nor does
> the Index Tuning Wizrd recommend that I add anything. No software
> other than SQL Server and some CommVault backup software are on the
> box. But still, performance is all over the place. Sometimes
> everything is humming right along between 3 and 5 seconds, but later
> on it will be between 40 and 50 seconds. Of course I see all of these
> Procs in Profiler, but everything appears to be OK with them. Ive
> been on this all day and am really at a loss as to what to do from
> here. Any ideas?
When you say the procs, as they appear in Profiler, appear ok, what do
you mean? If they are in fact appearing within your expected performance
parameters, you should see CPU, Reads, and Duration figures that are
normal. Duration should be in the 3,000 - 5,000ms range as you mentioned
and in all likelyhood, performance at the client should be good as well.
If OTOH you are seeing a Duration of 30,000ms or elevated CPU or Read
activity, then I'm guessing that Profiler is showing you there is a
problem.
Can you be more specific about any differences in the Profiler output
between good and bad performance.
David Gugick
Quest Software
www.imceda.com
www.quest.com

bizarre performance issue

Ive got a box that is having bizarre performance behavior. There is plenty o
f
CPU and RAM available. There is no blocking. I cannot find any table scans,
(other than - - 120 row temp table scans) nor does the Index Tuning Wizrd
recommend that I add anything. No software other than SQL Server and some
CommVault backup software are on the box. But still, performance is all over
the place. Sometimes everything is humming right along between 3 and 5
seconds, but later on it will be between 40 and 50 seconds. Of course I see
all of these Procs in Profiler, but everything appears to be OK with them.
Ive been on this all day and am really at a loss as to what to do from here.
Any ideas?
TIA,
ChrisRThat is not much to go on. What is it you are doing? 3 to 5 seconds isn't
humming along in my book but it is hard to say without seeing what you are
trying to do. No that it should make that much of a difference but maybe
you are getting a lot of recompiles due to the temp table populations. Have
you tried replacing them with table vars? They sound like the optimal size
for table vars over temp tables.
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:594022ED-21B3-44F6-84A3-803F877D243D@.microsoft.com...
> Ive got a box that is having bizarre performance behavior. There is plenty
> of
> CPU and RAM available. There is no blocking. I cannot find any table
> scans,
> (other than - - 120 row temp table scans) nor does the Index Tuning Wizrd
> recommend that I add anything. No software other than SQL Server and some
> CommVault backup software are on the box. But still, performance is all
> over
> the place. Sometimes everything is humming right along between 3 and 5
> seconds, but later on it will be between 40 and 50 seconds. Of course I
> see
> all of these Procs in Profiler, but everything appears to be OK with them.
> Ive been on this all day and am really at a loss as to what to do from
> here.
> Any ideas?
> --
> TIA,
> ChrisR|||ChrisR wrote:
> Ive got a box that is having bizarre performance behavior. There is
> plenty of CPU and RAM available. There is no blocking. I cannot find
> any table scans, (other than - - 120 row temp table scans) nor does
> the Index Tuning Wizrd recommend that I add anything. No software
> other than SQL Server and some CommVault backup software are on the
> box. But still, performance is all over the place. Sometimes
> everything is humming right along between 3 and 5 seconds, but later
> on it will be between 40 and 50 seconds. Of course I see all of these
> Procs in Profiler, but everything appears to be OK with them. Ive
> been on this all day and am really at a loss as to what to do from
> here. Any ideas?
When you say the procs, as they appear in Profiler, appear ok, what do
you mean? If they are in fact appearing within your expected performance
parameters, you should see CPU, Reads, and Duration figures that are
normal. Duration should be in the 3,000 - 5,000ms range as you mentioned
and in all likelyhood, performance at the client should be good as well.
If OTOH you are seeing a Duration of 30,000ms or elevated CPU or Read
activity, then I'm guessing that Profiler is showing you there is a
problem.
Can you be more specific about any differences in the Profiler output
between good and bad performance.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Sunday, February 12, 2012

Bizarre zero suppression on print

Bizarre Stored Procedure Behavior

Bizarre SQL statement

Bizarre sql management studio problem...cant see server status for

Bizarre slow query problem (again)

Bizarre set of SQL results...

bizarre query problem

Bizarre Query

bizarre performance issue

bizarre performance issue

sql bit data type

Blog Archive

About Me