All SP's slow than corresponding query

I am working on a database the eventually hold a large amount of data.
For some reason, now, all my stored procedures that used to run quickly, now
a lot slower than the exact same as a query int he analyzer. The plans show
exactly the same statistics, but SP takes about 10 times longer.
Any ideas out there?
TIA1. Check the query plans to see if they are the same..
2. One common potential problem is using local variables in a where clause
in a SP... At the time the sp is optimized, the optimizer CAN see all of the
sp parameter values, but local variables do not get a value until run
time... Therefore the optimizer can NOT use index statistics on these
queries to determine the most useful index, which can lead to performance
problems in sps.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DWinter" <dwinter@.attbi.com> wrote in message
news:uPOjaf6eDHA.560@.tk2msftngp13.phx.gbl...
> I am working on a database the eventually hold a large amount of data.
> For some reason, now, all my stored procedures that used to run quickly,
now
> a lot slower than the exact same as a query int he analyzer. The plans
show
> exactly the same statistics, but SP takes about 10 times longer.
> Any ideas out there?
> TIA
>|||Is it possible that they are recompiling for some reason? Check with
Profiler, you have SP:Recompile and some other events.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"DWinter" <dwinter@.attbi.com> wrote in message
news:uPOjaf6eDHA.560@.tk2msftngp13.phx.gbl...
> I am working on a database the eventually hold a large amount of data.
> For some reason, now, all my stored procedures that used to run quickly,
now
> a lot slower than the exact same as a query int he analyzer. The plans
show
> exactly the same statistics, but SP takes about 10 times longer.
> Any ideas out there?
> TIA
>|||Yes, the plan is the same, an no local variables used. When I say All, I
mean all stored procedures are slower. Something is wrong. Don't know if
some setting got set somehow or what.
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:%235dcMr6eDHA.1736@.TK2MSFTNGP12.phx.gbl...
> 1. Check the query plans to see if they are the same..
> 2. One common potential problem is using local variables in a where clause
> in a SP... At the time the sp is optimized, the optimizer CAN see all of
the
> sp parameter values, but local variables do not get a value until run
> time... Therefore the optimizer can NOT use index statistics on these
> queries to determine the most useful index, which can lead to performance
> problems in sps.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "DWinter" <dwinter@.attbi.com> wrote in message
> news:uPOjaf6eDHA.560@.tk2msftngp13.phx.gbl...
> > I am working on a database the eventually hold a large amount of data.
> > For some reason, now, all my stored procedures that used to run quickly,
> now
> > a lot slower than the exact same as a query int he analyzer. The plans
> show
> > exactly the same statistics, but SP takes about 10 times longer.
> >
> > Any ideas out there?
> >
> > TIA
> >
> >
>|||does the execution plan show a hash join or a hash match
operation?
if so, are the more than 10,000 rows involved in that op?
read BOL for join types, at some point the hash join
switches from in memory to grace or recursive.
the setting is different for RPC and SQL Batch.
From an ADO client program, if you append parameters, the
proc will show up in Profiler as RPC, anything you do in
QA shows up as SQL Batch.
if this is the case, there are a few things you can do to
avoid this
>--Original Message--
>I am working on a database the eventually hold a large
amount of data.
>For some reason, now, all my stored procedures that used
to run quickly, now
>a lot slower than the exact same as a query int he
analyzer. The plans show
>exactly the same statistics, but SP takes about 10 times
longer.
>Any ideas out there?
>TIA
>
>.
>|||It was a has issue. Sorry to inconvenience you guys.
I should have seen it earlier.
"Yovan Fernandez" <yfernandez@.sai-inc.com> wrote in message
news:mwm9b.2723$ev2.1916726@.newssrv26.news.prodigy.com...
> I have experienced this problem before on another DB i had, it has to do
> with Recompiling like Dejan said, one way to stop recompiling is to turn
> auto statistics off I really think having DB option AUTO_UPDATE_STATISTIC
on
> And when you said all are you including system sp or just user sps?
> Yovan Fernandez
>
> "DWinter" <dwinter@.attbi.com> wrote in message
> news:%23zA79w6eDHA.3216@.tk2msftngp13.phx.gbl...
> > Yes, the plan is the same, an no local variables used. When I say All, I
> > mean all stored procedures are slower. Something is wrong. Don't know if
> > some setting got set somehow or what.
> >
> > "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> > news:%235dcMr6eDHA.1736@.TK2MSFTNGP12.phx.gbl...
> > > 1. Check the query plans to see if they are the same..
> > > 2. One common potential problem is using local variables in a where
> clause
> > > in a SP... At the time the sp is optimized, the optimizer CAN see all
of
> > the
> > > sp parameter values, but local variables do not get a value until run
> > > time... Therefore the optimizer can NOT use index statistics on these
> > > queries to determine the most useful index, which can lead to
> performance
> > > problems in sps.
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Computer Education Services Corporation (CESC), Charlotte, NC
> > > www.computeredservices.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it's
> > > community of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > >
> > > "DWinter" <dwinter@.attbi.com> wrote in message
> > > news:uPOjaf6eDHA.560@.tk2msftngp13.phx.gbl...
> > > > I am working on a database the eventually hold a large amount of
data.
> > > > For some reason, now, all my stored procedures that used to run
> quickly,
> > > now
> > > > a lot slower than the exact same as a query int he analyzer. The
plans
> > > show
> > > > exactly the same statistics, but SP takes about 10 times longer.
> > > >
> > > > Any ideas out there?
> > > >
> > > > TIA
> > > >
> > > >
> > >
> > >
> >
> >
>|||> It was a has issue. Sorry to inconvenience you guys.
> I should have seen it earlier.
No inconvenince, we are glad you worked it out, that's why newsgroups are
for :-)
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

