|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
optimize queries with unexpected resultsThis is one for the MVP’s. I am trying to optimize queries but I am finding un-expected results. I am using sql 2000. For example, I have read in several sites that referencing objects with qualified owner names is faster. The truth is that I am experiencing all the contrary. Am I doing something wrong in my test or this is just a rumour and no objects should be qualified? I am running both next queries in my own desktop machine, and clearing the chache every time I run the query as per below scripts . As I understand that the workload in my machine might vary (not really doing nothing during the test) I am taking at least 10 runs of each script and take the average results. I would expect better results for script 2 that for one (test show the contrary), can anybody explain why? I am examining several counters, but would be happy enough if just logical reads would show better results. With this results … how can I face the developers and tell them this and other supposed coding “advantagesâ€, or shall I just forget about reality and tamper the results in a Mendel fashion :-) ? Script1: SET STATISTICS IO on SET STATISTICS TIME on --above to lines just run once in each window DBCC DROPCLEANBUFFERS dbcc freeproccache SELECT TOP 1000 * FROM Table1 , Table2 where Table1.Idfield= Table2.Idfield Script2: SET STATISTICS IO on SET STATISTICS TIME on --above to lines just run once in each window DBCC DROPCLEANBUFFERS dbcc freeproccache SELECT TOP 1000 * FROM dbo.Table1 , dbo.Table2 where dbo.Table1.Idfield= dbo.Table2.Idfield Thanks in advance, your help is much appreciated, Tristan In addition to the [procedure cache] which contains execution plans,
there is also the [buffer cache] which contains previously read data and index pages. This can have an even greater impact on query duration, especially if you are executing the same query back to back. Since this is a local instance of SQL Server, you may want to stop / re-start the service prior to each test. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_1zu4.asp Also, in Query Analyzer, choose the menu option Query.. Show Execution Plan. After the query comples, hovering the mouse over each step will reveal information like whether a index seek, index scan, table scan, etc. were performed and the actual I/O and CPU cost. If (after completely clearing all caches) one run has basically the same I./O cost as another run, then there are probably other environmental factors that influenced the outcome. Performance on a desktop PC will be more variable than on a production server, becuase there are so many other background services and processes, such as anti-virus, running. Show quote "Tristan" <Tris***@discussions.microsoft.com> wrote in message news:1D180C57-9AFF-458D-9529-9560FB154B73@microsoft.com... > Hi, > > This is one for the MVP's. I am trying to optimize queries but I am > finding > un-expected results. I am using sql 2000. > > For example, I have read in several sites that referencing objects with > qualified owner names is faster. The truth is that I am experiencing all > the > contrary. Am I doing something wrong in my test or this is just a rumour > and > no objects should be qualified? > > I am running both next queries in my own desktop machine, and clearing the > chache every time I run the query as per below scripts . As I understand > that > the workload in my machine might vary (not really doing nothing during the > test) I am taking at least 10 runs of each script and take the average > results. > > I would expect better results for script 2 that for one (test show the > contrary), can anybody explain why? I am examining several counters, but > would be happy enough if just logical reads would show better results. > With > this results . how can I face the developers and tell them this and other > supposed coding "advantages", or shall I just forget about reality and > tamper > the results in a Mendel fashion :-) ? > > Script1: > SET STATISTICS IO on > SET STATISTICS TIME on > --above to lines just run once in each window > > DBCC DROPCLEANBUFFERS > dbcc freeproccache > > SELECT TOP 1000 * > FROM Table1 , Table2 > where Table1.Idfield= Table2.Idfield > > Script2: > SET STATISTICS IO on > SET STATISTICS TIME on > --above to lines just run once in each window > > DBCC DROPCLEANBUFFERS > dbcc freeproccache > > SELECT TOP 1000 * > FROM dbo.Table1 , dbo.Table2 > where dbo.Table1.Idfield= dbo.Table2.Idfield > > Thanks in advance, your help is much appreciated, > Tristan > Hi JT / Wayne,
I was allready looking with execution plans plans and where the exactly the same, statistics showed brutal differences, but the worst was with the logial reads, it just didnt make sense a diference of 1000 between one method and the other. As sometimes happends in this job guys, closed the window and opened another one and the error disapeared. Anyway, thanks a lot guys. Hi JT, FYI from BOL "Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server.", dont know if there are any other methods to clear more buffer than the ones I ussed, though u r right to advise not to test in PC, sorry but prod and UAT are fried with users :-) Show quote "JT" wrote: > In addition to the [procedure cache] which contains execution plans, > there is also the [buffer cache] which contains previously read data and > index pages. This can have an even greater impact on query duration, > especially if you are executing the same query back to back. Since this is a > local instance of SQL Server, you may want to stop / re-start the service > prior to each test. > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_1zu4.asp > Also, in Query Analyzer, choose the menu option Query.. Show Execution > Plan. After the query comples, hovering the mouse over each step will reveal > information like whether a index seek, index scan, table scan, etc. were > performed and the actual I/O and CPU cost. If (after completely clearing all > caches) one run has basically the same I./O cost as another run, then there > are probably other environmental factors that influenced the outcome. > Performance on a desktop PC will be more variable than on a production > server, becuase there are so many other background services and processes, > such as anti-virus, running. > > > > > "Tristan" <Tris***@discussions.microsoft.com> wrote in message > news:1D180C57-9AFF-458D-9529-9560FB154B73@microsoft.com... > > Hi, > > > > This is one for the MVP's. I am trying to optimize queries but I am > > finding > > un-expected results. I am using sql 2000. > > > > For example, I have read in several sites that referencing objects with > > qualified owner names is faster. The truth is that I am experiencing all > > the > > contrary. Am I doing something wrong in my test or this is just a rumour > > and > > no objects should be qualified? > > > > I am running both next queries in my own desktop machine, and clearing the > > chache every time I run the query as per below scripts . As I understand > > that > > the workload in my machine might vary (not really doing nothing during the > > test) I am taking at least 10 runs of each script and take the average > > results. > > > > I would expect better results for script 2 that for one (test show the > > contrary), can anybody explain why? I am examining several counters, but > > would be happy enough if just logical reads would show better results. > > With > > this results . how can I face the developers and tell them this and other > > supposed coding "advantages", or shall I just forget about reality and > > tamper > > the results in a Mendel fashion :-) ? > > > > Script1: > > SET STATISTICS IO on > > SET STATISTICS TIME on > > --above to lines just run once in each window > > > > DBCC DROPCLEANBUFFERS > > dbcc freeproccache > > > > SELECT TOP 1000 * > > FROM Table1 , Table2 > > where Table1.Idfield= Table2.Idfield > > > > Script2: > > SET STATISTICS IO on > > SET STATISTICS TIME on > > --above to lines just run once in each window > > > > DBCC DROPCLEANBUFFERS > > dbcc freeproccache > > > > SELECT TOP 1000 * > > FROM dbo.Table1 , dbo.Table2 > > where dbo.Table1.Idfield= dbo.Table2.Idfield > > > > Thanks in advance, your help is much appreciated, > > Tristan > > > > > The first thing you should do is to look at the query plan for each of these
scenarios and see what the difference is.. -- Show quoteWayne Snyder MCDBA, SQL Server MVP Mariner, Charlotte, NC I support the Professional Association for SQL Server ( PASS) and it''s community of SQL Professionals. "Tristan" wrote: > Hi, > > This is one for the MVP’s. I am trying to optimize queries but I am finding > un-expected results. I am using sql 2000. > > For example, I have read in several sites that referencing objects with > qualified owner names is faster. The truth is that I am experiencing all the > contrary. Am I doing something wrong in my test or this is just a rumour and > no objects should be qualified? > > I am running both next queries in my own desktop machine, and clearing the > chache every time I run the query as per below scripts . As I understand that > the workload in my machine might vary (not really doing nothing during the > test) I am taking at least 10 runs of each script and take the average > results. > > I would expect better results for script 2 that for one (test show the > contrary), can anybody explain why? I am examining several counters, but > would be happy enough if just logical reads would show better results. With > this results … how can I face the developers and tell them this and other > supposed coding “advantagesâ€, or shall I just forget about reality and tamper > the results in a Mendel fashion :-) ? > > Script1: > SET STATISTICS IO on > SET STATISTICS TIME on > --above to lines just run once in each window > > DBCC DROPCLEANBUFFERS > dbcc freeproccache > > SELECT TOP 1000 * > FROM Table1 , Table2 > where Table1.Idfield= Table2.Idfield > > Script2: > SET STATISTICS IO on > SET STATISTICS TIME on > --above to lines just run once in each window > > DBCC DROPCLEANBUFFERS > dbcc freeproccache > > SELECT TOP 1000 * > FROM dbo.Table1 , dbo.Table2 > where dbo.Table1.Idfield= dbo.Table2.Idfield > > Thanks in advance, your help is much appreciated, > Tristan > There can be probably "wrong" index use problem, look ath te query analyzer,
which index is really used in query and probably you must force the select INDEX hint Wayne Snyder wrote: Show quote >The first thing you should do is to look at the query plan for each of these >scenarios and see what the difference is.. > >> Hi, >> >[quoted text clipped - 45 lines] >> Thanks in advance, your help is much appreciated, >> Tristan Tristan,
The execution plans of the two queries should be the same. If they are not, then that is a different issue. If you use TOP 1000 and the join between Table1 and Table2 produces more than 1000 rows, then the results can differ between runs. That is not a good test situation. The difference between a qualified and unqualified object is only relevant in the query compilation phase. The potential performance difference will not show up in the logical reads. Gert-Jan Tristan wrote: Show quote > > Hi, > > This is one for the MVP’s. I am trying to optimize queries but I am finding > un-expected results. I am using sql 2000. > > For example, I have read in several sites that referencing objects with > qualified owner names is faster. The truth is that I am experiencing all the > contrary. Am I doing something wrong in my test or this is just a rumour and > no objects should be qualified? > > I am running both next queries in my own desktop machine, and clearing the > chache every time I run the query as per below scripts . As I understand that > the workload in my machine might vary (not really doing nothing during the > test) I am taking at least 10 runs of each script and take the average > results. > > I would expect better results for script 2 that for one (test show the > contrary), can anybody explain why? I am examining several counters, but > would be happy enough if just logical reads would show better results. With > this results … how can I face the developers and tell them this and other > supposed coding “advantagesâ€, or shall I just forget about reality and tamper > the results in a Mendel fashion :-) ? > > Script1: > SET STATISTICS IO on > SET STATISTICS TIME on > --above to lines just run once in each window > > DBCC DROPCLEANBUFFERS > dbcc freeproccache > > SELECT TOP 1000 * > FROM Table1 , Table2 > where Table1.Idfield= Table2.Idfield > > Script2: > SET STATISTICS IO on > SET STATISTICS TIME on > --above to lines just run once in each window > > DBCC DROPCLEANBUFFERS > dbcc freeproccache > > SELECT TOP 1000 * > FROM dbo.Table1 , dbo.Table2 > where dbo.Table1.Idfield= dbo.Table2.Idfield > > Thanks in advance, your help is much appreciated, > Tristan  |
|||||||||||||||||||||||
Other interesting topics