Basic Performance Question

Last Post 08 Jan 2007 11:12 AM by sanjayattray. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
dianagele
New Member
New Member

--
07 Jan 2007 06:39 PM
I am not too embarassed to ask:

I was testing two tables and their performance with left right and full outer joins..
which brings me to the simple question I cannot find in help file:

Each time a query is executed, it takes longer and longer to execute.
I thought just the opposite would happen...
here's a very large table times:

TRY1: 20:07:49
TRY2: 20:08:06
TRY3: 20:08:11
TRY4: 20:08:33
TRY5: 20:08:55

I presumed the execution plan would have already been put in place...
Q: Why would why would an execute of a query take longer each time you execute?:
I shall crawl into my hole now...

(studied: InstantDoc #7446, but am using 2005)
sanjayattray
New Member
New Member

--
08 Jan 2007 11:12 AM
if your indexes are in right place, then try executing Update statistics <table name> with fullscan on tables this query uses.

Give it a try and it might help.
SQLUSA
New Member
New Member

--
09 Jan 2007 03:07 PM
Do DBCC dropcleanbuffers between trials.

Kalman Toth DB, DW & BI Architect, SQL Server Training
URL: http://www.sqlusa.com/order2005grandslam
The Best SQL Server 2005 Training in the World
JHunter
New Member
New Member

--
10 Jan 2007 02:40 AM
Kalman, I think this would invalidate what she is trying to demonstrate ie that due to caching the results, subsequent executions should be faster. Please correct me if I'm wrong dianagele...

dianagele,
How big is your data - is it larger than the max RAM that is available to your SQL engine?

Have you checked the IO figures for each run? (i.e. in QA: SET STATISTICS IO ON)

Have you looked to see if the plan "uses" is being incremented? (e.g. select usecounts, sql from master..syscacheobjects where dbid = ... and sql like ...)

Is your environment isolated (ie there are no other users on the server or jobs running during your tests)? Is the server performing any other functions ie IIS, file/print server, DC?

Jamie
whynot
New Member
New Member

--
10 Jan 2007 05:40 AM
dianagele:
I have not come out my hole for a long time. The warm winter makes me wake up earlier. I am not sure what time and what scale you are talking here. If the scale is hours, that difference is nothing to me. If this is duration time, that is normal, since that include the variable IO time. If this is CPU time, it's little bit odd, but still I won't bother to investigate. Anyway, I need a few more stretches to clear my mind.
dianagele
New Member
New Member

--
18 Jan 2007 06:27 PM
Thank you Jamie, for responding to my "tinkering" question that has been nagging at me.

my data is a bit large... about 100,000 rows with four indexes and two triggers; we let the app do the slicing and dicing. However, I tried this on my local copy and had the same results?

The test posted was on a different table

I appreciate the reccomendations for any performance "_sp's, WITH" and such; I am quite green and need to hone in on thes performance issues.

our server has other trusted servers, but yawns at minimum 100 concurrent users... so I don't think so...

I tried this on my local db & results were consistent. Scheduled jobs isolated at minimal times.

to whynot: Thanks for the response. The time is coming from the Client Statistics window in the QA --> cumulative amount of time (in milliseconds)

Any input on playing around with watching performance is invited! (BOL can get one blurry-eyed.)

to RuseelB: you hit the nail on the head; any examples for a greenhorn?
You are not authorized to post a reply.

Acceptable Use Policy