SQL tool for MSDE: develop

Showing posts with label develop. Show all posts

Sunday, February 19, 2012

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?

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]
>

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

Deke
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:etEEx2%23eEHA.3476@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> 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
> 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[vbcol=seagreen]
> 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.

Thursday, February 9, 2012

Advice required: Matrix report with a static column

First off, I apologize if this is an elementary question. Thanks for your
patience.

I have a requirement to develop a report that shows by calendar day how much
effort a resource has expended on what tasks. So, I have a stored procedure
that returns data that looks like this:

Resource calendarDay description etcHours effortHours
- - -- - --
Smith, John 2006-08-01 Requirements 20.00 8.00
Smith, John 2006-08-02 Requirements 20.00 8.00
Smith, John 2006-08-03 Design 80.00 8.00
Doe, Jane 2006-08-01 Requirements 5.00 4.00
Doe, Jane 2006-08-01 Design 30.00 4.00

And I've developed a matrix report that looks like this:

AUGUST
1 2 3 4 5
-
Name 1 Requirements 8.00 8.00
Design 8.00
Name 2 Requirements 4.00
Design 4.00

What I need to do, however, is add the "ETC" value at the END of the matrix
row for each Task for each Resource, so that it looks like this:

AUGUST
1 2 3 4 5 ETC

Name 1 Requirements 8.00 8.00 20.00
Design 8.00 80.00
Name 2 Requirements 4.00 5.00
Design 4.00 30.00

I can't seem to add a column to the matrix to represent the ETC column. What
would be the best way to accomplish this?

Thanks in advance for any replies,
Dr Jazz

Dr.

You can right click on Column 5 and select "Add Column" this will create ETC, this will add a static column to the matrix.

Hammer

|||

Hammer2 wrote:

You can right click on Column 5 and select "Add Column" this will create ETC, this will add a static column to the matrix.

Thanks for the reply, Hammer. Unfortunately, "Column 5" doesn't exist in the report designer because this is a matrix control. The only items available are "Column Groups" and I have two defined: one for the month, then another for the actual day (since the date range is a parameter specified by the user). I can right-click and add more Column Groups, but I cannot add a single static column, thus my dilemma.

Cheers,
Dr. Jazz

|||

Humm,

Puzzling because I have multiple Matrix reports and a number of them have static column, you should be able to "Add a column" this is a static column - you can also google matrix reports and static columns, this should bring up some examples.

Hope this helps

Hammer

|||

Hi Hammer,

Thanks for your continued patience and assistance. Check out this web page that will hopefully illustrate my problem better:

http://www.duotronic.com/matrix/matrix.htm

As you can see, I am able to get an "Add column" menu item, but I don't want it to be part of the group because this value will then be displayed for every day in the group. I just want it displayed at the end of the row. Is there a way to do this?

Cheers,
Dr Jazz

|||You arent using your query correctly as you should be using aggregates on that column as well.
SQL cannot tell if you just want a column there since your report is a grouping report.

Can you post your SQL query.

http://jhermiz.googlepages.com|||

Hi jhermiz. Thanks for your reply. I have no doubt that the structure of my query is not exactly compatible with how Reporting Services requires it. My query is listed below:

SELECT
emp.LastName + ', ' + emp.FirstName AS Resource,
cal.calendarDay,
task.taskID,
task.taskCode,
task.description,
tass.etcHours,
-- Get billable hours for this user on this day
SUM(tae.effortHours) AS effortHours

FROM
dbo.udf_getDateRangeTable(@.startDate, @.endDate) AS cal
CROSS JOIN HR.dbo.Employee AS emp
LEFT JOIN dbo.TaskAssignment AS tass ON tass.resourceID = emp.employeeID
LEFT JOIN dbo.TaskActualEffort AS tae ON tae.assignmentID = tass.assignmentID
AND tae.startDate = cal.calendarDay
LEFT JOIN dbo.Task AS task ON tass.taskID = task.taskID
LEFT JOIN dbo.TrackingItem AS ti ON task.trackingItemID = ti.trackingItemID

WHERE
ti.masterProjectID = @.masterProjectID
AND emp.employeeID IN (SELECT employeeID FROM HR.dbo.ProjectEmployeeAssignment WHERE projectID = @.masterProjectID)
-- Only return resources who have actually billed time in the reporting period
AND EXISTS
(
SELECT 1
FROM
dbo.TaskAssignment AS tass1
INNER JOIN dbo.Task AS task1 ON tass1.taskID = task1.taskID
INNER JOIN dbo.TaskActualEffort AS tae1 ON tass1.assignmentID = tae1.assignmentID
INNER JOIN dbo.TrackingItem AS ti1 ON ti1.trackingItemID = task1.trackingItemID
WHERE
tass1.resourceID = emp.employeeID
AND tae1.startDate BETWEEN @.startDate AND @.endDate
AND ti1.masterProjectID = @.masterProjectID
)

GROUP BY
emp.LastName + ', ' + emp.FirstName,
calendarDay,
emp.employeeID,
task.taskID,
task.taskCode,
task.description,
tass.etcHours

ORDER BY
task.taskID,
Resource,
cal.calendarDay

Cheers,
Andre

|||

Hey Dr.

Nice pages by the way, Okay, I understand the nature of your problem - When you "Add the column" you can use the "inscope" expression to tell the which group to display for.

Does that make sense?

Hammer

Sunday, February 19, 2012

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

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

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

Thursday, February 9, 2012

Advice required: Matrix report with a static column

SQL tool for MSDE

Blog Archive

About Me