Slow running query (Sometimes)

Question

Post reply

Slow running query (Sometimes)

Crispin Proctor

SSCoach

Points: 18161
More actions
October 5, 2003 at 11:32 pm

#160028
Greetings.
I have a comparison app which is used to compare two DB's.
On one machine, the app takes about an hour to run and another machine about 15 minutes.
I ran a trace and saved the SQL which it runs (About 76000 lines of code).
Server 1)
Dual P3 800, 2GB RAM, RAID 5.
Server 2) (Converted workstation)
P3 1GHz 512MB, IDE drive (OS and SQL same Drive)
Server 1)
Query takes around 170-200ms
Reads: 255
Server 2)
On the workstation, around 40-50ms.
Reads: 62
Both machines vary in reads. Sometimes less, sometimes more.
Both execution plans are identical. No table scans.
Both machines are idle and I am the only person using them.
Why is it that the workstation is so much faster than the server?
Does that 200MHz difference in proc make that much of a difference?
Why so many more reads on the more powerful machine?
Even breaking the query into a simple select from sysobjects is slower on the dev server.
Ideas?
```
 



SELECT DISTINCT 

USER_NAME(O.uid) +'.'+ O.name, 

O.sysstat & 7,

O.xtype

FROM 

sysobjects O, sysdepends D

WHERE 

D.depid = OBJECT_ID('PCalc_Main')

AND D.id=O.id 

AND O.sysstat & 7  != 0 

AND O.type <> 'C'



UNION 



SELECT DISTINCT 

USER_NAME(O.uid)+'.' + OBJECT_NAME(O.parent_obj) + '.' +

O.name,

O.sysstat & 7,O.xtype  

FROM 

sysobjects O,

sysdepends D 

WHERE 

D.depid=OBJECT_ID('PCalc_Main') 

AND D.id=O.id 

AND O.sysstat & 7 = 0

 
```
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47794 More actions · Answer 1

Couple of things to check,

OS and SQL service packs at same level.

Same MDAC on both machines.

Any other DB type application on the slow server? Something that could require older DLL than the workstation??

How is the Raid array implemented? Hardware or Software?

What (if any) are your cache settings on the RAID array?

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

Crispin Proctor SSCoach Points: 18161 More actions · Answer 2

Hi Phill,

I guess I should have added in the software settings etc.

Both are SQL 2k with SP3a.

MDac would both be whatever came with SP3a.

Raid on dev is hardware (Cache unknown)

Workstation has IIS running on it.

The comparison app is installed and run from my machine connecting to the DB's across a 100Mb network. All 3 machine on same switch and I am the only one here.

More info? 🙂

Cheers,

Crispin

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 3

Some questions to check:

- What about the statistics?

- Are they up to date (check with DBCC SHOW_STATISTICS ( table , target ))?

- Do you have the same amount of data?

- Do you have the server/database settings (memory, recovery, processor...settings)

- Do you have the same performance setting of the OS (ie. optimise for backround/forground applications)

Bye

Gabor

Bye
Gabor

rstone SSCertifiable Points: 6209 More actions · Answer 4

I noticed that the reads are 4x on the "slower" machine. How do the cluster sizes compare?

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