Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sameerv
Starting Member
29 Posts |
 Posted - 2002-10-22 : 00:50:16
|
| Hi Guys,I have 3 tablesTableA - 60356 recordsTableB - 2075 records.TableC - 0 records.When I run the following SELECT Query, it takes 3 min and 11 seconds select * from TableA where not exists (select distinct Code from TableB where Code =TableA.Code)If I run a similar query in which the result of the SELECT Query is inserted into TableC it takes exactly 1 second. Insert into TableCselect * from TableA where not exists (select distinct Code from TableB where Code =TableA.Code)Why is there such a huge difference in performance ???? |
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2002-10-22 : 03:06:08
|
| Maybe you have some kind of network problems. The first query will return a lot of rows (i presume). The other one returns only the rowcount. The queries should take equal time for the server to process (or the first one should be even faster). |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-22 : 08:11:51
|
| After you run the first SELECT query, the data is now in cache. When you run the INSERT, it can retrieve the data from cache instead of reading the disk. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-10-22 : 12:44:52
|
When Testing Performance, put this at the top of your query: DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHE DO NOT run this code on a production system. It could make things slow and may cause other issues that I'm unaware of. Only run this on your development DB.From BOL:Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server.Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
|
|
|
|
|
Doubt on performance