After Restore of a DB to another, it runs slower?

Hi,
I've created a DB,and then I restored it from the develop DB, so I can get
all the data into it, but all the qry's run slower on the production DB as
compared to the develop DB.
Any suggestions?
Thanks alot!
DekeThere can be lots of reasons but have you updated the stats after the
restore? If not run sp_updatestats.
--
Andrew J. Kelly SQL MVP
"Deke" <fakeemail@.nospam.net> wrote in message
news:enigPp8eEHA.4068@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I've created a DB,and then I restored it from the develop DB, so I can
get
> all the data into it, but all the qry's run slower on the production DB as
> compared to the develop DB.
> Any suggestions?
> Thanks alot!
> Deke
>|||Hi Andrew,
Well I ran the sp_udatestats from the master db in query analyzer and it
showed a bunch of stuff being updated, but when I run the query it's still
slow (10 sec) where in the other develop db when I run the same query it's
instant.
Any other suggestions?
Thanks
Deke
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e6jrk79eEHA.396@.TK2MSFTNGP12.phx.gbl...
> There can be lots of reasons but have you updated the stats after the
> restore? If not run sp_updatestats.
> --
> Andrew J. Kelly SQL MVP
>
> "Deke" <fakeemail@.nospam.net> wrote in message
> news:enigPp8eEHA.4068@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > I've created a DB,and then I restored it from the develop DB, so I can
> get
> > all the data into it, but all the qry's run slower on the production DB
as
> > compared to the develop DB.
> >
> > Any suggestions?
> > Thanks alot!
> > Deke
> >
> >
>|||Are the databases on the same server? If not could one server be better than
the other? Could network issues come into play due to the location of each
serer? Are you seeing the same results when you run SP from query analyzer?
"Deke" <fakeemail@.nospam.net> wrote in message
news:#WvNjO#eEHA.1724@.TK2MSFTNGP10.phx.gbl...
> Hi Andrew,
> Well I ran the sp_udatestats from the master db in query analyzer and it
> showed a bunch of stuff being updated, but when I run the query it's still
> slow (10 sec) where in the other develop db when I run the same query it's
> instant.
> Any other suggestions?
> Thanks
> Deke
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e6jrk79eEHA.396@.TK2MSFTNGP12.phx.gbl...
> > There can be lots of reasons but have you updated the stats after the
> > restore? If not run sp_updatestats.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Deke" <fakeemail@.nospam.net> wrote in message
> > news:enigPp8eEHA.4068@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > >
> > > I've created a DB,and then I restored it from the develop DB, so I
can
> > get
> > > all the data into it, but all the qry's run slower on the production
DB
> as
> > > compared to the develop DB.
> > >
> > > Any suggestions?
> > > Thanks alot!
> > > Deke
> > >
> > >
> >
> >
>|||Well running it in Master does nothing for the stats in the user db. Try:
USE YourDB
GO
sp_updatestats
It also takes a little while for the data to get in cache and the sp's etc
to all compile before it will be as fast as possible. Is the hardware
identical?
Andrew J. Kelly SQL MVP
"Deke" <fakeemail@.nospam.net> wrote in message
news:%23WvNjO%23eEHA.1724@.TK2MSFTNGP10.phx.gbl...
> Hi Andrew,
> Well I ran the sp_udatestats from the master db in query analyzer and it
> showed a bunch of stuff being updated, but when I run the query it's still
> slow (10 sec) where in the other develop db when I run the same query it's
> instant.
> Any other suggestions?
> Thanks
> Deke
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e6jrk79eEHA.396@.TK2MSFTNGP12.phx.gbl...
> > There can be lots of reasons but have you updated the stats after the
> > restore? If not run sp_updatestats.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Deke" <fakeemail@.nospam.net> wrote in message
> > news:enigPp8eEHA.4068@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > >
> > > I've created a DB,and then I restored it from the develop DB, so I
can
> > get
> > > all the data into it, but all the qry's run slower on the production
DB
> as
> > > compared to the develop DB.
> > >
> > > Any suggestions?
> > > Thanks alot!
> > > Deke
> > >
> > >
> >
> >
>|||Thanks Andrew,
The DB is on the same server, so it's not a hardware issue. Maybe during
the developemnt of the original query it fine tuned itself for the fastest
way, and then when copied it lost that info(')
But doing what you posted in your last post did the job, thank you very
much!
It's fun learning something new everyday. :)
Deke
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:etEEx2%23eEHA.3476@.tk2msftngp13.phx.gbl...
> Well running it in Master does nothing for the stats in the user db. Try:
> USE YourDB
> GO
> sp_updatestats
> It also takes a little while for the data to get in cache and the sp's etc
> to all compile before it will be as fast as possible. Is the hardware
> identical?
>
> --
> Andrew J. Kelly SQL MVP
>
> "Deke" <fakeemail@.nospam.net> wrote in message
> news:%23WvNjO%23eEHA.1724@.TK2MSFTNGP10.phx.gbl...
> > Hi Andrew,
> >
> > Well I ran the sp_udatestats from the master db in query analyzer and
it
> > showed a bunch of stuff being updated, but when I run the query it's
still
> > slow (10 sec) where in the other develop db when I run the same query
it's
> > instant.
> >
> > Any other suggestions?
> > Thanks
> > Deke
> >
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:e6jrk79eEHA.396@.TK2MSFTNGP12.phx.gbl...
> > > There can be lots of reasons but have you updated the stats after the
> > > restore? If not run sp_updatestats.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "Deke" <fakeemail@.nospam.net> wrote in message
> > > news:enigPp8eEHA.4068@.TK2MSFTNGP11.phx.gbl...
> > > > Hi,
> > > >
> > > > I've created a DB,and then I restored it from the develop DB, so I
> can
> > > get
> > > > all the data into it, but all the qry's run slower on the production
> DB
> > as
> > > > compared to the develop DB.
> > > >
> > > > Any suggestions?
> > > > Thanks alot!
> > > > Deke
> > > >
> > > >
> > >
> > >
> >
> >
>|||Well each time a stored proc (or any query for that matter) is run (in each
db) it has to be compiled and a query plan is chosen. This compiled plan is
then stored in cache (memory only) so that when you run it again there is no
need to recompile again. The query plan is based on several things one of
which are the statistics for the table(s) involved. When you restore a DB
you should always update the stats to ensure they are current and useable.
When you copy a db and run a sp in the new db it has no knowledge of the old
one so it gets a new plan and if the stats are wrong it can choose a wrong
plan. When you update the stats it forces the plans to be recompiled again
and then they will use the new stats and hopefully choose the proper plan.
--
Andrew J. Kelly SQL MVP
"Deke" <fakeemail@.nospam.net> wrote in message
news:Ol8R49%23eEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Thanks Andrew,
> The DB is on the same server, so it's not a hardware issue. Maybe
during
> the developemnt of the original query it fine tuned itself for the fastest
> way, and then when copied it lost that info(')
> But doing what you posted in your last post did the job, thank you very
> much!
> It's fun learning something new everyday. :)
> Deke
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:etEEx2%23eEHA.3476@.tk2msftngp13.phx.gbl...
> > Well running it in Master does nothing for the stats in the user db.
Try:
> >
> > USE YourDB
> > GO
> > sp_updatestats
> >
> > It also takes a little while for the data to get in cache and the sp's
etc
> > to all compile before it will be as fast as possible. Is the hardware
> > identical?
> >
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Deke" <fakeemail@.nospam.net> wrote in message
> > news:%23WvNjO%23eEHA.1724@.TK2MSFTNGP10.phx.gbl...
> > > Hi Andrew,
> > >
> > > Well I ran the sp_udatestats from the master db in query analyzer
and
> it
> > > showed a bunch of stuff being updated, but when I run the query it's
> still
> > > slow (10 sec) where in the other develop db when I run the same query
> it's
> > > instant.
> > >
> > > Any other suggestions?
> > > Thanks
> > > Deke
> > >
> > >
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:e6jrk79eEHA.396@.TK2MSFTNGP12.phx.gbl...
> > > > There can be lots of reasons but have you updated the stats after
the
> > > > restore? If not run sp_updatestats.
> > > >
> > > > --
> > > > Andrew J. Kelly SQL MVP
> > > >
> > > >
> > > > "Deke" <fakeemail@.nospam.net> wrote in message
> > > > news:enigPp8eEHA.4068@.TK2MSFTNGP11.phx.gbl...
> > > > > Hi,
> > > > >
> > > > > I've created a DB,and then I restored it from the develop DB, so
I
> > can
> > > > get
> > > > > all the data into it, but all the qry's run slower on the
production
> > DB
> > > as
> > > > > compared to the develop DB.
> > > > >
> > > > > Any suggestions?
> > > > > Thanks alot!
> > > > > Deke
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

After Restore of a DB to another, it runs slower?

Hi,
I've created a DB,and then I restored it from the develop DB, so I can get
all the data into it, but all the qry's run slower on the production DB as
compared to the develop DB.
Any suggestions?
Thanks alot!
DekeThere can be lots of reasons but have you updated the stats after the
restore? If not run sp_updatestats.
Andrew J. Kelly SQL MVP
"Deke" <fakeemail@.nospam.net> wrote in message
news:enigPp8eEHA.4068@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I've created a DB,and then I restored it from the develop DB, so I can
get
> all the data into it, but all the qry's run slower on the production DB as
> compared to the develop DB.
> Any suggestions?
> Thanks alot!
> Deke
>|||Hi Andrew,
Well I ran the sp_udatestats from the master db in query analyzer and it
showed a bunch of stuff being updated, but when I run the query it's still
slow (10 sec) where in the other develop db when I run the same query it's
instant.
Any other suggestions?
Thanks
Deke
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e6jrk79eEHA.396@.TK2MSFTNGP12.phx.gbl...
> There can be lots of reasons but have you updated the stats after the
> restore? If not run sp_updatestats.
> --
> Andrew J. Kelly SQL MVP
>
> "Deke" <fakeemail@.nospam.net> wrote in message
> news:enigPp8eEHA.4068@.TK2MSFTNGP11.phx.gbl...
> get
as[vbcol=seagreen]
>|||Are the databases on the same server? If not could one server be better than
the other? Could network issues come into play due to the location of each
serer? Are you seeing the same results when you run SP from query analyzer?
"Deke" <fakeemail@.nospam.net> wrote in message
news:#WvNjO#eEHA.1724@.TK2MSFTNGP10.phx.gbl...
> Hi Andrew,
> Well I ran the sp_udatestats from the master db in query analyzer and it
> showed a bunch of stuff being updated, but when I run the query it's still
> slow (10 sec) where in the other develop db when I run the same query it's
> instant.
> Any other suggestions?
> Thanks
> Deke
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e6jrk79eEHA.396@.TK2MSFTNGP12.phx.gbl...
can[vbcol=seagreen]
DB[vbcol=seagreen]
> as
>|||Well running it in Master does nothing for the stats in the user db. Try:
USE YourDB
GO
sp_updatestats
It also takes a little while for the data to get in cache and the sp's etc
to all compile before it will be as fast as possible. Is the hardware
identical?
Andrew J. Kelly SQL MVP
"Deke" <fakeemail@.nospam.net> wrote in message
news:%23WvNjO%23eEHA.1724@.TK2MSFTNGP10.phx.gbl...
> Hi Andrew,
> Well I ran the sp_udatestats from the master db in query analyzer and it
> showed a bunch of stuff being updated, but when I run the query it's still
> slow (10 sec) where in the other develop db when I run the same query it's
> instant.
> Any other suggestions?
> Thanks
> Deke
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e6jrk79eEHA.396@.TK2MSFTNGP12.phx.gbl...
can[vbcol=seagreen]
DB[vbcol=seagreen]
> as
>|||Thanks Andrew,
The DB is on the same server, so it's not a hardware issue. Maybe during
the developemnt of the original query it fine tuned itself for the fastest
way, and then when copied it lost that info(')
But doing what you posted in your last post did the job, thank you very
much!
It's fun learning something new everyday.
Deke
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:etEEx2%23eEHA.3476@.tk2msftngp13.phx.gbl...
> Well running it in Master does nothing for the stats in the user db. Try:
> USE YourDB
> GO
> sp_updatestats
> It also takes a little while for the data to get in cache and the sp's etc
> to all compile before it will be as fast as possible. Is the hardware
> identical?
>
> --
> Andrew J. Kelly SQL MVP
>
> "Deke" <fakeemail@.nospam.net> wrote in message
> news:%23WvNjO%23eEHA.1724@.TK2MSFTNGP10.phx.gbl...
it[vbcol=seagreen]
still[vbcol=seagreen]
it's[vbcol=seagreen]
> can
> DB
>|||Well each time a stored proc (or any query for that matter) is run (in each
db) it has to be compiled and a query plan is chosen. This compiled plan is
then stored in cache (memory only) so that when you run it again there is no
need to recompile again. The query plan is based on several things one of
which are the statistics for the table(s) involved. When you restore a DB
you should always update the stats to ensure they are current and useable.
When you copy a db and run a sp in the new db it has no knowledge of the old
one so it gets a new plan and if the stats are wrong it can choose a wrong
plan. When you update the stats it forces the plans to be recompiled again
and then they will use the new stats and hopefully choose the proper plan.
Andrew J. Kelly SQL MVP
"Deke" <fakeemail@.nospam.net> wrote in message
news:Ol8R49%23eEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Thanks Andrew,
> The DB is on the same server, so it's not a hardware issue. Maybe
during
> the developemnt of the original query it fine tuned itself for the fastest
> way, and then when copied it lost that info(')
> But doing what you posted in your last post did the job, thank you very
> much!
> It's fun learning something new everyday.
> Deke
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:etEEx2%23eEHA.3476@.tk2msftngp13.phx.gbl...
Try:[vbcol=seagreen]
etc[vbcol=seagreen]
and[vbcol=seagreen]
> it
> still
> it's
the[vbcol=seagreen]
I[vbcol=seagreen]
production[vbcol=seagreen]
>

Advice on T-SQL Solution using field expressions

I have a question about a expression used in access and would like to know
if there is a way to simulate the functionality in SQL.
The qry "select NetUsage<>0 AS Expr1 FROM TBL_QUATIONTEST"
In access would return either 1 or 0 or the calculated value if there was
one, where NetUsage is a field of TBL_QUATIONTEST and would evaluate to a
boolean or calcualated result based on the condition. How can I accomplish
this SQL? At times the expression could get complicated and use several
other fields from the table TBL_QUATIONTEST in the expression.
ex:
SELECT (12.06+ ((NetUsage/750+DemadUasge)*.332)) AS Expr1
FROM TBL_QUATIONTEST
Any adivce on how I can repliacte this in SQL would help. I truely simeply
need to evaluate it to a boolean value or the caluculated reslut.
Beat Reagrds.Can you please provide DDL, sample data (both "calculated value if there was
one" and one without a calculated value), and desired results.
See http://www.aspfaq.com/5006 if you have trouble with this request...
"Tim Harvey" <papow@.yahoo.com> wrote in message
news:uyZw4zWFGHA.3900@.TK2MSFTNGP10.phx.gbl...
>I have a question about a expression used in access and would like to know
>if there is a way to simulate the functionality in SQL.
> The qry "select NetUsage<>0 AS Expr1 FROM TBL_QUATIONTEST"
> In access would return either 1 or 0 or the calculated value if there was
> one, where NetUsage is a field of TBL_QUATIONTEST and would evaluate to a
> boolean or calcualated result based on the condition. How can I
> accomplish this SQL? At times the expression could get complicated and use
> several other fields from the table TBL_QUATIONTEST in the expression.
> ex:
> SELECT (12.06+ ((NetUsage/750+DemadUasge)*.332)) AS Expr1
> FROM TBL_QUATIONTEST
> Any adivce on how I can repliacte this in SQL would help. I truely simeply
> need to evaluate it to a boolean value or the caluculated reslut.
>
> Beat Reagrds.
>|||No problem.
The Access SQL is "almost" the same as SQL Server T-SQL,
in your case with multiple fields and expressions, it is very alike.
/ola
Tim Harvey wrote:
> I have a question about a expression used in access and would like to know
> if there is a way to simulate the functionality in SQL.
> The qry "select NetUsage<>0 AS Expr1 FROM TBL_QUATIONTEST"
> In access would return either 1 or 0 or the calculated value if there was
> one, where NetUsage is a field of TBL_QUATIONTEST and would evaluate to a
> boolean or calcualated result based on the condition. How can I accomplis
h
> this SQL? At times the expression could get complicated and use several
> other fields from the table TBL_QUATIONTEST in the expression.
> ex:
> SELECT (12.06+ ((NetUsage/750+DemadUasge)*.332)) AS Expr1
> FROM TBL_QUATIONTEST
> Any adivce on how I can repliacte this in SQL would help. I truely simeply
> need to evaluate it to a boolean value or the caluculated reslut.
>
> Beat Reagrds.|||Tim Harvey wrote:
> I have a question about a expression used in access and would like to
> know if there is a way to simulate the functionality in SQL.
> The qry "select NetUsage<>0 AS Expr1 FROM TBL_QUATIONTEST"
> In access would return either 1 or 0 or the calculated value if there
> was one, where NetUsage is a field of TBL_QUATIONTEST and would
> evaluate to a boolean or calcualated result based on the condition.
> How can I accomplish this SQL? At times the expression could get
> complicated and use several other fields from the table
> TBL_QUATIONTEST in the expression.
> ex:
> SELECT (12.06+ ((NetUsage/750+DemadUasge)*.332)) AS Expr1
> FROM TBL_QUATIONTEST
> Any adivce on how I can repliacte this in SQL would help. I truely
> simeply need to evaluate it to a boolean value or the caluculated
> reslut.
> Beat Reagrds.
What you posted will work. Just watch your divisions by integers if your
intent is to keep the decimal value intact:
SELECT (12.06+ ((<Col1> / 750.0 + <Col2> ) * .332)) AS "Expr1"
FROM <Table>
David Gugick
Quest Software
www.quest.com|||In addition to the other posts:
There's no Boolean datatype in TSQL, so below will not compile:
SELECT au_lname <> 'White', au_lname FROM authors
But watch out for below:
SELECT au_lname = 'White', au_lname FROM authors
Above will produce a column names au_lname as the first column with all rows
having the string
"White" in that column. I.e., same as below (which is ANSI SQL and preferred
way):
SELECT 'White' AS au_lname, au_lname FROM authors
(Pretty crappy semantics in above, but should show the point I'm trying to m
ake.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tim Harvey" <papow@.yahoo.com> wrote in message news:uyZw4zWFGHA.3900@.TK2MSFTNGP10.phx.gbl.
.
>I have a question about a expression used in access and would like to know
if there is a way to
>simulate the functionality in SQL.
> The qry "select NetUsage<>0 AS Expr1 FROM TBL_QUATIONTEST"
> In access would return either 1 or 0 or the calculated value if there was
one, where NetUsage is a
> field of TBL_QUATIONTEST and would evaluate to a boolean or calcualated re
sult based on the
> condition. How can I accomplish this SQL? At times the expression could ge
t complicated and use
> several other fields from the table TBL_QUATIONTEST in the expression.
> ex:
> SELECT (12.06+ ((NetUsage/750+DemadUasge)*.332)) AS Expr1
> FROM TBL_QUATIONTEST
> Any adivce on how I can repliacte this in SQL would help. I truely simeply
need to evaluate it to
> a boolean value or the caluculated reslut.
>
> Beat Reagrds.
>|||Not sure anyone answered the question you actually asked, change your query
from:
select NetUsage<>0 AS Expr1 FROM TBL_QUATIONTEST
To:
select case when NetUsage<>0 then 1 else 0 end as Expr1
FROM TBL_QUATIONTEST
Like Aaron said, there is not a boolean type, so you have to deal with this
in a CASE expression.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Tim Harvey" <papow@.yahoo.com> wrote in message
news:uyZw4zWFGHA.3900@.TK2MSFTNGP10.phx.gbl...
>I have a question about a expression used in access and would like to know
>if there is a way to simulate the functionality in SQL.
> The qry "select NetUsage<>0 AS Expr1 FROM TBL_QUATIONTEST"
> In access would return either 1 or 0 or the calculated value if there was
> one, where NetUsage is a field of TBL_QUATIONTEST and would evaluate to a
> boolean or calcualated result based on the condition. How can I
> accomplish this SQL? At times the expression could get complicated and use
> several other fields from the table TBL_QUATIONTEST in the expression.
> ex:
> SELECT (12.06+ ((NetUsage/750+DemadUasge)*.332)) AS Expr1
> FROM TBL_QUATIONTEST
> Any adivce on how I can repliacte this in SQL would help. I truely simeply
> need to evaluate it to a boolean value or the caluculated reslut.
>
> Beat Reagrds.
>