all spaces in a CHAR(5) column

i'm going nuts with this, i suppose i will crack it eventually, but i thought i'd ask around here, seems like all the smart SQL Server guys hang out here

(i'm an SQL guy, not an SQL Server guy)

how does one place 5 spaces into a CHAR(5) column?
create table testzeros
( id smallint not null primary key identity
, myfield char(5)
)
insert into testzeros (myfield) values (' 1')
insert into testzeros (myfield) values (' 11')
insert into testzeros (myfield) values (' 111')
insert into testzeros (myfield) values (' 1111')
insert into testzeros (myfield) values ('11111')
insert into testzeros (myfield) values (' ')

select id
, myfield
, len(myfield) as L
from testzerosno matter what i do, id=6 shows up with L=0, just like an empty string

i've even tried inserting 4 spaces and a non-blank character, which enters just fine, just as you would expect, but when i update the value and replace the non-blank character with a blank, all 5 spaces collapse back to an empty string

is there some kind of server setting like SET ALL_SPACE_EQUALS_EMPTY_YOU_IDIOT to OFF or something?FROM BOL:

"Interpretation of an empty string is controlled by the compatibility level, which is set with the sp_dbcmptlevel system stored procedure. If the compatibility level is 65 or lower, SQL Server interprets empty strings as single spaces. If the compatibility level is 70 or 80, SQL Server interprets empty strings as empty strings. For more information, see sp_dbcmptlevel."

I never used this and afterreading the documentation for sp_dbcmptlevel I am not sure it is such a good idea.|||thanks, that at least sounds somewhat related

but we aren't talking about inserting an empty string

i even tried this --

insert into testzeros(myfield) values (space(5))

and this was converted to empty string as well|||aha!! found it!!

i had declared it as NULL

BOL says:If ANSI_PADDING is ON when a char NULL column is created, it behaves the same as a char NOT NULL column: values are right-padded to the size of the column. If ANSI_PADDING is OFF when a char NULL column is created, it behaves like a varchar column with ANSI_PADDING set OFF: trailing blanks are truncated.
i am an idiot

:)|||Idiocy has been copyrighted??|||Idiots don't find the solutions to their own problems. Only an idiot would not know this.

Where that leaves you, I'm not sure. But thanks for posting the solution anyway.|||Idiocy has been copyrighted??no, but that particular phrase is all mine -- and it's a trademark!!

i lied about it being registered, though, and i suppose somebody else will eventually run out and register it -- i guess i'm just an idiot!!|||I did'nt know this off the top of my head. So I must be pretty stupid.

I am not participating in this forum anymore. Logging out.|||Try such. Its perversion IMHO, but one works
create table testzeros
( id smallint not null primary key identity
, myfield varchar(5) <--
)
insert into testzeros (myfield) values (' 1')
insert into testzeros (myfield) values (' 11')
insert into testzeros (myfield) values (' 111')
insert into testzeros (myfield) values (' 1111')
insert into testzeros (myfield) values ('11111')
insert into testzeros (myfield) values (' ')

select id
, myfield
, len(myfield) as L
, len(replace(myfield, ' ', '_')) <-- look this result
from testzeros|||no, but that particular phrase is all mine -- and it's a trademark!!

Ahh. That may explain why we have to go around saying "I R dum".|||"I R Dum" is freely available as SharePhrase. You can use it all you want, but you are not allowed to modify it or sell it.|||I'm thinking that with the mountains of easily verifiable "prior art" on this topic, that it would never survive a trademark registration. I could be wrong, but I think this would be a real pig to try to register. ;)

-PatP|||... the mountains of easily verifiable "prior art" on this topic... tee hee

i'm responsible for creating plenty of piles, that's for sure ;)