I have a strange performance question hopefully someone can clarify
for me. I take a production database and make a copy of it, called
test, on the same instance on the same server both running at the same
time. All the same things are running for each database and no one is
using each database but me. From Query Analyzer I run a SQL against
production three times in a row and it takes 1 minute 40 seconds on
the last/best run. I then run the same SQL against the test copy and
run it three times in a row and the last/best time is 12 seconds. Can
anyone explain this behavior? If so, I hope this points to something I
can do to my production database to get the same performance.
Thanks,
MGB
5  1536 
MGB wrote: I have a strange performance question hopefully someone can clarify
for me. I take a production database and make a copy of it, called
test, on the same instance on the same server both running at the same
time. All the same things are running for each database and no one is
using each database but me. From Query Analyzer I run a SQL against
production three times in a row and it takes 1 minute 40 seconds on
the last/best run. I then run the same SQL against the test copy and
run it three times in a row and the last/best time is 12 seconds. Can
anyone explain this behavior? If so, I hope this points to something I
can do to my production database to get the same performance.
Thanks,
MGB
Did you check indexes for the tables in the original db?
Do you have the same indexes in the test db?
I had similar issue not long ago, when i copied a dozen of tables to another
database and quesries ran much faster against them comparing to the original tables in original db.
What i found that the original tables were 'overloaded' with 3/4/5 column indexes, so index file
was around 500Meg. When i dropped all indexes and let QA Wizard redo them, i got good execution times.
Also, it helps someties to stop and re-start the sql service :)
WYGL,
Andrey
Andrey,
Thanks for the reply. I should have been more clear in my original
post: The "copy' was made by taking a full backup of the production
database and restoring it onto the test database. Correct me if I am
wrong, but I assume the indexes would remain intact in that case. It
would probably be good to mention that this is on SQL Server 2000
running on a Windows 2000 Advanced Server.
Many times I have considered restarting the service but I have read
that the service "learns" how to automatically set setting for
performance. So I have not restarted so it wouldn't have to re-learn.
Is that not the case?
There is one other difference I thought of after my original post: As
part of the restore process I change the recovery model from Full to
Simple in the test database.
Thanks,
MGB
Andrey <le*******@yaho o.com> wrote in message news:<7Q36d.127 675$D%.85272@at tbi_s51>... MGB wrote:
I have a strange performance question hopefully someone can clarify
for me. I take a production database and make a copy of it, called
test, on the same instance on the same server both running at the same
time. All the same things are running for each database and no one is
using each database but me. From Query Analyzer I run a SQL against
production three times in a row and it takes 1 minute 40 seconds on
the last/best run. I then run the same SQL against the test copy and
run it three times in a row and the last/best time is 12 seconds. Can
anyone explain this behavior? If so, I hope this points to something I
can do to my production database to get the same performance.
Thanks,
MGB
Did you check indexes for the tables in the original db?
Do you have the same indexes in the test db?
I had similar issue not long ago, when i copied a dozen of tables to another
database and quesries ran much faster against them comparing to the original tables in original db.
What i found that the original tables were 'overloaded' with 3/4/5 column indexes, so index file
was around 500Meg. When i dropped all indexes and let QA Wizard redo them, i got good execution times.
Also, it helps someties to stop and re-start the sql service :)
WYGL,
Andrey
A couple thoughts:
1. Does your benchmark SQL perform updates? If yes, is the original
database using "full" recovery model, and the copy using "simple"?
(The recovery model is inherited from the "model" database.)
2. Are both databases on the same disk(s) and controllers?
Is the original database .mdf file highly fragmented on the disk?
Try to detach the database, defrag the disk, then re-attach, and see
if it makes a difference.
MGB wrote: I have a strange performance question hopefully someone can clarify
for me. I take a production database and make a copy of it, called
test, on the same instance on the same server both running at the same
time. All the same things are running for each database and no one is
using each database but me. From Query Analyzer I run a SQL against
production three times in a row and it takes 1 minute 40 seconds on
the last/best run. I then run the same SQL against the test copy and
run it three times in a row and the last/best time is 12 seconds. Can
anyone explain this behavior? If so, I hope this points to something I
can do to my production database to get the same performance.
Thanks,
MGB
Yes. Production (original) is using "full" recovery model, and the
copy is using "simple". Is "simple" faster?
Yes. both databases are on the same disk(s) and controllers. Haven't
checked for fragmentation.
Thanks,
MGB
"L. Bertolini" <be*********@os u.edu> wrote in message news:<cj******* **@charm.magnus .acs.ohio-state.edu>... A couple thoughts:
1. Does your benchmark SQL perform updates? If yes, is the original
database using "full" recovery model, and the copy using "simple"?
(The recovery model is inherited from the "model" database.)
2. Are both databases on the same disk(s) and controllers?
Is the original database .mdf file highly fragmented on the disk?
Try to detach the database, defrag the disk, then re-attach, and see
if it makes a difference.
MGB wrote:
I have a strange performance question hopefully someone can clarify
for me. I take a production database and make a copy of it, called
test, on the same instance on the same server both running at the same
time. All the same things are running for each database and no one is
using each database but me. From Query Analyzer I run a SQL against
production three times in a row and it takes 1 minute 40 seconds on
the last/best run. I then run the same SQL against the test copy and
run it three times in a row and the last/best time is 12 seconds. Can
anyone explain this behavior? If so, I hope this points to something I
can do to my production database to get the same performance.
Thanks,
MGB
MGB wrote: Yes. Production (original) is using "full" recovery model, and the
copy is using "simple". Is "simple" faster?
Yes, because there is less logging overhead.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: jfixsen |
last post by:
Hello!
Oracle 9.2.0.4
SunOS pchi-db01 5.8 Generic_108528-19 sun4u sparc
SUNW,Ultra-EnterpriseSystem = SunOS
Node = pchi-db01
Release = 5.8
KernelID = Generic_108528-19
Machine = sun4u
BusType = <unknown>
|
by: David |
last post by:
hello.
when doing the simple following computation, the value put into the variable
numMinusOne is NOT the same as what the computation is showed to be in the
Watch window!!
here is the code:
Dim xSng As Single = 6547.972
Dim yInt As Integer = 8000
Dim num As Integer = CInt(Math.Floor(xSng * yInt))
Dim numMinusOne As Integer = CInt(Math.Floor(xSng * yInt) - 1)
|
by: Roland |
last post by:
Hi!
I am working on a project in which i implement a mathematical
optimization algorithm. One of the requirements on the code is very
good performance. So i started to optimize a bit. Now i do have a very
strange effect which i do not understand. Maybe somebody knows about:
I have the following class
template<class T> class SimplexL2Penalty : public Function<T,T>
|
by: Mark Barinstein |
last post by:
Hello.
W2K, db2 v7, fp11.
Given:
create table pays (
acode integer not null,
packno smallint not null,
sum decimal(15, 2) not null
|
by: Wescotte |
last post by:
Here is a small sample program I wrote in PHP to help illustrates
problem I'm having. The data base is using DB2 V5R3M0. The client is
WinXP machine using the iSeries Client Access Driver ver 10.00.04.00 to
connect to the database.
The problem is that executing the exact same SQL select statement more
than twice int a row stops produces results. The first two instances
will always produce the correct results but after that it will simply...
| | |
by: z. f. |
last post by:
Hi,
i have asp.net vb.net wep application.
i try to investigate performance issue.
so first i built a vb.net windows application that makes http requests to my pages and show me the time taken to receive the page and it's size.
the windows app uses a WebRequest and StreamReader to receive the http request.
the strange information is for a page that took to receive about 218 ms
i enabled the trace in the web.config of my pages, and trace...
|
by: William Sullivan |
last post by:
I have an extremely weird problem that I have no idea how to approach.
I have a simple page with a search textbox and a search button. The button
causes a postback, where I perform the search and display the results in a
DIV that has a tree. I've tested all the code up until I add in the code for
adjusting the tree in the DIV and it all works fine without problems. I can
perform the search and put the results in the body of the page and...
|
by: Wang E |
last post by:
I've been working on an online judge(for ACM/ICPC) using C#.Programmes
submitted by users can now be compiled,and it's the problem to judge.I
use the Process class in C#,and my thread is as follows:start a process
using this class with parameters such as input file,output file,etc,and
when the programme is running,i get its running information such as
running time,used memory,etc.I code as follows:
Process p = new Process();...
|
by: Nicholas Palmer |
last post by:
Hi all,
Got a question about the AspCompat=true page property. First a little
background. We have an ASP.NET app that uses two COM components. The first
is the Microsoft OWC 11 components and the second is a custom VB6 COM
component. So I was reading about AspCompat=true and it seemed like it would
be a good fit for our app. From what I can tell both of the COM components
that we are using are STA and we are creating the components in...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
